sql查询练习

sql查询练习：

表：

-- 2、查询“生物”课程比“体育”课程成绩高的所有学生的学号；
select A.student_id,sw,ty from
(select student_id,number as sw from score left join course on score.course_id = course.cid where course.cname = '生物') as A
left join
(select student_id,number  as ty from score left join course on score.course_id = course.cid where course.cname = '物理') as B
on A.student_id = B.student_id where sw > if(isnull(ty),0,ty);
3、查询平均成绩大于60分的同学的学号和平均成绩； 
select student_id,avg(number) as score_avg from score group by student_id having score_avg > 60
4.查询所有同学的学号、姓名、选课数、总成绩
select student_id, sname, course_count, score_sum from 
(select student_id, count(course_id) as course_count, sum(number) as score_sum
from score group by student_id) as A right join student on A.student_id = student.sid
5、查询姓“李”的老师的个数；
select count(1) from teacher where tname like '李%';

6、查询没学过“李平”老师课的同学的学号、姓名；
select sid,sname from student where sid not in 
(SELECT distinct student_id from score where score.course_id in (
select cid from course left join teacher on course.teacher_id=teacher.tid
where tname='李平')) -- 先查到李平老师教的所有课的ID，再取选过课的所有学生ID，再在学生表中筛选

7、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
select student_id,sname from 
(select student_id,course_id from score where course_id=1 or course_id=2) as B
left join student on student.sid=B.student_id group by student_id having 
count(1)=2

8、查询学过“波多”老师所教的所有课的同学的学号、姓名；
select student_id,sname from
-- 1.查出学过波多老师课的同学
(select student_id,course_id from score where course_id in (
select cid from course left join teacher on course.teacher_id=teacher.tid where tname='波多')
) as B 
-- 3.查出所有学过波多老师课的同学
left join student on B.student_id = student.sid group by student_id HAVING count(1) = 
-- 2.查出波多老师所教的课程数量
(select count(1) as c from course LEFT JOIN teacher on course.teacher_id=teacher.tid group by tname having
tname='波多')

 9、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
select * from student join (SELECT A.student_id as aid from 
(SELECT student_id,number from score where course_id=1) as A
LEFT JOIN
(SELECT student_id,number from score where course_id=2) as B
on A.student_id=B.student_id where A.number > B.number) as C
on aid = student.sid 

查询所有的课程的名称以及对应的任课老师姓名
 select cname,tname from course LEFT JOIN teacher on course.teacher_id = teacher.tid;
 
2、查询学生表中男女生各有多少人
select gender 性别, count(1) as 人数 from student GROUP BY gender

#3、查询体育成绩等于100的学生的姓名
select sname from student where sid in 
(select student_id from course join score on course.cid=score.course_id where cname='体育' and number=100)

10.查询有课程成绩小于60分的同学的学号、姓名；
SELECT  sid, sname from student where sid IN
(SELECT student_id from score where number<60)

SELECT distinct student_id, sname from student 
join score on student.sid = score.student_id where number<60


11、查询没有学全所有课的同学的学号、姓名；
先以学生分组计数课程，与总课程比较，再在student表中取出sname 
SELECT
    sid,sname
FROM
    student
WHERE
    sid IN (
SELECT student_id FROM score group by student_id
HAVING count(course_id) = (select count(cid) from course)
);

12查询波多老师教的课程的所有成绩记录
SELECT * from score 
where course_id in 
(select cid from course inner join teacher on course.teacher_id = teacher.tid 
where teacher.tname='波多');

13查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
select student_id,sname from student inner join score on student.sid=score.student_id where course_id in 
(select course_id from score where student_id=1) and student_id!=1

13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名；
先查询1号学过的所有课id，再找出至少学过一课的其他人，以这些人分组得到所学课程数，与1号所学课程数对比
select student_id,sname from student inner join score on student.sid=score.student_id
 where course_id 
in (select course_id from score where student_id=1) and student_id!=1 
GROUP BY student_id having count(course_id) = 
 (select count(course_id) from score where student_id = 1)

14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
个数相同
002学过的也学过

先过滤出和2号学的个数相同，且学的课2号也学过的student_id, 
再按学号分组再统计学的课程个数与2号相等的同学id
select 
        student_id, sname 
    from student inner join score on student.sid=score.student_id
where 
(select student_id from score where student_id!=2 group by student_id having count(1)
=(select count(course_id) from score where student_id=2))
and course_id in 
(select course_id from score where student_id = 2) 
GROUP BY  
        student_id 
    HAVING 
    count(course_id) = (select count(1) from score where student_id = 2)

15、删除学习“苍空”老师课的score表记录；
DELETE from score WHERE course_id in 
(SELECT cid from course INNER JOIN
 teacher on course.teacher_id=teacher.tid where tname='苍空') 

16.向SC表中插入一些记录，这些记录要求符合以下条件：
①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
由于insert 支持 
                inset into tb1(xx,xx) select x1,x2 from tb2;
        所有，获取所有没上过002课的所有人，获取002的平均成绩
insert into score select
0,sid,2,(select avg(number) FROM score where course_id=2) from student
where sid not in
(select student_id from score where course_id = 2)
 
update score  set sid=4 where sid=5
insert into score values(5,3,3,50)
delete from score where sid=4 or sid=5 or sid=6

17/按平均成绩从低到高 显示所有学生的“语文”、“数学”、“英语”三门的
课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
select sc.student_id as 学生ID,
(select number from score inner join course on score.course_id=course.cid where course.cname='生物' and score.student_id=sc.student_id)
as 生物,
(select number from score inner join course on score.course_id=course.cid where course.cname='体育' and score.student_id=sc.student_id)
as 体育,
(select number from score inner join course on score.course_id=course.cid where course.cname='物理' and score.student_id=sc.student_id)
as 物理,
count(sc.course_id) as 有效课程数,
avg(sc.number) as 有效平均分
from score as sc group by student_id desc        

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select course_id,max(number),min(number) from score GROUP BY course_id

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
select course_id,avg(number) as avgnum,
SUM(case when score.number>60 then 1 else 0 END)/count(1)*100 as percent from score  
group by course_id ORDER BY avgnum asc,percent desc;

SELECT course_id, avg(number) as avgnum,
sum(case when score.number>60 then 1 else 0 end)/count(1)*100 as percent from score
group by course_id order by avgnum asc,percent desc;


20、课程平均分从高到低显示（再显示任课老师）；
select score.course_id,course.cname, teacher.tname,avg(if(isnull(number),0,number)) as a from course
inner join score on course.cid = score.course_id
inner join teacher on course.teacher_id = teacher.tid
GROUP BY score.course_id order by a desc;

21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
先查出第四名的成绩(分组去重)，然后在成绩表中找出比第四名高的成绩的记录 
select * from 
(select
        student_id,
        course_id,
        number,
        1,
        (select number from score as s2 where s2.course_id=s1.course_id GROUP BY number ORDER BY number desc limit 3,1) as cc
from score as s1) as T 
where T.number>T.cc; 

22、查询每门课程被选修的学生数；
select course_id, count(1) from score group by course_id;

23、查询出只选修了一门课程的全部学生的学号和姓名；
select student_id, sname from student inner join 
score on student.sid = score.student_id group by 
student_id HAVING count(1)=1

24、查询男生、女生的人数；
select * from (select count(1) as man from student where gender='男') as A,
(select count(1) as feman from student where gender='女') as B

25、查询姓“张”的学生名单；
select sname from student where sname like '张%'

26、查询同名同姓学生名单，并统计同名人数；
SELECT sname,count(1) from student group by sname; 

27、查询每门课程的平均成绩，结果按平均成绩升序排列，
平均成绩相同时，按课程号降序排列；
SELECT course_id,avg(number) as a from score
GROUP BY course_id
ORDER BY  a asc, course_id desc;

28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
select student_id,sname,avg(number) as avg from  student inner join 
score on student.sid=score.student_id GROUP BY student_id
having avg > 50;

29、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
SELECT student_id,score.number from student LEFT JOIN
score on student.sid=score.student_id
LEFT JOIN course on score.course_id=course.cid
where course.cname ='体育' and score.number < 60

30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
SELECT student_id,sname from student LEFT JOIN
score on student.sid=score.student_id
where course_id =3 and score.number >80

31、求选了课程的学生人数
SELECT count(distinct student_id) from score;

select count(c) from (
select count(1) as c from score group by student_id) as A

32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
select sname,number from student LEFT JOIN score on student.sid=score.student_id
where score.course_id in (
SELECT cid from course INNER JOIN teacher on course.teacher_id=teacher.tid where tname='波多')
ORDER BY number desc LIMIT 1;

33、查询各个课程及相应的选修人数；
select course.cname,count(1) from course inner JOIN
score on course.cid = score.course_id group by course_id

34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；(笛卡尔积)
SELECT DISTINCT s1.sid,s2.student_id,s1.course_id,s1.number from 
score as s1,score as s2 where s1.number=s2.number and s1.course_id !=s2.course_id;

37查询全部学生都选的课程的课程号和课程名
select course_id,count(1) from score group by course_id having count(1) = (select count(1) from student);

-- 38、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
select student_id,student.sname from score
left join student on score.student_id = student.sid
where score.course_id not in (
select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '叶平'
)
group by student_id

-- 39、查询两门以上不及格课程的同学的学号及其平均成绩；
select student_id,avg(number) from score where number < 60 group by student_id having count(1) > 2

-- 40、检索“002”课程分数小于60，按分数降序排列的同学学号；
select student_id from score where course_id=2 and number<60 order by number desc

-- 41、删除“002”同学的“001”课程的成绩；
delete from score where course_id = 1 and student_id = 2

-- 42、查询每门课程成绩都高于57分的同学id
-- 一,查出每个同学高于57分的课程数，查出每个同学所学课程数，相等
select s1.student_id from 
(select student_id, count(1) as c from score where number>57 group by student_id) as s1,
(select student_id, count(1) as c from score group by student_id) as s2 
where s1.student_id=s2.student_id and s1.c = s2.c;
-- 二、查出每个同学的课程数，等于此同学大于57分的课程数，
select student_id from score GROUP BY student_id HAVING count(1) = sum(case when number>57 then 1 else 0 end);
-- 三、查出id不在有小于等于57分的id
select student_id from score group by student_id  having student_id not in (SELECT student_id from score where number<=57);
-- 四、以同学分组，最小成绩都大于57的同学
select student_id from score GROUP BY student_id having min(number)>57;

-- 43、查询1号课程成绩大于2号课程成绩的同学
select A.student_id from  
(select student_id,number from score where course_id=1) as A,
(select student_id,number from score where course_id=2) as B
where A.student_id = B.student_id and A.number>B.number