CSC 345 Lab 5-1
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"));
