Advanced SQL Chapter 8 Answers to Review Questions

Advanced SQL

NOTE

Several points are worth emphasizing:

· We have provided the SQL scripts for the chapters. These scripts are intended to facilitate the

flow of the material presented to the class. However, given the comments made by our

students, the scripts should not replace the manual typing of the SQL commands by students.

Some students learn SQL better when they have a chance to type their own commands and get

the feedback provided by their errors. We recommend that the students use their lab time to

practice the commands manually.

· In this chapter, most of the queries are executed in the Oracle RDBMS. This chapter uses

features that are restricted to client/server DBMSes. Such features are not commonly available

in a desktop DBMS such as Microsoft Access.

Answers to Review Questions

1. What is a CROSS JOIN? Give an example of its syntax.

2. What three join types are included in the OUTER JOIN classification?

3. Using tables named T1 and T2, write a query example for each of the three join types you

described in Question 10. Assume that T1 and T2 share a common column named C1.

4. What is a subquery, and what are its basic characteristics?

5. What are the three types of results a subquery can return?

6. What is a correlated subquery? Give an example.

7. Explain the difference between a regular subquery and a correlated subquery.

8. What does it mean to say that SQL operators are set-oriented?

9. The relational set operators UNION, INTERSECT, and MINUS work properly only if the

relations are union-compatible. What does union-compatible mean, and how would you check

for this condition?

10. What is the difference between UNION and UNION ALL? Write the syntax for each.

11. Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table

contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne

McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and

Mary Chen. Given that information, what is the query output for the UNION query? (List the

query output.)

12. Given the employee information in Question 11, what is the query output for the UNION ALL

query? (List the query output.)

13. Given the employee information in Question 11, what is the query output for the INTERSECT

query? (List the query output.)

14. Given the employee information in Question 1, what is the query output for the MINUS

query? (List the query output.)

15. Why does the order of the operands (tables) matter in a MINUS query but not in a UNION

query?

16. What MS Access/SQL Server function should you use to calculate the number of days between

the current date and January 25, 1999?

17. What Oracle function should you use to calculate the number of days between the current date

and January 25, 1999?

18. Suppose that a PRODUCT table contains two attributes, PROD_CODE and VEND_CODE.

Those two attributes have values of ABC, 125, DEF, 124, GHI, 124, and JKL, 123, respectively.

The VENDOR table contains a single attribute, VEND_CODE, with values 123, 124, 125, and

126, respectively. (The VEND_CODE attribute in the PRODUCT table is a foreign key to the

VEND_CODE in the VENDOR table.) Given that information, what would be the query

output for:

a. A UNION query based on these two tables?

b. A UNION ALL query based on these two tables?

c. An INTERSECT query based on these two tables?

d. A MINUS query based on these two tables?

19. What string function should you use to list the first three characters of a company’s

EMP_LNAME values? Give an example, using a table named EMPLOYEE.

20. What is an Oracle sequence? Write its syntax.

21. What is a trigger, and what is its purpose? Give an example.

22. What is a stored procedure, and why is it particularly useful? Give an example.

23. What is embedded SQL, and how is it used?

24. What is dynamic SQL, and how does it differ from static SQL?

Problem Solutions

Use the database tables in Figure P8.1 as the basis for problems 1-18.

Figure P8.1 Ch08_SimpleCo Database Tables

1. Create the tables. (Use the MS Access example shown in Figure P8.1 to see what table names

and attributes to use.)

3. Write the query that will generate a combined list of customers (from tables CUSTOMER and

CUSTOMER_2) that do not include the duplicate customer records. (Note that only the

customer named Juan Ortega shows up in both customer tables.)

4. Write the query that will generate a combined list of customers to include the duplicate

customer records.

5. Write the query that will show only the duplicate customer records.

6. Write the query that will generate only the records that are unique to the CUSTOMER_2

table.

7. Write the query to show the invoice number, the customer number, the customer name, the

invoice date, and the invoice amount for all customers with a customer balance of $1,000 or

more.

8. Write the query that will show the invoice number, the invoice amount, the average invoice

amount, and the difference between the average invoice amount and the actual invoice

amount.

9. Write the query that will write Oracle sequences to produce automatic customer number and

invoice number values. Start the customer numbers at 1000 and the invoice numbers at 5000.

10. Modify the CUSTOMER table to included two new attributes: CUST_DOB and CUST_AGE.

Customer 1000 was born on March 15, 1979, and customer 1001 was born on December 22,

1988.

11. Assuming you completed problem 10, write the query that will list the names and ages of your

customers.

NOTE

The correct age computation may be computed by

INT((DATE()-CUST_DOB)/365)

However, students have not (yet) seen the INT function at this point -- which is why we used

ROUND() function.

12. Assuming the CUSTOMER table contains a CUST_AGE attribute, write the query to update

the values in that attribute. Hint: Use the results of the previous query.

13. Write the query that will list the average age of your customers. (Assume that the

CUSTOMER table has been modified to include the CUST_DOB and the derived CUST_AGE

attribute.)

14. Write the trigger to update the CUST_BALANCE in the CUSTOMER table when a new

invoice record is entered. (Assume that the sale is a credit sale.) Test the trigger using the

following new INVOICE record:

8005, 1001, ’27-APR-12’, 225.40

Name the trigger trg_updatecustbalance.

15. Write a procedure to add a new customer to the CUSTOMER table. Use the following values

in the new record:

1002, ‘Rauthor’, ‘Peter’, 0.00

Name the procedure prc_cust_add. Run a query to see if the record has been added.

16. Write a procedure to add a new invoice record to the INVOICE table. Use the following values

in the new record:

8006, 1000, ’30-APR-12’, 301.72

Name the procedure prc_invoice_add. Run a query to see if the record has been

added.

17. Write a trigger to update the customer balance when an invoice is deleted. Name the trigger

trg_updatecustbalance2.

18. Write a procedure to delete an invoice given the invoice number as a parameter. Name the

procedure prc_inv_delete. Test the procedure by deleting invoices 8005 and 8006.

Figure P8.19 Ch08_SaleCo2 Database Tables

19. Write a query to display the products that have a price greater than $50.

20. Write a query to display the current salary for each employee in department 300. Assume that

only current employees are kept in the system, and therefore the most current salary for each

employee is the entry in the salary history with a NULL end date. Sort the output in

descending order by salary amount.

Figure P8.20 Current salary for employees in department 300

21. Write a query to display the starting salary for each employee. The starting salary would be

the entry in the salary history with the oldest salary start date for each employee. Sort the

output by employee number.

Figure P8.21 Starting salary for each employee

22. Write a query to display the invoice number, line numbers, product SKUs, product

descriptions, and brand ID for sales of sealer and top coat products of the same brand on the

same invoice.

Figure P8.22 Invoices for sealer and top coat of the same brand

23. The Binder Prime Company wants to recognize the employee who sold the most of their

products during a specified period. Write a query to display the employee number, employee

first name, employee last name, e-mail address, and total units sold for the employee who sold

the most Binder Prime brand products between November 1, 2011, and December 5, 2011. If

there is a tie for most units sold, sort the output by employee last name.

Figure P8.23 Employees with most Binder Prime units sold

24. Write a query to display the customer code, first name, and last name of all customers who

have had at least one invoice completed by employee 83649 and at least one invoice completed

by employee 83677. Sort the output by customer last name and then first name.

Figure P8.24 Customers with invoices filled by employees 83649 and 83677

25. LargeCo is planning a new promotion in Alabama (AL) and wants to know about the largest

purchases made by customers in that state. Write a query to display the customer code,

customer first name, last name, full address, invoice date, and invoice total of the largest

purchase made by each customer in Alabama. Be certain to include any customers in Alabama

who have never made a purchase (their invoice dates should be NULL and the invoice totals

should display as 0).

Figure P8.25 Largest purchases of customers in Alabama

26. One of the purchasing managers is interested in the impact of product prices on the sale of

products of each brand. Write a query to display the brand name, brand type, average price of

products of each brand, and total units sold of products of each brand. Even if a product has

been sold more than once, its price should only be included once in the calculation of the

average price. However, you must be careful because multiple products of the same brand can

have the same price, and each of those products must be included in the calculation of the

brand’s average price.

Figure P8.26 Average price and total units sold of products by brand

27. The purchasing manager is still concerned about the impact of price on sales. Write a query to

display the brand name, brand type, product SKU, product description, and price of any

products that are not a premium brand, but that cost more than the most expensive premium

brand products.

Figure P8.27 Nonpremium products that are more expensive than premium products

Use the Ch08_SaleCo2 database to work Problems 28-31.

50

Chapter 8 Advanced SQL

28. Create a trigger named trg_line_total to write the LINE_TOTAL value in the LINE table

every time you add a new LINE row. (The LINE_TOTAL value is the product of the

LINE_UNITS and the LINE_PRICE values.)

29. Create a trigger named trg_line_prod that will automatically update the product quantity on

hand for each product sold after a new LINE row is added.

30. Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL,

INV_TAX, and INV_TOTAL. The procedure takes the invoice number as a parameter. The

INV_SUBTOTAL is the sum of the LINE_TOTAL amounts for the invoice, the INV_TAX is

the product of the INV_SUBTOTAL and the tax rate (8%), and the INV_TOTAL is the sum of

the INV_SUBTOTAL and the INV_TAX.

31. Create a procedure named prc_cus_balance_update that will take the invoice number as a

parameter and update the customer balance. (Hint: You can use the DECLARE section to

define a TOTINV numeric variable that holds the computed invoice total.)

NOTE

Actually, the TOTINV is not really needed – because the INVOICE table already contains

the INV_TOTAL attribute. The procedure we have shown next uses the INV_TOTAL

attribute.

Use the Ch08_AviaCo database to work Problems 32-43.

ONLINE CONTENT

The Ch08_AviaCo database used for Problems 32-43 is available at www.cengagebrain.com, as

are the script files to duplicate the data set in Oracle.

Figure P8.32 Ch08_AviaCo Database Tables

32. Modify the MODEL table to add the following attribute and insert the values shown in Table

P8.23.

Table P8.32 The New Attribute for the MODEL Table

Attribute name Attribute Description Attribute type Attribute Values

MOD_WAIT_CHG Waiting charge per

hour for each model:

Numeric $100 for C-90A

$50 for PA23-250

$75 for PA31-350

33. Write the queries to update the MOD_WAIT_CHG attribute values based on problem 32.

34. Modify the CHARTER table to add the attributes shown in the following table.

Table P8.34 The New Attributes for the CHARTER Table

Attribute name Attribute Description Attribute type

CHAR_WAIT_CHG Waiting charge for each model (copied from the

MODEL table.)

Numeric

CHAR_FLT_CHG_HR Flight charge per mile for each model (copied

from the MODEL table using the

MOD_CHG_MILE attribute.)

Numeric

CHAR_FLT_CHG Flight charge (calculated by

CHAR_HOURS_FLOWN x CHAR_FLT_CHG_HR)

Numeric

CHAR_TAX_CHG CHAR_FLT_CHG x tax rate (8%) Numeric

CHAR_TOT_CHG CHAR_FLT_CHG + CHAR_TAX_CHG Numeric

CHAR_PYMT Amount paid by customer Numeric

CHAR_BALANCE Balance remaining after payment Numeric

35. Write the sequence of commands required to update the CHAR_WAIT_CHG attribute values

in the CHARTER table. Hint: Use either an updatable view or a stored procedure.

36. Write the sequence of commands required to update the CHAR_FLT_CHG_HR attribute

values in the CHARTER table. Hint: Use either an updatable view or a stored procedure.

37. Write the command required to update the CHAR_FLT_CHG attribute values in the

CHARTER table.

38. Write the command required to update the CHAR_TAX_CHG attribute values in the

CHARTER table.

39. Write the command required to update the CHAR_TOT_CHG attribute values in the

CHARTER table.

40. Modify the PILOT table to add the attribute shown in the following table.

Table P8.40 The New Attribute for the PILOT Table

Attribute name Attribute Description Attribute type

PIL_PIC_HRS Pilot in command (PIC) hours. Updated by adding the

CHARTER table’s CHAR_HOURS_FLOWN to the

PIL_PIC_HRS when the CREW table shows the

CREW_JOB to be pilot.

Numeric

41. Create a trigger named trg_char_hours that will automatically update the AIRCRAFT table

when a new CHARTER row is added. Use the CHARTER table’s CHAR_HOURS_FLOWN

to update the AIRCRAFT table’s AC_TTAF, AC_TTEL, and AC_TTER values.

42. Create a trigger named trg_pic_hours that will automatically update the PILOT table when a

new CREW row is added and the CREW table uses a ‘pilot’ CREW_JOB entry. Use the

CHARTER table’s CHAR_HOURS_FLOWN to update the PILOT table’s PIL_PIC_HRS

only when the CREW table uses a ‘pilot’ CREW_JOB entry.

43. Create a trigger named trg_cust_balance that will automatically update the CUSTOMER

table’s CUST_BALANCE when a new CHARTER row is added. Use the CHARTER table’s

CHAR_TOT_CHG as the update source (Assume that all charter charges are charged to the

customer balance.)

Case Solutions

The following problems expand on the TinyVideo case from Chapter 7. To complete the following

problems, it is necessary to have first completed the table creation and data entry requirements

specified in Problems 65 and 66 in Chapter 7.

44. Alter the DETAILRENTAL table to include a derived attribute named DETAIL_DAYSLATE

to store integers up to 3 digits. The attribute should accept null values.

45. Alter the VIDEO table to include an attribute named VID_STATUS to store character data up

to 4 characters long. The attribute should not accept null values. The attribute should have a

constraint to enforce the domain (“IN”, “OUT”, and “LOST”), and have a default value of

“IN”.

46. Update the VID_STATUS attribute of the VIDEO table using a subquery to set the

VID_STATUS to “OUT” for all videos that have a null value in the DETAIL_RETURNDATE

attribute of the DETAILRENTAL table.

47. Alter the PRICE table to include an attribute named PRICE_RENTDAYS to store integers up

to 2 digits. The attribute should not accept null values, and should have a default value of 3.

48. Update the PRICE table to place the values shown in the following table in the

PRICE_RENTDAYS attribute.

PRICE_CO

DE

PRICE_RENTD

AYS

49. Create a trigger named trg_late_return that will write the correct value to

DETAIL_DAYSLATE in the DETAILRENTAL table whenever a video is returned. The

trigger should execute as a BEFORE trigger when the DETAIL_RETURNDATE or

DETAIL_DUEDATE attributes are updated. The trigger should satisfy the following

conditions.

a. If the return date is null, then the days late should be null also.

b. If the return date is not null, then the days late should determine if the video is returned

late.

c. If the return date is noon of the day after the due date or earlier, then the video is not

considered late, and the days late should have a value of zero (0).

d. If the return date is past noon of the day after the due date, then the video is considered late

so the number days late must be calculated and stored.

50. Create a trigger named trg_mem_balance that will maintain the correct value in the

membership balance in the MEMBERSHIP table when videos are returned late. The trigger

should execute as an AFTER trigger when the due date or return date attributes are updated

in the DETAILRENTAL table. The trigger should satisfy the following conditions.

a. Calculate the value of the late fee prior to the update that triggered this execution of the

trigger. The value of the late fee is the days late times the daily late fee. If the previous

value of the late fee was null, then treat it as zero (0).

b. Calculate the value of the late fee after the update that triggered this execution of the

trigger. If the value of the late fee is now null, then treat it as zero (0).

c. Subtract the prior value of the late fee from the current value of the late fee to determine

the change in late fee for this video rental.

d. If the amount calculated in part c is not zero (0), then update the membership balance by

the amount calculated for the membership associated the rental that this detail is a part of.

51. Create a sequence named rent_num_seq to start with 1100, increment by 1, and not cache any

values.

52. Create a stored procedure named prc_new_rental to insert new rows in the RENTAL table.

The procedure should satisfy the following conditions.

a. The membership number will be provided as a parameter.

b. Use a Count() function to verify that the membership number exists in the MEMBERSHIP

table. If it does not exist, then a message should be displayed stating that the membership

does not exist and no data should be written to the database.

c. If the membership does exist, then retrieve the membership balance and display a message

stating the balance amount as the previous balance. (For example, if the membership has a

balance of $5.00, then display “Previous balance: $5.00”.)

d. Insert a new row in the rental table using the sequence created in #42 above to generate the

value for RENT_NUM, the current system date for the value for RENT_DATE, and the

membership number provided as the value for MEM_NUM.

53. Create a stored procedure named prc_new_detail to insert new rows in the DETAILRENTAL

table. The procedure should satisfy the following requirements.

a. The video number will be provided as a parameter.

b. Verify the video number exists in the VIDEO table. If it does not exist, then display a

message that the video does not exist, and do not write any data to the database.

c. If the video number does exist, then verify that the VID_STATUS for that video is “IN”. If

the status is not “IN”, then display a message that the return of the video must be entered

before it can be rented again, and do not write any data to the database.

d. If the status is “IN”, then retrieve the values of PRICE_RENTFEE,

PRICE_DAILYLATEFEE, and PRICE_RENTDAYS associated with the video from the

PRICE table.

e. Calculate the due date for the video rental by adding the number of days found in

PRICE_RENTDAYS above to 11:59:59PM (hours:minutes:seconds) on the current system

date.

f. Insert a new row in the DETAILRENTAL table using the previous value returned by

rent_num_seq as the RENT_NUM, the video number provided in the parameter as the

VID_NUM, the PRICE_RENTFEE as the value for DETAIL_FEE, the due date calculated

above for the DETAIL_DUEDATE, PRICE_DAILYLATEFEE as the value for

DETAIL_DAILYLATEFEE, and null for the DETAIL_RETURNDATE.

54. Create a stored procedure named prc_return_video enter data about the return of videos that

had been rented. The procedure should satisfy the following requirements.

a. The video number will be provided as a parameter.

b. Verify the video number exists in the VIDEO table. If it does not exist, display a message

that the video number provided was not found and do not write any data to the database.

c. If the video number does exist, then use a Count() function to ensure that the video has only

one record in DETAILRENTAL for which it does not have a return date. If more than one

row in DETAILRENTAL indicates that the video is rented but not returned, display an

error message that the video has multiple outstanding rentals and do not write any data to

the database.

d. If the video does not have any outstanding rentals, the update the video status for the video

in the VIDEO table to “IN”, and display a message that the video had no outstanding

rentals but it is now available for rental. If the video has only one outstanding rental, then

update the return date to the current system date, and update the video status for that

video in the VIDEO table to “IN”. Then display a message stating that the video was

successfully returned.