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;

 将以上语句放入一个sql文件中，如sqldemo.sql，在sqlplus中使用@ ${filepath}\sqldemo.sql命令执行查看结果。