45道经典SQL题练习(四)

-- 31、查询所有教师和同学的name、sex和birthday.
SELECT TNAME AS '名字',TSEX  AS SEX,TBIRTHDAY AS BIRTHDAY FROM TEACHER
UNION
SELECT SNAME AS '名字',SSEX AS SEX,SBIRTHDAY AS BIRTHDAY FROM STUDENT;

-- 32、查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT TNAME AS '名字',TSEX  AS SEX,TBIRTHDAY AS BIRTHDAY FROM TEACHER
WHERE TSEX='女'
UNION
SELECT SNAME AS '名字',SSEX AS SEX,SBIRTHDAY AS BIRTHDAY FROM STUDENT
WHERE SSEX='女';

-- *自连接* 注意此题 注意此题 注意此题 注意此题 注意此题 注意此题
-- 33、查询成绩比该课程平均成绩低的同学的成绩表。
SELECT * FROM SCORE A
WHERE DEGREE<(SELECT AVG(DEGREE) FROM SCORE B WHERE B.CNO=A.CNO);
-- *自连接* 注意此题 注意此题 注意此题 注意此题 注意此题 注意此题

-- 34、查询所有任课教师的Tname和Depart.
SELECT TNAME,DEPART FROM TEACHER;

-- 35  查询所有未讲课的教师的Tname和Depart. 
SELECT TNAME,DEPART FROM TEACHER
WHERE TNO NOT IN (SELECT TNO FROM COURSE
WHERE CNO IN (SELECT CNO FROM COURSE
WHERE CNO IN (SELECT CNO FROM SCORE)));

-- 36、查询至少有2名男生的班号。
SELECT SCLASS FROM STUDENT
WHERE