Database Storage & Design Appendix E Appendix Advanced Sql For Microsoft Access Select From Viewpetcount Order Pettype For

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

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

• 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

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

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

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

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

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