《数据库原理及应用》实验指导

实验1 创建数据库与数据表

下面写出实现如下操作的SQL语句：

(1) 创建供应系统“GYXT”数据库。

CREATE DATABASE GYXT

ON

(

NAME=GYXT_data,

FILENAME=’D:\\GYXT.mdf’,

SIZE=10,

MAXSIZE=50,

FILEGROWTH=5)

LOG ON

(

NAME=GYXT_Log,

FILENAME=’D:\\GXTYData.ldf’SIZE=5,

MAXSIZE=25,

FILEGROWTH=5);

(2) 建立供应商表S。

CREATE TABLE S

( SNO char(5) not null unique,

,

1

SNAME char(20) not null unique,

CITY char(20));

INSERT INTO S VALUES('S1','精益','天津');

INSERT INTO S VALUES('S2','万胜','北京');

INSERT INTO S VALUES('S3','东方','北京');

INSERT INTO S VALUES('S4','丰泰隆','上海');

INSERT INTO S VALUES('S5','康健','南京');

(3) 建立零件表P。

CREATE TABLE P

( PNO char(2) not null PRIMARY KEY(PNO),

PNAME char(20),

2

COLOR char(20),

WEIGHT smallint);

INSERT INTO P VALUES('P1','螺母','红',12);

INSERT INTO P VALUES('P2','螺栓','绿',17);

INSERT INTO P VALUES('P3','螺丝刀','蓝',14);

INSERT INTO P VALUES('P4','螺丝刀','红',14);

INSERT INTO P VALUES('P5','凸轮','蓝',40);

INSERT INTO P VALUES('P6','齿轮','红',30);

(4) 建立工程项目表J。

CREATE TABLE J

( JNO char(5) not null unique,

3

JNAME char(20) not null unique,

CITY char(20));

INSERT INTO J VALUES('J1','三建','北京');

INSERT INTO J VALUES('J2','一汽','长春');

INSERT INTO J VALUES('J3','弹簧厂','天津');

INSERT INTO J VALUES('J4','造船厂','天津');

INSERT INTO J VALUES('J5','机车厂','唐山');

INSERT INTO J VALUES('J6','无线电厂','常州');

INSERT INTO J VALUES('J7','半导体厂','南京');

(5) 建立供应情况表SPJ。

CREATE TABLE SPJ

4

( SNO char(5),

PNO char(5),

JNO char(5),

QTY int);

INSERT INTO SPJ VALUES('S1','P1','J1','200');

INSERT INTO SPJ VALUES('S1','P1','J3','100');

INSERT INTO SPJ VALUES('S1','P1','J4','700');

INSERT INTO SPJ VALUES('S1','P2','J2','100');

INSERT INTO SPJ VALUES('S2','P3','J1','400');

INSERT INTO SPJ VALUES('S2','P3','J2','200');

INSERT INTO SPJ VALUES('S2','P3','J4','500');

5

INSERT INTO SPJ VALUES('S2','P3','J5','400');

INSERT INTO SPJ VALUES('S2','P5','J1','400');

INSERT INTO SPJ VALUES('S2','P5','J2','100');

INSERT INTO SPJ VALUES('S3','P1','J1','200');

INSERT INTO SPJ VALUES('S3','P3','J1','200');

INSERT INTO SPJ VALUES('S4','P5','J1','100');

INSERT INTO SPJ VALUES('S4','P6','J3','300');

INSERT INTO SPJ VALUES('S4','P6','J4','200');

INSERT INTO SPJ VALUES('S5','P2','J4','100');

INSERT INTO SPJ VALUES('S5','P3','J1','200');

INSERT INTO SPJ VALUES('S5','P6','J2','200');

6

INSERT INTO SPJ VALUES('S5','P6','J4','500');

实验2 简单查询和连接查询

首先，写出下列操作的SQL语句，然后进入到SQL Server查询分析器中调试结果，并将结果抄写下来。

1．在教学管理JXGL数据库中进行如下操作：

(1) 求数学系学生的学号和姓名。

SELECT Sno,Sname

FROM STUDENT

WHERE Sdept='MA'

调试结果：

Sno Snanme

1 95003 王敏

7

(2) 求选修了课程的学生学号。

SELECT Sno FROM STUDENT

WHERE Sno not in(select Sno from SC )

调试结果：

Sno

1 95003

2 95004

(3) 求选修了数学课的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。

SELECT STUDENT.Sno,Grade

from STUDENT,SC,COURSE

WHERE STUDENT.Sno=SC.Sno and COURSE.Cno=SC.Cno and Cname like ('数学' )

8

order by Grade desc,STUDENT.Sno

调试结果：

Sno Grade

1 95002 90

2 95001 85

(4) 求选修数学课其且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数0.8输出。

SELECT DISTINCT Sno,0.8*Grade AS Grade FROM SC

WHERE SC.Cno='2' AND SC.Grade BETWEEN 80 AND 90

调试结果：

Sno Grade

1 95001 68.0

9

2 95002 72.0

(5) 求数学系或计算机系姓刘的学生的信息。

SELECT * FROM STUDENT

WHERE (Sdept='MA' OR Sdept='IS')AND Sname LIKE'刘%'

调试结果：

Sno Sname Ssex Sage Sdept

1 95002 刘晨 F 19 IS

(6) 求缺少了成绩的学生的学号和课程号。

SELECT Sno,Cno FROM SC

WHERE SC.Grade IS NULL

调试结果：

10

Sno Cno

(7) 查询每个学生的情况以及他（她）所选修的课程。

SELECT STUDENT.Sno,Sname,Ssex,Sage,Sdept,Cname FROM STUDENT,COURSE,SC

WHERE SC.Sno=STUDENT.Sno AND SC.Cno=COURSE.Cno

调试结果：

Sno Sname Ssex Sage Sdet Cname

1 95001 李勇 M 20 CS 数据库

2 95001 李勇 M 20 CS 数学

3 95001 李勇 M 20 CS 信息系统

4 95002 刘晨 F 19 IS 数学

5 95002 刘晨 F 19 IS 信息系统

11

(8) 求学生的学号、姓名、选修的课程名及成绩。

SELECT STUDENT.Sno,Sname,Cname,Grade FROM STUDENT,COURSE,SC

WHERE SC.Sno=STUDENT.Sno AND SC.Cno=COURSE.Cno

调试结果：

Sno Sname Cname Grade

1 95001 李勇 数据库 92

2 95001 李勇 数学 85

3 95001 李勇 信息系统 88

4 95002 刘晨 数学 90

5 95002 刘晨 信息系统 80

(9) 求选修数学课且成绩为90分以上的学生学号、姓名、及成绩。

12

SELECT STUDENT.Sno,Sname,Grade FROM STUDENT,COURSE,SC

WHERE SC.Sno=STUDENT.Sno AND SC.Cno=COURSE.Cno

AND COURSE.Cname='数学' AND SC.Grade>=90

调试结果：

Sno Sname Grade

1 95002 刘晨 90

(10)查询每一门课的间接先行课（即先行课的先行课）。

SELECT T1.CNO, T1.CPNO,T2.CPNO FROM COURSE T1.CPNO=T2.CNO

调试结果：

CNO CPNO CPNO

1 1 5 7

1

3 T1,COURSE T2 WHERE 2 3 1 5

3 4 6

4 5 7 6

5 7 6

2．在供应系统GYXT数据库中进行如下操作：

(1) 求供应工程J1零件的供应商号SNO。

SELECT DISTINCT SNO FROM SPJ

WHERE SPJ.JNO='J1'

调试结果：

SNO

1 S1

14

2 S2

3 S3

4 S4

5 S5

(2) 求供应工程J1零件P1的供应商号SNO。

SELECT DISTINCT SNO FROM SPJ

WHERE SPJ.JNO='J1' AND SPJ.PNO='P1'

调试结果：

SNO

1 S1

2 S3

15

(3) 统计每种零件的供应总量。

SELECT DISTINCT PNO,SUM(QTY) AS totalQTY FROM SPJ

GROUP BY PNO

调试结果：

PNO totalQTY

1 P1 1200

2 P2 200

3 P3 1900

4 P4 600

5 P5 1200

6 实验3 嵌套查询和集合查询

1

首先，写出下列操作的SQL语句，然后进入到SQL Server查询分析器中调试结果，并将结果抄写下来。

1．在教学管理JXGL数据库中进行如下操作：

(1) 求选修了数学的学生的学号和姓名。

ELECT STUDENT.Sno,Sname FROM STUDENT,SC,COURSE

WHERE COURSE.Cno=SC.Cno AND SC.Sno=STUDENT.Sno AND COURSE.Cname='数学'

调试结果：

Sno Sname

1 95001 李勇

2 95002 刘晨

(2) 求数学课程成绩高于李勇的学生学号和成绩。

SELECT STUDENT.Sno,Grade FROM STUDENT,SC,COURSE

17

WHERE COURSE.Cno=SC.Cno AND SC.Sno=STUDENT.Sno AND COURSE.Cname='数学'

AND SC.Grade>(SELECT Grade FROM STUDENT,SC,COURSE

WHERE COURSE.Cno=SC.Cno AND SC.Sno=STUDENT.Sno

AND COURSE.Cname='数学' AND STUDENT.Sname='李勇')

调试结果：

Sno Grade

1 95002 90

(3) 求其他系中年龄小于计算机系年龄最大者的学生。

SELECT Sname

FROM STUDENT

WHERE Sage>ALL(SELECT Sage

18

FROM SC

WHERE Sdept='IS')

AND Sdept<>'IS'

调试结果：

Sname

1 李勇

2 王敏

(4) 求其他系中比计算机系学生年龄都小的学生。

select *

from STUDENT

where Sage < (select min(Sage) FROM STUDENT WHERE Sdept LIKE 'IS' )

19

AND Sdept NOT LIKE 'IS'

(5) 求选修了数学课的学生姓名。

SELECT STUDENT.Sname

FROM STUDENT,SC

WHERE SC.Cno=2 AND STUDENT.Sno=SC.Sno

调试结果：

Sname

1 李勇

2 刘晨

20

(6) 求没有选修数学课的学生姓名。

SELECT DISTINCT Sname FROM STUDENT

WHERE ('2' NOT IN(SELECT SC.Cno FROM SC

WHERE STUDENT.Sno=SC.Sno))

调试结果：

Sname

1 王敏

2 张立

(7) 查询选修了全部课程的学生的姓名。

SELECT SNO,Sname FROM STUDENT WHERE NOT EXISTS (

SELECT * FROM COURSE WHERE NOT EXISTS (

21

SELECT * FROM SC WHERE SNO=STUDENT.SNO AND CNO=COURSE.CNO))

(8) 求至少选修了学号为“95002”的学生所选修的全部课程的学生学号和姓名。

SELECT Sno,Sname

FROM STUDENT

WHERE (EXISTS (SELECT *

FROM SC

WHERE Sno=STUDENT.Sno

AND Cno=ANY (SELECT Cno

FROM SC

WHERE Sno=95002

22

)))

调试结果：

Sno Sname

1 95001 李勇

2 65002 刘晨

(9) 求选修各门课的人数及平均成绩。

SELECT count(*) as num,avg(Grade) as avg_grade FROM SC

group by Cno

调试结果：

num avg_grade

1 1 92

23

2 2 87

3 2 84

(10)求选修课程在2门以上且都及格的学生号及总平均分。

SELECT Sno, AVG(Grade) AS AveScore

From SC

WHERE EXISTS (SELECT *

FROM STUDENT

WHERE NOT EXISTS (SELECT *

FROM SC

WHERE Sno=STUDENT.Sno

AND Cno <60))

24

GROUP BY Sno

HAVING (COUNT(*)>=2)

调试结果：

Sno AveScore

1 95001 88

2 95002 85

(11)求95级学生中选修课程在2门以上且都及格的学生号及总平均分，并按平均成绩排序。

SELECT Sno, AVG(Grade) AS AveScore

From SC

WHERE EXISTS (SELECT *

FROM STUDENT

25

WHERE NOT EXISTS (SELECT *

FROM SC

WHERE Sno=STUDENT.Sno

AND Cno <60))

AND Sno LIKE '95%'

GROUP BY Sno

HAVING (COUNT(*)>=2)

调试结果：

Sno AveScore

1 95001 88

2 95002 85

26

(12)统计每个人及格的成绩的平均值，及格的门数，结果按平均成绩降序，及格门数降序排列。

SELECT Sno, COUNT(*) AS C_Num, AVG(Grade) AS AveScore

FROM SC

WHERE Grade>=60

GROUP BY Sno

ORDER BY AveScore DESC, C_Num DESC

调试结果：

Sno C_Num AveScore

1 95001 3 88

2 95002 2 85

(13)统计所有课程均及格学生的平均成绩，及格的门数，结果按平均成绩降序，及格门数降序排列。

27

SELECT Sno, COUNT(*) AS C_Num, AVG(Grade) AS AveScore

FROM SC

WHERE EXISTS (SELECT *

FROM STUDENT

WHERE NOT EXISTS (SELECT *

FROM SC

WHERE Sno=STUDENT.Sno

AND Cno <60))

GROUP BY Sno

ORDER BY AveScore DESC, C_Num DESC

调试结果：

28

Sno C_Num AveScore

1 95001 3 88

2 95002 2 85

2．在供应系统GYXT数据库中进行如下操作：

(1) 求供应工程J1红色零件的供应商号SNO。

SELECT SNO

FROM SPJ

WHERE PNO=ANY( SELECT PNO

FROM P

WHERE COLOR='红')

AND JNO='J1'

29

调试结果：

SNO

1 S1

2 S2

(2) 求零件供应总量在1000种以上的供应商名字。

select SNAME

FROM S

WHERE SNO IN(SELECT SNO FROM SPJ GROUP BY SNO

HAVING SUM(QTY)>=1000)

30

实验4 数据完整性

2．创建人事关系RSGX数据库，并定义职工和部门两个关系模式：

职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；

部门（部门号，名称，经理名，电话），其中部门号为主码；

要求在模式中完成以下完整性约束条件的定义：

(1) 定义每个模式的主码；

(2) 定义参照完整性；

(3) 定义职工年龄不得超过60岁。

create database RSGX

create table department

(Dno char(10) primary key,

31

Dname char(10),

Dmanager char(10),

Dphone char(10))

create table employee

(Eno char(10),

Ename char(10),

Eage char(4),

Eduty char(10),

Elaborage char(10),

Dno char(10) not null foreign key references department(Dno),

check(Eage<=60),

32

primary key(Eno))

33