SQL Queries for Customer and Invoice Data

This assignment presents a set of 12 SQL queries designed to test your understanding of basic database operations. The queries primarily focus on retrieving data related to customers, invoices, products, and vendors from various tables. You'll need to use JOIN clauses to combine data from different tables, apply aggregate functions, and utilize HAVING clauses for filtering results based on specific conditions. These examples are useful for practicing and mastering essential SQL concepts.

Student ID –
Date -
Module Tutor –
Database Assignment

Report
Queries
Q-1:
select l.INV_NUMBER InvNo, sum(l.LINE_TOTAL) SumLine from Line l group by
INV_NUMBER;

Q-2:
select v.V_Code as CodeVendor, v.V_Name as NameVendor, v.V_Contact as
ContactVendor, v.V_Areacode as CodeArea,
v.V_Phone as PhoneVendor, v.V_State as StateVendor, v.V_Order as OrderVendor from
vendor v where v.v_state is null;

Q-3:
select c.Cus_Code as CodeCustomer, c.Cus_Lname as LastNameCustomer, c.Cus_Fname as
FirstNameCustomer,
c.Cus_Initial as InitialCustomer, c.Cus_Areacode as AreaCodeCustomer, c.Cus_Phone as
PhoneCustomer,
c.Cus_Balance as BalanceCustomer from customer c where c.cus_balance>200 and
c.cus_balance<300;

Q-4:
select c.Cus_Code as CodeCustomer, c.Cus_Lname as LastNameCustomer, c.Cus_Fname as
FirstNameCustomer,
c.Cus_Phone as PhoneCustomer, c.Cus_Balance as BalanceCustomer, i.INV_TOTAL as
InvTotal
from customer c inner join Invoice i on c.cus_code=i.cus_code
having INV_TOTAL>200;

Q-5:
select distinct c.cus_fname as FirstNameCustomer, c.cus_lname as LastNameCustomer
from customer c inner join Invoice i on c.cus_code=i.cus_code;

Q-6:
select c.cus_fname as FirstNameCustomer, c.Cus_Lname as LastNameCustomer ,
i.INV_TOTAL as InvTotal
from customer c inner join Invoice i on c.cus_code=i.cus_code
having i.INV_TOTAL>(Select avg(INV_TOTAL) from Invoice);

Q-7:
select c.cus_fname as FirstNameCustomer, c.Cus_Lname as LastNameCustomer
from customer c where c.cus_code not in (select cus_code from invoice);

Q-8:
select c.cus_fname as FirstNameCustomer, c.Cus_Lname as LastNameCustomer ,
i.INV_TOTAL as InvTotal
from customer c inner join Invoice i on c.cus_code=i.cus_code
having i.INV_TOTAL=(Select max(INV_TOTAL) from Invoice);

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

Q-10:
Select i.inv_number as invNo, i.cus_code as CodeCustomer, l.P_code as CodeProduct
from invoice i inner join Line l on i.inv_number=l.inv_number
having i.Cus_Code='10018';

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

Q-12:
Select p.P_CODE as CodeProduct, p.P_DESCRIPT as DescProduct, p.P_INDATE as
DateInvoive, p.P_QOH as QuantityHand,
p.P_MIN as min, p.P_PRICE as PriceProduct, p.P_DISCOUNT as DiscountProduct,
l.LINE_UNITS as LineUnits
from Product p inner join Line l on p.p_code=l.p_code where l.LINE_UNITS>10;