对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;
将以上语句放入一个sql文件中,如sqldemo.sql,在sqlplus中使用@ ${filepath}\sqldemo.sql命令执行查看结果。