2016年数据库的卷二练习答案.docx

--1、创建数据库
create database test
on primary
(
name = 'test_data',
filename='D:\SQL期末作业\test_data.mdf',
size=10MB,
maxsize=10MB,
filegrowth=1MB)
log on
(
name='test_log',
filename='D:\SQL期末作业\test_log.ldf',
size=1MB,
maxsize=5MB,
filegrowth=10%
)
go
--2、删除数据库
drop database test
--3、创建student表
use test
go
create table student
(
st_id nvarchar(9) not null constraint pk primary key,
st_nm nvarchar(8) not null ,
st_sex nvarchar(2),
st_birth datetime null,
st_score int null,
st_date datetime null,
st_from nchar(20),
st_dpid nvarchar(2),
st_mnt tinyint
)
--3、创建course表
use test
go
create table course
(cs_id nvarchar(4) not null constraint pk_id primary key,
cs_nm nvarchar(20) not null,
cs_tm int ,
cs_sc int )
--4、创建slt_course
use test
go
create table slt_course
(
cs_id nvarchar(4) not null constraint fk_id references course(cs_id),
st_id nvarchar(9) not null constraint fk_st_id references student(st_id),
score int ,
sltdate datetime
)
--5、创建院系（dept）
use test
go
create table dept
(
dp_id nvarchar(2) not null ,
dp_nm nvarchar(20) not null,
dp_drt nvarchar(8) ,
dt_tel nvarchar(12)
)
--修改表结构
--1）向表中添加列
use test
go
alter table dept
add dp_count nvarchar(3)
go
--2)修改列数据
use test
go
alter table dept
alter column dp_count int
go
--3、删除表指定列(column)
use test
go
alter table dept
drop column dp_count
go
--4、删除dept表
use test
go
drop table dept
go
--向表中输入数据记录
use test
go
insert student
values
('20151090101','邓建娥','女','1996','400',
'2015','山东省潍坊','01','学习部副部')
go
--3、数据完整性
--1）空值约束
use test
go
alter table student
alter column st_sex varchar(2) not null
--默认值约束（default）
use test
go
alter table student
add constraint df_from default '陕西省' for st_from
go
--检查约束（check）
use test
go
alter table slt_course
add constraint ck_course check (score>=0 and score<=100)
go
--主键 clustered为聚集索引，nonclustered为非聚集索引
use test
alter table dept
add constraint pk_dp_id primary key clustered (dp_id)
go
--唯一性约束（unique）
use test
alter table dept
add constraint uq_nm unique nonclustered(dp_nm)
go
--创建外键
use test
alter table student
add foreign key (st_dpid) references dept(dp_id)
-- 数据更新
--4.1、dept表中插入数据
use test
go
insert dept(dp_id,dp_nm,dp_drt,dt_tel)
values ('11','自动控制系','李启宇','81234567')
go
--4.2、student表中插入记录
use test
go
insert student (st_id,st_nm,st_sex,st_birth,st_dpid)
values ('070201001','王小五','男','1990-09-09','11')
go
--4.3 course表中插入记录
use test
go
insert course(cs_id ,cs_nm )
values ('1234','操作系统')
go
--4.4slt_course表中添加数据
use test
go
insert slt_course (cs_id ,st_id)
values('1234','070201001')
go
--2.修改表中数据
--4.5 修改student表中记录
use test
go
update student
set st_score ='88'
where st_nm ='王小五'
go
--4.6修改course表记录
use test
go
update course
set cs_sc =4,cs_tm =64
--4.7修改slt_course表中记录
use test
go
update slt_course
set score =77
where cs_id =1234 and st_id = 070201001
go
--删除表中数据
use test
delete slt_course
where cs_id='1234' and st_id ='070201001'
go
--删除course记录
use test
delete course
where cs_id ='1234'
go
--5 数据查询（）简单查询
--5.1查询所有系的信息
use test
go
select *
from dept
go
--5.2 查询所有的课程号与课程名称
use test
go
select cs_id,cs_nm
from course
go
--5.3 查询student时使用列表达式：入学成绩+400
use test
go
select st_score+400
from student
go
--5.4用as关键字dept表指定列名：系号、系名、系主任、联系电话
use test
go
select dp_id as 系号,dp_nm as 系名,dp_drt as 系主任,dt_tel as 联系电话
from dept
go
--5.5使用"="号为course表中属性指定列名：课程号、课程名、学时(=cs_sc*16)、学分
use test
go
select 课程号=cs_id,课程名=cs_nm,学分=cs_sc,学时=cs_sc*16
from course
go
--5.6查询列表中使用系统函数 显示所有学生的学号、姓名、性别和入学年份
use test
go
select st_id,st_nm,st_sex,st_date
from student
group by st_id
go
--5.7消除查询结果中的重复项 显示所有学生班级
use test
select distinct st_id
from student
go
--6数据查询（）-条件查询
--1.使用关系表达式查询条件
--6.1查询dept表中系号为的院系信息
use test
go
select *
from dept
where dp_id='11'
go
--6.2查询student表中系的学生学号、姓名、性别和所在系编号
use test
go
select st_id,st_nm,st_sex,st_dpid
from student
where st_dpid ='11'
go

--6.3 查询student表中年及以后入学的学生信息
use test
select *
from student
where st_date > '2008-12-31'
go
--6.4在查询student表班学生的学号、姓名、性别和入学成绩
use test
go
select st_id,st_nm,st_sex,st_score
from student
where st_bj='080808'
go

--使用逻辑表达式表示查询条件
--6.5查询选修了号课程且成绩在以下的学生学号
use test
go
select st_id
from slt_course
where cs_id='1002'and score <'60'
go
--6.6查询系和系的学生信息
use test
go
select *
from student
where st_dpid='11' or st_dpid='12'
go
--6.7查询所有“计算机”开头的课程信息
use test
go
select *
from course
where cs_nm like '计算机%'
go
--6.8查询在.7.1到.6.30之间出生的学生信息
use test
go
select *
from student
where st_birth between '1989-07-01' and '1990-06-30'
go
--6.9查询选修了号课程且成绩在到之间的学生选课信息
use test
go
select *
from slt_course
where cs_id='1001' and (score between 60 and 80)
go
--6.10使用IN关键字进行查询查询系、系、系的学生信息
use test
go
select *
from student
where st_dpid in ('11','12','13')
go
--6.11使用[NOT] NULL关键字进行查询查询系、系、系的学生信息
use test
go
select *
from student
where (st_dpid in ('11','12','13'))and (st_bj is null)
go
--6.11查询所有生源为非空的学生信息
use test
go
select *
from student
where st_from is not null
go
--7 数据查询（）——查询排序与查询结果存储
--7.1：查询课程信息，按课程名称降序排序:desc 升序：asc
use test
go
select *
from course
order by cs_nm desc
go
--7.2查询选修了号课程成绩非空的学生学号和成绩，并按成绩降序排序
use test
go
select a.st_id,b.score
from student as a join slt_course as b
on a.st_id = b.st_id
order by b.score desc
go
--8 数据查询（）——查询统计与汇总

--8.1查询选修号课程的学生人数
use test
go
select COUNT(*) as 学生人数
from student
where st_id in (select st_id from slt_course where cs_id='1001')
go
--8.2查询选修班学生的平均入学成绩
use test
go
select AVG(st_score) as 平均入学成绩
from student
where st_bj='070101'
gor
--8.3查询号学生选修课程的数量、总分以及平均分
use test
go
select COUNT(*)as 选修课数量, SUM(score) as 总分, AVG(score) as 平均分
from slt_course
where st_id='070101001'
go
--8.4查询选修号课程的学生人数、最高分、最低分和平均分
use test
go
select COUNT(*) as 学生人数 ,max(score)as 最高分,MIN(score) as 最低分,AVG(score) as 平均分
from slt_course
where cs_id=1001
go
--8.5查询选修了均分在以上的课程号及均分
use test
go
select cs_id ,AVG(score)平均分
from slt_course
group by cs_id
having AVG(score)>75
go
--9 数据查询（）——连接查询、子查询
--9.1查询学生学号、姓名、性别及其所选课程编号
use test
go
select student.st_id ,st_nm,st_sex,cs_id
from student join slt_course
on student.st_id=slt_course.st_id
go
--9.2查询学生学号、姓名及其所选课程名称及成绩
use test
go
select student.st_id ,st_nm,cs_id,score
from student join slt_course
on student.st_id=slt_course.st_id
go
--9.3查询选修了“数据结构”课程的学生学号、姓名及课程成绩
use test
go
select student.st_id,st_nm,score
from student join slt_course
on student.st_id=slt_course.st_id
where cs_id in (select cs_id from course where cs_nm='数据结构')
go
--10 数据查询（）——子查询
--10.1查询选修了课程成绩不及格的学生的学号、姓名和性别，并按姓名升序排序
use test
go
select st_id,st_nm,st_sex
from student
where st_id in (select st_id from slt_course where cs_id='1002' and score<'60')
go
--11视图和索引
--11.1基于student表创建一个视图view_s2014，可以查看年及以后入学的学生信息
use test
go
create view view_s2014
as
select *
from student
where st_date>'2014-12-30'
go
--11.2为course表创建基于课程编号列的聚集索引kcbh_index
use te- 77st
go
create clustered index kcbh_index on course (cs_id)
go

exec sp_helpindex 'course'
--12游标
--12.1用游标实现：输出所有女学生的学号、姓名、性别和班级代码。
use test
go
declare c_xsxx cursor keyset for
select st_id,st_nm,st_sex,st_bj
from student
open c_xsxx
declare @xh nvarchar(8),@xm nvarchar(8),@xb nvarchar(8),@bjdm nvarchar(10)
if @@ERROR =0 --判断游标打开是否成功
begin
if @@CURSOR_ROWS >0
begin
print '共有女学生'+rtrim(cast (@@cursor_rows as char(3)))+'名,分别是:'
print ''
fetch next from c_xsxx into @xh,@xm,@xb,@bjdm
while (@@FETCH_STATUS=0)
begin print @xh+','+@xm+','+@xb+','
fetch next from c_xhxx into @xh,@xm,@xb,@bjdm
end
end
end
else
print '游标存在问题！'
close c_xsxx
deallocate
--13 存储过程和触发器
--13.1创建一个带参数的存储过程cj：当任意输入一个学生的姓名时，将从三个表
-- （学生表、课程表、课程注册表）中返回该学生的学号、选修的课程名称和课程成绩
-- 学生表 课程 课程注册
use student
go
create proc cj @xm varchar(10)
as
select 学生.学号,课程名称,成绩
from 课程注册 join 学生 on 课程注册.学号=学生.学号
join 课程 on 课程注册.课程号=课程.课程号
where 学生.姓名=@xm
--执行cj存储过程，查询刘永辉的学号、选修课程和课程成绩。
use student
go
exec cj @xm='刘永辉'
--13.3创建一个带返回值的存储过程，根据“课程”表和“课程注册”表中的数据
--返回某课程的成绩大于分的人数。
use student
go
create proc g @rs
--创建一个del_zy的delete触发器
use student
go
create trigger del_zy on 专业
for delete
as
declare @zydm char(9)
select @zydm =专业代码 from deleted
if exists (select * from 班级 where 专业代码=@zydm)
begin
print '正在使用，不能被使用！'
rollback transaction
end
go
--13.5创建一个触发器bjdm_update，当班级表中的班级代码被更新时，

内部