sql server上机实验指导 sqlserver实验指导书答案

文章目录

• 实验一

• 实验内容
• 实验代码

• 创建数据库
• 创建表
• 添加数据

• 实验二

• 1. 查询年龄小于22岁的女学生的学号、姓名、年龄
• 2. 查询选修了“101”号课程的学生的学号、姓名
• 3. 查询选修了“101” 号课程或“204号”课程的学生的学号。
• 4. 查询选修了“数据库原理与应用”的学生的学号和姓名。
• 5. 查询成绩介于80-100的学生的学号、姓名、课程名、成绩，并按照成绩的升序排序。
• 6. 查询成绩为82、90、92的学生的姓名、课程名、成绩。
• 7. 查询所有姓“王”的学生的姓名、选修课程的课程名、成绩。
• 8. 查询各门课程的选修人数及平均分。要求查询结果显示课程号、选修人数、平均分。
• 9. 查询选修了“204”课程的学生中，成绩高于学号为“142001”的该门课成绩的学生的学号。
• 10. 查询未选修“101”号课程的学生的学号、姓名。
• 11. 查询“周培杰”未选修课程的课程号、课程名。

实验一

实验内容

1. 使用命令方式建立学生信息管理数据库。要求其主数据文件初始大小为20MB，最大200MB，增长速度为10%。日志文件初始大小为1MB，最大30MB,增长速度为1MB。
2. 通过命令方式在建好的学生信息管理数据库中新建3个数据表：学生表S、课程表C和选课表SC，表的具体内容如下：

1. 
   
   其表结构为：
   S（Sno,Sname,Sex,Age,Speciality）C（Cno,Cname,Teacher）SC（Sno,Cno,Grade） 要求为每个属性选择合适的数据类型及长度；定义每个表的主码；Null（是否允许空值）。实现学生表S的性别Sex列默认值是“女”的default约束；选课表SC中Grade列设置check约束，分数只能是介于0-100之间的整数。
2. 尝试使用insert into命令为3张表插入上图给定的数据记录。
   代码

实验代码

创建数据库

如果考试时，对mdf、ldf没有要求，就直接
create database xsgl

create database xsgl
on
(
	name='xsgl',
	filename='E:\abc\xsgl.mdf',
	size=20MB,
	maxsize=200MB,
	filegrowth=10%
)
log on
(
	name='xsgl_log',
	filename='E:\abc\xsgl_log.ldf',
	size=1MB,
	maxsize=30MB,
	filegrowth=1MB
)

创建表

-- create database xsgl
-- go
-- 前面已经创建xsgl数据库了
use xsgl
create table S(
Sno char(6) primary key,
Sname varchar(10) not null,
Sex char(2) default '女',
Age smallint,
Speciality varchar(30)
)
create table C(
Cno char(3) primary key,
Cname varchar(30),
Teacher varchar(30)
)
-- 长度要保持一致, sno、con共同作为主码
create table SC(
Sno char(6),
Cno char(3),
Grade smallint check(grade>=0 and grade<=100),
primary key(Sno,Cno)
)

添加数据

USE xsgl
INSERT INTO S VALUES
	('141001','刘星宇','男',22,'电子工程'),
	('141002','王小凤','女',20,'电子工程'),
	('142001','杨燕','女',21,'计算机应用'),
	('142001','周培杰','男',21,'计算机应用')
INSERT INTO C VALUES
	('101','信号与系统','李航远'),
	('204','数据库原理与应用','钱春丽'),
	('901','英语','唐莉')
INSERT INTO SC VALUES
	('141001','101',94),
	('141002','101',76),
	('141001','204',92),
	('141002','204',74),
	('141001','204',87),
	('142001','204',90),
	('142004','901',95),
	('141001','901',84),
	('142001','901',82),
	('142004','901',92)

实验二

练习T-SQL查询语句

1. 查询年龄小于22岁的女学生的学号、姓名、年龄

SELECT Sno, Sname, Age
FROM S
WHERE Age < 22 and Sex = '女'

2. 查询选修了“101”号课程的学生的学号、姓名

SELECT S.Sno, S.Sname
FROM S
JOIN SC
ON S.Sno = SC.Sno
WHERE SC.Cno = 101

3. 查询选修了“101” 号课程或“204号”课程的学生的学号。

SELECT DISTINCT Sno
FROM SC
WHERE Cno IN (101, 204)

4. 查询选修了“数据库原理与应用”的学生的学号和姓名。

SELECT S.Sno, S.Sname
FROM S
JOIN SC
ON S.Sno = SC.Sno
JOIN C
ON SC.Cno = C.Cno
WHERE C.Cname = '数据库原理与应用'

5. 查询成绩介于80-100的学生的学号、姓名、课程名、成绩，并按照成绩的升序排序。

SELECT S.Sno, S.Sname, C.Cname, SC.Grade
FROM S
JOIN SC
ON S.Sno = SC.Sno
JOIN C
ON SC.Cno = C.Cno
WHERE SC.Grade BETWEEN 80 AND 100
ORDER BY SC.Grade

6. 查询成绩为82、90、92的学生的姓名、课程名、成绩。

SELECT Sname, Cname, Grade
FROM S
JOIN SC
ON S.Sno = SC.Sno
JOIN C
ON SC.Cno = C.Cno
WHERE Grade in (82, 90, 92)

7. 查询所有姓“王”的学生的姓名、选修课程的课程名、成绩。

SELECT Sname, Cname, Grade
FROM S
JOIN SC
ON S.Sno = SC.Sno
JOIN C
ON SC.Cno = C.Cno
WHERE S.Sname LIKE '王%'

8. 查询各门课程的选修人数及平均分。要求查询结果显示课程号、选修人数、平均分。

SELECT Cno AS '课程号', COUNT(*) AS '选修人数', AVG(Grade) AS '平均分'
FROM SC
GROUP BY Cno

9. 查询选修了“204”课程的学生中，成绩高于学号为“142001”的该门课成绩的学生的学号。

SELECT Sno
FROM SC
WHERE Cno = 204 AND Grade > (
	SELECT Grade
	FROM SC
	WHERE Sno = 142001 AND Cno = 204
)

10. 查询未选修“101”号课程的学生的学号、姓名。

SELECT Sno, Sname
FROM S
WHERE Sno NOT IN(
	SELECT S.Sno
	FROM S
	JOIN SC
	ON S.Sno = SC.Sno
	WHERE Cno = 101
)

11. 查询“周培杰”未选修课程的课程号、课程名。

SELECT DISTINCT SC.Cno, C.Cname
FROM SC
JOIN C
ON SC.Cno = C.Cno
WHERE SC.Cno NOT IN(
	SELECT SC.Cno
	FROM SC
	JOIN S
	ON S.Sno = SC.Sno
	WHERE Sname = '周培杰'
)