# SQL Notes: Exercise 7: Creates, Updates, Alters, 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.
   Answer:

       -- The NULL problem is solved by using a left join.
       SELECT Company, name AS SalesPerson, empl_num AS IDNum,
              city AS Location
         FROM (Customers LEFT JOIN Salesreps ON cust_rep = empl_num)
               JOIN Offices ON office = rep_office
         ORDER BY Company;

       -- The left join using older, non-standard, *= notation is
         ...
         FROM Customers, Salesreps, Offices
         WHERE cust_rep *= empl_num AND rep_office *= office;
         ...

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.
   Answer:

       -- The domains should match those of the original tables.
       -- As a result of the LEFT JOIN all fields except Company may be
       -- NULL although they are NOT NULL in their original tables.
       CREATE TABLE CompanyReps (
              Company     VARCHAR(20) NOT NULL,
              Salesperson VARCHAR(15),
              IDNum       INTEGER     REFERENCES Salesreps (empl_num),
              Location    VARCHAR(15)
       );
       -- No need for a primary key since no other table links to it.
       -- The foreign key could also have been specified at the end as:
             FOREIGN KEY (IDNum) REFERENCES Salesreps (empl_num)

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.
   Answer:

       INSERT INTO CompanyReps (Company, SalesPerson, IDNum, Location)
          SELECT from-question-1-without-order-by;
       -- Everyone specified the destination columns.  Doing so is good
       -- style in case something changes, but it isn't necessary if you
       -- are absolutely sure the columns match.  Ie,
       INSERT INTO CompanyReps SELECT ...

4. (2 points) What query will your boss have to use to get the same results using the new table as she did before?
   Answer:

       SELECT *
         FROM CompanyReps
         ORDER BY Company;

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.
   Answer:

       UPDATE CompanyReps
          SET Company = 'Acmo Mfg.'
        WHERE Company = 'Acme Mfg.';

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.
   Answer:

       DELETE FROM CompanyReps;  -- deletes all rows from the table
       INSERT-statement-from-question-3;

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.
   Answer:

       -- (a)
       DROP TABLE CompanyReps;
       -- (b) Because no other object refers to this table, neither option
       --     does anything in this case.
       --     CASCADE = drop any objects that refer to this.
       --     RESTRICT= don't do this command if there are any
       --               references 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.
   Answer:

       CREATE VIEW CompanyReps (Company, SalesPerson, IDNum, Location) AS
          SELECT-from-question-3
          ORDER BY Company;
       -- Alternatively the new column names could come from the SELECT
       -- if we specified column aliases as in question 1.  Eg,
       CREATE VIEW CompanyReps
          SELECT-from-question-1;

   I was looking for the ORDER BY clause, but on reading the original phrasing of the question (now changed) it could have been interpreted as giving the same behavior as the CompanyReps table, which is inherently unsorted as are all tables.
9. (2 points) What is the command that your boss will use to do the query using this new view?
   Answer:

       SELECT *
         FROM CompanyReps;
       -- If you didn't add the ORDER BY clause when building the view,
       -- you need it here.

       SELECT *
         FROM CompanyReps
         ORDER BY Company;

10. (2 points) It has become necessary to break Salesreps.name into two fields, Fname and Lname. Add these columns to the Salesreps table.
    Answer:

        ALTER TABLE Salesreps
           ADD Fname VARCHAR(15);
        ALTER TABLE Salesreps
           ADD Lname VARCHAR(15);

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.
    Answer:

        -- (a)
        ALTER TABLE Salesreps
           DROP name;

        -- (b) Because the view CompanyReps exists
        --     RESTRICT will cause the drop to fail (no column drop).
        --     CASCADE will drop the column AND the CompanyReps view.