实验目的： 

通过交互式SQL的使用， 掌握数据库的创建、插入、更新、查询等基本操作

实验要求：

1 创建Student数据库（oracle可以不创建新的数据库，直接创建下列表就可以了）

包括Students,Courses,SC表，表结构如下：

Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)

Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)

SC(SNO,CNO,GRADE)

(注：下划线表示主键，斜体表示外键)，并插入一定数据。

2 完成如下的查询要求及更新的要求。

（1）查询身高大于1.80m的男生的学号和姓名；

（2）查询计算机系秋季所开课程的课程号和学分数；

（3）查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩；

（4）查询至少选修一门电机系课程的女生的姓名（假设电机系课程的课程号以EE开头）；

（5）查询每位学生已选修课程的门数和总平均成绩；

（6）查询每门课程选课的学生人数,最高成绩,最低成绩和平均成绩；

（7）查询所有课程的成绩都在80分以上的学生的姓名、学号、且按学号升序排列；

（8）查询缺成绩的学生的姓名，缺成绩的课程号及其学分数；

（9）查询有一门以上(含一门)三个学分以上课程的成绩低于70分的学生的姓名；

（10）查询1984年~1986年出生的学生的姓名,总平均成绩及已修学分数。

（11） 在STUDENT和SC关系中，删去SNO以’01’开关的所有记录。

（12）在ＳＴＵＤＥＮＴ关系中增加以下记录：

 何平　　女　　1987-03-02　　1.62>

 向阳　　男　　1986-12-11　　1.75>

（13）将课程CS-221的学分数增为３，讲课时数增为６０

3．补充题：

(1) 统计各系的男生和女生的人数。

(2) 列出学习过‘编译原理’，‘数据库’或‘体系结构’课程，且这些课程的成绩之一在90分以上的学生的名字。

(3) 列出未修选‘电子技术’课程，但选修了‘数字电路’或‘数字逻辑’课程的学生数。

(4) 按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用NULL表示。

(5) 列出平均成绩最高的学生名字和成绩。(SELECT句中不得使用TOP n子句)

4．选做题：

对每门课增加“先修课程”的属性，用来表示某一门课程的先修课程，每门课程应可记录多于一门的先修课程。要求：

1) 修改表结构的定义，应尽量避免数据冗余，建立必要的主键，外键。

2) 设计并插入必要的测试数据，完成以下查询：

列出有资格选修数据库课程的所有学生。(该学生已经选修过数据库课程的所有先修课，并达到合格成绩。)

注意：须设计每个查询的测试数据，并在查询之前用INSERT语句插入表中。

【实验环境】

PC机，WINDOWS操作系统，Oracle  或Microsoft SQL Server 数据库

一、实验步骤：

（1）启动oracle服务

      （2）管理工具->服务->所有oracle开头的服务（除去禁用）->连接数据库

（3）可以用OEM/sqlplus连上数据库

在DOS环境下，输入sqlplus,

用户名 sys as sysdba

密码abc123

二、实验数据：

      VALUES('0102105','李四','男','1985-04-01',1.86,'计算机系')；

VALUES('0401110','王五','女','1986-02-02',1.65,'电机系');

VALUES('0401111','赵六','男','1987-04-02',1.60,'电机系');

VALUES('CS-220','编译原理',30,4,'秋季');

VALUES('CS-221','体系结构',40,2,'秋季');

VALUES('CS-222','数据库',60,4,'秋季');

VALUES('EE-110','电子技术',30,4,'秋季');

VALUES('EE-111','数字逻辑',30,3,'秋季');

VALUES('EE-112','数字电路',30,4,'秋季');

VALUES('0102101','张三','男','1988-01-01',1.70,'计算机系')；

三、实验主要过程：

1.创建表

CREATE TABLE Students

(

 SNO char(7) PRIMARY KEY,

 SNAME varchar(30) UNIQUE,  //verchar2()

 SEX char(2) UNIQUE,

 BDATE date,

 HEIGHT number(2,2),

 DEPARTMENT varchar(50)

 );

CREATE TABLE Courses

(

 CNO varchar(20) PRIMARY KEY,

 CNAME varchar(30) UNIQUE,

 LHOUR int,

 CREDIT int,

 SEMESTER varchr(50)

 );

CREATE TABLE SC

(

 SNO char(7),

 CNO varchar(20),

 GRADE smallint,

 PRIMARY KEY(SNO,CNO),

 FOREIGN KEY(SNO) REFERENCES Students(SNO),

 FOREIGN KEY(CNO) REFERENCES Courses(CNO)

);

//插入测试数据

INSERT

INTO Students

VALUES('0102101','张三','男','1988-01-01',1.70,'计算机系')；

INSERT

INTO Students

VALUES('0102105','李四','男','1985-04-01',1.86,'计算机系')；

INSERT

INTO Students

VALUES('0401110','王五','女','1986-02-02',1.65,'电机系');

INSERT

INTO Students

VALUES('0401111','赵六','男','1987-04-02',1.60,'电机系');

INSERT

INTO Courses

VALUES('CS-220','编译原理',30,4,'秋季');

INSERT

INTO Courses

VALUES('CS-221','体系结构',40,2,'秋季');

INSERT

INTO Courses

VALUES('CS-222','数据库',60,4,'秋季');

INSERT

INTO Courses

VALUES('EE-110','电子技术',30,4,'秋季');

INSERT

INTO Courses

VALUES('EE-111','数字逻辑',30,3,'秋季');

INSERT

INTO Courses

VALUES('EE-112','数字电路',30,4,'秋季');

INSERT

INTO SC

VALUES('0102101','CS-220',);

INSERT

INTO SC

VALUES('0102101','CS-221',90);

INSERT

INTO SC

VALUES('0102101','CS-222',97);

INSERT

INTO SC

VALUES('0102105','CS-220',66);

INSERT

INTO SC

VALUES('0102105','CS-221',70);

INSERT

INTO SC

VALUES('0102105','CS-222',60);

INSERT

INTO SC

VALUES('0401110','EE-110',NULL);

INSERT

INTO SC

VALUES('0401110','EE-111',80);

INSERT

INTO SC

VALUES('0401110','EE-112',90);

INSERT

INTO SC

VALUES('0401111','EE-110',90);

INSERT

INTO SC

VALUES('0401111','EE-111',);

INSERT

INTO SC

VALUES('0401111','EE-112',90);

2 完成如下的查询要求及更新的要求。

（1）查询身高大于1.80m的男生的学号和姓名；

SELECT SNO,SNAME

FROM Students

WHERE HEIGHT>1.80 AND SEX='男';

（2）查询计算机系秋季所开课程的课程号和学分数；

SELECT CNO,CREDIT

FROM Courses

WHERE SEMESTER='秋季' AND DEPARTMENT='计算机系';

（3）查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩；

SELECT s.SNAME,SC.CNO,c.CREDIT,SC.GRADE

FROM Students s

     INNER JOIN SC ON SC.SNO=s.SNO

     INNER JOIN Courses c ON sc.CNO=c.CNO

WHERE s.DEPARTMENT='计算机系' AND s.SEX='男' AND c.SEMESTER='秋季';

（4）查询至少选修一门电机系课程的女生的姓名（假设电机系课程的课程号以EE开头）；

SELECT DISTINCT s.sname

FROM Students s,sc

WHERE s.sno=sc.sno AND s.sex='女' AND sc.cno like 'EE%';

（5）查询每位学生已选修课程的门数和总平均成绩；

SELECT count(c.CNO) AS Cnum,

       avg(SC.GRADE) AS SAvgGrade

FROM students s

     INNER JOIN SC ON sc.SNO=s.SNO

     INNER JOIN Courses c ON sc.CNO=c.CNO

GROUP BY s.SNO

（6）查询每门课程选课的学生人数,最高成绩,最低成绩和平均成绩；

SELECT CNAME,COUNT(CNO),MAX(GRADE),MIN(GRADE),AVG(GRADE)

FROM Students

     NATURAL JOIN sc NATURAL JOIN Courses

GROUP BY CNAME;

（7）查询所有课程的成绩都在80分以上的学生的姓名、学号、且按学号升序排列；

SELECT SNAME,SNO

FROM Students NATURAL JOIN

(SELECT SNO,MIN(GRADE) AS MINI

FROM SC

GROUP BY SNO)

WHERE MINI>80

ORDER BY(SNO);

（8）查询缺成绩的学生的姓名，缺成绩的课程号及其学分数；

SELECT SNAME,Courses.CNO,CREDIT

FROM Students,Courses,SC

WHERE Students.SNO=SC.SNO AND Courses.CNO=SC.CNO AND GRADE IS NULL

（9）查询有一门以上(含一门)三个学分以上课程的成绩低于70分的学生的姓名；

SELECT SNAME

FROM Students,Courses,SC

WHERE Students.SNO=SC.SNO AND Courses.SNO=SC.SNO AND CREDIT>=3 AND GRADE<70

（10）查询1984年~1986年出生的学生的姓名,总平均成绩及已修学分数。

SELECT SNAME,AVG(GRADE),SUM(CREDIT)

FROM Students NATURAL JOIN SC NATURAL JOIN Courses

WHERE BDATE between '1984-00-00' AND '1987-00-00'

GROUP BY SNAME;

（11） 在STUDENT和SC关系中，删去SNO以’01’开关的所有记录。

DELETE SC

WHERE SNO LIKE '%01%'

DELETE Students

WHERE SNO LIKE '%01%'

（12）在ＳＴＵＤＥＮＴ关系中增加以下记录：

<0409101  何平　　女　　1987-03-02　　1.62>

<0408130  向阳　　男　　1986-12-11　　1.75>

INSERT

INTO Students

VALUES('0409101','何平','女','1987-03-02',1.62,'')

INSERT

INTO Students

VALUES('0408130','向阳','男','1986-12-11',1.75,'')

（13）将课程CS-221的学分数增为３，讲课时数增为６０

UPDATE Courses

SET CREDIT=3

WHERE CNO='CS-221'

UPDATE Courses

SET LHOUR=60

WHERE CNO='CS-221'

3.补充题

(1) 统计各系的男生和女生的人数。

SELECT

DEPARTMENT,

SUM(CASE WHEN SEX='男' THEN 1 ELSE O END),

SUM(CASE WHEN SEX='女' THEN 1 ELSE 0 END),

COUNT(SNO) AS SNUM //[AS]

FROM Students

GROUP BY DEPARTMENT

ORDER BY DEPARTMENT;

(2) 列出学习过‘编译原理’，‘数据库’或‘体系结构’课程，且这些课程的成绩之一在90分以上的学生的名字。

SELECT SNAME

FROM Students NATURAL JOIN SC NATURAL JOIN Courses

WHERE CNAME='编译原理' OR CNAME='数据库' OR CNAME='体系结构' AND GRADE>90

(3) 列出未修选‘电子技术’课程，但选修了‘数字电路’或‘数字逻辑’课程的学生数。

SELECT COUNT(DISTINCT SC.SNO)

FROM Courses,SC

WHERE SC.SNO NOT IN

(

SELECT SC.SNO

FROM Courses,SC

WHERE Courses.CNO=SC.CNO AND CNAME='电子技术'

 )

AND Courses.CNO=SC.CNO

AND SC.CNO IN

(

SELECT CNO

FROM Courses

WHERE CNAME='数字逻辑' OR CNAME='数字电路'

 )

 (4) 按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用NULL表示

SELECT DISTINCT

COURSES.CNO.COURSES.CNAME,SNO,GRADE                       

FROM COURSES LEFT JOIN SC ON (COURSES.CNO=SC.CNO)

GROUP BY COURSES.CNO.COURSES.CNAME,SNO,GRADE

ORDER BY COURSES.CNO.COURSES.CNAME,SNO,GRADE

(5) 列出平均成绩最高的学生名字和成绩。(SELECT句中不得使用TOP n子句)

SELECT SNAME,r

FROM (SELECT SNAME,AVG(GRADE) as 

             FROM STUDENTS,SC

             WHERE STUDENTS.SNO=SC.SNO

            GROUP BY SNAME,STUDENTS.SNO

            ORDER BY r DESC)

WHERE ROWNUM=1;

成绩：           指导教师签名：

                                               批阅日期：