SQL: 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: Staff contains staff details and staffNo is the key. Skill contains descriptions of skill codes (e.g. Programmer, Analyst, Manager, etc.) and the charge out rate per hour for that skill; the key is skillCode. Project contains project details and projectNo is the key. Booking contains details of the date and the number of hours that a member of staff worked on a project and the key is staffNo/projectNo. Formulate the following queries using SQL: Find staff members who have worked on multiple projects simultaneously: (Write a SQL query to find staff members who have worked on more than one project on the same day. This will involve using a group by clause and having a count of distinct project number greater than one.) SELECT staffNo, COUNT(DISTINCT projectNo) AS numProjects FROM Booking GROUP BY staffNo, dateWorkedOn HAVING COUNT(DISTINCT projectNo) > 1; What are the total hours worked by staff in each department for ‘Project Z’? List all projects that have at least two staff booking to it. List all staff with a charge out rate greater than the average charge out rate.
- SQL: 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: Staff contains staff details and staffNo is the key.
Skill contains descriptions of skill codes (e.g. Programmer, Analyst,
Manager, etc.) and the charge out rate per hour for that skill; the
key is skillCode.
Project contains project details and projectNo is the key.
Booking contains details of the date and the number of hours that a member of
staff worked on a project and the key is staffNo/projectNo.
- Formulate the following queries using SQL:
- Find staff members who have worked on multiple projects simultaneously: (Write a SQL query to find staff members who have worked on more than one project on the same day. This will involve using a group by clause and having a count of distinct project number greater than one.)
SELECT staffNo, COUNT(DISTINCT projectNo) AS numProjects
FROM Booking
GROUP BY staffNo, dateWorkedOn
HAVING COUNT(DISTINCT projectNo) > 1;
- What are the total hours worked by staff in each department for ‘Project Z’?
- List all projects that have at least two staff booking to it.
List all staff with a charge out rate greater than the average charge out rate.
Trending now
This is a popular solution!
Step by step
Solved in 5 steps
