sql题目---day39

# 1、查询所有的课程的名称以及对应的任课老师姓名
#where
select 
    teacher.tname,course.cname
from 
    teacher,course
where
    course.teacher_id  = teacher.tid

#内联写法
select 
    teacher.tname,course.cname
from
    teacher inner join course on course.teacher_id = teacher.tid;
    

# 2、查询学生表中男女生各有多少人
select 
    gender,count(*)
from 
    student
group by
    gender;

# 3、查询物理成绩等于100的学生的姓名
#where
select
    student.sid,student.sname,score.num
from 
    course,score,student
where
    course.cid = score.course_id
and student.sid = score.student_id
and course.cname = "物理"
and score.num = 100 

# inner join
select
    student.sid,student.sname,score.num
from 
    course inner join score on course.cid = score.course_id
    inner join student on student.sid = score.student_id
where
    course.cname = "物理"
and score.num = 100

#4.查询平均成绩大于八十分的同学的姓名和平均成绩
select
    student_id,avg(num)
from 
    score
group by
    student_id
having
    avg(num) > 80;

#where 写法
select 
    student_id,avg(num),sname
from 
    score,student
where
    score.student_id = student.sid
group by
    student_id
having
    avg(num) > 80;

#内联写法
select
    student_id,avg(num),sname
from 
    score inner join student on score.student_id = student.sid
group by
    student_id
having
    avg(num) > 80;
    
    
# 5、查询所有学生的学号，姓名，选课数，总成绩
#选课数
select
    student_id,count(*)
from
    score
group by
    student_id
#总成绩
select
    student_id,sum(num)
from 
    score
group by
    student_id

#where
'''group by +字段 by谁搜谁，跟其他表要搜索的字段无关'''
select 
    student_id,sname,count(*),sum(num)
from 
    score,student
where
    score.student_id = student.sid
group by
    student_id;

#内联写法
select
    student_id,sname,count(*),sum(num)
from 
    score inner join student on score.student_id  = student.sid
group by
    student_id;

#附加(展现所有学生，使用左联以学生表为主)
select
    student.sid,sname,count(score.course_id),sum(num)
from 
    student left join score on score.student_id = student.sid
group by
    student.sid;


## 6、 查询姓李老师的个数
select
    count(*)
from 
    teacher
where
    tname like "李%";

# 7、 查询没有报李平老师课的学生姓名
#1.先查报了李平老师课程的学生id
'''distinct 去重
distinct score.student_id
distinct(score.student_id)
'''
select
    distinct(score.student_id)
from
    teacher,course,score
where
    teacher.tid = course.teacher_id
and course.cid =score.course_id
and teacher.tname = "李平";
#2.除了这些学习李平老师id的，剩下的就没有学习李平课程的
select
    *
from 
    student
where
    sid not in (1号);
#3.综合拼接
select
    sname
from 
    student
where
    sid not in (select
    distinct(score.student_id)
from
    teacher,course,score
where
    teacher.tid = course.teacher_id
and course.cid =score.course_id
and teacher.tname = "李平"  
    );


## 8、 查询物理课程的分数比生物课程的分数高的学生的学号
#1.物理课程学生分数
select
    score.student_id,score.num,course.cid
from 
    course inner join score on course.cid = score.course_id
where
    course.cname ="物理";
#2.生物课程学生分数
select
    score.student_id,score.num,course.cid
from
    course inner join score on course.cid = score.course_id
where
    course.cname = '生物'
#3.综合拼接
select
    t1.t1_id
from
    (select 
        score.student_id as t1_id,score.num as t1_num,course.cid as t1_cid
    from 
        course inner join score on course.cid = score.course_id
    where 
        course.cname = "物理"
    ) as t1
    inner join(select 
    score.student_id as t2_id,score.num as t2_num,course.cid as t2_cid
    from 
        course inner join score on course.cid = score.course_id
    where 
        course.cname = "生物"
    ) as t2
    on t1.t1_id = t2.t2_id
where
    t1.t1_num > t2.t2_num;


# 9、 查询没有同时选修物理课程和体育课程的学生姓名
#1.找物理和体育的课程id
select
    course.cid
from 
    course
where
    cname ="物理" or cname = "体育"
#2.找学习了体育和物理的学生id
select 
    student_id
from 
    score
where
    course_id in(2,3);
#3.拼接数据
select
    student_id
from 
    score
where
    course_id in(select 
    course.cid
    from 
        course
    where 
        cname = "物理" or cname = "体育"
    );
#4.(同时)学习物理和体育的学生id
select
    student_id
from 
    score
where
    course_id in (select 
    course.cid
    from 
        course
    where 
        cname = "物理" or cname = "体育"
    )
group by
    score.student_id
having
    count(*) = 2;
#5.除了同时学习物理和体育的学生id之外，剩下的都是没有同时学习的id
select
    sid
from 
    student
where
    sid not in (3号)
#6.综合拼接
select
    sid
from 
    student
where
    sid not in (select 
    student_id
    from 
        score
    where 
        course_id in (select 
        course.cid
        from 
            course
        where 
            cname = "物理" or cname = "体育"
    )
group by
    score.student_id
having
    count(*) = 2)

#10、查询挂科超过两门(包括两门)的学生姓名和班级
'''通过查询出来的id，在和其他表进行联表，找出需要中的对应字段展示即可'''
select
    student_id,sname,caption
from 
    score inner join student on student.sid = score.student_id
    inner join class on class.cid = student.class_id
where
    num < 60
group by
    student_id
having
    count(*) >= 2;

# 11、查询选修了所有课程的学生姓名
# 1.先统计所有课程总数
select count(*) from course;
#2.按照学生分类，总数量是1号查询出来的数据，就认为学了所有课
select 
    score.student_id,student.sname
from 
    socre inner join student on score.student_id = student.sid
group by 
    score.student_id
having
    count(*) = (select count(*) from course);
#3.综合拼接
select
    score.student_id,student.sname
from 
    score inner join student on score.student_id = student.sid
group by
    score.student_id
having
    count(*) = (1号);

# 12、查询李平老师教的课程的所有成绩记录
#内联写法
select
    score.student_id,course.cid,course.cname,score.num
from 
    teacher,course,score
where
    teacher.tid = course.teacher_id
and
    score.course_id = course.cid
and
    teacher.tname = '李平';
#1.找李平老师所有课程id
select
    course.cid
from 
    teacher,course
where
    teacher.tid = course.teacher_id
and
    teacher.tname ="李平";
#2.找这几门课程对应的数据
select
    *
from 
    score
where
    course_id in (1号)
#3.综合拼接
select
    *
from 
    score
where
    course_id in (select 
    course.cid
    from 
        teacher,course
    where 
        teacher.tid = course.teacher_id
        and
        teacher.tname = "李平"
    );

#13.查询全部学生都选修了的课程号和课程名
#1.通过score表，找有成绩的学生个数
select 
    count(distinct student_id)
from 
    score
#2.按照课程分类，筛选学生个数为13的课程id(一个学科被13人学习，等于说都选秀了)
select
    course_id
from 
    score
group by
    course_id
having
    count(*) = (1号数据13);
#3.综合拼接
select
    course_id,course.cname
from 
    score,course
where
    score.course_id = course.cid
group by
    course_id
having
    count(*) = (select
    count(distinct student_id)
    from 
        score
    );

#14.查询每门课程被选修的次数
select 
    course_id,count(*)
from 
    score
group by
    course_id;

#15.查询只选修了一门课程的学生学号和姓名
#1.按照学生分类，统计个数是1(选一门)
select
    student_id
from 
    score
group by
    student_id
having
    count(*) = 1;
#2.顺带连一张学生表，通过id拿学生姓名
select
    student_id,student.sname
from 
    score inner join student on score.student_id =student.sid
group by
    student_id
having
    count(*) = 1;

#16.查询所有学生考出的成绩并按从高到低排序（成绩去重）
select
    distinct num
from 
    score
order by
    num desc;

#加上学生形成一一对应的关系
select
    distinct num,student_id
from 
    score
order by
    num desc;

## 17、查询平均成绩大于85的学生姓名和平均成绩
#1.先搜索出学生id
select
    score.student_id,avg(score.num)
from 
    score
group by
    score.student_id
having  
    avg(score.num) > 85;
#2.拿id顺带联一张学生表找出姓名
select
    score.student_id,avg(score.num),student.sname
from 
    score inner join student on student.sid =score.student_id
group by
    score.student_id
having
    avg(score.num) >85;

# 18、查询生物成绩不及格的学生姓名和对应生物分数
select
    student.sname,score.num,course.cname
from 
    course inner join score on score.course_id =course.cid
    inner join student on score.student_id  = student.sid
where
    score.num < 60
and
    course.cname ="生物";

#查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
#1.找李平老师所教的课程id
select
    course.cid
from 
    teacher,course
where
    teacher.tic =course.teacher_id
and
    teacher.tname = "李平"; #(2,4)
#2.在学习李平老师课程基础上，按照学生分类，找出平均分最高的id
select
    score.student_id
from 
    score
where
    score.course_id in (2,4)
group by
    score.student_id
order by
    avg(num) desc limit 1;
#3.通过学生id,顺带连一张学生表，找出姓名
select
    score.student_id,student.sname,avg(num)
from 
    score,student
where
    score.student_id =student.sid
and
    score,course_id in(2,4)
group by
    score.student_id
order by
    avg(num) desc limit 1;

# 20、查询每门课程成绩最好的学生姓名和分数,课程id
#1.找分数最大值，按照课程分类
select
    course_id,max(num) as max_num
from 
    score
group by
    score.course_id;
#2.找出该分数对应的学生相关数据
select
    *
from 
    score as t1 inner join(1号) as t2 on t1.course_id = t2.course_id
    inner join student t3 on t1.student_id =t3.sid;
#3.数据拼接
select
    t2.max_num,t3.sname,t1.course_id
from 
    score as t1 inner join(select 
    course_id,max(num) as max_num
    from 
        score
    group by 
        score.course_id
    ) as t2 on t1.course_id = t2.course_id
        inner join student t3 on t1.student_id = t3.sid
where
    t2.max_num = t1.num

#21.查询不同课程但成绩相同的，学生号，成绩，课程号
select 
    s1.student_id as s1_sid,
    s2.student_id as s2_sid,
    s1.course_id as s1_cid,
    s2.course_id as s2_cid,
    s1.num as s1_num,
    s2.num as s2_num
from 
    score as s1,
    score as s2
where
    # 不同的课程 (不要使用!= 相同的数据会查两遍,>的一遍,<的一遍)
    s1.course_id > s2.course_id
    and
    s1.num = s2.num


# 24、任课最多的老师中学生单科成绩最高的课程id、学生姓名和分数
# 1.老师任课的最大数量是几门?
select 
    count(*)
from 
    course
group by
    teacher_id
order by 
    count(*) desc limit 1
    
# 2.找最大任课数量为2的老师id
select 
    teacher_id
from 
    course
group by 
    teacher_id
having
    count(*) = (1号)

# 综合拼接
select 
    teacher_id
from 
    course
group by 
    teacher_id
having
    count(*) = (select 
        count(*)
    from 
        course
    group by
        teacher_id
    order by 
        count(*) desc limit 1)

# 3.通过老师id,找对应课程
select cid from course where  teacher_id in (2) # 2,4


# 4.通过该课程号,找其中的最大分数
select 
    course_id,
    max(num) as max_num
from 
    score
where 
    course_id in (3号)
group by
    course_id


# 5.把对应的学生姓名,最大分数拼在一起,做一次单表查询
select
    t1.num,t2.max_num,t3.sid,t3.sname,t1.course_id
from 
    score t1 inner join (4号) t2 on t1.course_id = t2.course_id
    inner join student t3 on t3.sid = t1.student_id
where
    t1.num = t2.max_num


# 综合拼装:

select
    t1.num,t2.max_num,t3.sid,t3.sname,t1.course_id
from 
    score t1 inner join (select 
    course_id,
    max(num) as max_num
from 
    score
where 
    course_id in (select cid from course where  teacher_id in (select 
    teacher_id
from 
    course
group by 
    teacher_id
having
    count(*) = (select 
        count(*)
    from 
        course
    group by
        teacher_id
    order by 
        count(*) desc limit 1)))
group by
    course_id
) t2 on t1.course_id = t2.course_id
    inner join student t3 on t3.sid = t1.student_id
where
    t1.num = t2.max_num