SQL Queries for Database Management

This assignment presents a set of 12 SQL query examples designed to test your understanding of fundamental database management concepts. Each query focuses on specific tasks like retrieving distinct customer information with maximum invoice totals, counting invoices per customer, selecting invoices based on a customer code, identifying product details with high quantity on hand, and more. These queries involve joining tables, using aggregate functions, and applying filters to manipulate and extract data effectively.

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