Heather Sweeney is an interior designer who specializes in home kitchen design. She offe...

Heather Sweeney is an interior designer who specializes in home kitchen design. She offers a variety of seminars at home shows, kitchen and appliance stores, and other public locations. The seminars are free; she offers them as a way of building her customer base. She earns revenue by selling books and videos that instruct people on kitchen design. She also offers custom-design consulting services.

After someone attends a seminar, Heather wants to leave no stone unturned in attempting to sell that person one of her products or services. She would therefore like to develop a database to keep track of customers, the seminars they have attended, the contacts she has made with them, and the purchases they have made. She wants to use this database to continue to contact her customers and offer them products and services, including via a Web application that allows customers to create an account and purchase items online.

We use the task of designing a database for Heather Sweeney Designs (HSD) as an example for our discussion of developing first the HSD data model in Chapter 4 (pages 262–270) and then the HSD database design in Chapter 5 (pages 310–317). Although you will study the HSD database development in detail in these chapters, you do not need to know that material to answer the following questions. Here we will take that final database design and actually implement it in a database using the SQL techniques that you learned in this chapter.

For reference, the SQL statements shown here are built from the HSD database design in Figure 5-27, the column specifications in Figure 5-26, and the referential integrity constraint specifications detailed in Figure 5-28.

Figure 3-27 shows the tables in the Heather Sweeney Designs database as they appear in the Microsoft Access 2013 Relationships view. This is similar to the view of the WPC database tables shown in Figure 3-1, and illustrates the tables in the HSD database and the relationships between them.

The SQL statements to create the Heather Sweeney Designs (HSD) database are shown in Figure 3-28 in SQL Server syntax. The SQL statements to populate the HSD database are shown in Figure 3-29, again in SQL Server syntax. Write SQL statements and answer questions for this database as follows:

A. Create a database named HSD in your DBMS.

B. Write an SQL script based on Figure 3-28 to create the tables and relationships for the HSD database. Save this script, and then execute the script to create the HSD tables.

C. Write an SQL script based on Figure 3-29 to insert the data for the HSD database. Save this script, and then execute the script to populate the HSD tables.

¦ Note: For your answers to parts D through O, you should create an SQL script to save and store your SQL statements. You can use one script to contain all the necessary statements. You can also include your answer to part P, but be sure to put it in

comment marks so that it is interpreted as a comment by the DBMS and cannot actually be run!

D. Write SQL statements to list all columns for all tables.

E. Write an SQL statement to list LastName, FirstName, and Phone for all customers that live in Dallas.

F. Write an SQL statement to list LastName, FirstName, and Phone for all customers that live in Dallas and have a LastName that begins with the letter T.

G. Write an SQL statement to list the INVOICE.InvoiceNumber for sales that include the Heather Sweeney Seminar Live in Dallas on 25-OCT-13 video. Use a subquery. (Hint: The correct solution uses three tables in the query because the question asks for INVOICE.InvoiceNumber. Otherwise, there is a possible solution with only two tables in the query.)

H. Answer part G but use a join in JOIN ON syntax. (Hint: The correct solution uses three tables in the query because the question asks for INVOICE.InvoiceNumber. Otherwise, there is a possible solution with only two tables in the query.)

I. Write an SQL statement to list the FirstName, LastName and Phone of customers (list each name only once) who have attended the Kitchen on a Big D Budget seminar.

J. Write an SQL statement to list the FirstName, LastName, Phone, ProductNumber, and Description names for customers (list each combination of name and product only

once) who have purchased a video product. Sort the results by LastName in descending order, then by FirstName in descending order, and then by ProductNumber in descending order. (Hint: Video products have a ProductNumber that starts with VK.)

K. Write an SQL statement to show all Heather Sweeney Designs seminars and the customers that attended them. The output from this statement should include any seminars that do not have any customers shown as attending them. The SQL statement output should list SeminarID, SeminarDate, Location, SeminarTitle, CustomerID, LastName, and FirstName. (Hint: Use JOIN ON syntax.)

L. Write an SQL statement to show all customers and the products that they have purchased. The output from this statement should include any products that have not been purchased by any customer. The SQL statement output should list CustomerID, LastName, FirstName, InvoiceNumber, ProductNumber, ProductType, and ProductDescription. (Hint: Use JOIN ON syntax.)

M. Write an SQL statement to show the sum of Subtotal (this is the money earned by HSD on products sold exclusive of shipping costs and taxes) for INVOICE as SumOfSubTotal.

N. Write an SQL statement to show the average of Subtotal (this is the money earned by HSD on products sold exclusive of shipping costs and taxes) for INVOICE as AverageOfSubTotal.

O. Write an SQL statement to show both the sum and the average of Subtotal (this is the money earned by HSD on products sold exclusive of shipping costs and taxes) for INVOICE as SumOfSubTotal and AverageOfSubTotal respectively.

P. Write an SQL statement to modify PRODUCT UnitPrice for ProductNumber VK004 to $34.95 instead of the current UnitPrice of $24.95.

Q. Write an SQL statement to undo the UnitPrice modification in part P.

R. Do not run your answer to the following question in your actual database! Write the fewest number of DELETE statements possible to remove all the data in your database but leave the table structures intact.