ITC556 Assignment: SQL Queries for Database Analysis and Reporting

Verified

Added on 2024/07/01

AI Summary

This document presents the solutions to an assignment focused on querying databases using SQL. It includes a series of SQL queries designed to extract and manipulate data from a database, covering various aspects such as summing line units for each invoice, identifying products with missing vendor codes, filtering invoices based on subtotal ranges, finding the invoice with the minimum subtotal, and listing vendors who supplied products. The assignment also explores the use of EXCEPT and NOT IN clauses to identify vendors who did not supply any products, counts the number of products supplied by each vendor, lists purchases by a specific customer, retrieves employee details based on area code, and utilizes inner joins to find products with line prices greater than 100. Screenshots are included to demonstrate the output of each query. This resource is available on Desklib, a platform offering a variety of study tools and resources for students.

ITC556 - ASSIGNMENT 5: QUERY
DATABASES USING SQL

Paraphrase This Document

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

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

Paraphrase This Document

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

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

Paraphrase This Document

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

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