--S (sno,sname)学生关系，sno为学号 sname为姓名

--C（cno,cname,Cteacher）课程关系 cno为课程号，cname为课程名，cteacher 为任课教师

--SC（sno,cno,scgrade）选课关系 scgrade为成绩 

---- 没有选修过 李明 老师讲授课程的所有学生姓名 

SELECT * FROM S  WHERE SNO NOT  IN(    SELECT DISTINCT    A.sno    FROM SC  A  INNER JOIN  ( SELECT * FROM  C  A WHERE A.Cteacher='李明'） B

   ON A.cno=B.cno

 )

----  列出二门以上（含两门）不及格课程学生姓名及学号

WITH TB AS

(

SELECT  A.SNO   FROM SC A WHERE A.scgrade<60   GROUP BY A.sno HAVING COUNT(*)>=2

)

SELECT   S.* FROM TB INNER JOIN S ON TB.SNO=S.SNO 

--- 列出学过 “1” 号课程 又学过 “2” 号课程的所有学生姓名

WITH TB AS

(

      SELECT  DISTINCT  SC.CNO   FROM SC  WHERE  SC.CNO IN ( SELECT CNO   FROM C  WHERE C.CNAME IN('1'，'2')  ) GROUP BY SC.CNO

      HAVING COUNT(*)>=2

)

SELECT S.* FROM TB INNER JOIN  S ON TB.SNO =S.SNO

或采用 INTERSECT 关键字

WITH  TC AS

(

SELECT SNO FROM SC WHERE SC IN ( SELECT CNO FROM C WHERE C.CNAME ='1')

INTERSECT

SELECT SNO FROM SC WHERE SC IN ( SELECT CNO FROM C WHERE C.CNAME ='2')

)

SELECT * FROM S WHERE S.SNO IN ( SELECT * FROM TC )

--- 列出 “1” 号课程 比 “2” 号课程成绩高的所有学生号

 SELECT A.SNO     FROM (     SELECT * FROM  SC WHERE SC.SNO = (SELECT CNO   FROM C  WHERE C.CNAME='1')) A

 LEFT JOIN

  (   SELECT * FROM  SC WHERE SC.SNO = (SELECT CNO   FROM C  WHERE C.CNAME='2')) B

   ON A.SNO=B.SNO AND A.scgrade>B.scgrade

--- 列出 “1” 号课程成绩 比“2” 号课程成绩高所有学生学号 及其 “1” 号课程 及 “2” 号课程成绩

 SELECT A.SNO ,A.scgrade,B.scgrade  FROM (     SELECT * FROM  SC WHERE SC.SNO = (SELECT CNO   FROM C  WHERE C.CNAME='1')) A

 LEFT JOIN

  (   SELECT * FROM  SC WHERE SC.SNO = (SELECT CNO   FROM C  WHERE C.CNAME='2')) B

   ON A.SNO=B.SNO AND A.scgrade>B.scgrade