Q2:

[answerhappygod]

Q2:

Q2 1 (54.16 KiB) Viewed 30 times

Section B: Question 2 - Structured Query Language ( 20 marks). Given the relational scheme of a human resource management database as the following: Employee (EmpNo, FName, LName, Address, DOB, Gender (int), Position, DeptNo) Department (DeptNo(int), DeptName(varchar(128)), ManagerEmpNo (int)) Project (ProjNo, ProjName, DeptNo) WorksOn (EmpNo, ProjNo, DateWorked, HoursWorked) *ManagerEmpNo is the FK of Employee. Write SQL statement for the requirement in the followings: (a) Insert a new record into Employee. The info is as the following. [2 Marks] EmpNo:99, Name: Bong Chih How, Address: FIT, UNIMAS, DOB: 1 Jan 1980,Gender:0, Position: Lecturer, DeptNo: 2 (b) Delete a record in Employee. The record to be deleted is EmpNo=99. [1 Marks] (c) Create a Department table. Please specify DeptNo as the primary key. [2 Marks] (d) List all employees with the attributes, FName, LName and EmpNo, in ascending alphabetical order of FName. [1 Marks] (e) List all the details of employees who are male ( 0 for male, 1 for female). [1 Marks] (f) List all the employee names and addresses of all employees who are Managers. [2 Marks] (g) Produce a list of the names and addresses of all employees who works for the "Marketing", as appear in DeptName. (Hint: recursive query) [3 Marks] h) Find out how many employees are managed by 'Nurul' (FName). (Hint: recursive query) [3 marks] (i) Produce a report in the format (ProjName, FName, LName, TotalHoursWorked) of the total hours worked by each employee on according to the projects. (Hint: groupby) [5 marks]