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