TERTIARY AND VOCATIONAL EDUCATION COMMISSION
COMMON WRITTEN EXAMINATION
All rights reserved.
NVQ Level 05 –Semester II
Information & Communication Technology
Database Systems 11 – Theory K72T001M02 Three Hours
Answer only 03 questions (Question 01 and 02 are COMPULSORY).
Usethe given tables below to answer your questions.
Employee (Empid char(04), Fname varchar(10), Lname varchar(20), City varchar(75), Bdate
Date, Designation varchar(25), Date_Joined Date, Salary Number(7,2), Dnum
char(03))
Project (Pnumber, Pname, Plocation, Dnum)
Department (Dnum, Dname, Mgr_Id, Dlocation, Total_Salary)
Works_on(Empid, Pnumber, Date_joined, Payment, Hours)
Note:
Mgr_Id denotes the Empid of the employee who manages the department
Question 01:
1. Write suitable code to create a database named ‘Company’. (3 marks)
2. Write SQL Statement to create the Employee table with the given data types (No need
to insert the records) (6 marks)
3. Write SQL Statement to add a new record to Department table with following details
Dnum- ‘025’, Dname - ‘Marketing’, Mgr_Id-–‘M324’, Dlocation - ‘Kandy’ (4 marks)
4. Update salaries of all employees in the ‘Research’ department with a 10 percent raise
in salary. (6 marks)
5. Write the code to delete the records from Works_on table with respect to the
employees who have joined the project 10 years before the current date. (6 marks)
COMMON WRITTEN EXAMINATION
All rights reserved.
NVQ Level 05 –Semester II
Information & Communication Technology
Database Systems 11 – Theory K72T001M02 Three Hours
Answer only 03 questions (Question 01 and 02 are COMPULSORY).
Usethe given tables below to answer your questions.
Employee (Empid char(04), Fname varchar(10), Lname varchar(20), City varchar(75), Bdate
Date, Designation varchar(25), Date_Joined Date, Salary Number(7,2), Dnum
char(03))
Project (Pnumber, Pname, Plocation, Dnum)
Department (Dnum, Dname, Mgr_Id, Dlocation, Total_Salary)
Works_on(Empid, Pnumber, Date_joined, Payment, Hours)
Note:
Mgr_Id denotes the Empid of the employee who manages the department
Question 01:
1. Write suitable code to create a database named ‘Company’. (3 marks)
2. Write SQL Statement to create the Employee table with the given data types (No need
to insert the records) (6 marks)
3. Write SQL Statement to add a new record to Department table with following details
Dnum- ‘025’, Dname - ‘Marketing’, Mgr_Id-–‘M324’, Dlocation - ‘Kandy’ (4 marks)
4. Update salaries of all employees in the ‘Research’ department with a 10 percent raise
in salary. (6 marks)
5. Write the code to delete the records from Works_on table with respect to the
employees who have joined the project 10 years before the current date. (6 marks)
