sql练习

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT a.*,t.01score,t.02score from student a
JOIN (
SELECT ss.s_id,ss.s_score as 01score , s.s_score as 02score from score ss
join score s on s.s_id = ss.s_id and ss.c_id = '01' and s.c_id='02' and ss.s_score > s.s_score)t on a.s_id =t.s_id;
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT a.*,t.01score,t.02score from student a
JOIN (
SELECT ss.s_id,ss.s_score as 01score , s.s_score as 02score from score ss
join score s on s.s_id = ss.s_id and ss.c_id = '01' and s.c_id='02' and ss.s_score < s.s_score)t on a.s_id =t.s_id;
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT a.s_id,a.s_name,s.avgscore from student a
JOIN(
SELECT s_id,ROUND(AVG(s_score),2) as avgscore from score GROUP BY s_id )s on a.s_id=s.s_id and avgscore>=60
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
SELECT a.s_id,a.s_name,s.avgscore from student a
JOIN(
SELECT s_id,ROUND(AVG(s_score),2) as avgscore from score GROUP BY s_id )s on a.s_id=s.s_id and avgscore<60
UNION
SELECT
a.s_id,a.s_name,0 as avgscore from student a WHERE a.s_id not in(SELECT s_id from score);
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT a.s_id as '学生编号',a.s_name as '学生姓名', IF(s.num is null,0,s.num) as '课程总数' ,IF(s.sumscore is null,0,s.sumscore) as '所有课程总成绩' from student a
LEFT JOIN (SELECT s_id, COUNT(1) as num,sum(s_score) as sumscore from score GROUP BY s_id)s on s.s_id = a.s_id;
-- 6、查询"李"姓老师的数量
SELECT COUNT(1) as "\"李\"姓老师的数量 " from teacher where t_name like'李%'
-- 7、查询学过"张三"老师授课的同学的信息
-- 1.先把张三教的课程找出来
SELECT * from course where t_id =(SELECT t_id from teacher where t_name='张三');
-- 2.
SELECT a.* from student a
JOIN score s on s.s_id = a.s_id and s.c_id =(SELECT c_id from course where t_id =(SELECT t_id from teacher where t_name='张三'));
-- 8、查询没学过"张三"老师授课的同学的信息
SELECT * from student where s_id not in(
SELECT a.s_id from student a
JOIN score s on s.s_id = a.s_id and s.c_id =(SELECT c_id from course where t_id =(SELECT t_id from teacher where t_name='张三')));
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT * from student where s_id in(
SELECT s.s_id from score s
JOIN score ss on s.s_id =ss.s_id and s.c_id ='01' and ss.c_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 in(
SELECT t.s_id from(SELECT s_id,COUNT(1) as num from score GROUP BY s_id HAVING num !=3)t
);
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT * from student where s_id in(SELECT DISTINCT s_id from score where c_id not in(SELECT c_id from score where s_id = '01'));
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT * from score where s_id != '01' and c_id in(SELECT c_id from score where s_id = '01');

select a.* from student a where a.s_id in(
select distinct s_id from score where s_id!='01' and c_id in(select c_id from score where s_id='01')
group by s_id
having count(1)=(select count(1) from score where s_id='01'));
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT * from student where s_id not in(
SELECT s_id from score where
c_id in (SELECT c_id from course where t_id =(SELECT t_id from teacher where t_name='张三'))
);
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT a.s_id,a.s_name,t.avgscore from student a
join(
SELECT s_id, AVG(s_score) as avgscore from score where s_id in(
SELECT s_id from score where s_score<60 GROUP BY s_id HAVING count(1)>=2) GROUP BY s_id)t on t.s_id =a.s_id;
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT a.* from student a
LEFT JOIN score s on a.s_id = s.s_id and s.c_id='01'
ORDER BY s.s_score DESC
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT s_id,AVG(s_score) avgscore from score GROUP BY s_id ORDER BY avgscore desc;

select a.s_id,(select s_score from score where s_id=a.s_id and c_id='01') as 语文,
(select s_score from score where s_id=a.s_id and c_id='02') as 数学,
(select s_score from score where s_id=a.s_id and c_id='03') as 英语,
round(avg(s_score),2) as 平均分 from score a GROUP BY a.s_id ORDER BY 平均分 DESC;

SELECT t.*,d.avgscore from
(
SELECT a.s_id,
max(case a.c_id
when '01' THEN a.s_score END)
AS '数学'
,
max(case a.c_id
when '02' THEN a.s_score END)
as '语文'
,
max(case a.c_id
when '03' THEN a.s_score END)
as '英语'
FROM score a
GROUP BY a.s_id )t
LEFT JOIN (SELECT s_id,AVG(s_score) avgscore from score GROUP BY s_id)d on t.s_id = d.s_id
ORDER BY d.avgscore desc
;
-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
SELECT
s.c_id as 课程ID,
c.c_name as 课程name,
MAX(s.s_score) as 最高分,
min(s.s_score) as 最低分,
round(AVG(s.s_score),2) as 平均分,
CONCAT (ROUND((SELECT count(1) from score a where a.c_id=s.c_id AND a.s_score >=60)/count(1)*100,2),"%")as 及格率,
CONCAT (ROUND((SELECT count(1) from score a where a.c_id=s.c_id AND a.s_score >=70 and a.s_score <80)/count(1)*100,2),"%")as 中等率,
CONCAT (ROUND((SELECT count(1) from score a where a.c_id=s.c_id AND a.s_score >=80 and a.s_score <90)/count(1)*100,2),"%")as 优良率,
CONCAT (ROUND((SELECT count(1) from score a where a.c_id=s.c_id AND a.s_score >=90)/count(1)*100,2),"%")as 优秀率
FROM score s
LEFT JOIN course c on s.c_id =c.c_id
GROUP BY s.c_id;

select a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2),
ROUND(100*(SUM(case when a.s_score>=60 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 及格率,
ROUND(100*(SUM(case when a.s_score>=70 and a.s_score<=80 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 中等率,
ROUND(100*(SUM(case when a.s_score>=80 and a.s_score<=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优良率,
ROUND(100*(SUM(case when a.s_score>=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优秀率
from score a left join course b on a.c_id = b.c_id GROUP BY a.c_id,b.c_name;
-- 19、按各科成绩进行排序，并显示排名(实现不完全)
select @i:=0;

SELECT
*,
(@i:=@i+1)pm
from score ,(select @i:=0)t ORDER BY c_id ASC,s_score DESC;

select a.s_id,a.c_id,
@i:=@i +1 as i保留排名,
@k:=(case when @score=a.s_score then @k else @i end) as rank不保留排名,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id='01' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
union
select a.s_id,a.c_id,
@i:=@i +1 as i,
@k:=(case when @score=a.s_score then @k else @i end) as rank,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id='02' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s
union
select a.s_id,a.c_id,
@i:=@i +1 as i,
@k:=(case when @score=a.s_score then @k else @i end) as rank,
@score:=a.s_score as score
from (
select s_id,c_id,s_score from score WHERE c_id='03' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC
)a,(select @k:=0,@i:=0,@score:=0)s;
-- 20、查询学生的总成绩并进行排名
SELECT
s_id,
SUM(s_score) sumscore
from score
GROUP by s_id
ORDER BY sumscore desc;
-- 21、查询不同老师所教不同课程平均分从高到低显示
SELECT
t.t_name,
s.c_id,
avg(s.s_score) as avgscore
from score s
LEFT JOIN course c on c.c_id = s.c_id
LEFT JOIN teacher t on t.t_id = c.t_id
GROUP BY s.c_id
ORDER BY avgscore DESC;
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SELECT * from
student a
JOIN
((SELECT s_id,c_id,s_score
from score
where c_id ='01'
ORDER BY s_score DESC
LIMIT 1,2)
UNION
(SELECT s_id,c_id,s_score
from score
where c_id ='02'
ORDER BY s_score DESC
LIMIT 1,2)
UNION
(SELECT s_id,c_id,s_score
from score
where c_id ='03'
ORDER BY s_score DESC
LIMIT 1,2))t on t.s_id =a.s_id;

-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT
s.c_id,
c.c_name,
ROUND((SELECT count(1) from score a where a.c_id =s.c_id and a.s_score>=85 AND a.s_score<=100)/(SELECT count(1) from score a where a.c_id =s.c_id)*100,2),
ROUND((SELECT count(1) from score a where a.c_id =s.c_id and a.s_score>=70 AND a.s_score<85)/(SELECT count(1) from score a where a.c_id =s.c_id)*100,2),
ROUND((SELECT count(1) from score a where a.c_id =s.c_id and a.s_score>=60 AND a.s_score<70)/(SELECT count(1) from score a where a.c_id =s.c_id)*100,2),
ROUND((SELECT count(1) from score a where a.c_id =s.c_id and a.s_score<60)/(SELECT count(1) from score a where a.c_id =s.c_id)*100,2)
from score s
LEFT JOIN course c on c.c_id = s.c_id
GROUP BY s.c_id;
-- 24、查询学生平均成绩及其名次

SELECT
*,
(@i:=@i+1)pm
from score ,(select @i:=0)t ORDER BY c_id ASC,s_score DESC;

SELECT
s.*,
(@i:=@i+1)pm
from
(
SELECT
s_id,
avg(s_score)
from score
GROUP BY s_id
ORDER BY s_score DESC
)s ,(select @i:=0)t;
-- 25、查询各科成绩前三名的记录
-- 1.选出b表比a表成绩大的所有组
-- 2.选出比当前id成绩大的 小于三个的
SELECT
s.*
FROM score s
LEFT JOIN score ss on s.c_id=ss.c_id and s.s_score < ss.s_score
GROUP BY s.c_id,s.s_id,s.s_score HAVING COUNT(s.c_id)<3
ORDER BY s.c_id ASC,s.s_score DESC;

select a.s_id,a.c_id,a.s_score from score a
left join score b on a.c_id = b.c_id and a.s_score<b.s_score
group by a.s_id,a.c_id,a.s_score HAVING COUNT(b.s_id)<3
ORDER BY a.c_id,a.s_score DESC ;

-- 26、查询每门课程被选修的学生数
SELECT c_id,count(1) stuNum from score
GROUP BY c_id;
-- 27、查询出只有两门课程的全部学生的学号和姓名
SELECT* from student where s_id in(SELECT s_id from score group by s_id HAVING count(1) =2 )
-- 28、查询男生、女生人数
SELECT
s_sex,
COUNT(s_sex)
from student GROUP BY s_sex

-- 29、查询名字中含有"风"字的学生信息
SELECT * from student where s_name like'%风%';
-- 30、查询同名同性学生名单，并统计同名人数
SELECT
a.s_name,
count(1)
from student a
JOIN student b ON a.s_name=b.s_name and a.s_id!=b.s_id
group by a.s_name;

select a.s_name,a.s_sex,count(*) from student a JOIN
student b on a.s_id !=b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex
GROUP BY a.s_name,a.s_sex;

-- 31、查询1990年出生的学生名单
SELECT s_name from student where DATE_FORMAT(s_birth,'%Y') =1990;
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT
c_id,
avg(s_score) asvscore
from score
GROUP BY c_id
ORDER by asvscore DESC , c_id asc;
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT
a.s_id,
b.s_name,
avg(a.s_score) avgscore
from score a
LEFT JOIN student b on a.s_id = b.s_id
GROUP BY s_id
HAVING avgscore>=85;
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT
a.s_name,
b.s_score
from student a
JOIN score b on a.s_id = b.s_id and b.s_score<60
JOIN course c on b.c_id = c.c_id and c.c_name='数学';

SELECT
a.s_name,
b.s_score
FROM
score b
LEFT JOIN student a ON a.s_id = b.s_id
WHERE
b.c_id = (
SELECT
c_id
FROM
course
WHERE
c_name = '数学'
)
AND b.s_score < 60;
-- 35、查询所有学生的课程及分数情况；
SELECT
s_id,
max(CASE when c_id =02 THEN s_score END) 语文,
max(CASE when c_id =01 THEN s_score END) 数学,
max(CASE when c_id =03 THEN s_score END) 英语
from score
GROUP BY s_id;

select a.s_id,a.s_name,
SUM(case c.c_name when '语文' then b.s_score else 0 end) as '语文',
SUM(case c.c_name when '数学' then b.s_score else 0 end) as '数学',
SUM(case c.c_name when '英语' then b.s_score else 0 end) as '英语',
SUM(b.s_score) as '总分'
from student a left join score b on a.s_id = b.s_id
left join course c on b.c_id = c.c_id
GROUP BY a.s_id,a.s_name ;

-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
SELECT
a.s_name,
c.c_name,
b.s_score
from student a
Left JOIN score b on a.s_id = b.s_id
Left JOIN course c on c.c_id = b.c_id
WHERE b.s_score >=70;

select a.s_name,b.c_name,c.s_score from course b left join score c on b.c_id = c.c_id
left join student a on a.s_id=c.s_id where c.s_score>=70 ;
-- 37、查询不及格的课程
SELECT
a.s_name,
b.s_score,
c.c_name
FROM student a
LEFT JOIN score b on a.s_id = b.s_id
LEFT JOIN course c on b.c_id =c.c_id
where b.s_score<60;

SELECT
a.s_id,
a.c_id,
b.c_name,
a.s_score
FROM
score a
LEFT JOIN course b ON a.c_id = b.c_id
WHERE
a.s_score < 60;
-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
SELECT
a.s_id,
a.s_name
from student a
LEFT JOIN score b on a.s_id=b.s_id
where
b.c_id =01 and b.s_score>80;
-- 39、求每门课程的学生人数
SELECT
c.c_name,
count(1) num
from score b
LEFT JOIN course c on b.c_id =c.c_id
GROUP BY c.c_id;
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select a.*,b.s_score,b.c_id,c.c_name from student a
LEFT JOIN score b on a.s_id = b.s_id
LEFT JOIN course c on b.c_id=c.c_id
where b.c_id in(SELECT
c_id
from course
where t_id
=(SELECT t_id from teacher where t_name ='张三'))

and b.s_score =(
SELECT
max(s_score)
from score
where c_id in(
SELECT
c_id
from course
where t_id
=(SELECT t_id from teacher where t_name ='张三')))
;

 

select a.*,b.s_score,b.c_id,c.c_name from student a
LEFT JOIN score b on a.s_id = b.s_id
LEFT JOIN course c on b.c_id=c.c_id
where b.c_id =(select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name='张三')
and b.s_score in (select MAX(s_score) from score where c_id='02');
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT
a.s_id,a.c_id,
a.s_score
from score a
JOIN score b on a.c_id != b.c_id and a.s_score = b.s_score;

 

SELECT DISTINCT
b.s_id,
b.c_id,
b.s_score
FROM
score a,
score b
WHERE
a.c_id != b.c_id
AND a.s_score = b.s_score;
-- 42、查询每门功成绩最好的前两名
-- 牛逼的写法
select a.s_id,a.c_id,a.s_score from score a
where (select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 ORDER BY a.c_id
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT
c_id,
count(1) num
from score
GROUP BY c_id
HAVING num >5
ORDER BY num desc,c_id asc;
-- 44、检索至少选修两门课程的学生学号
SELECT
s_id,
COUNT(1) num
from score
GROUP BY s_id
HAVING num>=2;

-- 45、查询选修了全部课程的学生信息
SELECT * from student
where s_id in(
SELECT
s_id
from score
GROUP BY s_id
HAVING COUNT(s_id)=3);
-- 46、查询各学生的年龄
-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
SELECT
s_id,
s_birth,
if(DATE_FORMAT(s_birth,'%m')>DATE_FORMAT(CURRENT_DATE,'%m'),(DATE_FORMAT(CURRENT_DATE,'%Y')- DATE_FORMAT(s_birth,'%Y'))-1,(DATE_FORMAT(CURRENT_DATE,'%Y')- DATE_FORMAT(s_birth,'%Y')))
from student;

select s_birth,(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、查询本周过生日的学生
SELECT
*
from student
where
week(CURRENT_DATE) =week(s_birth);

 

select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)