ITC556 Database Systems: SQL Queries for Data Manipulation and Update

This document presents a solution to an assignment focused on SQL queries for database updates and manipulation, covering both Data Manipulation Language (DML) and Data Definition Language (DDL) aspects. The DML part includes queries for selecting data based on various criteria, such as product codes, area codes, and customer names, as well as aggregate functions like counting products and grouping vendors. It also covers joining tables to retrieve combined data from customer, vendor, product, and invoice tables. The DDL part demonstrates how to alter tables by adding columns, update data within tables, and delete records based on specified conditions. The document includes figures showing the SQL queries and the corresponding results generated from their execution. Desklib offers a wide range of past papers and solved assignments for students.

ITC556 - Database Systems
Assessment 5-Query and update databases using SQL
1

Table of Contents
Part A- DML....................................................................................................................................4
Q-1)..............................................................................................................................................4
Q-2)..............................................................................................................................................4
Q-3)..............................................................................................................................................5
Q-4)..............................................................................................................................................5
Q-5)..............................................................................................................................................5
Q-6)..............................................................................................................................................6
Q-7)..............................................................................................................................................7
Q-8)..............................................................................................................................................7
Q 9)..............................................................................................................................................8
Part B- DDL.....................................................................................................................................9
Q-1)..............................................................................................................................................9
Q-2)............................................................................................................................................10
Q-3)............................................................................................................................................10
List of Figures
Figure 1- Query1............................................................................................................................4
Figure 2- Query1 Result Generated.............................................................................................4
Figure 3- Query 2...........................................................................................................................4
Figure 4- Query2 Result Generated.............................................................................................4
Figure 5- Query 3...........................................................................................................................5
Figure 6- Query3 Result Generated.............................................................................................5
Figure 7- Query 4...........................................................................................................................5
Figure 8- Query 4 Result...............................................................................................................5
Figure 9- Query 5...........................................................................................................................6
Figure 10- Query 5 Result Generated..........................................................................................6
Figure 11- Query 6.........................................................................................................................6
Figure 12- Query 6 Result Generated..........................................................................................6
Figure 13- Query 7.........................................................................................................................7
Figure 14- Query 7 Result Generated..........................................................................................7
Figure 15- Query 8.........................................................................................................................7
Figure 16- Query 8 Result Generated..........................................................................................8
Figure 17- Query 9.........................................................................................................................8
Figure 18- Query 9 Result Generated..........................................................................................8
2

Figure 20- Part B Query 1.............................................................................................................9
Figure 21- Part B Query 1 Result.................................................................................................9
Figure 22- Part B Query 2...........................................................................................................10
Figure 23- Part B Query 2 Result...............................................................................................10
Figure 24- Part B Query 3...........................................................................................................10
Figure 25- Part B Query 3 Result...............................................................................................11
3

Part A- DML
Q-1)
Select * FROM product AS pro where pro.P_CODE LIKE "22%";
Figure 1- Query1
Figure 2- Query1 Result Generated
Q-2)
Select * FROM vendor AS ven where ven.V_AREACODE= 615
Figure 3- Query 2
Figure 4- Query2 Result Generated
4

Q-3)
Select CUS_FNAME AS Customer_first_name, CUS_LNAME AS Customer_last_name,
CUS_BALANCE AS Customer_balance FROM customer WHERE CUS_LNAME="Williams"
and CUS_FNAME="George"
Figure 5- Query 3
Figure 6- Query3 Result Generated
Q-4)
SELECT COUNT(product.P_CODE) AS Number_of_product, vendor.V_CODE FROM product
INNER JOIN vendor ON product.V_CODE=vendor.V_CODE GROUP BY vendor.V_CODE
Having Number_of_product > 2
Figure 7- Query 4
Figure 8- Query 4 Result
Q-5)
Select vendor.V_CODE AS Vendor_code, vendor.V_NAME AS Vendor_name,
COUNT(product.P_CODE)AS Products_in_total, vendor.V_CONTACT AS Vendor_contact
FROM vendor JOIN product ON vendor.V_CODE=product.V_CODE GROUP BY
vendor.V_CODE
5

Figure 9- Query 5
Figure 10- Query 5 Result Generated
Q-6)
Select cus.CUS_FNAME, cus.CUS_LNAME FROM customer AS cus where
cus.CUS_BALANCE= 0
Figure 11- Query 6
Figure 12- Query 6 Result Generated
6

Q-7)
Select customer.CUS_LNAME AS Customer_Last_Name, customer.CUS_FNAME AS
Customer_First_Name from customer join invoice on
customer.CUS_CODE=invoice.CUS_CODE
Figure 13- Query 7
Figure 14- Query 7 Result Generated
Q-8)
Select customer.CUS_CODE, customer.CUS_FNAME, customer.CUS_LNAME FROM
customer where NOT EXISTS(Select * FROM invoice where invoice.CUS_CODE=
customer.CUS_CODE)
Figure 15- Query 8
7

Figure 16- Query 8 Result Generated
Q 9)
SELECT COUNT(customer.CUS_CODE) AS Invoices_generated, customer.CUS_FNAME AS
Customer_first_name, customer.CUS_LNAME AS Customer_last_name FROM customer join
invoice ON customer.CUS_CODE=invoice.CUS_CODE GROUP BY invoice.CUS_CODE
ORDER BY Invoices_generated
Figure 17- Query 9
Figure 18- Query 9 Result Generated
8

Part B- DDL
Q-1)
Alter Table customer add email_address VARCHAR(30)
Figure 19- Part B Query 1
Figure 20- Part B Query 1 Result
9

Q-2)
Update customer set cus.email_address = '[e-mail]' where CUS_CODE = 10016
Figure 21- Part B Query 2
Figure 22- Part B Query 2 Result
Q-3)
Delete from emp where EMP_LNAME="Johnson" AND EMP_FNAME="Edward"
Figure 23- Part B Query 3
10

Figure 24- Part B Query 3 Result
11