Database Storage & Design Chapter 3 Three Structured Query Language Write Sql Statement List The Invoiceinvoicenumber For

Chapter Three – Structured Query Language

G. Write an SQL statement to list the INVOICE.InvoiceNumber for sales that include the

Heather Sweeney Seminar Live in Dallas on 25-OCT-15 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.)

— Two table solution – starting with LINE_ITEM

SELECT InvoiceNumber

FROM LINE_ITEM

— Three table solution – starting with INVOICE

SELECT InvoiceNumber

FROM INVOICE

WHERE InvoiceNumber IN

Chapter Three – Structured Query Language

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.)

For SQL Server and MySQL:

— Two table solution

SELECT LI.InvoiceNumber

FROM LINE_ITEM AS LI JOIN PRODUCT AS P

— Three table solution

SELECT I.InvoiceNumber

FROM INVOICE AS I JOIN LINE_ITEM AS LI

ON I.InvoiceNumber = LI.InvoiceNumber

JOIN PRODUCT AS P

For Oracle:

— Two table solution

SELECT LI.InvoiceNumber

FROM LINE_ITEM LI JOIN PRODUCT P

— Three table solution

SELECT I.InvoiceNumber

FROM INVOICE I JOIN LINE_ITEM LI

ON I.InvoiceNumber = LI.InvoiceNumber

For Access:

— Two table solution

SELECT LI.InvoiceNumber

Chapter Three – Structured Query Language

— Three table solution

SELECT I.InvoiceNumber

FROM (INVOICE AS I INNER JOIN LINE_ITEM AS LI

ON I.InvoiceNumber = LI.InvoiceNumber)

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.

For SQL Server and MySQL:

For Oracle:

For Access:

Chapter Three – Structured Query Language

J. Write an SQL statement to list the FirstName, LastName, Phone, ProductNumber and

ProductDescription for customers who have purchased a video product (list each

combination of name and product only once). 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.)

For SQL Server and MySQL:

SELECT DISTINCT FirstName, LastName, Phone,

P.ProductNumber, P.ProductDescription

FROM CUSTOMER AS C JOIN INVOICE AS I

For Oracle:

SELECT DISTINCT FirstName, LastName, Phone,

P.ProductNumber, P.ProductDescription

FROM CUSTOMER C JOIN INVOICE I

For Access:

Chapter Three – Structured Query Language

SELECT DISTINCT FirstName, LastName, Phone,

P.ProductNumber, P.ProductDescription

FROM ((CUSTOMER AS C INNER JOIN INVOICE AS I

ON C.CustomerID = I.CustomerID)

K. Write an SQL statement to show all Heather Sweeney Designs seminars and the

customers who 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).

For SQL Server and MySQL:

SELECT S.SeminarID, S.SeminarDate, S.Location, S.SeminarTitle,

SC.CustomerID, C.LastName, C.FirstName

For Access:

Chapter Three – Structured Query Language

Answer omitted; Access is unable to determine the proper join order in this situation

and the solution in this situation is to use a second stored query (view), which is not

covered in this chapter.

For Oracle:

SELECT S.SeminarID, S.SeminarDate, S.Location, S.SeminarTitle,

SC.CustomerID, C.LastName, C.FirstName

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).

For SQL Server and MySQL:

SELECT C.CustomerID, C.LastName, C.FirstName, I.InvoiceNumber,

LI.ProductNumber, P.ProductType, P.ProductDescription

FROM CUSTOMER as C JOIN INVOICE as I

For Oracle:

Chapter Three – Structured Query Language

SELECT C.CustomerID, C.LastName, C.FirstName, I.InvoiceNumber,

LI.ProductNumber, P.ProductType, P.ProductDescription

FROM CUSTOMER C JOIN INVOICE I

For Access:

Answer omitted; Access is unable to determine the proper join order in this situation

and the solution in this situation is to use a second stored query (view), which is not

covered in this chapter.

Chapter Three – Structured Query Language

M. Write an SQL statement to show the sum of SubTotal (this is the money earned by HSD

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.

UPDATE PRODUCT

SET UnitPrice = 34.95

Chapter Three – Structured Query Language

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

UPDATE PRODUCT

SET UnitPrice = 24.95

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.

This answer is based on CASCADING DELETES. When DELETEs cascade, this can clear data from

tables with foreign keys based on the deleted primary key. In the HSD database, there is a

CASCADING DELETE from INVOICE to LINE_ITEM, so we don’t need to delete any LINE_ITEMs

explicitly.

Chapter Three – Structured Query Language

 ANSWERS TO GARDEN GLORY PROJECT QUESTIONS

Assume that Garden Glory designs a database with the following tables.

OWNER (OwnerID, OwnerName, OwnerEmailAddress, OwnerType)

The referential integrity constraints are:

OwnerID in OWNED_PROPERTY must exist in OwnerID in OWNER

Assume that OwnerID in OWNER, PropertyID in OWNED_PROPERTY, ServiceID in

GG_SERVICE, EmployeeID in EMPLOYEE, and PropertyServiceID in

PROPERTY_SERVICE are surrogate keys with values as follows:

OwnerID Start at 1 Increment by 1

PropertyID Start at 1 Increment by 1

Sample data are shown in Figures 3–39, 3-40, 3-41, 3-42, and 3-43. OwnerType is either

Individual or Corporation. PropertyType is one of Office, Apartments, or Private

Residence. ExperienceLevel is one of Junior, Senior, or Master. These tables,

referential integrity constraints, and data are used as the basis for the SQL statements

Chapter Three – Structured Query Language

Note to Instructor: For the database solutions for the Project Questions about

GARDEN_GLORY, see the IRC files supplied and use:

• Microsoft Access:

• DBC–08-GG.accdb

• SQL Server 2016:

• DBC–e08-MSSQL-GG-Create-Tables.sql

• DBC–e08-MSSQL-GG-Insert-Data.sql

• DBC–e08-MSSQL-GG-SQL-Queries-CH03.sql

• Oracle Database 11g Release 2:

• DBC–e08-ODB-GG-Create-Tables.sql

• DBC–e08-ODB-GG-Insert-Data.sql

• DBC–e08-ODB-GG-SQL-Queries-CH03.sql

• MySQL 5.7:

• DBC–e08-MySQL-GG-Create-Tables.sql

• DBC–e08-MySQL-GG-Insert-Data.sql

• DBC–e08-MySQL-GG-SQL-Queries-CH03.sql

Write SQL statements and answer questions for this database as follows:

A. Write CREATE TABLE statements for each of these tables.

B. Write foreign key constraints for the relationships in each of these tables. Make your own

assumptions regarding cascading updates and deletions and justify those assumptions.

(Hint: You can combine the SQL for your answers to questions A and B)

Assuming the tables were created without any foreign key constraints, this could be done in all

four systems with ALTER TABLE statements as follows:

Chapter Three – Structured Query Language

ADD CONSTRAINT PROPERTY_OWNER_FK FOREIGN KEY (OwnerID)

REFERENCES OWNER(OwnerID);

Note that the ON DELETE and ON UPDATE clauses are optional when the default behavior of NO

ACTION is desired.

However, the discussion and figures in the text show this being done using the CREATE TABLE

statement. Foreign key constraints would be included the CREATE TABLE statements as follows:

For SQL Server:

CREATE TABLE OWNER(

OwnerID Int NOT NULL IDENTITY(1,1),

OwnerName Char(50) NOT NULL,

);

CREATE TABLE OWNED_PROPERTY(

PropertyID Int NOT NULL IDENTITY(1,1),

PropertyName VarChar(50) NOT NULL,

PropertyType VarChar(50) NOT NULL,

);

CREATE TABLE EMPLOYEE(

EmployeeID Int NOT NULL IDENTITY(1,1),

LastName Char(25) NOT NULL,

Chapter Three – Structured Query Language

CREATE TABLE GG_SERVICE(

CREATE TABLE PROPERTY_SERVICE(

PropertyServiceID Int NOT NULL IDENTITY (1,1),

PropertyID Int NOT NULL,

CONSTRAINT PROP_SERVICE_SERVICE_FK FOREIGN KEY (ServiceID)

REFERENCES GG_SERVICE(ServiceID)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

Note that there are no cascading deletions because we don’t want to allow for inadvertently

removing property, service, owner, or employee records. Since all the referenced primary keys

are surrogate keys (and thus should never be updated), we do not need to cascade updates.

For Microsoft Access:

Access SQL does not support the (m, n) extension of the Numeric data type. Create

PROPERTY_SERVICE.HoursWorked and GG_SERVICE.CostPerHour as Numeric, and then set the

column properties in the GUI. CHECK constraints must be added via the GUI validation rules.

For Oracle Database:

Chapter Three – Structured Query Language

and set starting values and increment values. Therefore, the definitions of the surrogate keys

should be written as:

CREATE TABLE OWNER(

CREATE TABLE OWNED_PROPERTY(

PropertyID Int NOT NULL,

.

);

CREATE SEQUENCE seqOPID INCREMENT BY 1 START WITH 1;

CREATE TABLE GG_SERVICE(

ServiceID Int NOT NULL,

.

);

CREATE SEQUENCE seqGGSID INCREMENT BY 1 START WITH 1;

For MySQL:

The SQL CREATE TABLE commands shown for SQL Server 2016 will also work for MySQL with

only one modification. MySQL uses AUTO_INCREMENT to set surrogate keys with a starting

value of 1 and an increment of 1. Therefore, the definitions of the surrogate keys should be

written as:

Chapter Three – Structured Query Language

CREATE TABLE OWNER(

OwnerID Int NOT NULL AUTO_INCREMENT,

.

);

CREATE TABLE EMPLOYEE(

EmployeeID Int NOT NULL AUTO_INCREMENT,

.

);

CREATE TABLE PROPERTY_SERVICE(

PropertyServiceID Int NOT NULL AUTO_INCREMENT,

.

);

C. Write SQL statements to insert the data into each of the five Garden Glory database

tables. Assume that any surrogate key value will be supplied by the DBMS. Use the

data in Figures 3-39, 3-40, 3-41, 3-42, and 3-43.

Chapter Three – Structured Query Language

Thus, for example, SQL Server would use the SQL command:

Whereas Microsoft Access and MySQL would use:

Oracle would use:

Finally, MySQL uses a date format of ‘YYYY-MM–DD’ as in ’2012-02–27′ and Oracle’s date formats

are numerous and depend on the default set by the DBA. See Appendix B for more information

on Oracle Database use of sequences and date functions.

The IRC files listed above contain the complete solutions for MySQL, Access, and Oracle.

The tables need to be populated in an order consistent with the referential integrity constraints.

Specifically, OWNER needs to be populated before OWNED_PROPERTY. Also,

OWNED_PROPERTY, GG_SERVICE, and EMPLOYEE need to be populated before

PROPERTY_SERVICE. Therefore, we will enter data into the tables in the following order (other

orders are possible):

OWNER No foreign keys

EMPLOYEE No foreign keys

/***** OWNER DATA ***********************************************************/

INSERT INTO OWNER VALUES(

‘Mary Jones’, ‘Mary.Jones@somewhere.com’, ‘Individual’);

INSERT INTO OWNER VALUES(

Chapter Three – Structured Query Language

/***** EMPLOYEE DATA ****************************************************/

INSERT INTO EMPLOYEE VALUES(

‘Smith’, ‘Sam’, ‘206-254-1234’, ‘Master’);

INSERT INTO EMPLOYEE VALUES(

‘Evanston’, ‘John’,’206-254-2345′, ‘Senior’);

INSERT INTO EMPLOYEE VALUES(

/***** GG_SERVICE DATA ***************************************************/

INSERT INTO GG_SERVICE VALUES(‘Mow Lawn’, 25.00);

INSERT INTO GG_SERVICE VALUES(‘Plant Annuals’, 25.00);

INSERT INTO GG_SERVICE VALUES(‘Weed Garden’, 30.00);

Chapter Three – Structured Query Language

/***** OWNED_PROPERTY DATA ************************************************/

INSERT INTO OWNED_PROPERTY VALUES(

‘Eastlake Building’, ‘Office’, ‘123 Eastlake’,

‘Seattle’, ‘WA’, ‘98119’, 2);

INSERT INTO OWNED_PROPERTY VALUES(

‘Elm St Apts’, ‘Apartments’, ‘4 East Elm’,

‘Lynwood’, ‘WA’, ‘98223’, 1);

INSERT INTO OWNED_PROPERTY VALUES(

‘Jefferson Hill’, ‘Office’, ’42 West 7th St’,

‘Bellevue’, ‘WA’, ‘98007’, 2);

INSERT INTO OWNED_PROPERTY VALUES(

‘Lake View Apts’, ‘Apartments’, ‘1265 32nd Avenue’,

‘Redmond’, ‘WA’, ‘98052’, 3);

***** PROPERTY_SERVICE ****************************************************/

INSERT INTO PROPERTY_SERVICE VALUES(1, 2, ’05-MAY-17′, 1, 4.50);

INSERT INTO PROPERTY_SERVICE VALUES(3, 2, ’08-MAY-17′, 3, 4.50);

INSERT INTO PROPERTY_SERVICE VALUES(2, 1, ’08-MAY-17′, 2, 2.75);

INSERT INTO PROPERTY_SERVICE VALUES(6, 1, ’10-MAY-17′, 5, 2.50);

INSERT INTO PROPERTY_SERVICE VALUES(5, 4, ’12-MAY-17′, 4, 7.50);

Chapter Three – Structured Query Language

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

SELECT * FROM OWNER;

SELECT * FROM EMPLOYEE;

SELECT * FROM GG_SERVICE;