ITC556 Database Systems: SQL DML, DDL Queries and Updates Solution

This document presents a comprehensive solution to an ITC556 Database Systems assignment, focusing on SQL queries and updates. The assignment is divided into two parts: DML (Data Manipulation Language) and DDL (Data Definition Language). The DML part includes nine queries that demonstrate various SQL operations such as selecting data based on specific criteria (using LIKE and WHERE clauses), joining tables (INNER JOIN), grouping data (GROUP BY), and using aggregate functions (COUNT). The DDL part covers creating and modifying database schemas, including adding a new column to a table (ALTER TABLE), updating data in a table (UPDATE), and deleting data from a table (DELETE). Screenshots of the query outputs are included to verify the correctness of the solutions. This solved assignment is available on Desklib, where students can find similar resources to aid their studies.

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