SQL Multi-Table COUNT/JOIN Help Write an SQL query to create a list of all the s
ID: 3671628 • Letter: S
Question
SQL Multi-Table COUNT/JOIN Help
Write an SQL query to create a list of all the services ever performed, including the number of times they have been performed and the total cost of those services. You will need to use the AppointmentServices table, the ServiceAppointments table, and the Services table. The output should be sorted in descending order based on total cost. Here is what the output should look like (please note the column header names):
ServiceName
# times performed
Total Cost
Oil Change
5
149.75
Maintenance Level 3
1
119.95
Maintenance Level 2
1
79.95
Radiator Flush
1
79.95
Tire Rotation
3
59.85
Inspection
4
59.80
Maintenance Level 1
1
39.95
FROM THE DATABASE BELOW
ServiceName
# times performed
Total Cost
Oil Change
5
149.75
Maintenance Level 3
1
119.95
Maintenance Level 2
1
79.95
Radiator Flush
1
79.95
Tire Rotation
3
59.85
Inspection
4
59.80
Maintenance Level 1
1
39.95
Explanation / Answer
SQL>SELECT SERVICES.SERVICENAME,SERVICES.ALLOTEDTIME #TIMES PERFORMED,SERVICES.COST
FROM APPOINTMENTSERVICE,SERVICEAPPOINTMENTS,SERVICES
WHERE APPOINTMENTSERVICE.APPOINTMENTID=SERVICEAPPOINTMENTS.APPOINTMENTID
AND APPOINTMENTSERVICE.SERVICEID=SERVICES.SERVICESID
ORDER BY SERVICES.COST DESC;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.