Arba Minch University
Sawla Campus
Department of Business Administration and
Information System
2015 E.C
DB System II Lab Practical Exam Time allowed: 2hrs
rd
Target group: 3 year BAIS Total weight: 15%
Name Data Type
Emp_ID Varchar (12), primary key
First_name char (10)
Middle_name char (11)
Last_name char (12)
Phone_num Numeric (10)
age int
DOB date
Salary Decimal (9,4)
1.Create a table called Employee information with the following structure
under database Employee.
Table 1: The Structure of the Table.
Emp id First Middle Last Phone Age Sex DOB Salary
Name Name Name Number
P001 Elias Petros Zegeye 0964887449 23 Male 2000- 8000
09-06
P002 Etagegn Ermias Eda 0934541771 25 Femal 1998- 3000
e 08-07
P003 Zekarias Abel Awel 0932433455 23 Male 2000- 5000
04-08
P004 Minyahi Petros Desse 0916544534 26 Male 1997- 4000
l 03-05
P005 Sada Amir Husien 0987655634 30 Femal 1993- 9000
e 02-03
Table 1: The Data to be Inserted.
I. Based on the Above tables write the appropriate SQL commands on the
answer sheet provided for each of the following questions.
A. Add a column sex with data type char and size 7 after column age to the
Employee information table (1 point).
Page | 1
Arba Minch University
Sawla Campus
Department of Business Administration and
Information System
2015 E.C
DB System II Lab Practical Exam Time allowed: 2hrs
Total weight: 15%
B. Manipulate the table with given data in table 2 after adding new column sex (1
point).
C. List the Employees in ascending order by first name (1 point).
D. Retrieve total number of employees as Number_of_Employees(1 point).
i.e
Number_of_Employees
5
E. Change the first name of employee to Zeleke and age to 29 where employee id
is p004(1 point).
F. Change primary key from Employee id to phone number (1 point).
G. Increase the salary of an Employee by 25% where first name ends by S.
(1.5pnt).
H. Decrease the salary of Employee by 15% where first name second letter is L.
(1.5 point).
I. Display average salary of Employees (1 point).
J. Display Maximum Salary of Employees (1 point).
K. Retrieve all information of Employees (1point).
L. Retrieve the First name of Employees whose salary is greater than
5000(1point).
M. Delete Employee whose employee id is p005(1 point).
N. Change the name of table from Employee information to employee_info (1
point).
Total weight: 15%
Page | 2
Arba Minch University
Sawla Campus
Department of Business Administration and
Information System
2015 E.C
DB System II Lab Practical Exam Time allowed: 2hrs
Name ID
1.______________________________________
________________
2.______________________________________ ________________
3.______________________________________
________________
Answer sheet
A._______________________________________________________________
B._______________________________________________________________
C._______________________________________________________________
D._______________________________________________________________
E._______________________________________________________________
F._______________________________________________________________
G._______________________________________________________________
H._______________________________________________________________
I.________________________________________________________________
J.________________________________________________________________
K._______________________________________________________________
L._______________________________________________________________
M._______________________________________________________________
N._______________________________________________________________
Page | 3
Arba Minch University
Sawla Campus
Department of Business Administration and
Information System
2015 E.C
DB System II Lab Practical Exam Time allowed: 2hrs
Bonus (2 points)
II. Write the commands for Data Definition Language (DDL), Data Manipulation
Language (DML), Data Control Language (DCL), Transaction Control Language
(TCL).
Answer
Page | 4
