SQL简单查询练习

1、 部门表：DEPT

No.	字段名称	类型	描述
1	DEPTNO	BIGINT	部门编号，由两位数字所组成(主键)
2	DNAME	VARCHAR(20)	部门名称，由14位字符所组成
3	LOC	VARCHAR(20)	部门位置

2、 雇员表：EMP

No.	字段名称	类型	描述
1	EMPNO	BIGINT	雇员编号，由四位数字所组成(主键)
2	ENAME	VARCHAR(20)	雇员姓名
3	JOB	VARCHAR(20)	职位
4	MGR	BIGINT	一个雇员对应的领导编号（雇员编号）
5	HIREDATE	DATE	雇员的雇佣日期
6	SAL	DOUBLE(7,2)	基本工资，由两位小数和五位整数所组成
7	COMM	DOUBLE(7,2)	奖金/奖金
8	DEPTNO	BIGINT	一个雇员所在的部门编号，与dept表的对应

DEPT表数据:

INSERT INTO `DEPT` VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO `DEPT` VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO `DEPT` VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO `DEPT` VALUES (40, 'OPERATIONS', 'BOSTON');

EMP表数据:

INSERT INTO `EMP` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20); INSERT INTO `EMP` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30); INSERT INTO `EMP` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30); INSERT INTO `EMP` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20); INSERT INTO `EMP` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30); INSERT INTO `EMP` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30); INSERT INTO `EMP` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10); INSERT INTO `EMP` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20); INSERT INTO `EMP` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10); INSERT INTO `EMP` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30); INSERT INTO `EMP` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20); INSERT INTO `EMP` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30); INSERT INTO `EMP` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20); INSERT INTO `EMP` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

练习题：

01.查询每个雇员的编号、姓名、职位。

Select EMPNO,ENAME,JOB from EMP;

02.查询每个雇员的职位，职位。 Select JOB from EMP;

03.查询每个雇员的职位，使用DISTINCT消除掉显示的重复行记录。 Select DISTINCT JOB from EMP;

04.计算出每个雇员的基本年薪，同时查询出雇员的编号、姓名。 Select SAL*12,EMPNO,ENAME from EMP;

05.每个雇员每个月公司会补贴饭食200元，交通补助300元，计算年薪（年薪=(工资+奖金)*12）。 Select ((SAL+200+300)*12) from EMP;

06.查询基本工资高于2000的全部雇员信息。 Select ENAME from EMP where SAL>2000;

07.查询出smith的信息。 Select * from EMP where ENAME = ‘SMITH’;

08.查询出所有不是CLERK的详细信息。 Select * from EMP where JOB != ‘CLERK’;

09.查询出所有销售人员(SALESMAN)的基本信息，并且要求销售人员的工资高于1300。 Select ENAME from EMP where JOB = ‘SALESMAN’and SAL>1300;

10.查询出工资范围在1500~3000之间的全部雇员信息（包含1500和3000）。 Select ENAME from EMP where SAL >= 1300 and SAL <= 1500;

11.查询出所有经理或者是销售人员的信息，并且要求这些人的基本工资高于1500。 Select ENAME from EMP where SAL > 1500;

12.要求查询出所有在1981年雇佣的雇员信息。 SELECT * FROM EMP WHERE HIREDATE BETWEEN'1-1月-1981' AND'31-12月-1981';

13.查询所有领取奖金的雇员信息（comm不为空）。 Select ENAME from EMP where COMM >= 0;

14.查询所有领取奖金高于100的雇员信息。 Select ENAME from EMP where COMM > 100;

15.查询出雇员编号是7369、7566、9999的雇员信息。 Select ENAME from EMP where EMPNO = '7369'or empno = '7566' or empno = '9999';

16.查询出所有雇员姓名是以A开头的全部雇员信息。 select JOB from emp where JOB like 'A%';

17.查询出雇员姓名第二个字母是M的全部雇员信息。 select JOB from emp where JOB like '_M%';

18.查询出雇员姓名任意位置上包含字母A的全部雇员信息。 select JOB from emp where JOB like '%A%';

19.查询出所有雇员的信息，要求按照工资排序。  select ENAME from EMP order by SAL;

20.要求查询所有雇员的信息，按照雇佣日期由先后排序 select SAL from EMP order by SAL;

21.查询全部雇员信息，按照工资由高到低排序，如果工资相同，则按照雇佣日期由先后排序。 select SAL from EMP order by SAL desc,HIREDATE asc;

22.查询部门30中的所有员工。  Select ENAME from EMP where DEPTNO = 30;

23.查询出所有办事员（CLERK）的姓名，编号和部门编号。 Select ENAME,MGR,DEPTNO from EMP where JOB = 'CLERK';

24.查询出奖金高于薪金的员工。  Select ENAME from EMP where COMM > SAL;

25.查询出奖金高于薪金的60%的员工。 Select ENAME from EMP where COMM > (SAL*0.6);

26.查询出部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料。   Select ENAME,MGR,DEPTNO from EMP where JOB = 'MANAGER'or JOB = 'CLERK';

27.查询出部门10中所有经理，部门20中所有办事员，既不是经理又不是办事员但其薪金大于或等于2000的所有员工的信息。  select * from emp where (DEPTNO = 10 and job != MANAGER or DEPTNO=20 and job != CLERK) sal > 2000;

28.查询出收取奖金的员工的不同工作。 select distinct JOB from emp where comm >= 0;

29.查询出不收取奖金或收取的奖金低于100的员工。 select ename  from emp where (nvl(comm,0)<100);

30.查询出不带有“R”的员工的姓名。 select JOB from emp where JOB not like '%R%';

31.查询出每个雇员的姓名、职位、领导姓名。 select c.ename,c.JOB,B.ename  from emp c,emp B where c.mgr = B.EMPNO;

32.查询出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。 select a.empno,a.ename,b.empno,b.ename,(b.sal+nvl(b.comm,0))*12"工资" from emp a ,emp b where a.mgr = b.empno order by (b.sal+nvl(b.comm,0))*12 desc;

33.查询出在销售部（SALES）工作的员工姓名、基本工资、雇佣日期、部门名称。（不知道销售部编号）。  select ename,sal,HIREDATE,job  from emp  where DEPTNO = (select DEPTNO from dept where dname = 'SALES');

34.查询出所有员工的姓名、部门名称和工资。 Select ENAME,JOB,SAL from EMP;

35.查询出所有员工的年工资，所在部门名称，按年薪从低到高排序。 select ename,(sal+nvl(comm,0))*12,job from emp order by (sal+nvl(comm,0))*12 desc;

36.查询出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000。 select distinct a.ename,c.DNAME,a.sal from emp a ,emp b ,dept c where a.EMPNO = b.MGR and a.sal>3000 and a.DEPTNO = c.DEPTNO;

37.查询出公司的最高和最低工资。 select max(sal),min(sal) from emp;

38.查询出每个部门的人数、平均工资，只显示部门编号。  select deptno,count(ename),avg(sal) from emp group by deptno;

39.查询出每种职位的最高和最低工资。 select job, MAX(sal),MIN(sal) FROM emp group by job;

40.查询平均工资高于2000的职位信息，以及从事此职位的雇员人数、平均工资。 select distinct job,avg(sal),count(ENAME) from emp group by  job having avg(sal)>2000;

41.查询出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。 select max(SAL),min(SAL),avg(SAL),e.DEPTNO,d.dname from emp e join dept d  on e.DEPTNO = d.DEPTNO group by e.DEPTNO,d.dname;

42.查询出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。 select dept.DNAME,COUNT(ename),nvl(AVG(sal),0) from emp right JOIN dept on emp.DEPTNO = dept.DEPTNO group by dept.DNAME;

43.插入自己和左右同桌的信息 insert into emp values(1,'张三','码农',8888,'1/1月/2021',2000.00,100.00,20); insert into emp values(2,'王五','码农',9888,'1/1月/2020',2100.00,110.00,10); insert into emp values(3,'自己','老板',9888,'1/1月/1980',8100.00,11000.00,30);

44.修改右边/左边同桌的信息(工资,奖金,雇佣日期) update emp set HIREDATE='1/1月/1992',SAL=20000,comm=2000 where EMPNO = 1; update emp set HIREDATE='1/1月/1929',SAL=30000,comm=3000 where EMPNO = 2;

45.删除自己的个人信息 Delete from emp where EMPNO=3;