Database Assignment Solution - SQL Queries (Module Name, University)

This document presents a comprehensive solution to a database assignment, focusing on SQL queries. The assignment covers a range of database concepts, including retrieving data from multiple tables, using aggregate functions, filtering data based on specific conditions, and using subqueries to perform complex data retrieval tasks. The solution includes SQL queries for tasks such as calculating invoice totals, retrieving customer information, joining tables, and filtering data based on various criteria. The document also provides solutions for more advanced queries involving subqueries, such as finding customers who have not placed any orders, and using aggregate functions with joins. This assignment is a valuable resource for students studying database management systems and SQL.

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

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

Q-2:
select V_Code VCode, V_Name VName, V_Contact VContact, V_Areacode AreaCode,
V_Phone Phone, V_State State, V_Order VOrder from vendor where v_state is null;
3 | P a g e

Q-3:
select Cus_Code CustCode, Cus_Lname CustLastName, Cus_Fname CustFirstName,
Cus_Initial CustInitial, Cus_Areacode CustAreaCode, Cus_Phone CustPhone,
Cus_Balance CustBalance
from customer where cus_balance>200 and cus_balance<300;
4 | P a g e

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

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

Q-8:
select cus_fname CustFirstName, cus_lname CustLastName, 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 concat(cus_fname,' ' , cus_lname) CustFullName, count(invoice.cus_code)
NumOfIvoices 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 InvNumber,cus_code CustCode, P_code ProdCode
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 VName from Vendor where V_Code in (Select V_Code from Product);
12 | P a g e

Q-12:
Select Product.P_CODE ProdCode, P_DESCRIPT ProdDescription, P_INDATE InvDate,
P_QOH QtyOnHand, P_MIN Minimum, P_PRICE ProdPrice, P_DISCOUNT ProdDiscount,
LINE_UNITS LineUnits
from Product inner join Line on Product.p_code=Line.p_code where LINE_UNITS>10;
13 | P a g e