1)/* autorep Show all employees and, if he/she has completed any repair orders,
ID: 3708574 • Letter: 1
Question
1)/* autorep
Show all employees and, if he/she has completed any repair
orders, show how many.
Show the person ID, employee ID, first name, last name and
repair order count.
Sort by employee ID.
Hint 1: Not everyone in the PERSON table is an employee.
Hint 2: Some employees haven't completed a repair order so the
count column should show some zeros.
*/
2)/* autorep
Solve the previous problem but use the PLUS notation and the
CROSS PRODUCT methods.
*/
AUTOREP.REPAIR DETAIL AUTOREP.REPAIR ORDER ORD_DETAILID VARCHAR2 (10 BYTE VARCHAR2 (10 BYTE ITEM_CHARGE NUMBER (8,2) ORD START DATE DATE AUTOREP.VEHICLE F ORD ID VARCHAR2 (10 BYTE) VARCHAR2 (8 BYTE DATE VARCHAR2 (6 BYTE VARCHAR2 (8 BYTE) VARCHAR2 (9 BYTE VARCHAR2 (40 BYTE ORD END DATE ORD STATUS F INV CODE P VEHICLE ID VARCHAR2 (40 BYTE) F CUST ID *VEHICLE MAKE VARCHAR2 (20 BYTE REPAIR DETAIL PK (ORD DETAIL ID) VEHICLE MODEL VARCHAR2 (20 BYTE VEHICLE YEAR VARCHAR2 (4 BYTE FK_ REPAIRDET INV (INV CODE REPAIR_DETAIL REPAIR ORDER_FK (ORD ID) F VEHICLE ID F CUST ID VARCHAR2 (8 BYTE REPAIR ORDER PK (ORD ID) REPAIR ORDER CUSTOMER FK (CUST ID) REPAIR ORDER EMPLOYEE FK (EMP ID) REPAIR ORDER VEHICLE FK (VEHICLE ID) REPAIR ORDER PK (ORD ID) VEHICLE PK (VEHICLE ID) VEHICLE CUSTOMER_FK (CUST ID) VEHICLE PK (VEHICLE ID) ? REPAIR DETAIL-PK (ORD-DETAIL-ID) AUTOREP.INVENTORY VARCHAR2 (8 BYTE) INV NAME VARCHAR2 (35 BYTE AUTOREP.CUSTOMER INV COST NUMBER (8,2) AUTOR EPZ?PCODE VARCHAR2 (8 BYTE P ZIPCODE INV_PRICE NUMBER (8,2) INVENTORY_PK (INV ID) INVENTORY-PK (INV-ID) VARCHAR2 (10 BYTE) VARCHAR2 (30 BYTE) VARCHAR2 (3 BYTE AUTOREP.EMPLOYEE CUST_CREDIT VARCHAR2 (3 BYTE CUST LIMIT PERS-ID VARCHAR2 (9 BYTE NUMBER (7,2) VARCHAR2 (8 BYTE) EMP EMAIL VARCHAR2 (35 BYTE F STATE NAME VARCHAR2 (25 BYTE ZIPCODE PK (ZIPCODE) ? NUMBER (6,2) CUSTOMER PK (CUST ID) CUSTOMERPERSONFK (PERSID) EMP STATUS VARCHAR2 (8 BYTE F PERS ID F EMP SUPV VARCHAR2 (8 BYTE VARCHAR2 (9 BYTE ? ZIPCODE-PK(ZIPCODE) - - - ? CUSTOMER-PK (CUST-ID? ? CUSTOMER-IDX (PERS-ID) EMPLOYEE PK (EMP ID) EMPLOYEE EMPLOYEE FK (EMP SUPV) EMPLOYEE PERSON FK (PERS ID) EMPLOYEE PK (EMP-ID) EMPLOYEE AUTOREPPERSON AUTOREP PHONE ? ARCHAR2 (8 BYTE) VARCHAR2 (15 BYTE) VARCHAR2 (15 BYTE) VARCHAR2 (35 BYTE VARCHAR2 (10 BYTE IDX (PERSID) P PERS ID P PHONE ID VARCHAR2 (5 BYTE - FIRST NAME LASTNAME ADDR PHONE TYPE VARCHAR2 (10 BYTE PHONE NO VARCHAR2 (12 BYTE PHONE PK (PHONE ID) PHONE PERSON FK (PERS ID) - VARCHAR2 (8 BYTE F ZIPCODE PERSON PK (PERS ID) PERSON ZIPCODE FK (ZIPCODE PERSON PK (PERS ID) PHONE PK (PHONE ID)Explanation / Answer
1) SELECT PERS_ID, FIRST_NAME, LAST_NAME, EMP_ID, COUNT (ORD_ID)
FROM PERSON JOIN EMPLOYEE ON PERSON.PERS_ID = EMPLOYEE.PERS_ID
JOIN REPAIR_ORDER ON REPAIR_ORDER.EMP_ID = EMPLOYEE.EMP_ID
GROUP BY ( PERS_ID, FIRST_NAME, LAST_NAME, EMP_ID)
HAVING COUNT (ORD_ID) > 0;
2) SELECT PERS_ID, FIRST_NAME, LAST_NAME, EMP_ID, COUNT (ORD_ID)
FROM PERSON P
CROSS JOIN EMPLOYEE E
CROSS JOIN REPAIR_ORDER R
ON P.PERS_ID (+) = E.PERS_ID
AND E.EMP_ID (+) = R.EMP_ID
GROUP BY (PERS_ID, FIRST_NAME, LAST_NAME, EMP_ID)
HAVING COUNT (ORD_ID) > 0;
Please let me know in case of any clarifications required. Thanks!
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.