Database Queries Assignment Solution - University Name, Spring 2024

This document presents a comprehensive solution to a database assignment focusing on SQL queries. The assignment includes a series of queries addressing various aspects of database management, such as selecting data, filtering records based on conditions, joining tables, and using aggregate functions. The solution covers queries related to customer and invoice data, product information, and vendor details. It addresses tasks like retrieving specific fields, calculating totals, filtering based on criteria, joining tables to extract related information, and using subqueries to perform more complex data retrieval operations. The assignment covers queries that use functions such as SUM, AVG, MAX, COUNT, and DISTINCT. The solution provides a clear understanding of how to formulate and execute SQL queries to extract meaningful information from a database. The assignment is designed to test understanding of SQL syntax and database concepts.

Student ID –
Date -
Database Assignment
Module Tutor –
1 | P a g e

Report
Queries
Q-1:
select INV_NUMBER, sum(LINE_TOTAL) as 'Line Total' from Line group by
INV_NUMBER;
2 | P a g e

Q-2:
select * from vendor where v_state is null;
3 | P a g e

Q-3:
select * from customer where cus_balance>200 and cus_balance<300;
4 | P a g e

Q-4:
select distinct customer.cus_code, cus_fname , cus_lname, cus_phone, cus_balance,
INV_TOTAL
from customer inner join Invoice on Customer.cus_code=Invoice.cus_code
having INV_TOTAL>200;
5 | P a g e

Q-5:
select distinct cus_fname , cus_lname
from customer inner join Invoice on Customer.cus_code=Invoice.cus_code;
6 | P a g e

Q-6:
select distinct cus_fname , cus_lname , INV_TOTAL
from customer inner join Invoice on Customer.cus_code=Invoice.cus_code
having INV_TOTAL>(Select avg(INV_TOTAL) from Invoice);
7 | P a g e

Q-7:
select distinct cus_fname , cus_lname from customer where cus_code not in (select cus_code
from invoice);
8 | P a g e

Q-8:
select distinct cus_fname , cus_lname , INV_TOTAL
from customer inner join Invoice on Customer.cus_code=Invoice.cus_code
having INV_TOTAL=(Select max(INV_TOTAL) from Invoice);
9 | P a g e

Q-9:
select distinct cus_fname , cus_lname, count(invoice.cus_code) as 'Invoices' from customer
inner join invoice on customer.cus_code=invoice.cus_code group by cus_fname, cus_lname;
10 | P a g e

Q-10:
Select invoice.inv_number,cus_code, P_code from invoice inner join Line on
invoice.inv_number=Line.inv_number
having Cus_Code='10018';
11 | P a g e

Q-11:
Select V_Name from Vendor where V_Code in (Select V_Code from Product);
12 | P a g e

Q-12:
Select Product.P_CODE, P_DESCRIPT, P_INDATE, P_QOH, P_MIN, P_PRICE,
P_DISCOUNT, LINE_UNITS
from Product inner join Line on Product.p_code=Line.p_code where LINE_UNITS>10;
13 | P a g e