【数据库34道习题(二)】通过34道习题来真正掌握sql语句的运用

首要:三表的全部数据查询

emp

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

dept

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

salgrade

mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

18、列出薪金比"SMITH" 多的所有员工信息

select
	*
from
	emp 
where
	sal > (select sal from emp where ename = 'smith');
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

19、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数

找出是办事员的员工姓名

select ename,job from emp where job = 'clerk';
+--------+-------+
| ename  | job   |
+--------+-------+
| SMITH  | CLERK |
| ADAMS  | CLERK |
| JAMES  | CLERK |
| MILLER | CLERK |
+--------+-------+

找出是办事员的员工的部门名称

select
	e.ename,e.job,d.dname,d.deptno
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
where
	e.job = 'clerk';
+--------+-------+------------+--------+
| ename  | job   | dname      | deptno |
+--------+-------+------------+--------+
| MILLER | CLERK | ACCOUNTING |     10 |
| SMITH  | CLERK | RESEARCH   |     20 |
| ADAMS  | CLERK | RESEARCH   |     20 |
| JAMES  | CLERK | SALES      |     30 |
+--------+-------+------------+--------+

求每个部门的人数

select deptno,count(*) as deptcount from emp group by deptno;
+--------+-----------+
| deptno | deptcount |
+--------+-----------+
|     10 |         3 |
|     20 |         5 |
|     30 |         6 |
+--------+-----------+

办事员的员工的部门名称的表与每个部门的人数的表连接 在找出名字等,连接条件为编号相等

select 
	t1.*,t2.deptcount
from
	(select 
		e.ename,e.job,d.dname,d.deptno
	from 
		emp e
	join
		dept d
	on
		e.deptno = d.deptno
	where 
		e.job = 'CLERK') t1
join
	(select deptno, count(*) as deptcount from emp group by deptno) t2
on
	t1.deptno = t2.deptno;


+--------+-------+------------+--------+-----------+
| ename  | job   | dname      | deptno | deptcount |
+--------+-------+------------+--------+-----------+
| MILLER | CLERK | ACCOUNTING |     10 |         3 |
| SMITH  | CLERK | RESEARCH   |     20 |         5 |
| ADAMS  | CLERK | RESEARCH   |     20 |         5 |
| JAMES  | CLERK | SALES      |     30 |         6 |
+--------+-------+------------+--------+-----------+

20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数

按照工作岗位分组求最小值。

select
	job,count(*)
from
	emp
group by
	job
having
	min(sal) > 1500;

+-----------+----------+
| job       | count(*) |
+-----------+----------+
| ANALYST   |        2 |
| MANAGER   |        3 |
| PRESIDENT |        1 |
+-----------+----------+

21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.

select
	ename
from
	emp
where
	deptno = (select deptno from dept where dname = 'sales');

+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+

22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.

求出平均薪资
表连接查询出部门上级领导和工资等级

select
	e.ename as '员工',b.ename as '上级领导',d.dname,s.grade as salgrade
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	emp b
on
	e.mgr = b.empno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
where
	e.sal > (select avg(sal) from emp);
	
+-------+----------+------------+----------+
| 员工     | 上级领导      | dname      | salgrade |
+-------+----------+------------+----------+
| JONES | KING     | RESEARCH   |        4 |
| BLAKE | KING     | SALES      |        4 |
| CLARK | KING     | ACCOUNTING |        4 |
| SCOTT | JONES    | RESEARCH   |        4 |
| FORD  | JONES    | RESEARCH   |        4 |
+-------+----------+------------+----------+

注意!!!!这里少了一个king 原因是表的自连接时 king的上级领导为空,所以没有显示,因此自连接的时候要使用外连接 left join…

select
	e.ename as '员工',b.ename as '上级领导',d.dname,s.grade as salgrade
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
left join
	emp b
on
	e.mgr = b.empno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
where
	e.sal > (select avg(sal) from emp);


+-------+----------+------------+----------+
| 员工     | 上级领导      | dname      | salgrade |
+-------+----------+------------+----------+
| JONES | KING     | RESEARCH   |        4 |
| BLAKE | KING     | SALES      |        4 |
| CLARK | KING     | ACCOUNTING |        4 |
| SCOTT | JONES    | RESEARCH   |        4 |
| KING  | NULL     | ACCOUNTING |        5 |
| FORD  | JONES    | RESEARCH   |        4 |
+-------+----------+------------+----------+

23、 列出与"SCOTT" 从事相同工作的所有员工及部门名称

select job from emp where ename = 'SCOTT';
+---------+
| job     |
+---------+
| ANALYST |
+---------+
select 
	e.ename,e.job,d.dname
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
where
	e.job = (select job from emp where ename = 'SCOTT')
and
	e.ename <> 'SCOTT';

+-------+---------+----------+
| ename | job     | dname    |
+-------+---------+----------+
| FORD  | ANALYST | RESEARCH |
+-------+---------+----------+

24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.

部门 30 中员工的薪金

select distinct e.sal from emp e where e.deptno = 30;

+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+

select 
	e.ename,e.sal 
from 
	emp e 
where 
	e.sal in (select distinct e.sal from emp e where e.deptno = 30) and e.deptno != 30;

Empty set (0.00 sec)

注意这里不能用等于,因为查询的结果是多个,等于是一对一,所以要用in

25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称

找出部门30中的最高工资

select max(sal) maxsal from emp where deptno = 30;

+---------+
| maxsal  |
+---------+
| 2850.00 |
+---------+
select
	e.ename,e.sal,d.dname
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
where
	e.sal > (select max(sal) from emp where deptno = 30);

+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| KING  | 5000.00 | ACCOUNTING |
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+

26、列出在每个部门工作的员工数量, 平均工资和平均服务期限

没有员工的部门,部门人数是0

select 
	d.deptno, count(e.ename) ecount,ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
group by
	d.deptno;

+--------+--------+-------------+----------------+
| deptno | ecount | avgsal      | avgservicetime |
+--------+--------+-------------+----------------+
|     10 |      3 | 2916.666667 |        38.0000 |
|     20 |      5 | 2175.000000 |        35.8000 |
|     30 |      6 | 1566.666667 |        38.3333 |
|     40 |      0 |    0.000000 |         0.0000 |
+--------+--------+-------------+----------------+

在mysql当中怎么计算两个日期的“年差”,差了多少年?
TimeStampDiff(间隔类型, 前一个日期, 后一个日期)

timestampdiff(YEAR, hiredate, now())

间隔类型:
	SECOND   秒,
	MINUTE   分钟,
	HOUR   小时,
	DAY   天,
	WEEK   星期
	MONTH   月,
	QUARTER   季度,
	YEAR   年

27、 列出所有员工的姓名、部门名称和工资

select
	e.ename,d.dname,e.sal
from
	emp e
join
	dept d
on
	e.deptno = d.deptno;

+--------+------------+---------+
| ename  | dname      | sal     |
+--------+------------+---------+
| CLARK  | ACCOUNTING | 2450.00 |
| KING   | ACCOUNTING | 5000.00 |
| MILLER | ACCOUNTING | 1300.00 |
| SMITH  | RESEARCH   |  800.00 |
| JONES  | RESEARCH   | 2975.00 |
| SCOTT  | RESEARCH   | 3000.00 |
| ADAMS  | RESEARCH   | 1100.00 |
| FORD   | RESEARCH   | 3000.00 |
| ALLEN  | SALES      | 1600.00 |
| WARD   | SALES      | 1250.00 |
| MARTIN | SALES      | 1250.00 |
| BLAKE  | SALES      | 2850.00 |
| TURNER | SALES      | 1500.00 |
| JAMES  | SALES      |  950.00 |
+--------+------------+---------+

28、列出所有部门的详细信息和人数

select 
	d.deptno,d.dname,d.loc,count(e.ename)
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
group by
	d.deptno,d.dname,d.loc;

+--------+------------+----------+----------------+
| deptno | dname      | loc      | count(e.ename) |
+--------+------------+----------+----------------+
|     10 | ACCOUNTING | NEW YORK |              3 |
|     20 | RESEARCH   | DALLAS   |              5 |
|     30 | SALES      | CHICAGO  |              6 |
|     40 | OPERATIONS | BOSTON   |              0 |
+--------+------------+----------+----------------+

29、列出各种工作的最低工资及从事此工作的雇员姓名

找出各种工作中的最低工资

select
	job,min(sal) as minsal 
from
	emp
group by
	job;

+-----------+---------+
| job       | minsal  |
+-----------+---------+
| ANALYST   | 3000.00 |
| CLERK     |  800.00 |
| MANAGER   | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN  | 1250.00 |
+-----------+---------+

emp e和以上t连接

select 
	e.ename,t.*
from
	emp e
join
	(select 
		job,min(sal) as minsal
	from
		emp
	group by
		job) t
on
	e.job = t.job and e.sal = t.minsal;

+--------+-----------+---------+
| ename  | job       | minsal  |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| FORD   | ANALYST   | 3000.00 |
+--------+-----------+---------+

30、列出各个部门的 MANAGER( 领导) 的最低薪金

select
	deptno,min(sal) as minsal
from
	emp
where
	job = 'manager'
group by
	deptno;


+--------+---------+
| deptno | minsal  |
+--------+---------+
|     10 | 2450.00 |
|     20 | 2975.00 |
|     30 | 2850.00 |
+--------+---------+

31、列出所有员工的 年工资, 按 年薪从低到高排序

select
	ename,(sal + ifnull(comm,0)) * 12 as yearsal
from
	emp
order by
	yearsal asc;

+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| WARD   | 21000.00 |
| ALLEN  | 22800.00 |
| CLARK  | 29400.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| FORD   | 36000.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
+--------+----------+

如果comm为空则当成0

32、求出员工领导的薪水超过3000的员工名称与领导

select
	a.ename as '员工',b.ename as '领导',b.sal
from
	emp a
left join
	emp b
on
	a.mgr = b.empno
where
	b.sal > 3000;


+-------+------+---------+
| 员工     | 领导   | sal     |
+-------+------+---------+
| JONES | KING | 5000.00 |
| BLAKE | KING | 5000.00 |
| CLARK | KING | 5000.00 |
+-------+------+---------+

33、求出部门名称中, 带’S’字符的部门员工的工资合计、部门人数

select
	d.deptno,d.dname,count(e.ename),ifnull(sum(e.sal),0) as sumsal
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
where
	d.dname like '%s%'
group by
	d.deptno,d.dname;

+--------+----------+----------------+----------+
| deptno | dname    | count(e.ename) | sumsal   |
+--------+----------+----------------+----------+
|     20 | RESEARCH |              5 | 10875.00 |
|     30 | SALES    |              6 |  9400.00 |
+--------+----------+----------------+----------+

注意 !!!!又漏了一个数据,因为没有加上右外连接

select
	d.deptno,d.dname,count(e.ename),ifnull(sum(e.sal),0) as sumsal
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
where
	d.dname like '%s%'
group by
	d.deptno,d.dname;
+--------+------------+----------------+----------+
| deptno | dname      | count(e.ename) | sumsal   |
+--------+------------+----------------+----------+
|     20 | RESEARCH   |              5 | 10875.00 |
|     30 | SALES      |              6 |  9400.00 |
|     40 | OPERATIONS |              0 |     0.00 |
+--------+------------+----------------+----------+

34、给任职日期超过 30 年的员工加薪 10%.

update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值