SQL_Server SQL实验与练习题 答案

实验1 数据库操作

1.创建数据库:

操作1.1:创建一个test数据库,其主数据文件逻辑名test_data,物理文件名test_data.mdf,初始大小10MB,最大尺寸为无限大,增长速度1MB;数据库日志文件逻辑名称为test_log,物理文件名为test_log.ldf,初始大小为1MB,最大尺寸为5MB,增长速度为10%。

CREATE DATABASE [test]

ON  PRIMARY

(

NAME = N’test_data’,

FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_data.mdf’ ,

SIZE = 10240KB ,

FILEGROWTH = 1024KB

)

LOG ON

(

NAME = N’test_log’,

FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test_log.ldf’ ,

SIZE = 1024KB ,

MAXSIZE = 5120KB ,

FILEGROWTH = 10%

)

GO

2.查看数据库属性:

操作1.2:使用T-SQL语句查看数据库test属性

exec sp_helpdb test

3.删除数据库:

操作1.3:使用T-SQL语句删除数据库test

drop database test

实验2 表操作

1.创建表:

操作2.1:创建学生表:

表名:student   说明:学生基本信息表
属性列 数据类型 长度 空值 列约束 说明
st_id nVarChar 9 Not Null PK 学生学号
st_nm nVarChar 8 Not Null   学生姓名
st_sex nVarChar 2 Null   学生性别
st_birth datetime   Null   出生日期
st_score int   Null   入学成绩
st_date datetime   Null   入学日期
st_from nChar 20 Null   学生来源
st_dpid nVarChar 2 Null   所在系编号
st_mnt tinyint   Null   学生职务

create table student

(

st_id nvarchar(9) not null primary key,

st_nm nvarchar(8) not null,

st_sex nvarchar(2),

st_birth datetime,

st_score int,

st_date datetime,

st_from nchar(20),

st_dpid nvarchar(2),

st_mnt tinyint

)

 

操作2.2:创建课程信息表:

表名:couse   说明:课程信息表
属性列 数据类型 长度 空值 列约束 说明
cs_id nVarChar 4 Not Null PK 课程编号
cs_nm nVarChar 20 Not Null   课程名称
cs_tm int   Null   课程学时
cs_sc int   Null   课程学分

create table couse

(

cs_id nvarchar(4) not null primary key,

cs_nm nvarchar(20) not null,

cs_tm int,

cs_sc int

)

 

操作2.3:创建选课表:

表名:slt_couse   说明:选课表
属性列 数据类型 长度 空值 列约束 说明
cs_id nVarChar 4 Not Null FK 课程编号
st_id nVarChar 9 Not Null FK 学生编号
score int   Null   课程成绩
sltdate datetime   Null   选课日期

create table slt_couse

(

cs_id nvarchar(4) not null foreign key references couse(cs_id),

st_id nvarchar(9) not null foreign key references student(st_id),

score int,

sltdate datetime

)

 

操作2.4:创建院系信息表:

表名:dept   说明:院系信息表
属性列 数据类型 长度 空值 列约束 说明
dp_id nVarChar 2 Not Null   系编号
dp_nm nVarChar 20 Not Null   院系名称
dp_drt nVarChar 8 Null   院系主任
dt_tel nVarChar 12 Null   联系电话

create table dept

(

dp_id nvarchar(2) not null,

dp_mn nvarchar(20) not null,

dp_drt nvarchar(8),

dt_tel nvarchar(12)

)

2.修改表结构:

(1)向表中添加列:

操作2.5:为“dept”表添加“dp_count”列(数据类型为nvarchar,长度为3,允许为空)

alter table dept

add dp_count nvarchar(3)

(2)修改列数据类型:

操作2.6:修改“dept”表的“dp_count”列数据类型为int

alter table dept

alter column dp_count

int

(3)删除表中指定列:

操作2.7:删除“dept”表的“dp_count”列

alter table dept

drop column dp_count

3.删除表

操作2.8:删除“dept”表

drop table dept

4.向表中输入数据记录

操作2.9:分别向“student”表、“couse”表、“slt_couse”表、“dept”表中输入数据记录

 

实验3 数据完整性

1.空值约束( NULL )

操作3.1:将student表中的st_sex列属性更改为NOT NULL

alter table student

alter column st_sex nvarchar(2) not null

2.默认值约束( DEFAULT )

操作3.2:将student表中的st_from列默认值设置为“陕西省”

alter table student

add default ‘陕西省’ for st_from

 

3.默认值对象

操作3.3:创建默认值对象df_today为当前日期,并将其绑定到slt_couse表中的sltdate列,然后取消绑定,最后删除默认值对象df_today。

create default df_today as getdate()

go

exec sp_bindefault df_today, ‘slt_couse.sltdate’

go

exec sp_unbindefault ‘slt_couse.sltdate’

go

drop default df_today

go

4.检查约束( CHECK )

操作3.4:将slt_couse表中的score列的检查约束设置为>=0且<=100

alter table slt_couse

add check (score >= 0 and score <= 100)

5.规则约束对象

操作3.5:创建规则约束对象rl_sex,用于检查性别的取值仅限于“男”和“女”,并将其绑定到student表中的st_sex列,然后取消绑定,最后删除规则约束对象rl_sex。

create rule r1_sex

as @age in(‘男’, ‘女’)

go

exec sp_bindrule r1_sex, ‘student.st_sex’

go

exec sp_unbindrule ‘student.st_sex’

go

drop rule r1_sex

go

6.主键

操作3.6:将dept表中的dp_id列设置为主键

alter table dept

add primary key (dp_id)

7.唯一性约束( UNIQUE )

操作3.7:将dept表中的dp_nm列设置为唯一性约束

alter table dept

add constraint uq_dpmn

unique (dp_mn)

8.标识列

操作3.8:向slt_couse表中添加标识列id,第1行默认值为1,相邻两个标识列间的增量为1

alter table slt_couse

add id int identity(1, 1) not null

9.外键( FOREIGN KEY )

操作3.9:被参照表为dept,参照表为student

alter table student

add constraint rk_stu_dept

foreign key (st_dpid) references dept(dp_id)

 

 

实验4 数据更新

1.表中插入数据

操作4.1:向dept表插入一条记录,系号11,系名自动控制系,系主任为李其余,电话81234567

insert into dept

values(11, ‘自动控制系’, ‘李其余’, 81234567)

操作4.2:向student表插入一条记录,学号070201001,姓名为王小五,性别为男,出生日期为1990年9月9日,系号为11,其余字段为NULL或默认值

insert into student

(st_id, st_nm, st_sex, st_birth, st_dpid)

values

(‘070201001’, ‘王小五’, ‘男’, 1990-9-9, 11)

操作4.3:向couse表插入一条记录,课程号1234,课程名为操作系统,其余字段为NULL或默认值

insert into couse

(cs_id, cs_nm)

values

(‘1234’, ‘操作系统’)

操作4.4:向slt_couse表插入一条记录,课程号1234,学名070201001,其余字段为NULL或默认值

insert into slt_couse

(cs_id, st_id)

values

(‘1234’, ‘070201001’)

2.修改表中数据

操作4.5:修改student表记录,将王小五的入学成绩改为88

update student set

st_score = 88 where st_nm = ‘王小五’

操作4.6:修改couse表记录,将所有记录的学分改为4,学时改为64

update couse set

cs_sc = 4 , cs_tm = 64

操作4.7:修改slt_couse表记录,将课程号为1234,学名为070201001的记录的成绩改为77

update slt_couse set

score = 77 where (cs_id = ‘1234’ and st_id = ‘070201001’)

3.删除表中数据

操作4.8:删除slt_couse表记录,将课程号为1234,学名为070201001的记录删除

delete slt_couse

where (cs_id = ‘1234’ and st_id = ‘070201001’)

操作4.9:删除couse表记录,将课程号为1234的记录删除

delete couse

where cs_id = ‘1234’

 

实验5 数据查询(1)——简单查询

(1)查询表中所有的列

操作5.1:查询所有系的信息

select * from dept

(2)查询表中指定列的信息

操作5.2:查询所有的课程号与课程名称

select cs_id, cs_nm from couse

(3)在查询列表中使用列表达式

操作5.3:在查询student表时使用列表达式:入学成绩+400

select st_score + 400 from student

(4)重新命名查询结果

操作5.4:使用AS关键字为dept表中属性指定列名:系号、系名、系主任、联系电话

select dp_id as 系号, dp_mn as 系名, dp_drt as 系主任, dt_tel as 联系电话

from dept

操作5.5:使用”=”号为couse表中属性指定列名:课程号、课程名、学时(=cs_sc*16)、学分

select 课程号= cs_id, 课程名= cs_nm, 学时= cs_sc * 16, 学分= cs_sc  from couse

(5)增加说明列

操作5.6:查询dept表的系号、系名和系主任,向查询结果中插入说明列:系号、系名和系主任

select ‘系号’, dp_id, ‘系名’,dp_mn, ‘系主任’,dp_drt from dept

(6)查询列表中使用系统函数

操作5.7:显示所有学生的学号、姓名、性别和入学年份

select st_id, st_nm, st_sex, datepart(yy, st_birth) as 入学年份from student

操作5.8:显示所有学生学号、姓名、性别和班级(学号前6位)

select st_id, st_nm, st_sex, left(st_id, 6) as 班级from student

(7)消除查询结果中的重复项

操作5.9:显示所有学生班级

select distinct left(st_id, 6) as 班级from student

(8)取得查询结果的部分行集

操作5.10:显示前5条学生记录信息

select top 5 * from student

操作5.11:显示前25%条学生记录信息

select top 25 percent * from student

操作5.12:显示前n条学生记录信息,n为局部变量

 

实验6 数据查询(2)——条件查询

1.使用关系表达式表示查询条件

操作6.1:查询dept表中系号为11的院系信息

select dp_drt from dept where dp_id = 11

操作6.2:查询student表中11系的学生学号、姓名、性别和所在系编号

select st_id, st_nm, st_sex, st_dpid

from student

where st_dpid = 11

操作6.3:查询student表中2008年及以后入学的学生信息

select * from student

where st_date >= 2008

操作6.4:在查询student表080808班学生的学号、姓名、性别和入学成绩

select st_id, st_nm, st_sex, st_score

from student

where left(st_id, 6) = 080808

2.使用逻辑表达式表示查询条件

操作6.5:查询student表中非11系的学生信息

select * from student

where st_dpid != 11

操作6.6:查询选修了1002号课程且成绩在60以下的学生学号

select st_id from slt_couse

where (cs_id = 1002 and score < 60)

操作6.7:查询2007年入学的11系所有男生信息

select * from student

where (st_date = 2007 and st_dpid = 11)

操作6.8:查询11系和12系的学生信息

select * from student

where st_dpid between 11 and 12

操作6.9:查询11系和12系所有2007年入学的学生信息

select * from student

where (st_dpid between 11 and 12) and st_date = 2007

3.使用LIKE关键字进行模糊查询

操作6.10:查询所有“计算机”开头的课程信息

select * from couse

where left(cs_nm, 3) = ‘计算机’

操作6.11:查询所有由三个字组成的“王”姓学生信息

select * from student

where left(st_nm, 1) = ‘王’ and len(st_nm) = 3

操作6.12:查询所有课程名中包含“信息”的课程信息

select * from couse

where cs_nm like ‘%信息%’

操作6.13:查询学生姓名介于王姓到张姓的信息

select * from couse

where cs_nm between ‘王’ and ‘张’

4.使用BETWEEN…AND关键字进行查询

操作6.14:查询在1989.7.1到1990.6.30之间出生的学生信息

select * from student

where st_birth between 1989-7-1 and 1990-6-30

操作6.15:查询选修了1001号课程且成绩在60到80之间的学生选课信息

select * from slt_couse

where cs_id = ‘1001’ and score between 60 and 80

5.使用IN关键字进行查询

操作6.16:查询11系、12系、13系的学生信息

select * from student

where st_dpid in (11, 12, 13)

操作6.17:查询所有张,王,李,赵姓的学生的学号、姓名、性别

select st_id, st_nm, st_sex from student

where left(st_nm, 1) in (‘张’, ‘王’, ‘李’, ‘赵’)

6.使用[NOT] NULL关键字进行查询

操作6.18:查询所有生源为非空的学生信息

select * from student

where st_mnt is not null

操作6.19:查询选修了1001号课程且成绩为空的学生选课信息

select * from slt_couse

where cs_id = ‘1001’ and score is null

 

实验7 数据查询(3)——查询排序与查询结果存储

操作7.1:查询课程信息,按课程名称降序排序

select * from couse

order by cs_nm desc

操作7.2:查询选修了1001号课程成绩非空的学生学号和成绩,并按成绩降序排序

select cs_id, score from slt_couse

where cs_id = ‘1001’

order by score desc

操作7.3:查询11系学生学号、姓名和年龄,按年龄升序排序

select st_id, st_nm,  datepart(yy, getdate())-datepart(yy, st_birth) as age from student

order by age asc

操作7.4:查询学生信息,按姓名升序排序,再按系号降序排序

select * from student

order by st_nm asc, st_dpid desc

操作7.5:创建学生表副本student01,仅保留学生学号、姓名和性别

select st_id, st_nm, st_sex

into student01

from student

操作7.6:查询陕西籍学生,将结果保存在新表st_shanxi

select *

into st_shanxi

from student

where st_from = ‘陕西省’

操作7.7:查询选修了1001号课程学生的选课信息,按学号升序排序,将结果保存在新表slt1001

select *

into slt1001

from slt_couse

where cs_id = ‘1001’

order by cs_id

操作7.8:用局部变量@stage保存学生张三的年龄

declare @stage int

select @stage = datepart(yy, getdate())-datepart(yy, st_birth)

from student

where st_nm = ‘张三’

操作7.9:用局部变量@name和@stscore保存070101班按学号排序后最后一个学生的姓名和入学成绩

declare @name nvarchar(8), @stscore int

select @name = st_nm, @stscore = st_score

from student

where left(st_id, 6) = ‘070101’

order by st_id

实验8 数据查询(4)——查询统计与汇总

操作8.1:查询课程总数

select count(*)

from couse

操作8.2:查询选修1001号课程的学生人数

select count(*)

from slt_couse

where cs_id = ‘1001’

操作8.3:查询被选修课程的数量

select count(*)

from slt_couse

group by cs_id

操作8.4:查询070101班学生的平均入学成绩

select AVG(st_score)

from student

where LEFT(st_id, 6)=’070101′

操作8.5:查询070101001号学生选修课程的数量、总分以及平均分

select count(*), sum(score), avg(score)

from slt_couse

where st_id = ‘070101001’

操作8.6:查询选修1001号课程的学生人数、最高分、最低分和平均分

select count(*), max(score), min(score), avg(score)

from slt_couse

where cs_id = ‘1001’

操作8.7:求各个课程号和相应的选课人数

select cs_id, count(*)

from slt_couse

group by cs_id

操作8.8:统计各班人数

select left(st_id, 6), count(*)

from student

group by left(st_id, 6)

操作8.9:依次按班级、系号对学生进行分类统计人数、入学平均分

select left(st_id, 6), count(*), avg(st_score)

from student

group by left(st_id, 6)

select st_dpid, count(*), avg(st_score)

from student

group by st_dpid

操作8.10:查询选修了均分在75以上的课程号及均分

select cs_id, avg(score)

from slt_couse

group by cs_id

having avg(score) > 75

操作8.11:查询选修了2门以上课程的学生学号

select st_id

from slt_couse

group by st_id

having count(*) > 2

操作8.12:明细汇总年龄<20的学生,并汇总学生数量、平均年龄

操作8.13:按班级明细汇总成绩<85分的学生,汇总学生数、均分

select left(st_id, 6), count(*), avg(st_score)

from student

where st_score < 85

group by left(st_id, 6)

 

实验9 数据查询(5)——连接查询

操作9.1:用SQL Server形式连接查询学生学号、姓名、性别及其所选课程编号

操作9.2:用ANSI形式连接查询学生学号、姓名、性别及其所选课程编号

select student.st_id, st_nm, st_sex, cs_id

from student, slt_couse

where slt_couse.st_id = student.st_id

操作9.3:用SQL Server形式连接查询学生学号、姓名及其所选课程名称及成绩

操作9.4:用ANSI形式连接查询学生学号、姓名及其所选课程名称及成绩

select student.st_id, st_nm, cs_nm, score

from student, slt_couse, couse

where slt_couse.st_id = student.st_id and slt_couse.cs_id = couse.cs_id

操作9.5:查询选修了1002课程的学生学号、姓名及1001课程成绩

select student.st_id, st_nm

from student, slt_couse

where student.st_id = slt_couse.st_id and slt_couse.cs_id = ‘1001’

操作9.6:查询选修了“数据结构”课程的学生学号、姓名及课程成绩

select student.st_id, st_nm, score

from slt_couse, student, couse

where couse.cs_nm = ‘数据结构’ and slt_couse.cs_id = couse.cs_id and slt_couse.st_id = student.st_id

操作9.7:用左外连接查询没有选修任何课程的学生学号、姓名

select student.st_id, st_nm, score

from student left outer join slt_couse on student.st_id = slt_couse.st_id

where cs_id = NULL

操作9.8:用右外连接查询选修各个课程的学生学号

 

 

实验10 数据查询(6)——子查询

操作10.1:用子查询对各班人数进行查询(新增列)

select distinct left(a.st_id, 6) as 班级, 人数=

(

select count(st_id) from student b

where left(a.st_id, 6) = left(b.st_id, 6)

)

from student a

操作10.2:用子查询对各课程的选课人数进行查询(新增列)

select distinct a.cs_id as 课程号, 人数=

(

select count(st_id) from slt_couse b

where a.cs_id = b.cs_id

)

from slt_couse a

操作10.3:查询选修了1002课程成绩不及格的学生的学号、姓名和性别,并按姓名升序排序

通过子查询实现:——使用IN关键字

select st_id, st_nm, st_sex

from student

where st_id in

(

select st_id from slt_couse

where cs_id = ‘1002’ and score < 60

)

order by st_nm asc

通过子查询实现:——使用比较运算符

select st_id, st_nm, st_sex

from student a

where

(

select score from slt_couse b

where a.st_id = b.st_id and b.cs_id = ‘1002’

) > 60

order by st_nm asc

操作10.4:查询“东方红”同学所在班的学生信息,并按姓名降序排序

通过子查询实现:——IN运算符

select *

from student a

where left(st_id, 6) in

(

select left(st_id, 6) from student b

where b.st_nm = ‘东方红’

)

order by st_nm

通过自连接查询实现:——JOIN

select s1.st_id, s1.st_nm, s1.st_sex

from student s1 join student s2

on left(s1.st_id, 6) = left(s2.st_id, 6)

where s2.st_nm = ‘东方红’

order by s1.st_nm

操作10.5:查询其它班比070511班某一学生的1002号课程成绩高的学生信息(ANY/ALL)

select * from slt_couse

where score > any

(

select score from slt_couse

where cs_id = ‘1002’ and left(st_id, 6) = ‘070511’

)

and left(st_id, 6) != ‘070511’ and cs_id = ‘1002’

操作10.6:查询其它班比070511班任一学生的1002号课程成绩高的学生信息(ANY/ALL)

select * from slt_couse

where score > all

(

select score from slt_couse

where cs_id = ‘1002’ and left(st_id, 6) = ‘070511’

)

and left(st_id, 6) != ‘070511’ and cs_id = ‘1002’

操作10.7:查询大于等于60分且且比1003课程平均成绩低的学生课程信息(Between…And)

select * from slt_couse a

where a.score between 60 and (

select avg(score) from slt_couse b

where b.cs_id = ‘1003’

)

操作10.8:查询系主任为“赵虎”的系的所有学生信息

通过子查询实现:——IN运算符

select * from student a

where a.st_id in

(

select b.st_id from student b

where b.st_dpid = ‘赵虎’

)

通过子查询实现:——=运算符

select * from student a

where a.st_dpid =

(

select b.st_dpid from student b

where b.st_dpid = ‘赵虎’

)

 

实验11 数据查询(7)——数据更新与子查询

操作11.1:将070511班所有学生信息插入到表student01(st_id,st_nm,st_sex)

insert into student01

select st_id, st_nm, st_sex

from student

where left(st_id, 6) = ‘070511’

操作11.2:生成1002号课程的成绩单student02(st_id,st_nm, score)

insert into student02

select student.st_id, st_nm, score

from slt_couse, student

where slt_couse.st_id= student.st_id and slt_couse.cs_id = ‘1002’

操作11.3:将有不及格成绩的学生的st_mnt值更改为3

update student

set st_mnt = 3

where st_id in

(

select distinct st_id from slt_couse

where score < 60

)

操作11.4:将没有被选修的课程的学分更改为0

update couse

set cs_sc = 0

where cs_id not in

(

select distinct cs_id from slt_couse

)

操作11.5:删除5系学生的选课信息

delete from slt_couse

where st_id in

(

select st_id from student

where st_dpid = 5

)

操作11.6:删除学分为0的选课信息

delete from slt_couse

where cs_id in

(

select distinct cs_id from couse

where cs_sc = 0

)

 

欢迎留言