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

前言

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

纠错

第十四题有误，已经修改。

实验内容

运行环境

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

本实验的全部SQL脚本

--（1）向数据库MyStudent的三个表S(学生表),C（课程表）,SC（选课表），加入数据。
create table S (
	Sno char(12) primary key,
	Sname char(20) not null,
	Ssex char(2) null,
	Sbirthday datetime null,
	Sdept char(40) null,
	Saddress char(50) null
);

create table C (
	Cno char(6) primary key,
	Cname char(30) not null,
	Cpno char(6) null,
	Ccredit int null
);

create table SC (
	Sno char(12) foreign key references S(Sno),
	Cno char(6) foreign key references C(Cno),
	Score int null
);

insert into S values('420109070226','小张','男','2002-04-27','cs','江西');
insert into S values('420109070426','小明','女','1987-05-17','ms','河北');
insert into S values('421109070126','小刚','男','1992-10-01','cs','北京');
insert into S values('419109070226','丽丽','女','2002-01-21','ds','湖南');
insert into S values('419109070212','金刚','男','1988-01-21','ds','上海');
insert into S values('421109070123','阿发','女','1992-10-01','会计学院','北京');
insert into S values('123131231111','顺丰','女','2002-01-21','土地学院','湖南');
insert into S values('412109011231','鸡兔','女','1988-01-21','英语学院','上海');
insert into S values('41210900286','小狗','男','1988-01-21','ds','上海');

insert into C values('0000','思想品德',null,4);
insert into C values('0001','高数','0000',5);
insert into C values('0011','数据结构','0001',4);
insert into C values('0009','Java','0011',3);
insert into C values('0008','JavaWeb','0009',5);
insert into C values('500004','数值分析','0001',4);
insert into C values('500012', '大学英语','0000',4);

insert into SC values('420109070226','0009', 100);
insert into SC values('420109070426','0000', 90);
insert into SC values('421109070126','0008', 79);
insert into SC values('419109070226','0011', 84);
insert into SC values('41210900286','0011', 90);
insert into SC values('421109070126','500004', 80);
insert into SC values('420109070226','500004', 90);
insert into SC values('420109070226','0000', 90);
insert into SC values('420109070226','0008', 90);
insert into SC values('421109070126','0000', 40);
insert into SC values('41210900286','0009', 20);
insert into SC values('421109070126','0009', 58);
insert into SC values('421109070126','500012', 90);

-- (2) 查询学生的姓名、学院、年龄等信息
select Sname, Sdept, datediff(yyyy,Sbirthday,getdate()) as age from S;

-- (3) 查询学分4分，先修课为空的课程。
select cname from c where ccredit = 4 and cpno is null;

-- (4) 查询在1986~1988年出生的男生的学生信息。
select * from s where ssex = '男' and year(Sbirthday) between 1986 and 1988;

-- (5) 查询“土地学院”、“会计学院”、“英语学院”的女生信息。
select * from S where ssex = '女' and sdept in ('英语学院','会计学院','土地学院');

-- (6)显示学校所有的学院信息。
select sdept, count(*) as 学院人数 from S group by sdept;

-- (7) C表中查询前5门课的信息。
select top 5 * from C;

-- (8) 显示学分最高的前20％的课程。
select top 20 percent * from C order by ccredit desc;

-- (9)查询10月份出生的学生。
select * from S where month(Sbirthday) = 10;

-- (10) 查询学号以“41210901”开头的学生信息。
select * from S where sno like '41210901%';

-- (11) 查询选修了课程的学生人数。
select count(*) from S where sno in (
	select sno from sc
);

-- (12) 统计“500004”课程的学生的平均成绩，最高成绩。
select cno, avg(score) as avg_score, max(score) as max_age
from sc group by cno having cno = '500004';

-- (13) 计算41210900286学号的学生总分，平均分。
select sum(score) as sum_score, avg(score) as avg_score
from sc group by sno having sno = '41210900286';

-- (14) 统计女生人数低于100人的学院。
select distinct sdept from s where 100 > (
	select count(*) from s as s_t where s_t.ssex = '女' and s.sdept = s_t.sdept
);

-- 错误版本
-- select distinct sdept from s where 100 > (
--	select count(*) from s as s_t where s_t.ssex = '女' and sdept = s_t.sdept
-- );


-- (15) 统计各课程的选课人数。
select sc.cno, count(1) as number
from sc group by sc.cno;

-- (16) 查询选修了3门课以上的学生学号。
select sno from sc group by sno having count(1) > 3;

-- (17) 查询不及格人数最多的两门课的课程号和不及格人数 。
select top 2 tmp.cno,tmp.不及格人数 from (
	select cno, sum(case when score < 60 then 1 else 0 end) as '不及格人数' 
	from sc group by cno
) tmp where tmp.不及格人数 > 0 order by tmp.不及格人数 desc;

-- (18) 显示两门课（包括）以上不及格的学生学号。
select distinct tmp.sno from (
	select sno, sum(case when score < 60 then 1 else 0 end) as '不及格人数' 
	from sc group by sno
) tmp where tmp.不及格人数 >= 2;

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

-- (20) 查询所有学生的选课情况。
select s.sname, sc.cno, c.cname from sc, s, c
where sc.sno = s.sno and c.cno = sc.cno order by s.sname;

-- (21) 查询所有课程的被选情况。
select c.cno, c.cname, s.sname from sc, s, c
where sc.sno = s.sno and c.cno = sc.cno order by c.cno;

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

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