SQL Server练习题

许多同学在学习数据库初期，在练习时苦于没有练习资源，本文将举出一些例子供大家学习参考，所有例子的SQL查询语句均在文章末尾分享给大家，并已经过测试，可正确执行。欢迎大家提出更新更高效的解决办法。

设计要求及内容：

一、创建数据库，名字为exam（创建方法和数据库参数自定）

二、在exam数据库中用SQL语句创建以下三个表。

stu

列名	语义说明	数据类型	长度	可否为空	默认值	约束与检查
No	学号	char	8	否	无	主键
Name	姓名	char	8	否	无
Major	专业	varchar	20	否	无
Sex	性别（男=1，女=0）	bit		否	1
Birth	出生日期	smalldatetime		否	无
Total	总学分	smallint		否	0

cs

列名	语义说明	数据类型	长度	可否为空	默认值	约束与检查
cid	课程号	char	3	否	无	主键
cname	课程名	char	20	否	无
credit	学分	tinyint		否	0	0≤学分≤7

sele

列名	语义说明	数据类型	长度	可否为空	默认值	约束与检查
sid	学号	char	8	否	无	外键（参照stu表的学号）
cid	课程号	char	3	否	无	外键（参考cs表的课程号）
sgrade	成绩	tinyint		是	0	0≤成绩≤100

三、插入一下三个表内的信息：

stu

No	Name	Major	Sex	Birth	Total
20192101	王林	计算机	1	1983-1-23	40
20192102	吴荣华	计算机	0	1984-3-28	44
20192103	张强	电子	1	1981-11-19	0
20192110	王向荣	电子	1	1980-12-9	50
20192221	李丽	电子	0	1982-7-30	0
20192111	刘明	自控	1	1982-10-18	38
20192130	叶凡	数学	1	1983-11-18	46

cs

cid	cname	credit
101	计算机基础	3
102	C语言	5
103	数据库	4
201	离散数学	4
202	计算机网络	4
301	软件工程	4

sele

sid	cid	sgrade
20192101	101	80
20192101	102	89
20192101	103	78
20192102	101	57
20192102	102	67
20192103	103	90
20192110	101	85
20192110	102	91
20192221	101	69
20192130	101	78
20192130	103	74

四、利用SQL语句完成以下查询

1.查询所有学生的学号、姓名和总学分，结果中各列的标题分别指定为学号，名字和总学分。

2.查询专业为“计算机”且总学分为0的学生情况。

3.查询年龄最小的前三个学生的姓名、专业和年龄。

4.统计专业为“计算机”的学生的总人数。

5.求选修了“101”课程的学生的人数。

6.统计各个专业的学生数。

7.查询平均成绩在80分以上的学生的学号、姓名和平均成绩。

8.查找学号为“20192101”的同学的“计算机基础”课程的成绩。

9.查询所以选课的学生和学号。

10.查询学生的信息，并添加等级：成绩≥90的为优秀，80~89为优良，70~79为中等，60~69为及格，否则为不及格。

11.查询两门及其以上课程成绩小于80的同学的学号、姓名及其平均成绩。

五、测试数据：

----------------------------创建三个表---------------------------------
create table stu(
No char(8) primary key,
Name char(8) not null,
Major varchar(20) not null,
Sex bit default 1,
Birth smalldatetime not null,
Total smallint default 0
)
create table cs(
cid char(3) primary key,
cname char(20) not null,
credit tinyint default 0 check(credit>=0 and credit<=7)
)
create table sele(
sid char(8) not null,
cid char(3) not null,
sgrade tinyint default 0 check(sgrade>=0 and sgrade<=100),
primary key (sid,cid),
foreign key(sid) references stu(No),
foreign key(cid) references cs(cid)
)
--------------------------------------------------------------------------

-------------------插入测试数据----------------------------------------------------
insert into stu(No,Name,Sex,Birth,Major,Total) values ('20192101','王林','1','1983-1-23','计算机','40')
insert into stu(No,Name,Sex,Birth,Major,Total) values ('20182102','吴荣华','0','1984-3-28','计算机','44')
insert into stu(No,Name,Sex,Birth,Major,Total) values ('20192103','张强','1','1981-11-19','电子','0')
insert into stu(No,Name,Sex,Birth,Major,Total) values ('20192110','王向荣','1','1980-12-9','电子','50')
insert into stu(No,Name,Sex,Birth,Major,Total) values ('20192221','李丽','0','1982-7-30','电子','0')
insert into stu(No,Name,Sex,Birth,Major,Total) values ('20192111','刘明','1','1982-10-18','自控','38')
insert into stu(No,Name,Sex,Birth,Major,Total) values ('20192130','叶凡','1','1983-11-18','数学','46')

insert into cs values('101','计算机基础','3')
insert into cs values('102','C语言','5')
insert into cs values('103','数据库','4')
insert into cs values('201','离散数学','4')
insert into cs values('202','计算机网络','4')
insert into cs values('301','软件工程','4')

insert into sele values('20192101','101','80')
insert into sele values('20192101','102','89')
insert into sele values('20192101','103','78')
insert into sele values('20192102','101','57')
insert into sele values('20192102','102','67')
insert into sele values('20192103','103','90')
insert into sele values('20192110','101','85')
insert into sele values('20192110','102','91')
insert into sele values('20192221','101','69')
insert into sele values('20192130','101','78')
insert into sele values('20192130','103','74')
-----------------------------------------------------------------------------------

 六、参考语句：

--1.查询所有学生的学号、姓名和总学分，结果中各列的标题分别指定为学号，名字和总学分。
select No 学号,Name 名字,Total 总学分 from stu

--2.查询专业为“计算机”且总学分为0的学生情况。
select * from stu
where Major='计算机' and Total=0

--3.查询年龄最小的前三个学生的姓名、专业和年龄。
---方法一求年龄
select top 3 DATEDIFF(yy,birth,GETDATE()) AS agetop,Name,Major from stu
order by agetop desc
---方法二求年龄
select top 3 2021-year(Birth) AS agetop,Name,Major from stu
order by agetop desc

--4.统计专业为“计算机”的学生的总人数。
select count(*) 计算机专业人数 from stu
where Major='计算机'
group by Major

--5.求选修了“101”课程的学生的人数。
select count(sid) 选课人数 from sele
where cid='101'

--6.统计各个专业的学生数。
select Major,count(*) from stu
group by Major

--7.查询平均成绩在80分以上的学生的学号、姓名和平均成绩。
select No,name,AVG(sgrade) 平均成绩 from stu st
join sele se on st.No=se.sid
group by No,name
having AVG(sgrade)>80

--8.查找学号为“20192101”的同学的“计算机基础”课程的成绩。
select sgrade from sele s
join cs c on s.cid=c.cid
where cname='计算机基础'and sid='20192101'

--9.查询所以选课的学生和学号。
select No,Name from stu
where No in (select distinct sid from sele)

--10.查询学生的信息，并添加等级：成绩≥90的为优秀，80~89为优良，70~79为中等，60~69为及格，否则为不及格。
select No,Name,Major,Sex,Total,cid,sgrade,case
when sgrade >=90 then '优秀'
when sgrade between 80 and 89 then '优良'
when sgrade between 70 and 79 then '中等'
when sgrade between 60 and 69 then '及格'
when sgrade <60 then '不及格'
end as 考试情况
from stu st
join sele se on st.No=se.sid

--11.查询两门及其以上课程成绩小于80的同学的学号、姓名及其平均成绩。
select No,Name,AVG(sgrade) from stu st
join sele se on st.No=se.sid
group by No,Name
having No in (select sid from sele
where sgrade<80 
group by sid 
having COUNT(sgrade)>=2)