1415-1SQL实验与练习题参考答案

数据库技术与应用（SQL Server）实验题目 8—1

实验1 数据定义与数据更新

一．实验目的：

1. 熟悉SQL Server 2005的运行环境和使用方法 2. 掌握数据库、数据表的定义方法 3. 掌握数据表中数据的更新方法 4. 掌握完整性约束的定义方法

二．实验内容： 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 = test_data, FILENAME = 'd:\\test_data.mdf', SIZE = 5MB, MAXSIZE = unlimited, FILEGROWTH = 1MB ) LOG ON ( NAME = test_log, FILENAME = 'd:\\test_log.ldf', SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 10% ) GO 操作1.2：使用T-SQL语句查看数据库test属性

EXEC sp_helpdb test

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

DROP DATABASE Test

2、表操作

上机实验相关数据表：

表名：student 长度 9 8 2 说明：学生基本信息表 空值 Not Null Not Null Null 列约束 PK 说明 学生学号 学生姓名 学生性别 属性列 st_id st_nm st_sex 数据类型 nVarChar nVarChar nVarChar 数据库技术与应用（SQL Server）实验题目 8—2

st_birth st_score st_date st_from st_dpid st_mnt

datetime int datetime nChar nVarChar tinyint 数据类型 nVarChar nVarChar int int 20 2 Null Null Null Null Null Null 出生日期 入学成绩 入学日期 学生来源 所在系编号 学生职务 表名：couse 属性列 cs_id cs_nm cs_tm cs_sc

说明：课程信息表 长度 4 20 空值 Not Null Not Null Null Null 列约束 PK 说明 课程编号 课程名称 课程学时 课程学分 表名：slt_couse 属性列 cs_id st_id score sltdate

说明：选课表 长度 4 9 空值 Not Null Not Null Null Null 列约束 说明 课程编号 学生编号 课程成绩 选课日期 数据类型 nVarChar nVarChar int datetime 数据类型 nVarChar nVarChar nVarChar nVarChar 表名：dept 属性列 dp_id dp_nm dp_drt dt_tel

说明：院系信息表 长度 2 20 8 12 空值 Not Null Not Null Null Null 列约束 说明 系编号 院系名称 院系主任 联系电话 操作1.4：创建学生表student：

USE test GO

CREATE TABLE student ( st_id nVarChar(9) st_nm nVarChar(8) st_sex nVarChar(2) st_birth datetime st_score int st_date datetime st_ from nVarChar(20) st_dpid nVarChar(2) st_ mnt tinyint ) GO

primary key NOT NULL , NOT NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL

数据库技术与应用（SQL Server）实验题目 8—3

操作1.5：创建课程信息表couse：

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

操作1.6：为“student”表添加“dp_count”列（数据类型为nvarchar，长度为3，允许为空）

ALTER TABLE student ADD dp_count nvarchar(3) NULL 操作1.7：修改“student”表的“dp_count”列数据类型为int

ALTER TABLE student ALTER COLUMN dp_count INT NULL 操作1.8：删除“student”表的“dp_count”列

ALTER TABLE student DROP COLUMN dp_count 操作1.9：删除“dept”表

DROP TABLE couse

操作1.10：分别向“student”表、“couse”表中输入数据记录

3、数据更新

操作1.11：向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' )

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

INSERT INTO couse(cs_id, cs_nm) VALUES ('1234', '操作系统') 操作1.13：修改student表记录，将王小五的入学成绩改为88

UPDATE student SET st_score=88 WHERE st_nm='王小五'

操作1.14：修改couse表记录，将所有记录的学分改为4，学时改为64

UPDATE couse SET cs_tm=64, cs_sc=4

操作1.15：删除couse表记录，将课程号为1234的记录删除

DELETE FROM couse WHERE cs_id='1234'

操作1.16：删除student表记录，将姓别为男，成绩为88的记录删除

DELETE FROM student WHERE st_sex='男' AND st_score=88

4、完整性约束

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

ALTER TABLE student ALTER COLUMN st_nm nVarChar(8) NOT NULL 操作1.18：将student表中的st_from列默认值设置为“陕西省”

ALTER TABLE student ADD DEFAULT '陕西省' FOR st_from

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

ALTER TABLE slt_couse ADD CHECK (score>=0 AND score<=100) 操作1.20：将dept表中的dp_id列设置为主键

数据库技术与应用（SQL Server）实验题目 8—4

ALTER TABLE dept ADD PRIMARY KEY (dp_id) 操作1.21：将dept表中的dp_nm列设置为唯一性约束

ALTER TABLE dept ADD UNIQUE (dp_nm) 操作1.22：被参照表为dept，参照表为student

ALTER TABLE student ADD FOREIGN KEY (st_dpid) REFERENCES dept(dp_id)

三．实验总结：

实验2 简单查询

一．实验目的：

1. 掌握基本查询的使用方法 2. 掌握条件查询的使用方法

3. 掌握对查询结果进行排序的方法 4. 掌握在查询中使用统计函数的方法 5. 掌握对查询结果进行分类汇总的方法

二．实验内容：

操作2.1：查询所有系的信息

SELECT * FROM dept

操作2.2：查询所有的课程号与课程名称

SELECT cs_id, cs_nm FROM couse

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

SELECT st_id, st_nm, st_sex, DATEPART(yy,st_birth) AS 入学年份 FROM student 操作2.4：显示所有学生班级

SELECT DISTINCT LEFT(st_id,6) AS 班级 FROM student

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

SELECT st_id, st_nm, st_sex, st_dpid FROM student WHERE st_dpid = '11'

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

SELECT * FROM student

WHERE DATEPART( yy, st_date )>= 2008 操作2.7：查询student表中非11系的学生信息

SELECT * FROM student WHERE NOT (st_dpid = '11') 操作2.8：查询选修了1002号课程且成绩在60以下的学生学号

SELECT st_id FROM slt_couse WHERE (cs_id='1002') AND (score<60) 操作2.9：查询2007年入学的11系所有男生信息

SELECT * FROM student

WHERE DATEPART(yy,st_date)=2007 AND st_dpid='11' AND st_sex='男' 操作2.10：查询11系和12系的学生信息

SELECT * FROM student WHERE st_dpid='11' OR st_dpid='12' 操作2.11：查询所有“计算机”开头的课程信息

SELECT * FROM couse WHERE cs_nm LIKE '计算机%' 操作2.12：查询所有由三个字组成的“王”姓学生信息

数据库技术与应用（SQL Server）实验题目 8—5

SELECT * FROM student WHERE st_nm LIKE '王__' 操作2.13：查询所有课程名中包含“信息”的课程信息

SELECT * FROM couse WHERE cs_nm LIKE '%信息%' 操作2.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'

操作2.15：查询所有张，王，李，赵姓的学生的学号、姓名、性别

SELECT st_id, st_nm, st_sex FROM student WHERE Left(st_nm,1) IN ('张','王','李','赵') 操作2.16：查询所有生源为非空的学生信息

SELECT * FROM student WHERE st_from IS NOT NULL 操作2.17：查询课程信息，按课程名称降序排序

SELECT * FROM couse ORDER BY cs_nm DESC

操作2.18：查询学生表中的男生信息，按姓名升序排序，再按系号降序排序

SELECT * FROM student WHERE st_sex='男' ORDER BY st_nm, st_dpid DESC 操作2.19：查询课程总数

SELECT COUNT( * ) FROM couse 操作2.20：查询选修1001号课程的学生人数

SELECT COUNT(st_id) FROM slt_couse Where cs_id = '1001' 操作2.21：查询070101001号学生选修课程的数量、总分以及平均分

SELECT COUNT(cs_id) AS 课程数量,SUM(score) AS 总分,AVG(score) AS 平均分 FROM slt_couse WHERE st_id='070101001' 操作2.22：求各个课程号和相应的选课人数

SELECT cs_id, COUNT(st_id) FROM slt_couse GROUP BY cs_id 操作2.23：统计各班人数

SELECT LEFT(st_id,6) AS 班级, COUNT(st_id) AS 人数 FROM student GROUP BY LEFT(st_id,6)

三．实验总结：

实验3 连接查询与子查询

一．实验目的：

1. 掌握连接查询的概念，进行连接查询的使用方法

2. 掌握子查询的概念，以及在查询和更新语句中使用子查询的方法

二．实验内容：

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

SELECT a.st_id, st_nm, st_sex, cs_id FROM student a, slt_couse b WHERE a.st_id = b.st_id ORDER BY a.st_id

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

SELECT a.st_id, st_nm, st_sex, cs_id

FROM student a INNER JOIN slt_couse b ON a.st_id = b.st_id ORDER BY a.st_id

本文来源：https://www.bwwdw.com/article/ed4v.html