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

数据库原理及应用实验报告数据库原理及应用实验报告学院：信息科学与工程学院 班级：计算机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,半导体厂,南京);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);时间： 2012年5月 机房号： 05503J 得分： 实验2 简单查询和连接查询实验目的：1熟练掌握SQL Server查询分析器的使用方法，加深对标准SQL查询语句的理解。2熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。实验内容：1基于实验一创建的教学管理JXGL数据库，实现数据的基本查询操作。2基于实验一创建的供应系统GYXT数据库，实现数据的基本查询操作。实验步骤： 写出下列操作的SQL语句。1在教学管理JXGL数据库中进行如下操作：(1) 求数学系学生的学号和姓名。SELECT Sno,SnameFROM STUDENTWHERE Sdept=MA;(2) 求选修了课程的学生学号。SELECT SnoFROM SC;(3) 求选修了数学课的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。SELECT SC.Sno,SC.GradeFROM SC,COURSEWHERE SC.Cno=COURSE.Cno AND COURSE.Cname=数学ORDER BY Grade DESC,Sno;(4) 求选修数学课其且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数0.8输出。SELECT SC.Sno,SC.Grade*0.8FROM SC,COURSEWHERE SC.Cno=COURSE.Cno AND Cname=数学 AND (Grade BETWEEN 80 AND 90);(5) 求数学系或计算机系姓刘的学生的信息。SELECT *FROM STUDENTWHERE Sname LIKE 刘% AND (Sdept=MA OR Sdept=CS);(6) 求缺少了成绩的学生的学号和课程号。SELECT Sno,CnoFROM SCWHERE Grade IS NULL;(7) 查询每个学生的情况以及他（她）所选修的课程。SELECT STUDENT.Sno,Sname,Ssex,Sage,Sdept,COURSE.CnameFROM STUDENT,COURSE,SCWHERE STUDENT.Sno=SC.Sno AND SC.Cno=COURSE.Cno;(8) 求学生的学号、姓名、选修的课程名及成绩。SELECT STUDENT.Sno,Sname,COURSE.Cname,GradeFROM STUDENT,COURSE,SCWHERE STUDENT.Sno=SC.Sno AND COURSE.Cno=SC.Cno;(9) 求选修数学课且成绩为90分以上的学生学号、姓名、及成绩。SELECT STUDENT.Sno,Sname,GradeFROM STUDENT,COURSE,SCWHERE STUDENT.Sno=SC.Sno AND COURSE.Cno=SC.Cno AND COURSE.Cname=数学 AND Grade90; (10)查询每一门课的间接先行课（即先行课的先行课）。SELECT COURSE.CpnoFROM COURSEWHERE COURSE.Cno IN (SELECT Cpno FROM COURSE);2在供应系统GYXT数据库中进行如下操作：(1) 求供应工程J1零件的供应商号SNO。SELECT SNOFROM SPJWHERE JNO=J1;(2) 求供应工程J1零件P1的供应商号SNO。SELECT SNOFROM SPJWHERE JNO=J1 AND PNO=P1;(3) 统计每种零件的供应总量。SELECT PNO,SUM(QTY)FROM SPJGROUP BY PNO;时间： 2012年5月 机房号： 05503J 得分： 实验3 嵌套查询和集合查询实验目的：1熟练掌握SQL Server查询分析器的使用方法，加深对标准SQL查询语句的理解。2熟练掌握数据查询中的嵌套、分组、统计、计算和组合的操作方法。实验内容：1基于实验一创建的教学管理JXGL数据库，实现数据的高级查询操作。2基于实验一创建的供应系统GYXT数据库，实现数据的基本查询操作。实验步骤： 写出下列操作的SQL语句。1在教学管理JXGL数据库中进行如下操作：(1) 求选修了数学的学生的学号和姓名。SELECT STUDENT.Sno,STUDENT.Sname FROM STUDENTWHERE 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.GradeFROM SC,COURSEWHERE 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.SageFROM STUDENTWHERE STUDENT.Sdept!=MA AND Sage (SELECT MAX(Sage) FROM STUDENT AS X WHERE X.Sdept=MA);(4) 求其他系中比计算机系学生年龄都小的学生。SELECT STUDENT.Sno,STUDENT.Sname,STUDENT.SageFROM STUDENTWHERE STUDENT.Sdept!=CS AND STUDENT.Sage 2 AND MIN(SC.Grade)=60;(11)求95级学生中选修课程在2门以上且都及格的学生号及总平均分，并按平均成绩排序。SELECT SC.Sno,AVG(SC.Grade) AS 平均分FROM SCGROUP BY SC.SnoHAVING COUNT(*)2 AND MIN(SC.Grade)=60ORDER BY AVG(Grade);(12)统计每个人及格的成绩的平均值，及格的门数，结果按平均成绩降序，及格门数降序排列。SELECT SC.Sno,AVG(SC.Grade) AS 平均成绩,COUNT(*) AS 及格门数FROM SCWHERE SC.Grade=60GROUP BY SC.SnoORDER BY AVG(SC.Grade) DESC,COUNT(*) DESC;(13)统计所有课程均及格学生的平均成绩，及格的门数，结果按平均成绩降序，及格门数降序排列。SELECT SC.Sno,AVG(SC.Grade),COUNT(*)FROM SCGROUP BY SC.SnoHAVING MIN(SC.Grade)=60ORDER BY AVG(SC.Grade) DESC,COUNT(*) DESC;2在供应系统GYXT数据库中进行如下操作：(1) 求供应工程J1红色零件的供应商号SNO。SELECT SNOFROM SPJWHERE PNO IN (SELECT P.PNO FROM P WHERE P.COLOR=红);(2) 求零件供应总量在1000种以上的供应商名字。SELECT S.SNAMEFROM SWHERE S.SNO IN (SELECT SPJ.SNO FROM SPJ GROUP BY SPJ.SNO HAVING SUM(SPJ.QTY)1000 ); 时间： 2012年6月 机房号： 05503J 得分： 实验4 数据完整性实验目的：1. 了解