Write a SQL query that will find the busiest (quantity of appts) store last year

Question

Write a SQL query that will find the busiest (quantity of appts) store last year from the below database, make sure to join all tables referenced so that a Cartesian product is avoided (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

// Number of Mechanical application in a perticular store
-> Mech_appt(emp_id, appt_no) emp_id and appt_no both underlined

// Number of Service application in a perticular year
-> Service_appt(appt_no, is_completed, date_time, type) appt_no underlined

// Number of Application in a perticular store
-> Auto_appt(appt_no, vin) appt_no and vin both underlined

create view v_1 as select appt_no, count(appt_no) as c from (Select appt_no from Service_appt whre date_time = '2014') group by appt_no;
create view v_2 as select appt_no, count(appt_no) as c from Auto_appt group by appt_no;
create view v_3 as select appt_no, count(appt_no) as c from Mech_appt group by appt_no;

create view v_4 as select appt_no, v_1.c + v_2.c + v_3.c as c from v_1,v_2,v_3 where v_1.appt_no = v_2.appt_no && v_2.appt_no = v_3.appt_no;
// c is the maximum application busiest Store received last year;
select appt_no,max(c) from v_4;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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