Write a SQL query to find mechanics with more than five certifications given the
ID: 3771428 • Letter: W
Question
Write a SQL query to find mechanics with more than five certifications given the following database (underlined items written to the side of each table):
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
Mechanic_certs(emp_id, cert_org, cert_name) emp_id, cert_org, cert_name all underlined
We have to find mechanics with more than five certifications. As we have given databae but we don't have mechanics name anywhere
so we will find mechanics Id. Now from given number of tables we only need one table to solve the query
Mechanic_certs(emp_id, cert_org, cert_name) emp_id, cert_org, cert_name all underlined
Underlined Items are primary key. As any single item can not be possible as primary key in this case because one mechanics
can have more than one certification and from more than one organization, So all collectively serve as primary key
Now To solve the query we will use group by in SQL because Mechanics Id are multiple we can group by all the data by Mechanics ID and
calculate count of certifications on the basis of Mechanics Id. We have to use Nested Query
select Mechanic_certs.emp_id from (select Mechanic_certs.emp_id, count(Mechanic_certs.cert_name)as Cnt from Mechanic_certs group by(Mechanic_certs.emp_id)) where Cnt>5
when query in from clause will execute it will create table with 2 columns with emp_name , count of certifications
after that outer query will execute from this table and gives you name having certifications more than 5.
Please note we have to use "Mechanic_certs.emp_id" because there are multiple fields with same name in other tables also.
So we need to append table name also with field name.
In this first query in from clause will get executed and from the result of that inner query
base query will get executed.
I have tested on MySql database.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.