SQL Queries for Database Management and Data Retrieval Homework

This assignment provides a comprehensive solution to a set of SQL queries designed to manipulate and retrieve data from a database. The queries cover various aspects of database management, including data aggregation, filtering, joining, and subqueries. The solution demonstrates how to retrieve sums of line units for each invoice, identify products without vendor codes, show invoice details based on subtotal ranges, find invoices with minimum subtotals, list vendor details, and use EXCEPT and NOT IN operators to find vendors who did not supply products. The assignment also includes queries to list vendor codes and the number of products supplied, list vendor names, and retrieve product details based on customer purchases and employee locations. Finally, it uses inner joins to retrieve product details based on line prices.

Steps for create Database and open the Database in sqlite3

1. Show sums of line units for each invoice.
SELECT INV_NUMBER AS INVOICE_NUMBER, SUM(LINE_UNITS) AS 'LINE UNITS SUM' FROM LINE
GROUP BY INV_NUMBER;
2. Show the details of the products that do not have a value for the attribute v_code.
SELECT P_CODE AS Product_Code, P_DESCRIPT AS Product_Description, P_INDATE AS
Product_Indate, P_PRICE AS Product_Price FROM PRODUCT WHERE V_CODE ='';
3. Show the details of the invoices whose subtotal is greater than 25 but less than 75.
SELECT INV_NUMBER AS Invoice_Number, CUS_CODE AS Customer_Code, INV_DATE AS
Invoice_Date, INV_TOTAL AS Invoice_Total FROM INVOICE WHERE INV_SUBTOTAL BETWEEN 25
AND 75;
4. Show the details of the invoice who has the minimum subtotal.
SELECT INV_NUMBER AS Invoice_Number, CUS_CODE AS Customer_Code, INV_DATE AS
Invoice_Date, INV_TOTAL AS Invoice_Total FROM INVOICE WHERE INV_SUBTOTAL = (SELECT
MIN(INV_SUBTOTAL) FROM INVOICE);

5. Show the codes and names of the vendors who supplied products.
SELECT DISTINCT ven.V_CODE AS Vendor_Code, ven.V_NAME AS Vendor_Name FROM VENDOR
ven, PRODUCT prod WHERE prod.V_CODE=ven.V_CODE;
6. Using EXCEPT show the codes of the vendors who did not supply any products.
SELECT V_CODE AS Vendor_Code FROM VENDOR EXCEPT SELECT V_CODE FROM PRODUCT;
7. Using ‘NOT IN’ show the codes and names of the vendors who did not supply any products.
SELECT V_CODE AS Vendor_Code,V_NAME AS Vendor_Name FROM VENDOR WHERE V_CODE
NOT IN (SELECT V_CODE FROM PRODUCT);

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.
SELECT V_CODE AS 'VENDOR CODE', COUNT(*) AS 'NUMBER OF PRODUCTS' FROM PRODUCT
WHERE V_CODE<> '' GROUP BY V_CODE;
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.
SELECT ven.V_NAME AS 'VENDOR NAME', ven.V_CODE AS 'VENDOR CODE', COUNT(*) AS
'NUMBER OF PRODUCTS' FROM VENDOR ven, PRODUCT prod WHERE
prod.V_CODE=ven.V_CODE AND prod.V_CODE<> '' GROUP BY ven.V_NAME,ven.V_CODE;
10. List all purchases by customer 10011.
SELECT P_CODE AS Product_Code, P_DESCRIPT AS Product_Description, P_INDATE AS
Product_Indate, P_PRICE AS Product_Price FROM PRODUCT WHERE P_CODE IN (SELECT P_CODE
FROM LINE WHERE INV_NUMBER IN (SELECT INV_NUMBER FROM INVOICE WHERE
CUS_CODE='10011'));

11. Show the details of the employees who are located in area code 615.
SELECT EMP_NUM AS Employee_Number, EMP_LNAME AS Employee_LastName, EMP_FNAME
AS Employee_FirstName, EMP_DOB AS Emp_DateOfBirth, EMP_HIRE_DATE AS
Employee_Hire_Date FROM EMP WHERE EMP_AREACODE=615;
12. Using inner join, list the details of the products whose line price is greater than 100.
SELECT prod.P_CODE,prod.P_DESCRIPT,prod.P_INDATE,prod.P_PRICE,prod.P_DISCOUNT FROM
PRODUCT AS prod INNER JOIN LINE AS li ON prod.P_CODE=li.P_CODE WHERE li.LINE_PRICE>100;