实验内容与要求
请有选择地实践以下各题。
（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