Database Storage & Design Chapter 3 Three Structured Query Language Figure Shows The Column Characteristics For Computerassignment

Chapter Three – Structured Query Language

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

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

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

SELECT * FROM CONTACT;

Chapter Three – Structured Query Language

SELECT * FROM CUSTOMER;

SELECT * FROM INVOICE;

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

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