ITS26000Lab6 (2)
docx
keyboard_arrow_up
School
Purdue University, Northwest *
*We aren’t endorsed by this school
Course
CYBER SECU
Subject
Information Systems
Date
Dec 6, 2023
Type
docx
Pages
3
Uploaded by ChefPencil9883
ITS 26000 – Applied Database Tech
Lab 06 – (50 points)
Questions: Module 5
Exercises
KimTay Pet Supplies: 2,3 ( total - 20 points for 2 question)
Critical thinking : 2 - (20 points – 1 Question)
StayWell: 1
– (total - 10 points for 1 question)
KimTay Pet Supplies
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. (10 points)
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");
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. (10 points)
SELECT INVOICES.INVOICE_NUM, INVOICE_DATE, ITEM_ID, QUANTITY, QUOTED_PRICE
FROM INVOICES, INVOICE_LINE
WHERE (INVOICES.INVOICE_NUM = INVOICE_LINE.INVOICE_NUM);
Critical Thinking
2. For each sales rep, list the ID, first name, and last name for the customer, along with the sales rep first name, and
sales rep last name. All reps should be included in the results. Order the results by rep ID. There are two SQL
commands for this query that lists the same results. Create and run each SQL command. (20 points)
SELECT R.REP_ID, R.FIRST_NAME AS REP_FIRST, R.LAST_NAME AS REP_LAST, C.CUST_ID,
C.FIRST_NAME AS CUSTOMER_FIRST, C.LAST_NAME AS CUSTOMER_LAST
FROM CUSTOMER C
LEFT JOIN SALES_REP R
ON C.REP_ID = R.REP_ID
ORDER BY R.REP_ID;
StayWell Student Accommodation
1.
For every property, list the management office number, address, monthly rent, owner number, owner’s first
name, and owner’s last name. (10 points)
Deliverables: Document Report with question and answer (either word or pdf file) need to be uploaded into
Brightspace in one submission
SELECT P.PROPERTY_ID, P.OFFICE_NUM, P.ADDRESS, P.MONTHLY_RENT, P.OWNER_NUM,
O.FIRST_NAME, O.LAST_NAME
From PROPERTY as P
Inner Join OWNER as O
on P.OWNER_NUM = O.OWNER_NUM;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help