Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Chapter Three – Structured Query Language
CREATE TABLE ENROLLMENT(
CustomerNumber Int NOT NULL,
CourseNumber Int NOT NULL,
);
For MySQL:
MySQL uses the AUTO_INCREMENT keyword to implement surrogate keys. By default,
AUTO_INCREMENT starts at 1 and increments by 1. Although the increment cannot be changed,
the starting value can be reset using an ALTER command.
CREATE TABLE CUSTOMER(
CustomerNumber Int NOT NULL AUTO_INCREMENT,
CustomerLastName Char(25) NOT NULL,
CustomerFirstName Char(25) NOT NULL,
Phone Char(12) NOT NULL,
CONSTRAINT CUSTOMER_PK PRIMARY KEY(CustomerNumber)
);
Chapter Three – Structured Query Language
3.53 Populate the tables with the data in Figure 1-12.
For Access, Oracle Database, and MySQL:
See the solutions to Review Question 3.7 for comments on modifying the data shown below for
SQL Server 2016 for data inserts in Microsoft Access, Oracle Database, and MySQL.
For SQL Server:
/***** CUSTOMER DATA **********************************************/
INSERT INTO CUSTOMER VALUES(
'Johnson', 'Ariel','206-567-1234');
SELECT * FROM CUSTOMER;
/***** COURSE DATA ************************************************/
INSERT INTO COURSE VALUES(
'Adv Pastels', '01-OCT-17', 500.00);
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 43 of 154
/***** ENROLLMENT DATA ********************************************/
INSERT INTO ENROLLMENT VALUES(1, 1, 250.00);
INSERT INTO ENROLLMENT VALUES(1, 3, 350.00);
SELECT * FROM ENROLLMENT;
3.54 Write and run an SQL query to list all occurrences of Adv Pastels in the COURSE
table. Include all associated data for each occurrence of the class.
SELECT *
3.55 Write and run an SQL query to list all students and courses they are registered for.
Include, in this order, CustomerNumber, CustomerLastName, CustomerFirstName,
Phone, CourseNumber, and AmountPaid.
For Microsoft Access:
Chapter Three – Structured Query Language
Note the use of the keyword INNER and the parentheses to group the INNER JOIN
statements.
SELECT CU.CustomerNumber,
CustomerLastName, CustomerFirstName,
For SQL Server and MySQL:
SELECT CU.CustomerNumber, CustomerLastName,
For Oracle:
SELECT CU.CustomerNumber, CustomerLastName, CustomerFirstName,
3.56 Write and run an SQL query to list all students registered in Adv Pastels starting on
October 1, 2017. Include, in this order, the Course, CourseDate, Fee,
CustomerLastName, CustomerFirstName, and Phone.
For Microsoft Access:
Note the use of the keyword INNER and the parentheses to group the INNER JOIN
statements, and the use of the number symbols ( # ) to delineate the Course Date value.
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 45 of 154
SELECT Course, CourseDate, Fee,
CustomerLastName, CustomerFirstName, Phone
FROM (CUSTOMER AS CU INNER JOIN ENROLLMENT AS EN
ON CU.CustomerNumber=EN.CustomerNumber)
INNER JOIN COURSE AS CO
ON EN.CourseNumber = CO.CourseNumber
WHERE Course = 'Adv Pastels'
AND CourseDate = #10/1/2017#;
For SQL Server and MySQL:
For Oracle:
SELECT Course, CourseDate, Fee,
3.57 Write and run an SQL query to list all students registered in Adv Pastels starting on
October 1, 2017. Include in this order, Course, CourseDate, CustomerLastName,
CustomerFirstName, Phone, Fee, and AmountPaid. Use a join using JOIN ON
syntax. Can this query be run using one or more subqueries? If not, why not?
This query cannot be run using subqueries since its result requires data from all three of the
tables involved in the query.
For Microsoft Access:
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 46 of 154
ON EN.CourseNumber = CO.CourseNumber
WHERE Course = 'Adv Pastels'
AND CourseDate = #10/1/2017#;
For SQL Server and MySQL:
For Oracle:
SELECT Course, CourseDate,
CustomerLastName, CustomerFirstName,
The following exercises are intended for use with a DBMS other than Microsoft Access. If you
are using Microsoft Access, see the equivalent questions in the “Access Workbench Exercises”
that follow.
Note to Instructors: The following solutions are available in the IRC:
• SQL Server 2016:
Chapter Three – Structured Query Language
• DBC-e08-ODB-WP-SQL-Queries-CH03-Exercises.sql
• MySQL 5.7:
• DBC-e08-MySQL-WP-Create-Tables.sql
• DBC-e08-MySQL-WP-Insert-Data.sql
• DBC-e08-MySQL-WP-SQL-Queries-CH03-Exercises.sql
3.58 If you haven’t done so, create the WP database, tables, and relationships described
in this chapter, using the SQL DBMS of your choice. Be sure to populate the tables
with the data shown in Figure 3-2.
See Figure 3-7 for the SQL statements to create the database tables. See Figure 3-12 for the
SQL statements to populate the database.
/***** DEPARTMENT DATA ***********************************/
SELECT * FROM DEPARTMENT;
Chapter Three – Structured Query Language
/***** PROJECT DATA **************************************/
SELECT * FROM PROJECT;
/***** ASSIGMENT DATA *************************************/
SELECT * FROM ASSIGNMENT;
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 49 of 154
3.59 Using the SQL DBMS of your choice, create and run queries to answer the questions
in exercise AW.3.1.
Using [Access] SQL, create and run queries to answer the questions that follow. Save each
query using the query name format SQLQuery-AWE-3-1-## where the ## sign is replaced by
the letter designator of the question. For example, the first query will be saved as SQLQuery-
AWE-3-1-A.
A. What projects are in the PROJECT table? Show all information for each project.
Chapter Three – Structured Query Language
B. What are the ProjectID, ProjectName, StartDate, and EndDate values of projects
in the PROJECT table?
/***** Question B – SQL-Query-AWE-3-1-B.sql ***************/
SELECT ProjectID, ProjectName, StartDate, EndDate
FROM PROJECT;
C. What projects in the PROJECT table started before August 1, 2017? Show all
the information for each project.
/***** Question C – SQL-Query-AWE-3-1-C.sql ***************/
D. What projects in the PROJECT table have not been completed? Show all the
information for each project.
/***** Question D – SQL-Query-AWE-3-1-D.sql ***************/
SELECT *
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 51 of 154
E. Who are the employees assigned to each project? Show ProjectID,
EmployeeNumber, LastName, FirstName, and OfficePhone.
For SQL Server and MySQL:
For Oracle:
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 52 of 154
F. Who are the employees assigned to each project? Show ProjectID,
ProjectName, and Department. Show EmployeeNumber, LastName, FirstName,
and OfficePhone.
For SQL Server and MySQL:
/***** Question F – SQL-Query-AWE-3-1-F.sql *************/
SELECT P.ProjectID, ProjectName, P.Department,
Chapter Three – Structured Query Language
For Oracle:
/***** Question F – SQL-Query-AWE-3-1-F.sql *************/
Chapter Three – Structured Query Language
G. Who are the employees assigned to each project? Show ProjectID,
ProjectName, Department, and DepartmentPhone. Show EmployeeNumber,
LastName, FirstName, and OfficePhone. Sort by ProjectID in ascending order.
For SQL Server and MySQL:
/***** Question G – SQL-Query-AWE-3-1-G.sql *************/
SELECT P.ProjectID, ProjectName, D.DepartmentName,
DepartmentPhone, E.EmployeeNumber, LastName, FirstName,
OfficePhone
For Oracle:
/***** Question G – SQL-Query-AWE-3-1-G.sql *************/
SELECT P.ProjectID, ProjectName, D.DepartmentName,
Chapter Three – Structured Query Language
H. Who are the employees assigned to projects run by the Sales and Marketing
Department? Show ProjectID, ProjectName, Department, and DepartmentPhone.
Show EmployeeNumber, LastName, FirstName, and OfficePhone. Sort by
ProjectID in ascending order.
For SQL Server and MySQL:
SELECT P.ProjectID, ProjectName, D.DepartmentName,
DepartmentPhone, E.EmployeeNumber, LastName, FirstName,
Chapter Three – Structured Query Language
For Oracle:
SELECT P.ProjectID, ProjectName, D.DepartmentName,
I. How many projects are being run by the Sales and Marketing Department? Be
sure to assign an appropriate column name to the computed results.
/***** Question I – SQL-Query-AWE-3-1-I.sql **************/
J. What is the total MaxHours of projects being run by the Sales and Marketing
Department? Be sure to assign an appropriate column name to the computed
results.
/***** Question J – SQL-Query-AWE-3-1-J.sql **************/
SELECT SUM(MaxHours) AS TotalMaxHoursForMKTGDeptProjects
K. What is the average MaxHours of projects being run by the Sales and Marketing
Department? Be sure to assign an appropriate column name to the computed
results.
Chapter Three – Structured Query Language
© 2018 Pearson Education, Inc. Page 57 of 154
L. How many projects are being run by each department? Be sure to display each
DepartmentName and to assign an appropriate column name to the computed
results.
/***** Question L – SQL-Query-AWE-3-1-L.sql **************/
SELECT Department, COUNT(*) AS NumberOfDeptProjects
FROM PROJECT
3.60 Using the SQL DBMS of your choice, complete steps A through E in exercise AW.3.3,
but exclude step F.
WP has decided to keep track of computers used by the employees. In order to do this,
two new tables will be added to the database. The schema for these tables, as related
to the existing EMPLOYEE table, is (note that we are purposely excluding the recursive
relationship in EMPLOYEE at this time):
EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Position, Supervisor,
OfficePhone, EmailAddress)
The referential integrity constraints are:
EmployeeNumber is a surrogate key and never changes. Employee records are never
deleted from the database. SerialNumber is not a surrogate key because it is not
generated by the database. However, a computer’s SerialNumber never changes, and,
therefore, there is no need to cascade updates. When a computer is at its end of life,
Chapter Three – Structured Query Language
the record in COMPUTER for that computer and all associated records in
COMPUTER_ASSIGNMENT are deleted from the database.
The solutions presented for this question (parts A-D) are for Microsoft SQL Server. Solution
files for Microsoft Access, Oracle Database XE, and MySQL can be found in the WP IRC files
referenced above. Note that the major differences are surrogate key management, date
formats, and the lack of UPDATE CASCADE in Oracle. More details are available in
Appendices A, B, C, H, and the solutions to questions 3.7-3.10.
A. Figure 3-32 shows the column characteristics for the WP COMPUTER table.
Using the column characteristics, use Access SQL to create the COMPUTER
table and its associated constraints in the WP.accdb database. Are there any
table characteristics that cannot be created in SQL? If so, what are they? Use
the Access GUI to finish setting table characteristics, if necessary.
CREATE TABLE COMPUTER(
SerialNumber Int NOT NULL,
Make Char(12) NOT NULL,
Model Char(24) NOT NULL,
ProcessorType Char(24) NULL,
B. The data for the COMPUTER table are in Figure 3-33. Use Access SQL to enter these data into
your COMPUTER table.
Chapter Three – Structured Query Language
INSERT INTO COMPUTER VALUES(
9871234, 'HP', 'ProDesk 600 G1', 'Intel i5-4690', 3.50,
'16.0 GBytes', '1.0 TBytes');
INSERT INTO COMPUTER VALUES(
9871235, 'HP', 'ProDesk 600 G1', 'Intel i5-4690', 3.50,
'16.0 GBytes', '1.0 TBytes');
INSERT INTO COMPUTER VALUES(
9871236, 'HP', 'ProDesk 600 G1', 'Intel i5-4690', 3.50,
Chapter Three – Structured Query Language
INSERT INTO COMPUTER VALUES(
9871242, 'HP', 'ProDesk 600 G1', 'Intel i5-4690', 3.50,
'16.0 GBytes', '1.0 TBytes');
INSERT INTO COMPUTER VALUES(
9871243, 'HP', 'ProDesk 600 G1', 'Intel i5-4690', 3.50,
'16.0 GBytes', '1.0 TBytes');
INSERT INTO COMPUTER VALUES(
