Lab 6 - Solutions

1. SELECT
   	ORDER_ID,
   	CUST_FIRST_NAME || ' ' || CUST_LAST_NAME as Name
   FROM OEHR_ORDERS o, OEHR_CUSTOMERS c
   WHERE o.CUSTOMER_ID = c.CUSTOMER_ID
   

2. SELECT
   	ORDER_ID, LAST_NAME
   FROM OEHR_ORDERS o, OEHR_EMPLOYEES e
   WHERE o.SALES_REP_ID = e.EMPLOYEE_ID AND e.LAST_NAME = 'Whatever Name'
   

3. SELECT
   	ORDER_ID,
   	CUST_FIRST_NAME,
   	CITY
   FROM OEHR_ORDERS, OEHR_CUSTOMERS
   WHERE OEHR_ORDERS.SALES_REP_ID = OEHR_CUSTOMERS.CUSTOMER_ID AND CITY = 'WHATEVER'
   

4. SELECT * FROM OEHR_ORDERS WHERE ORDER_TOTAL > (
   	SELECT AVG(ORDER_TOTAL)
   	FROM OEHR_ORDERS 
   	WHERE ORDER_DATE >= '20-OCT-19' AND ORDER_DATE < '21-OCT-19'
   )
   

5. CREATE VIEW SALESMAN AS
   SELECT FIRST_NAME, LAST_NAME
   FROM OEHR_EMPLOYEES
   WHERE JOB_ID = 'SA_REP'
   

6. SELECT * 
   FROM OEHR_ORDERS 
   WHERE SALES_REP_ID IN (
   	SELECT EMPLOYEE_ID 
   	FROM OEHR_EMPLOYEES 
   	WHERE COMMISSION_PCT = (
   		SELECT MAX(COMMISSION_PCT) AS MCP 
   		FROM OEHR_EMPLOYEES
   	)
   )