ITC556 Database Systems: SQL Queries and Updates - Data Handling

This document presents a solution to an ITC556 Database Systems assignment focusing on SQL queries and database updates. The assignment is divided into two parts: Data Manipulation Language (DML) and Data Definition Language (DDL). The DML part includes various SELECT queries to retrieve specific data from the 'product', 'vendor', and 'customer' tables, using functions like LIKE, COUNT, and concatenation. The DDL part demonstrates how to alter a table by adding a new column ('email_address' to the 'customer' table), update records within the table, and delete records based on specified conditions. The solution provides the SQL queries along with their corresponding outputs, offering a practical guide to SQL operations. Desklib provides a platform to explore more solved assignments and past papers.

ITC556 - Database Systems
Assessment 5: QUERY AND UPDATE DATABASES USING SQL
Student Name: Jagjeet Singh Tiwana
Student ID: 11661567

Jagjeet Singh Tiwana 11661567
Table of Contents
Part A- Data Manipulation Language........................................................................................3
Q-1)........................................................................................................................................3
Q-2)........................................................................................................................................3
Q-3)........................................................................................................................................4
Q-4)........................................................................................................................................4
Q-5)........................................................................................................................................5
Q-6)........................................................................................................................................5
Q-7)........................................................................................................................................6
Q-8)........................................................................................................................................6
Q 9).........................................................................................................................................7
Part B- Data Definition Language..............................................................................................8
Q-1)........................................................................................................................................8
Q-2)........................................................................................................................................8
Q-3)......................................................................................................................................10
1

Jagjeet Singh Tiwana 11661567
List of Figures
Figure 1- Query1..............................................................................................................................4
Figure 2- Query1 Output.................................................................................................................4
Figure 3- Query 2.............................................................................................................................4
Figure 4- Query2 Output.................................................................................................................5
Figure 5- Query 3.............................................................................................................................5
Figure 6- Query3 Output.................................................................................................................5
Figure 7- Query 4.............................................................................................................................5
Figure 8- Query 4 Output................................................................................................................6
Figure 9- Query 5.............................................................................................................................6
Figure 10- Query 5 Output..............................................................................................................6
Figure 11- Query 6...........................................................................................................................7
Figure 12- Query 6 Output..............................................................................................................7
Figure 13- Query 7...........................................................................................................................7
Figure 14- Query 7 Output..............................................................................................................8
Figure 15- Query 8...........................................................................................................................8
Figure 16- Query 8 Output..............................................................................................................8
Figure 17- Query 9...........................................................................................................................9
Figure 18- Query 9 Output..............................................................................................................9
Figure 20- Query 1 Part B.............................................................................................................10
Figure 21- Query 1 Part B Output.................................................................................................10
Figure 22- Query 2 Part B.............................................................................................................11
Figure 23- Query 2 Part B Output.................................................................................................11
Figure 24- Query 3 Part B.............................................................................................................12
Figure 25- Query 3 Part B Output.................................................................................................12
2

Jagjeet Singh Tiwana 11661567
Part A- Data Manipulation Language
Q-1)
Select * from product where P_CODE LIKE '22%';
Figure 1- Query1
Figure 2- Query1 Output
Q-2)
Select * FROM vendor as v where v.V_AREACODE LIKE 615;
Figure 3- Query 2
3

Jagjeet Singh Tiwana 11661567
Figure 4- Query2 Output
Q-3)
Select CUS_BALANCE AS Balance_for_George_Williams from customer where
CUS_FNAME='George' and CUS_LNAME='Williams';
Figure 5- Query 3
Figure 6- Query3 Output
Q-4)
SELECT vendor.V_NAME as VendorName, COUNT(product.P_CODE) AS
NumberOFproducts, vendor.V_CODE as VendorCode FROM product Inner join vendor ON
product.V_CODE=vendor.V_CODE group by vendor.V_CODE Having
NumberOFproducts> 2 order by NumberOFproducts DESC;
Figure 7- Query 4
Figure 8- Query 4 Output
4

Jagjeet Singh Tiwana 11661567
Q-5)
Select vendor.V_CODE as Code, vendor.V_NAME as Name, vendor.V_CONTACT as
Phone_number, COUNT(product.P_CODE) as NumberOFproducts FROM vendor Inner join
product ON vendor.V_CODE= product.V_CODE group by vendor.V_CODE order by
NumberOFproducts asc;
Figure 9- Query 5
Figure 10- Query 5 Output
Q-6)
Select CUS_FNAME || '-' || CUS_LNAME as Customer_name from customer where
CUS_BALANCE= 0;
Figure 11- Query 6
Figure 12- Query 6 Output
5

Jagjeet Singh Tiwana 11661567
Q-7)
Select CUS_FNAME || ' ' || CUS_LNAME as Customer_name from customer Inner join
invoice on customer.CUS_CODE=invoice.CUS_CODE;
Figure 13- Query 7
Figure 14- Query 7 Output
Q-8)
Select CUS_FNAME || ' ' || CUS_LNAME as Customer_name, customer.CUS_CODE as
Code FROM customer where CUS_CODE not in(Select CUS_CODE from invoice);
Figure 15- Query 8
Figure 16- Query 8 Output
6

Jagjeet Singh Tiwana 11661567
Q 9)
SELECT customer.CUS_FNAME || ' ' || customer.CUS_LNAME as Customer_name,
count(customer.CUS_CODE) AS InvoiceGenerated from customer join invoice ON
customer.CUS_CODE=invoice.CUS_CODE group by invoice.CUS_CODE;
Figure 17- Query 9
Figure 18- Query 9 Output
7

Jagjeet Singh Tiwana 11661567
Part B- Data Definition Language
Q-1)
ALTER TABLE customer add email_address Varchar (30);
Figure 19- Query 1 Part B
Figure 20- Query 1 Part B Output
Q-2)
UPDATE customer SET email_address = '[e-mail]' WHERE CUS_CODE =
10016;
Figure 21- Query 2 Part B
8

Jagjeet Singh Tiwana 11661567
Figure 22- Query 2 Part B Output
9

Jagjeet Singh Tiwana 11661567
Q-3)
DELETE from emp where emp.EMP_LNAME='Johnson' and emp.EMP_FNAME='Edward';
Figure 23- Query 3 Part B
Figure 24- Query 3 Part B Output
10