CSC 345 Lab 5-1 Exam | Questions with Verified Answers

Document Content and Description Below

CSC 345 Lab 5-1 Exam | Questions with Verified Answers 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. SELECT INVOICE_NUM, INVOICE_DATE, CUSTOMER.CUST_ID, FIRST_NAME, LAST_NAME FROM INVOICES, CUSTOMER WHERE INVOICES.CUST_ID = CUSTOMER.CUST_ID; 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. SELECT INVOICE_NUM, CUSTOMER.CUST_ID, FIRST_NAME, LAST_NAME FROM INVOICES, CUSTOMER WHERE (INVOICES.CUST_ID = CUSTOMER.CUST_ID) AND (INVOICE_DATE = "2021-11-15"); 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. SELECT INVOICES.INVOICE_NUM, INVOICE_DATE, ITEM_ID, QUANTITY, QUOTED_PRICE FROM INVOICES, INVOICE_LINE WHERE (INVOICES.INVOICE_NUM = INVOICE_LINE.INVOICE_NUM); 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. SELECT CUST_ID, FIRST_NAME, LAST_NAME FROM CUSTOMER WHERE CUST_ID IN (SELECT CUST_ID FROM INVOICES WHERE INVOICE_DATE = "2021-11-15"); Task 5: Repeat Task 4, but this time use the EXISTS operator in your answer. SELECT CUST_ID, FIRST_NAME, LAST_NAME FROM CUSTOMER WHERE EXISTS (SELECT * FROM INVOICES WHERE (INVOICES.CUST_ID = CUSTOMER.CUST_ID) AND (INVOICE_DATE = "2021-11-15")); Task 6: Find the ID, first name, and last name of each customer for which an invoice was not created on November 15, 2021. SELECT CUST_ID, FIRST_NAME, LAST_NAME FROM CUSTOMER WHERE CUST_ID IN (SELECT CUST_ID FROM INVOICES WHERE INVOICE_DATE <> "2021-11-15"); Task 7: For each invoice, list the invoice number, invoice date, item ID, description, and category for each item that makes up the invoice. SELECT INVOICES.INVOICE_NUM, INVOICE_DATE, ITEM.ITEM_ID, DESCRIPTION, CATEGORY FROM INVOICES, INVOICE_LINE, ITEM WHERE (INVOICES.INVOICE_NUM = INVOICE_LINE.INVOICE_NUM) AND (INVOICE_LINE.ITEM_ID = ITEM.ITEM_ID); Task 8: Repeat Task 7, but this time order the rows by category and then by invoice number. SELECT INVOICES.INVOICE_NUM, INVOICE_DATE, ITEM.ITEM_ID, DESCRIPTION, CATEGORY FROM INVOICES, INVOICE_LINE, ITEM WHERE (INVOICES.INVOICE_NUM = INVOICE_LINE.INVOICE_NUM) AND (INVOICE_LINE.ITEM_ID = ITEM.ITEM_ID) ORDER BY CATEGORY, INVOICE_NUM; 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. SELECT REP_ID, FIRST_NAME, LAST_NAME FROM SALES_REP WHERE EXISTS (SELECT * FROM CUSTOMER WHERE CREDIT_LIMIT = 500 AND CUSTOMER.REP_ID = SALES_REP.REP_ID) Task 10: Repeat Task 9, but this time do not use a subquery. SELECT SALES_REP.REP_ID, SALES_REP.FIRST_NAME, SALES_REP.LAST_NAME FROM SALES_REP, CUSTOMER WHERE CUSTOMER.REP_ID = SALES_REP.REP_ID AND CREDIT_LIMIT = 500; 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). SELECT CUST_ID, FIRST_NAME, LAST_NAME FROM CUSTOMER WHERE CUST_ID IN (SELECT CUST_ID FROM INVOICES WHERE INVOICE_NUM IN (SELECT INVOICE_NUM FROM INVOICE_LINE WHERE ITEM_ID = "KH81")); [Show More]

Last updated: 8 months ago

Preview 3 out of 5 pages