Find the busiest store last year (quantity of appts) so we can give them an awar

Question

Find the busiest store last year (quantity of appts) so we can give them an award. I want an SQL Query and be sure to join all tables referenced so as to avoid Cartesian Products.

Shop(location num, address, no_of_bays, phone, fax) employee(emp id, location num. home_address, wage, is_hourly, date_of_hire) admin(emp id, position) mechanic(emp id,years_exp) mechanic certs(emp id, cert org, cert name) mechanic appt(emp id. appt no) service appt(appt no, is_completed, date_time,type) auto_appt(appt_no, vin) auto(vin. cust no. year, type, engine, transmission) customer(cust no, name, address, phone_l, phone_2)

Explanation / Answer

select s.location_num,count(*) as cnt
from shop s,employee e,admin adm,mechanic m,mechanic_certs mc,mechanic_appt ma,service_appt sa,auto_appt aa,auto a,customer c
where
s.location_num=e.location_num and
e.emp_id=adm.emp_id and
e.emp_id=m.emp_id and
e.emp_id=mc.emp_id and
e.emp_id=ma.emp_id and
ma.appt_no=sa.appt_no and
ma.appt_no=aa.appt_no and
aa.vin=a.vin and
a.cust_no=c.cus_no and
sa.is_completed=1
group by s.location_num
order by cnt desc;

The busiest store (quantity of appts) will be the shop location_num with maximum count shown at the top of the query result

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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