Lab: Writing SQL statements

You can choose to use either the username_siteVote database from the previous exercise or the username_classicmodels database that you imported in first exercise.

In working with Databases in PHP, there are four things that you need to be able to do that are know as CRUD.

C - Create new Records
R - recall records according to the criteria, fields, and sort order that you want with just the fields from one or more tables.
U - update records to change the data
D - delete records

In this Lab, I want you to write SQL statements in each of these four categories and test them in phpMyAdmin. You test them by going to Databases in phpMyAdmin and then picking the database you want to try it on. Then type the SQL statement into the box, and click GO. After you write each of the required SQL statements as described below, add them to the edit submission on this page. Indicate for each one, the name of the database it was done on.

For example, if you were going to show the names and phone numbers of all the Customers from the classicmodels database you would write this on this web page:

classicmodels:
SELECT customerName, phone FROM customers;

NOTE about autoincrement and primary keys:

Most tables have a field that is called the primary key. It uniquely identifies the record. You have the option in MySQL databases to turn on a extra attribute of primary keys so that the database automatically creates them as a sequence of increasing numbers. This is called Auto_increment. If auto_increment is on, you should not insert that field when you create new records, leave the field unlisted in the insert statement.

If the table you are inserting into does not use auto_increment (like all the tables in the classicmodels database) then you must include the field and a value for it when doing the insert. But in most cases you have to pick a value that has not been used before. Look at the database and find the highest number that was used before, and make your key one more.

Also primary keys are used to relate between two tables. When you see a primary key from one table listed as a field within another table, those two tablels are probably related to each other in one of the 4 types of relations described in the Gosselin book. If you update a record or delete a record you need to be aware if this leaves a problem in the overall database. For example you delete Product record in the products table in the classicmodels database, then this would leave Orderdetails records refering to a productCode number that will no longer exist. These are important issues to handle for relational databases, but are beyond the scope of this class. So in this class, you will not be penalized if you "break" the database.
To learn more about this, you would need a good database class like the Oracle CBIS 424 or the CBIS 38 class.

I will cover some simple cases on how to handle these problems in the code of the next chapter.


Ok now do the following:

1. Create:

write 2 SQL statements to add records to a table in classicmodels or into your siteVote database. You can pick any table to insert the records into (as log as it works)

NOTE: you should pick codes numbers or Id numbers that are unique if it is a primary key, and already exist as a primary key if they are listed as a ''foreign' ' key.

2. Recall

write 3 select statements to show fields for different tables. One should do a sort, one should use a criteria, and one should only show one record by using the where part to specify a unique value of the primary key.

3. Update

write an update sql statment to change one or more values in a table record

4. Delete

write a delete sql statement to delete one record indicated by a unique id number

Remember to write the database name and sql code for each part in the edit submission area of this page