sql server实验一到实验七

实验一 熟悉SQL SERVER的环境

（ 验证型实验 2学时）

1.目的要求：

了解SQL Server management studio的使用 2.实验内容：

回答下面每一个问题,写出实验步骤

1) 在 “已注册服务器窗口”中注册sql server数据库服务器

2) 在“对象资源管理器”中创建名字为sc的数据库

Create database sc

3) 在sc数据库中创建一个名字为student的基本表 create table student

(sno char(9) constraint aa primary key, sname char(20) unique, ssex char(2) default '男', sage smallint default 19,

sdept char(20), );在查询窗口中里创建名为S_C的数据库

4) 在查询窗口中使用sql语言创建名字为course的基本表 create table course (cno char(4) primary key, cname char(40), cpno char(4), credit smallint); 3.主要仪器设备及软件： （1）PC

（2）Microsoft SQL Server 2005

实验二 建立表格，并插入若干记录

（ 验证型实验 2学时）

1. 目的要求：

学会使用Create Table语句和Insert语句 2. 实验内容：

1) 使用sql语言建立student,course和sc共三张表格（包括主键,外码的指定）,

分析具体情况适当给出一些用户自定义的约束.

2) 使用Insert语句向这四张表格里添加至少10条记录(数据如教材56页所示),如

果出现错误,分析错误原因

3) 在“对象资源管理器”中实现(1)题中的三张表

4) 在“对象资源管理器”中向这(1)题中的三张表添加至少10条记录(数据如教材

56页所示),如果出现错误,分析错误原因

3. 主要仪器设备及软件：

（1）PC

（2）Microsoft SQL Server 2005

Sql 语言

代码

create database sd create table student

(sno char(9) constraint aa primary key, sname char(20) unique, ssex char(2) default '男', sage smallint default 19, sdept char(20), );

create table course

(cno char(4) primary key, cname char(40), cpno char(4),

values('20050411','李华','男','cs')

insert into student(sno,sname,ssex,sdept) values('20050112','李丽','女','cs')

insert into student(sno,sname,ssex,sdept) values('20040101','张三','男','cs')

insert into student(sno,sname,ssex,sdept) values('20040501','李晨','男','cs')

insert into student(sno,sname,ssex,sdept) values('20060301','李小','女','math') insert into student(sno,sname,ssex,sdept) values('20060401','张红','女','cs')

insert into student(sno,sname,ssex,sdept) values('20070401','李金','女','is')

insert into course(cno,cname,cpno,credit) values('1','数据库','1','4')

insert into course(cno,cname,cpno,credit) values('2','数学',null,'4')

insert into course(cno,cname,cpno,credit) values('3','数据处理','2','4')

insert into course(cno,cname,cpno,credit) values('4','Pascal语言','3','4')

insert into sc(sno,cno,grade) values('20050411','1',null) insert into sc(sno,cno,grade) values('20050411','2','80') insert into sc(sno,cno,grade) values('20050112','2','79') insert into sc(sno,cno,grade) values('20040501','3',null) insert into sc(sno,cno,grade)

values('20040501','2','72') 1）列出没有成绩的学生的学号和课程号 select sc.sno,sc.cno from sc

where grade is null;

2）列出2号课程成绩在70分到80分学生的学号 select sno from sc

where cno='2' and grade between 70 and 80

1) 查询所有2005级的学生的姓名，性别和所在系 select sname,ssex,sdept from student

where sno like '2005%'

2) 查询计科系2004级全体学生的所有信息 select *

from student

where sno like '2004%'

3) 查询计科系2006级3班和4班学生的姓名和性别 select sname,ssex

from student

where sno like '200603%' or sno like '200604%'

4) 查询所有以“数”打头的课程的名称和学分 select cname,credit from course

where cname like '数%'

5) 查询数学系所有学生的姓名，性别和出生年份 select sname,ssex,2011-sage from student

where sdept='math'

6) 将course表中名为PASCAL语言的课程更名为“C语言”

update course set cname='C语言'

where cname='Pascal语言' select * from course

7) 将所有课程的学分增加1分

update course set credit=credit+1 select *from course

8）

删除没有选课成绩的选课记录

delete from sc where grade is null select * from sc

8) 删除“IS”系的所有学生信息

delete from student where sdept='is' select * from student

9) 删除所有的课程信息 delete from course where cname is not null select * from course

3. 主要仪器设备及软件：

（1）PC

（2）Microsoft SQL Server 2005

实验四 查询（多表查询，嵌套查询，分组查询）

（ 验证型实验 12学时）

1. 目的要求：

实现单表和多表的普通查询和嵌套查询。包括返回单值的子查询和返回多值的子查询。使用5个聚合函数以及GROUP BY子句和HAVING子句实现分组查询. 2. 实验内容

有如下关系模式，分析每个关系模式的主码，外码，完成后面的查询 职员表：Emp(eid:integer;ename:string,salary:real)

部门表：Dept(did:integer,dname:string,managerid:integer,floornum:integer) 职员与部分的关系表：Works(eid:integer,did:integer)； Works表表示：一个职员可以在多个部门工作，一个部门有多个职员

Dept表中managerid可以取值null，表示尚未任命部门经理，floornum可以取值null，表示尚未分配工作地点

用单表查询完成如下操作： 1) 输出所有员工的姓名和工资 select ename,salary from emp

2) 输出薪水少于10 000或者大于100 000的雇员的名字 select ename from emp

where salary<10000 or salary>100000

and dname='玩具')

9) 输出比“电视”部门职工平均工资高的雇员的姓名 select ename from emp

where salary > (select avg(salary) from emp,dept,works where emp.eid=works.eid and dept.did=works.did and dname='电视')

10) 找出所有有职工的部门的名字和楼层号 select dname,floornum from dept,emp,works

where dept.did=works.did and emp.eid=works.eid

11) 查询所有没有职工的部门编号和名字(经理算不算员工) select did,dname from dept

where managerid IS null and did not in(select did from works)

12) 输出同时在玩具部门和糖果部门工作的雇员的名字和薪水 select ename salary from emp,dept,works

where emp.eid=works.eid and dept.did=works.did and dept.dname='玩具' and ename in(select ename

from emp,works,dept

where emp.eid=works.eid and dept.did=works.did and dept.dname='糖果' )

3. 主要仪器设备及软件：

（1）PC

（2）Microsoft SQL Server 2005

实验五 为表格建立约束，修改约束和查询约束

（ 验证型实验 4学时）

1. 目的要求：

使用ALTER语句和CREATE语句建立、修改、删除和查询约束 2. 实验内容

执行以下SQL语句，完成随后的操作，若有错误，分析错误原因并改正错误： Create table student(sno char(9) not null , sname char(10),ssex char(2),sage tinyint,sdept varchar(40));

Create table course(cno char(4) not null, cname varchar(30),cpno char(4),credit tinyint);

Create table sc(sno char(9),char(5),grade numeric(3,1));

1) 在student表中，使sdept只能取值“计算机科学学院”，“数软学院”，“电子工

程学院”，“化学与材料科学学院”

alter table student add check(sdept in('计算机科学学院','数软学院','电子工程学院','化学与材料科学学院')) 2) 在student表中sage有默认值18

alter table student add constraint de_sage default 18 for sage 3) 为student表建立主键 alter table student add primary key(sno);

4) 为course表建立主键和外键，其中外键约束名为C_FK_CPNO alter table course add primary key(cno);

alter table course add constraint C_FK_CPNO foreign key(cpno) references course(cno)

5) 为course表建立检查约束，限定credit的取值只能取3，2，4，5； alter table course add check(credit in('3','2','4','5')) 6) 为course表建立唯一约束，确保每们课程名字唯一 alter table course add unique(cname) 7) 为sc表建立主键和外键，并给出相应的约束名

alter table course add unique(cname)

由于外键只能在不能为空的列上建 ，所以要先把sc中sno，cno改为不能为空 alter table sc alter column sno char(9) not null alter table sc alter column cno char(5) not null

alter table sc add constraint pk_sc primary key(sno,cno);

8) 在course表中插入元组(1,数据库，5，4)和(2,数学，null,2),若不能正确插入，

分析原因，给出解决办法

不能第一条语句不能插入 ，因为外键值必须是他所参照的咧中已存在的值，可以先禁用外键，就能狗插入了,第二条可以插入。 alter table course nocheck constraint C_FK_CPNO insert into course

values ('1','数据库','5','4')

insert into course

values ('2','数学',null,'2')

9) 在sc表中插入元组（95001,1,92）和（95001，3，88），若不能正确插入，分析

原因，给出解决办法

insert into sc

values ('95001','1','92')

insert into sc

values ('95001','3','88')

（2）使用系统存储过程， sp_help, sp_helpconstraint等对约束进行查询和管理 查阅联机帮助文档，选中“索引”选项卡，分别输入sp_help, sp_helpconstraint，阅读其帮助信息。

使SC数据库成为当前数据库，执行如下命令，简要解释执行结果

1) Exec sp_help

2) Exec sp_help student

3) Exec sp_help course

4) Exec sp_helpconstaint sc

5) Exec sp_helpconstraint student

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