ITC556 Database Systems: SQL Querying and Updating Databases

This document presents a comprehensive solution to an ITC556 Database Systems assignment, focusing on SQL Data Manipulation Language (DML) and Data Definition Language (DDL) queries. The DML part includes SELECT statements with various conditions, joins, and aggregate functions to retrieve specific data from the database, such as selecting products based on a substring of their code, finding vendors in a specific area code, and retrieving customer information. The DDL part demonstrates how to modify the database schema by adding a new column to a table, updating existing records, and deleting records based on specified criteria. Each query is accompanied by its corresponding result, providing a clear understanding of the query's effect on the database. Desklib offers this document along with a wealth of other solved assignments and past papers to support students in their studies.

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