几道 SQL 语句面试题

--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