Write a SQL query to find the customer with the most quantity of appts given the

Question

Write a SQL query to find the customer with the most quantity of appts given the following database (underlined parts of each table written out to the side of each):

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

Select cust_no, name from Customer where cust_no IN(Select cust_id from Auto_appt,Auto , Customer where Customer.cust_no=Auto.cust_no and Auto.vin=Auto_appt.vin group by cust_id having max(count(*)))

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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