ITC556 - Database Systems: Assessment 5 - Query and Update Databases using SQL

ITC556 - Database Systems
Assessment 5-Query and update databases using SQL
1

Table of Contents
Part A- DML....................................................................................................................................4
Q-1)..............................................................................................................................................4
Q-2)..............................................................................................................................................4
Q-3)..............................................................................................................................................5
Q-4)..............................................................................................................................................5
Q-5)..............................................................................................................................................5
Q-6)..............................................................................................................................................6
Q-7)..............................................................................................................................................7
Q-8)..............................................................................................................................................7
Q 9)..............................................................................................................................................8
Part B- DDL.....................................................................................................................................9
Q-1)..............................................................................................................................................9
Q-2)............................................................................................................................................10
Q-3)............................................................................................................................................10
List of Figures
Figure 1- Query1............................................................................................................................4
Figure 2- Query1 Result Generated.............................................................................................4
Figure 3- Query 2...........................................................................................................................4
Figure 4- Query2 Result Generated.............................................................................................4
Figure 5- Query 3...........................................................................................................................5
Figure 6- Query3 Result Generated.............................................................................................5
Figure 7- Query 4...........................................................................................................................5
Figure 8- Query 4 Result...............................................................................................................5
Figure 9- Query 5...........................................................................................................................6
Figure 10- Query 5 Result Generated..........................................................................................6
Figure 11- Query 6.........................................................................................................................6
Figure 12- Query 6 Result Generated..........................................................................................6
Figure 13- Query 7.........................................................................................................................7
Figure 14- Query 7 Result Generated..........................................................................................7
Figure 15- Query 8.........................................................................................................................7
Figure 16- Query 8 Result Generated..........................................................................................8
Figure 17- Query 9.........................................................................................................................8
Figure 18- Query 9 Result Generated..........................................................................................8
2

Figure 20- Part B Query 1.............................................................................................................9
Figure 21- Part B Query 1 Result.................................................................................................9
Figure 22- Part B Query 2...........................................................................................................10
Figure 23- Part B Query 2 Result...............................................................................................10
Figure 24- Part B Query 3...........................................................................................................10
Figure 25- Part B Query 3 Result...............................................................................................11
3

Part A- DML
Q-1)
Select * FROM product AS pro where pro.P_CODE LIKE "22%";
Figure 1- Query1
Figure 2- Query1 Result Generated
Q-2)
Select * FROM vendor AS ven where ven.V_AREACODE= 615
Figure 3- Query 2
Figure 4- Query2 Result Generated
4

Q-3)
Select CUS_FNAME AS Customer_first_name, CUS_LNAME AS Customer_last_name,
CUS_BALANCE AS Customer_balance FROM customer WHERE CUS_LNAME="Williams"
and CUS_FNAME="George"
Figure 5- Query 3
Figure 6- Query3 Result Generated
Q-4)
SELECT COUNT(product.P_CODE) AS Number_of_product, vendor.V_CODE FROM product
INNER JOIN vendor ON product.V_CODE=vendor.V_CODE GROUP BY vendor.V_CODE
Having Number_of_product > 2
Figure 7- Query 4
Figure 8- Query 4 Result
Q-5)
Select vendor.V_CODE AS Vendor_code, vendor.V_NAME AS Vendor_name,
COUNT(product.P_CODE)AS Products_in_total, vendor.V_CONTACT AS Vendor_contact
FROM vendor JOIN product ON vendor.V_CODE=product.V_CODE GROUP BY
vendor.V_CODE
5

Figure 9- Query 5
Figure 10- Query 5 Result Generated
Q-6)
Select cus.CUS_FNAME, cus.CUS_LNAME FROM customer AS cus where
cus.CUS_BALANCE= 0
Figure 11- Query 6
Figure 12- Query 6 Result Generated
6

Q-7)
Select customer.CUS_LNAME AS Customer_Last_Name, customer.CUS_FNAME AS
Customer_First_Name from customer join invoice on
customer.CUS_CODE=invoice.CUS_CODE
Figure 13- Query 7
Figure 14- Query 7 Result Generated
Q-8)
Select customer.CUS_CODE, customer.CUS_FNAME, customer.CUS_LNAME FROM
customer where NOT EXISTS(Select * FROM invoice where invoice.CUS_CODE=
customer.CUS_CODE)
Figure 15- Query 8
7

Figure 16- Query 8 Result Generated
Q 9)
SELECT COUNT(customer.CUS_CODE) AS Invoices_generated, customer.CUS_FNAME AS
Customer_first_name, customer.CUS_LNAME AS Customer_last_name FROM customer join
invoice ON customer.CUS_CODE=invoice.CUS_CODE GROUP BY invoice.CUS_CODE
ORDER BY Invoices_generated
Figure 17- Query 9
Figure 18- Query 9 Result Generated
8

Part B- DDL
Q-1)
Alter Table customer add email_address VARCHAR(30)
Figure 19- Part B Query 1
Figure 20- Part B Query 1 Result
9

Q-2)
Update customer set cus.email_address = '[e-mail]' where CUS_CODE = 10016
Figure 21- Part B Query 2
Figure 22- Part B Query 2 Result
Q-3)
Delete from emp where EMP_LNAME="Johnson" AND EMP_FNAME="Edward"
Figure 23- Part B Query 3
10

Figure 24- Part B Query 3 Result
11