Consider the following relational schema:

Formulate the following queries using SQL:
(a)
(1) List all skills with a charge out rate greater than 60 per hour, in alphabetical order of description.
(2) List all staff with the skill description 'Programmer' who work in the 'Special Projects' department.
(3) For all projects that were active in July 1995, list the staff name, project number and the date and number of hours worked on the project, ordered by staff name, within staff name by the project number and within project number by date.
(4) How many staff have the skill 'Programmer'?
(5) List all projects that have at least two staff booking to it.
(6) List the average charge out rate.
(7) List all staff with a charge out rate greater than the average charge out rate.
(b) Create a view of staff details giving the staff number, staff name, skill description, and department, but excluding the skill number and charge out rate.

Question

Consider the following relational schema:
 
 Formulate the following queries using SQL:
(a) 
(1) List all skills with a charge out rate greater than 60 per hour, in alphabetical order of description.
(2) List all staff with the skill description 'Programmer' who work in the 'Special Projects' department.
(3) For all projects that were active in July 1995, list the staff name, project number and the date and number of hours worked on the project, ordered by staff name, within staff name by the project number and within project number by date.
(4) How many staff have the skill 'Programmer'?
(5) List all projects that have at least two staff booking to it.
(6) List the average charge out rate.
(7) List all staff with a charge out rate greater than the average charge out rate.
(b) Create a view of staff details giving the staff number, staff name, skill description, and department, but excluding the skill number and charge out rate.

Quizplus · Accepted Answer

The Answer is (a)
(1)SELECT *
FROM Skill
WHERE chargeOutRate > 60
ORDER BY description;

(2)SELECT *
FROM Staff s, Skill k
WHERE s.skillCode = k.skillCode AND
description = 'Programmer' AND dept = 'Special Projects';

(3)SELECT name, p.projectNo, dateWorkedOn, timeWorkedOn
FROM Staff s, Project p, Booking b
WHERE s.staffNo = b.staffNo AND
b.projectNo = p.projectNo AND
endDate >= DATE '1995-06-01'
ORDER BY name, p.projectNo, dateWorkedOn;

(4)SELECT COUNT(*)
FROM Staff s, Skill k
WHERE s.skillCode = k.skillCode AND
description = 'Programmer';

(5)SELECT projectNo, COUNT(*)
FROM Booking
GROUP BY projectNo
HAVING COUNT(*) >= 2;

(6)SELECT AVG(chargeOutRate)
FROM Skill;

(7)SELECT s.* FROM Staff s, Skill k
WHERE s.skillCode = k.skillCode AND
chargeOutRate > (SELECT AVG(chargeOutRate)
FROM Skill);

(b)CREATE VIEW SD (staffNo, name, dept, description)
AS SELECT staffNo, name, dept, description
FROM Staff s, Skill k
WHERE s.skillCode = k.skillCode;

Consider the Following Relational Schema