# SQL Notes: Exercise 7: Creation, Updates, Views

Name: _________________________________

Use the "Sales" database from SQL: The Complete Reference in answering these questions. You must assume that the data may have changed from the sample data, but the definition of the tables has remained the same.

1. (4 points) Your boss wants to see a list of every customer (company), the salesperson (name and employee number), and the city their office is in. The lines should be alphabetized by the company name. The columns should be titled Company, SalesPerson, IDNum, and Location. Unfortunately, she didn't take ISM411, so you have to do the work as usual. Write the query for her.
   There should be one row of output per company, even if there is a NULL in the Salesreps.rep_office or Customers.cust_rep fields, since these fields may be NULL as they are defined on p850. Indeed one of them is NULL in the sample database.
2. (4 points) Now she says that this is too slow, although it's never been slow for you, and asks you to build a new table (called CompanyReps) that has only the information that was produced by the previous query. Give the SQL command(s) necessary to create that table, including specification of the primary and foreign keys, if any.
3. (2 points) Now that you've created the table, extract the appropriate data (the data as shown by the SELECT command from question 1) from the database and add it to the new table. Because you don't know what the values are in the current database, you can't write a series of INSERT...VALUES statements. Also, do not use the common, but non-standard, SELECT...INTO statement. Write the SQL command(s) to do this.
4. (2 points) What query will your boss have to use to get the same results using the new table as she did before?
5. (2 points) The data has changed in the original tables and you need to update the new table. You're in a hurry to go to lunch, so you decide to change only the one value that is out-of-date without updating everything in the table. Change the name of "Acme Mfg." to "Acmo Mfg." in the CompanyReps table.
6. (2 points) You're back from lunch now, and you decide you had better update all values in the CompanyReps table because there might have been other changes in the base tables that you hadn't noticed. Note that this may result in more, or fewer, rows than before because of changes in the database (more or fewer customers). You could DROP the table and start all over again, but that is too drastic. Give the command(s) to do this.
7. (3 points) Although this new table is much faster, the out-of-date information in it has caused even greater problems. (a) Give an SQL command that will remove this table. (b) The SQL2 standard says that you must specify either RESTRICT or CASCADE on DROP TABLE command although many database systems do not require it. What would be the effects of specifying RESTRICT or CASCADE when deleting this table. Assume no other object refers to this table.
8. (2 points) Now that there is no CompanyReps table, create a view with the same name that helps your boss see the information she was looking as in question 1.
9. (2 points) What is the command that your boss will use to do the query using this new view?
10. (2 points) It has become necessary to break Salesreps.name into two fields, Fname and Lname. Add these columns to the Salesreps table.
11. (3 points) We didn't study the functions on p 87, so you probably don't know how to break up the current name field to put it into the new Lname and Fname fields, so let's assume this has already been done. Now it's time to get rid of the old name column. (a) Show the SQL to do that. (b) Remembering that the old (not yet updated) CompanyReps view is still there, discuss what would happen when you specify the RESTRICT and CASCADE options when removing the name column from Salesreps.