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

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

Table of Contents
Part A- DML....................................................................................................................................3
Part B- DDL...................................................................................................................................12
List of Figures
Figure 1- Query1 Part A..................................................................................................................3
Figure 2- Query1 Part A Result.......................................................................................................3
Figure 3- Query 2 Part A.................................................................................................................4
Figure 4- Query2 Part A Result.......................................................................................................4
Figure 5- Query 3 Part A.................................................................................................................5
Figure 6- Query3 Part A Result.......................................................................................................5
Figure 7- Query 4 Part A.................................................................................................................6
Figure 8- Query 4 Part A Result......................................................................................................6
Figure 9- Query 5 Part A.................................................................................................................7
Figure 10- Query 5 Part A Result....................................................................................................7
Figure 11- Query 6 Part A...............................................................................................................8
Figure 12- Query 6 Part A Result....................................................................................................8
Figure 13- Query 7 Part A...............................................................................................................9
Figure 14- Query 7 Part A Result....................................................................................................9
Figure 15- Query 8 Part A.............................................................................................................10
Figure 16- Query 8 Part A Result..................................................................................................10
Figure 17- Query 9 Part A.............................................................................................................11
Figure 18- Query 9 Part A Result..................................................................................................11
Figure 19- Customer Table before Query......................................................................................12
Figure 20- Query 1 Part B.............................................................................................................12
Figure 21- Query 1 Part B Result..................................................................................................12
Figure 22- Query 2 Part B.............................................................................................................13
Figure 23- Query 2 Part B Result..................................................................................................13
Figure 24- Query 2 Part B Result..................................................................................................13
Figure 25- Query 3 Part B.............................................................................................................14
Figure 26- Query 3 Part B Result..................................................................................................14
2

Part A- DML
Q-1) Select * from assignment5.product where SUBSTRING(P_CODE,1,2)='22';
Figure 1- Query1 Part A
Figure 2- Query1 Part A Result
3

Q-2) Select * from assignment5.vendor where vendor.V_AREACODE= 615;
Figure 3- Query 2 Part A
Figure 4- Query2 Part A Result
4

Q-3) Select cus.CUS_FNAME, cus.CUS_LNAME, cus.CUS_BALANCE FROM
assignment5.customer AS cus
WHERE cus.CUS_FNAME="George" AND cus.CUS_LNAME="Williams";
Figure 5- Query 3 Part A
Figure 6- Query3 Part A Result
Q-4) SELECT v.V_NAME, COUNT(p.P_CODE)AS Total_number_of_products,
IF(COUNT(p.P_CODE)> 2, v.V_CODE, "")
FROM assignment5.vendor AS v JOIN assignment5.product AS p ON v.V_CODE=p.V_CODE
GROUP BY v.V_CODE;
5

Figure 7- Query 4 Part A
Figure 8- Query 4 Part A Result
6

Q-5) SELECT v.V_CODE AS Vendor_code, v.V_NAME AS Vendor_Name, v.V_CONTACT
AS Vendor_contact, COUNT(p.P_CODE)AS Total_number_of_products
FROM assignment5.vendor AS v INNER JOIN assignment5.product AS p ON
v.V_CODE=p.V_CODE GROUP BY v.V_CODE
Figure 9- Query 5 Part A
Figure 10- Query 5 Part A Result
7

Q-6) SELECT cus.CUS_CODE, cus.CUS_FNAME, cus.CUS_LNAME,cus.CUS_AREACODE,
cus.CUS_PHONE
from assignment5.customer AS cus where cus.CUS_BALANCE= 0;
Figure 11- Query 6 Part A
Figure 12- Query 6 Part A Result
8

Q-7) Select cus.CUS_FNAME AS First_Name, cus.CUS_LNAME AS Last_Name from
customer
AS cus join invoice AS inv on cus.CUS_CODE=inv.CUS_CODE;
Figure 13- Query 7 Part A
Figure 14- Query 7 Part A Result
9

Q-8) SELECT cus.CUS_CODE AS Customer_Code, cus.CUS_FNAME AS First_Name,
cus.CUS_LNAME AS Last_Name
FROM customer AS cus WHERE cus.CUS_CODE NOT IN(SELECT inv.CUS_CODE FROM
invoice AS inv)
Figure 15- Query 8 Part A
Figure 16- Query 8 Part A Result
10

Q 9) SELECT COUNT(cus.CUS_CODE) AS Number_of_times_customer_generated_invoice,
cus.CUS_FNAME AS First_name, cus.CUS_LNAME AS Last_name
FROM customer AS cus JOIN invoice AS inv ON inv.CUS_CODE=cus.CUS_CODE
GROUP BY inv.CUS_CODE ORDER BY Number_of_times_customer_generated_invoice
Figure 17- Query 9 Part A
Figure 18- Query 9 Part A Result
11

Part B- DDL
Q-1) ALTER TABLE assignment5.customer ADD email_address VARCHAR(30) NOT NULL;
Figure 19- Customer Table before Query
Figure 20- Query 1 Part B
Figure 21- Query 1 Part B Result
12

Q-2) UPDATE assignment5.customer AS cus set cus.email_address = '[e-mail]'
where cus.CUS_CODE = 10016
Figure 22- Query 2 Part B
Figure 23- Query 2 Part B Result
Figure 24- Query 2 Part B Result
13

Q-3) Delete from assignment5.emp where EMP_FNAME="Edward" AND
EMP_LNAME="Johnson"
Figure 25- Query 3 Part B
Figure 26- Query 3 Part B Result
14