SQL Queries for E-Commerce Database

This assignment presents 12 solved SQL queries designed to interact with an e-commerce database. The queries cover various operations like selecting customer information alongside their invoices, finding the customer with the highest total invoice amount, listing the number of invoices per customer, retrieving specific invoice details based on a customer code, and identifying products with quantities exceeding a certain threshold. Additionally, the queries showcase fetching vendor names associated with products and calculating product-related metrics.

Student ID –
Date -
Database Assignment
Module Tutor –

Report
Queries
Q-1:
select INV_NUMBER as InvoiceNo, sum(LINE_TOTAL) as SumOfLine from Line group
by INV_NUMBER;

Q-2:
select V_Code as CodeOfVendor, V_Name as NameOfVendor, V_Contact as
ContactOfVendor, V_Areacode as CodeOfArea,
V_Phone as PhoneOfVendor, V_State as StateOfVendor, V_Order as OrderOfVendor from
vendor where v_state is null;

Q-3:
select Cus_Code as CodeOfCustomer, Cus_Lname as LastNameOfCustomer, Cus_Fname as
FirstNameOfCustomer,
Cus_Initial as InitialOfCustomer, Cus_Areacode as AreaCodeOfCustomer, Cus_Phone as
PhoenOfCustomer,
Cus_Balance as BalanceOfCustomer from customer where cus_balance>200 and
cus_balance<300;

Q-4:
select customer.Cus_Code as CodeOfCustomer, Cus_Lname as LastNameOfCustomer,
Cus_Fname as FirstNameOfCustomer,
Cus_Phone as PhoenOfCustomer, Cus_Balance as BalanceOfCustomer, INV_TOTAL as
InTotal
from customer inner join Invoice on Customer.cus_code=Invoice.cus_code
having INV_TOTAL>200;

Q-5:
select distinct cus_fname as FirstNameOfCustomer, cus_lname as LastNameOfCustomer
from customer inner join Invoice on Customer.cus_code=Invoice.cus_code;

Q-6:
select cus_fname as FirstNameOfCustomer, cus_lname as LastNameOfCustomer ,
INV_TOTAL as InTotal
from customer inner join Invoice on Customer.cus_code=Invoice.cus_code
having INV_TOTAL>(Select avg(INV_TOTAL) from Invoice);

Q-7:
select cus_fname as FirstNameOfCustomer, cus_lname as LastNameOfCustomer
from customer where cus_code not in (select cus_code from invoice);

Q-8:
select cus_fname as FirstNameOfCustomer, cus_lname as LastNameOfCustomer ,
INV_TOTAL as InTotal
from customer inner join Invoice on Customer.cus_code=Invoice.cus_code
having INV_TOTAL=(Select max(INV_TOTAL) from Invoice);

Q-9:
select concat(cus_fname,' ' , cus_lname) as NameOfCustomer, count(invoice.cus_code) as
NumberOfInvoices from customer
inner join invoice on customer.cus_code=invoice.cus_code group by cus_fname, cus_lname;

Q-10:
Select invoice.inv_number,cus_code as CodeOfCustomer, P_code as CodeOfProduct
from invoice inner join Line on invoice.inv_number=Line.inv_number
having Cus_Code='10018';

Q-11:
Select V_Name as NameOfVendor from Vendor where V_Code in (Select V_Code from
Product);

Q-12:
Select Product.P_CODE as CodeOfProduct, P_DESCRIPT as DescOfProduct,
P_INDATE as DateOfInvoive, P_QOH as QuantityOnHand, P_MIN, P_PRICE as
PriceOfProduct, P_DISCOUNT as DiscountOfProduct
, LINE_UNITS as LineOfUnits
from Product inner join Line on Product.p_code=Line.p_code where LINE_UNITS>10;