SQL Database Queries and Operations - Assignment 5 - Semester 2018

This assignment provides solutions to various SQL database queries, covering tasks such as calculating sums of line units for invoices, identifying products without specific vendor codes, filtering invoices based on subtotal ranges, finding the invoice with the minimum subtotal, and listing vendors who supplied products. It also demonstrates the use of EXCEPT and NOT IN clauses to find vendors who did not supply any products, listing vendors and the number of products they supplied, and retrieving purchases by a specific customer. Furthermore, the assignment includes queries to show details of employees in a specific area code and utilizes inner joins to list products with a line price greater than 100. The solutions are designed to help students understand and apply SQL for effective database management. Find more solved assignments and past papers on Desklib.

1. Show sums of line units for each invoice.
Query:
SELECT INV_NUMBER,SUM(LINE_UNITS) AS "Sum of Line Units" FROM line GROUP BY
INV_NUMBER;
Result:
2. Show the details of the products that do not have a value for the attribute v_code.
Query:
SELECT * FROM product WHERE V_CODE ='';
Result:

3. Show the details of the invoices whose subtotal is greater than 25 but less than 75.
Query:
SELECT * FROM invoice WHERE INV_SUBTOTAL>25 AND INV_SUBTOTAL<75;
Result:
4. Show the details of the invoice who has the minimum subtotal.
Query:
SELECT * FROM invoice WHERE INV_SUBTOTAL = (SELECT MIN(INV_SUBTOTAL) FROM invoice);
Result:

5. Show the codes and names of the vendors who supplied products.
Query:
SELECT V_CODE, V_NAME FROM vendor WHERE V_CODE IN (SELECT V_CODE FROM product);
Result:
6. Using EXCEPT show the codes of the vendors who did not supply any products.
Query:
SELECT V_CODE FROM vendor WHERE V_CODE NOT IN (SELECT V_CODE FROM product);
Result:

7. Using ‘NOT IN’ show the codes and names of the vendors who did not supply any products.
Query:
SELECT V_CODE,V_NAME FROM vendor WHERE V_CODE NOT IN (SELECT V_CODE FROM
product);
Result:
8. List the codes of vendors and the number of products each vendor has supplied, i.e. vendor XXX has
supplied xxx products, and vendor YYY has supplied yyy products etc.
Query:
SELECT CONCAT("Vendor ", V_CODE, " has supplied ",COUNT(*), " products") AS Message FROM
PRODUCT WHERE V_CODE <> '' GROUP BY V_CODE;
Result:

9. List the names and codes of vendors and the number of products each vendor has supplied, i.e.
vendor XXX has supplied xxx products, and vendor YYY has supplied yyy products etc.
Query:
SELECT CONCAT(v.V_NAME," - ", v.V_CODE, " has supplied ",COUNT(*), " products") AS Message
FROM PRODUCT p, VENDOR v WHERE v.V_CODE=p.V_CODE AND v.V_CODE <> '' GROUP BY
v.V_NAME, v.V_CODE;
Result:
10. List all purchases by customer 10011.
Query:
SELECT * FROM invoice WHERE CUS_CODE='10011';
Result:

11. Show the details of the employees who are located in area code 615.
Query:
SELECT * FROM emp WHERE EMP_AREACODE='615';
Result:
12. Using inner join, list the details of the products whose line price is greater than 100.
Query:
SELECT product.P_CODE,product.P_DESCRIPT, product.P_INDATE, product.P_QOH,
product.P_MIN, product.P_PRICE,product.P_DISCOUNT FROM product INNER JOIN line ON
product.P_CODE=line.P_CODE WHERE line.LINE_PRICE>100;
Result: