大数据库实验3问题详解.doc

实验三：交互式 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数据操作参考示例：建立一个学生选课数据库，练习对表、视图和索引等数据库对象的各种操作一、数据定义创建学生选课数据库 ST,包括三个基本表，其中 Student表保存学生基本信息， Course表保存课程信息，SC表保存学生选课信息，其结构如下表：表1. Stude nt表结构列名称用途类型长度约束备注Sno学号字符8主键Sn ame字符8Ssex性别字符2Sage年龄整型Sdept所在系字符20Sclass班级字符4表2. Course表结构列名称用途类型长度约束备注Cno】田千口 i=r. 课程号字符4主键Cn ame课程名字符40Cpno先修课程号字符4Ccredit学分整型表3. SC表结构列名称用途类型长度约束备注Sno学号字符8外键Cno】田千口 i=r. 课程号字符4Grade成绩整型1.创建、修改及删除基本表(1) 创建 Student 表CREATE TABLE Student(SnoCHAR (8) PRIMARY KEY ,Sn ameCHAR (8),SsexCHAR (2) NOT NULL,SageINT,SdeptCHAR (20)丿，（2）创建Course 表CREATE TABLE Course(CnoCHAR (4) PRIMARY KEYCnameCHAR (40) NOT NULL,CpnoCHAR (4),CcreditSMALLINT ,丿，（3）创建SC表CREATETABLE SC(Sno CHAR (8) FOREIGN KEY (Sno) REFERENCES Student(Sno),Cno CHAR (4),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.学生基本信息表学号性别年龄所在系班级20100001勇男20CS100120100002晨女19CS100120100021王敏女18MA100220100031\立男19IS100320100003洋女100120100010斌男19IS100520100022明明男19CS1002表5.课程信息表】田千口 i=r. 课程号课程名先修课程号学分1数据库系统原理5642高等数学23管理信息系统144操作系统原理635数据结构746数据处理27C语言4表6.学生选课信息表学号】田千口 i=r. 课程号成绩2010000119220100001285201000013882010000219020100002280201000031201000103(1)插入到 Student表INSERT INTO Student VALUES ('20100001','勇','男',20,'CS','1001')INSERT INTO Student VALUES ('20100002','晨','女',19,'CS','1001')INSERT INTO Student(Sno,Sname,Ssex,SageSdept,Sclass) VALUES ('20100021','王敏：女',18,'MA','1002')INSERT INTO Student(Sno,Sname,Ssex,SageSdept,sclass) VALUES ('20100031','立','男',19,'IS','1003')INSERT INTO Student(Sno,Sname,Ssex,sclass) VALUES ('20100003',洋'，女','1001')检查下列语句中的错误，并改正:INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,sclass) VALUES('20100010',斌，'男','19','IS','1005')INSERT INTO Student VALUES ('20100022','明明',19,'男','CS','1002')(2) 插入到 Course表INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('1','数据库系统原理','5',4) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('2','高等数学',null, 2) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('3','管理信息系统','1',4) 请写出插入其余行的插入语句，并插入数据。

3) 插入到SC表INSERT INTO SC VALUES ('20100001','1',92)INSERT INTO SC VALUES ('20100002','2',80)INSERT INTO SC(Sno,Cno) VALUES ('20100003','1')INSERT INTO SC(Sno,Cno,Grade) VALUES ('20100010','3',null) 请写出插入其余行的插入语句，并运行4) 多行插入到表中创建存一个表，保存学生的学号、和年龄:CREATE TABLE cs_Student(学号 char(8),char(8),年龄 smallint);插入数据行：INSERT INTO cs_Student SELECT Sno,Sname,Sage FROM 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 = 85 WHERE 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）删除计算机科学系所。