《数据库原理及应用教程》考试系统

仅供学习参考，自己做出来的才是王道。

如有错误，请凭正确 截图私聊证实。结课后个人系统数据被清空，无法重新查改验证，请见谅~

一、记录增删改查初级

1.已知商品信息表有商品编号、商品名称、库存编号、供应商编号、产地、单价等字段，请写一个SQL语句添加一条记录，内容如下：
商品编号为1021，商品名称为4G DDR3内存，库存编号为1008，供应商编号为1002，产地为广州市，单价为199。

//运行后显示结果如下：
商品编号       商品名称       库存编号       供应商编号       产地       单价       均价
————————————————————————————————————————————————————————————————————————————————————————
1021          4G DDR3内存    1008           1002           广州市      199       NULL

//答案
go
insert into 商品信息 values(1021,'4G DDR3内存',1008,1002,'广州市'，199，NULL)

2.已知商品信息表有商品编号、商品名称、库存编号、供应商编号、产地、单价等字段，请写一个SQL语句删除产地含有 “州” 字的记录。

//运行后显示结果如下：
剩余记录数
——————————
15

//答案
go
delete 商品信息 where 产地 like '%州%'

3.已知教师表teacher(tno, tname, sex, birthday, prof, depart)，其中depart为教师所在院系，写一个SQL语句把 “计算机” 改为 “信息学院” 。

//运行结果如下：
tno		tname		depart
————————————————————————————
t01		余志利		信息学院
t02		高晓蓝		信息学院

//答案
go
update teacher set depart='信息学院' where depart='计算机'

4.已知教师表teacher(tno, tname, sex, birthday, prof, depart)，其中sex为性别，prof为职称，写一条更新语句将女教师的职称(prof) 置空（NULL）

//运行结果如下：
tno		tname		sex		birthday		prof		depart
—————————————————————————————————————————————————————————————————
t02		高晓蓝		女		...				NULL		...
t04		高芸		女		...				NULL		...
t07		黄欣茹		女		...				NULL		...
t09		壬意琛		女		...				NULL		...
t12		白枚		女		...				NULL		...
t13		李丽娜		女		...				NULL		...
t14		刘慧琴		女		...				NULL		...

//答案
go
update teacher set prof=NULL where sex='女'

二、表和列增删改初级

1.使用SQL语句创建教师表，表名为 jsb，有如下字段：教师号（tno），整型，主键；姓名（tname），最多允许输入10个汉字或20个字母，非空；性别（sex），只能输入1个汉字或2个字母。

//该表的内容如下：
tno		tname		sex
——————————————————————————
101		郑晓军		男
102		张三		男
103		赵兰		女

//答案
go
create table jsb (tno int primary key,tname varchar(20) not null,sex char(2))
go
insert into jsb values //values 可替换成 (tno,tname,sex)
(101,'郑晓军','男'),
(102,'张三','男'),
(103,'赵兰','女')

2.已知学生表xs 有学号、姓名、出生(CS)、毕业院校、成绩(CJ) 等字段，请写一个SQL语句将字段名 “CS” 改为 “出生” 。

go
exec sp_rename 'XS.CS','出生' //出生 可替换成 column)

3.已知学生表XS 有学号、姓名、毕业院校、成绩(CJ)等字段，请写一个SQL语句将删除成绩(CJ) 字段。

go
alter table XS drop column CJ
(select * from XS)

4.已知学生表XS 有学号、姓名、毕业院校、成绩(CJ)等字段，请写一个SQL语句将增加一个非Uni code备注型字段，字段名为 “BZ” （用大写字母表示）

go
alter table XS add BZ text //或 alter table XS add BZ text null
select * from XS

5.已知学生表XS 有学号、姓名、毕业院校、成绩(CJ)等字段，请写一个SQL语句将字段 “CJ” 改为 “成绩” 。

go
exec sp_rename 'XS.CJ','成绩' 

6.已知学生表XS 有学号、姓名、毕业院校、成绩(CJ) 等字段，请写一个SQL语句成绩(CJ) 字段类型改为浮点型(Float)。

go
alter table XS alter column CJ float //alter 可替代为 drop

7.使用SQL语句创建员工表，表名为 ygb，有如下字段：工号(eno)，整型，主键；姓名(ename)，最多允许输入8个汉字或16个字母，非空；性别(sex)，只能输入1个汉字或2个字母。

//该表的内容如下：
eno		ename		sex
——————————————————————————
101		陈军		女
102		王丰		男
103		张兰		女

//答案
go
create table ygb (eno int primary key,ename varchar(16) not null,sex char(2))
insert into ygb values (101,'陈军','女'),(102,'王丰','男'),(103,'张兰','女')

8.使用SQL语句创建学生表，表名为XS，该表含学号字段XH（整型，主键，用大写字母表示），姓名字段XM（最多允许输入6个汉字或12个字母，用大写字母表示）。

//该表内容如下：
XH		XM
——————————————
1		张三
2		李四
3		王五

//答案
go
create table XS (XH int primary key,XM varchar(12))
insert into XS values (1,'张三'),(2,'李四'),(3,'王五')

9.使用SQL语句创建借阅表，表名为JY，该表含书号字段SH 注意字段名大小写（字符型，最多允许输入7个汉字或14个字符），学号字段XH （字符型，最多允许输入5个汉字或11个字符），借阅日期字段JYRQ和归还时间字段GHSJ都是DateTime类型。

//添加如下内容（日期格式可能不同 ==注意字段名大小写）：
SH		XH				JYRQ			GHSJ
———————————————————————————————————————————————————
1		20120881101		2011-02-21		2013-04-16

//答案
go
create table JY (SH varchar(14),XH varchar(11),JYRQ DateTime,GHSJ DateTime)
insert into JY values(1,'20120881101','2011-02-21','2013-04-16')

10.为课程表（course）增加一个备注字段，字段名为bz (小写字母)，类型为非Uni code 备注 型。

//运行结果显示：
cno		cname		tno		bz

//答案
go
alter table course add bz text (null)

三、SQL查询中级

1.已知学生表 XS 有学号、姓名、毕业院校、成绩(CJ)等字段，请写一个SQL语句显示各毕业院校优秀学生（CJ>=80）人数。

//显示结果如下：
毕业院校			人数
————————————————————
福建工程学院		3
厦门华夏职业学院	1
厦门理工学院		3

//答案
select 毕业院校,count(毕业院校) as 人数 from XS where CJ>=80 group by 毕业院校

2.已知学生表 XS 有学号、姓名、毕业院校、成绩(CJ)、出生(CS) 等字段，请写一个SQL语句显示姓 “荣” 的学生姓名。

//结果显示如下：
姓名
————————
荣旭

//答案
select 姓名 from XS where 姓名 like '荣%'

3.已知学生表 XS 有学号、姓名、毕业院校 等字段，请写一个SQL语句显示毕业于 “厦门” 的学生姓名，并按姓名降序。

//显示如下：
姓名
————————
邹芳荣
张龙剑
张剑
叶荣林
李志恒
陈艳

//答案
select 姓名 from XS where 毕业院校 like '%厦门%' order by 姓名 desc

4.已知学生表 XS 有学号、姓名、毕业院校、成绩(CJ) 等字段，请写一个SQL语句显示成绩60分以下的人数。

//结果显示如下：
不及格人数
——————————
        3

//答案
select count(*) as 不及格人数 from XS where Cj<60

5.已知选修表 score (sno, cno, degree)，查询课程号(cno) 为 ‘c02’ 和 ‘c03’ 课程的选课人数。

//运行结果如下：
cno		选课人数
——————  ——————————
c02		4
c03		3

//答案
select cno, count(cno) as 选课人数 from score where cno='c02' or cno='c03' group by cno

6.已知学生表XS（学号、姓名、CS、毕业院校、CJ），请写一个SQL语句显示都有哪些毕业院校，一个院校有多个学生，只要显示一次，并按升序显示。

//结果显示如下：
毕业院校
————————
...
...
（11个）

//答案
select 毕业院校 from XS group by 毕业院校

7.已知选修表 score (sno, cno, degree)，查询课程号为 ‘c03’ 的最高分数(degree)。

//运行结果如下：
c03课程最高分
————————————
          85

//答案
select max(degree) as c03课程最高分 from score where cno='c03'

8.已知学生表XS 有学号、姓名、毕业院校、成绩(CJ) 、出生(CS) 等字段，请写一个SQL语句显示姓名中含有 “荣” 的学生姓名。

//结果显示如下：
姓名
————————
周荣通
邹艺荣
叶荣林
荣旭

//答案
select 姓名 from XS where 姓名 like '%荣%'

9.已知教师表 teacher(tno, tname, sex, birthday, prof, depart)，查询姓名(tname) 为2个汉字的教师工号(tno) 、姓名(tname) 、职称(prof) 和所在院系(depart) 等信息。

//运行结果如下：
tno		tname		prof		depart
——————————————————————————————————————————
t03		钱程		教授		金融
t04		高芸		副教授		...
......

//答案
select tno,tname,prof, depart from teacher where tname like '__'

10.已知学生表XS 有学号、毕业院校、成绩(CJ) 等字段，请写一个SQL语句显示所有参加考试学生的平均成绩（保留4位小数）。

//结果显示如下：
平均成绩
——————————
76.0625

//答案
select cast(avg(CJ*1.0) as decimal(6.4)) as 平均成绩 from XS

11.已知学生表XS 有学号、姓名、毕业院校、成绩(CJ) 等字段，请写一个SQL语句显示没有成绩的学生姓名，并按姓名升序显示。

//结果显示如下：
姓名
————————
黄忠顺
王兆钦
张龙剑
左见霞

//答案
select 姓名 from XS where CJ is null order by 姓名 asc

12.已知教师表 teacher(tno, tname, sex, birthday, prof, depart)，查所有姓郑的教师教师号(tno)、姓名(tname)、职称(prof)。

//运行结果如下：
tno		tname		prof
————————————————————————————
tll		郑志强		教授

//答案
select tno,tname,prof from teacher where tname like '%郑%'

13.已知选修表score(sno, cno, degree)，查询选修了课程号(cno) 为 ‘c02’ 的学生成绩(degree) 信息查询结果按成绩的升序排列。

//运行结果如下：
sno		degree
————————————————
s01001	65
s02001	70
s03001	78
s01002	89

//答案
select sno,degree from score where cno='c02' order by degree

14.已知选修表score(sno, cno, degree)，查询学号(sno) 为==‘s01001’ 和 ‘s03001’==学生的选课门数。

//运行结果如下：
sno		选课门数
——————————————————
s01001			4
s03001			7

//答案
select sno,count(sno) as 选课门数 from score where sno='s01001' or sno='s03001' group by sno

15.已知学生表XS 有学号、姓名、毕业院校等字段，请写一个SQL语句显示毕业院校含 “职业” 2字的学生学号，并按升序显示。

//结果显示如下：
学号
——————————————
20100881207
20100881208
20100881209
20100881220

//答案
select 学号 from XS where 毕业院校 like '%职业%'

四、记录增删改中级

1.已知学生表XS 有学号、姓名、毕业院校、成绩(CJ) 等字段，请写一个SQL语句将不及格学生的学号和姓名按学号升序写入补考名单（该表已存在不能再创建）中。

//写入内容如下：
学号				姓名
————————————————————————————
20100881205			王聪慧
20100881206			游连桦
20100881211			邹艺荣

//答案
go
insert into 补考名单 select 学号,姓名 from XS where Cj<60 order by 学号 asc 

2.现要对学生表（xs）的成绩（字段名为CJ）进行加分，加分的规则是成绩除3余0加1分，成绩除3余1加4分，成绩除3余2加7分。

//已知学生表（xs）加分前后成绩如下：
		       加分前成绩如下						                   加分后成绩如下
————————————————————————————————————————————————————————————————————————————————————————————————————
学号				姓名				CJ				学号				姓名				CJ
20100881201			谢文娟				67				...					...					71
......

//答案
update xs set CJ=
case CJ%3
	when 0 then CJ+1
	when 1 then CJ+4
	when 2 then CJ+7
end

//类似题：加分的规则是成绩为偶数的+1分，奇数+2分。加分前后成绩（同上）
update xs set cj=
case cj%2
	when 0 then CJ+1
	when 1 then CJ+2
end

3.已知商品信息表含有商品编号、商品名称、库存编号、供应商编号、产地、单价等字段，请写一个SQL语句将产地不含 “京” 字的所有商品价格打八折且零头忽略不计处理。

//执行后结果（其中产地含 “京” 字的所有商品价格不变）如下：
商品名称						产地		单价
————————————————————————————————————————————
Intel D915GVWB主板			广州市		690
Maxtor 40G硬盘				南京市	   514.25

//答案
update 商品信息 set 单价=floor(单价