-- 创建stu
create table stu(
s_id varchar(10) primary key,
s_name varchar(10) not null,
s_birth date,
s_sex varchar(10));

-- 导入数据
insert into stu values
(\'01\' , \'赵雷\' , \'1990-01-01\' , \'男\'),
(\'02\' , \'钱电\' , \'1990-12-21\' , \'男\'),
(\'03\' , \'孙风\' , \'1990-05-20\' , \'男\'),
(\'04\' , \'李云\' , \'1990-08-06\' , \'男\'),
(\'05\' , \'周梅\' , \'1991-12-01\' , \'女\'),
(\'06\' , \'吴兰\' , \'1992-03-01\' , \'女\'),
(\'07\' , \'郑竹\' , \'1992-04-21\' , \'女\'),
(\'08\' , \'王菊\' , \'1990-01-20\' , \'女\');

select * from stu; -- 检查数据
select count(*) from stu; -- 检查总行数8


-- 创建co
create table co(
c_id varchar(10) primary key,
c_name varchar(10),
t_id varchar(10));

-- 导入数据
insert into co values
(\'01\' , \'语文\' , \'02\'),
(\'02\' , \'数学\' , \'01\'),
(\'03\' , \'英语\' , \'03\');

select * from co; -- 检查数据
select count(*) from co; -- 检查总行数3


-- 创建te
create table te(
t_id varchar(10) primary key,
t_name varchar(10));

-- 导入数据
insert into te values
(\'01\' , \'张三\'),
(\'02\' , \'李四\'),
(\'03\' , \'王五\');

select * from te; -- 检查数据
select count(*) from te; -- 检查总行数3


-- 创建sc
create table sc(
s_id varchar(10),
c_id varchar(10),
score int);

-- 导入数据
insert into sc values
(\'01\' , \'01\' , 80),
(\'01\' , \'02\' , 90),
(\'01\' , \'03\' , 99),
(\'02\' , \'01\' , 70),
(\'02\' , \'02\' , 60),
(\'02\' , \'03\' , 80),
(\'03\' , \'01\' , 80),
(\'03\' , \'02\' , 80),
(\'03\' , \'03\' , 80),
(\'04\' , \'01\' , 50),
(\'04\' , \'02\' , 30),
(\'04\' , \'03\' , 20),
(\'05\' , \'01\' , 76),
(\'05\' , \'02\' , 87),
(\'06\' , \'01\' , 31),
(\'06\' , \'03\' , 34),
(\'07\' , \'02\' , 89),
(\'07\' , \'03\' , 98);

select * from sc; -- 检查数据
select * from te; -- 检查数据
select * from co; -- 检查数据
select * from stu; -- 检查数据
-- ----------------------------------------------------------------------------------
-- 1、查询\"01\"课程比\"02\"课程成绩高的学生信息及课程分数(选修的每一门课程的分数)
select *from sc where c_id=\"01\";
select *from sc where c_id=\"02\";

select stu.*,sc.c_id,sc.score
from stu
join (select *from sc where c_id=\"01\") as a1  on  stu.s_id=a1.s_id
join (select *from sc where c_id=\"02\") as a2  on  stu.s_id=a2.s_id
join sc on stu.s_id=sc.s_id
where a1.score>a2.score
;
-- ----------------------------------------------------------------------------------
-- 2、练习：查询\"01\"课程比\"02\"课程成绩低的学生的信息及课程分数
select stu.*,sc.c_id,sc.score
from stu
join (select *from sc where c_id=\"01\") as a1  on  stu.s_id=a1.s_id
join (select *from sc where c_id=\"02\") as a2  on  stu.s_id=a2.s_id
join sc on stu.s_id=sc.s_id
where a1.score<a2.score
;

-- ----------------------------------------------------------------------------------
-- 7、查询学过\"张三\"老师授课的同学的信息
select stu.*
from stu
join sc on stu.s_id=sc.s_id
join co on sc.c_id=co.c_id
join te on co.t_id=te.t_id
where te.t_name=\"张三\"
;
-- ----------------------------------------------------------------------------------
-- 40、查询选修\"张三\"老师所授课程的学生中，成绩最高的学生信息及其成绩
select stu.*,score
from stu
join sc on stu.s_id=sc.s_id
join co on sc.c_id=co.c_id
join te on co.t_id=te.t_id
where te.t_name=\"张三\" and score=(
select max(score)
from stu
join sc on stu.s_id=sc.s_id
join co on sc.c_id=co.c_id
join te on co.t_id=te.t_id
where te.t_name=\"张三\");

select stu.*,score
from stu
join sc on stu.s_id=sc.s_id
join co on sc.c_id=co.c_id
join te on co.t_id=te.t_id
where te.t_name=\"张三\"
having score=max(score)
;
-- ----------------------------------------------------------------------------------
-- 8、练习：查询没学过\"张三\"老师授课的同学的信息
select *
from stu
where s_id not in (
                                        select stu.s_id
                                        from stu
                                        left join sc on stu.s_id=sc.s_id
                                        left join co on sc.c_id=co.c_id
                                        left join te on co.t_id=te.t_id
                                        where te.t_name=\"张三\")
                                        ;
-- ----------------------------------------------------------------------------------
-- 9、查询学过编号为\"01\"并且也学过编号为\"02\"的课程的同学的信息
select stu.*
from stu
left join sc on stu.s_id=sc.s_id
where c_id in (\"01\",\"02\")
group by stu.s_id
having count(c_id)=2
;
-- ----------------------------------------------------------------------------------
-- 10、练习：查询学过编号为\"01\"但是没有学过编号为\"02\"的课程的同学的信息
select *
from sc
left join stu on stu.s_id=sc.s_id
where sc.c_id=01
and stu.s_id not in (
                                select sc.s_id from sc
                                left join stu on stu.s_id=sc.s_id
                                where sc.c_id=02)
;
               
select stu.*
from sc
left join stu on stu.s_id=sc.s_id
where c_id in (\"01\",\"02\")
group by stu.s_id
having group_concat(c_id)=\"01\"
;

-- ----------------------------------------------------------------------------------
-- 12、查询至少有一门课与学号为\"01\"的同学所学相同的同学的信息

select distinct stu.*
from sc
left join stu on stu.s_id=sc.s_id
where sc.c_id in (
select c_id
from sc
where sc.s_id=\"01\") and sc.s_id!=\"01\";

-- ----------------------------------------------------------------------------------
-- 13、练习：查询和\"01\"号的同学学习的课程完全相同的其他同学的信息

select stu.*
from sc
left join stu on sc.s_id=stu.s_id
group by sc.s_id
having group_concat(sc.c_id)=(select group_concat(c_id) from sc where s_id=\"01\") and sc.s_id!=\"01\"
;

-- ----------------------------------------------------------------------------------
-- 35、查询所有学生的课程及分数情况（一维转二维）
select
stu.s_id,
sum(if(c_id=\"01\",score,0)) as \"01\",
sum(if(c_id=\"02\",score,0)) as \"02\",
sum(if(c_id=\"03\",score,0)) as \"03\"
from stu
left join sc on stu.s_id=sc.s_id
group by stu.s_id
;
select
stu.s_id,
sum(c_id=\"01\")*score as \"01\",
sum(c_id=\"02\")*score as \"02\",
sum(c_id=\"03\")*score as \"03\"
from stu
left join sc on stu.s_id=sc.s_id
group by stu.s_id
;
-- ----------------------------------------------------------------------------------
-- 17、练习：按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select * from sc; -- 检查数据
select
s_id,
sum(if(c_id=\"01\",score,0)) as 课程01,
sum(if(c_id=\"02\",score,0)) as 课程02,
sum(if(c_id=\"03\",score,0)) as 课程03,
avg(score)  as 平均成绩
from sc
group by s_id
order by avg(score) desc;

select
s_id,
sum((c_id=\"01\")*score) as 课程01,
sum((c_id=\"02\")*score) as 课程02,
sum((c_id=\"03\")*score) as 课程03,
avg(score)
from sc
group by s_id
order by avg(score) desc;

-- ----------------------------------------------------------------------------------
-- 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
select sc.c_id,c_name,max(score) 最高分,min(score) 最低分,avg(score) 平均分,
                avg(score>=60) 及格率,
        avg(score>=70 and score<80) 中等率,
        avg(score>=80 and score<90) 优良率,
        avg(score>=90) 优秀率
from sc
left join co on sc.c_id=co.c_id
group by sc.c_id;