Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 61 of 154
C. Figure 3-34 shows the column characteristics for WP COMPUTER_ASSIGNMENT table. Using
the column characteristics, use Access SQL to create the COMPUTER_ASSIGNMENT table and
the associated constraints in the WP.accdb database. Are there any table or relationship settings
or characteristics that cannot be created in SQL? If so, what are they? Use the Access GUI to
finish setting table characteristics and relationship settings if necessary.
CREATE TABLE COMPUTER_ASSIGNMENT(
SerialNumber Int NOT NULL,
EmployeeNumber Int NOT NULL,
DateAssigned Date NOT NULL,
Chapter Three – Structured Query Language
REFERENCES EMPLOYEE(EmployeeNumber)
ON UPDATE NO ACTION
);
D. The data for the COMPUTER_ASSIGNMENT table are in Figure 3-35. Use Access SQL to enter
these data into your COMPUTER_ASSIGNMENT table.
Chapter Three – Structured Query Language
INSERT INTO COMPUTER_ASSIGNMENT VALUES(9871234, 12, '15-Sep-17',
'21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT VALUES(9871235, 13, '15-Sep-17',
'21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT VALUES(9871236, 14, '15-Sep-17',
'21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT VALUES(9871237, 15, '15-Sep-17',
'21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT VALUES(9871238, 6, '15-Sep-17',
'21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT VALUES(9871239, 7, '15-Sep-17',
'21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT VALUES(9871240, 8, '15-Sep-17',
'21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT VALUES(9871241, 9, '15-Sep-17',
'21-Oct-17');
DateAssigned) VALUES(6541010, 17, '21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber,
DateAssigned) VALUES(9871234, 1, '21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber,
DateAssigned) VALUES(9871235, 2, '21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber,
DateAssigned) VALUES(9871236, 3, '21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber,
DateAssigned) VALUES(9871237, 4, '21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber,
DateAssigned) VALUES(9871238, 5, '21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber,
DateAssigned) VALUES(9871239, 10, '21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber,
DateAssigned) VALUES(9871240, 11, '21-Oct-17');
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 64 of 154
INSERT INTO COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber,
DateAssigned) VALUES(9871241, 18, '21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber,
DateAssigned) VALUES(9871242, 19, '21-Oct-17');
INSERT INTO COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber,
DateAssigned) VALUES(9871243, 20, '21-Oct-17');
SELECT *
FROM COMPUTER_ASSIGNMENT
ORDER BY DateAssigned ASC, SerialNumber ASC;
E. Who is currently using which computer at WP? Create an appropriate SQL query to answer this
question. Show SerialNumber, Make, and Model. Show EmployeeID, LastName, FirstName,
Department, and OfficePhone. Sort first by Department and then by employee LastName. Save
this query using the query naming rules in exercise AW.3.1.
Chapter Three – Structured Query Language
For SQL Server and MySQL:
/***** Question E – SQL-Query-AWE-3-3-E.sql ************************/
SELECT C.SerialNumber, Make, Model, E.EmployeeNumber,
LastName, FirstName, Department, OfficePhone
For Oracle:
/***** Question E – SQL-Query-AWE-3-3-E.sql ************************/
SELECT C.SerialNumber, Make, Model, E.EmployeeNumber, LastName,
FirstName, Department, OfficePhone
Chapter Three – Structured Query Language
ANSWERS TO HEATHER SWEENEY DESIGNS CASE QUESTIONS
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
280–288) and then the HSD database design in Chapter 5 (pages 348–355). 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 for Heather Sweeney Designs (HSD) and actually implement it in a
database using the SQL techniques that you learned in this chapter.
Note to Instructors: For the database solutions for the Case Questions about HSD, see
the IRC files supplied and use:
• Microsoft Access:
• DBC-e08-HSD-CH03-AppE.accdb
• SQL Server 2016:
• DBC-e08-MSSQL-HSD-Create-Tables.sql
• DBC-e08-MSSQL-HSD-Insert-Data.sql
• DBC-e08-MSSQL-HSD-SQL-Queries-CH03.sql
Chapter Three – Structured Query Language
• Oracle Database Express Edition 11g Release 2:
• DBC-e08-ODB-HSD-Create-Tables.sql
• DBC-e08-ODB-HSD-Insert-Data.sql
• DBC-e08-ODB-HSD-SQL-Queries-CH03.sql
• MySQL 5.7:
• DBC-e08-MySQL-HSD-Create-Tables.sql
• DBC-e08-MySQL-HSD-Insert-Data.sql
• DBC-e08-MySQL-HSD-SQL-Queries-CH03.sql
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-37 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-38 to insert the data for the HSD database. Save
this script, and then execute the script to populate the HSD tables.
Chapter Three – Structured Query Language
Chapter Three – Structured Query Language
Chapter Three – Structured Query Language
Chapter Three – Structured Query Language
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 72 of 154
Chapter Three – Structured Query Language
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 74 of 154
Chapter Three – Structured Query Language
D. Write SQL statements to list all columns for all tables.
SELECT * FROM CONTACT;
Chapter Three – Structured Query Language
Chapter Three – Structured Query Language
SELECT * FROM CUSTOMER;
SELECT * FROM INVOICE;
Chapter Three – Structured Query Language
Chapter Three – Structured Query Language
SELECT * FROM PRODUCT;
For SQL Server, Access, and MySQL:
SELECT * FROM SEMINAR;
Note that Oracle time data is stored in a DATE type column (there is not a separate TIME
data type in Oracle). To actually display just the time value, the following SQL statement
is required:
SELECT SeminarID, SeminarDate, TO_CHAR(SeminarTime, 'hh24:mi') AS
SeminarTime, Location, SeminarTitle
FROM SEMINAR;
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 80 of 154
E. Write an SQL statement to list LastName, FirstName, and Phone for all customers who
live in Dallas.
SELECT LastName, FirstName, Phone
F. Write an SQL statement to list LastName, FirstName, and Phone for all customers who
live in Dallas and have a LastName that begins with the letter T.
For SQL Server, Oracle, and MySQL:
SELECT LastName, FirstName, Phone
For Microsoft Access:
SELECT LastName, FirstName, Phone
