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, g
...
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.