Question

Database Systems

Lab Exercises

1. Populate the Dept table with the following:

Dept_Code	Dept_Name
ICS	Information and Computer Science
COE	Computer Engineering
SWE	Software Engineering
SE	System Engineering

2. Populate the Faculty table with the following:

Faculty_Id	Last_Name	First_Name	Dept
100234	Hashim	Ahmad	ICS
287234	Yoesuf	Mohammed	COE
767636	Amn	Faisal	ICS
557899	Hamzah	Yusuf	SE
345256	Lukman	Mousa	SWE
626277	Ali	Isa	COE
246266	Dawood	Ageel	SE

3. Change the last name of Faculty Id = 767636 to "Ameen".
4. Change the Dept of Mr. Ali to 'MGT'.
5. Delete Faculty Id 557899.
6. Delete Dept_Code 'SWE' on Dept table.

Answer 1

Below are the sql queries for given requirement

Query to populate Dept table

insert into Dept(Dept_Code, Dept_Name) values('ICS', 'Information and Computer Science');
insert into Dept(Dept_Code, Dept_Name) values('COE', 'Computer Engineering');
insert into Dept(Dept_Code, Dept_Name) values('SWE', 'Software Engineering');
insert into Dept(Dept_Code, Dept_Name) values('SE', 'System Engineering');

Query to populate Faculty table

insert into Faculty(Faculty_Id, Last_Name, First_Name, Dept) values(100234, 'Hashim', 'Ahmad', 'ICS');
insert into Faculty(Faculty_Id, Last_Name, First_Name, Dept) values(287234, 'Yoesuf', 'Mohammed', 'COE');
insert into Faculty(Faculty_Id, Last_Name, First_Name, Dept) values(767636, 'Amn', 'Faisal', 'ICS');
insert into Faculty(Faculty_Id, Last_Name, First_Name, Dept) values(557899, 'Hamzah', 'Yusuf', 'SE');
insert into Faculty(Faculty_Id, Last_Name, First_Name, Dept) values(345256, 'Luckman', 'Mousa', 'SWE');
insert into Faculty(Faculty_Id, Last_Name, First_Name, Dept) values(626277, 'Ali', 'Isa', 'COE');
insert into Faculty(Faculty_Id, Last_Name, First_Name, Dept) values(246266, 'Dawood', 'Ageel', 'SE');

Below are the sql statements.

Change the last name of Faculty Id = 767636 to "Ameen".

Update Faculty set Last_Name = 'Ameen' where Faculty_id = 767636;

Change the Dept of Mr. Ali to 'MGT'.

Update Faculty set Dept = 'MGT' where Last_Name = 'Ali';

Delete Faculty Id 557899.

DELETE FROM Faculty where Faculty_id = 557899;

Delete Dept_Code 'SWE' on Dept table.

DELETE FROM Dept where Dept_Code = 'SWE';