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

ITC556
Database Systems
ASSESSMENT 5
QUERY AND UPDATE DATABASES USING SQL
Student Name: Karunakar Burugula
Student ID: 11613503

Karunakar Burugula 11613503
Table of Contents
Part A-DML............................................................................................................................................2
Query1...............................................................................................................................................2
Query2...............................................................................................................................................3
Query3...............................................................................................................................................4
Query4...............................................................................................................................................5
Query5...............................................................................................................................................6
Query6...............................................................................................................................................7
Query 7..............................................................................................................................................8
Query 8............................................................................................................................................10
Query 9............................................................................................................................................11
Part B-DDL...........................................................................................................................................12
Query1.............................................................................................................................................12
Query2.............................................................................................................................................13
Query3.............................................................................................................................................14
1

Karunakar Burugula 11613503
List of figure
Figure 1:Screenshot 1............................................................................................................................3
Figure 2: Query 1...................................................................................................................................3
Figure 3: screenshot 2...........................................................................................................................4
Figure 4: Query 2...................................................................................................................................4
Figure 5: Screenshot 3...........................................................................................................................5
Figure 6: Query 3...................................................................................................................................5
Figure 7: Screenshot 4...........................................................................................................................6
Figure 8: Query 4...................................................................................................................................6
Figure 9: Screenshot 5...........................................................................................................................7
Figure 10: Query 5.................................................................................................................................7
Figure 11: Screenshot 6.........................................................................................................................8
Figure 12: Query 6.................................................................................................................................8
Figure 13: Screenshot 7.........................................................................................................................9
Figure 14: Query 7...............................................................................................................................10
Figure 15: Screenshot 8.......................................................................................................................11
Figure 16: Query 8...............................................................................................................................11
Figure 17: Screenshot 9.......................................................................................................................12
Figure 18: Query 9...............................................................................................................................12
Figure 19: Screenshot 10.....................................................................................................................13
Figure 20: DDL-Query 1.......................................................................................................................13
Figure 21: Screenshot 11.....................................................................................................................14
Figure 22: DDL-Query 2.......................................................................................................................14
Figure 23: Screenshot 12.....................................................................................................................15
Figure 24: DDL-Query 3.......................................................................................................................15
2

Karunakar Burugula 11613503
Part A-DML
Query1
Select * FROM assignment5.product where assignment5.product.P_CODE LIKE '22%'
Figure 1:Screenshot 1
Figure 2: Query 1
3

Karunakar Burugula 11613503
Query2
select * from assignment5.vendor where vendor.V_AREACODE='615'
Figure 3: screenshot 2
Figure 4: Query 2
4

Karunakar Burugula 11613503
Query3
select customer.CUS_BALANCE from assignment5.customer where
concat(CUS_FNAME,CUS_LNAME)="GeorgeWilliams"
Figure 5: Screenshot 3
Figure 6: Query 3
5

Karunakar Burugula 11613503
Query4
select COUNT(product.P_CODE)AS NO_of_products,assignment5.vendor.V_NAME,
if(COUNT(product.P_CODE)>='2',vendor.V_CODE,"")AS vendor_code from assignment5.product
INNER JOIN assignment5.vendor ON assignment5.product.V_CODE=
assignment5.vendor.V_CODE GROUP BY assignment5.vendor.V_NAME
Figure 7: Screenshot 4
Figure 8: Query 4
6

Karunakar Burugula 11613503
Query5
select COUNT(product.P_CODE)AS
NO_of_products,assignment5.vendor.V_NAME,assignment5.vendor.V_CODE,assignment5.vendor.V
_PHONE
from assignment5.product INNER JOIN assignment5.vendor ON assignment5.product.V_CODE=
assignment5.vendor.V_CODE GROUP BY assignment5.vendor.V_NAME
Figure 9: Screenshot 5
Figure 10: Query 5
7

Karunakar Burugula 11613503
Query6
select * from assignment5.customer where customer.CUS_BALANCE='0'
Figure 11: Screenshot 6
Figure 12: Query 6
8

Karunakar Burugula 11613503
Query 7
select CONCAT(customer.CUS_FNAME,CUS_LNAME)as customer_name from assignment5.customer
INNER JOIN assignment5.invoice ON customer.CUS_CODE=invoice.CUS_CODE GROUP BY
customer_name
Figure 13: Screenshot 7
9

Karunakar Burugula 11613503
Figure 14: Query 7
10

Karunakar Burugula 11613503
Query 8
select CONCAT(customer.CUS_FNAME,customer.CUS_LNAME), customer.CUS_CODE from
assignment5.customer where customer.CUS_CODE NOT IN (select invoice.CUS_CODE from
assignment5.invoice)
Figure 15: Screenshot 8
Figure 16: Query 8
11

Karunakar Burugula 11613503
Query 9
select CONCAT(customer.CUS_FNAME,CUS_LNAME)as customer_name,
COUNT(invoice.INV_NUMBER)as no_of_invoice from assignment5.customer INNER JOIN
assignment5.invoice ON customer.CUS_CODE=invoice.CUS_CODE GROUP BY customer_name
Figure 17: Screenshot 9
Figure 18: Query 9
12

Karunakar Burugula 11613503
Part B-DDL
Query1
ALTER table assignment5.customer
ADD email_address varchar(30) NULL;
Figure 19: Screenshot 10
Figure 20: DDL-Query 1
13

Karunakar Burugula 11613503
Query2
UPDATE assignment5.customer set email_address='[e-mail]' where
customer.CUS_CODE='10016'
Figure 21: Screenshot 11
Figure 22: DDL-Query 2
14

Karunakar Burugula 11613503
Query3
delete from assignment5.emp where CONCAT(EMP_FNAME,' ',+EMP_LNAME)='Edward Johnson'
Figure 23: Screenshot 12
Figure 24: DDL-Query 3
15