数据库实验报告2

实验内容与要求

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

（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 https://www.wendangku.net/doc/4b8635062.html,o=https://www.wendangku.net/doc/4b8635062.html,o

)

);

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

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 https://www.wendangku.net/doc/4b8635062.html,o=https://www.wendangku.net/doc/4b8635062.html,o

)

);

（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 https://www.wendangku.net/doc/4b8635062.html,o=https://www.wendangku.net/doc/4b8635062.html,o

GROUP BY https://www.wendangku.net/doc/4b8635062.html,o;

④统计每门课程的学生选修人数，要求超过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

)

);

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

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 https://www.wendangku.net/doc/4b8635062.html,O,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 https://www.wendangku.net/doc/4b8635062.html,O

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 https://www.wendangku.net/doc/4b8635062.html,O=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 https://www.wendangku.net/doc/4b8635062.html,O=https://www.wendangku.net/doc/4b8635062.html,O

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 https://www.wendangku.net/doc/4b8635062.html,O=https://www.wendangku.net/doc/4b8635062.html,O 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);