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);