ITSS 4300 - Lab 4 Using the script file provided, write and execute the followin
ID: 3919815 • Letter: I
Question
ITSS 4300 - Lab 4
Using the script file provided, write and execute the following queries. Write the query in this document and provide a screen shot of the results.
Provide a list of customers who have not purchased an invoice. Display the customer code, their last name and their first name. Sort by cus_code in ascending order.
SELECT CUS_CODE, CUS_LNAME, CUS_FNAME
FROM CUSTOMER
WHERE CUS_CODE NOT IN (SELECT CUS_CODE FROM INVOICE WHERE CUS_CODE IS NOT NULL)
ORDER BY CUS_CODE ASC
What is the average price of all invoices ? Round to 2 decimal places. Add a column alias for the column name.
SELECT (ROUND(AVG(LINE_PRICE), 2)) AS "Average Invoice Price"
FROM LINE
GROUP BY INV_NUMBER
Count the number of products that are supplied by each vendor. Display the vendor name in alphabetical order.
SELECT V_NAME, COUNT(P_CODE)
FROM VENDOR V, PRODUCT P
WHERE V.V_CODE = P.V_CODE
GROUP BY V_NAME
ORDER BY V_NAME ASC
Modify the above query slightly to only show those vendors who supply more than 2 products.
SELECT V_NAME, COUNT(P_CODE)
FROM VENDOR V, PRODUCT P
WHERE V.V_CODE = P.V_CODE
HAVING COUNT(P_CODE) > 2
GROUP BY V_NAME
ORDER BY V_NAME ASC
Write a single query that will update the balance for all customers who have a balance greater than 500. Decrease the balance by 100.
UPDATE CUSTOMER
SET CUS_BALANCE = CUS_BALANCE - 100 WHERE CUS_BALANCE > 500
Explanation / Answer
You would need to install MySQL in your system and then create the tables required and insert some data into them. You can then run the following queries and attach a screenshot of the query outputs. Since I dont have the tables created in my MySQL, I cannot provide you the screenshot.
Q1) Provide a list of customers who have not purchased an invoice. Display the customer code, their last name and their first name. Sort by cus_code in ascending order.
A1)
SELECT CUS_CODE, CUS_LNAME, CUS_FNAME
FROM CUSTOMER
WHERE CUS_CODE NOT IN (SELECT CUS_CODE FROM INVOICE WHERE CUS_CODE IS NOT NULL)
ORDER BY CUS_CODE ASC;
Q2) What is the average price of all invoices ? Round to 2 decimal places. Add a column alias for the column name.
A2)
SELECT (ROUND(AVG(LINE_PRICE), 2)) AS "Average Invoice Price"
FROM LINE
GROUP BY INV_NUMBER;
Q3) Count the number of products that are supplied by each vendor. Display the vendor name in alphabetical order.
A3)
SELECT V_NAME, COUNT(P_CODE)
FROM VENDOR V, PRODUCT P
WHERE V.V_CODE = P.V_CODE
GROUP BY V_NAME
ORDER BY V_NAME ASC;
Q4)Modify the above query slightly to only show those vendors who supply more than 2 products.
A4)
SELECT V_NAME, COUNT(P_CODE)
FROM VENDOR V, PRODUCT P
WHERE V.V_CODE = P.V_CODE
HAVING COUNT(P_CODE) > 2
GROUP BY V_NAME
ORDER BY V_NAME ASC;
Q5) Write a single query that will update the balance for all customers who have a balance greater than 500. Decrease the balance by 100.
A5)
UPDATE CUSTOMER
SET CUS_BALANCE = CUS_BALANCE - 100 WHERE CUS_BALANCE > 500;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.