APRIL 2011 QUESTION 7(20 marks)

clutteredreverandData Management

Oct 31, 2013 (3 years and 9 months ago)

99 views

| ITS232 | INTRODUCTION TO DATABASE MANAGEMENT SYSTEMS | DEC 11


MAR2012 |
| TUTORIIOL 4
-
2: SQL |


sitinur151

Page
1


Name



: _______________________________________________

Student Number

: _______________________________________________

Group/Class


: _______________________________________________


APRIL 2011

QUESTION 7(20 marks)

The following questions
will be based on the table structures below:




a) Create table Staff.

(3 marks)


b) Increase the salary of all staff by 15 percent.

(2 marks)


c) Add a new staff details for branch B003 with the following information: name
-


IskandarZulkarnain, staff number
-

SG13, position
-

auditor, and salary
-

RM3100.

(3 marks)


d) Delete all staff whose positions are Supervisor and salary below than RM1600.

(3 marks)


e) Calculate the average salary for every branch.

(3 marks)


f) List

the staff first name, position and branch state for male staff working in West Wing.

(3 marks)


g) Identify the total number of female staff working in South Wing.

(3 marks)

4
0

| ITS232 | INTRODUCTION TO DATABASE MANAGEMENT SYSTEMS | DEC 11


MAR2012 |
| TUTORIIOL 4
-
2: SQL |


sitinur151

Page
2


QUESTION
2

(20 marks)

Consider the ERD o
f
a kid’s hospital
database system:


Based on the above ERD,

write the IBM DB2 SQL commands to:



a)

Insert the following data into NURSEMAID.


Staff_ID

Name

Address

Salary

1234

Aminah Jamali

2, Taman Indah

RM2000


(2 marks)


b)

List nursemaid ID, name, address and salary
for all employees who earn more than
RM2000. Sequence the results in descending order by salary.


(4 marks)



c)

List the kids’ IDs and names for those who are monitored by nursemaid 1212 or 2323.


(3 marks)



d)

Display all kids’ information whose names start
with MUHD.


(3 marks)



e)

Delete all information for staff_ID 4545 from NURSEMAID.


(2 marks)



f)

Produce report that list Staff_ID, name, salary, Kid_ID and Kid_name.


(3 marks)



g)

Update new salary for nursemaid 3434 to RM3500.


(3 marks)