SQL练习题:2.2

建表

# 学生表
create table t_student
(
    stu_id   varchar(10),
    stu_name varchar(10),
    stu_age  datetime,
    stu_sex  varchar(10)
);

# 课程表
create table t_t_course
(
    c_id    varchar(10),
    c_name  varchar(10),
    c_teaid varchar(10)
);

# 教师表
create table t_t_teacher
(
    tea_id   varchar(10),
    tea_name varchar(10)
);

# 成绩表
create table t_t_score
(
    s_stuid varchar(10),
    s_cid   varchar(10),
    s_score decimal(18, 1)
);

-- 向t_student表插入数据
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('01', '赵雷', '1990-01-01', '男');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('02', '钱电', '1990-12-21', '男');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('03', '孙风', '1990-12-20', '男');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('04', '李云', '1990-12-06', '男');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('05', '周梅', '1991-12-01', '女');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('06', '吴兰', '1992-01-01', '女');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('07', '郑竹', '1989-01-01', '女');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('09', '张三', '2017-12-20', '女');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('10', '李四', '2017-12-25', '女');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('11', '李四', '2012-06-06', '女');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('12', '赵六', '2013-06-13', '女');
insert into t_student(stu_id, stu_name, stu_age, stu_sex)
values ('13', '孙七', '2014-06-01', '女');

-- 向t_t_course表插入数据
insert into t_t_course(c_id, c_name, c_teaid)
values ('01', '语文', '02');
insert into t_course(c_id, c_name, c_teaid)
values ('02', '数学', '01');
insert into t_course(c_id, c_name, c_teaid)
values ('03', '英语', '03');

-- 向t_t_teacher表插入数据
insert into t_teacher(tea_id, tea_name)
values ('01', '张三');
insert into t_teacher(tea_id, tea_name)
values ('02', '李四');
insert into t_teacher(tea_id, tea_name)
values ('03', '王五');

-- 向t_t_score表插入数据
insert into t_score(s_stuid, s_cid, s_score)
values ('01', '01', 80);
insert into t_score(s_stuid, s_cid, s_score)
values ('01', '02', 90);
insert into t_score(s_stuid, s_cid, s_score)
values ('01', '03', 99);
insert into t_score(s_stuid, s_cid, s_score)
values ('02', '01', 70);
insert into t_score(s_stuid, s_cid, s_score)
values ('02', '02', 60);
insert into t_score(s_stuid, s_cid, s_score)
values ('02', '03', 80);
insert into t_score(s_stuid, s_cid, s_score)
values ('03', '01', 80);
insert into t_score(s_stuid, s_cid, s_score)
values ('03', '02', 80);
insert into t_score(s_stuid, s_cid, s_score)
values ('03', '03', 80);
insert into t_score(s_stuid, s_cid, s_score)
values ('04', '01', 50);
insert into t_score(s_stuid, s_cid, s_score)
values ('04', '02', 30);
insert into t_score(s_stuid, s_cid, s_score)
values ('04', '03', 20);
insert into t_score(s_stuid, s_cid, s_score)
values ('05', '01', 76);
insert into t_score(s_stuid, s_cid, s_score)
values ('05', '02', 87);
insert into t_score(s_stuid, s_cid, s_score)
values ('06', '01', 31);
insert into t_score(s_stuid, s_cid, s_score)
values ('06', '03', 34);
insert into t_score(s_stuid, s_cid, s_score)
values ('07', '02', 89);
insert into t_score(s_stuid, s_cid, s_score)
values ('07', '03', 98);

练习

#**9. 查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩**
select s.stu_id, s.stu_name, s_score
from t_student s
         inner join t_score sc on s.stu_id = sc.s_stuid
         inner join t_course c on sc.s_cid = c.c_id
         inner join t_teacher t on c.c_teaid = t.tea_id
where t.tea_name = '张三'
  and sc.s_score = (select MAX(sc1.s_score)
                    from t_score sc1
                             inner join t_course c1 on sc1.s_cid = c1.c_id
                             inner join t_teacher t1 on c1.c_teaid = t1.tea_id
                    where t1.tea_name = '张三');


#**10. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩**
select sc1.s_stuid, sc1.s_cid, sc1.s_score
from t_score sc1
         inner join t_score sc2
                    on sc1.s_stuid = sc2.s_stuid
                        and sc1.s_cid <> sc2.s_cid
                        and sc1.s_score = sc2.s_score
;

#11. 查询每门功成绩最好的前两名
#select sc.s_stuid,sc.s_cid,sc.s_score from t_score sc遍历学生
#select COUNT(*) from t_score sc1 where sc.s_cid=sc1.s_cid and sc1.s_score>=sc.s_score)<=2找出比当前学生成绩高的人数,不小于2即为前两名
select sc.s_stuid, sc.s_cid, sc.s_score
from t_score sc

where (select COUNT(*) from t_score sc1 where sc.s_cid = sc1.s_cid and sc1.s_score >= sc.s_score) <= 2
ORDER BY sc.s_cid;

#12查询选修了全部课程的学生信息
select count(*), s_stuid, stu_id, stu_name, stu_age, stu_sex
from t_student s
         inner join t_score sc on s.stu_id = sc.s_stuid
         inner join t_course c on sc.s_cid = c.c_id
group by s_stuid, stu_id, stu_name, stu_age, stu_sex
having count(*) = (select count(*) from t_course);

#13查询本周过生日的学生
select *, week(stu_age)
from t_student t
where week(t.stu_age) = week(NOW());

#14. 查询下月过生日的学生
select *
from t_student t
where month(t.stu_age) = month(NOW()) + 1;

#15. 查询出只选修两门课程的学生学号和姓名
select count(*), s_stuid, stu_id, stu_name, stu_age, stu_sex
from t_student s
         inner join t_score sc on s.stu_id = sc.s_stuid
         inner join t_course c on sc.s_cid = c.c_id
group by s_stuid, stu_id, stu_name, stu_age, stu_sex
having count(*) = 2;

#16. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select sc1.*
from t_score sc1
         inner join t_score sc2 on sc1.s_stuid = sc2.s_stuid and sc1.s_cid = '01' and sc2.s_cid = '02' and
                                   sc1.s_score > sc2.s_score;