数据库面试题

1、数据库有两张表为user表和user_role表，执行：SELECT u.*，r.name FROM USER u LEFT JOIN user_role r ON u.id = r.user_id 之后获取到的结果为:(A)

联查:
	left join/right join :
		特点:主表的所有数据均显示,另外一张表中有匹配的数据,则显示,若无匹配的数据,该字段显示为null
	inner join:
		特点:查询的数据是满足所有条件的数据,若没有满足所有条件的数据,则结果集为空
user表数据:             user_role表数据:
    id username             id name user_id
    1  张三                  1 售后   1
    2  李四                  2 运维   2
    3  王五                  3 研发   3
    4  小刘                  4 实施   5 
A.  1 张三 售后  
    2 李四 运维 
    3 王五 研发 
    4 小刘 null
B.  1 张三 售后 
    2 李四 运维 
    3 王五 研发
C.  1 张三 售后 
    2 李四 运维 
    3 王五 研发
    null null 实施

2、请描述查询语句中各部分的执行顺序 -- 面试

select distinct...from t1 (left/right) join t2  on t1.xx=t2.xx where t1.xx=? and t2.xx=? group by t1.xx having ...order by ... limit ..

where:  筛选条件   分组之前
having: 筛选条件   前提:必然先分组    对分组后的结果再次筛选
where,group by,having三者同时出现,则顺序一定为:
	where....group by ... having ....
	
limit用法:
	limit m,n   从索引m处开始,筛选n条件数据 -- 实现分页
	limit m   从第一条数据开始,筛选前m条


1. from  join   产生虚拟表v1  - 产生的是笛卡尔积
	笛卡尔积：两表完全连接的结果叫做笛卡尔积，会产生m*n条数据
2. on    根据连接条件进行筛选,基于V1,产生虚拟表v2   
3. left/right join 若是外连接,将主表中的所有数据进行补充到v2表中
4. where   根据查询条件,从v2中进行数据筛选,产生虚拟表v3
5. group by  对v3分组,产生虚拟表v4
	经常会和聚合函数配合使用，聚合函数的结果在group by之后就会产生
	使用场景: 统计每个班级的学生人数
		select   count(xx)  ....group by bj_id
		    统计每个部门的人数
		select   count(xx)  ... group by d.id
6. having    对分组后的结果再次筛选 ,产生虚拟表v5
7. select    选择最终要显示的字段,产生虚拟表v6
8. distinct  对结果集进行去重,产生虚拟表v7
9. order by  对结果根据某字段进行升序/降序排列 
10. limit    选择需要的数据

   where 和having区别：
   	  where是在分组之前进行数据筛选，having是对分组后的结果再次筛选
   	  where,group by,having若同时出现，则三者的顺序一定是
   	    where在 group by之前，having在group by之后

 3、表名：stu_score

id	name	score	bj
1	赵一	89	1班
2	钱二	88	2班
3	孙三	84	1班
4	李四	86	2班
5	王五	87	1班
6	吴六	91	2班

获取每个班级的最后一名

final.sql中最后一张表
考点:group by能查到什么
    group by cola  
    	可以查询到  分组后的cola,相关的聚合函数,与cola具有一对一关系的列,除此之外,其他字段都查询不到
正确的思路:
	1. 查询出每个班级的最低分
	2. 根据上述的查询结果,再次到stu_Score表中查询,查询某个班级最低分对应的学生是谁
	
	select ss.* from stu_score ss join 
	(select bj,min(score) min from stu_score group by bj)tmp
	on ss.score=tmp.min and ss.bj=tmp.bj

 4、表user，字段有：id（非自增，不连续），age，name，sex，addTime。求创建时间（addTime）在2020-05-22至2020-06-17之间所有数据

查询某段时间区间内的数据,使用between..and...  ,也可以使用>  <
select * from user where addTime between '2020-05-22' and '2020-06-17'
 数据库中between..and两个边界值都包含

5、基于表user，查出表中第10条至第20条连续分页数据的sql语句

select * from user limit 9,11

 

 5. 有以下表以及数据，写出以下sql

student表:

sno	sname	sage
1	周杰伦	18
2	周润发	18
3	吴孟达	25
4	刘德华	25
5	李连杰	29

 course表：

cno	cname
1	语文
2	数学
3	英语

score表：

sno	cno	score
1	1	60
1	2	61
2	1	80

三张表分别为:student ,course,score

1.查询所有学生的学号，姓名，选课数，总成绩

分析题意,得出需要使用Student表和score两表外连接查询,其中student表为主表
从student表中查询sno,sname,选课数和总成绩查询时需要根据学号分组,必须根据是student表的sno分组,不能根据score表的sno分组.

select s.sno,sname,count(cno)'选课数',ifnull(sum(score),0)'总成绩' from student s left join score sc on s.sno=sc.sno group by s.sno

若结果为null,想替换为其他值,则可以使用数据库提供的函数 ifnull(exp1,exp2)

count()
	该函数中可以用* ,也可以用列名
		count(*): 统计有多少行
		count(sno)

2.查询没有学完所有课程的学生学号，姓名

思路: 查询选课数<总课程数的学生
1. 查询总课程数
	select count(*) from course1 
2. 统计每个学生的选课数,然后筛选选课数<总课程数的
	select s.sno,sname from student s left join score sc on s.sno=sc.sno group by s.sno having count(cno)<(select count(*) from course1 )

6、 已知以下表结构

班级表（class）

编号	班级名称
id	class_name
1	一班
2	二班
3	三班
4	四班
5	五班

学生表（student）:

编号	学号	姓名	性别	所属班级
id	stu_no	stu_name	stu_gender	class_id
1	2020001	张三	男	1
2	2020002	李四	男	1
3	2020003	李丽	女	2
4	2020004	赵婷	女	3
5	2020005	王五	男	3

成绩表（score）:

学生	语文	数学
stu_id	chinese	math
1	70	47
2	80	60
3	50	82
4	80	90

业务场景限制：

1. 一个班级有多名学生，一名学生只属于一个班级
2. 学生有可能没有成绩

题目：

1.查询所有学生的信息（学号，姓名，性别，班级名称）

select stu_no,stu_name,stu_gender,class_name from student s join class c on s.class_id=c.id

 2.查询所有人(包括没有成绩的学生)的课程分数（学号，姓名，性别，班级名称，语文分数，数学分数）

三表联查,其中student表和class 表内联;student表和score是外连接查询
多表联查,既有内联又有外联的语法:
	select...from A join B on A.xx=B.xx  left join C on A.xx=C.xx
	不要写成:
		select..from A join B left join C on A.xx=B.xx and A.xx=C.xx
		
	若三表全都内联语法:
		select..from A join B join C on A.xx=B.xx and A.xx=C.xx
		
		不要写成:
			select..from A join B on A.XX=B.xx join C on A.xx=c.XX 
				-- 语法通过,结果也是正确的

select stu_no,stu_name,stu_gender,class_name,chinese,math  from student s join class c on s.class_id=c.id left join score sc on s.id=sc.stu_id

3.查询语文分数比“张三”高的学生（学号，姓名，性别，班级名称，语文分 数）

1. 查询张三的语文分数
	SELECT chinese FROM student s JOIN score sc ON s.id=sc.stu_id WHERE 
stu_name='张三'
2. 三表联查,查询语文>张三语文分的学生信息 -- 三表内联查询结果即可
	select stu_no,stu_name,stu_gender,class_name,chinese from student s join class c join score sc on s.class_id=c.id and s.id=sc.stu_id where chinese>(SELECT chinese FROM student s JOIN score sc ON s.id=sc.stu_id WHERE 
stu_name='张三')

4.查询各科都合格（分数>=60）的学生（学号，姓名，语文分数，数学分数）

select stu_no,stu_name,chinese,math from student s join score sc on s.id=sc.stu_id where chinese>=60 and math>=60

5.查询班级人数>=2的班级（班级编号，班级名称，人数）

1. 求出每个班级的人数,然后筛选人数>=2的班级 -- 注意:无需使用外连接
 select class_id,class_name,count(stu_no) cou from student s join class c on s.class_id=c.id group by class_id having cou>=2
count()聚合函数的使用
	count(*):统计行
	count(col):对这列中的数据进行计数

4、有一张表score，三个字段名，姓名，课程，分数，数据如下，请写一条sql语句，查询出每门课程都大于等于80分的学生信息

name	course	score
张三	语文	81
张三	数学	75
李四	语文	76
王五	语文	81
王五	数学	100
王五	英语	90

思路:统计每个学生的最低分,筛选最低分>=80
select name from score group by name having min(score)>=80

或者
SELECT score.* 
FROM score 
WHERE `name` 
NOT IN(
SELECT DISTINCT NAME
FROM score a
WHERE score<80)

5、有一张表student，包括字段id和name，请写一条sql语句，将表中name字段中重复的记录删除，只保留重复数据中的id最大的那一条数据。

id	name
1	张三
2	张三
3	李四
4	王五
5	王五
6	王五

要求只留下：2 张三， 3 李四， 6 王五 这三条记录

1. 查询每个学生的最大id
2. 从表中删除数据,删除条件是id不在以上值之内

delete from student1 where id not in(select max(id) from student1 group by name)
注意点:在查询结果基础上进行update操作,在mariadb10版本是支持的,在mysql5.5 mysql8都不支持

6、学生表如下

自动编号	学号	姓名	课程编号	课程名称	分数
1	2021001	张三	0001	数学	69
2	2021002	李四	0001	数学	89
3	2021001	张三	0001	数学	69

删除除了自动编号不同,其它都相同的学生冗余信息

考点:group  by后可以加多个字段
delete from student2 where id not in(select min(id) from student2 group by stu_no,name,course_id,course_name,score)

7、 有如下一张表,按照右侧的结果格式写出生成右侧结果的SQL语句

高频笔试题  
考点: case when类比与java中的if..else
case when  exp then ..
	 when  exp1  then...
	 when  exp2  then...
	 else....end

在成绩表   col:  name  course  score  显示出每个学生分数处于哪个级别(>=90 优秀  >=80 良好  >=70 中等  >=60  及格   <60 不及格  )

要求:查询每个学生的成绩,并显示级别: name  course  score  level

select name,course,score,
    (case when score>=90 then '优秀'
    when  score>=80 then '良好'
    when score>=70 then '中等'
    when score>=60 then '及格'
    ELSE '不及格' end)level
    from score 
    
case when经常和聚合函数配合使用 -- 经常考察
	要求:统计出每个学生及格和不及格的科目数
		
	select name,
	sum(case when score>=60 then 1 else 0 end)'及格科目数',
    sum(case when score<60 then 1 else 0 end)'不及格科目数' from score group by name

	
	select name,
	count(case when score>=60 then 1  end)'及格科目数',
    count(case when score<60 then 1  end)'不及格科目数' from score group by name
    
    注意点:case when 和sum()和count()使用时,用法不同(意义不同)
    	sum()配合使用,满足条件返回1,表示进行+1  else 0表示+0
    	count()配合使用,返回的值只要不是null,就进行一次计数,所以使用count()必须注意:若满足条件想进行一次计数,不满足条件不计数,应写成:
    		count(case when ....then 1 else null end)
    		或 count(case when ...then 1 end)  没有else,表示不满足条件,返回null

本题解法:
select match_date '比赛日期',
sum(case when result='胜' then 1 else 0 end)'胜',
sum(case when result='负' then 1 else 0 end)'负'
from match_record group by match_date

8、有如下3张表,完成如下需求:

(1) 用户表中添加一条数据,请写出SQL语句

insert into user values(1,'tom','男',21,'2021-12-12',1)

(2) 在用户表中把姓名为"李四"的在职用户的年龄修改为28

update user set age=28 where name='李四' and state=1

(3) 查询所有的角色名称为"技术人员",性别是女的在职人员

select u.* from role r join user_role ur join user u on r.id=ur.role_id and ur.user_id=u.id where sex='女' and state=1 and r.name='技术人员'

(4) 把角色名称为"研发人员"的用户"张三"从用户表中删除

delete from user where id in(select u.id from role r join user_role ur join user u on r.id=ur.role_id and ur.user_id=u.id where r.name='研发人员' and u.name='张三')

(5) 查询年龄在18到24岁之间的男性在职人员

select * from user where age between 18 and 24 and sex='男' and state=1

(6)查询年龄为18,19,20的男性和女性员工的人数,结果如下图所示:

年龄	男	女
18	5	3
19	4	6
20	1	0

select u.age,
(select count(*) from user u1 where u1.age=u.age and u1.sex='男')'男',
(select count(*) from user u2 where u2.age=u.age and u2.sex='女')'女'
from user u where age between 18 and 20 group by age

(7) 查询前50名姓张的男员工

(8）查询所有根角色员工,根角色就是父角色为null的角色.

 9、 以下四个表，表名称及表结构如下：

student(sno,sname,sage,ssex) 学生表
course(cno,cname,tno) 课程表
sc(sno,cno,score) 成绩表
teacher(tno,tname) 教师表

1.查询课程1的成绩比课程2的成绩高的所有学生的信息

select s.*,sc1.score'课程1',sc2.score'课程2' from sc sc1 join sc sc2 join student s on sc1.sno=sc2.sno and sc1.score>sc2.score and sc1.sno=s.sno
where sc1.cno=1 and sc2.cno=2

2.查询平均成绩大于60分的同学的学号和平均成绩

select sno,avg(score) av from sc group by sno having av>60

3.查询学过‘李四’老师所教课程的所有同学的学号，姓名

select distinct s.sno,sname from teacher t join course c join sc join student s on t.tno=c.tno and c.cno=sc.cno and sc.sno=s.sno where tname='李四'

4.查询姓“李”得老师的个数

select count(*) from teacher where tname like '李%'

5.查询每门课程的选修人数(课程名称，学生数量)--存在没有人选的课程

select cname,count(sno) from course c left join sc on c.cno=sc.cno group by c.cno

6.删除“1002”同学的“1”课程的成绩

delete from sc where sno=1002 and cno=1

7.查询选修人数最多的课程（课程id，课程名称，学生数量）--考虑有多门课程都是选修最多的情况

1. 查询选修人数对多的数目,思路统计每门课程的选修人数,对人数进行降序排列,筛选第一个条数据即为最多的选修人数
	select count(sno) cou from sc group by cno order by cou desc limit 1;
2. 查询每门课程的选修人数,筛选人数=上述人数的课程
select sc.cno,cname,count(sno) cou from course c join sc on sc.cno=c.cno group by sc.cno having cou=(select count(sno) cou from sc group by cno order by cou desc limit 1)

10、 数据库表名为guest，请简答

账号	消费	时间	金额	班次
accounts	details	date	money	class
s0001	房费	2020-01-01	280	001
s0001	酒水	2020-01-02	120	001
s0001	房费	2020-01-08	300	003
s0002	酒水	2020-01-29	50
s0003	房费	2020-01-31	180	002
s0004	房费	2020-02-01	230	001
s0005	酒水	2020-02-01	100
s0005	房费	2020-02-02	128	001

1.查询出房费都大于200的账号

思路:
1. 先筛选出所有的房费数据
2. 对以上的数据根据账号进行分组,统计每个账号房费的最低消费金额
3. 对以上的数据进行再次筛选,筛选最低房费金额>200的账号
select accounts from guest where details='房费' group by accounts having min(money)>200

2.查询出1月份每个账号酒水和房费的总金额

思路:
1. 先筛选出所有1月份的数据  
2. 对以上的数据根据账号进行分组,统计每个账号一月份中消费的总金额
select accounts,sum(money) from guest where date between '2020-01-01' and '2020-01-31' group by accounts 
或
select accounts,sum(money) from guest where month(date)=1 group by accounts

使用mysql提供的日期函数
	year(col) - 获取该列中日期的年份的值
	month(col) -- 获取该列中日期的月份的值
	day(col) -- 获取该列中日期的天数
	date(col) -- 获取日期中的年月日
	hour(col) -- 获取小时
	minute(col) -- 获取分钟
	second(col) -- 获取秒数

3.将不是房费的班次都更改为‘001’

update guest set class='001' where details!='房费'

4.查询出消费都大于100的账号

思路:选择每个账号最低消费金额大于100的
即先查询每个账号的最低消费金额,然后筛选最低消费金额大于100

select accounts from guest group by accounts having min(money)>100

11、完成以下sql查询

下面是学生成绩表(student_score)结构说明

字段名称	字段解释	字段类型	字段长度
student_id	学号	字符	8
student_name	姓名	字符	50
student_gender	性别	字符(男/女)	4
course_id	课程号	字符	5
score	分数	数值	3
ismakeup	当前考试是否为补考	字符(补考:1;非补考:0)	2

下面是课程表(course)说明

字段名称	字段解释	字段类型	字段长度	约束
course_id	课程号	字符	5	PK
course_name	课程名	字符	30	Not null
course_desc	课程介绍	字符	60

1.查找第一次考试后所有需要补考(小于60分)的学生姓名和这门课程的名称和成绩；

select student_name,course_name,score from student_score ss join course2 c on ss.course_id=c.course_id where is_makeup=0 and score<60

2.查询每个学生第一次考试后需要补考(小于60分)的课程平均分和科目数

1. 筛选第一次考试后不及格的数据
2. 针对以上数据,统计每个学生的不及格科目数以及课程平均分
select student_id,student_name,count(course_id),avg(score) from student_score where is_makeup=0 and score<60 group by student_id

3.查询所有参加了补考的学生的学生姓名，课程名称，补考成绩和非补考成绩；

思路:使用表的自连接查询,从一张成绩表中查询出参加了补考的学生信息以及补考成绩,到另一个成绩表中查询is_makeup=0的数据中,学号,课程编号均与另一张成绩表中相同的成绩数据.

select sc1.student_name,course_name,sc1.score,sc2.score 
from student_score sc1 join student_score sc2 join course2 c 
on sc1.course_id=c.course_id and sc1.student_id=sc2.student_id and sc1.course_id=sc2.course_id  
where sc1.is_makeup=1 and sc2.is_makeup=0

12、 有如下表：

emp:

empno int(员工编号) ,ename varchar(50)(员工姓名) ，job varchar(100) （工作岗位），mgr int (上级领导编号)，hiredate date（雇佣日期），sal int（薪金），comm int（佣金） deptno int (部门编号)

提示：工资=薪金+佣金

dept表：

deptno int (部门编号) ， dname 部门名称 loc 地点

1.列出在每个部门工作的员工数量，平均工资

思路:
1. 部门表和员工表进行外链接查询,部门表为主表
2. 根据部门表中的dept_no分组,统计员工数据(对empno使用count()),部门的平均工资(avg(sal+comm))

select d.deptno,dname,count(empno),avg(sal+comm) from dept d left join emp e on d.deptno=e.deptno group by d.deptno

2.列出所有员工的姓名，部门名称和工资

select ename,dname,sal+comm '工资' from emp e join dept d on e.deptno=d.deptno

3.列出所有部门的详细信息和部门人数

select d.*,count(empno) from dept d left join emp e on d.deptno=e.deptno group by d.deptno

4.列出各种工作的最低工资

select job,min(sal+comm) from emp group by job

5.列出各个部门的manager的最低薪金(若是manager,其job的值为manageer)

select e.deptno,dname,min(sal) from dept d join emp e on d.deptno=e.deptno where job='manager' group by e.deptno

6.列出受雇日期早于其直接上级的所有员工

select e.*,mgr.hiredate from emp e join emp mgr on e.mgr=mgr.empno where e.hiredate<mgr.hiredate

7.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select dname,e.* from dept d left join emp e on e.deptno=d.deptno  order by dname

8.列出所有‘clerk’（办事员）岗位的姓名以及部门名称

select ename,dname from emp e join dept d on e.deptno=d.deptno where job='clerk'

9.列出最低薪金大于6500的各种工作

select job from emp group by job having min(sal)>6500

10.列出在研发部工作的员工的姓名，假定不知道研发部的部门编号

select ename from dept d join emp e on d.deptno=e.deptno where dname='研发部'

19题：

teacher表：

 course表：

 student表：

 score表：

 

1、查询"语文"课程比"数学"课程成绩高的学生的性别及课程分数

1. 查询出语文的成绩表
	select sc.* from course c join score sc on c.c_id=sc.c_id where c_name='语文'
2. 查询出数学的成绩表
	select sc.* from course c join score sc on c.c_id=sc.c_id where c_name='数学'
3. 以上两表和学生表进行连接查询,查询对应的学生数据(3表联查)
select s.s_id,s_name,s_sex,chinese.s_score'语文分',math.s_score'数学分' from student s 
join  (select sc.* from course c join score sc on c.c_id=sc.c_id where c_name='语文')chinese 
join  (select sc.* from course c join score sc on c.c_id=sc.c_id where c_name='数学') math
on chinese.s_id=math.s_id and chinese.s_score>math.s_score and s.s_id=math.s_id

2、查询"语文"课程比"数学"课程成绩低的学生的性别及课程分数 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select s.s_id,s_name,avg(s_score) av from score sc join student s on sc.s_id=s.s_id group by sc.s_id having av>=60

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

思路:求出每个学生的总分/课程总数 = 平均分,然后筛选平均分<60的学生
select s.s_id,s_name,sum(s_score)/(select count(*) from course) av from student s join score sc on s.s_id=sc.s_id group by sc.s_id having av<60

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select s.s_id,s_name,count(c_id),sum(s_score) from student s left join score sc on s.s_id=sc.s_id group by  s.s_id

6、查询"王"姓老师的数量

select count(*) from teacher where t_name like '王%'

7、查询学过"张三"老师授课的同学的信息

select distinct s.* from teacher t join course c join score sc join student s on t.t_id=c.t_id and c.c_id=sc.c_id and sc.s_id=s.s_id where t_name='张三'

8、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select s.* from score sc1 join score sc2 join student s on sc1.s_id=sc2.s_id and sc1.s_id=s.s_id where sc1.c_id='01' and sc2.c_id='02'

9、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

1. 先求出每个学生的平均分(总分/课程总数) - 要求所有学生均要查,使用外链接查询
 select s.s_id,s_name,sum(s_score)/(select count(*) from course) av,
 (select sc1.s_score from score sc1 where sc1.c_id='01' and sc1.s_id=s.s_id)'语文',
 (select sc2.s_score from score sc2 where sc2.c_id='02' and sc2.s_id=s.s_id)'数学',
 (select sc3.s_score from score sc3 where sc3.c_id='03' and sc3.s_id=s.s_id)'英语'
 from student s left join score sc on s.s_id=sc.s_id group by s.s_id order by av desc

10、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 (及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90)

sum(case when score>=60  then 1 else 0 end)/count(s_id)

select c.c_id,c_name,max(s_score)'最高分',min(s_score)'最低分',avg(s_score)'平均分',
sum(case when s_score>=60 then 1 else 0 end)/count(s_id)'及格率',
sum(case when s_score>=70 and s_score<80 then 1 else 0 end)/count(s_id)'中等率',
sum(case when s_score>=80 and s_score<90 then 1 else 0 end)/count(s_id)'优良率',
sum(case when s_score>=90 then 1 else 0 end)/count(s_id)'优秀率'
from course c left join  score sc on c.c_id=sc.c_id group by c.c_id

11、查询每门科目成绩都在60分及以上的学生id，姓名，成绩

select s.s_id,s_name,sc1.s_score'语文',sc2.s_score'数学',sc3.s_score'英语' from score sc1 join score sc2 join score sc3 join student s on sc1.s_id=sc2.s_id and sc2.s_id=sc3.s_id and sc3.s_id=s.s_id where sc1.c_id='01' and sc1.s_score>=60 and sc2.c_id='02' and sc2.s_score>=60 and sc3.c_id='03' and sc3.s_score>=60

12、查询至少有一门科目成绩在90分及以上的学生id，姓名，成绩

select s.s_id,s_name,
(select s_score from score sc1 where sc1.c_id='01' and sc1.s_id=s.s_id)'语文',
(select s_score from score sc2 where sc2.c_id='02' and sc2.s_id=s.s_id)'数学',
(select s_score from score sc3 where sc3.c_id='03' and sc3.s_id=s.s_id)'英语'
from score sc join student s on sc.s_id=s.s_id group by sc.s_id having max(s_score)>=90

13、 查询所有科目成绩总和在240分及以上的学生id，姓名，和成绩总和，降序排列

select s.s_id,s_name,sum(s_score) sum from score sc join student s on sc.s_id=s.s_id group by sc.s_id having sum>=240 order by sum desc

14、查询每个科目成绩最高的学生的学生id，姓名，写在一个表里

将查询结果存入新表语法：
create table tname as select.....
创建视图语法：
create view v_name as select....
create view v_name(col,col,col) as select....
答案：
CREATE TABLE t4 AS 
SELECT s.s_id,s_name,tmp.c_id,max FROM score sc JOIN student s JOIN 
(SELECT c_id,MAX(s_score) max FROM score GROUP BY c_id)tmp
ON sc.c_id=tmp.c_id AND sc.s_score=tmp.max AND s.s_id=sc.s_id

15、查询每个科目成绩最低的学生的学生id，姓名

select s.s_id,s_name,tmp.c_id,min from score sc join student s join 
(select c_id,min(s_score) min from score group by c_id)tmp
on sc.c_id=tmp.c_id and sc.s_score=tmp.min and sc.s_id=s.s_id

16、查询目前没有安排课程的老师姓名

思路：查询每个老师的课程数，筛选课程数为0的
方式1：查询教课数为0的老师
select t.t_id,t_name from teacher t left join course c on t.t_id=c.t_id group by t.t_id having count(c_id)=0

方式2： 两表外联，查询c_id为null的数据
select t_name from teacher t left join course c on t.t_id=c.t_id where c_id is null

17、将之前老师表的英文名字转化为大写

lower(col):转换为小写
upper(col):转换为大写
update teacher set t_name=upper(t_name)

18、查询每门课程成绩最好的前两名

思路：统计每个学生每门课程比其分数高的人数，筛选比其分数高的人数<2的数据 ，即当前这个学生是这门课程中的前2名

select sc1.s_id,s_name,sc1.c_id,sc1.s_score from score sc1 join student s on sc1.s_id=s.s_id left join score sc2 on sc1.c_id=sc2.c_id and sc2.s_score>sc1.s_score group by sc1.s_id,sc1.c_id having count(sc2.s_id)<2 order by sc1.c_id

19、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select c.c_id,c_name,
sum(case when s_score<60 then 1 else 0 end)'[0-60]',
sum(case when s_score>=60 and s_score<70 then 1 else 0 end)'[60-70]',
sum(case when s_score>=70 and s_score<85 then 1 else 0 end)'[70-85]',
sum(case when s_score>=85  then 1 else 0 end)'[85-100]',
sum(case when s_score<60 then 1 else 0 end)/count(s_id)'不及格率',
sum(case when s_score>=60 and s_score<70 then 1 else 0 end)/count(s_id)'中等率',
sum(case when s_score>=70 and s_score<85 then 1 else 0 end)/count(s_id)'良好率',
sum(case when s_score>=85  then 1 else 0 end)/count(s_id)'优秀率'
from course c left join score sc on c.c_id=sc.c_id group by c.c_id

20、 查询学生表中姓名重复的姓名

select s_name from student group by s_name having count(*)>1

21、查询学生表中姓名重复的所有学生信息 

select * from student where s_name in(select s_name from student group by s_name having count(*)>1)