SQL练习题:2.3

建表

# 学生表
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);

练习

#17.查询学过编号为"02"但没有学过编号为"01"课程的学生信息和相关课程成绩
select *
from t_student s
         inner join t_score sc on s.stu_id = sc.s_stuid
where s.stu_id in (select sc1.s_stuid from t_score sc1 where sc1.s_cid = 02)
  and s.stu_id not in (select sc2.s_stuid from t_score sc2 where sc2.s_cid = 01);

#18. 查询同时选修了"01"课程和"02"课程的学生信息及相关课程成绩
select *
from t_student s
where s.stu_id in (select sc1.s_stuid from t_score sc1 where sc1.s_cid = 02)
  and s.stu_id in (select sc2.s_stuid from t_score sc2 where sc2.s_cid = 01);

#19. 查询选修了"01"课程但可能没有选修"02"课程的学生信息及相关课程成绩（不存在时显示为0)
select *
from t_student s
         inner join t_score sc on s.stu_id = sc.s_stuid
where s.stu_id not in (select sc1.s_stuid from t_score sc1 where sc1.s_cid = 02)
  and s.stu_id in (select sc2.s_stuid from t_score sc2 where sc2.s_cid = 01);

#20. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩（成绩保留2位小数）
select round(avg(sc.s_score), 2), s.stu_name, s.stu_id
from t_student s
         inner join t_score sc on s.stu_id = sc.s_stuid
group by s.stu_name, s.stu_id
having avg(sc.s_score >= 60);

#21. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和** （没有选课的学生显示为0）
select s.stu_id, s.stu_name, COUNT(sc.s_score), sum(sc.s_score)
from t_student s
         inner join t_score sc on s.stu_id = sc.s_stuid
group by s.stu_id, s.stu_name;

#22. 查询学过「张三」老师授课的同学的信息
select distinct s.*
from t_student s
         inner join t_score sc on s.stu_id = s.stu_id
         inner join t_course co on sc.s_cid = co.c_id
         inner join t_teacher t on co.c_teaid = t.tea_id
where t.tea_name = '张三' order by s.stu_id;

#23. 查询没有学全所有课程的同学的信息
select s.stu_id,s.stu_name,count(sc.s_score) from t_student s inner join t_score sc on s.stu_id = sc.s_stuid inner join t_course co on sc.s_cid = co.c_id group by s.stu_id,stu_name having count(sc.s_score) != 3;

#24. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息（不含学号为"01"的学生）
select distinct s.* from t_student s inner join t_score sc on s.stu_id = sc.s_stuid where sc.s_cid in (select sc1.s_cid from t_score sc1 where sc1.s_stuid = 01);