SQL Queries for Database Management

This assignment presents 12 SQL queries that demonstrate different database operations. The queries cover topics such as joining tables (customer and invoice), finding the maximum invoice total, calculating the number of invoices per customer, retrieving specific invoices based on a customer code, and more. These examples illustrate how to use SQL for data retrieval, analysis, and manipulation within a relational database.

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

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

Q-2:
select V_Code VCode, V_Name VName, V_Contact VContact, V_Areacode AreaCode,
V_Phone Phone, V_State State, V_Order VOrder from vendor where v_state is null;
3 | P a g e

Q-3:
select Cus_Code CustCode, Cus_Lname CustLastName, Cus_Fname CustFirstName,
Cus_Initial CustInitial, Cus_Areacode CustAreaCode, Cus_Phone CustPhone,
Cus_Balance CustBalance
from customer where cus_balance>200 and cus_balance<300;
4 | P a g e

Q-4:
select distinct cus_fname CustFirstName, cus_lname CustLastName, INV_TOTAL
TotalInvoice
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 CustFirstName, cus_lname CustLastName
from customer inner join Invoice on Customer.cus_code=Invoice.cus_code;
6 | P a g e

Q-6:
select cus_fname CustFirstName, cus_lname CustLastName, INV_TOTAL TotalInvoice
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 cus_fname CustFirstName, cus_lname CustLastName
from customer where cus_code not in (select cus_code from invoice);
8 | P a g e

Q-8:
select cus_fname CustFirstName, cus_lname CustLastName, INV_TOTAL TotalInvoice
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 concat(cus_fname,' ' , cus_lname) CustFullName, count(invoice.cus_code)
NumOfIvoices 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 InvNumber,cus_code CustCode, P_code ProdCode
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 VName from Vendor where V_Code in (Select V_Code from Product);
12 | P a g e

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