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 LastName and FirstName of customers who have purchased an item that costs more than $50. Use a subquery.
3. Answer part I 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?
4. Write an SQL statement to list the LastName and FirstName of customers who have purchased an item that was created by an artist with a LastName that begins with the letter J. 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 CustomerID, LastName, FirstName, and sum of PreTaxAmount 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 ArtistLastName and then ArtistFirstName in ascending order. Note that this should include the full PreTaxAmount 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 PreTaxAmount greater than $25. Use a join using JOIN ON syntax, and sort the results by ArtistLastName and then ArtistFirstName in 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, InvoiceNumber, InvoiceDate, ItemNumber, ItemDescription, ArtistLastName, and ArtistFirstName. Use a join using JOIN ON syntax, and sort the results by ArtistLastName and ArtistFirstName in ascending order. Note that in Microsoft Access this requires multiple queries.
10. Write an SQL statement to modify all ITEM rows with an artist last name of Baxter to an artist first name of Rex.
11. Write SQL statements to switch the values of ArtistLastName so that all rows currently having the value Baker will have the value Baxter and all rows currently having the value Baxter will have the value Baker.

LastName Stanley Customer D 1 2 3 4 price Becky Birch Romez Jackson First Name Elizabeth Fred Linda Pamela Ricardo Samantha 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.Price@somewhere.com Linda.Becky@somewhere.com Pamela.Birch@somewhere.com Ricardo Romez@somewhere.com Samantha Jackson@somewhere.com 5 FIGURE 3-44 Sample Data for JRJ ITEM Table ItemNumber 1 Cost ArtistLastName 120.00 Llosenson FIGURE 3-44 2 3 $ $ $ $ $ $ 4 5 6 7 $ 8 ArtistFirstName Mary Samantha Mary Samantha Sam Mary Mary John John Mary Mary Mary John John John $ Item Description 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 9 10 $ $ $ 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 11 12 13 $ $ $ 14 15 $ 16 17 $ $ $ $ Mary Mary Samantha Sam 18 19 CustomerID 1 2 $ 3 4 InvoiceNumber 1001 1002 1003 1004 1005 1006 1007 1008 1009 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 PreTaxAmount $ 155.00 $ 203.00 $ 75.00 $ 67.00 $ 330.00 $ 25.00 $ 45.00 $ 445.00 72.00 FIGURE 3-45 5 1 3 1 6 FIGURE 3-46 Sample Data for JRJ PURCHASE_ITEM Table InvoiceNumber InvoiceLineNumber 1001 1002 1 ItemNumber 1 1 RetailPrice 155.00 203.00 2 1003 1 3 75.00 1004 1 6 $ $ $ $ $ $ $ 35.00 32.00 2 7 1004 1005 1 4 240.00 1005 2 8 90.00 25.00 1 10 $ 1006 1007 1 11 $ $ $ $ 45.00 175.00 1008 1 5 1008 2 9 215.00 1008 3 12 $ $ $ $ 55.00 72.00 1009 1 14 LastName Stanley Customer D 1 2 3 4 price Becky Birch Romez Jackson First Name Elizabeth Fred Linda Pamela Ricardo Samantha 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.Price@somewhere.com Linda.Becky@somewhere.com Pamela.Birch@somewhere.com Ricardo Romez@somewhere.com Samantha Jackson@somewhere.com 5 FIGURE 3-44 Sample Data for JRJ ITEM Table ItemNumber 1 Cost ArtistLastName 120.00 Llosenson FIGURE 3-44 2 3 $ $ $ $ $ $ 4 5 6 7 $ 8 ArtistFirstName Mary Samantha Mary Samantha Sam Mary Mary John John Mary Mary Mary John John John $ Item Description 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 9 10 $ $ $ 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 11 12 13 $ $ $ 14 15 $ 16 17 $ $ $ $ Mary Mary Samantha Sam 18 19 CustomerID 1 2 $ 3 4 InvoiceNumber 1001 1002 1003 1004 1005 1006 1007 1008 1009 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 PreTaxAmount $ 155.00 $ 203.00 $ 75.00 $ 67.00 $ 330.00 $ 25.00 $ 45.00 $ 445.00 72.00 FIGURE 3-45 5 1 3 1 6 FIGURE 3-46 Sample Data for JRJ PURCHASE_ITEM Table InvoiceNumber InvoiceLineNumber 1001 1002 1 ItemNumber 1 1 RetailPrice 155.00 203.00 2 1003 1 3 75.00 1004 1 6 $ $ $ $ $ $ $ 35.00 32.00 2 7 1004 1005 1 4 240.00 1005 2 8 90.00 25.00 1 10 $ 1006 1007 1 11 $ $ $ $ 45.00 175.00 1008 1 5 1008 2 9 215.00 1008 3 12 $ $ $ $ 55.00 72.00 1009 1 14