Database Assignment Report: SQL Queries and Solutions, University ABC

This document presents a comprehensive solution to a database assignment focused on SQL queries. It includes the SQL code for eleven distinct queries, each designed to retrieve specific information from a database. The queries cover a range of functionalities, including data aggregation (summing line totals), filtering based on conditions (e.g., vendor state, customer balance), joining tables (customer and invoice), and using subqueries to refine results. The assignment addresses tasks such as identifying customers with balances within a specific range, retrieving customer and invoice data, finding distinct customer names, calculating average invoice totals, identifying customers who have not placed invoices, determining maximum invoice totals, counting invoices per customer, and retrieving product and vendor information. The solutions are designed to provide students with practical examples of SQL query construction and database interaction.

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;