week9.sql

SELECT d.department_id,l.location_id,e.last_name,e.job_id,e.salary FROM employees e join departments d ON e.department_id = d.department_id join locations l ON d.location_id = l.location_id WHERE location_id = &location_id;
SELECT d.department_name,l.location_id,e.last_name,e.job_id,e.salary,j.job_title FROM employees e JOIN departments d ON e.department_id = d.department_id join locations l ON d.location_id = l.location_id join jobs j ON e.job_id = j.job_id WHERE location_id = &location_id;
SELECT d.department_name,l.location_id,e.last_name,e.job_id,e.salary,j.job_title FROM employees e JOIN departments d ON e.department_id = d.department_id join locations l ON d.location_id = l.location_id join jobs j ON e.job_id = j.job_id WHERE location_id = &location_id;
SELECT d.department_name,l.location_id,e.last_name,e.job_id,e.salary,j.job_title FROM employees e , departments d , locations l , jobs j WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND e.job_id = j.job_id AND d.location_id = &location_id;

SELECT wkr.employee_id,wkr.last_name,mgr.last_name manager FROM employees wkr, employees mgr WHERE wkr.manager_id = mgr.employee_id AND wkr.last_name = 'Lorentz';

SELECT wkr.last_name,wkr.hire_date FROM employees wkr, employees dv WHERE wkr.HIRE_DATE > dv.hire_date AND dv.last_name = 'Davies';

--EMP with No DEPT
SELECT last_name,NVL(department_name,'No depaato') department_name FROM employees e left outer join departments d ON e.department_id = d.department_id;
--DEPT with no EMP
SELECT last_name,department_name department_name FROM employees e right outer join departments d ON e.department_id = d.department_id;
--DEPT with no EMP and EMP with no DEPT
SELECT last_name,department_name department_name FROM employees e full outer join departments d ON e.department_id = d.department_id;

--Unit 7
SELECT last_name,salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE employee_id = 149);
SELECT last_name,salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE LOWER(last_name) = 'abel');
SELECT last_name,job_id,salary FROM employees WHERE salary < (SELECT AVG(salary) FROM employees);

SELECT last_name,job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141);

SELECT last_name,job_id,SALARY FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141) AND SALARY > (SELECT salary FROM employees WHERE employee_id = 141);

SELECT last_name FROM employees WHERE job_id = (SELECT job_id FROM jobs WHERE job_title = 'President');

SELECT department_id,MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);

SELECT JOB_ID, AVG(salary) FROM employees GROUP BY JOB_ID HAVING AVG(salary) = (SELECT MIN(AVG(salary)) FROM employees GROUP BY JOB_ID);

SELECT last_name,salary,department_id FROM employees WHERE salary IN(SELECT MIN(salary) FROM employees GROUP BY department_id);

SELECT employee_id,last_name FROM employees WHERE department_id IN (SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%')