Database Queries Assignment Solution - Database Module

This document presents a comprehensive solution to a database assignment, focusing on SQL queries. The assignment includes a series of queries designed to retrieve and manipulate data from a database. The solution covers a range of SQL commands, including SELECT statements with various clauses like GROUP BY, WHERE, and JOIN, along with subqueries. Specific queries address tasks such as calculating invoice totals, retrieving vendor and customer information, filtering data based on specific criteria, and joining tables to extract relevant information. The solution demonstrates the application of SQL to solve practical database problems, providing insights into data retrieval, filtering, and aggregation. The document also shows how to use aggregate functions such as SUM, COUNT, and AVG. Furthermore, the assignment includes solutions for complex queries involving joins and subqueries, showcasing the versatility of SQL in data management. Overall, the document serves as a valuable resource for understanding and implementing SQL queries in database environments.

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