--表结构：
--学生表：sid 学生编号，sname 学生姓名，sage 出生年月，ssex 学生性别
--课程表：cid 课程编号，cname 课程名称， tid 教师编号
--教师表：tid 教师编号，tname 教师姓名
--成绩表：sid 学生编号，cid 课程编号，score 分数

create table student --学生表
(
sid varchar(10),
sname varchar(50),
sage datetime,
ssex char(2)
);

create table course --课程表
(
cid varchar(10),
cname varchar(50),
tid varchar(10)
);
create table teacher --老师表
(
tid varchar(10),
tname varchar(50)
);

create table sc --成绩表
(
sid varchar(10),
cid varchar(10),
score float
);

insert into student
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', '郑竹', '1989-07-01',
'女'),('08', '王菊', '1990-01-20', '女');

insert into course
values ('01', '语文', '02'),('02', '数学', '01'),('03', '英语', '03');
insert into teacher values ('01', '张三'),('02', '李四'),('03', '王五');

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);

GO

--1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
--分析：需要几张表 学生表，成绩表这两张表之间有直接关系，所以可以直接关联

select s.*,one.score,two.score from student s
left join sc one on s.sid=one.sid and one.cid='01'
left join sc two on s.sid=two.sid and two.cid='02'
where one.score>two.score;

----2、查询平均成绩大于60分的同学的学号和平均成绩；
select sid,avg(score) as avgScore from sc
group by sid having avg(score)>60

--3、查询所有同学的学号、姓名、选课数、总成绩；
select * from student t1
left join (select sid,SUM(score) as sumScore from sc group by sid) t2 on t1.sid= t2.sid

--4、查询姓“李”的老师的个数；
select count(*) from teacher where tname like '李%'

--5、查询没学过“张三”老师课的同学的学号、姓名；
select * from student where sid not in(
select sid from sc
inner join course t1 on sc.cid = t1.cid
inner join teacher t2 on t1.tid = t2.tid
where t2.tname='张三'
)

--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

select s.*,one.score,two.score from student s
left join sc one on s.sid=one.sid and one.cid='01'
left join sc two on s.sid=two.sid and two.cid='02'
where one.score is not null and two.score is not null

-- sql server 查询空值：colName is null
-- sql server 查询空值：colName is not null

--9、查询所有课程成绩小于60分的同学的学号、姓名；
select sid,sname from student
where sid not in
(select sid from sc where sid=sid and score>60)

--10、查询没有学全所有课的同学的学号、姓名；
select sid,sname from student st
where
(select count(*) from sc where st.sid = sc.sid)<
(select count(*) from course)

--11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；
select distinct st.sid,sname from student st
inner join sc on st.sid = sc.sid
where sc.cid in (select cid from sc where sid='01') and st.sid != '01'

--12、查询和“02”号的同学学习的课程完全相同的其他同学学号和姓名；
select sid,sname from student st
where sid <> '02' and
not Exists (select * from sc where sc.sid=st.sid and cid not in (
select cid from sc where sid='02')) and
not Exists (select * from sc where sid='02' and cid not in (select cid from sc where sc.sid=st.sid))

--13 、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select cid as 课程ID,
最高分=(select Max(score) from sc where sc.cid=course.cid),
最低分=(select Min(score) from sc where sc.cid=course.cid)
from course

--14、查询不同老师所教不同课程平均分从高到低显示
select 课程ID = cid,课程名称 = cname,授课教师 = tname,
平均成绩 = (select avg(score) from sc where cid = course.cid)
from course
inner join teacher on course.tid=teacher.tid
order by 平均成绩 desc

--15、查询每门课程被选修的学生数
select 课程ID=cid,
选修人数=(select count(*) from (select distinct sid from sc where cid=cs.cid) as temp)
from course cs

-- 16、查询出只选修了2门课程的全部学生的学号和姓名
select sid,sname from student where sid in
(select sid from sc group by sid having count(*) =2)

select 学号=sid,姓名=sname from student st where
(select count(*) from (select distinct cid from sc where sid=st.sid) as temp) =2
-- (select distinct cid from sc where sid=st.sid) as temp 根据学生ID 查询学习了几门课程

--17、查询男生、女生人数
select ssex,count(ssex) from student group by ssex

select 男生人数= (select COUNT(*) from student where ssex = '男'),
女生人数 = (select COUNT(*) from student where ssex = '女')

--18、1991年出生的学生名单(注：Student表中Sage列的类型是datetime)
select * from student where YEAR(sage)=1991

--19、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select 课程ID=cid,课程名称=cname,
平均成绩=(select Avg(score) from sc where cid=cs.cid)
from course cs order by 平均成绩,cid desc

--20、查询课程名称为“语文”，且分数低于60的学生姓名和分数
select 姓名=sname,分数=score from sc
inner join student st on sc.sid = st.sid
inner join course cs on sc.cid = cs.cid
where cname='语文' and score<60

--21、查询所有学生的选课情况；
select 学号=sid,
选课数=(select count(*) from (select distinct cid from sc where sid=st.sid) as temp)
from student st
--这种通过分组方式的查询，不能查到一门都不选的学生
select sid,sname from student where sid in
(select sid from sc group by sid )

--22、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select st.sname,temp.score from student st
inner join
(select top 1 sid,score from sc where cid =
(select cid from course where tid = (select tid from teacher where tname='张三'))
order by score desc) as temp
on st.sid = temp.sid
-- left : 会把所有的学生都查出来只有一个有成绩，其他都 null
-- inner: 只查询到一条数据

select cid,cname,
该科最高学生=(select sname from student where sid in (select Top 1 sid from sc where cid =cs.cid order by score desc))
,成绩=(select top 1 score from sc where cid =cs.cid order by score desc)
from course cs inner join teacher tc on cs.tid = tc.tid
where tname ='张三'

--23、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select sid ,cid,score from sc sc1
where exists (
select * from sc sc2 where sc1.score = sc2.score and sc1.sid=sc2.sid and sc1.cid<>sc2.cid
)

--24、查询每门功成绩最好的前两名
select cid ,
top1 = (select top 1 sid from sc where cid = cs.cid order by score desc),
top2 = (select top 1 sid from (select top 2 sid,score from sc where cid = cs.cid order by score desc) as tmp order by score)
from course cs

select * from sc where cid=01 order by score desc

--25、查询全部学生都选修的课程的课程号和课程名
select cid,count(*) from sc group by cid having count(cid) = (select count(*) from student)

--26、查询两门以上不及格课程的同学的学号及其平均成绩
select sid,avg(score) as avgScore from sc
where (select count(*) from sc sc2 where sc.sid=sc2.sid and score<60)>=2
group by sid

select * from sc where sid in (04,06)

--27、向SC表中插入一些记录，这些记录要求符合以下条件：
--没有上过编号“03”课程的同学学号、'02'号课的平均成绩；
insert into sc (sid,cid,score)
select sid,'02',(select avg(score) from sc where cid = '02') from sc where
sid not in (select sid from sc where cid ='03')

--28、把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；
update sc set score = (select avg(score) from sc where sc.cid = sc.cid)
where cid in (select cid from course inner join teacher on course.tid = teacher.tid where tname = '张三')

--29、删除学习“张三”老师课的SC表记录；
delete from sc where cid in (
select cid from course t1 inner join teacher t2 on t1.tid = t2.tid
where tname = '张三')