数据库试验3答案

实验三：交互式SQL语句的使用1、实验目的（1）掌握数据库对象的操作过程，包括创建、修改、删除（2）熟悉表的各种操作，包括插入、修改、删除、查询（3）熟练掌握常用SQL语句的基本语法2、实验平台使用SQL Server提供的Microsoft SQL Server Management Studio工具，交互式使用SQL语句。

3 实验内容及要求选择如下一个应用背景之一：学生选课系统?习题3、4、和5中使用的数据库?其它你熟悉的应用?。

）建立一个数据库和相关的表、索引、视图等数据库对象，练习对表、索引和视图的各种操作1（（2）要求认真进行实验，记录各实验用例及执行结果。

（3）深入了解各个操作的功能。

实验要求包括如下方面的内容：3.1 数据定义1．基本表的创建、修改及删除2．索引的创建3．视图的创建3.2 数据操作完成各类更新操作包括：1．插入数据2．修改数据3. 删除数据3.3 数据查询操作完成各类查询操作1．单表查询2．分组统计3. 连接查询4. 嵌套查询5. 集合查询3.4 数据操作1．创建视图2．视图查询参考示例：建立一个学生选课数据库，练习对表、视图和索引等数据库对象的各种操作。

数据定义一、创建学生选课数据库ST，包括三个基本表，其中Student表保存学生基本信息，Course表保存课程信息，SC表保存学生选课信息，其结构如下表：表1. Student表结构列名用类长约备主字学Sn字姓Snam字符2性别Ssex整型年龄Sage字符20Sdept所在系字符4Sclass班级表2. Course表结构1．创建、修改及删除基本表（1Student表）创建Student TABLE CREATE, CHAR (Sno(8)PRIMARY KEY8 Sname CHAR(),NOT NULL,)Ssex CHAR(2,Sage INT CHAR(20)Sdept);）创建Course表（2Course TABLECREATE(Cno KEY PRIMARY,)CHAR(4NOT(Cname CHAR40)NULL, 4(), CHAR CpnoSMALLINT,Ccredit);（表SC3）创建SC CREATETABLE 8CHAR Sno(()FOREIGNKEY StudentSno(),REFERENCES)(SnoCno 4(CHAR),Grade,SMALLINT);4（）创建员工表EmployeeCREATE TABLE Employee(编号CHAR(8)PRIMARY KEY,姓名VARCHAR(8)not null部门CHR（40）,工资numeric(8,2),生日datetime，职称char(20),);指出该语句中的错误并改正后执行。

（5）检查表是否创建成功SELECT*FROM StudentSELECT*FROM CourseSELECT*FROM SCSELECT*FROM Employee（6）修改表结构及约束增加班级列?ALTER TABLE Student ADD Sclass char(4)修改年龄列?ALTER TABLE Student ALTER COLUMN Sage smallint 增加约束?ALTER TABLE Course ADD UNIQUE(Cname)（7）删除表DROP TABLE Employee2．创建索引（1）为Course表按课程名称创建索引CREATE INDEX iCname On Course(Cname)（2）为Student表按学生姓名创建唯一索引CREATE UNIQUE INDEX iSname ON Student(Sname)（3）为SC表按学号和课程号创建聚集索引CREATE CLUSTERED INDEX iSnoCno On SC(Sno,Cno desc)（4）为Course表按课程号创建唯一索引请自己完成该操作3．创建视图建立信息系学生的视图：CREATE VIEW IS_StudentASSELECT Sno,Sname,Sage FROM StudentWHERE Sdept='IS';3.2 数据操作1．插入数据将如下表格中的数据分别插入到数据库相应的表中：表4.学生基本信息表班100100100100100100100表5.课程信息表课程号课程名先修课程号学分41数据库系统原理562高等数学24管理信息系统31346操作系统原理475数据结构26数据处理4语言7C表6.学生选课信息表学号课程号成绩19220100001852********883201000019020100002180220100002201000031201000103表1）插入到Student（'1001'),男',20,'CS'INTOStudent VALUES('20100001','李勇','INSERT19,'CS','1001')'VALUES('20100002','刘晨,'女',Student INSERT INTO女','VALUES,Sdept,Sclass)('20100021','王敏,INSERTINTO Student(Sno,Sname,SsexSage,'1002')',18,'MA'男VALUES('20100031','张立',',(INSERT INTO StudentSno,Sname,Ssex,SageSdept,sclass)),'1003'',19,'IS'','1001')'VALUES,INSERT INTO Student(SnoSname,Ssex,sclass)('20100003','刘洋',女检查下列语句中的错误，并改正：男,'INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,sclass) VALUES('20100010',赵斌','19','IS','1005')'1002'19INSERT INTO StudentVALUES('20100022','张明明',,'男','CS',)（2）插入到Course表4)'数据库系统原理','5',VALUES,,INTO Course(CnoCname,CpnoCcredit)('1',INSERTnull,2),,Ccredit)VALUES('2''高等数学',CpnoCno INTOINSERT Course(,Cname,),管理信息系统''1',4,CpnoCno INTOINSERT Course(,Cname,,Ccredit)VALUES('3''请写出插入其余行的插入语句，并插入数据。

3）插入到SC表（'1'VALUESINTOINSERT SC('20100001',,92) 80),(INTO SC VALUES'20100002','2'INSERT),(CnoSnoSC INSERT INTO(,)VALUES'20100003''1',null),(GradeCnoSnoSC INTOINSERT (,,)VALUES'20100010''3'请写出插入其余行的插入语句，并运行。

）多行插入到表中（4创建存一个表，保存学生的学号、姓名和年龄：CREATE cs_Student TABLE(char学号 8(),姓名char(8),年龄smallint);插入数据行：INSERT INTO cs_StudentSELECT Sno,Sname,SageFROM student Where Sdept='CS';（5）检查插入到表中的数据SELECT*FROM StudentSELECT*FROM CourseSELECT*FROM SC2．修改数据（1）将学生20100001的年龄改为22岁。

UPDATE student SET Sage= 22 WHERE Sno='20100001';（2）将所有学生的年龄增加一岁。

UPDATE Student SET Sage=Sage+1（3）填写赵斌同学的管理信息系统课程的成绩UPDATE SC SET Grade= 85WHERE Sno='20100010'AND Cno='3'（4）将计算机科学系全体学生的成绩加5分UPDATE sc SET Grade=Grade+ 5WHERE'CS'=(select Sdept from student where student.Sno=sc.Sno);（5）请自己完成如下操作?将刘晨同学的2号课程成绩修改为80?将“20100021”同学的学号修改为“20100025”（6）检查数据是否修改3. 删除数据（1）删除学号为201000022的学生记录DELETE FROM Student WHERE Sno='20100022'（2）删除学号20100001学生的1号课程选课记录将选课信息复制到一个临时表tmpSC中：SELECT*INTO tmpSC FROM SC在tmpSC中执行删除操作：DELETE FROM tmpSC WHERE Sno='20100001'and Cno='1'（3）删除临时表中20100002学生的全部选课记录请自己完成该操作。

（4）删除计算机科学系所有学生的选课记录DELETE FROM tmpSC WHERE'CS'=(select Sdept from student where student.Sno=tmpSC.Sno);（5）删除全部选课记录DELETE FROM tmpSC（6）检查数据是否删除3.3 数据查询操作完成如下查询操作：1．单表查询（1）按指定目标列查询?查询学生的详细记录：SELECT*FROM Student;?查询学生的学号、姓名和年龄SELECT Sno,Sname,Sage FROM Student;（2）目标列包含表达式的查询?查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。