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

理学院 信科082班 陈先国 3080942214实验1 创建数据库与数据表下面写出实现如下操作的SQL语句： (1) 创建供应系统“GYXT”数据库。 CREATE DATABASE GYXTON(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, 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),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, 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( 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);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 简单查询和连接查询 首先，写出下列操作的SQL语句，然后进入到SQL Server查询分析器中调试结果，并将结果抄写下来。1在教学管理JXGL数据库中进行如下操作：(1) 求数学系学生的学号和姓名。SELECT Sno,SnameFROM STUDENTWHERE Sdept=MA调试结果： Sno Snanme 1 95003 王敏(2) 求选修了课程的学生学号。SELECT Sno FROM STUDENTWHERE Sno not in(select Sno from SC )调试结果： Sno 1 95003 2 95004(3) 求选修了数学课的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。SELECT STUDENT.Sno,Gradefrom STUDENT,SC,COURSEWHERE STUDENT.Sno=SC.Sno and COURSE.Cno=SC.Cno and Cname like (数学 )order by Grade desc,STUDENT.Sno 调试结果： Sno Grade1 95002 902 95001 85(4) 求选修数学课其且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数0.8输出。SELECT DISTINCT Sno,0.8*Grade AS Grade FROM SCWHERE SC.Cno=2 AND SC.Grade BETWEEN 80 AND 90调试结果： Sno Grade1 95001 68.02 95002 72.0(5) 求数学系或计算机系姓刘的学生的信息。SELECT * FROM STUDENTWHERE (Sdept=MA OR Sdept=IS)AND Sname LIKE刘%调试结果： Sno Sname Ssex Sage Sdept1 95002 刘晨 F 19 IS(6) 求缺少了成绩的学生的学号和课程号。SELECT Sno,Cno FROM SCWHERE SC.Grade IS NULL调试结果： Sno Cno (7) 查询每个学生的情况以及他（她）所选修的课程。SELECT STUDENT.Sno,Sname,Ssex,Sage,Sdept,Cname FROM STUDENT,COURSE,SCWHERE SC.Sno=STUDENT.Sno AND SC.Cno=COURSE.Cno调试结果： Sno Sname Ssex Sage Sdet Cname1 95001 李勇 M 20 CS 数据库2 95001 李勇 M 20 CS 数学3 95001 李勇 M 20 CS 信息系统4 95002 刘晨 F 19 IS 数学5 95002 刘晨 F 19 IS 信息系统(8) 求学生的学号、姓名、选修的课程名及成绩。SELECT STUDENT.Sno,Sname,Cname,Grade FROM STUDENT,COURSE,SCWHERE SC.Sno=STUDENT.Sno AND SC.Cno=COURSE.Cno调试结果： Sno Sname Cname Grade1 95001 李勇 数据库 922 95001 李勇 数学 853 95001 李勇 信息系统 884 95002 刘晨 数学 905 95002 刘晨 信息系统 80(9) 求选修数学课且成绩为90分以上的学生学号、姓名、及成绩。SELECT STUDENT.Sno,Sname,Grade FROM STUDENT,COURSE,SCWHERE SC.Sno=STUDENT.Sno AND SC.Cno=COURSE.Cno AND COURSE.Cname=数学 AND SC.Grade=90调试结果： Sno Sname Grade1 95002 刘晨 90(10)查询每一门课的间接先行课（即先行课的先行课）。SELECT T1.CNO, T1.CPNO,T2.CPNO FROM COURSE T1,COURSE T2 WHERE T1.CPNO=T2.CNO调试结果： CNO CPNO CPNO1 1 5 7 2 3 1 53 4 64 5 7 65 7 62在供应系统GYXT数据库中进行如下操作：(1) 求供应工程J1零件的供应商号SNO。SELECT DISTINCT SNO FROM SPJWHERE SPJ.JNO=J1调试结果： SNO1 S12 S23 S34 S45 S5(2) 求供应工程J1零件P1的供应商号SNO。SELECT DISTINCT SNO FROM SPJWHERE SPJ.JNO=J1 AND SPJ.PNO=P1调试结果： SNO1 S1 2 S3(3) 统计每种零件的供应总量。SELECT DISTINCT PNO,SUM(QTY) AS totalQTY FROM SPJGROUP BY PNO调试结果： PNO totalQTY1 P1 12002 P2 2003 P3 19004 P4 6005 P5 1200实验3 嵌套查询和集合查询 首先，写出下列操作的SQL语句，然后进入到SQL Server查询分析器中调试结果，并将结果抄写下来。1在教学管理JXGL数据库中进行如下操作：(1) 求选修了数学的学生的学号和姓名。ELECT STUDENT.Sno,Sname FROM STUDENT,SC,COURSEWHERE COURSE.Cno=SC.Cno AND SC.Sno=STUDENT.Sno AND COURSE.Cname=数学调试结果： Sno Sname1 95001 李勇2 95002 刘晨(2) 求数学课程成绩高于李勇的学生学号和成绩。SELECT STUDENT.Sno,Grade FROM STUDENT,SC,COURSEWHERE COURSE.Cno=SC.Cno AND SC.Sno=STUDENT.Sno AND COURSE.Cname=数学AND SC.Grade(SELECT Grade FROM STUDENT,SC,COURSEWHERE COURSE.Cno=SC.Cno AND SC.Sno=STUDENT.Sno AND COURSE.Cname=数学 AND STUDENT.Sname=李勇)调试结果： Sno Grade1 95002 90(3) 求其他系中年龄小于计算机系年龄最大者的学生。SELECT SnameFROM STUDENTWHERE SageALL(SELECT SageFROM SCWHERE Sdept=IS)AND SdeptIS调试结果： Sname 1 李勇2 王敏(4) 求其他系中比计算机系学生年龄都小的学生。select *from STUDENT where Sage (select min(Sage) FROM STUDENT WHERE Sdept LIKE IS )AND Sdept NOT LIKE IS(5) 求选修了数学课的学生姓名。SELECT STUDENT.SnameFROM STUDENT,SCWHERE SC.Cno=2 AND STUDENT.Sno=SC.Sno调试结果： Sname1 李勇2 刘晨(6) 求没有选修数学课的学生姓名。SELECT DISTINCT Sname FROM STUDENTWHERE (2 NOT IN(SELECT SC.Cno FROM SCWHERE STUDENT.Sno=SC.Sno)调试结果： Sname1 王敏2 张立(7) 查询选修了全部课程的学生的姓名。SELECT SNO,Sname FROM STUDENT WHERE NOT EXISTS ( SELECT * FROM COURSE WHERE NOT EXISTS ( SELECT * FROM SC WHERE SNO=STUDENT.SNO AND CNO=COURSE.CNO) (8) 求至少选修了学号为“95002”的学生所选修的全部课程的学生学号和姓名。SELECT Sno,SnameFROM STUDENTWHERE (EXISTS (SELECT *FROM SCWHERE Sno=STUDENT.SnoAND Cno=ANY (SELECT Cno FROM SC WHERE Sno=95002)调试结果： Sno Sname1 95001 李勇2 65002 刘晨(9) 求选修各门课的人数及平均成绩。SELECT count(*) as num,avg(Grade) as avg_grade FROM SCgroup by Cno调试结果： num avg_grade1 1 922 2 873 2 84(10)求选修课程在2门以上且都及格的学生号及总平均分。SELECT Sno, AVG(Grade) AS AveScoreFrom SCWHERE EXISTS (SELECT * FROM STUDENT WHERE NOT EXISTS (SELECT *FROM SCWHERE Sno=STUDENT.SnoAND Cno =2)调试结果： Sno AveScore1 95001 882 95002 85(11)求95级学生中选修课程在2门以上且都及格的学生号及总平均分，并按平均成绩排序。SELECT Sno, AVG(Grade) AS AveScoreFrom SCWHERE EXISTS (SELECT * FROM STUDENT WHERE NOT EXISTS (SELECT *FROM SCWHERE Sno=STUDENT.SnoAND Cno =2)调试结果： Sno AveScore1 95001 882 95002 85(12)统计每个人及格的成绩的平均值，及格的门数，结果按平均成绩降序，及格门数降序排列。SELECT Sno, COUNT(*) AS C_Num, AVG(Grade) AS AveScoreFROM SCWHERE Grade=60GROUP BY SnoORDER BY AveScore DESC, C_Num DESC调试结果： Sno C_Num AveScore1 95001 3 882