第8次作业

SELECT * FROM emp WHERE deptno = 12;

SELECT empno, ename, deptno FROM emp WHERE job = 'CLERK';

SELECT * FROM emp WHERE comm > sal;

SELECT * FROM emp WHERE comm > sal * 1.20;

SELECT * FROM emp WHERE deptno = 10 AND job = 'MANAGER' UNION ALL SELECT * FROM emp WHERE deptno = 20 AND job = 'CLERK';

SELECT * FROM emp WHERE job NOT IN ('MANAGER', 'CLERK') AND sal >= 2000;

SELECT DISTINCT job FROM emp WHERE comm > 0;

SELECT empno, ename, sal + NVL(comm, 0) AS total_compensation FROM emp;

SELECT * FROM emp WHERE comm IS NULL OR comm < 100;

SELECT * FROM emp WHERE TO_CHAR(hiredate, 'DD') = LAST_DAY(TO_DATE(TO_CHAR(hiredate, 'YYYY-MM'), 'YYYY-MM')) - 1;

SELECT * FROM emp WHERE MONTHS_BETWEEN(SYSDATE, hiredate) >= 120;

SELECT initcap(ename) AS name, e.* FROM emp e;

SELECT * FROM emp WHERE LENGTH(ename) = 6;

SELECT * FROM emp WHERE REGEXP_LIKE(ename, '[^S]');

SELECT * FROM emp WHERE SUBSTR(ename, 2, 1) = 'M';

SELECT SUBSTR(ename, 1, 3) AS first_three_chars FROM emp;

SELECT SUBSTR(ename, 1, 3) AS first_three_chars FROM emp;

SELECT ename, hiredate FROM emp ORDER BY hiredate ASC;

SELECT ename, job, sal, comm FROM emp ORDER BY job DESC, sal ASC;

SELECT ename, EXTRACT(YEAR FROM hiredate) AS hire_year, EXTRACT(MONTH FROM hiredate) AS hire_month FROM emp ORDER BY hire_month ASC, hire_year ASC;

SELECT * FROM emp WHERE EXTRACT(MONTH FROM hiredate) = 2;

SELECT * FROM dept WHERE deptno IN (SELECT deptno FROM emp);

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH');

SELECT e.ename AS employee, m.ename AS manager FROM emp e LEFT JOIN emp m ON e.mgr = m.empno;

SELECT e.* FROM emp e WHERE hiredate < (SELECT hiredate FROM emp WHERE empno = e.mgr);

SELECT d.deptno, d.dname, e.empno, e.ename FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno;

SELECT e.empno, e.ename, d.deptno, d.dname FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;