Write SQL statements to list all columns for all tables Write an SQL statement to list ItemNumber and ItemDescription for all items that cost more than $100 Write an SQL statement to list ItemNumber and ItemDescription for all items that cost more than $100 and were produced by an artist with a name ending with the letters son 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 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 Write an SQL statement to list LastName and FirstName of customers who have purchased an item that costs more than $50 Use a subquery 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 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 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 Write an SQL statement to show the CustomerID, LastName, FirstName, and sum of PreTaxAmount for each customer Use a join using JOIN ON syntax 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 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 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 Write an SQL statement to modify all ITEM rows with an artist last name of Baxter to an artist first name of Rex 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

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 25, 2024

Write SQL statements to list all columns for all tables. Write an SQL statement to list ItemNumber and ItemDescription for all items that cost more

image text in transcribed

Write SQL statements to list all columns for all tables.
Write an SQL statement to list ItemNumber and ItemDescription for all items that cost more than $100.
Write an SQL statement to list ItemNumber and ItemDescription for all items that cost more than $100 and were produced by an artist with a name ending with the letters son.
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.
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?
Write an SQL statement to list LastName and FirstName of customers who have purchased an item that costs more than $50. Use a subquery.
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?
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.
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?
Write an SQL statement to show the CustomerID, LastName, FirstName, and sum of PreTaxAmount for each customer. Use a join using JOIN ON syntax.
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.
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.
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.
Write an SQL statement to modify all ITEM rows with an artist last name of Baxter to an artist first name of Rex.
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