sql实验一到实验七.docx
《sql实验一到实验七.docx》由会员分享，可在线阅读，更多相关《sql实验一到实验七.docx（15页珍藏版）》请在冰豆网上搜索。
sql实验一到实验七
实验一熟悉SQLSERVER的环境
（验证型实验2学时）
1.目的要求：
了解SQLServermanagementstudio的使用
2.实验内容：
回答下面每一个问题,写出实验步骤
1）在“已注册服务器窗口”中注册sqlserver数据库服务器
2）在“对象资源管理器”中创建名字为sc的数据库
3）在sc数据库中创建一个名字为student的基本表
4）在查询窗口中里创建名为S_C的数据库
5）在查询窗口中使用sql语言创建名字为course的基本表
3.主要仪器设备及软件：
（1）PC
（2）MicrosoftSQLServer2005
实验二建立表格，并插入若干记录
（验证型实验2学时）
1.目的要求：
学会使用CreateTable语句和Insert语句
2.实验内容：
1）使用sql语言建立student,course和sc共三张表格（包括主键,外码的指定）,分析具体情况适当给出一些用户自定义的约束.
createtableSStudent
（Snochar（11）primarykey,
Snamechar（20）,
Ssexchar
（2）,
Sageint,
Sdeptchar（20）
）
select*fromSStudent;
insertintoSStudent
values（'1','小妖','女',18,'计科'）
insertintoSStudent
values（'2','小二','男',19,'计科'）
insertintoSStudent
values（'3','小三','男',20,'物电'）
insertintoSStudent
values（'4','小四','女',20,'计科'）
insertintoSStudent
values（'5','小五','男',18,'物电'）
insertintoSStudent
values（'6','小六','男',19,'物电'）
insertintoSStudent
values（'7','小七','女',18,'计科'）
选课表
课程表
2）使用Insert语句向这四张表格里添加至少10条记录（数据如教材56页所示）,如果出现错误,分析错误原因
3）在“对象资源管理器”中实现
（1）题中的三张表
4）在“对象资源管理器”中向这
（1）题中的三张表添加至少10条记录（数据如教材56页所示）,如果出现错误,分析错误原因
3.主要仪器设备及软件：
（1）PC
（2）MicrosoftSQLServer2005
实验三修改表格结构，修改和删除表格中的数据
（验证型实验4学时）
1.目的要求：
用ALTER语句修改表结构：
添加列，修改列定义，删除列。
使用UPDATE和DELETE语句修改和删除Student,sc,course表格中的数据。
添加列：
altertableSStudentaddaddresschar（40）
修改列定义：
altertableSStudentaltercolumnSagechar（3）
删除列：
altertableSStudentdropcolumnaddress
修改数据：
updateSStudentsetSAge=22whereSno='4'
删除数据：
deletefromSStudentwhereSname='小四'
2.实验内容：
如下所示，创建表s，并完成随后的操作
Createtables（snochar
（2）primarykey,snamechar（10））;
1）向表中添加属性列status,数据类型为int
altertablesaddStatusint;
2）向表中添加属性列city,数据类型为varchar（20）,并限定其取值范围为“上海”，“北京”，“天津”之一
altertablesaddcityvarchar（20）check（cityin（'上海','北京','天津'））altertablesaltercolumnstatussmallint
3）修改属性列status的数据类型为smallint
altertablesaltercolumnstatussmallint
4）删除（3）题中取值范围的约束
5）删除属性列status
altertablesdropcolumnstatus
如教材85页所示，建立student,sc,course等表,并插入若干数据，完成如下操作：
1）列出没有成绩的学生的学号和课程号
selectSno,CnofromSCwhereGradeisnull
2）列出2号课程成绩在70分到80分学生的学号
selectSnofromSCwhereCno='2'andGradebetween70and80
3）查询所有2005级的学生的姓名，性别和所在系
SelectSname,Ssex,SdeptfromstudentwhereSnolike'2005%%';
4）查询计科系2004级全体学生的所有信息
select*fromStudentwhereSdept='cs'andSnolike'2004%';
5）查询计科系2006级3班和4班学生的姓名和性别
selectSname,SsexfromStudentwhereSnoin（'200603%','200604%'）;
6）查询所有以“数”打头的课程的名称和学分
selectCname,CcreditfromCoursewhereCnamelike'数%'
7）查询数学系所有学生的姓名，性别和出生年份
selectSname,Ssex,2005-SagefromStudentwhereSdept='ma';
8）将course表中名为PASCAL语言的课程更名为“C语言”
updateCourse
setCname='c语言'whereCname='PASCAL语言'
9）将所有课程的学分增加1分
updateCoursesetCcredit=Ccredit+1
10）删除没有选课成绩的选课记录
deletefromSCwhereGrade=0
11）删除“IS”系的所有学生信息
deletefromStudentwhereSdept='IS'
12）删除所有的课程信息
deletefromSC
deletefromCourse
3.主要仪器设备及软件：
（1）PC
（2）MicrosoftSQLServer2005
实验四查询（多表查询，嵌套查询，分组查询）
（验证型实验12学时）
1.目的要求：
实现单表和多表的普通查询和嵌套查询。
包括返回单值的子查询和返回多值的子查询。
使用5个聚合函数以及GROUPBY子句和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）输出所有员工的姓名和工资
Selectename,salaryfromEMp
2）输出薪水少于10000或者大于100000的雇员的名字
Selectenamefromempwheresalary<10000orsalary>100000
3）输出所有姓“欧阳”，且全名为四个字的雇员的姓名和工资
Selectenamefromempwhereenamelike‘欧阳____’
4）输出薪水在20000和50000之间的雇员的名字
Selectenamefromempwheresalarybetween20000and50000
Selectenamefromempwheresalary>=20000andsalary<=50000
5）输出部门名字中含有“_”的所有部门的名字和楼层号
Selectdnamefromdeptwhereenamelike‘%\_%’escape‘\’
6）查询公司的员工数
Selectcount（*）fromemp
7）查询所有还没有部门经理的部门的名字和编号
Selectdid,dnamefromdeptwheremanageeridisnull
8）查询所有已分配楼层的部门的所有信息
Select*fromdeptwherefloornuminnotnull
用连接查询完成如下操作：
职员表：
Emp（eid:
integer;ename:
string,salary:
real）
部门表：
Dept（did:
integer,dname:
string,managerid:
integer,floornum:
integer）
职员与部分的关系表：
Works（eid:
integer,did:
integer）；
1）查询“电视”部门的职工人数
Selectcount（*）fromdept,workswheredept.did=works.didanddept.dname=’电视’
2）输出每个部门的名字和平均工资
select dname,avg（salary）fromdept,works,empwheredept.did=works.didandworks.eid=emp.eidgropbydname
3）查询每个部门的部门编号，及其拥有的雇员的人数
Selectdid,count（eid）fromdept,emp,workswheredept.did=works.didandworks.eid=emp.eidgroupbydid.
4）查询在第10层工作，同时薪水少于￥50000的所有雇员的名字
Selectenamedistinctfromdept,emp,worksdept.did=works.didandworks.eid=emp.eidandfloornum=10andsalary<50000
5）输出同时管理三个或者更多部门的管理者的名字
selectenamefromemp,deptwhereemp.eid=dept.manageridgroupbyename,manageridhavingcount（did）>=3
6）输出管理在同一层上10个以上部门的所有管理者的名字
selectenamefromemp,deptwhereemp.eid=dept.managerid
groupbyfloornum,ename,manageridhavingcount（did）>=10）
7）输出雇员“刘丽”工作的部门的名字
Selectdnamefronmemp,dept,workswheredept.did=works.didandworks.eid=emp.eidandename=’刘丽’
用嵌套查询完成如下操作：
职员表：
Emp（eid:
integer;ename:
string,salary:
real）
部门表：
Dept（did:
integer,dname:
string,managerid:
integer,floornum:
integer）
职员与部分的关系表：
Works（eid:
integer,did:
integer）；
1）查询工资最高的雇员的名字
Selectenamefromempwheresalary=（selectmax（salary）fromemp）
2）查询工资最低的雇员的名字及其所在部门的编号和名字
Selectename,did,dnamefromemp,dept,workswheredept.did=works.didandworks.eid=emp.eidandsalary=（selectmin（salary）fromemp）
3）输出与Santa工作部门相同的所有雇员的所有信息
selectdistinctemp.eid,ename,salaryfromemp,works,deptwhereemp.eid=works.eidandworks.didin（selectdidfromworkswhereeidin（selecteidfromempwhereename='Santa'）
4）找出薪水在20000以上，并且在电视部门或者玩具部门工作的雇员的名字
selectenamefromempwheresalary>=20000andeidin（selecteid
fromworkswheredidin（selectdidfromdeptwheredname='电视'ordname='玩具'））
5）输出与刘丽在同一层工作的雇员的名字
selectenamefromemp,dept,workswhereemp.eid=works.eidand
works.did=dept.didandfloornumin（selectfloornumfromemp,dept,works
whereemp.eid=works.eidandworks.did=dept.didandename='刘丽'
6）输出比所在部门的经理挣的还要多的雇员的名字
selectenamefromempwheresalary>all（selectsalaryfromempjoindeptonemp.eid=dept.manageridwheredidin（selectdidfromworks
whereworks.eid=emp.eid））
7）输出满足如下条件的各个部门的名字：
经理的姓为张，同时他的薪水既不是本部门最高也不是最低
selectdnamefromdept,empwhereeid=manageridandenamelike'张%'
andsalary<（selectmax（salary）fromEmp,workswhereworks.eid=
emp.eidanddept.did=works.did）andsalary>（selectmin（salary）
fromEmp,workswhereworks.eid=emp.eidanddept.did=works.did）
8）输出比“玩具”部门所有职工工资都高的雇员的姓名
selectenamefromempwheresalary>all（selectsalary
fromemp,dept,workswhereemp.eid=works.eidand
works.did=dept.didanddname='玩具'）
9）输出比“电视”部门职工平均工资高的雇员的姓名
selectename,salaryfromEmpwheresalary>all（selectavg（salary）
fromEmpwhereeidin（selecteidfromworkswheredid=
（selectdidfromdeptwheredname='电视'）））
10）找出所有有职工的部门的名字和楼层号
selectemp.eid,dname,floornumfromdeptwheredidin（
selectdistinctdidfromworks
11）查询所有没有职工的部门编号和名字
selectdid,dnamefromdeptwheredidnotin（
selectdistinctdidfromworks）
12）输出同时在玩具部门和糖果部门工作的雇员的名字和薪水
selectenamesalary
fromemp
whereemp.eidin（selecteidfromworkswheredidin（selectdid
fromdeptwheredname='玩具'））intersectselectenamesalaryfromemp
whereemp.eidin（selecteidfromworks
wheredidin（selectdidfromdeptdname='糖果'））
3.主要仪器设备及软件：
（1）PC
（2）MicrosoftSQLServer2005
实验五为表格建立约束，修改约束和查询约束
（验证型实验4学时）
1.目的要求：
使用ALTER语句和CREATE语句建立、修改、删除和查询约束
2.实验内容
执行以下SQL语句，完成随后的操作，若有错误，分析错误原因并改正错误：
Createtablestudent（snochar（9）notnull,snamechar（10）,ssexchar
（2）,sagetinyint,sdeptvarchar（40））;
Createtablecourse（cnochar（4）notnull,cnamevarchar（30）,cpnochar（4）,credittinyint）;
Createtablesc（snochar（9）,ccredictchar（5）,gradenumeric（3,1））;
1）在student表中，使sdept只能取值“计算机科学学院”，“数软学院”，“电子工程学院”，“化学与材料科学学院”
altertablestudentaltercolumnsteptvarchar（40）check（steptin（‘计算机科学学院’,’数软学院’,’电子工程学院’,’化学与材料科学学院’））
答案：
altertablestudentwithnocheck
addconstraintd_checkcheck（sdeptin（'计算机科学学院','数软学院','电子工程学院','化学与材料科学学院'））;
2）在student表中sage有默认值18
答案：
altertablestudent
addconstraintmrzdefault'18'forsage;
3）为student表建立主键
altertablestudent
addconstraintpkprimarykey（sno）
4）为course表建立主键和外键，其中外键约束名为C_FK_CPNO
altertablecourseaddconstraintp_pprimarykey（cno）
altertablecourseaddconstraintC_FK_CPNOforeignkey（cpno）referencescourse（cno）
5）为course表建立检查约束，限定credit的取值只能取3，2，4，5；
altertablecourseaddconstraintc_kcheck（creditin（'3','2','4','5'））
6）为course表建立唯一约束，确保每们课程名字唯一
altertablecourseaddconstraintu_kunique（cname）
7）为sc表建立主键和外键，并给出相应的约束名
altertablescaddconstraintp_kkprimarykey（sno）
altertablescaddconstraintC_creforeignkey（ccre）referencescourse（cno）
注意：
SNO的值不能为空
8）在course表中插入元组（1,数据库，5，4）和（2,数学，null,2）,若不能正确插入，分析原因，给出解决办法
第一条不能正确插入，因为先行课为外键，参照了课程号，必须为已经出现过的课程号。
第二条不能够插入，因为先行课为空，也不满足约束。
9）在sc表中插入元组（95001,1,92）和（95001，3，88），若不能正确插入，分析原因，给出解决办法
insertintosc（sno,ccre,grade）
values（'95001','1','92'）
insertintosc（sno,ccre,grade）
values（'95001','3','88'）
（2）使用系统存储过程，sp_help,sp_helpconstraint等对约束进行查询和管理
查阅联机帮助文档，选中“索引”选项卡，分别输入sp_help,sp_helpconstraint，阅读其帮助信息。
使SC数据库成为当前数据库，执行如下命令，简要解释执行结果
1）Execsp_help
sp_help：
用于显示参数清单和其数据类型，如果执行不带参数的sp_help，则返回当前数据库中现有的所有类型对象的汇总信息。
2）Execsp_helpstudent
3）Execsp_helpcourse
以上两条语句会返回student表和course表的所有信息有包括：
关列对象的其他结果集、针对标识列返回的其他结果集、针对各列返回的其他结果集、针对文件组返回的其他结果集、针对索引返回的其他结果集、针对约束返回的其他结果集、针对执行引用的对象返回的其他结果集、针对存储过程、函数或扩展存储过程返回的其他结果集。
4）Execsp_helpconstaintsc
5）Execsp_helpconstraintstudent
以上两条语句分别显示sc、student表的约束信息。
6）对存储过程sp_help和sp_helpconstraint进行总结，简要解释其用途
sp_help用于显示表中的所有信息，sp_helpconstraint用于显示表的约束信息。
（3）修改约束：
关闭和打开某个约束。
升级会员 