2023年数据库实验报告4

实验内容与规定

请有选择地实践以下各题。

(1)基于“教学管理”数据库jxgl,使用SQL的查询语句表达下列查询：

①检索年龄大于23岁的男学生的学号和姓名；

SELECTSno,Sname

FROMStudent

WHERESsex='男'AND

Sage>23;

②检索至少选修一门课程的女生姓名；

SELECTSname

FROMStudent

WHERESsex='女'ANDSnoIN

(SELECTSno

FROMSC

GROUPBYSno

HAVINGcount(*)>=1；

)；

③检索王同学不学的课程的课程号；

SELECTCno;

FROMCourse

WHERECnoNOTIN

(SELECTCno

FROMStudent,SC

WHERESnamelike'王％'ANDStudent.Sno=SC.Sno

)；

④检索至少选修两门课程的学生学号;

SELECTDISTINCTSno

FROMSC

GROUPBYSno

HAVINGcount(*)>=2;

⑤检索所有学生都选修的课程的课程号与课程名；

SELECTCno,Cname

FROMCourse

WHERENOTEXISTS

(SELECT*

FROMStudent

WHERENOTEXISTS

(SELECT*

FROMSC

WHERESC.Sno=Student.SnoANDSC.Cno二Course.Cno

)

)；

⑥检索选修了所有3学分课程的学生学号；

SELECTDISTINCTSno

FROMSCX

WHERENOTEXISTS

(SELECT*

FROMCourse

WHERECcredit=3ANDNOTEXISTS

(SELECT*

FROMSCY

WHEREX.Sno=Y.SnoANDCourse.Cno=Y.Cno

)

);

(2)基于“教学管理”数据库jxg1,使用SQL的查询语句表达下列查询：

①记录有学生选修的课程门数；

.SELECTcount(DISTINCTCno)

FROMSC;

②求选修4号课程的学生的平均年龄；

oSELECTAVG(Sage)

FROMStudent,SC

WHERECno=4ANDStudent.Sno=SC.Sno;

③求学分为3的每门课程的学生平均成绩；

SELECTAVG(Grade)

FROMCourse,SC

WHERECcredit=3ANDCourse.Cno=SC.Cno

GROUPBYSC.Cno;

④记录每门课程的学生选修人数,规定超过3人的课程才记录,规定输出课程号和选修人数,

查询结果按人数降序排列,若人数相同,按课程号升序排列；

SELECTCno,count(Sno)

FROMSC

GROUPBYCno

HAVINGcount(Sno)>3

ORDERBYcount(Sno)DESC,CnoASC;

⑤检索学号比“王菲”同学大而年龄比他小的学生姓名；

SELECTSname

FROMStudentX

WHERESno>

(SELECTSno

FROMStudentY

WHERESname=,王菲'ANDSage>

(6ELECTSage

FROMStudentZ

WHERESname=,王菲'ANDX.Sno=Z.SnoANDY.Sno=Z.Sno

)

);

⑥检索姓名以“王”打头的所有学生的姓名和年龄；

SELECTSname,Sage

FROMStudent

WHERESnameLIKE'王％';

⑦在SC中检索成绩为空置的学生学号和课程号；

SELECTSno,Cno

FROMSC

WHEREGradeisNULL；

⑧求年龄大于女同学平均年龄的男学生姓名和年龄；

SELECTSname,Sage

FROMStudentX

WHERESsex=,男'ANDSage>

(•SELECTAVG(Sage)

FROMStudent

WHERESsex='女'ANDX.Sno=Y.Sno

)；

⑨求年龄大于所有女同学年龄的男同学姓名和年龄；

SELECTSname,Sage

FROMStudentX

WHERESsex='男'ANDSage>

(SELECTMAX(Sage)

FROMStudentY

WHERESsex=’女'ANDX.Sno=Y.Sno

)；

⑩检索所有比“王华”年龄大的学生姓名，年龄和性别；

SELECTSname,Sage,Ssex

FROMStudentX

WHERESage>

GSELECTSage

FROMStudentY

WHERESname=,王华'ANDX.Sno=Y.Sno

);

①检索选修“2”课程的学生中成绩最高的学生和学号；

SELECTSname,SC.Sno

FROMStudent,SC

WHERECno-2ANDStudent.Sno=SC.Sno;

②检索学生姓名和其所选修课程的课程号和成绩；

SELECTSname,Cno,Grade

FROMStudent,SC

WHEREStudent.Sno=SC.Sno

GROUPBYSname;

③检索选修4门以上课程的学生总成绩(不记录不及格的课程)，并规定按总成绩的降序排列

出来；

SELECTSno,SUM(Grade)

FROMSCX

WHEREGrade>=60ANDSnoIN

(•SELECTSno

FROMSCY

WHEREX.Sno=Y.Sno

GROUPBYSno

HAVINGcount(Cno)>4

)

'ORDERBYSUM(Grade)DESC；

(3)设有表4-1~表4-4的4个基本表(表结构于表内容是假设的)，请先创建数据库及根

据表内容创建表结构,并添加表记录，写出实现以下各题功能的SQL语句：

创建以下4各表：

CREATETABLESTUDENT

(

SNOCHAR(6)PRIMARYKEY,

SNAMECHAR(20)UNIQUE,

SEXCHAR(2),

AGESMALLINT,

CLASSCHAR(4)

)；

CREATETABLETEACHER

(

TNOCHAR(3)PRIMARYKET,

TNAMWCHAR(20)UNIQUE,

SEXCHAR(2),

AGESMALLINT,

PROFCHAR(10),

DEPTCHAR(10)

)；

CREATETABLECOURSE

CNOCHAR(4)PRIMARYKEY,

CNAMECHAR(20)UNIQUE,

TNOCHAT(3),

FOREIGNKEYTNOREFERENCESTEACHER(TNO)

)；

CREATETABLESC

(

SNOCI1AR(6),

CNOCHAR(4),

GRADESMALLINT,

PRIMARYKEY(SNO,CNO),

FOREIGNKEYSNOREFERENCESSTUDENT(SNO),

EOREIGNKEYCNOREFERENCESCOURSE(CNO)

插入数据：

INSERTINTOSTUDENT

VALUES("980101'李华'，，男'，19,'9801');

同上方法依次插入数据

①查询选修课程“8105”且成绩在80到90之间的所有记录;

SELECT*

FROMSC

WHERECN0='8105'ANDGRADEBETWEEN80AND90;

②查询成绩为79,89或99的记录；

SELECT*

FROMSC

WHEREGRADEIN(79,89,99);

③查询“9803”班的学生人数;

SELECTcount(SNO)

FROMSTUDENT

WHERESNOLIKE'9803%';

④查询至少有20名学生选修的并且课程号以8开头的课程及平均成绩;

SELECTCNO,AVG(GRADE)

FROMSC

WHERECNOLIKE'8%'

GROUPBYCNO;

HAVINGcount(SNO)>=20;

⑤查询最低分大于80,最高分小于95的SNO与平均分；

SELECTSNO,AVG(GRADE)

FROMSC

GROUPBYSNO

HAVINGMIN(GRADE)>80ANDMAX(GRADE)<95;

⑥查询“9803”班的学生所选各课程的课程号及平均成绩；

SELECTCNO,AVG(GRADE)

FROMSC

WHERESNOLIKE'9803’

GROUPBYCNO；

⑦查询选修“8105”课程的成绩高于“980302”号同学成绩的所有同学的记录；

SELECT*

FROMSCX

WHERECNO='8105'ANDGRATE>

(

SELECTGRATE

FROMSCY

WHERECN0='8105'ANDSNO='980302'ANDX.SNO=Y.SNO

);

⑧查询与学号为“980103”的同学同岁的所有学生的SNO,SNAME和AGE；

SELECTSNO,SNAME,AGE

FROMSTUDENTX

WHEREAGE二

(

SELECTAGE

FROMSTUDENTY

WHERESNO='980103'ANDX.SN0=Y.SNO

)；

⑨查询“钱军”教师任课的课程号，以及选修其课程学生的学号和成绩；

SELECTSC.CN0,SNO,GRADE

FROMSC,TEACHRT,C0URSE

WHERETNAME='钱军'ANDTEACHER.TN0=COURSE.TN0ANDSC.CONTOUR

SE.CON；

⑩查询选修某课程的学生人数多于20人的教师姓名；

SELECTDISTINCTTNAME

FROMTEACHER,SC,COURSE

WHERETEACHER.TNO=COURSE.TNOANDSC.CON二COURSE.CON

GROUPBYSC.CNO

HAVINGcount(SNO)>20；

11查询选修编号为“8105”课程且成绩至少高于其选修编号为“8245”课程成绩的同学

的

SNO及“8105”课程成绩,并按成绩从高到低依次排列；

SELECTSNOGRADE

FROMSCX

WHERECN0='8105'ANDGRADE>

(

SELECTGRADE

FROMSCY

WHERECNO='8245'ANDX.SNO=Y.SNO

)

ORDERBYGRADEDESC；

12查询选修编号为“8105”课程且成绩高于所有选修编号为“8245”课程成绩的同学的

CNO、SNO、GRADE；

SELECTCNO,SNO,GRADE

FROMSC

WHERECNO'8105,ANDGRADE>

(

SELECTMAX(GRADE)

FROMSC

WHERECNO='8245'ANDX.CN0=Y.SNO

)；

13列出所有教师和同学的姓名，SEX,AGE；

SELECTTNAME,TEACHER.SEX,TEACHER.AGE,SNAME,STUDENT.SEX,STUDENT.AG

FROMTEACHER,SC

14查询成绩比该课程平均成绩高的学生的成绩表;

SELECT*

FROMSC.X

GROUPBYSNO

HAVINGGRADE>

G

SELECTAVG(GRADE)

FROMSC.Y

WHEREX.CN0=Y.CNO

GROUPBYCNO

)；

15列出所有任课教师的TNAME和DEPT；

SELECTTNAME,DEPT

FROMTEACHER,COURSE

WHERETEACHER.TNO=COURSE.TNO

16列出所有未讲课教师的TNAME和DEPT;

SELECTTNAME,DEPY

FROMTEACHER

WHERENOTEXISTS

(

SELECT*

FROMCOURSE

WHERETEACHER.TNO=COURSE.TNO

);

17列出至少有4名男生的班号；

SELECTCLASS

FROMSTUDENT

GROUPBYCLASS

HAVINGcount(SNO)>=4;

18查询不姓“张”的学生记录；

SELECT*

FROMSTUDENT

WHERESNAMENOTLIKE'张%';

19查询每门课最高分的学生的SNO,CNO,GRADE；

SELECTSNO,CNO,GRADE

FROMSC

GROUPBYCNO

HAVINGGRADE=MAX(GRADE);

20查询与“李华”同性并同班的同学SNAME;

SELECTSNAME

FROMSTUDENTX

WHERECLASS=

(

SELECTCLASS

FROMSTUDENTY

WHERESNAME='李华'ANDSEX二