ITC556 - Database Systems: SQL Querying and Data Manipulation

This document presents a solution to a database systems assignment, specifically focusing on SQL queries and database updates using Data Manipulation Language (DML) and Data Definition Language (DDL). The solution includes a series of SQL queries designed to retrieve and manipulate data from a database, demonstrated through screenshots of both the queries and their corresponding results. The DML part covers tasks such as selecting data based on specific criteria, joining tables, using aggregate functions, and concatenating fields. The DDL part includes altering tables by adding columns and updating data within those columns. The assignment solution provides practical examples of SQL commands and their application in a database environment. Desklib provides many more solved assignments and past papers for students.

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