数据库原理及应用实验报告

数据库原理及应用实验报告

学院：信息科学与工程学院 班级：计算机2010-1班 学号：3100717109 姓名：薛斌

————————————————————————————————————————

时间： 2012年5月 机房号： 05503J 得分：

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

实验目的：

1．熟悉SQL SERVER环境；

2．掌握数据库和数据表的创建和删除，实践SQL的CREATE、ALTER和DROP命令； 3．掌握数据表的创建和数据记录的插入方法。 实验内容：

2．创建供应系统“GYXT”数据库。(注：可采用可视窗体和SQL命令两种方法创建。) 下面写出实现如下操作的SQL语句： (1) 创建供应系统“GYXT”数据库。 CREATE DATABASE GYXT;

(2) 建立供应商表S。 CREATE TABLE S ( SNO CHAR(4) PRIMARY KEY, SNAME VARCHAR(20) , CITY VARCHAR(20));

(3) 建立零件表P。 CREATE TABLE P (

PNO CHAR(4) PRIMARY KEY, PNAME VARCHAR(20) , COLOR VARCHAR(4) , WEIGHT SMALLINT);

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

JNO CHAR(4) PRIMARY KEY, JNAME VARCHAR(20), CITY VARCHAR(20) );

数据库原理及应用实验报告

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

SNO CHAR(4) NOT NULL, PNO CHAR(4) NOT NULL, JNO CHAR(4) NOT NULL, QTY INT,

PRIMARY KEY(SNO,PNO,JNO),

FOREIGN KEY (SNO) REFERENCES S (SNO),

FOREIGN KEY (PNO) REFERENCES P (PNO), FOREIGN KEY (JNO) REFERENCES J (JNO));

(6) 应用INSERT INTO命令将相应数据写入到供应商表S、零件表P、工程项目表J和供应情况表SPJ四个数据表。

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','康健','南京');

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);

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','半导体厂','南京');

第 1页 ，共11页

数据库原理及应用实验报告

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); 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); INSERT INTO SPJ VALUES ('S5','P6','J4',500);

第 2页 ，共11页

数据库原理及应用实验报告

时间： 2012年5月 机房号： 05503J 得分：

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

实验目的：

1．熟练掌握SQL Server查询分析器的使用方法，加深对标准SQL查询语句的理解。 2．熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。 实验内容：

1．基于实验一创建的教学管理JXGL数据库，实现数据的基本查询操作。 2．基于实验一创建的供应系统GYXT数据库，实现数据的基本查询操作。 实验步骤：

写出下列操作的SQL语句。

1．在教学管理JXGL数据库中进行如下操作： (1) 求数学系学生的学号和姓名。 SELECT Sno,Sname FROM STUDENT

WHERE Sdept='MA';

(2) 求选修了课程的学生学号。 SELECT Sno FROM SC;

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

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

WHERE SC.Cno=COURSE.Cno AND COURSE.Cname='数学' ORDER BY Grade DESC,Sno;

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

SELECT SC.Sno,SC.Grade*0.8

FROM SC,COURSE

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

AND (Grade BETWEEN 80 AND 90);

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

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

第 3页 ，共11页

数据库原理及应用实验报告

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

SELECT Sno,Cno FROM SC

WHERE Grade IS NULL;

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

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

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

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

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

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

(9) 求选修数学课且成绩为90分以上的学生学号、姓名、及成绩。 SELECT STUDENT.Sno,Sname,Grade FROM STUDENT,COURSE,SC

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

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

(10)查询每一门课的间接先行课（即先行课的先行课）。 SELECT COURSE.Cpno FROM COURSE

WHERE COURSE.Cno IN

(SELECT Cpno

FROM COURSE);

第 4页 ，共11页

数据库原理及应用实验报告

2．在供应系统GYXT数据库中进行如下操作： (1) 求供应工程J1零件的供应商号SNO。 SELECT SNO FROM SPJ

WHERE JNO='J1';

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

WHERE JNO='J1' AND PNO='P1';

(3) 统计每种零件的供应总量。SELECT PNO,SUM(QTY) FROM SPJ

GROUP BY PNO;

第 5页 ，共11页

数据库原理及应用实验报告

时间： 2012年5月 机房号： 05503J 得分：

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

实验目的：

1．熟练掌握SQL Server查询分析器的使用方法，加深对标准SQL查询语句的理解。 2．熟练掌握数据查询中的嵌套、分组、统计、计算和组合的操作方法。 实验内容：

1．基于实验一创建的教学管理JXGL数据库，实现数据的高级查询操作。 2．基于实验一创建的供应系统GYXT数据库，实现数据的基本查询操作。 实验步骤：

写出下列操作的SQL语句。

1．在教学管理JXGL数据库中进行如下操作： (1) 求选修了数学的学生的学号和姓名。 SELECT STUDENT.Sno,STUDENT.Sname FROM STUDENT

WHERE STUDENT.Sno IN

(SELECT SC.Sno FROM SC

WHERE SC.Cno IN

(SELECT COURSE.Cno FROM COURSE

WHERE COURSE.Cname='数学'));

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

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

WHERE SC.Cno=COURSE.Cno AND COURSE.Cname='数学' AND SC.Grade > ( SELECT SC.Grade FROM SC,STUDENT,COURSE

WHERE STUDENT.Sname='李勇' AND SC.Sno=STUDENT.Sno AND COURSE.Cname='数学' AND COURSE.Cno=SC.Cno);

(3) 求其他系中年龄小于计算机系年龄最大者的学生。 SELECT STUDENT.Sno,STUDENT.Sname,STUDENT.Sage FROM STUDENT

WHERE STUDENT.Sdept!='MA' AND Sage<

(SELECT MAX(Sage) FROM STUDENT AS X

WHERE X.Sdept='MA');

第 6页 ，共11页

数据库原理及应用实验报告

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

SELECT STUDENT.Sno,STUDENT.Sname,STUDENT.Sage FROM STUDENT

WHERE STUDENT.Sdept!='CS' AND STUDENT.Sage <

(SELECT MIN(Sage) FROM STUDENT AS X

WHERE X.Sdept='CS');

(5) 求选修了数学课的学生姓名。 SELECT STUDENT.Sname FROM STUDENT WHERE STUDENT.Sno IN

(SELECT SC.Sno FROM SC

WHERE SC.Cno IN

(SELECT COURSE.Cno FROM COURSE

WHERE COURSE.Cname='数学' ));

(6) 求没有选修数学课的学生姓名。 SELECT STUDENT.Sname FROM STUDENT

WHERE STUDENT.Sno NOT IN

(SELECT SC.Sno FROM SC

WHERE SC.Cno IN

(SELECT COURSE.Cno FROM COURSE

WHERE COURSE.Cname='数学'));

(7) 查询选修了全部课程的学生的姓名。 SELECT STUDENT.Sname FROM STUDENT

WHERE STUDENT.Sno IN

(SELECT SC.Sno FROM SC

GROUP BY SC.Sno

HAVING COUNT(*)=(SELECT COUNT(*) FROM COURSE));

第 7页 ，共11页

数据库原理及应用实验报告

(8) 求至少选修了学号为“95002”的学生所选修的全部课程的学生学号和姓名。 SELECT DISTINCT X.Sno FROM SC X

WHERE NOT EXISTS(SELECT *FROM SC Y where Y.Sno='95002' AND NOT EXISTS

(SELECT *FROM SC Z where Z.Sno=X.Sno and Z.Cno=Y.Cno));

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

SELECT COUNT(*)AS 选修门课的人数,AVG(SC.Grade) AS 平均分 FROM SC

GROUP BY SC.Cno;

(10)求选修课程在2门以上且都及格的学生号及总平均分。 SELECT SC.Sno,AVG(SC.Grade) AS 平均分 FROM SC

GROUP BY SC.Sno

HAVING COUNT(*)>2 AND MIN(SC.Grade)>=60;

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

SELECT SC.Sno,AVG(SC.Grade) AS 平均分 FROM SC

GROUP BY SC.Sno

HAVING COUNT(*)>2 AND MIN(SC.Grade)>=60 ORDER BY AVG(Grade);

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

SELECT SC.Sno,AVG(SC.Grade) AS 平均成绩,COUNT(*) AS 及格门数 FROM SC

WHERE SC.Grade>=60 GROUP BY SC.Sno

ORDER BY AVG(SC.Grade) DESC,COUNT(*) DESC;

第 8页 ，共11页

数据库原理及应用实验报告

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

SELECT SC.Sno,AVG(SC.Grade),COUNT(*) FROM SC

GROUP BY SC.Sno

HAVING MIN(SC.Grade)>=60

ORDER BY AVG(SC.Grade) DESC,COUNT(*) DESC;

2．在供应系统GYXT数据库中进行如下操作： (1) 求供应工程J1红色零件的供应商号SNO。 SELECT SNO FROM SPJ WHERE PNO IN

(SELECT P.PNO FROM P

WHERE P.COLOR='红');

(2) 求零件供应总量在1000种以上的供应商名字。 SELECT S.SNAME FROM S WHERE S.SNO IN

(SELECT SPJ.SNO FROM SPJ

GROUP BY SPJ.SNO

HAVING SUM(SPJ.QTY)>1000 );

第 9页 ，共11页

数据库原理及应用实验报告

时间： 2012年6月 机房号： 05503J 得分：

实验4 数据完整性

实验目的：

1. 了解SQL Server用约束来维护数据完整性的机制。

2．掌握在企业管理器和查询分析器中如何创建、使用及删除约束的方法。 实验内容：

2．创建人事关系RSGX数据库，并定义职工和部门两个关系模式： 职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码； 部门（部门号，名称，经理名，电话），其中部门号为主码； 要求在模式中完成以下完整性约束条件的定义： (1) 定义每个模式的主码； CREATE DATABASE RSGX;

CREATE TABLE STAFF

(Sno CHAR(10) PRIMARY KEY, Sname VARCHAR(20), Sage SMALLINT,

Sposition VARCHAR(20), Swage int,

Sdeptno CHAR(10));

CREATE TABLE DEPARTMENT

(Deptno CHAR(10) PRIMARY KEY, Deptname VARCHAR(20), Managername VARCHAR(20), Dphonenumber int);

(2) 定义参照完整性； ALTER TABLE STAFF

ADD FOREIGN KEY (Sdeptno) REFERENCES DEPARTMENT (Deptno);

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

ALTER TABLE STAFF ADD CHECK (Sage<60);

第 10页 ，共11页