Database Storage & Design Chapter 3 Three Structured Query Language Create Table Enrollment Customernumber Int Not Null

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

/***** 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

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

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

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

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

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

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(