Database Queries for Data Analysis

This assignment focuses on various SQL queries designed for data analysis, including retrieving customer and invoice information, filtering data based on conditions, and aggregating results. It serves as a practical exercise for students to enhance their database management skills in the context of data science.

STUDENT DETAILS

1.
Query
SELECT invoice.INV_TOTAL FROM invoice;
2.
Query
SELECT * FROM vendor WHERE vendor.V_STATE IS NULL;

3.
Query
SELECT * FROM customer WHERE customer.CUS_BALANCE BETWEEN 200 and 300;

4.
Query
SELECT customer.*,invoice.INV_TOTAL FROM customer
INNER JOIN invoice ON customer.CUS_CODE=invoice.CUS_CODE
HAVING invoice.INV_TOTAL>200;

5.
Query
SELECT customer.CUS_FNAME,customer.CUS_LNAME FROM customer
WHERE customer.CUS_CODE IN (SELECT invoice.CUS_CODE FROM invoice);

6.
Query
SELECT customer.CUS_CODE,customer.CUS_FNAME,customer.CUS_LNAME FROM customer
INNER JOIN invoice ON customer.CUS_CODE=invoice.CUS_CODE
GROUP BY customer.CUS_CODE
HAVING SUM(invoice.INV_TOTAL)>(SELECT AVG(invoice.INV_TOTAL));

7.
Query
SELECT customer.CUS_FNAME,customer.CUS_LNAME FROM customer
WHERE customer.CUS_CODE NOT IN (SELECT invoice.CUS_CODE FROM invoice);

8.
Query
SELECT customer.CUS_CODE,invoice.INV_TOTAL FROM customer
INNER JOIN invoice ON customer.CUS_CODE=invoice.CUS_CODE
ORDER BY invoice.INV_TOTAL DESC
LIMIT 1;

9.
Query
SELECT customer.CUS_FNAME,customer.CUS_LNAME,COUNT(invoice.CUS_CODE) FROM
customer
INNER JOIN invoice ON customer.CUS_CODE=invoice.CUS_CODE
GROUP BY customer.CUS_CODE;

10.
Query
SELECT customer.CUS_FNAME,customer.CUS_LNAME,invoice.INV_NUMBER,product.P_CODE,
product.P_DESCRIPT FROM customer
INNER JOIN invoice ON customer.CUS_CODE=invoice.CUS_CODE
INNER JOIN line ON invoice.INV_NUMBER=line.INV_NUMBER
INNER JOIN product ON line.P_CODE=product.P_CODE
WHERE customer.CUS_CODE=10018;

11.
Query
SELECT vendor.V_NAME FROM vendor
LEFT JOIN product ON vendor.V_CODE=product.V_CODE
INNER JOIN line ON line.P_CODE=product.P_CODE
WHERE vendor.V_CODE IN (SELECT vendor.V_CODE FROM PRODUCT)
AND product.P_CODE IN (SELECT line.P_CODE FROM line)
GROUP BY vendor.V_NAME;

12.
Query
SELECT product.P_CODE,product.P_DESCRIPT,SUM(line.LINE_UNITS) FROM product
INNER JOIN line ON product.P_CODE=line.P_CODE
GROUP BY product.P_CODE
HAVING SUM(line.LINE_UNITS)>10;