SQL Queries for Business Data Analysis

This assignment presents 12 SQL queries focusing on analyzing business data related to customers, invoices, products, and vendors. The queries cover various aspects such as finding the customer with the highest invoice total, counting the number of invoices per customer, retrieving product details based on specific criteria, and identifying vendors associated with products. These examples demonstrate practical applications of SQL for extracting meaningful insights from business databases.

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

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

Q-2:
select V_Code 'VendorCode', V_Name 'VendorName', V_Contact 'VendorContact',
V_Areacode 'AreaCode',
V_Phone 'VendorPhone', V_State 'VendorState', V_Order 'VendorOrder' from vendor where
v_state is null;
3 | P a g e

Q-3:
select Cus_Code 'CustomerCode', Cus_Lname 'CustomerLastName', Cus_Fname
'CustomerFirstName',
Cus_Initial 'CustomerInitial', Cus_Areacode 'AreaCode', Cus_Phone 'CustomerPhone',
Cus_Balance 'CustomerBalance'
from customer where cus_balance>200 and cus_balance<300;
4 | P a g e

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

Q-6:
select distinct cus_fname 'CustomerFirstName', cus_lname 'CustomerLastName',
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 distinct cus_fname 'CustomerFirstName', cus_lname 'CustomerLastName'
from customer where cus_code not in (select cus_code from invoice);
8 | P a g e

Q-8:
select distinct cus_fname 'CustomerFirstName', cus_lname 'CustomerLastName',
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 distinct concat(cus_fname,' ' , cus_lname) as 'CustomerFullName',
count(invoice.cus_code) as 'Number Of 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 'InvoiceNumber',cus_code 'CustomerCode', P_code 'ProductCode'
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 'VendorName' from Vendor where V_Code in (Select V_Code from
Product);
12 | P a g e

Q-12:
Select Product.P_CODE 'ProductCode', P_DESCRIPT 'ProductDescription', P_INDATE
'InvoiceDate',
P_QOH 'QtyOnHand', 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