Write a SQL query to find the highest producing mechanic (quantity of appts) fro

Question

Write a SQL query to find the highest producing mechanic (quantity of appts) from the following database (keys that should be underlined are written to the side):

Shop(location_num, address, no_of_bays, phone, fax) location_num underlined

Employees(emp_id, location_num, home_address, wage, is_hourly, date_of hire) emp_id and location_num both underlined

Admin(emp_id, position) emp_id underlined

Mechanic(emp_id, years_exp) emp_id underlined

Mechanic_certs(emp_id, cert_org, cert_name) emp_id, cert_org, cert_name all underlined

Mech_appt(emp_id, appt_no) emp_id and appt_no both underlined

Service_appt(appt_no, is_completed, date_time, type) appt_no underlined

Auto_appt(appt_no, vin) appt_no and vin both underlined

Auto(vin, cust_no, year, type, engine, transmission) appt_no underlined

Customer(cust_no, name, address, phone_1, phone_2) cust_no underlined

Explanation / Answer

Mech_appt(emp_id, appt_no) emp_id and appt_no both underlined
Service_appt(appt_no, is_completed, date_time, type) appt_no underlined

For this query we need to use above both tables
in table Service_appt--> appt_no is primary key and "is_completed" flag will tell whether appt_no is completed or not.
So to calculate highest producing mechanics we will consider only those appt_no whose "is_completed" field is yes.

And we have to find mechanics Id and there is no field of Id in Service_appt table we have to join this table with Mech_appt table

We will use inner join because both table has one field common and that is appt_no.

(Select T.ID from ((Select Mech_appt.emp_id as ID, count(Mech_appt.appt_no) Cnt from(select Mech_appt.emp_id,Mech_appt.appt_no from Mech_appt M, Service_appt S
where M.appt_no=S.appt_no and S.is_completed='Y')
group by (Mech_appt.emp_id)) T) where T.Cnt =(Select Max(T.Cnt) from T ))

Here 'T' is the name of One Intermediate Table for easy understanding.

All the inner queries will get execute first then outer queries. Control follow from innermost to outerMost.
I will try to give you some insight of what I wrote.
Step1: First we will join both tables on Appt_no and is_completed flag=y so it will give you table with 2 columns name mechanics Id and appt_nos .

Step 2: Then from this table we will extract table using group by(mechanics Id) which will give table with two columns name mechanicsId and count_of_no_of_appt on which that mechanic worked

Step3: After that We will find that employee whose count is maximum. So for this first we will find Max count.

Step 4: Then compare count of every mechanics which we got in step 2 with this maximum count got in step3 and whose mechanics count = max count that row will come in output table.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.