環(huán)境是mysql

練習(xí)數(shù)據(jù)見SQL：練習(xí)的前期準(zhǔn)備
sql 練習(xí)(一)
sql 練習(xí)(二)
21皆看、查詢成績高于學(xué)號為“109”仓坞、課程號為“3-105”的成績的所有記錄。

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

22腰吟、查詢和學(xué)號為108的同學(xué)同年出生的所有學(xué)生的Sno无埃、Sname和Sbirthday列。

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

23毛雇、查詢“張旭“教師任的學(xué)生成績录语。

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、查詢選修某課程的同學(xué)人數(shù)多于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班全體學(xué)生的記錄虽缕。

 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蒲稳、查詢出“計(jì)算機(jī)系“教師所教課程的成績表。

SELECT sno,cno,degree
  FROM score
 WHERE cno IN(SELECT cno
                FROM course a
                JOIN (SELECT tno FROM teacher WHERE depart = '計(jì)算機(jī)系')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='計(jì)算機(jī)系'))

28伍派、查詢“計(jì)算機(jī)系”與“電子工程系“不同職稱的教師的Tname和Prof江耀。

SELECT Tname,prof
  FROM teacher
 WHERE depart='計(jì)算機(jī)系' OR depart='電子工程系' 
 GROUP BY prof
 HAVING COUNT(prof)=1

SELECT Tname,prof 
  FROM teacher 
 WHERE prof NOT IN(SELECT prof FROM teacher WHERE Depart='計(jì)算機(jī)系' 
   AND prof IN(SELECT prof FROM teacher WHERE Depart='電子工程系'))

29、查詢選修編號為“3-105”且成績高于選修編號為“3-245”課程的同學(xué)的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祥国、查詢所有教師和同學(xué)的name、sex和birthday.

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