建表

# 學生表
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 sinner 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 sinner 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 sinner 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 sinner 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 sinner join t_score sc on s.stu_id = s.stu_idinner join t_course co on sc.s_cid = co.c_idinner 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);