ITC556 Database Systems: SQL Querying for Data Retrieval and Analysis

Verified

Added on 2024/07/01

AI Summary

This assignment solution for ITC556 Database Systems demonstrates various SQL queries for data retrieval and analysis. The solution includes SQL statements for tasks such as summing line units for each invoice, identifying products without a vendor code, filtering invoices based on subtotal values, finding the invoice with the minimum subtotal, and listing vendors who supplied products. It also covers using EXCEPT and NOT IN to find vendors who did not supply any products, listing the number of products supplied by each vendor, listing purchases by a specific customer, showing employees in a specific area code, and using inner joins to list products with line prices greater than 100. The assignment leverages SQL functionalities like aggregate functions (SUM, MIN, COUNT), joins (INNER JOIN, LEFT JOIN), and subqueries to address different data querying requirements. Desklib offers more solved assignments and resources for students.

ITC556
DATABASE SYSTEM
ASSESSMENT 5
STUDENT NAME-
STUDENT ID-

Paraphrase This Document

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

Write and run SQL statements to complete the following tasks
1. Show sums of line units for each invoice.
select INV_NUMBER, sum(LINE_UNITS) AS sum_of_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=" ";
Screenshot:
1

3. Show the details of the invoices whose subtotal is greater than 25 but less than
75.
select *
from invoice
where 25<INV_SUBTOTAL and INV_SUBTOTAL<75;
Screenshot:
2

4. Show the details of the invoice who has the minimum subtotal.
select *
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 v.V_CODE, v.V_NAME
from vendor v
inner join product p on
v.V_CODE=p.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 v.V_CODE
from vendor v
left join product p on v.V_CODE=p.V_CODE
where v.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 distinct v.V_CODE,v.V_NAME
from vendor v, product p
where v.V_CODE not in(select V_CODE from product);
Screenshot:
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 p.V_CODE, count(p.V_CODE) as supplied_products
from product p
group by p.V_CODE;
Screenshot:
5

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 v.V_NAME, v.V_CODE, count(p.V_CODE) as supplied_products
from product p, vendor v
where v.V_CODE=p.V_CODE
group by v.V_NAME;
Screenshot:
10. List all purchases by customer 10011.
6

Paraphrase This Document

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

select c.CUS_CODE,l.P_CODE
from customer c, line l, invoice i
where c.CUS_CODE=i.CUS_CODE and i.INV_NUMBER=l.INV_NUMBER and
c.CUS_CODE=10011;
Screenshot:
11. Show the details of the employees who are located in area code 615.
select * from emp where EMP_AREACODE=615;
Screenshot:
7

12. Using inner join, list the details of the products whose line price is greater than
100.
select p.P_CODE, p.P_DESCRIPT, p.P_INDATE, p.P_QOH, p.P_MIN, p.P_PRICE,
p.P_discount
from product p
inner join line l
on l.P_CODE=p.P_CODE
where l.LINE_PRICE>100;
Screenshot:
8

Paraphrase This Document

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

References
 Lutus, P. (2018). MySQL Tutorial 1: Overview, Tables, Queries. Retrieved from
https://arachnoid.com/MySQL/
 MySQL :: MySQL Tutorial :: 7 Examples of Common Queries. (2018). Retrieved
from https://dev.mysql.com/doc/mysql-tutorial-excerpt/5.5/en/examples.html
 MySQL Tutorial - MySQL By Examples for Beginners. (2018). Retrieved from
https://www.ntu.edu.sg/home/ehchua/programming/sql/MySQL_Beginner.html
 Procedures, M., Views, M., Triggers, M., Indexes, M., Administration, M., & Search,
M. et al. (2018). Using MySQL SELECT Statement to Query Data. Retrieved from
http://www.mysqltutorial.org/mysql-select-statement-query-data.aspx
10