B.6. What You Should Know

Now that you have finished this appendix, you should be able to answer the following questions:

1.	How do you retrieve all the records from a database table?
2.	How do you retrieve a select set of records or a single record from a table based on specific criteria?
3.	How do you select and sort records in a database?
4.	How do you select a range of rows from a database?
5.	How do you create a database?
6.	How do you create database tables?
7.	How do you assign a primary key to a field?
8.	How are records inserted into the database table?
9.	How are records updated in a table?
10.	How do you delete a record?

Exercise B 1. Go to the MySQL console and use the show command to list all the databases. Use the mysql database. Now display all of its tables. 2. Create a new database called school. Once you create the database, you need to be able to use it: use school; 3. Create a table called student. The table will consist of the following fields: FirstName LastName Email CellPhone Major GPA StartDate StudentId (the primary key) The following information is the type of data you will use to define your table. Go to the Web and look for a table similar to this to use as your guide. Data Type Description integer(size)   int(size)   smallint(size)   tinyint(size) Holds integers only The maximum number of digits is specified by size in parentheses. decimal(size, d) numeric(size, d) Holds numbers with fractions. The maximum number of digits is specified in size. The maximum number of digits to the right of the decimal is specified in d. char(size) Holds a fixed-length string (can contain letters, numbers, and special characters). The fixed size is specified by size in parentheses. varchar(size) Holds a variable-length string (can contain letters, numbers, and special characters). The maximum size is specified by size in parentheses. date(yyyymmdd) Holds a date. 4. Use the SQL describe statement to display the information you used to create the school database. 5. Insert three rows into the table: Row 1: FirstName: John LastName: Doe Email: johndoe@smileyface.edu CellPhone: 408-333-3456 Major: CIS GPA: 2.8 StartDate: 09/22/2004 (use the correct date format!) StudentId: 1 Row 2: FirstName: Mary LastName: Chin Email: mchin@qmail.com CellPhone: 408-204-1234 Major: Biology GPA: 3.3 StartDate: 06/22/2003 StudentId: 2 Row 3: FirstName: Sadish LastName: Pamel Email: sadi@univ_ab.edu CellPhone: 415-204-1234 Major: CIS GPA: 3.9 StartDate: 06/22/2003 StudentId: 2 6. Use the show commands to display all the fields. 7. Use select statements to display the following (write your query in the blank line): The data in all of the columns The first and last names of the students The student's first and last names and major The student's cellphone and e-mail addresses Any distinct majors Only 2 rows 8. Select all students who have a GPA over 3.0. Select all students who have a GPA between 3.0 and 4.0. Select students whose cellphones are in the 408 area code. Display rows for students who started in 2003. Select student first and last names who are majoring in CIS and have a GPA over 3.5. Select student first name and e-mail address if the e-mail address ends in .com. 9. Insert a new entry into the table. Sort the student table by last names. Sort the student table by GPA in descending order. 10. Change Mary's phone number to 650-123-4563. The next three questions deal with SQL functions: 11. Find the average GPA for all the students. 12. Find the number of rows in the table. 13. Print today's date using a SQL function.