SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 352

,QYRLFH
,16(57 ,172 ,192,&( 9$/8(6
-XQ 9,6$
5DOSK$EOH#VRPHZKHUHFRP
,16(57 ,172 /,1(B,7(0 9$/8(6 9.
,16(57 ,172 /,1(B,7(0 9$/8(6 9%
,16(57 ,172 /,1(B,7(0 9$/8(6 %.

,QYRLFH
,16(57 ,172 ,192,&( 9$/8(6
-XQ 0DVWHU&DUG
-HQQ\7\OHU#VRPHZKHUHFRP
,16(57 ,172 /,1(B,7(0 9$/8(6 9.
,16(57 ,172 /,1(B,7(0 9$/8(6 9%

,QYRLFH
,16(57 ,172 ,192,&( 9$/8(6
-XQ 0DVWHU&DUG
-RDQ:D\QH#HOVHZKHUHFRP
,16(57 ,172 /,1(B,7(0 9$/8(6 9.
,16(57 ,172 /,1(B,7(0 9$/8(6 %.
,16(57 ,172 /,1(B,7(0 9$/8(6 9.
,16(57 ,172 /,1(B,7(0 9$/8(6 9%
,16(57 ,172 /,1(B,7(0 9$/8(6 9.

,QYRLFH
,16(57 ,172 ,192,&( 9$/8(6
-XQ 9,6$
5DOSK$EOH#VRPHZKHUHFRP
,16(57 ,172 /,1(B,7(0 9$/8(6 9.
,16(57 ,172 /,1(B,7(0 9$/8(6 9%
,16(57 ,172 /,1(B,7(0 9$/8(6 9.
,16(57 ,172 /,1(B,7(0 9$/8(6 9%

Figure 7-42
Continued

I. Answer part G but use a join. (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.)

J. 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. Sort
the results by LastName in descending order, and then by FirstName in descending order.

K. Write an SQL statement to list the FirstName, LastName, Phone, ProductNumber,
and Description of customers (list each combination of name and video product only
once) who have purchased a video product. Sort the results by LastName in descend-
ing order, then by FirstName in descending order, and then by ProductNumber in
descending order. (Hint: Video products have a ProductNumber that starts with VK.)

L. Write an SQL statement to show the sum of SubTotal (this is the money earned
by HSD on products sold exclusive of shipping costs and taxes) for INVOICE as
SumOfSubTotal.

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

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

O. Write an SQL statement to modify PRODUCT UnitPrice for ProductNumber VK004 to
$34.95 instead of the current UnitPrice of $24.95.