建表

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

練習

#**25. 查詢和學號"01"同學學習的課程完全相同的其他同學的信息**
select t.stu_id, count(*)
from t_student tinner join t_score sc on t.stu_id = sc.s_stuidinner join t_course co on sc.s_cid = co.c_id
where co.c_id in (select co.c_idfrom t_student t1inner join t_score sc1 on t1.stu_id = sc1.s_stuidinner join t_course co1 on sc1.s_cid = co1.c_idwhere t1.stu_id = 01)
group by t.stu_id
having count(*) = (select count(*)from t_student t1inner join t_score sc1 on t1.stu_id = sc1.s_stuidinner join t_course co1 on sc1.s_cid = co1.c_idwhere t1.stu_id = 01);#26. 查詢沒學過"張三"老師講授的任一門課程的學生姓名
select s1.stu_name, s1.stu_id
from t_student s1
where s1.stu_id not in (select s.stu_idfrom t_student sinner join t_score sc on s.stu_id = sc.s_stuidinner join t_course co on sc.s_cid = co.c_idwhere co.c_id in (select co1.c_idfrom t_teacher teinner join t_course co1 on te.tea_id = co1.c_teaidwhere te.tea_name = '張三'));#**27. 查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績** （成績保留兩位小數）
select s.stu_id, s.stu_name, AVG(sc.s_score)
from t_student sinner join t_score sc on s.stu_id = sc.s_stuid
where sc.s_score < 60
group by s.stu_id, s.stu_name
having count(*) >= 2;#**28. 檢索"01"課程分數小于 60，按分數降序排列的學生信息**
select *
from t_student sinner join t_score sc on s.stu_id = sc.s_stuidinner join t_course co on sc.s_cid = co.c_id
where sc.s_score < 60and co.c_id = 01
order by sc.s_score DESC;#**29. 按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績** （平均成績保留兩位小數）
select s.*, sc.s_score, avg.a
from t_student sinner join t_score sc on s.stu_id = sc.s_stuidinner join (Select Avg(sc1.s_score) as 'a', sc1.s_stuid from t_score sc1 group by sc1.s_stuid) avgon s.stu_id = avg.s_stuid
order by a DESC;#**30. 查詢各科成績最高分、最低分、平均分、選修人數、及格率** （及格率以百分比格式顯示）
select MAX(sc.s_score),MIN(sc.s_score),AVG(sc.s_score),COUNT(sc.s_score),concat(FORMAT(((select Count(*) from t_score sc1 where sc1.s_cid = sc.s_cid and sc1.s_score >= 60) /(select Count(*) from t_score sc2 where sc2.s_cid = sc.s_cid)) * 100, 2), '%')
from t_score scinner join t_student s on s.stu_id = sc.s_stuid
group by sc.s_cid;#31. 按各科成績進行排序，并顯示排名， 成績重復時不保留名次空缺
select s.*, sc.s_cid, row_number() over (PARTITION BY sc.s_cid ORDER BY sc.s_score)
from t_student sinner join t_score sc on s.stu_id = sc.s_stuid;#**32. 按各科成績進行排序，并顯示排名，成績重復時保留名次空缺**
select s.*, sc.s_cid, RANK() over (PARTITION BY sc.s_cid ORDER BY sc.s_score)
from t_student sinner join t_score sc on s.stu_id = sc.s_stuid;