数据库实验报告

实验内容与要求

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

（1）基于“教学管理”数据库jxgl，使用SQL的查询语句表达下列查询： ①检索年龄大于23岁的男学生的学号和姓名；

SELECT Sno,Sname FROM Student

WHERE Ssex=’男’AND

Sage>23;

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

SELECT Sname FROM Student

WHERE Ssex=’女’AND Sno IN ( SELECT Sno

FROM SC

GROUP BY Sno

HAVING count(*)>=1; );

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

SELECT Cno; FROM Course

WHERE Cno NOT IN ( SELECT Cno

FROM Student,SC

WHERE Sname like ’王%’AND Student.Sno=SC.Sno );

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

SELECT DISTINCT Sno FROM SC

GROUP BY Sno

HAVING count(*)>=2;

⑤检索全部学生都选修的课程的课程号与课程名；

SELECT Cno,Cname FROM Course

WHERE NOT EXISTS ( SELECT *

FROM Student WHERE NOT EXISTS ( SELECT *

FROM SC

WHERE SC.Sno=Student.Sno AND SC.Cno=Course.Cno ) );

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

SELECT DISTINCT Sno FROM SC X

WHERE NOT EXISTS ( SELECT *

FROM Course

WHERE Ccredit=3 AND NOT EXISTS ( SELECT *

FROM SC Y

WHERE X.Sno=Y.Sno AND Course.Cno=Y.Cno ) );

（2）基于“教学管理”数据库jxgl，使用SQL的查询语句表达下列查询： ①统计有学生选修的课程门数； SELECT count(DISTINCT Cno)

FROM SC;

②求选修4号课程的学生的平均年龄； SELECT AVG(Sage)

FROM Student,SC

WHERE Cno=4 AND Student.Sno=SC.Sno; ③求学分为3的每门课程的学生平均成绩；

SELECT AVG(Grade) FROM Course,SC

WHERE Ccredit=3 AND Course.Cno=SC.Cno GROUP BY SC.Cno;

④统计每门课程的学生选修人数，要求超过3人的课程才统计，要求输出课程号和选修人数,查询结果按人数降序排列，若人数相同，按课程号升序排列；

SELECT Cno,count(Sno) FROM SC GROUP BY Cno

HAVING count(Sno)>3

ORDER BY count(Sno) DESC,Cno ASC;

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

SELECT Sname FROM Student X WHERE Sno> ( SELECT Sno

FROM Student Y

WHERE Sname=’王菲’AND Sage> ( SELECT Sage

FROM Student Z

WHERE Sname=’王菲’AND X.Sno=Z.Sno AND Y.Sno=Z.Sno ) );

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

SELECT Sname,Sage FROM Student

WHERE Sname LIKE ‘王%’;

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

SELECT Sno,Cno FROM SC

WHERE Grade is NULL;

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

SELECT Sname,Sage FROM Student X

WHERE Ssex=’男’AND Sage> ( SELECT AVG(Sage)

FROM Student

WHERE Ssex=’女’AND X.Sno=Y.Sno );

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

SELECT Sname,Sage FROM Student X

WHERE Ssex=’男’AND Sage> ( SELECT MAX(Sage)

FROM Student Y

WHERE Ssex=’女’AND X.Sno=Y.Sno );

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

SELECT Sname,Sage,Ssex FROM Student X WHERE Sage> ( SELECT Sage

FROM Student Y

WHERE Sname=’王华’AND X.Sno=Y.Sno );

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

SELECT Sname,SC.Sno FROM Student,SC

WHERE Cno=2 AND Student.Sno=SC.Sno;

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

SELECT Sname,Cno,Grade FROM Student,SC

WHERE Student.Sno=SC.Sno GROUP BY Sname;

③检索选修4门以上课程的学生总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来；

SELECT Sno,SUM(Grade) FROM SC X

WHERE Grade>=60 AND Sno IN ( SELECT Sno

FROM SC Y

WHERE X.Sno=Y.Sno GROUP BY Sno

HAVING count(Cno)>4 )

` ORDER BY SUM(Grade) DESC;

（3）设有表4-1~表4-4的4个基本表（表结构于表内容是假设的），请先创建数据库及根据表内容创建表结构，并添加表记录，写出实现以下各题功能的SQL语句：

创建以下4各表： CREATE TABLE STUDENT (

SNO CHAR(6) PRIMARY KEY, SNAME CHAR(20) UNIQUE, SEX CHAR(2), AGE SMALLINT, CLASS CHAR(4) );

CREATE TABLE TEACHER (

TNO CHAR(3) PRIMARY KET, TNAMW CHAR(20) UNIQUE, SEX CHAR(2), AGE SMALLINT, PROF CHAR(10), DEPT CHAR(10) );

CREATE TABLE COURSE (

CNO CHAR(4) PRIMARY KEY, CNAME CHAR(20) UNIQUE, TNO CHAT(3),

FOREIGN KEY TNO REFERENCES TEACHER(TNO) );

CREATE TABLE SC (

SNO CHAR(6), CNO CHAR(4), GRADE SMALLINT,

PRIMARY KEY(SNO,CNO),

FOREIGN KEY SNO REFERENCES STUDENT(SNO), FOREIGN KEY CNO REFERENCES COURSE(CNO) );

插入数据：

INSERT INTO STUDENT

VALUES(‘980101’,’李华’，‘男’，19，’9801’); ......

同上方法依次插入数据

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

SELECT * FROM SC

WHERE CNO=’8105’AND GRADE BETWEEN 80 AND 90; ②查询成绩为79，89或99的记录；

SELECT * FROM SC

WHERE GRADE IN(79,89,99); ③查询“9803”班的学生人数；

SELECT count(SNO) FROM STUDENT

WHERE SNO LIKE ‘9803%’;

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

SELECT CNO,AVG(GRADE) FROM SC

WHERE CNO LIKE ‘8%’ GROUP BY CNO;

HAVING count(SNO)>=20;

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

SELECT SNO,AVG(GRADE) FROM SC

GROUP BY SNO

HAVING MIN(GRADE)>80 AND MAX(GRADE)<95;

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

SELECT CNO,AVG(GRADE) FROM SC

WHERE SNO LIKE ‘9803’ GROUP BY CNO;

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

SELECT * FROM SC X

WHERE CNO=’8105’AND GRATE> (

SELECT GRATE FROM SC Y

WHERE CNO=’8105’AND SNO=’980302’AND X.SNO=Y.SNO );

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

SELECT SNO,SNAME,AGE

FROM STUDENT X WHERE AGE= (

SELECT AGE FROM STUDENT Y

WHERE SNO=’980103’AND X.SNO=Y.SNO );

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

SELECT SC.CNO,SNO,GRADE FROM SC,TEACHRT,COURSE

WHERE TNAME=’钱军’AND TEACHER.TNO=COURSE.TNO AND SC.CON=COURSE.CON; ⑩查询选修某课程的学生人数多于20人的教师姓名；

SELECT DISTINCT TNAME FROM TEACHER,SC,COURSE

WHERE TEACHER.TNO=COURSE.TNO AND SC.CON=COURSE.CON GROUP BY SC.CNO

HAVING count(SNO)>20;

11查询选修编号为“8105”课程且成绩至少高于其选修编号为“8245”课程成绩的同学的 SNO及“8105”课程成绩，并按成绩从高到低依次排列；

SELECT SNO GRADE FROM SC X

WHERE CNO=’8105’AND GRADE> (

SELECT GRADE FROM SC Y

WHERE CNO=’8245’AND X.SNO=Y.SNO )

ORDER BY GRADE DESC; 12查询选修编号为“8105”课程且成绩高于所有选修编号为“8245”课程成绩的同学的CNO、SNO、GRADE；

SELECT CNO,SNO,GRADE FROM SC

WHERE CNO=’8105’AND GRADE> (

SELECT MAX(GRADE) FROM SC

WHERE CNO=’8245’AND X.CNO=Y.SNO );

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

SELECT TNAME,TEACHER.SEX,TEACHER.AGE,SNAME,STUDENT.SEX,STUDENT.AGE FROM TEACHER,SC

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

SELECT * FROM SC.X

GROUP BY SNO HAVING GRADE> (

SELECT AVG(GRADE) FROM SC.Y

WHERE X.CNO=Y.CNO GROUP BY CNO );

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

SELECT TNAME,DEPT FROM TEACHER,COURSE

WHERE TEACHER.TNO=COURSE.TNO 16列出所有未讲课教师的TNAME和DEPT;

SELECT TNAME,DEPY FROM TEACHER WHERE NOT EXISTS (

SELECT * FROM COURSE

WHERE TEACHER.TNO=COURSE.TNO );

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

SELECT CLASS FROM STUDENT GROUP BY CLASS

HAVING count(SNO)>=4; 18查询不姓“张”的学生记录；

SELECT * FROM STUDENT

WHERE SNAME NOT LIKE ‘张%’;

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

SELECT SNO,CNO,GRADE FROM SC

GROUP BY CNO

HAVING GRADE=MAX(GRADE);

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

SELECT SNAME FROM STUDENT X WHERE CLASS= (

SELECT CLASS FROM STUDENT Y

WHERE SNAME=’李华’AND SEX= (

SELSCT SEX FROM STUDENT Z

WHERE SNAME=’李华’AND X.SNO=Y.SNO AND Y.SNO=Z.SNO )

);

21查询“女”教师及其所上的课程；

SELECT TNAME,CNO,CNAME FROM TEACHER,COURSE

WHERE TEACHER.TNO=COURSE.TNO,SEX=’女’;

22查询选修“数据库系统”课程的“男”同学的成绩表；

SELECT *

FROM SC,COURSE,STUDENT

WHERE STUDENT.SNO=SC.SNO AND COURSE.CNO=SC.CNO AND CNAME=’数据库系统’

AND SEX=’男’;

23查询所有比刘涛年龄大的教师姓名，年龄和刘涛的年龄；

SELECT TNAME,AGE FROM TEACHER X

WHERE SNAME=’刘涛’OR AGE> (

SELECT AGE FROM TEACHER Y

WHERE X.TNO=Y.TNO AND SNAME=’刘涛’ );

24查询不讲授“8106”号课程的教师姓名。

SELECT TNAME

FROM TEACHER,COURSE

WHERE TEACHER.TNO=COURSE.TNO AND CNO NOT IN(8106);

本文来源：https://www.bwwdw.com/article/k0d6.html