根据实际EXPLAIN此SELECT语句，第一个的扫描次数要小于第二个
24.SELECT A.TNAME FROM TEACHER A JOIN (COURSE B, SCORE C) ON (A.TNO=B.TNO AND B.CNO=C.CNO)
GROUP BY C.CNO HAVING COUNT(C.CNO)>5;
另一种解法：select tname from teacher where tno in(select x.tno from course x,score y where
x.cno=y.cno group by x.tno having count(x.tno)>5);
实际测试1明显优于2
25。select cno,sno,degree from score where cno=(select x.cno from course x,teacher y where
x.tno=y.tno and y.tname='张旭');
26。SELECT CNO FROM SCORE GROUP BY CNO HAVING MAX(DEGREE)>85;
另一种解法：select distinct cno from score where degree in (select degree from score where
degree>85);
27。SELECT A.* FROM SCORE A JOIN (TEACHER B,COURSE C) ON A.CNO=C.CNO AND B.TNO=C.TNO
WHERE B.DEPART='计算机系';
另一种解法：SELECT * from score where cno in (select a.cno from course a join teacher b on
a.tno=b.tno and b.depart='计算机系');
此时2略好于1，在多连接的境况下性能会迅速下降
28。select tname,prof from teacher where depart='计算机系' and prof not in (select prof from
teacher where depart='电子工程系');
29。SELECT * FROM SCORE WHERE DEGREE>ANY(SELECT DEGREE FROM SCORE WHERE CNO='3-245') ORDER
BY DEGREE DESC;
30。SELECT * FROM SCORE WHERE DEGREE>ALL(SELECT DEGREE FROM SCORE WHERE CNO='3-245') ORDER
BY DEGREE DESC;
31.SELECT SNAME AS NAME, SSEX AS SEX, SBIRTHDAY AS BIRTHDAY FROM STUDENT
UNION
SELECT TNAME AS NAME, TSEX AS SEX, TBIRTHDAY AS BIRTHDAY FROM TEACHER;
32.SELECT SNAME AS NAME, SSEX AS SEX, SBIRTHDAY AS BIRTHDAY FROM STUDENT WHERE SSEX='女'
UNION
SELECT TNAME AS NAME, TSEX AS SEX, TBIRTHDAY AS BIRTHDAY FROM TEACHER WHERE TSEX='女';
33.SELECT A.* FROM SCORE A WHERE DEGREE<(SELECT AVG(DEGREE) FROM SCORE B WHERE A.CNO=B.CNO);
须注意********此题
34。解法一：SELECT A.TNAME,A.DEPART FROM TEACHER A JOIN COURSE B ON A.TNO=B.TNO;
解法二：select tname,depart from teacher a where exists
(select * from course b where a.tno=b.tno);
解法三：SELECT TNAME,DEPART FROM TEACHER WHERE TNO IN (SELECT TNO FROM COURSE);
实际分析，第一种揭发貌似更好，至少扫描次数最少。
35.解法一：SELECT TNAME,DEPART FROM TEACHER A LEFT JOIN COURSE B USING(TNO) WHERE ISNUL
(B.tno);
解法二：select tname,depart from teacher a where not exists
(select * from course b where a.tno=b.tno);
解法三：SELECT TNAME,DEPART FROM TEACHER WHERE TNO NOT IN (SELECT TNO FROM COURSE);
NOT IN的方法效率最差，其余两种差不多
36.SELECT CLASS FROM STUDENT A WHERE SSEX='男' GROUP BY CLASS HAVING COUNT(SSEX)>1;
37.SELECT * FROM STUDENT A WHERE SNAME not like '王%';
38.SELECT SNAME,(YEAR(NOW())-YEAR(SBIRTHDAY)) AS AGE FROM STUDENT;
39.select sname,sbirthday as THEMAX from student where sbirthday =(select min(SBIRTHDAY)
from student)
union
select sname,sbirthday as THEMIN from student where sbirthday =(select max(SBIRTHDAY) from
student);
40.SELECT CLASS,(YEAR(NOW())-YEAR(SBIRTHDAY)) AS AGE FROM STUDENT ORDER BY CLASS DESC,AGE
DESC;
41.SELECT A.TNAME,B.CNAME FROM TEACHER A JOIN COURSE B USING(TNO) WHERE A.TSEX='男';
42.SELECT A.* FROM SCORE A WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE B );
43.SELECT SNAME FROM STUDENT A WHERE SSEX=(SELECT SSEX FROM STUDENT B WHERE B.SNAME='李军');
44.SELECT SNAME FROM STUDENT A WHERE SSEX=(SELECT SSEX FROM STUDENT B WHERE B.SNAME='李军' )
AND CLASS=(SELECT CLASS FROM STUDENT C WHERE c.SNAME='李军');
45.解法一：SELECT A.* FROM SCORE A JOIN (STUDENT B,COURSE C) USING(sno,CNO) WHERE B.SSEX='男
' AND C.CNAME='计算机导论';
解法二：select * from score where sno in(select sno from student where
ssex='男') and cno=(select cno from course
where cname='计算机导论');