Queries.txt

1. SELECT sc_id AS SERVICE_CENTER_ID FROM (SELECT sc_id, COUNT(c_id) AS counter FROM CUSTOMER GROUP BY sc_id ORDER BY counter desc) WHERE ROWNUM = 1;

2. SELECT AVG(price) AS AVERAGE FROM PRICE_CHECK WHERE s_no = (SELECT s_no FROM SERVICES WHERE s_name = 'Evaporator Repair') AND manufacturer = 'Honda';

3. SELECT DISTINCT c_id AS ID, full_name AS Name FROM SCHEDULED_SERVICES WHERE sc_id = '30003' AND invoice_status = 'Unpaid';

4. SELECT DISTINCT service_name FROM MAINTENANCE WHERE service_type = 'mr';

5. SELECT P1 - P2 AS DIFFERENCE FROM (SELECT PC1.price + PC2.price AS P1 FROM PRICE_CHECK PC1, PRICE_CHECK PC2 WHERE PC1.s_no = (SELECT S.s_no FROM SERVICES S WHERE S.s_name = 'Belt Replacement') AND PC2.s_no = (SELECT S1.s_no FROM SERVICES S1 WHERE S1.s_name = 'A') AND PC1.manufacturer = 'Toyota' AND PC1.sc_id = '30001' AND PC2.manufacturer = 'Toyota' AND PC2.sc_id = '30001'), (SELECT PC1.price + PC2.price AS P2 FROM PRICE_CHECK PC1, PRICE_CHECK PC2 WHERE PC1.s_no = (SELECT S.s_no FROM SERVICES S WHERE S.s_name = 'Belt Replacement') AND PC2.s_no = (SELECT S1.s_no FROM SERVICES S1 WHERE S1.s_name = 'A') AND PC1.manufacturer = 'Toyota' AND PC1.sc_id = '30002' AND PC2.manufacturer = 'Toyota' AND PC2.sc_id = '30002');

6. SELECT NEXT_MAINTENANCE FROM (SELECT VIN, LAST_SCHEDULED_MAINTENANCE_NAME , (CASE WHEN LAST_SCHEDULED_MAINTENANCE_NAME = 'A' THEN 'B' ELSE (CASE WHEN LAST_SCHEDULED_MAINTENANCE_NAME = 'B' THEN 'C' ELSE (CASE WHEN LAST_SCHEDULED_MAINTENANCE_NAME = 'C' THEN 'A' ELSE 'NA' END)END) END) AS NEXT_MAINTENANCE FROM VEHICLE_LAST_SERVICED) WHERE vin = '34KLE19D';