ITC556 - Database Systems: Assessment 5 - QUERY AND UPDATE DATABASES USING SQL

ITC556 - Database Systems
Assessment 5: QUERY AND UPDATE DATABASES USING SQL
Student Name: Jagjeet Singh Tiwana
Student ID: 11661567

Jagjeet Singh Tiwana 11661567
Table of Contents
Part A- Data Manipulation Language........................................................................................3
Q-1)........................................................................................................................................3
Q-2)........................................................................................................................................3
Q-3)........................................................................................................................................4
Q-4)........................................................................................................................................4
Q-5)........................................................................................................................................5
Q-6)........................................................................................................................................5
Q-7)........................................................................................................................................6
Q-8)........................................................................................................................................6
Q 9).........................................................................................................................................7
Part B- Data Definition Language..............................................................................................8
Q-1)........................................................................................................................................8
Q-2)........................................................................................................................................8
Q-3)......................................................................................................................................10
1

Jagjeet Singh Tiwana 11661567
List of Figures
Figure 1- Query1..............................................................................................................................4
Figure 2- Query1 Output.................................................................................................................4
Figure 3- Query 2.............................................................................................................................4
Figure 4- Query2 Output.................................................................................................................5
Figure 5- Query 3.............................................................................................................................5
Figure 6- Query3 Output.................................................................................................................5
Figure 7- Query 4.............................................................................................................................5
Figure 8- Query 4 Output................................................................................................................6
Figure 9- Query 5.............................................................................................................................6
Figure 10- Query 5 Output..............................................................................................................6
Figure 11- Query 6...........................................................................................................................7
Figure 12- Query 6 Output..............................................................................................................7
Figure 13- Query 7...........................................................................................................................7
Figure 14- Query 7 Output..............................................................................................................8
Figure 15- Query 8...........................................................................................................................8
Figure 16- Query 8 Output..............................................................................................................8
Figure 17- Query 9...........................................................................................................................9
Figure 18- Query 9 Output..............................................................................................................9
Figure 20- Query 1 Part B.............................................................................................................10
Figure 21- Query 1 Part B Output.................................................................................................10
Figure 22- Query 2 Part B.............................................................................................................11
Figure 23- Query 2 Part B Output.................................................................................................11
Figure 24- Query 3 Part B.............................................................................................................12
Figure 25- Query 3 Part B Output.................................................................................................12
2

Jagjeet Singh Tiwana 11661567
Part A- Data Manipulation Language
Q-1)
Select * from product where P_CODE LIKE '22%';
Figure 1- Query1
Figure 2- Query1 Output
Q-2)
Select * FROM vendor as v where v.V_AREACODE LIKE 615;
Figure 3- Query 2
3

Jagjeet Singh Tiwana 11661567
Figure 4- Query2 Output
Q-3)
Select CUS_BALANCE AS Balance_for_George_Williams from customer where
CUS_FNAME='George' and CUS_LNAME='Williams';
Figure 5- Query 3
Figure 6- Query3 Output
Q-4)
SELECT vendor.V_NAME as VendorName, COUNT(product.P_CODE) AS
NumberOFproducts, vendor.V_CODE as VendorCode FROM product Inner join vendor ON
product.V_CODE=vendor.V_CODE group by vendor.V_CODE Having
NumberOFproducts> 2 order by NumberOFproducts DESC;
Figure 7- Query 4
Figure 8- Query 4 Output
4

Jagjeet Singh Tiwana 11661567
Q-5)
Select vendor.V_CODE as Code, vendor.V_NAME as Name, vendor.V_CONTACT as
Phone_number, COUNT(product.P_CODE) as NumberOFproducts FROM vendor Inner join
product ON vendor.V_CODE= product.V_CODE group by vendor.V_CODE order by
NumberOFproducts asc;
Figure 9- Query 5
Figure 10- Query 5 Output
Q-6)
Select CUS_FNAME || '-' || CUS_LNAME as Customer_name from customer where
CUS_BALANCE= 0;
Figure 11- Query 6
Figure 12- Query 6 Output
5

Jagjeet Singh Tiwana 11661567
Q-7)
Select CUS_FNAME || ' ' || CUS_LNAME as Customer_name from customer Inner join
invoice on customer.CUS_CODE=invoice.CUS_CODE;
Figure 13- Query 7
Figure 14- Query 7 Output
Q-8)
Select CUS_FNAME || ' ' || CUS_LNAME as Customer_name, customer.CUS_CODE as
Code FROM customer where CUS_CODE not in(Select CUS_CODE from invoice);
Figure 15- Query 8
Figure 16- Query 8 Output
6

Jagjeet Singh Tiwana 11661567
Q 9)
SELECT customer.CUS_FNAME || ' ' || customer.CUS_LNAME as Customer_name,
count(customer.CUS_CODE) AS InvoiceGenerated from customer join invoice ON
customer.CUS_CODE=invoice.CUS_CODE group by invoice.CUS_CODE;
Figure 17- Query 9
Figure 18- Query 9 Output
7

Jagjeet Singh Tiwana 11661567
Part B- Data Definition Language
Q-1)
ALTER TABLE customer add email_address Varchar (30);
Figure 19- Query 1 Part B
Figure 20- Query 1 Part B Output
Q-2)
UPDATE customer SET email_address = '[e-mail]' WHERE CUS_CODE =
10016;
Figure 21- Query 2 Part B
8

Jagjeet Singh Tiwana 11661567
Figure 22- Query 2 Part B Output
9

Jagjeet Singh Tiwana 11661567
Q-3)
DELETE from emp where emp.EMP_LNAME='Johnson' and emp.EMP_FNAME='Edward';
Figure 23- Query 3 Part B
Figure 24- Query 3 Part B Output
10