2023年数据库实验报告4.pdf
实验内容与规定请有选择地实践以下各题。(1)基 于“教学管理”数据库j xgl,使用SQL的查询语句表达下列查询：检索年龄大于2 3 岁的男学生的学号和姓名；SE L E C T S n o,Sn a meFROM St u dentW HERE S s ex=男 ANDS a ge 2 3;检索至少选修一门课程的女生姓名；SE L EC T Snam eFROM St u d e n tW HERE Ss ex=女 AND Sno IN(SELECT SnoFROM SCGROUP BY SnoH A V ING cou nt (*)=1；)；检索王同学不学的课程的课程号；S E L E C T Cno;FROM C o u r s eW HERE Cn o N O T IN(S E L E C T CnoFR O M S t u d e nt,SCW H ERE S n ame l i k e 王 AND St u dent.Sn o=SC.Sno)；检索至少选修两门课程的学生学号;S ELECT D I ST I NCT Sn oFROM SCGROUP B Y SnoHAV I N G cou nt(*)=2;检索所有学生都选修的课程的课程号与课程名；S E L E CT C no,C n ameFROM C o u r s eW HERE NOT EX ISTS(SELECT*FROM St u dentW HERE NOT EX I STS(SELECT*FROM SCW HERE SC.Sno=St u d ent.Sno AND SC.C n o 二 Cou r s e.Cno)；检索选修了所有3 学分课程的学生学号；SELECT DISTINCT Sn oFROM SC XW HERE NOT EX ISTS(SELECT*F ROM Cou r s eW HERE Cc r ed i t=3 AND NOT EX ISTS(SELECT*FROM SC YW H E R E X.S n o=Y.Sno A N D Cou r s e.Cno=Y.Cno);(2)基 于“教学管理”数据库jx g 1 ,使用S Q L的查询语句表达下列查询：记录有学生选修的课程门数；.SELECT count(DIS T I NCT C no)FROM SC;求选修4号课程的学生的平均年龄；oSELECT AVG(Sage)FROM St u d en t,S CWHERE C n o=4 AN D St u den t.Sn o=S C.Sno;求学分为3的每门课程的学生平均成绩；SELECT A V G(G rade)FROM Cou r s e,SCWH E RE C credit=3 AND Course.C no=SC.C noGROUP BY S C.Cno;记录每门课程的学生选修人数,规定超过3人的课程才记录,规定输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列；S E L E CT C no,c ount(Sno)FROM SCGROUP B Y CnoHAVING c o un t(S n o)3ORDER BY count(Sno)DESC,C n o ASC;检索学号比“王菲”同学大而年龄比他小的学生姓名；SELECT SnameFROM S tudent XWHERE Sno(SELECT S noFROM S t u d en t YW HE RE Snam e=,王 菲 AND Sage(6 ELE CT SageFROM St u dent ZW HERE Sname=,王 菲 A N D X.Sno=Z.Sno AND Y.Sno=Z.Sno);检索姓名以“王”打头的所有学生的姓名和年龄；S E L E C T Sn a me,S a geFROM St u dentW HERE Sna m e LIKE 王;在S C 中检索成绩为空置的学生学号和课程号；S ELECT Sno,CnoF R O M SCW HERE Gr ade i s NULL；求年龄大于女同学平均年龄的男学生姓名和年龄；S E L ECT Sname,S a geF ROM St u dent XW HERE Ss ex=,男 AND S a g e(SELECT AVG(S a ge)F ROM St u dentW HERE Ss ex=女 AND X.Sno=Y.Sno)；求年龄大于所有女同学年龄的男同学姓名和年龄；SELECT Sname,Sag eFROM S tu d ent XWHERE S s e x=男AND Sage(SELECT M A X (Sage)FROM Stu d en t YW HERE Ssex=女 A N D X.Sn o=Y.Sno)；检索所有比“王华”年龄大的学生姓名，年龄和性别；S E L E C T Sn a m e,S a ge,SsexFROM S t u de n t XWHERE Sag eG S ELECT S a g eFROM Stu d e n t YWH ERE S n am e=,王 华 AND X.S no=Y.Sno);检索选修“2”课程的学生中成绩最高的学生和学号；SELECT Snam e,SC.S noFROM S tu d ent,SCWHE RE C no-2 AND S tudent.Sno=SC.Sno;检索学生姓名和其所选修课程的课程号和成绩；SELE CT S name,C n o,G ra deFROM S t uden t,SCWHERE Studen t.S no=SC.SnoGROUP BY Sname;检索选修4 门以上课程的学生总成绩(不记录不及格的课程)，并规定按总成绩的降序排列出来；SELECT S no,SUM(Gra d e)FROM SC XW HERE Gr ade=6 0 AND S no IN(SELECT SnoFROM SC YW H E RE X.S n o=Y.S noG R OUP BY SnoHAVING cou nt(Cno)4)O R D E R BY SUM(Gr ade)DES C；(3)设有表4-1 表 4-4的 4 个基本表(表结构于表内容是假设的)，请先创建数据库及根据表内容创建表结构,并添加表记录，写出实现以下各题功能的S Q L 语句：创建以下4 各表：CREATE TABL E STUDENT(S NO CHAR(6)PRIMARY K EY,S NAME CHAR(2 0)UNIQUE,SEX CHAR(2),AGE S MAL LINT,CLASS CHAR(4)；CREATE T A BLE TEACHER(TNO CHAR(3)PRIMARY KE T,TNAMW CHAR(2 0)UNIQUE,S E X C H A R (2),AGE SMALLINT,PROF C H AR(1 0),DEPT CHAR(1 0)；CREATE TABLE COURSECNO CHAR(4)PRIMARY KEY,CNAME CHAR(2 0)UNIQUE,T N O CHAT(3),FOREIGN KEY TNO REFER ENCES TEAC HER(TNO)；CREATE TABLE SC(SNO CI1 AR(6),CNO C HAR(4),GRADE SMALLINT,PR IMARY KEY(SNO,CNO),F OREIGN KEY SNO REFERENCE S S TUDENT(S NO),EO REIGN KEY CNO REFERENCES COURSE(CNO)插入数据：I NSERT INTO STU D E N TVALUES(9 8 0 1 0 1 李 华 ，男，1 9,9 8 0 1 );同上方法依次插入数据查询选修课程“8 1 0 5”且成绩在8 0 到 9 0 之间的所有记录;SELECT*FROM SCW HERE CN0=8 1 0 5 AND GRADE BETW EEN 8 0 A N D 9 0;查询成绩为7 9,8 9 或 9 9 的记录；SELECT*FROM SCW HERE GRADE IN(7 9,8 9,9 9);查 询“9 8 0 3”班的学生人数;S E L E C T cou nt(SNO)F R OM STUDENTW HERE SNO LIKE 9 8 0 3%;查询至少有2 0 名学生选修的并且课程号以8开头的课程及平均成绩;SELECT CNO,A VG(GRADE)F R O M SCW HERE CNO L IKE 8%GROUP BY CNO;HAVING cou nt (SNO)=2 0;查询最低分大于8 0,最高分小于95 的 SNO与平均分；SELECT SNO,AVG(GRADE)F R O M S CG ROUP BY SNOHAVING MIN(GRADE)8 0 AND M A X(G R ADE)(SELECT G RATEFROM S C YW HERE C N0=8 1 0 5 A N D SNO=9 8 0 30 2 AND X.S N O=Y.SNO);查询与学号为“9 8 0 1 0 3”的同学同岁的所有学生的SNO,SNAME和 A G E；SELECT SNO,SNAME,AGEF R O M S TUDENT XW HERE AGE 二(SELECT AGEF ROM STUDENT YW HERE S NO=9 8 0 1 0 3 A N D X.SN0=Y.SNO)；查 询“钱军”教师任课的课程号，以及选修其课程学生的学号和成绩；SELECT S C.CN0,SNO,GRADEFROM SC,TEACHRT,C 0 U R S EW HERE TNAME=钱 军 A N D TEACHER.T N 0=COURSE.T N0 AND SC.C O N T O U RSE.C O N；查询选修某课程的学生人数多于2 0 人的教师姓名；SELECT DISTINCT TNAMEFROM T E A C H E R,SC,COURSEW HERE TEACHER.T N O=COURSE.TNO A N D SC.C ON二 COURSE.CONG R O U P B Y SC.CNOHAV ING c o u n t (SNO)2 0 ；1 1 查询选修编号为“8 1 0 5”课程且成绩至少高于其选修编号为“8 2 4 5”课程成绩的同学的S N O 及“8 1 0 5”课程成绩,并按成绩从高到低依次排列；S E L E CT SNO G RADEFROM SC XW HERE C N 0=8 1 0 5 AND GRADE(SELECT GRADEFROM SC YW HERE C N O=8 2 4 5 AND X.SNO=Y.SNO)ORDER BY GRADE D E S C；1 2查询选修编号为“8 1 0 5”课程且成绩高于所有选修编号为“8 2 45”课程成绩的同学的CNO、SNO、GRADE；SELEC T CNO,SNO,GRADEFROM SCW HERE C N O 8 1 0 5,AND GRADE(S E LECT MAX(GRADE)FROM SCW HER E CNO=8 2 45 A N D X.CN0=Y.SNO)；1 3 列出所有教师和同学的姓名，SEX,A G E；SELECT TN A ME,TEACHER.SEX,TEACHER.AG E,SNA ME,S T UDENT.SEX,STUDENT.AGFROM TEACHER,SC1 4查询成绩比该课程平均成绩高的学生的成绩表;S E L E C T *F R O M SC.XGROUP BY S N OH A V I N G GRADEGSELECT A VG(GRADE)FROM SC.YW HERE X.CN0=Y.C N OG R O U P BY C N O)；1 5列出所有任课教师的T N A M E和DEPT；SELECT TNAME,DEPTFROM TEACHER,COURSEW HERE TEACHER.TNO=COU RSE.TNO1 6列出所有未讲课教师的TNAME和DE PT;SELECT T NAME,D E P YFROM TEACHERW HERE NOT EX ISTS(SELECT*F ROM C O U R S EW H E R E TEACHER.TNO=COURSE.TNO);1 7 列出至少有4 名男生的班号；SELECT CLASSFROM STUDENTGROUP BY CLAS SHAVING cou nt(SNO)=4;1 8 查询不姓“张”的学生记录；SELECT*FROM STUDENTW HERE S N A M E NOT LIKE 张%;1 9查询每门课最高分的学生的SNO,CNO,GRADE；SELECT SNO,CNO,GRADEFROM SCGROUP BY CNOHAVING G R A D E=MAX (GRADE);2 0 查询与“李华”同性并同班的同学SNAME;SE LECT SNAMEF ROM STUDEN T XW HERE CLASS=(SELECT C L A S SFROM STUDENT YW H E R E SNAME=李 华 AND SEX 二(SELSCT SEXFROM STUDENT ZW HERE SNAME=李 华 AND X.S N O=Y.S N O A ND Y.SN0=Z.SN0);2 1 查 询“女”教师及其所上的课程；S E LECT TNAME,C NO,CNAMEFROM T EACH ER,COURS EW HERE T E ACHER.TNO=COURSE.TNO,SEX =,女 ；22查询选修“数据库系统”课程的“男”同学的成绩表；SEL E C T*FROM SC,COURSE,S TUDENTW HERE STUDENT.SNO=SC.SNO AND COURS E.CNO=SC.CNO AND C N A M E=,数据库系统A N D S E X=男；2 3 查询所有比刘涛年龄大的教师姓名，年龄和刘涛的年龄；SELECT TNAME,AGEFROM TEACHER XW HE RE SNAME=刘 涛 OR AGE(S ELECT AGEFROM TEACHER YW HERE X.TNO=Y.TNO A N D S N A M E=刘涛)；2 4 查询不讲授“8 1 0 6 ”号课程的教师姓名。S E LECT TNAMEF R O M TEACHER,COURSEWHERE T E A C HER.TNO=COURS E.TNO AND C N O NOT I N (8 1 0 6);
