Consider the following relational schema: Staff (staffNo, name, dept, skillCode) Skill (skillCode, description, chargeOutRate) Project (projectNo, startDate, endDate, budget, projectManagerStaffNo) Booking (staffNo, projectNo, dateWorkedOn, timeWorkedOn) Where the relation Staff contains staff details, Skill contains descriptions of skill codes (e.g. programmer, Analyst, Manager, etc.) and the charge out rate per hour for that skill. Project contains project details and Booking contains details of the date and the number of hours that a member of staff worked on a project. The primary keys are underlined. Formulate the following queries using SQL: a) List all skills with a charge out rate greater than 60 per hour, in alphabetical order of description. [3] b) List all staff with the skill description ‘Programmer’ who work in the ‘Special Projects’ department. c) How many staff have the skill ‘Programmer’? d) List the average charge out rate e) List all staff with a charge out rate greater than the average charge out rate.
Consider the following relational schema:
Staff (staffNo, name, dept, skillCode)
Skill (skillCode, description, chargeOutRate)
Project (projectNo, startDate, endDate, budget, projectManagerStaffNo)
Booking (staffNo, projectNo, dateWorkedOn, timeWorkedOn)
Where the relation Staff contains staff details, Skill contains descriptions of skill codes (e.g.
programmer, Analyst, Manager, etc.) and the charge out rate per hour for that skill. Project contains
project details and Booking contains details of the date and the number of hours that a member of
staff worked on a project. The primary keys are underlined.
Formulate the following queries using SQL:
a) List all skills with a charge out rate greater than 60 per hour, in alphabetical order of
description.
[3]
b) List all staff with the skill description ‘Programmer’ who work in the ‘Special Projects’
department.
c) How many staff have the skill ‘Programmer’?
d) List the average charge out rate
e) List all staff with a charge out rate greater than the average charge out rate.
AI-generated content may present inaccurate or offensive content that does not represent bartleby’s views.
Unlock instant AI solutions
Tap the button
to generate a solution
