Database Queries Assignment - SQL Queries - Semester 1

This database assignment focuses on SQL queries to retrieve, analyze, and manipulate data. The assignment includes various queries to extract information from different tables, such as customer, vendor, invoice, and product tables. The queries cover tasks like selecting specific fields, filtering data based on conditions, joining tables to retrieve combined data, and calculating aggregate values. The solutions demonstrate how to use SQL commands like SELECT, FROM, WHERE, JOIN, GROUP BY, and HAVING to accomplish these tasks. The assignment covers a range of database operations, including retrieving customer information, vendor details, invoice totals, and product data, providing a comprehensive understanding of SQL query techniques. This solution is perfect for students seeking to improve their SQL skills and understand how to solve database-related problems.

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;