ceu-notes/1/GBD/assignments/ej-emp-dept.sql

-- 1
SELECT ename FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'JAMES');

-- 2
SELECT ename FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename='SMITH');

-- 3
SELECT ROUND(AVG(sal), 2) FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename='JAMES');

-- 4
SELECT ename FROM emp WHERE hiredate < (SELECT hiredate FROM emp WHERE ename='MILLER');

-- 5
SELECT ename FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename='FORD') AND
	sal > (SELECT sal FROM emp WHERE ename='JAMES');

-- 6
SELECT dept.dname FROM emp JOIN dept ON emp.deptno = dept.deptno WHERE ename='SCOTT';

-- 7
SELECT COUNT(*) FROM emp WHERE sal < (SELECT sal FROM emp WHERE ename='MILLER');

-- 8
SELECT dept.loc FROM emp JOIN dept ON emp.deptno = dept.deptno WHERE emp.ename='KING';

-- 9
SELECT DISTINCT dept.dname FROM emp JOIN dept ON emp.deptno = dept.deptno WHERE emp.job='CLERK';

-- 10
SELECT DISTINCT dname FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp WHERE job = 'ANALYST');

-- 11
SELECT ename FROM emp JOIN dept ON emp.deptno = dept.deptno WHERE dept.loc = 'DALLAS' ORDER BY ename;

-- 12
SELECT ename, sal FROM emp WHERE job = (SELECT job FROM emp WHERE ename='JONES') AND ename != 'JONES';

-- 13
SELECT * FROM emp
	WHERE deptno = (SELECT deptno FROM emp WHERE ename='SCOTT') AND
	sal > (SELECT sal FROM emp WHERE ename='SMITH') AND
	job = (SELECT job FROM emp WHERE ename='MILLER');

-- 14
SELECT * FROM emp
	WHERE deptno = (SELECT deptno FROM emp WHERE ename='MARTIN') AND
	sal = (SELECT sal FROM emp WHERE ename='MARTIN');

-- 15
SELECT ename, sal FROM emp
	WHERE deptno = (SELECT deptno FROM emp WHERE ename='FORD') AND
	sal = (SELECT sal FROM emp WHERE ename='FORD')
	ORDER BY ename ASC, sal DESC;

-- 16
SELECT (SELECT MAX(sal) FROM emp WHERE deptno=10) "deptno 10",
	(SELECT MAX(sal) FROM emp WHERE deptno=20) "deptno 20" FROM dual;

-- 17
SELECT (SELECT MIN(sal) FROM emp WHERE deptno=10) "min sal deptno 10",
	(SELECT COUNT(*) FROM emp WHERE deptno=20) "employees deptno 20",
	(SELECT AVG(sal) FROM emp WHERE deptno=30) "avg sal deptno 30" FROM dual;

-- 18
SELECT ename "NOMBRE", sal+NVL(comm, 0) "SALARIO_TOTAL"
	FROM emp
	WHERE deptno=20 ORDER BY ename;

-- 19
SELECT ename "NOMBRE", sal+NVL(comm, 0) "SALARIO_TOTAL" FROM emp
	WHERE deptno=10 AND sal > 2000
	UNION
	SELECT ename, sal+NVL(comm, 0) FROM emp
	WHERE sal > 1200
	ORDER BY SALARIO_TOTAL DESC;

-- 20
SELECT deptno, ename FROM emp
	WHERE deptno IN (SELECT deptno FROM emp WHERE ename IN ('JAMES', 'SCOTT')) AND
	ename NOT IN ('JAMES', 'SCOTT')
	ORDER BY deptno, ename;

-- 21
SELECT dname FROM emp JOIN dept ON emp.deptno=dept.deptno GROUP BY dept.dname HAVING COUNT(*) > 4;

-- 23
SELECT deptno, ROUND(AVG(sal), 2) FROM emp
	WHERE deptno IN (SELECT DISTINCT deptno FROM emp WHERE job='SALESMAN') GROUP BY deptno;

-- 24
SELECT deptno FROM emp
	GROUP BY deptno HAVING COUNT(*) =
	(SELECT MAX(COUNT(*)) FROM emp
	GROUP BY deptno);

-- 25
SELECT dname FROM dept
	WHERE deptno=(
	SELECT deptno FROM emp
	GROUP BY deptno HAVING COUNT(*) =
	(SELECT MAX(COUNT(*)) FROM emp
	GROUP BY deptno));

-- 26
SELECT ename, sal FROM emp
	WHERE deptno=(
	SELECT deptno FROM emp
	GROUP BY deptno HAVING COUNT(*) =
	(SELECT MAX(COUNT(*)) FROM emp
	GROUP BY deptno)) ORDER BY ename;

-- 27
SELECT ROUND(AVG(sal), 2) FROM emp
	GROUP BY deptno HAVING deptno !=
	(SELECT deptno FROM emp WHERE ename='SCOTT');

-- 28
SELECT ename FROM emp
	WHERE mgr IN (
	SELECT empno FROM emp
	WHERE sal > (SELECT sal FROM emp WHERE hiredate='1981-04-02'));

-- 29
SELECT job, ename, sal FROM emp
	WHERE sal > (
	SELECT AVG(sal) FROM emp WHERE deptno=(
	SELECT deptno FROM emp WHERE ename='MILLER'));

-- 30
SELECT ename, hiredate FROM emp
	WHERE hiredate BETWEEN
	(SELECT hiredate FROM emp WHERE ename='JONES')
	AND
	(SELECT hiredate FROM emp WHERE ename='SCOTT')
	ORDER BY ename;