课程名称 数据库原理 实验 成绩
实验名称 交互式 SQL 语言 学号
姓名
班级
日期
实验目的：
1. 熟悉数据库的交互式 SQL 工具； 2. 熟悉通过 SQL 语言对数据库进行操作；
实验平台：
利用 RDBMS（SQL Server 2005）及其交互查询工具来操作 SQL 语言；
实验内容与步骤：
一、 数据定义 （一）基本表操作 1. 建立基本表 在数据库 TEST 中建立 3 张基本表：Student、Course 和 SC。
（1）
创建学生表 Student，由以下属性组成：学号 SNO(char 型，长度为 4， 主码)，姓名 SNAME(char 型，长度为 8，其值唯一)，性别 SEX（char 型，长度为 2），所在系 DEPTNO(char 型，长度为 2)。
--CREATE TABLE Student
--( --
SNO CHAR(4)PRIMARY KEY, --
SNAME CHAR(8)UNIQUE, --
SEX
CHAR(2), --
DEPTNO CHAR(2),
--)
（2）创建课程表 Course,由以下属性组成：课程号 CNO(char 型,长度为 2，主码)，课程名称 CNAME(char 型，长度为 20，非空)，授课教师编号 TNO（char型，长度为 3，主码），学分 CREDIT(int 型)。
--CREATE TABLE Course --( --
CNO CHAR(2), --
CNAME CHAR(20), --
TNO CHAR(3)PRIMARY KEY, --
CREAIT INT,
--);
（3）创建学生选课表 SC,由以下属性组成：学号 SNO( char 型，长度为 4，主码),课程号 CNO( char 型,长度为 2，主码),成绩 GRADE(int 型)。
--
CREATE TABLE SC --
( --
SNO CHAR(4), --
CNO CHAR(2), --
GRADE INT, --
PRIMARY KEY (SNO,CNO), --
);
（4）创建教师表 Teacher,由以下属性组成：教师编号 TNO(char 型，长度为3，主码),教师姓名 TNAME(char 型，长度为 8,非空)，所在系 DEPTNO（char型，长度为 2）
--
CREATE TABLE Teacher --
( --
TNO CHAR(3)PRIMARY KEY, --
TNAME CHAR(8)NOT NULL, --
DEPTNO CHAR(2) --
)
（5）创建系表 Dept，由以下属性组成：系号 DEPTNO(char 型，长度为 2，主码)，系名 DNAME(char 型，长度为 20,非空)。
CREATE TABLE Dept
(
DEPTNO CHAR(2)PRIMARY KEY,
DNAME
CHAR(20) NOT NULL,
)
2. 修改基本表 在 student 表中加入属性 AGE（int 型）； ALTER TABLE Student
ADD AGE INT;
3. 删除基本表 后边所有操作结束后，删除所创建的基本表；
DROP TABLE Student;
DROP TABLE Course;
DROP TABLE SC;
DROP TABLE DEPT;
DROP TABLE TEACHER;
（二）索引操作 1. 建立索引 （1）
在 Student 表上建立关于属性 SNO 的唯一索引；
CREATE UNIQUE INDEX Stusno ON Student(Sno);
（2）
在 Course 表上建立关于 CNO 的唯一索引；
create unique index Coucno ON Course(Cno);
2. 删除索引 删除上面所建立的两个索引； -drop index Student.stusno
--drop index Course.Coucno;
（三）视图操作 1. 建立视图 在插入数据的 Student 基本表上为计算机系学生的记录建立一个视图CS_STUDENT。
CREATE VIEW CS_STUDENT AS SELECT SNO,SNAME,SEX,DEPTNO,AGE FROM Student WHERE DEPTNO="CS";
2. 删除视图 在操作结束后，删除视图 CS_STUDENT。
DROP VIEW CS_STUDENT;
二、 数据操作
（一）更新操作 1.插入操作 （1）向 Student 表中插入下列数据：
1001，张天,男，20，10
1002，李兰，女，21，10
1003，陈铭，男，21，10
1004，刘茜，女，21，20
1005，马朝阳，男，22，20
（2）向 Course 表中插入下列数据：
1，数据结构,101,4
2，数据库，102，4
3，离散数学，103，4
4，C 语言程序设计，101，2
（3）向 SC 表中插入下列数据：
1001,1,80 1001,2,85 1001,3,78
1002,1,78 1002,2,82 1002,3,86 1003,1,92 1003,3,90 1004,1,87 1004,4,90 1005,1,85 1005,4,92
（4）向 Teacher 表中插入下列数据：
101，张星，10
102，李珊，10
103，赵天应，10
104，刘田，20
（5）向 Dept 表中插入下列数据：
10，计算机科学与技术
20，信息
2.修改数据 将张星老师数据结构课程的学生成绩全部加 2 分；
--UPDATE SC SET GRADE = GRADE +2 --WHERE SC.CNO IN (SELECT CNO FROM Course,Teacher --WHERE Course.TNO=Teacher.TNO AND TNAME="张星" AND Course.cname="数据结构");
3. 删除数据 删除马朝阳同学的所有选课记录。
（二）查询操作
1. 单表查询
（1）查询所有学生的信息； SELECT * FROM Student
（2）查询所有女生的姓名； SELECT SNAME
FROM Student where (SEX="女")
（3）
查询成绩在 80~89 分之间的所有学生的选课记录，查询结果按照成绩降序排列； SELECT SNO,CNO FROM SC WHERE GRADE BETWEEN 80 AND 90 ORDER BY GRADE DESC;
（4）
查询各个系的学生人数； SELECT COUNT (SNO) AS COUNT FROM Student GROUP BY DEPTNO;
2. 连接查询 查询信息系年龄在 21 岁以下的女生的姓名及其年龄； SELECT SNAME,AGE FROM Student,Dept WHERE AGE <21 AND Student.DEPTNO=Dept.DEPTNO
3. 嵌套查询 （1）
查询选修课总学分在 10 学分以下的学生的姓名；
SELECT Sname FROM Student WHERE Student.sno in (
SELECT Student.SNO FROM Student,SC,Course
WHERE Student.sno = SC.SNO AND SC.CNO = Course.CNO
GROUP BY Student.SNO
HAVING SUM (CREAIT)<10 )
（2）
查询各门课程最高成绩的学生的姓名及其成绩； SELECT SNAME,GRADE FROM Student,SC S1 WHERE Student.SNO=S1.SNO AND GRADE = (
SELECT MAX(GRADE)
FROM SC S2
WHERE S2.CNO = S1.CNO
GROUP BY S2.CNO )
（3）
查询选修了 1001 号学生所选修的全部课程的学生的学号； SELECT DISTINCT SNO FROM SC SCX
注意：红色字体的实验内容待讲完相应知识点后在做。
WHERE NOT EXISTS (
SELECT * FROM SC SCY
WHERE SCY.SNO ="1001"
AND NOT EXISTS (
SELECT * FROM SC SCZ
WHERE SCZ.SNO = SCX.SNO
AND SCZ.CNO = SCY.CNO ) )
（4）查询选修了张星老师所开设的全部课程的学生的姓名； SELECT SNAME FROM Student
WHERE EXISTS (
SELECT SC.CNO FROM SC,Course,Teacher,Student
WHERE SC.CNO = Course.CNO
AND Course.TNO = Teacher.TNO AND TNAME ="张星"
AND SC.SNO = Student.SNO )