Lab10

--SELECT JOB_ID, COUNT(FIRST_NAME) FROM EMPLOYEES GROUP BY MANAGER_ID, JOB_ID
--SELECT AVG(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING COUNT(LAST_NAME)>3
--SELECT DEPARTMENT_ID, COUNT(FIRST_NAME) FROM EMPLOYEES WHERE MANAGER_ID !=101 GROUP BY DEPARTMENT_ID HAVING AVG(SALARY)>3000 
--SELECT MAX(SALARY)-MIN(SALARY) FROM EMPLOYEES 
--SELECT MANAGER_ID, COUNT(FIRST_NAME) FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL GROUP BY MANAGER_ID
--SELECT DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING SUM(NVL(COMMISSION_PCT,0))=0 

--SELECT EMPLOYEE_ID, JOB_ID, HIRE_DATE, DEPARTMENT_ID FROM EMPLOYEES UNION ALL SELECT EMPLOYEE_ID, JOB_ID, START_DATE, DEPARTMENT_ID FROM JOB_HISTORY
--SELECT EMPLOYEE_ID, JOB_ID, HIRE_DATE, DEPARTMENT_ID FROM EMPLOYEES UNION  SELECT EMPLOYEE_ID, JOB_ID, START_DATE, DEPARTMENT_ID FROM JOB_HISTOR

--SELECT EMPLOYEE_ID, JOB_ID, HIRE_DATE, DEPARTMENT_ID FROM EMPLOYEES MINUS SELECT EMPLOYEE_ID, JOB_ID, START_DATE, DEPARTMENT_ID FROM JOB_HISTORY
--SELECT EMPLOYEE_ID, JOB_ID, START_DATE, DEPARTMENT_ID FROM JOB_HISTORY INTERSECT SELECT EMPLOYEE_ID, JOB_ID, HIRE_DATE, DEPARTMENT_ID FROM EMPLOYEES 

--SELECT SONG_ID FROM D_PLAY_LIST_ITEMS WHERE EVENT_ID =(SELECT EVENT_ID FROM D_PLAY_LIST_ITEMS WHERE SONG_ID =45)
--SELECT NAME FROM D_EVENTS WHERE COST >(SELECT ID FROM D_EVENTS WHERE ID = 100) 
--SELECT FIRST_NAME , LAST_NAME FROM F_STAFFS WHERE STAFF_TYPE != (SELECT STAFF_TYPE FROM F_STAFFS WHERE LAST_NAME = 'Miller')

--SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE SALARY >(SELECT SALARY FROM EMPLOYEES WHERE LAST_NAME = 'Lorentz') AND DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME ='Abel')
--SELECT STAFF_TYPE FROM F_STAFFS WHERE SALARY <(SELECT SALARY FROM F_STAFFS WHERE STAFF_TYPE ='Cook')
--SELECT DEPARTMENT_ID, AVG(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING AVG(SALARY) < (SELECT SALARY FROM EMPLOYEES WHERE LAST_NAME ='Ernst') 
--SELECT DEPARTMENT_ID , MIN(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING MIN(SALARY) > (SELECT MIN(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID != 50)
--SELECT * FROM D_SONGS WHERE TYPE_CODE IN (SELECT CODE FROM D_TYPES WHERE DESCRIPTION IN('Jazz','Pop'))
--SELECT SALARY, DEPARTMENT_ID,LAST_NAME FROM EMPLOYEES WHERE SALARY IN(SELECT MIN(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID)
--SELECT SALARY, FIRST_NAME, LAST_NAME FROM EMPLOYEES P WHERE SALARY IN(SELECT MIN(SALARY) FROM EMPLOYEES E WHERE P.DEPARTMENT_ID = E.DEPARTMENT_ID)
--SELECT FIRST_NAME, LAST_NAME , DEPARTMENT_ID, MANAGER_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = 141) AND MANAGER_ID = (SELECT MANAGER_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = 141) AND EMPLOYEE_ID !=141
--SELECT SALARY, FIRST_NAME, DEPARTMENT_ID FROM EMPLOYEES P WHERE SALARY IN(SELECT MAX(SALARY) FROM EMPLOYEES E WHERE P.DEPARTMENT_ID = E.DEPARTMENT_ID)
--SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMPLOYEES P WHERE EMPLOYEE_ID IN(SELECT MANAGER_ID FROM EMPLOYEES E WHERE E.MANAGER_ID = P.EMPLOYEE_ID)
--SELECT LAST_NAME, STAFF_TYPE FROM F_STAFFS WHERE SALARY > (SELECT SALARY FROM F_STAFFS WHERE LAST_NAME = 'Doe')

--SELECT * FROM EMPLOYEES WHERE HIRE_DATE =(SELECT MIN(HIRE_DATE) FROM EMPLOYEES WHERE JOB_ID ='IT_PROG')
--SELECT * FROM EMPLOYEES E WHERE HIRE_DATE = (SELECT MIN(HIRE_DATE) FROM EMPLOYEES D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID) ORDER BY HIRE_DATE
--SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_ID NOT IN(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID IS NOT NULL)
--SELECT LAST_NAME, DEPARTMENT_ID FROM EMPLOYEES WHERE SALARY < ANY (SELECT SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = 80) AND DEPARTMENT_ID != 80
--SELECT DEPARTMENT_NAME, AVG(SALARY) FROM DEPARTMENTS JOIN EMPLOYEES USING (DEPARTMENT_ID) GROUP BY DEPARTMENT_NAME HAVING AVG(SALARY) > (SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID =(SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME = 'Sales'))
--SELECT DEPARTMENT_ID FROM DEPARTMENTS JOIN EMPLOYEES USING (DEPARTMENT_ID) GROUP BY DEPARTMENT_ID HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID) 
--SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES L WHERE SALARY < (SELECT MAX(SALARY)/2 FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = L.DEPARTMENT_ID)