CUST_ID FIRST NAME Joey Billy 125 182 227 294 314 375 435 492 543 616 721...

        

Transcribed Image Text:

CUST_ID FIRST NAME Joey Billy 125 182 227 294 314 375 435 492 543 616 721 795 Sandra Samantha Tom Melanie James Elmer Angiel Sally Leslie Randy LAST NAME Smith Rufton Pincher Smith Rascal Jackson Gonzalez Jackson Hendricks Cruz Smith Blacksmith ADDRESS 17 Fourth St 21 Simple Cir 53 Verde Ln 14 Rock Ln 1 Rascal Farm Rd 42 Blackwater Way 16 Rockway Rd 22 Jackson Farm Rd 27 Locklear Ln 199 18th Ave 123 Sheepland Rd 75 Stream Rd CITY Cody Garland Powell Ralston Cody Elk Butte Wapiti Garland Powell Ralston Elk Butte Cody STATE POSTAL WY WY WY WY WY WY WY WY WY WY WY WY 82414 82435 82440 82440 82414 82433 82450 82435 82440 82440 82433 82414 EMAIL jsmith 17@example.com billyruff@example.com spinch2@example.com ssmith5@example.com trascal3@example.com mjackson5@example.com jgonzo@example.com ejackson4@example.com ahendricks7@example.com scruz5@example.com Ismith12@example.com rblacksmith6@example.com BALANCE 80.68 43.13 156.38 58.60 17.25 252.25 230.40 45.20 315.00 8.33 166.65 61.50 CREDIT_LIMIT REP_ID 500.00 750.00 500.00 500.00 250.00 250.00 1000.00 500.00 750.00 500.00 1000.00 500.00 05 10 15 10 15 05 15 10 05 15 10 05 INVOICE_NUM 14216 14219 14222 14224 14228 14231 14233 14237 INVOICE_DATE 2021-11-15 2021-11-15 2021-11-16 2021-11-16 2021-11-18 2021-11-18 2021-11-18 2021-11-19 CUST_ID 125 227 294 182 435 125 435 616 INVOICE_NUM 14216 14219 14219 14222 14224 14228 14228 14231 14233 14233 14233 14237 ITEM_ID CA75 AD72 DT12 LD14 KH81 FS42 PF19 UF39 KH81 QB92 WB49 LP73 QUANTITY 3 2 4 1 1 1 2 1 4 4 3 QUOTED_PRICE 37.99 79.99 39.99 47.99 18.99 124.99 74.99 189.99 19.99 109.95 74.95 54.95 ITEM_ID AD72 BC33 CA75 DT12 FM23 FS39 FS42 KH81 LD14 LP73 DESCRIPTION Dog Feeding Station Feathers Bird Cage (12x24x18) Enclosed Cat Litter Station Dog Toy Gift Set Fly Mask with Ears Folding Saddle Stand Aquarium (55 Gallon) Wild Bird Food (25 lb) Locking Small Dog Door Large Pet Carrier ON HAND 12 10 15 27 41 12 5 24 14 23 CATEGORY DOG BRD CAT DOG HOR HOR FSH BRD DOG DOG LOCATION B B C B C C A C A B PRICE 79.99 79.99 39.99 39.99 24.95 39.99 124.99 19.99 49.99 59.99 REP_ID 05 10 15 20 FIRST NAME Susan Richard Donna Daniel LAST_NAME Garcial Miller Smith Jackson ADDRESS 42 Mountain Ln 87 Pikes Dr 312 Oak Rd 19 Lookout Dr CITY Cody Ralston Powell Elk Butte STATE WY WY WY WY POSTAL 82414 82440 82440 82433 CELL PHONE 307-824-1245 307-406-4321 307-982-8401 307-883-9481 COMMISSION 12743.16 20872.11 14912.92 0.00 RATE 0.04 0.06 0.04 0.04 Lab 5-1: KimTay Pet Supplies Lahl Instructions Page 2 of 4 U Task 1: For each invoice, list the invoice number and invoice date along with the ID, first name, and last name of the customer for which the invoice was created. Task 2: For each invoice placed on November 15, 2021, list the invoice number along with the ID, first name, and last name of the customer for which the invoice was created. Task 4: Use the IN operator to find the ID, first name, and last name of each customer for which as invoice was created on November 15, 2021. Task 5: Repeat Task 4, but this time use the EXISTS operator in your answer. Task 3: For each invoice, list the invoice number, > invoice date, item ID, quantity ordered, and quoted price for each invoice line that makes up the invoice. Task 6: Find the ID, first name, and last name of each customer for which an invoice was not created on November 15, 2021. T--I. O. n. < = 2/4 > > > Task 7: For each invoice, list the invoice number, > invoice date, item ID, description, and category for each item that makes up the invoice. > > FILETREE ~/workspace query.sql ~ query.sql 1 SELECT 1. INVOICE_NUMBER, 1. INVOICE_DATE, c.ID, c.FIRST_NAME, c.LAST_NAME 2 FROM INVOICE i 3 JOIN CUSTOMER c ON i.CUSTOMER_ID = c. ID; 4 SELECT invoice. INVOICE_NUM, customer.CUST_ID, customer. FIRST_NAME, customer.LAST_NAME 5 FROM invoice 6 JOIN customer ON invoice.CUST_ID = customer.CUST_ID 7 WHERE invoice. INVOICE_DATE= '2021-11-15'; 8 SELECT Invoice. InvoiceNumber, Invoice. InvoiceDate, InvoiceLine. ItemID, InvoiceLine.Quantity. InvoiceLine. QuotedPrice 9 FROM Invoice 10 JOIN InvoiceLine ON Invoice. InvoiceNumber = InvoiceLine. InvoiceNumber; 11 SELECT c. ID, c.first_name, c.last_name 12 FROM customer c 13 WHERE c.ID IN ( 14 15 16 17 ) 10 CELECT TO x SELECT DISTINCT i.customer_id + FROM invoice i WHERE i.invoice_date = '2021-11-15 SQL Viewer KimTay D X > Terminal + ERROR 1146 (42SO2) at line 1: Table 'KimTay.INVOICE' doesn't exist Task 8: Repeat Task 7, but this time order the rows by category and then by invoice number. > Lab 5-1: Kim Tay Pet Supplies T = ... % ? Instructions Page 3 of 4 Task 9: Use a subquery to find the sales rep ID, first name, and last name of each sales rep who represents at least one customer with a credit limit of $500. List each sales rep only once in the results. Task 10: Repeat Task 9, but this time do not use a subquery. Task 11: Find the ID, first name, and last name of each customer that currently has an invoice on file for Wild Bird Food (25 lb). Task 12: List the item ID, description, and category for each pair of items that are in the same category. (For example, one such pair would be item FS42 and item PF19, because the category for both items is FSH.) Order the output by category. Task 13: List the invoice number and invoice date for each invoice created for the customer James Gonzalez. Task 14: List the invoice number and invoice date for each invoice that contains an invoice line for a Wild Bird Food (25 lb). Task 15: List the invoice number and invoice date for each invoice that either was created for James Gonzalez or that contains an invoice line for Wild Bird Food (25lb). 3/4 < = > > > > > FILETREE ~/workspace query.sql query.sql 1 SELECT 1. INVOICE_NUMBER, 1. INVOICE_DATE, c.ID, c.FIRST_NAME, c.LAST_NAME 2 FROM INVOICE i 3 JOIN CUSTOMER c ON i.CUSTOMER_ID = c. ID; 4 SELECT invoice. INVOICE_NUM, customer.CUST_ID, customer. FIRST_NAME, customer.LAST_NAME 5 FROM invoice 6 JOIN customer ON invoice.CUST_ID = customer.CUST_ID 7 WHERE invoice. INVOICE_DATE= '2021-11-15'; 8 SELECT Invoice. InvoiceNumber, Invoice. InvoiceDate, InvoiceLine. ItemID, InvoiceLine.Quantity, InvoiceLine. QuotedPrice 9 FROM Invoice 10 JOIN InvoiceLine ON Invoice. InvoiceNumber = InvoiceLine. InvoiceNumber; 11 SELECT c.ID, c.first_name, c.last_name 12 FROM customer c 13 WHERE c.ID IN ( 14 15 16 17 ) 10 SELECT ID KimTay SELECT DISTINCT i.customer_id + SQL Viewer X + FROM invoice i WHERE i.invoice_date = '2021-11-15 9 X first name. Toot >_ Terminal + ERROR 1146 (42S02) at line 1: Table 'KimTay.INVOICE' doesn't exist CUST_ID FIRST NAME Joey Billy 125 182 227 294 314 375 435 492 543 616 721 795 Sandra Samantha Tom Melanie James Elmer Angiel Sally Leslie Randy LAST NAME Smith Rufton Pincher Smith Rascal Jackson Gonzalez Jackson Hendricks Cruz Smith Blacksmith ADDRESS 17 Fourth St 21 Simple Cir 53 Verde Ln 14 Rock Ln 1 Rascal Farm Rd 42 Blackwater Way 16 Rockway Rd 22 Jackson Farm Rd 27 Locklear Ln 199 18th Ave 123 Sheepland Rd 75 Stream Rd CITY Cody Garland Powell Ralston Cody Elk Butte Wapiti Garland Powell Ralston Elk Butte Cody STATE POSTAL WY WY WY WY WY WY WY WY WY WY WY WY 82414 82435 82440 82440 82414 82433 82450 82435 82440 82440 82433 82414 EMAIL jsmith 17@example.com billyruff@example.com spinch2@example.com ssmith5@example.com trascal3@example.com mjackson5@example.com jgonzo@example.com ejackson4@example.com ahendricks7@example.com scruz5@example.com Ismith12@example.com rblacksmith6@example.com BALANCE 80.68 43.13 156.38 58.60 17.25 252.25 230.40 45.20 315.00 8.33 166.65 61.50 CREDIT_LIMIT REP_ID 500.00 750.00 500.00 500.00 250.00 250.00 1000.00 500.00 750.00 500.00 1000.00 500.00 05 10 15 10 15 05 15 10 05 15 10 05 INVOICE_NUM 14216 14219 14222 14224 14228 14231 14233 14237 INVOICE_DATE 2021-11-15 2021-11-15 2021-11-16 2021-11-16 2021-11-18 2021-11-18 2021-11-18 2021-11-19 CUST_ID 125 227 294 182 435 125 435 616 INVOICE_NUM 14216 14219 14219 14222 14224 14228 14228 14231 14233 14233 14233 14237 ITEM_ID CA75 AD72 DT12 LD14 KH81 FS42 PF19 UF39 KH81 QB92 WB49 LP73 QUANTITY 3 2 4 1 1 1 2 1 4 4 3 QUOTED_PRICE 37.99 79.99 39.99 47.99 18.99 124.99 74.99 189.99 19.99 109.95 74.95 54.95 ITEM_ID AD72 BC33 CA75 DT12 FM23 FS39 FS42 KH81 LD14 LP73 DESCRIPTION Dog Feeding Station Feathers Bird Cage (12x24x18) Enclosed Cat Litter Station Dog Toy Gift Set Fly Mask with Ears Folding Saddle Stand Aquarium (55 Gallon) Wild Bird Food (25 lb) Locking Small Dog Door Large Pet Carrier ON HAND 12 10 15 27 41 12 5 24 14 23 CATEGORY DOG BRD CAT DOG HOR HOR FSH BRD DOG DOG LOCATION B B C B C C A C A B PRICE 79.99 79.99 39.99 39.99 24.95 39.99 124.99 19.99 49.99 59.99 REP_ID 05 10 15 20 FIRST NAME Susan Richard Donna Daniel LAST_NAME Garcial Miller Smith Jackson ADDRESS 42 Mountain Ln 87 Pikes Dr 312 Oak Rd 19 Lookout Dr CITY Cody Ralston Powell Elk Butte STATE WY WY WY WY POSTAL 82414 82440 82440 82433 CELL PHONE 307-824-1245 307-406-4321 307-982-8401 307-883-9481 COMMISSION 12743.16 20872.11 14912.92 0.00 RATE 0.04 0.06 0.04 0.04 Lab 5-1: KimTay Pet Supplies Lahl Instructions Page 2 of 4 U Task 1: For each invoice, list the invoice number and invoice date along with the ID, first name, and last name of the customer for which the invoice was created. Task 2: For each invoice placed on November 15, 2021, list the invoice number along with the ID, first name, and last name of the customer for which the invoice was created. Task 4: Use the IN operator to find the ID, first name, and last name of each customer for which as invoice was created on November 15, 2021. Task 5: Repeat Task 4, but this time use the EXISTS operator in your answer. Task 3: For each invoice, list the invoice number, > invoice date, item ID, quantity ordered, and quoted price for each invoice line that makes up the invoice. Task 6: Find the ID, first name, and last name of each customer for which an invoice was not created on November 15, 2021. T--I. O. n. < = 2/4 > > > Task 7: For each invoice, list the invoice number, > invoice date, item ID, description, and category for each item that makes up the invoice. > > FILETREE ~/workspace query.sql ~ query.sql 1 SELECT 1. INVOICE_NUMBER, 1. INVOICE_DATE, c.ID, c.FIRST_NAME, c.LAST_NAME 2 FROM INVOICE i 3 JOIN CUSTOMER c ON i.CUSTOMER_ID = c. ID; 4 SELECT invoice. INVOICE_NUM, customer.CUST_ID, customer. FIRST_NAME, customer.LAST_NAME 5 FROM invoice 6 JOIN customer ON invoice.CUST_ID = customer.CUST_ID 7 WHERE invoice. INVOICE_DATE= '2021-11-15'; 8 SELECT Invoice. InvoiceNumber, Invoice. InvoiceDate, InvoiceLine. ItemID, InvoiceLine.Quantity. InvoiceLine. QuotedPrice 9 FROM Invoice 10 JOIN InvoiceLine ON Invoice. InvoiceNumber = InvoiceLine. InvoiceNumber; 11 SELECT c. ID, c.first_name, c.last_name 12 FROM customer c 13 WHERE c.ID IN ( 14 15 16 17 ) 10 CELECT TO x SELECT DISTINCT i.customer_id + FROM invoice i WHERE i.invoice_date = '2021-11-15 SQL Viewer KimTay D X > Terminal + ERROR 1146 (42SO2) at line 1: Table 'KimTay.INVOICE' doesn't exist Task 8: Repeat Task 7, but this time order the rows by category and then by invoice number. > Lab 5-1: Kim Tay Pet Supplies T = ... % ? Instructions Page 3 of 4 Task 9: Use a subquery to find the sales rep ID, first name, and last name of each sales rep who represents at least one customer with a credit limit of $500. List each sales rep only once in the results. Task 10: Repeat Task 9, but this time do not use a subquery. Task 11: Find the ID, first name, and last name of each customer that currently has an invoice on file for Wild Bird Food (25 lb). Task 12: List the item ID, description, and category for each pair of items that are in the same category. (For example, one such pair would be item FS42 and item PF19, because the category for both items is FSH.) Order the output by category. Task 13: List the invoice number and invoice date for each invoice created for the customer James Gonzalez. Task 14: List the invoice number and invoice date for each invoice that contains an invoice line for a Wild Bird Food (25 lb). Task 15: List the invoice number and invoice date for each invoice that either was created for James Gonzalez or that contains an invoice line for Wild Bird Food (25lb). 3/4 < = > > > > > FILETREE ~/workspace query.sql query.sql 1 SELECT 1. INVOICE_NUMBER, 1. INVOICE_DATE, c.ID, c.FIRST_NAME, c.LAST_NAME 2 FROM INVOICE i 3 JOIN CUSTOMER c ON i.CUSTOMER_ID = c. ID; 4 SELECT invoice. INVOICE_NUM, customer.CUST_ID, customer. FIRST_NAME, customer.LAST_NAME 5 FROM invoice 6 JOIN customer ON invoice.CUST_ID = customer.CUST_ID 7 WHERE invoice. INVOICE_DATE= '2021-11-15'; 8 SELECT Invoice. InvoiceNumber, Invoice. InvoiceDate, InvoiceLine. ItemID, InvoiceLine.Quantity, InvoiceLine. QuotedPrice 9 FROM Invoice 10 JOIN InvoiceLine ON Invoice. InvoiceNumber = InvoiceLine. InvoiceNumber; 11 SELECT c.ID, c.first_name, c.last_name 12 FROM customer c 13 WHERE c.ID IN ( 14 15 16 17 ) 10 SELECT ID KimTay SELECT DISTINCT i.customer_id + SQL Viewer X + FROM invoice i WHERE i.invoice_date = '2021-11-15 9 X first name. Toot >_ Terminal + ERROR 1146 (42S02) at line 1: Table 'KimTay.INVOICE' doesn't exist

Answer rating: 100% (QA)

It appears youve shown me a series of images with data tables and a task list that seems to be from ... View the full answer