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