完成如下的SQL语句练习:
每个员工的所有信息
SQL> select * from empscott; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7839 KING PRESIDENT 1981/11/17 5000.00 10 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7900 JAMES CLERK 7698 1981/12/3 950.00 30 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 14 rows selected |
每个人的部门编号,姓名,薪水
SQL> select deptno,ename,sal from empscott; DEPTNO ENAME SAL ------ ---------- --------- 20 SMITH 800.00 30 ALLEN 1600.00 30 WARD 1250.00 20 JONES 2975.00 30 MARTIN 1250.00 30 BLAKE 2850.00 10 CLARK 2450.00 20 SCOTT 3000.00 10 KING 5000.00 30 TURNER 1500.00 20 ADAMS 1100.00 30 JAMES 950.00 20 FORD 3000.00 10 MILLER 1300.00 14 rows selected |
每个人的年薪
SQL> select ename,sal*12 from empscott; ENAME SAL*12 ---------- ---------- SMITH 9600 ALLEN 19200 WARD 15000 JONES 35700 MARTIN 15000 BLAKE 34200 CLARK 29400 SCOTT 36000 KING 60000 TURNER 18000 ADAMS 13200 JAMES 11400 FORD 36000 MILLER 15600 14 rows selected |
求每个人的年薪,列的别名:“年薪”
SQL> select ename,sal*12 as "年薪" from empscott; ENAME 年薪 ---------- ---------- SMITH 9600 ALLEN 19200 WARD 15000 JONES 35700 MARTIN 15000 BLAKE 34200 CLARK 29400 SCOTT 36000 KING 60000 TURNER 18000 ADAMS 13200 JAMES 11400 FORD 36000 MILLER 15600 14 rows selected |
求 10 这个部门的所有员工
SQL> select * from empscott where deptno=10; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7839 KING PRESIDENT 1981/11/17 5000.00 10 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 |
求名字是 KING 的这个人的信息
SQL> select * from empscott where ename='KING'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7839 KING PRESIDENT 1981/11/17 5000.00 10 |
求薪水大于 2000 的员工信息
SQL> select * from empscott where SAL>2000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7839 KING PRESIDENT 1981/11/17 5000.00 10 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 6 rows selected |
求部门不是 10 的员工信息
SQL> select * from empscott where deptno<>10; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7900 JAMES CLERK 7698 1981/12/3 950.00 30 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 11 rows selected |
求薪水在 800 和 1500 之间的员工信息(包含 800 和 1500)
SQL> select * from empscott where SAL BETWEEN 800 AND 1500; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7900 JAMES CLERK 7698 1981/12/3 950.00 30 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 7 rows selected |
列出 deptno 为 10 或者 30,并且工资>2000 的所有人
SQL> select * from empscott where deptno in(10,30) and sal>2000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7839 KING PRESIDENT 1981/11/17 5000.00 10 |
利用 in 操作符,列出部门 10 和 20 的人员
SQL> select * from empscott where deptno in(10,20); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7839 KING PRESIDENT 1981/11/17 5000.00 10 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 8 rows selected |
利用 like 操作符,查处名字中含有"H"的人员
SQL> select * from empscott where ename like '%H%'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 |
分别利用 like 操作符和正则表达式,查处名字中含有"S"或者"M"的人员
SQL> select * from empscott where ename like '%S%' or ename like '%M%'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980/12/17 800.00 20 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 7900 JAMES CLERK 7698 1981/12/3 950.00 30 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 7 rows selected |
计算 emp 表中的所有人员的平均薪水
SQL> select avg(sal) from empscott; AVG(SAL) ---------- 2073.21428 |
计算 emp 表中最高薪水
SQL> select max(sal) from empscott; MAX(SAL) ---------- 5000 |
计算 emp 表中最低薪水
SQL> select min(sal) from empscott; MIN(SAL) ---------- 800 |
计算 emp 表中薪水大于 1000 的人员的个数
SQL> select count(*) from empscott where sal>1000; COUNT(*) ---------- 12 |
计算 emp 表中薪水的总和
SQL> select sum(sal) from empscott; SUM(SAL) ---------- 29025 |
计算 emp 表中薪水和津贴的总和
SQL> select sum(sal)+sum(comm) from empscott; SUM(SAL)+SUM(COMM) ------------------ 31225 |
求各部门最高薪水
SQL> select deptno,max(sal) from empscott group by deptno; DEPTNO MAX(SAL) ------ ---------- 30 2850 20 3000 10 5000 |
按照部门和职位分组,分别求最高薪水,该组人员个数
SQL> select deptno,job,max(sal),count(*) from empscott group by deptno,job order by deptno; DEPTNO JOB MAX(SAL) COUNT(*) ------ --------- ---------- ---------- 10 CLERK 1300 1 10 MANAGER 2450 1 10 PRESIDENT 5000 1 20 ANALYST 3000 2 20 CLERK 1100 2 20 MANAGER 2975 1 30 CLERK 950 1 30 MANAGER 2850 1 30 SALESMAN 1600 4 9 rows selected |
求薪水最高的员工姓名
SQL> select ename from empscott where sal=(select max(sal) from empscott); ENAME ---------- KING |
求平均薪水是 2000 以上的部门
SQL> select e.deptno,d.dname,avg(sal) from empscott e inner join deptscott d on d.deptno=e.deptno group by e.deptno,d.dname having avg(sal)>2000; DEPTNO DNAME AVG(SAL) ------ -------------- ---------- 10 ACCOUNTING 2916.66666 20 RESEARCH 2175 |
求每个部门的平均薪水,并按照薪水降序排列
SQL> select deptno,avg(sal) from empscott group by deptno order by avg(sal) desc; DEPTNO AVG(SAL) ------ ---------- 10 2916.66666 20 2175 30 1566.66666 |
求每个部门薪水在 1200 以上的雇员的平均薪水、最高薪水,并且分组结果
中只包含平均薪水大于 1500 的部门,排序按照部门平均薪水倒序排列
SQL> select deptno,avg(t.sal),max(t.sal) from (select deptno,ename,sal from empscott where sal>1200 group by deptno,ename,sal)t having avg(t.sal)>1500 group by deptno order by avg(t.sal) desc; DEPTNO AVG(T.SAL) MAX(T.SAL) ------ ---------- ---------- 20 2991.66666 3000 10 2916.66666 5000 30 1690 2850 |
把雇员按部门分组, 求最高薪水, 部门号, 过滤掉名字中第二个字母是'A'的, 要
求分组后的平均薪水>1500, 按照部门编号倒序排列
SQL> select deptno,max(sal) from empscott where ename not like '_A%' group by deptno having avg(sal)>1500 order by deptno desc; DEPTNO MAX(SAL) ------ ---------- 30 2850 20 3000 10 5000 |
求平均薪水最高的部门的部门编号
SQL> select deptno from (select deptno, avg(sal) from empscott group by deptno order by avg(sal) desc) where rownum=1; DEPTNO ------ 10 |
求出 emp 表中哪些人是经理人,打印出名字和编号
SQL> select ename,empno from empscott where job='MANAGER'; ENAME EMPNO ---------- ----- JONES 7566 BLAKE 7698 CLARK 7782 |
求比普通员工的最高薪水还要高的经理人名称
SQL> select ename,empno from empscott where job='MANAGER' AND SAL>(SELECT MAX(SAL) FROM EMPSCOTT WHERE JOB<>'MANAGER'); ENAME EMPNO ---------- ----- |
每个部门平均薪水的等级(需要用到表的连接)
SQL> select e.deptno, s.grade from (select deptno, avg(sal) avg_sal from empSCOTT group by deptno) e join salgrade s on e.avg_sal between s.losal and s.hisal; DEPTNO GRADE ------ ---------- 30 3 20 4 10 4 |
求部门经理人中平均薪水最低的部门名称
SQL> select deptno, d.dname from (select deptno, avg(sal) from empSCOTT where job='MANAGER' group by deptno order by avg(sal)) natural join deptSCOTT d where rownum=1; DEPTNO DNAME ------ -------------- 10 ACCOUNTING |
求薪水最高的前 5 名雇员
SQL> select * from (select * from empSCOTT order by sal desc) where rownum<=5; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7839 KING PRESIDENT 1981/11/17 5000.00 10 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 |
求薪水最高的第 6 到第 10 名雇员
SQL> select * from (select rownum rn, t.* from (select * from empSCOTT order by sal desc) t where rownum<=10) tt where tt.rn>5 and rownum<=10; RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ----- ---------- --------- ----- ----------- --------- --------- ------ 6 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 7 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 8 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 9 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 10 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 |