sql面试题50题（mysql版）

-- 查询所有同学的学号、姓名、选课数、总成绩；

select SSid,Sname,选课数=COUNT(Cid),总成绩=SUM(Score)

   from Student S join Sc on SSid=scSid

  group by SSid,Sname

-- 查询学过“叶萍”老师所教的所有课程的同学学号、姓名；

select SSid,Sname

from Student S join Sc on SSid=ScSid

    join Course C on CCid=ScCid

join teacher T on TTid=CTid

where Tname='叶萍'

  group by SSid,Sname

      having COUNT(distinct CCid)=(select COUNT(Cid) from Course join teacher on CourseTid=teacherTid where Tname='叶萍')

-- 查询课程编号"002"的成绩比课程编号“001”课程低的所有同学的学号、姓名

select Sid,Sname

from Student

where Sid in(select ASid

from Sc A join Sc B on ASid=BSid

where ACid=1 AND BCid=2 AND AScore>BScore)

-- 按平均成绩高到低显示所有学生的 "数学"、"物理"两门课程的成绩，以下形式显示：学生ID，数学，物理，平均分

select Sid,数学=max(case when Cname='数学' then Score end),物理=max(case when Cname='物理' then Score end),

平均分=(max(case when Cname='数学' then Score end)+max(case when Cname='物理' then Score end))/2

from Sc join Course C on ScCid=CCid

  where Cname in('数学','物理')

group by Sid

select d科目代码 from

(select c科目代码,sum(cflag) as num from

(select b科目代码，if(b发生额>a发生额 1,0) as flag from

(select 科目代码，发生额 from voucher where 科目代码='101')a --12条记录(1)

left join

(select 科目代码，发生额 from voucher where 科目代码<>'101')b --非101科目条数12条记录(2)

on a发生月份=b发生月份)c --设个标志，如果发生额>101的同月发生额，为1，否自为0(3)

group by c科目代码)d --对标志值分组累加(4)

where dnum=12 --如果累加值为12，说明该代码12个月的发生额都大于101同月发生额。(5)

记事本手写未经测试，有问题自己修改吧。

--插入学生表测试数据

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

insert into Student values('02' , '钱电' , '1990-12-21' , '男');

insert into Student values('03' , '孙风' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

insert into Student values('08' , '王菊' , '1990-01-20' , '女');

--课程表测试数据

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

--教师表测试数据

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

--成绩表测试数据

insert into Score values('01' , '01' , 80);

insert into Score values('01' , '02' , 90);

insert into Score values('01' , '03' , 99);

insert into Score values('02' , '01' , 70);

insert into Score values('02' , '02' , 60);

insert into Score values('02' , '03' , 80);

insert into Score values('03' , '01' , 80);

insert into Score values('03' , '02' , 80);

insert into Score values('03' , '03' , 80);

insert into Score values('04' , '01' , 50);

insert into Score values('04' , '02' , 30);

insert into Score values('04' , '03' , 20);

insert into Score values('05' , '01' , 76);

insert into Score values('05' , '02' , 87);

insert into Score values('06' , '01' , 31);

insert into Score values('06' , '03' , 34);

insert into Score values('07' , '02' , 89);

insert into Score values('07' , '03' , 98);

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select c,as_score as 01课程score,bs_score as 02课程score from

score a,score b

left join student c

on bs_id = cs_id

where as_id = bs_id and ac_id = '01' and bc_id = '02' and as_score > bs_score;

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

select a ,bs_score as 01课程,cs_score as 02课程 from student a

join score b

on as_id=bs_id and bc_id = '01'

left join score c

on bs_id = cs_id and cc_id = '02'

where bs_score < cs_score ;

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select as_id,as_name,round(avg(bs_score),2) as 平均成绩 from student a

join score b

on as_id = bs_id

group by bs_id having 平均成绩 >= 60;

备注：round[avg(成绩),1]里,round是四舍五入函数，1代表保留1位小数

-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

-- (包括有成绩的和无成绩的)

select b ,round(avg(as_score),2) as 平均成绩 from

student b

left join score a on bs_id = as_id group by as_id having 平均成绩 < 60

union

select b ,0 as 平衡成绩 from student b where bs_id not in (select s_id from score);

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

select as_id,as_name,count(bc_id) as 选课总数 ,sum(bs_score) as 总分 from student a

left join score b

on as_id = bs_id group by s_id ;

-- 6、查询"李"姓老师的数量

select count() as 李姓老师数量 from teacher where t_name like '李%';

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

select a from student a join score b

on as_id = bs_id

where bc_id in (select cc_id from course c

join teacher d on ct_id = dt_id where dt_name = '张三');

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

select a from student a left join score b on as_id = bs_id where as_id not in

(select s_id from score where c_id =

(select c_id from course where t_id =

(select t_id from teacher where t_name = '张

三'))) group by as_id;

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

select from student where s_id in

(select as_id from score a join score b on as_id = bs_id

where ac_id = '01' and bc_id = '02');

-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select from student where s_id in

(select s_id from score where c_id = '01' )

and s_id not in (select s_id from score where c_id = '02' );

-- 11、查询没有学全所有课程的同学的信息

select from student where s_id not in

(select s_id from score group by s_id having count(c_id) = 3);

-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select distinct a from student a left join score b

on as_id = bs_id where bc_id in

(select c_id from score where s_id = '01') and as_id != '01' ;

注意：distinct是去重的

-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

select from student where s_id in

(select s_id from score group by s_id having count(c_id) =

(select count(c_id) from score where s_id = '01') and s_id not in

(select s_id from score where c_id not in

(select c_id from score where s_id = '01')) and s_id != '01');

-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名

select s_name from student where s_id not in

(select s_id from score where c_id in

(select c_id from course where t_id in

(select t_id from teacher where t_name ='张三')));

-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select as_id ,bs_name,round(avg(as_score),2) as 平均成绩 from score a

left join student b on as_id = bs_id

where s_score < 60 group by s_id having count(1) >=2;

或者试试

select as_id ,bs_name,round(avg(as_score),2) as 平均成绩 from score a

left join student b on as_id = bs_id

where as_score < 60 group by as_id having count() >=2;

-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息

select a ,bc_id ,bs_score from student a

left join score b on as_id = bs_id

where bc_id = '01' and bs_score < 60

order by bs_score desc;

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

select as_name ,

sum(case when bc_id = '01' then s_score else null end ) as 语文,

sum(case when bc_id = '02' then s_score else null end ) as 数学,

sum(case when bc_id = '03' then s_score else null end ) as 英语,

round(avg(s_score),2) as 平均成绩

from student a left join score b on as_id = bs_id group by as_name

order by 平均成绩 desc;

-- 18查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select bc_id,bc_name,

max(as_score) as 最高分,

min(as_score) as 最低分,

round(avg(as_score),2) as 平均分,

round(sum(case when as_score>= 60 then 1 else 0 end)/count(s_id),2) as 及格率 ,

round(sum(case when as_score>= 70 and as_score <80 then 1 else 0 end)/count(s_id),2) as 中等率,

round(sum(case when as_score>= 80 and as_score <90 then 1 else 0 end)/count(s_id),2) as 优良率,

round(sum(case when as_score>= 90 then 1 else 0 end)/count(s_id),2) as 优秀率

from score a

left join course b

on ac_id = bc_id group by bc_id;

-- 19、按各科成绩进行排序，并显示排名

第一种：

set @pre_c_id:= '01';

set @rank:=0;

select tb2s_id ,tb2c_id,tb2s_score,tb2排名 from

(select ,(case when tb1c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,

(case when @pre_c_id = tb1c_id then @pre_c_id else @pre_c_id:=tb1c_id end ) as pre_c_id

from

(select from score order by c_id,s_score desc) tb1 )tb2;

如果看不懂用第二种方法：

SELECT ac_id,as_id,as_score,COUNT(bs_score)+1 AS 排名

FROM score a LEFT JOIN score b ON as_score<bs_score AND ac_id = bc_id

GROUP BY ac_id,as_id,as_score ORDER BY ac_id,排名,as_id ASC

-- 20、查询学生的总成绩并进行排名

set @rank:=0;

select ,(@rank:=@rank+1) as rank from

(select s_id ,sum(s_score) as 总成绩 from score

group by s_id order by 总成绩 desc) tb1;

-- 21、查询不同老师所教不同课程平均分从高到低显示

select ac_id, dt_name,round(avg(as_score)) as 平均分 from score a

left join student b on as_id = bs_id

left join course c on ac_id = cc_id

left join teacher d on ct_id = dt_id group by ac_id

order by 平均分 desc;

-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

set @pre_c_id:= '01';

set @rank:=0;

select bs_name,tb2s_id ,tb2c_id,tb2s_score,tb2排名 from

(select ,(case when tb1c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,

(case when @pre_c_id = tb1c_id then @pre_c_id else @pre_c_id:=tb1c_id end ) as pre_c_id

from

(select from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2s_id = bs_id where 排名 = 2 or 排名 =3;

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

select bc_id,bc_name ,

sum(case when as_score >=85 then 1 else 0 end) as 100-85 ,

concat(round(100 sum(case when as_score >=85 then 1 else 0 end)/count( ),2), '%') as 百分比,

sum(case when as_score <85 and as_score >=70 then 1 else 0 end) as 85-70 ,

concat(round(100 sum(case when as_score <85 and as_score >=70 then 1 else 0 end)/count( ),2),'%') as 百分比,

sum(case when as_score <70 and as_score >=60 then 1 else 0 end) as 70-60 ,

concat(round(100 sum(case when as_score <70 and as_score >=60 then 1 else 0 end)/count( ),2) ,'%')as 百分比,

sum(case when as_score <60 and as_score >=0 then 1 else 0 end) as 60-0 ,

concat(round(100 sum(case when as_score <60 and as_score >=0 then 1

else 0 end)/count( ),2),'%') as 百分比

from score a left join course b on ac_id = bc_id group by bc_id;

-- 24、查询学生平均成绩及其名次

select tb1,(@rank:=@rank +1 ) as rank from

(select s_id ,round(avg(s_score),2) as 平均成绩 from score

group by s_id order by 平均成绩 desc) tb1,(select @rank:=0) b;

-- 25、查询各科成绩前三名的记录

set @pre_c_id:= '01';

set @rank:=0;

select bs_name,tb2s_id ,tb2c_id,tb2s_score,tb2排名 from

(select ,(case when tb1c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,

(case when @pre_c_id = tb1c_id then @pre_c_id else @pre_c_id:=tb1c_id end ) as pre_c_id

from

(select from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2s_id = bs_id where 排名 <4;

-- 26、查询每门课程被选修的学生数

select c_id ,count(s_id) as 选修人数 from score group by c_id;

-- 27、查询出只有两门课程的全部学生的学号和姓名

select as_id ,bs_name from score a left join student b on as_id = bs_id group by s_id having count() = 2;

-- 28、查询男生、女生人数

select sum(case s_sex when '男' then 1 else 0 end) as 男生人数,

sum(case s_sex when '女' then 1 else 0 end) as 女生人数 from student;

-- 29、查询名字中含有"风"字的学生信息

select from student where s_name like '%风%';

-- 30、查询同名同性学生名单，并统计同名人数

--略，不想写

-- 31、查询1990年出生的学生名单

select from student where s_birth like '1990%';

-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select c_id ,round(avg(s_score),2) as 平均成绩 from score group by c_id order by 平均成绩 desc, c_id asc;

-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select as_id,bs_name ,round(avg(s_score),2) as 平均成绩 from score a

left join student b on as_id = bs_id group by as_id having 平均成绩>=85;

-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

select bs_name ,as_score from score a

left join student b on as_id = bs_id

where ac_id=(select c_id from course where c_name = '数学')and as_score < 60;

-- 35、查询所有学生的课程及分数情况；

select bs_name,

sum(case when ac_id = '01' then as_score else null end) as 语文,

sum(case when ac_id = '02' then as_score else null end) as 数学,

sum(case when ac_id = '03' then as_score else null end) as 英语

from score a right join student b on as_id = bs_id group by bs_name

-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

select bs_name,

sum(case when ac_id = '01' then as_score else null end) as 语文,

sum(case when ac_id = '02' then as_score else null end) as 数学,

sum(case when ac_id = '03' then as_score else null end) as 英语

from score a right join student b on as_id = bs_id group by bs_name having 语文>= 70 or 数学>= 70 or 英语>= 70 ;

-- 37、查询不及格的课程

select as_id,ac_id,bc_name,as_score from score a

left join course b on ac_id = bc_id where as_score<60;

--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

select as_id,bs_name from score a left join student b on as_id = bs_id where ac_id = '01' and as_score>=80;

-- 39、求每门课程的学生人数

select c_id,count() as 学生人数 from score group by c_id ;

-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

select a,bc_id,max(bs_score) as 最高成绩 from student a

right join score b on as_id = bs_id

group by bc_id

having bc_id = (select c_id from course

where t_id = (select t_id from teacher where t_name = '张三'));

-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

--（这题我搞不清题目是什么意思,是指查找学生个体参加了的所有课程的成绩各不相同的那个学生信息呢？还是所有课程之间做对比呢，我更倾向于理解为前者）

--理解为前者的写法

select from

(select from score group by s_id,s_score) tb1

group by s_id having count() = 1;

--理解为后者的写法

select distinct as_id,ac_id,bs_score from score a,score b where ac_id != bc_id and as_score = bs_score;

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

set @pre_c_id:= '01';

set @rank:=0;

select tb2s_id ,tb2c_id,tb2s_score from

(select ,(case when tb1c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,

(case when @pre_c_id = tb1c_id then @pre_c_id else @pre_c_id:=tb1c_id end ) as pre_c_id

from

(select from score order by c_id,s_score desc) tb1 )tb2

join student b on tb2s_id = bs_id where 排名 <3;

-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人相同，按课程号升序排列

select c_id ,count() as 选修人数 from score group by c_id having 选修人数>5 order by 选修人数 desc , c_id asc;

-- 44、检索至少选修两门课程的学生学号

select s_id from score group by s_id having count() >= 2;

-- 45、查询选修了全部课程的学生信息

select from student where s_id in

(select s_id from score group by s_id having count() = 3)

--46、查询各学生的年龄

select s_name ,(date_format(now(),'%Y')-date_format(s_birth,'%Y') + (CASE when date_format(now(),'%m%d')>=date_format(s_birth,'%m%d') then 0 else 1 end)) as age

from student

-- 47、查询本周过生日的学生

---(实现得并不完全，因为例如出生月日为‘01-01’在每一年可能会输入不同周）

select from student where week(date_format(s_birth,'%m%d'))=week(date_format(now(),'%m%d')) ;

-- 48、查询下周过生日的学生

select from student

where week(date_format(s_birth,'%m%d'))=week(date_format(date_add(now(),interval 7-dayofweek(now())+1 day),'%m%d'));

-- 49、查询本月过生日的学生

select from student where date_format(s_birth,'%m') = date_format(now(),'%m')

-- 50、查询下月过生日的学生

select from student where date_format(s_birth,'%m') = date_format(date_add(now(),interval 1 month),'%m')

SELECT TOP 10 uname,uage,allin =isnull((SELECT sum(tnumberpbuyprice)  FROM TEST_PRODECT p,TEST_TREED t

WHERE ptype = tptype AND tuid=uuserid AND tBUY_OR_SALE ='BUY'),0),

allout=isnull((SELECT sum(tnumberpsaleprice)  FROM TEST_PRODECT p,TEST_TREED t

WHERE ptype = tptype AND tuid=uuserid AND tBUY_OR_SALE ='SALE'),0)

,

realin = (isnull((SELECT sum(tnumberpbuyprice)  FROM TEST_PRODECT p,TEST_TREED t

WHERE ptype = tptype AND tuid=uuserid AND tBUY_OR_SALE ='BUY'),0) - isnull((SELECT sum(tnumberpsaleprice)  FROM TEST_PRODECT p,TEST_TREED t

WHERE ptype = tptype AND tuid=uuserid AND tBUY_OR_SALE ='SALE'),0))

FROM TEST_USER u

ORDER BY (isnull((SELECT sum(tnumberpbuyprice)  FROM TEST_PRODECT p,TEST_TREED t

WHERE ptype = tptype AND tuid=uuserid AND tBUY_OR_SALE ='BUY'),0) - isnull((SELECT sum(tnumberpsaleprice)  FROM TEST_PRODECT p,TEST_TREED t

WHERE ptype = tptype AND tuid=uuserid AND tBUY_OR_SALE ='SALE'),0)) DESC

第2题答案

经过测试无误

以上就是关于SQL语句面试题全部的内容，包括:SQL语句面试题、SQL语句问题 面试题、sql面试题50题（mysql版）等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！