MySQLStudy06

常见面试题34道:

SQL文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
SQL数据如下:
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`empno` int(4) NOT NULL,
`ename` varchar(10) DEFAULT NULL,
`job` varchar(9) DEFAULT NULL,
`mgr` int(4) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` double(7,2) DEFAULT NULL,
`comm` double(7,2) DEFAULT NULL,
`deptno` int(2) DEFAULT NULL,
PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

------
-- Records of emp
------
INSERT INTO `emp` VALUES ('7369', 'SIMITH', 'CLERK', '7902', '1980-12-17', '800.00', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975.00', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850.00', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450.00', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000.00', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500.00', null, '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100.00', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950.00', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300.00', null, '10');

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`deptno` int(10) NOT NULL,
`dname` varchar(14) DEFAULT NULL,
`loc` varchar(13) DEFAULT NULL,
PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

------

-- Records of dept

------

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

------

-- Table structure for salgrade

------

DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
`grade` int(11) DEFAULT NULL,
`losal` int(11) DEFAULT NULL,
`hisal` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

------
-- Records of salgrade
------

INSERT INTO `salgrade` VALUES ('1', '700', '1200');
INSERT INTO `salgrade` VALUES ('2', '1201', '1400');
INSERT INTO `salgrade` VALUES ('3', '1401', '2000');
INSERT INTO `salgrade` VALUES ('4', '2001', '3000');
INSERT INTO `salgrade` VALUES ('5', '3001', '5000');



------
-- Table structure for s
------

DROP TABLE IF EXISTS `s`;
CREATE TABLE `s` (
`sno` int(10) NOT NULL,
`sname` varchar(14) DEFAULT NULL,
PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

------

-- Records of s

------

INSERT INTO `s` VALUES ('1', 'a');
INSERT INTO `s` VALUES ('2', 'b');
INSERT INTO `s` VALUES ('3', 'c');
INSERT INTO `s` VALUES ('4', 'd');

------
-- Table structure for c
------

DROP TABLE IF EXISTS `c`;
CREATE TABLE `c` (
`cno` int(10) NOT NULL,
`cname` varchar(14) DEFAULT NULL,
`cteacher` varchar(14) DEFAULT NULL,
PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

------
-- Records of c
------

INSERT INTO `c` VALUES ('1', 'java', '王老师');
INSERT INTO `c` VALUES ('2', 'C++', '张老师');
INSERT INTO `c` VALUES ('3', 'C#', '李老师');
INSERT INTO `c` VALUES ('4', 'mysql', '周老师');
INSERT INTO `c` VALUES ('5', 'oracle', '黎明');



DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`sno` int(10) NOT NULL,
`cno` int(10) NOT NULL,
`scgrade` int(10) DEFAULT NULL,
PRIMARY KEY (`sno`,`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

------
-- Records of sc
------

INSERT INTO `sc` VALUES ('1', '1', '50');
INSERT INTO `sc` VALUES ('1', '2', '50');
INSERT INTO `sc` VALUES ('1', '3', '50');
INSERT INTO `sc` VALUES ('2', '2', '80');
INSERT INTO `sc` VALUES ('2', '3', '70');
INSERT INTO `sc` VALUES ('2', '4', '59');
INSERT INTO `sc` VALUES ('3', '1', '60');
INSERT INTO `sc` VALUES ('3', '2', '61');
INSERT INTO `sc` VALUES ('3', '3', '99');
INSERT INTO `sc` VALUES ('3', '4', '100');
INSERT INTO `sc` VALUES ('3', '5', '52');
INSERT INTO `sc` VALUES ('4', '3', '82');
INSERT INTO `sc` VALUES ('4', '4', '99');
INSERT INTO `sc` VALUES ('4', '5', '40');

题目如下

1、取得每个部门最高薪水人员的名称

1
2
3
select t.deptno,e.ename,t.msal from emp e
join (select deptno,max(sal) msal from emp group by deptno) t
on e.sal =t.msal;

2、哪些人的薪水在部门平均薪水之上?

思路–> 先求平均薪水构成表 a2;联立 emp e 表,和 a2 表

1
2
3
4
select a.ename,a.sal,a.deptno
from emp a
join (select deptno,avg(sal) asal from emp group by deptno) b
on a.sal > b.asal and a.deptno =b.deptno;

3、取得部门中(所有人的)平均的薪水等级

思路–> 计算每个人的薪水等级;然后根据部门编号分组

1
2
3
select e.deptno,avg(s.grade) agrade from emp e
join salgrade s on e.sal between s.losal and s.hisal
group by e.deptno;

4、求最高薪水

1
2
3
> select ename, max(sal) from emp;	
> select ename, sal from emp order by sal desc limit 1;
>

5、取得平均薪水最高的部门和部门编号(至少给出两种解决方案)

1
2
3
create table a5 as select deptno,avg(sal) asal from emp group by deptno;
select deptno,max(asal) masal from a5;
select deptno, asal from a5 order by asal desc limit 1;

6、取得平均薪水最高的部门和部门名称

1
2
3
4
select d.dname,avg(e.sal)asal from emp e 
join dept d on e.deptno = d.deptno group by d.dname
having avg(e.sal) =
(select avg(sal) from emp group by deptno order by avg(sal) desc limit 1);

7、求平均薪水等级最低的部门的部门名称

1
2
3
4
5
select d.dname, avg(s.grade) agrade 
from emp e join dept d on e.deptno =d.deptno
join salgrade s on e.sal between s.losal and s.hisal
group by d.dname
order by agrade asc limit 1;

8、取得比普通员工(院代码没有在 mgr 字段出现的)的最高薪水还要高的领导人姓名

1
2
3
4
select b.ename,b.sal
from (select max(sal) msal from emp where empno not in(select distinct mgr from emp where mgr is not null)) a
join (select * from emp where empno in(select distinct mgr from emp)) b
on a.msal<b.sal;

9、取得薪水最高的前五名员工

1
select * from emp order by sal desc limit 5;

10、取得薪水最高的第六到第十名员工

1
select * from emp order by sal desc limit 5,5;(limit关键字:(5,5]-->从第六名开始到第十名共5个)

11、取得最后入职的 5 名员工

1
select * from emp order by hiredate desc limit 5;

12、取得每个薪水等级有多少员工

1
select s.grade,count(e.empno) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;

课程表题目

有三个表 S(学生表),C(课程表),SC(学生选课表)

S(SNO,SNAME) 代表(学号,姓名)

C(CNO,CNAME,CTEACHER) 代表(课号,课名,教师)

SC(SNO,CNO,SCGRADE) 代表(学号、课号、成绩)

问题:

1、找出没选过‘黎明’老师的所有学生姓名;

2、列出 2 门以上(含 2 门)不及格学生姓名及平均成绩;

3、既学过 1 号课程又学过 2 号课所有学生的姓名。

思路:先在草稿纸模拟数据

s 学生表

sno(pk) sname


1 a

2 b

3 c

4 d

c 学生表

cno(pk) cname cteacher


1 java 王老师

2 C++ 张老师

3 C# 李老师

4 mysql 周老师

5 oracle 黎明

sc 学生选课表【一个主键,两个外键,sno+cno 是复合主键,同时 sno 是外键,con 也是外键】

sno cno scgrade


1 1 50

1 2 50

1 3 50

2 2 80

2 3 70

2 4 59

3 1 60

3 2 61

3 3 99

3 4 100

3 5 52

4 3 82

4 4 99

4 5 40


1、找出没选过‘黎明’老师的所有学生姓名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
思路;-->
第一步:找出黎明老师所授课课程编号
select cno fromc where cteacher = ‘黎明’

第二步:通过学生选课表查询 cno = 上面结果的 sno,这些 sno 都是选过课的学号
select sno fromsc where cno = select cno from c where cteacher = ‘黎明’

第三步:在学生表中查询 sno not in 上面结果的数据
select snamefrom s where sno not in select sno from sc where cno = select cno from c wherecteacher = ‘黎明’();

方法:
select sname from s
where sno not in
(select distinct sno from sc where cno
in(select cno from c where cteacher = '黎明'));

2、列出 2 门以上(含 2 门)不及格学生姓名及平均成绩;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
思路;-->
第一步:列出 2 门以上(含 2 门)不及格学生姓名
select s.sname from sc join s on sc.sno = s.sno wheresc.scgrade <60 group by s.sname having count(*) >= 2;

第二步:找出每一个学生的平均成绩
select sno,avg(scgrade) avggrade from sc group by sno;

第三步: 第一步和第二步联合
select t1.sname,t2.avggrade from (select s.sno,s.sname from sc join s on sc.sno = s.sno
where sc.scgrade <60 group by s.sno,s.sname havingcount(*) >= 2) t1
join (select sc.sno,avg(scgrade) avggrade from sc group bysc.sno) t2
on t1.sno = t2.sno;
方法:
select s.sno,s.sname,avg(sc.scgrade) grade from s
join sc on s.sno = sc.sno where s.sno =
(select sno from sc where scgrade < 60 group by sno having count(sno) >= 2)
group by s.sno;

3、既学过 1 号课程又学过 2 号课所有学生的姓名。

1
2
3
4
5
6
7
8
9
10
11
12
13
思路;-->
第一步:找出学过 1 号课程的学生
select sno from sc where cno = 1

第一步:找出学过 2 号课程的学生
select sno from sc where cno = 2

第三步:第一步和第二步联合
select s.sname from sc join s on s.sno = sc.sno where sc.cno =1 and sc.cno in(select sno from sc where cno = 2)
方法:
select sname from s where sno in
(select sno from sc where cno = 1 and cno in
(select sno from sc where cno = 2));

14、列出所有员工及领导的姓名

1
2
select e.ename as empname,l.ename as leadername 
from emp e left join emp l on e.mgr = l.empno;

15、列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称

1
2
3
select e.empno eno,e.ename ename,e.hiredate edate,l.empno lno,l.ename lname,l.hiredate ldate,d.dname dname 
from emp e join emp l on e.mgr = l.empno and e.hiredate < l.hiredate
join dept d on e.deptno= d.deptno;

16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门

1
select e.*,d.dname  from emp e right join dept d on e.deptno = d.deptno;

17、列出至少有 5 个员工的所有部门

1
2
select d.deptno,d.dname,e.ename,count(e.deptno) from emp e join dept d on e.deptno = d.deptno 
group by e.deptno having count(e.deptno) >= 5;

18、列出薪金比‘simith’多的所有员工信息

1
select * from emp where sal > (select sal from emp where ename = 'simith');

19、列出所有‘clerk’(办事员)的姓名及部门名称,部门人数

1
2
3
4
5
select e.ename,d.dname,c.deptcount
from (select *from emp where job = 'clerk') e
join dept d on e.deptno = d.deptno
join (select deptno,count(deptno) deptcount from emp group by deptno) c
on e.deptno =c.deptno;

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

1
2
select job,min(sal) minsal,count(job) jobcount 
from emp group by job having min(sal) >1500;

21、列出在部门‘sales’(销售部)工作的员工的姓名,假定不知道销售部的部门编号

1
2
3
select d.deptno,d.dname,e.ename 
from emp e join dept d on e.deptno = d.deptno having d.deptno =
(select deptno from dept where dname= 'sales');

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

1
2
3
select a.empno,a.ename,a.mgr,a.deptno,s.grade from 
emp a join (select avg(sal) avgsal from emp) b on a.sal >
b.avgsal join salgrade s on a.sal between s.losal and s.hisal;

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

1
2
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno 
where e.job = (select job from emp where ename = 'scott');

24、列出与‘scott’从事相同工作的所有员工及部门名称(不包括scott)

1
2
3
select e.ename,d.dname 
from emp e join dept d on e.deptno = d.deptno
where e.job = (select job from emp where ename = 'scott')and ename != 'scott';

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

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

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

1
2
3
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);

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

1
2
3
4
5
select d.deptno,count(e.deptno)deptcount,ifnull(avg(e.sal),0) avgsal,
ifnull(avg((to_days(now()) - to_days(hiredate))/365),0) days
from emp e
right join dept d on e.deptno = d.deptno
group by d.deptno;

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

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

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

1
2
select d.*,count(e.deptno) deptcount 
from emp e right join dept d on e.deptno = d.deptno group by e.deptno;

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

1
2
3
select e.ename,e.sal,a.minsal from emp e
join (select job,min(sal) minsal from emp group by job) a
on e.sal = a.minsal;

31、列出各个部门的 manager 的最低薪金

1
2
select e.ename,e.job,min(e.sal),e.deptno minsal 
from emp e where e.job = 'manager' group by e.deptno;

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

1
select *,(sal + ifnull(comm,0)) * 12 ysal from emp order by ysal asc;

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

1
2
3
select e.ename empname,e.job empjob,e.sal empsal,l.ename 	leadername,l.sal leadersal
from emp e join emp l on e.mgr = l.empno
where l.sal> 3000;

34、求出部门名称中,带有‘s’字符的部门员工的工资合计、部门人数

1
2
3
4
select d.dname,sum(e.sal) cumsal,count(e.deptno)deptcount
from emp e right join dept d on e.deptno = d.deptno
group by e.deptno
having d.dname like '%s%';

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

1
2
3
第一步:mysql> create table emp_bak as select * from emp;
第二步:mysql> update emp_bak set sal = sal * 1.1 where((to_days(now()) - to_days(hiredate))/365) > 35;
第三步:mysql> select * from emp_bak;

------------------------------------本文结束感谢您的阅读--------------------------------
wangyekun wechat
扫一扫关注一波
坚持原创,您的支持将鼓励我继续创作!