SQL Basic Example – 源码集中营

对Oracle 11g scott用户下的4张表进行数据查询

SET linesize 500; SET pagesize 100; DESC dept; DESC emp; DESC bonus; DESC salgrade; --基本查询 SELECT * FROM dept; SELECT * FROM emp; SELECT * FROM bonus; SELECT * FROM salgrade; SELECT deptno, dname FROM dept; SELECT DISTINCT deptno, job FROM emp; SELECT ename, hiredate FROM emp WHERE hiredate > '01-JAN-82'; SELECT ename, hiredate FROM emp WHERE hiredate > TO_DATE('1982-01-01', 'YYYY-MM-DD'); SELECT deptno, sal, job, ename FROM emp WHERE deptno = 20 AND (sal > 2500 OR job = 'CLERK'); SELECT deptno, sal, job, ename FROM emp WHERE deptno IN(20, 30) AND job NOT IN('CLERK', 'SALESMAN'); SELECT sal, hiredate, comm, ename FROM emp WHERE sal BETWEEN 2500 AND 3500 AND hiredate BETWEEN TO_DATE('1981-01-01', 'YYYY-MM-DD') AND TO_DATE('1981-12-31', 'YYYY-MM-DD') AND comm IS NULL; SELECT ename, sal, deptno FROM emp WHERE ename LIKE 'A%' OR ename LIKE '_A%'; SELECT ename, job, TO_CHAR(hiredate, 'YYYY-MM-DD'), sal * 1.2 FROM emp WHERE hiredate > TO_DATE('1982-01-01', 'YYYY-MM-DD'); SELECT rowid, rownum, ename, sal, deptno FROM emp WHERE ename LIKE 'A%' OR ename LIKE '_A%'; SELECT deptno, ename, sal, comm FROM emp WHERE sal BETWEEN 1500 AND 3000 ORDER BY deptno DESC, ename; SELECT DISTINCT deptno , job FROM emp WHERE deptno = 20 ORDER BY job; --分组查询 SELECT empno, sal , comm FROM emp WHERE deptno = 30; SELECT avg(sal), avg(distinct sal), max(sal), min(sal), sum(sal), count(*), count(sal), count(distinct sal), count(comm) FROM emp WHERE deptno = 30; SELECT deptno, avg(sal), max(sal) FROM emp GROUP BY deptno ORDER BY avg(sal); SELECT deptno, job, avg(sal), max(sal) FROM emp GROUP BY deptno, job; SELECT deptno, job, avg(sal), max(sal) FROM emp GROUP BY rollup(deptno, job); SELECT deptno, job, avg(sal), max(sal) FROM emp GROUP BY cube(deptno, job); SELECT deptno, avg(sal), max(sal) FROM emp WHERE deptno <= 50 GROUP BY deptno HAVING avg(sal) > 2000; --连接查询 SELECT deptno FROM dept WHERE deptno < 30; SELECT deptno, ename FROM emp WHERE job = 'CLERK'; SELECT d.deptno, e.deptno, e.ename FROM dept d, emp e WHERE d.deptno < 30 AND e.job = 'CLERK'; SELECT d.deptno, d.dname, e.ename, e.sal FROM dept d, emp e WHERE d.deptno = e.deptno AND d.deptno = 20; SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal AND e.deptno = 30; SELECT empno, ename, mgr FROM emp WHERE deptno = 30; SELECT e.ename, m.ename FROM emp e, emp m WHERE e.mgr = m.empno AND e.deptno = 30; --集合查询 SELECT empno, ename, mgr FROM emp WHERE deptno = 30 UNION ALL SELECT empno, ename, mgr FROM emp WHERE job = 'MANAGER'; SELECT empno, ename, mgr FROM emp WHERE deptno = 30 UNION SELECT empno, ename, mgr FROM emp WHERE job = 'MANAGER'; SELECT empno, ename, mgr FROM emp WHERE deptno = 30 MINUS SELECT empno, ename, mgr FROM emp WHERE job = 'MANAGER'; SELECT empno, ename, mgr FROM emp WHERE deptno = 30 INTERSECT SELECT empno, ename, mgr FROM emp WHERE job = 'MANAGER'; --子查询 SELECT ename, deptno, sal FROM emp WHERE sal = (SELECT max(sal) FROM emp); SELECT ename, deptno, sal, job FROM emp WHERE job IN(SELECT distinct job FROM emp WHERE deptno = 20); SELECT ename, deptno, sal, job FROM emp WHERE job NOT IN(SELECT distinct job FROM emp WHERE deptno = 20); SELECT ename, deptno, sal, job FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 20); SELECT ename, deptno, sal, job FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 20) ORDER BY deptno; SELECT ename, deptno, sal, job FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH'); SELECT ename, deptno, sal, job, mgr FROM emp WHERE job IN(SELECT job FROM emp WHERE deptno = 20) AND mgr IN(SELECT mgr FROM emp WHERE deptno = 20) ORDER BY deptno; SELECT deptno, ( SELECT max(sal) FROM emp b WHERE b.deptno = a.deptno) maxsal FROM emp a ORDER BY deptno; SELECT ename, deptno, sal, job FROM emp WHERE EXISTS( SELECT 'x' FROM dept WHERE dept.deptno = emp.deptno AND dept.loc = 'NEW YORK'); SELECT distinct deptno, ( SELECT max(sal) FROM emp b WHERE b.deptno = a.deptno) maxsal FROM emp a ORDER BY deptno; SET linesize 500; S