SQL SERVER 上机实验
更新时间:2023-09-15 03:04:01 阅读量: 资格考试认证 文档下载
- sql server推荐度:
- 相关推荐
数据库原理与应用(SQL Server)练习题 - 1 -
实验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 ( ) LOG ON ( ) GO
name = test_log,
filename = 'd:\\test\\test_log.ldf', size = 1MB, maxsize = 5MB, filegrowth = 10% name = test_data,
filename = 'd:\\test\\test_data.mdf', size = 5MB,
maxsize = unlimited, filegrowth = 1MB
2.查看数据库属性:
操作1.2:使用T-SQL语句查看数据库test属性 参考答案:
EXEC sp_helpdb test
3.删除数据库:
操作1.3:使用T-SQL语句删除数据库test 参考答案:
drop database Test
实验2 表操作
1.创建表:
操作2.1:创建学生表:
表名:student
长度 9 说明:学生基本信息表 空值 Not Null 列约束 PK 说明 学生学号 属性列 st_id 数据类型 nVarChar 数据库原理与应用(SQL Server)练习题 - 2 -
st_nm st_sex st_birth st_score st_date st_from st_dpid st_mnt nVarChar nVarChar datetime int datetime nChar nVarChar tinyint 8 2 20 2 Not Null Null Null Null Null Null Null Null 学生姓名 学生性别 出生日期 入学成绩 入学日期 学生来源 所在系编号 学生职务 参考答案: USE test GO
CREATE TABLE student (
st_id nVarChar(9) primary key NOT NULL , 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 nVarChar(20) NULL , st_dpid nVarChar(2) NULL , st_ mnt tinyint NULL ) GO 操作2.2:创建课程信息表:
表名:couse 长度 4 20 说明:课程信息表 空值 Not Null Not Null Null Null 列约束 PK 说明 课程编号 课程名称 课程学时 课程学分 属性列 cs_id cs_nm cs_tm cs_sc 数据类型 nVarChar nVarChar int int 参考答案:
USE test GO
CREATE TABLE couse (
cs_id nVarChar(4) primary key NOT NULL , cs_nm nVarChar(20) NOT NULL , cs_tm int NULL , cs_sc int NULL ) GO 操作2.3:创建选课表:
表名:slt_couse
说明:选课表
数据库原理与应用(SQL Server)练习题 - 3 -
属性列 cs_id st_id score sltdate 数据类型 nVarChar nVarChar int datetime 长度 4 9 空值 Not Null Not Null Null Null 列约束 FK FK 说明 课程编号 学生编号 课程成绩 选课日期 参考答案:
USE test GO
CREATE TABLE couse (
cs_id nVarChar(4) NOT NULL , st_id nVarChar(9) NOT NULL , score int NULL ,
sltdate datetime ) GO
操作2.4:创建院系信息表:
表名:dept NULL
长度 2 20 8 12 说明:院系信息表 空值 Not Null Not Null Null Null 列约束 说明 系编号 院系名称 院系主任 联系电话 属性列 dp_id dp_nm dp_drt dt_tel 数据类型 nVarChar nVarChar nVarChar nVarChar 参考答案:
USE test GO
CREATE TABLE dept (
dp_id nVarChar(2) NOT NULL , dp_nm nVarChar(20) NOT NULL , dp_drt nVarChar(8) NULL , dp_tel nVarChar(12) NULL ) GO
2.修改表结构:
(1)向表中添加列:
操作2.5:为“dept”表添加“dp_count”列(数据类型为nvarchar,长度为3,允许为空) 参考答案:
ALTER TABLE dept ADD dp_count nvarchar(3) NULL (2)修改列数据类型:
操作2.6:修改“dept”表的“dp_count”列数据类型为int 参考答案:
ALTER TABLE dept ALTER COLUMN dp_count int NULL
数据库原理与应用(SQL Server)练习题 - 4 -
(3)删除表中指定列:
操作2.7:删除“dept”表的“dp_count”列 参考答案:
ALTER TABLE dept DROP COLUMN dp_count
3.删除表
操作2.8:删除“dept”表 参考答案:
DROP TABLE student
4.向表中输入数据记录
操作2.9:分别向“student”表、“couse”表、“slt_couse”表、“dept”表中输入数据记录
实验3 数据完整性
1.空值约束( NULL )
操作3.1:将student表中的st_sex列属性更改为NOT NULL 参考答案:
ALTER TABLE student ALTER COLUME st_nm nVarChar(8) 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)
数据库原理与应用(SQL Server)练习题 - 5 -
5.规则约束对象
操作3.5:创建规则约束对象rl_sex,用于检查性别的取值仅限于“男”和“女”,并将其绑定到student表中的st_sex列,然后取消绑定,最后删除规则约束对象rl_sex。
参考答案:
CREATE RULE rl_sex AS @chksex ’男’ OR @chksex=’女’ 或
CREATE RULE rl_sex AS @chksex IN (’男’, ’女’) GO
EXEC sp_bindrule rl_sex, 'student.st_sex' GO
EXEC sp_unbindrule 'student.st_sex' GO
DROP RULE rl_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 UNIQUE (dp_nm)
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 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)
数据库原理与应用(SQL Server)练习题 - 6 -
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_tm=64, cs_sc=4
操作4.7:修改slt_couse表记录,将课程号为1234,学名为070201001的记录的成绩改为77
UPDATE slt_couse SET score=77 WHERE cs_id='1234' AND st_id='070201001' 操作4.8:将“软件学院”学生的成绩加5分
update slt_couse set score=score+5
where st_id in(select st_id from student
where st_dpid in(select st_dpid from dept
where dp_nm='软件学院'))
3.删除表中数据
操作4.9:删除slt_couse表记录,将课程号为1234,学名为070201001的记录删除
DELETE FROM slt_couse WHERE cs_id='1234' AND st_id='070201001' 操作4.10:删除couse表记录,将课程号为1234的记录删除
DELETE FROM couse WHERE cs_id='1234' 操作4.11:将“软件学院”学生的选课记录删除
delete slt_couse
from slt_couse,dept,student
where slt_couse.st_id=student.st_id
and student.st_dpid=dept.dp_id and dept.dp_nm='软件学院'
实验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_id, st_nm, st_score, st_score+400 AS new_score FROM student (4)重新命名查询结果
操作5.4:使用AS关键字为dept表中属性指定列名:系号、系名、系主任、联系电话
数据库原理与应用(SQL Server)练习题 - 7 -
SELECT dp_id AS 系号, dp_nm AS 系名, dp_drt AS 系主任, dp_tel AS联系电话 FROM dept
操作5.5:使用\号为couse表中属性指定列名:课程号、课程名、学时(=cs_sc*16)、学分
SELECT 课程号=cs_id, 课程名=cs_nm, 学分=cs_sc, 学时=cs_sc*16 FROM couse (5)增加说明列
操作5.6:查询dept表的系号、系名和系主任,向查询结果中插入说明列:系号、系名和系主任
SELECT '系号:', st_id, '系名:', st_nm, '系主任:', st_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为局部变量
DECLARE @n INT SET @n = 4
SELECT TOP @n * FROM student
实验6 数据查询(2)——条件查询
1.使用关系表达式表示查询条件
操作6.1:查询dept表中系号为11的院系信息
SELECT * 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 DATEPART( yy, st_date )>= 2008
操作6.4:在查询student表080808班学生的学号、姓名、性别和入学成绩
SELECT st_id, st_nm, st_sex, st_score FROM student WHERE Left(st_id,6)='080808'
数据库原理与应用(SQL Server)练习题 - 8 -
2.使用逻辑表达式表示查询条件
操作6.5:查询student表中非11系的学生信息
SELECT * FROM student WHERE NOT (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 DATEPART(yy,st_date)=2007 AND st_dpid='11' AND st_sex='男' 操作6.8:查询11系和12系的学生信息
SELECT * FROM student
WHERE st_dpid='11' OR st_dpid='12' 操作6.9:查询11系和12系所有2007年入学的学生信息
SELECT * FROM student
WHERE (st_dpid='11' OR st_dpid='12') AND DATEPART(yy,st_date)=2007
3.使用LIKE关键字进行模糊查询
操作6.10:查询所有“计算机”开头的课程信息
SELECT * FROM couse WHERE cs_nm LIKE '计算机%' 操作6.11:查询所有由三个字组成的“王”姓学生信息
SELECT * FROM student WHERE st_nm LIKE '王__' 操作6.12:查询所有课程名中包含“信息”的课程信息
SELECT * FROM couse WHERE cs_nm LIKE '%信息%' 操作6.13:查询学生姓名介于王姓到张姓的信息
SELECT * FROM student
WHERE st_nm LIKE '[王-张]%'
4.使用Between…And关键字进行查询
操作6.14:查询在1989.7.1到1990.6.30之间出生的学生信息
SELECT st_id, st_nm, st_sex, st_birth FROM student WHERE st_birth BETWEEN '1981.7.1' AND '1999.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_from IS NOT NULL
数据库原理与应用(SQL Server)练习题 - 9 -
操作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 st_id, score FROM slt_corse
WHERE cs_id='1001' AND score IS NOT NULL 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, 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_corse WHERE cs_id='1001' ORDER BY st_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号课程的学生人数
数据库原理与应用(SQL Server)练习题 - 10 -
SELECT COUNT(st_id) FROM slt_couse
Where cs_id = '1001'
操作8.3:查询被选修课程的数量
SELECT COUNT( DISTINCT cs_id ) FROM slt_couse 操作8.4:查询选修070101班学生的平均入学成绩
SELECT AVG(st_score) FROM student
WHERE LEFT(st_id,6)='070101'
操作8.5:查询070101001号学生选修课程的数量、总分以及平均分
SELECT COUNT(cs_id) AS 课程数量,SUM(score) AS 总分,AVG(score) AS 平均分 FROM slt_couse
WHERE st_id='070101001'
操作8.6:查询选修1001号课程的学生人数、最高分、最低分和平均分
SELECT COUNT(*) AS 学生人数, MAX(score) AS 最高分, MIN(score) AS 最低分, AVG (score) AS 平均分 FROM slt_couse
WHERE cs_id='1001' 操作8.7:求各个课程号和相应的选课人数
SELECT cs_id, COUNT(st_id) FROM slt_couse GROUP BY cs_id
操作8.8:统计各班人数
SELECT LEFT(st_id,6) AS 班级, COUNT(st_id) AS 人数 FROM student
GROUP BY LEFT(st_id,6) 操作8.9:依次按班级、系号对学生进行分类统计人数、入学平均分
SELECT st_dpid AS 系号, LEFT(st_id,6) AS 班级, COUNT(st_nm) AS 人数, AVG(st_score) AS 均分 FROM student
GROUP BY LEFT(st_id,6), st_dpid 操作8.10:查询选修了均分在75以上的课程号及均分
SELECT cs_id AS 课程编号, AVG(score) AS 均分 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的学生,并汇总学生数量、平均年龄
SELECT st_nm,DATEPART(yy,GETDATE( ))-DATEPART(yy,st_birth) AS 年龄 FROM student
WHERE DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)<20
COMPUTE COUNT(st_nm),AVG(DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)) 操作8.13:按班级明细汇总成绩<85分的学生,汇总学生数、均分
SELECT st_nm, LEFT(st_id,6) AS 班级, st_score FROM student
正在阅读:
SQL SERVER 上机实验09-15
1-10数字描红(word打印)03-29
1-10数字描红(word打印)03-18
《行测完全攻略与练习》精华:判断推理03-05
1-10数字描红(word打印)08-08
搬运费月报表模板08-17
某酒店筹备开业个人建议01-08
镇机关支部党建工作述职报告05-02
- 梳理《史记》素材,为作文添彩
- 2012呼和浩特驾照模拟考试B2车型试题
- 关于全面推进施工现场标准化管理实施的通知(红头文件)
- 江西省房屋建筑和市政基础设施工程施工招标文件范本
- 律师与公证制度第2阶段练习题
- 2019-2020年最新人教版PEP初三英语九年级上册精编单元练习unit6训练测试卷内含听力文件及听力原文
- 小升初数学模拟试卷(十四) 北京版 Word版,含答案
- 认识创新思维特点 探讨创新教育方法-精选教育文档
- 00266 自考 社会心理学一(复习题大全)
- 多媒体在语文教学中的运用效果
- 派出所派出所教导员述职报告
- 低压电工作业考试B
- 18秋福建师范大学《管理心理学》在线作业一4
- 中国铝业公司职工违规违纪处分暂行规定
- 13建筑力学复习题(答案)
- 2008年新密市师德征文获奖名单 - 图文
- 保安员培训考试题库(附答案)
- 银川市贺兰一中一模试卷
- 2011—2017年新课标全国卷2文科数学试题分类汇编 - 1.集合
- 湖北省襄阳市第五中学届高三生物五月模拟考试试题一
- 上机
- 实验
- SERVER
- SQL