ITC556 - Database System: Query and Update Databases Using SQL

ITC556 – Database System
Assessment Item5: QUERY AND UPDATE DATABASES USING SQL
Student Name: Saketh Chava
Student ID: 11613481

Saketh Chava 11613481
Contents
Part A-DML............................................................................................................................................2
Part B-DDL...........................................................................................................................................11
List of figures
Figure 1: Query 1 screenshot.................................................................................................................2
Figure 2: Result 1...................................................................................................................................2
Figure 3: Query 2 screenshot.................................................................................................................3
Figure 4: Result 2...................................................................................................................................3
Figure 5: Query 3 screenshot.................................................................................................................4
Figure 6: Result 3...................................................................................................................................4
Figure 7: Query4 screenshot..................................................................................................................5
Figure 8: Result 4...................................................................................................................................5
Figure 9: Query 5screenshot..................................................................................................................6
Figure 10: Result 5.................................................................................................................................6
Figure 11: Query6 screenshot................................................................................................................7
Figure 12:Result 6..................................................................................................................................7
Figure 13: Query 7 screenshot...............................................................................................................8
Figure 14: Result 7.................................................................................................................................8
Figure 15: Query 8 screenshot...............................................................................................................9
Figure 16: Result 8.................................................................................................................................9
Figure 17: Query 9 screenshot.............................................................................................................10
Figure 18: Result 9...............................................................................................................................10
Figure 19: DDL-Query1 screenshot......................................................................................................11
Figure 20: DDL-Result 1.......................................................................................................................11
Figure 21: DDL-Query 2screenshot......................................................................................................12
Figure 22: DDL Result 2........................................................................................................................12
Figure 23:DDL-Query 3 screenshot......................................................................................................13
Figure 24: DDL-Result 3.......................................................................................................................13
1

Saketh Chava 11613481
Part A-DML
Query1
Use assignment5;
select P_CODE,P_DESCRIPT,P_INDICATE,P_QOH,P_MIN,P_PTICE,P_DISCOUNT,V_CODE FROM
product where product.P_CODE LIKE '22%'
Figure 1: Query 1 screenshot
Figure 2: Result 1
2

Saketh Chava 11613481
Query2
use assignment5;
select * from vendor where vendor.V_AREACODE=615
Figure 3: Query 2 screenshot
Figure 4: Result 2
3

Saketh Chava 11613481
Query3
Use assignment5;
select CUS_BALANCE AS BALANCE from customer where concat(CUS_FNAME,'
',CUS_LNAME)="George Williams";
Figure 5: Query 3 screenshot
Figure 6: Result 3
4

Saketh Chava 11613481
Query4
use assignment5;
select vendor.V_NAME AS NAME,COUNT(product.P_CODE)AS total_products,
if(COUNT(product.P_CODE)>2,vendor.V_CODE,null)AS code from vendor INNER JOIN product ON
vendor.V_CODE= product.V_CODE
GROUP BY vendor.V_NAME order by total_products
Figure 7: Query4 screenshot
Figure 8: Result 4
5

Saketh Chava 11613481
Query5
Use assignment5;
select vendor.V_PHONE as PHONE,vendor.V_CODE AS CODE,vendor.V_NAME as
NAME,COUNT(product.P_CODE)AS total_products from vendor INNER JOIN product ON
product.V_CODE= vendor.V_CODE GROUP BY vendor.V_NAME order by CODE;
Figure 9: Query 5screenshot
Figure 10: Result 5
6

Saketh Chava 11613481
Query6
Use assignment5;
select CONCAT(CUS_FNAME,' ',CUS_LNAME) as NAME from customer where CUS_BALANCE='0'
order BY NAME
Figure 11: Query6 screenshot
Figure 12:Result 6
7

Saketh Chava 11613481
Query7
Use assignment5;
select CONCAT(CUS_FNAME,' ',CUS_LNAME) AS
name,customer.CUS_CODE,CUS_INITIAL,CUS_AREACODE,CUS_BALANCE from customer INNER JOIN
invoice ON customer.CUS_CODE=invoice.CUS_CODE order by CUS_INITIAL
Figure 13: Query 7 screenshot
Figure 14: Result 7
8

Saketh Chava 11613481
Query8
Use assignment5;
Select customer.CUS_CODE AS CODE ,CONCAT (customer.CUS_FNAME,' ',customer.CUS_LNAME) AS
NAME from customer where customer.CUS_CODE NOT IN (select invoice.CUS_CODE from
assignment5.invoice)
Figure 15: Query 8 screenshot
Figure 16: Result 8
9

Saketh Chava 11613481
Query 9
Use assignment5;
select COUNT(invoice.INV_NUMBER)as INVOICE,CONCAT(customer.CUS_FNAME,CUS_LNAME)as
NAME from customer INNER JOIN invoice ON customer.CUS_CODE=invoice.CUS_CODE GROUP BY
NAME ORDER BY INVOICE
Figure 17: Query 9 screenshot
Figure 18: Result 9
10

Saketh Chava 11613481
Part B-DDL
Query1
Use assignment5;
ALTER table customer ADD EMAIL_ADDRESS varchar (30);
Select * from customer;
Figure 19: DDL-Query1 screenshot
Figure 20: DDL-Result 1
11

Saketh Chava 11613481
Query2
Use assignment5;
UPDATE customer set EMAIL_ADDRESS='[e-mail]' where customer.CUS_CODE=10016;
Select *from customer;
Figure 21: DDL-Query 2screenshot
Figure 22: DDL Result 2
12

Saketh Chava 11613481
Query3
Use assignment5;
delete from emp where CONCAT(EMP_FNAME,' ',+EMP_LNAME)='Edward Johnson';
select * from emp;
Figure 23:DDL-Query 3 screenshot
Figure 24: DDL-Result 3
13