数据库实验三（SQL Server & SSMS）

前言

• 所有数据和sql都是博主本人所写，仅供大家参考。
• 本实验不算难，建议先独自动手写sql，遇到困难时可以将我的sql作为参考。

实验内容

运行环境

• SQL Server 2022
• SQL Server Management Studio Management Studio 19

本实验的全部SQL脚本

insert into S values('412109002126','xiaojing','男','2000-05-21','cs','上海');
insert into S values('131312321111','张三','男','2000-02-08','cs','上海');
insert into S values('256345611138','留香','女','2000-10-21','计算机学院','上海');

insert into C values('100002', '高等数学1','0000',6);
insert into C values('500001', '大学物理','0000',5);

insert into SC values('123131231111','100002', 100);
insert into SC values('131312321111','100002', 90);
insert into SC values('256345611138','100002', 80);
insert into SC values('412109002126','100002', 70);
insert into SC values('41210900286','100002', 60);
insert into SC values('419109070212','100002', 50);
insert into SC values('419109070226','100002', 40);
insert into SC values('420109070226','100002', 30);
insert into SC values('420109070426','100002', 20);
insert into SC values('421109070126','100002', 10);
insert into SC values('420109070226','500001', 80);

insert into SC values('420109070226','0000', 80);
insert into SC values('420109070226','0001', 80);
insert into SC values('420109070226','500012', 80);

-- (1) 查询每一门的间接先修课的信息
select c2.* from c c1, c c2 where c1.cpno = c2.cno and c1.cpno is not null;

-- (2) 查询所有学生的选课情况，没选课的学生也要显示。
select s.sname, sc.cno from sc full join s on sc.sno = s.sno;

-- (3) 查询选修了“500012”课程且成绩在80~90之间的学生信息。
select s.sno, s.sname, s.saddress from s, sc
where s.sno = sc.sno and sc.cno = '500012' and sc.score between 80 and 90;

-- (4) 查询每个学生的学号、姓名、选修课名称及成绩
select s.sno, s.sname, c.cname, sc.score from sc, s, c where sc.sno = s.sno and sc.cno = c.cno;

-- (5) 查询女生人数最多的两个学院，查询结果显示：学院、女生人数。
select top 2 dept, persons from (
	select sdept as dept, count(s.sno) as persons from s group by sdept
) tmp order by persons desc;

-- (6) 查询最低分低于50分的学生信息
select s.sno, s.sname from sc, s where s.sno = sc.sno and sc.score < 50;

-- (7) 查询与“张三”同岁的“计算机学院”的学生信息
select s1.sno, s1.sname from s s1
where s1.sdept = '计算机学院' and datediff(yyyy, s1.Sbirthday,getdate()) = (
	select datediff(yyyy,s2.Sbirthday,getdate()) from s s2 where s2.sname = '张三'
);

-- (8) 查找“高等数学1”成绩最好的10个学生的信息。
select top 10 sno, sname, score from (
	select s.sno, s.sname, sc.score from sc, c, s
	where sc.cno = c.cno and c.cname = '高等数学1' and s.sno = sc.sno
) tmp order by score desc;

-- (9) 找出每个学生超过他的选修课程平均成绩的课程号
select sc.sno, cno from sc,(
	select sno, avg(score) avg_score from sc group by sno
) tmp where  score > avg_score and sc.sno = tmp.sno;

-- (10) 查询其它学院比“计算机学院”的学生年龄都大的学生信息
select sno, sname, sdept from s where s.sdept != '计算机学院' and s.Sbirthday <= ALL(
	select tmp.sbirthday from s tmp where tmp.Sdept = '计算机学院'
);

-- (11) 查询没有选课的学生信息
select * from s where s.sno not in (
	select distinct sno from sc where cno is not null
);

-- (12) 查询至少有两门课超过85分的学生的基本信息
select * from s, (
	select sno, sum(case when score > 85 then 1 else 0 end) as cnt from sc group by sno
) tmp where s.sno = tmp.sno and cnt >= 2;

-- (13) 查询只被一个学生选修了的课程信息
select sc.cno from sc, (
	select count(sno) cnt, cno from sc group by cno
) tmp where 1 = tmp.cnt and sc.cno = tmp.cno;

-- (14) 查询所有没有选修“500001”课程的学生信息 ( EXISTS练习)
select * from s where not exists (
	select * from sc where sc.sno = s.sno and sc.cno = 500001
);

-- (15) 查询选修了全部课程的学生信息
select * from s where not exists (
	select * from c where not exists(
		select * from sc where sc.cno = c.cno and sc.sno = s.sno
	)
)

--(16) 查询至少选修了“李勇”选修的所有课程的学生信息
select * from sc x where not exists (
	select * from sc y where y.sno = (
		select sno from s where s.sname = '李勇'
	) and not exists (
		select * from sc z where z.cno = y.cno and z.sno = x.sno
	)
)