Paragraph Styles I G. Write an SQL statement to list LastName and FirstName of customers who have made at least one purchase with PreTaxAmount greater than $200. Use a subquery 1. Answer part G but use a join using JOIN ON syntax What are the consequences of using (or not using) the DISTINCT keyword in this version of the query? 2 Write an SQL statement to list Lastilanne and FirstName of customers who have purchased an itern that costs more than $50. Use a subquery! 3. part I but use a join using JOIN ON syntax. What are the consequences of using for not using the DISTINCT keyword in this version of the query? 4.Write an SQL statement to list the lastNare and FirstName of customers who have purchased an item that was created by an artist with a LastName that begins with the letter 1. Use a subquery. 5. Answer part K but use a join using JOIN ON syntax. What are the consequences of using (or not using) the DISTINCT keyword in this version of the query? 6. Write an SQL statement to show the Customer Last Name, FirstName, and sum of PretasAmount for each customer. Use a join using JOIN ON syntax 7. Write an SQL statement to show the sum of PreTaxAmount for each artist (Hint: The result will have only one line per each artist). Use a join using JOIN ON syntax, and sort the results by AttistlastName and then ActistFirstName in ascending order. Note that this should include the full Pie Casamount for any purchase in which the artist had an item, even if other items in the purchase were created by other artists. 8. Write an SQL statement to show the sum of PreTaxAmount for each artist, as in Part N, but exclude any purchases with PretasAmount greater than $25. Use a join using JOIN ON syntax, and sort the results by AutistlastNams and then assistit domein descending order 9. Write an SQL statement to show which customers bought which items, and include any items that have not been sold. Include CUSTOMER.LastName, CUSTOMER FirstName, loveica Nucore. Inxeics Rate. Item Number. Item Resscintigo. ActistlastName, and ActisskirstNams. Use a join using JOIN ON syntax and sort the results by ActistlastName and GatisticsNome in ascending order. Note that in Microsoft Access this requires multiple queries FIGURE 3-43 Sample Data for JRJ CUSTOMER Table Customerid 1 2 LastName Stanley Price Becky Birch Romez Jackson FirstName Elizabeth Fred Linda Pamela Ricardo Phone 555-236-7789 555-236-0091 555-236-0392 555-236-4493 555-236-3334 555-236-1095 EmailAddress Elizabeth Stanley@somewhere.com Fred. Pricepsomewhere.com Linda. Becky@somewhere.com Pamela.Birch somewhere.com Ricardo Romez@somewhere.com Samantha.jackson@somewhere.com 3 4 5 Samantha James River Jewelry Project Questions FIGURE 3-44 Sample Data for JRJ ITEM Table Item Number 1 2 $ $ $ $ $ 3 4 5 6 7 8 9 ItemDescription Gold Bracelet Gold Necklace Bead Earrings Gold Bracelet Silver Necklace Bead Earrings Bead Earrings Gold Earrings Gold Necklace Bead Earrings Bead Earrings Bead Earrings Gold Necklace Silver Earrings Gold Bracelet Bead Earrings Bead Earrings Gold Bracelet Silver Necklace Cost ArtistlastName 120.00 Josephson 160.00 Baker 50.00 Josephson 180.00 Baker 135.00 Baker 25.00 Josephson 22.50 Josephson 50.00 Lintz 160.00 Lintz 20.00 Josephson 35.00 Josephson 45.00 Josephson 225.00 Lintz 55.00 Lintz 200.00 Lintz 25.00 Josephson 45.00 Josephson 210.00 Baker 165.00 Baker $ $ $ $ $ ArtistFirstName Mary Samantha Mary Samantha Sam Mary Mary John John Mary Mary Mary John John John Mary Mary Samantha Sam 10 11 12 14 15 16 $ $ $ $ $ $ $ 17 18 19 FIGURE 3-45 Sample Data for JRJ PURCHASE Table InvoiceNumber 1001 CustomerID 1 .1002 PreTaxAmount $ 155.00 $ 203.00 $ 75.00 $ 67.00 2 1003 3 1004 4 InvoiceDate 5/5/2019 5/7/2019 5/11/2019 5/15/2019 5/15/2019 5/16/2019 5/25/2019 6/6/2019 6/7/2019 1005 330.00 5 $ $ 1006 25.00 1 1007 3 $ $ $ 45.00 445.00 1008 1 | 1009 72.00 6 FIGURE 3-46 Sample Data for JRJ PURCHASE_ITEM Table itemNumber Invoice Number 1001 InvoiceLine Number 1 1 $ RetailPrice 155.00 203.00 75.00 1002 1 2 $ 1003 1 3 6 1004 1004 1 2 1 35.00 32.00 7 1005 4 1005 2 8 240.00 90.00 25.00 1006 1 $ $ $ $ $ $ $ $ $ $ $ 10 1007 1 11 45.00 1008 1 5 1008 2 175.00 215.00 55.00 72.00 1008 9 12 14 3 1009 1
