ITC556 Assignment: SQL Queries for Database Analysis and Reporting

Verified

Added on 2024/07/01

AI Summary

ITC556 - ASSIGNMENT 5: QUERY
DATABASES USING SQL

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.

1. Show sums of line units for each invoice.
SELECT INV_NUMBER, SUM (LINE_UNITS)
FROM LINE
GROUP BY INV_NUMBER;
Screenshot: -
2. Show the details of the products that do not have a value for the
attribute v_code.
SELECT * FROM PRODUCT
WHERE V_CODE=" ";
1

Screenshot: -
3. Show the details of the invoices whose subtotal is greater than 25 but
less than 75.
SELECT *
FROM INVOICE
WHERE INV_SUBTOTAL>25 AND INV_SUBTOTAL<75;
Screenshot: -
2

4. Show the details of the invoice who has the minimum subtotal.
SELECT INV_NUMBER, CUS_CODE, INV_DATE, MIN(INV_SUBTOTAL),
INV_TAX, INV_TOTAL
FROM INVOICE
WHERE INV_SUBTOTAL=(SELECT MIN(INV_SUBTOTAL) FROM INVOICE);
Screenshot: -
5. Show the codes and names of the vendors who supplied products.
SELECT DISTINCT VENDOR.V_CODE, V_NAME
FROM VENDOR, PRODUCT
WHERE VENDOR.V_CODE=PRODUCT.V_CODE;
Screenshot: -
3

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.

6. Using EXCEPT show the codes of the vendors who did not supply
any products.
SELECT VENDOR.V_CODE
FROM VENDOR
LEFT JOIN PRODUCT ON VENDOR.V_CODE=PRODUCT.V_CODE
WHERE VENDOR.V_CODE NOT IN (SELECT V_CODE FROM PRODUCT);
Screenshot: -
4

7. Using ‘NOT IN’ show the codes and names of the vendors who did
not supply any products.
SELECT VENDOR.V_CODE , VENDOR.V_NAME
FROM VENDOR
WHERE VENDOR.V_CODE NOT IN (SELECT V_CODE FROM PRODUCT);
Screenshot: -
5

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, COUNT (V_CODE) AS NO_OF_SUPPLIES
FROM PRODUCT
group by V_CODE ;
screenshot: -
6

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser

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 VENDOR.V_NAME, PRODUCT.V_CODE, COUNT
(PRODUCT.V_CODE) AS NO_OF_SUPPLIES
FROM VENDOR, PRODUCT
WHERE VENDOR.V_CODE IN (PRODUCT.V_CODE)
GROUP BY PRODUCT.V_CODE;
Screenshot: -
7

10.List all purchases by customer 10011.
SELECT INVOICE.INV_NUMBER, CUSTOMER.CUS_CODE,
PRODUCT.P_CODE, PRODUCT.P_DESCRIPT
FROM CUSTOMER, LINE, INVOICE, PRODUCT
WHERE CUSTOMER.CUS_CODE=10011 AND
CUSTOMER.CUS_CODE=INVOICE.CUS_CODE
AND INVOICE.INV_NUMBER=LINE.INV_NUMBER AND
LINE.P_CODE=PRODUCT.P_CODE;
Screenshot: -
8

11.Show the details of the employees who are located in area code 615.
SELECT *
FROM EMP
WHERE EMP_AREACODE=615;
Screenshot: -
9

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.

12.Using inner join, list the details of the products whose line price is
greater than 100.
SELECT PRODUCT.P_CODE, PRODUCT.P_DESCRIPT, PRODUCT.P_INDATE,
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;
Screenshot: -
10

References
 Yang, L., & Cao, L. (2016). The Effect of MySQL Workbench in Teaching Entity-
Relationship Diagram (ERD) to Relational Schema Mapping. International Journal of
Modern Education and Computer Science, 8(7), 1.
 Zhang, P. (2017). Practical Guide for Oracle SQL, T-SQL and MySQL. CRC Press.
12