sql 练习(三)

环境是mysql

练习数据见SQL：练习的前期准备
sql 练习(一)
sql 练习(二)
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

SELECT *
  FROM score
 WHERE degree>(SELECT degree
                 FROM score 
                WHERE sno>='109' AND cno='3-105')

22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

SELECT sno,sname,sbirthday
  FROM student 
 WHERE YEAR(sbirthday)=(SELECT YEAR(sbirthday)
                  FROM student
                 WHERE sno='108')

23、查询“张旭“教师任的学生成绩。

SELECT degree
  FROM score
 WHERE cno IN(SELECT cno
        FROM course b
                JOIN teacher a ON a.tno=b.tno
           WHERE a.tname='张旭')

SELECT degree
  FROM score 
 WHERE cno IN( SELECT Cno 
                 FROM course
                WHERE Tno=(SELECT Tno FROM teacher WHERE Tname='张旭'))

24、查询选修某课程的同学人数多于5人的教师姓名。

SELECT tname
  FROM teacher
 WHERE tno=(SELECT tno
          FROM course a
          JOIN (SELECT COUNT(cno),cno
                 FROM score
                GROUP BY cno
               HAVING COUNT(cno)>5) b
                   ON a.cno=b.cno)

SELECT Tname 
  FROM teacher 
 WHERE Tno IN( SELECT Tno 
                 FROM course 
                WHERE Cno IN( SELECT Cno 
                                FROM score 
                               GROUP BY Cno 
                              HAVING COUNT(Cno)>5))

25、查询95033班和95031班全体学生的记录。

 SELECT * 
   FROM student 
   LEFT JOIN score 
     ON student.sno = score.sno 
  WHERE student.class IN ('95033','95031')

26、查询存在有85分以上成绩的课程Cno.

SELECT DISTINCT cno
  FROM score
 WHERE degree>'85'

27、查询出“计算机系“教师所教课程的成绩表。

SELECT sno,cno,degree
  FROM score
 WHERE cno IN(SELECT cno
                FROM course a
                JOIN (SELECT tno FROM teacher WHERE depart = '计算机系')b
                  ON a.tno=b.tno)
                  
SELECT degree,cno,Sno 
  FROM score 
 WHERE cno IN(SELECT Cno 
                FROM Course 
               WHERE Tno IN ( SELECT Tno FROM teacher WHERE depart='计算机系'))

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

SELECT Tname,prof
  FROM teacher
 WHERE depart='计算机系' OR depart='电子工程系' 
 GROUP BY prof
 HAVING COUNT(prof)=1

SELECT Tname,prof 
  FROM teacher 
 WHERE prof NOT IN(SELECT prof FROM teacher WHERE Depart='计算机系' 
   AND prof IN(SELECT prof FROM teacher WHERE Depart='电子工程系'))

29、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

SELECT sno,cno,degree
  FROM score
 WHERE cno='3-105'
   AND degree>=(SELECT MIN(degree) FROM score WHERE cno='3-245')

30、查询所有教师和同学的name、sex和birthday.

SELECT tname,tsex,tbirthday FROM teacher 
UNION 
SELECT sname,ssex,sbirthday FROM student;