Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Appendix E – Advanced SQL
For Microsoft Access:
E.33 Code an SQL statement to create a view named DogBreedCountView that shows each
breed of dog and the number of each breed in the database.
For Microsoft Access:
E.34 Code an SQL statement that displays the data in DogBreedCountView, sorted
alphabetically by PetBreed.
For Microsoft Access:
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 22 of 107
SELECT *
FROM DogBreedCountView
ORDER BY PetBreed;
E.35 Write a user-defined function named FirstNameFirst that concatenates the
OwnerLastName and OwnerFirstName into a single value named OwnerName and
displays, in order, the OwnerFirstName and OwnerLastName with a single space between
them (hint: Downs and Marsha would be combined to read Marsha Downs).
For Microsoft Access:
Appendix E – Advanced SQL
/****** Test Function *********************************************************/
E.36 Code an SQL statement to create a view named PetOwnerFirstNameFirstView that
shows PetID, PetName, PetType, OwnerID, OwnerLastName and OwnerFirstName
concatenated using the FirstNameFirst user-defined function and displayed as
PetOwnerName, OwnerPhone, and OwnerEmail.
For Microsoft SQL Server (Oracle and MySQL use slightly different syntax for naming and calling
functions; see the Oracle solutions file for details):
E.37 Code an SQL statement that displays the data in PetOwnerFirstNameFirstView, sorted
alphabetically by PetOwnerName and PetName.
Appendix E – Advanced SQL
E.38 Describe the SQL/PSM component of the SQL standard. What are PL/SQL and T-SQL?
What is the MySQL equivalent?
E.39 What is a user-defined function?
A user-defined function (also known as a stored function) is a stored set of SQL statements,
embedded in SQL/PSM, that:
E.40 What is a trigger?
Appendix E – Advanced SQL
INSTEAD OF Insert INSTEAD OF Update INSTEAD OF Delete
AFTER Insert AFTER Update AFTER Delete
E.42 What are stored procedures? How do they differ from triggers?
E.43 Summarize the key advantages of stored procedures.
ANSWERS TO EXERCISES
If you haven’t created the Art Course database described in Chapter 3, create it now (by
completing exercises 3.52 and 3.53). Use the Art Course database to answer exercises
E.44 through E.54.
For the database solutions for the Review Questions about the Art-Course-Database,
see the IRC files supplied and use:
• Microsoft Access:
• DBC-e08-Art-Course-Database-CH03-AppE.accdb
• SQL Server 2016:
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 26 of 107
• DBC-e078ODB-Art-Course-Database-Create-Tables.sql
• DBC-e08-ODB-Art-Course-Database-Insert-Data.sql
• DBC-e08-ODB-Art-Course-Database-SQL-AppE.sql
• MySQL 5.7:
• DBC-e08-MySQL-Art-Course-Database-Create-Tables.sql
• DBC-e08-MySQL-Art-Course-Database-Insert-Data.sql
• DBC-e08-MySQL-Art-Course-Database-SQL-AppE.sql
E.44 Code an SQL statement to create a view named CourseView that shows unique course
numbers listed together with the corresponding course names and fees.
Note that DISTINCT is not needed since CourseNumber is a key.
For Microsoft Access:
Create and save the following Access query as viewCourse.
E.45 Code an SQL statement that displays the data in CourseView, sorted by CourseNumber.
For Microsoft Access:
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 27 of 107
SELECT *
FROM CourseView
ORDER BY CourseNumber;
E.46 Code an SQL statement to create a view named CourseEnrollmentView that shows
CourseNumber, Course, CourseDate, CustomerNumber, CustomerLastName,
CustomerFirstName, and Phone.
For Microsoft Access:
Create and save the following Access query as viewCourseEnrollment.
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 28 of 107
E.47 Code an SQL statement that displays the data in CourseEnrollmentView for the
Advanced Pastels course starting 10/01/17. Sort the data alphabetically by
CustomerLastName.
Note that the solutions below assume Oracle’s standard default date format. If that format has
been changed by your DBA, then you will need to adjust the date formats accordingly. See
Appendix B for more on Oracle date formats.
For Microsoft Access:
E.48 Code an SQL statement that displays the data in CourseEnrollmentView for the
Beginning Oils course starting 10/15/17. Sort the data alphabetically by
CustomerLastName.
Appendix E – Advanced SQL
Note that the solutions below assume Oracle’s standard default date format. If that format has
been changed by your DBA, then you will need to adjust the date formats accordingly. See
Appendix B for more on Oracle date formats.
For Microsoft Access:
E.49 Code an SQL statement to create a view named CourseFeeOwedView that shows
CourseNumber, Course, CourseDate, CustomerNumber, CustomerLastName,
CustomerFirstName, Phone, Fee, AmountPaid, and the calculated column (Fee –
AmountPaid), renamed as AmountOwed.
For Microsoft Access:
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 30 of 107
CREATE VIEW CourseFeeOwedView AS
SELECT CO.CourseNumber, Course, CourseDate,
CU.CustomerNumber,
CustomerLastName, CustomerFirstName, Phone,
Fee, AmountPaid, (Fee- AmountPaid) AS AmountOwed
FROM COURSE AS CO JOIN ENROLLMENT AS E
ON CO.CourseNumber = E.CourseNumber
JOIN CUSTOMER AS CU
ON E.CustomerNumber = CU.CustomerNumber;
For Oracle Database:
E.50 Code an SQL statement that displays the data in CourseFeeOwedView, sorted
alphabetically by CustomerLastName.
For Microsoft Access:
Appendix E – Advanced SQL
E.51 Code an SQL statement that displays the data in CourseFeeOwedView, sorted
alphabetically by CustomerLastName for any customer who still owes money for a course
fee.
For Microsoft Access:
Appendix E – Advanced SQL
CREATE FUNCTION dbo.FirstNameFirst
-- These are the input parameters
E.53 Code an SQL statement to create a view named CourseEnrollmentFirstNameFirstView
that shows CourseNumber, Course, CourseDate, CustomerNumber, CustomerLastName
and CustomerFirstName concatenated using the FirstNameFirst user-defined function and
displayed as CustomerName, and Phone.
For SQL Server and MySQL:
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 33 of 107
CREATE VIEW CourseEnrollFirstNameFirstView AS
SELECT CR.CourseNumber, Course, CourseDate,
C.CustomerNumber,
dbo.FirstNameFirst(CustomerFirstName, CustomerLastName)
AS CustomerName, Phone
FROM CUSTOMER C JOIN ENROLLMENT E
ON C.CustomerNumber = E.CustomerNumber
JOIN COURSE CR
ON E.CourseNumber = CR.CourseNumber;
E.54 Code an SQL statement that displays the data in CourseEnrollmentFirstNameFirstView,
sorted alphabetically by CustomerName and CourseNumber.
For exercises E.55 through E.59 we will use the WP database as described in Chapter 3
and this appendix.
Appendix E – Advanced SQL
• MICROSOFT Access:
• DBC-e08-WP-CH03-AppE.accdb
• SQL Server 2016:
Appendix E – Advanced SQL
E.55 Insert the data shown in Figure E-36 into the PRODUCTION_ITEM table. Note that if
you executed the merge statement in SQL-Merge-AppE-01 earlier in this appendix, then
the first two rows will already be there, but you will need to change their QuantityOnHand
values to 50.
Assuming SQL-MERGE-AppE-01 has not been run:
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:
Appendix E – Advanced SQL
Assuming SQL-MERGE-AppE-01 has been run:
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:
UPDATE PRODUCTION_ITEM
SET QuantityOnHand = 50
E.56 Create a new table named CATALOG_SKU_2017 based on the column characteristics
shown in Figure E-2. Include a referential integrity constraint to the PRODUCTION_ITEM
table.
Appendix E – Advanced SQL
© 2018 Pearson Education, Inc. Page 37 of 107
The solutions presented for this question are for Microsoft SQL Server. Solution files for
Microsoft Access, Oracle Database XE, and MySQL can be found in the IRC files referenced
above. Note that the major differences are surrogate key management and date formats. More
details are available in Appendices A, B, C, H, and the solutions to questions 3.7-3.10.
CREATE TABLE CATALOG_SKU_2017(
);
E.57 Populate the CATALOG_SKU_2017 table with the data in Figure E-37.
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:
Appendix E – Advanced SQL
INSERT INTO CATALOG_SKU_2017 VALUES(
170203001, 'Our popular Bravo III model in black.', 15,
'01-JAN-17');
E.58 Create and run an SQL SELECT statement that answers the question: “What products
were available for sale (by either catalog or Web site) in 2016 and 2017?” Sort your result
by SKU.
SELECT SKU, CatalogDescription, CatalogPage, DateOnWebSite
FROM CATALOG_SKU_2016
Appendix E – Advanced SQL
E.59 Create and run an SQL SELECT statement that answers the question: “What products
were available for sale (by either catalog or Web site) in 2015, 2016, and 2017?” Sort your
result by SKU.
SELECT SKU, CatalogDescription, CatalogPage, DateOnWebSite
FROM CATALOG_SKU_2015
Appendix E – Advanced SQL
ANSWERS TO HEATHER SWEENEY DESIGNS CASE QUESTIONS
These questions are based on Chapter 3’s Heather Sweeney Designs Case Questions.
Base your answers to the questions that follow on the HSD database, as described
there. If possible, run your SQL statements in an actual DBMS to validate your work.
For the database solutions for the Project Questions about the Heather Sweeney
Designs Case, see the IRC files supplied and use:
• Microsoft Access 2016:
• DBC-e08-HSD.accdb
• SQL Server 2016:
• DBC-e08-MSSQL-HSD-Create-Tables.sql
• DBC-e08-MSSQL-HSD-Insert-Data.sql
• DBC-e08-MSSQL-HSD-SQL-AppE.sql
• MySQL 5.7:
• DBC-e08-MySQL-HSD-Create-Tables.sql
• DBC-e08-MySQL-HSD-Insert-Data.sql
• DBC-e08-MySQL-HSD-SQL-AppE.sql
