SQL（之一）-SQL经典题目

说明：以下SQL练习题（主要针对DBMS为ORACLE），题目来自网络，本人整理编辑完成，难度分为1-5星。

涉及的4个表的表间关系：

--0-查询每个学生、最高学科的成绩、及最高成绩的所在学科在班级中排名-★★★

--RANK() OVER(PARTITION BY XXX ORDER BY XXX ) 使用分析函数
SELECT A.SID,
       A.CID,
       A.SCORE,
       DENSE_RANK() OVER(PARTITION BY A.CID ORDER BY A.SCORE DESC NULLS LAST) AS 学生最高分数所在该科目排名,
       A.TOTAL_MAX_SCORE 当前科目最高分
  FROM (SELECT T.SID,
               T.CID,
               T.SCORE,
               MAX(T.SCORE) OVER(PARTITION BY T.SID) PRESON_MAX_SCORE, --个人最高分
               MAX(T.SCORE) OVER(PARTITION BY T.CID) TOTAL_MAX_SCORE --当前科目最高分
          FROM SC T) A
 WHERE A.SCORE = A.PRESON_MAX_SCORE
 ORDER BY 2;

--1、查询"001"课程比"002"课程成绩高的所有学生的学号-★

SELECT T1.SID ,T1.SCORE ,T2.SCORE FROM SC T1 
LEFT JOIN SC T2 ON T1.SID=T2.SID 
WHERE T1.CID='001' AND T2.CID='002' AND T1.SCORE >T2.SCORE  ORDER BY 1;

--2、查询平均成绩大于60分的同学的学号和平均成绩-★

SELECT T1.SID ,ROUND(AVG(T1.SCORE),2) AVG_SCORE FROM SC T1 
GROUP BY T1.SID  HAVING AVG(T1.SCORE)>60  ORDER BY 2 DESC,1 ;

--3、查询所有同学的学号、姓名、选课数、总成绩-★

SELECT T1.SID   ,T2.SNAME  ,COUNT(T1.CID) COUNT_CID  ,SUM(T1.SCORE)SUM_SCORE  FROM SC T1
LEFT JOIN STUDENT T2 ON T1.SID=T2.SID 
GROUP BY T1.SID  ,T2.SNAME  ORDER BY 1,4 DESC,3 ;

--4、查询姓"李"的老师的个数-★

SELECT COUNT (1) FROM TEACHER T1 WHERE T1.TNAME LIKE '李%';

--5A、查询没学过"叶平"老师课的同学的学号、姓名-★★
--5B、查询没学过"叶平"老师讲授的任一门课程的学生姓名-★★ 

--思路：反其道而行之,查询学过"叶平"老师 任意课程 的学生,然后排除这些学生
--方式1：
SELECT DISTINCT T4.SID,T4.SNAME FROM STUDENT  T4 WHERE T4.SID NOT IN ( 
SELECT T3.SID FROM SC T3 WHERE T3.CID  IN (
 SELECT T2.CID FROM TEACHER T1 ,COURSE T2 WHERE T1.TID=T2.TID  AND  T1.TNAME ='叶平' 
 )) ORDER BY 1 ;

--方式2
SELECT  DISTINCT T4.SID,T4.SNAME  FROM STUDENT T4 WHERE T4.SID NOT IN (
SELECT DISTINCT  T1.SID FROM SC T1 WHERE    EXISTS 
(SELECT * FROM COURSE T2, TEACHER T3 WHERE T3.TNAME='叶平'  AND T1.CID=T2.CID AND T2.TID=T3.TID) 
)ORDER BY 1;

--6、查询学过"001"并且也学过编号"002"课程的同学的学号、姓名-★

SELECT DISTINCT T1.SID ,T3.SNAME   FROM SC T1
  LEFT JOIN SC T2 ON T1.SID=T2.SID 
  LEFT JOIN STUDENT T3 ON T3.SID=T1.SID AND T3.SID=T2.SID 
WHERE T1.CID='001' AND T2.CID='002'
ORDER BY 1 ;
--或者
SELECT T1.SID ,T3.SNAME  FROM SC T1,SC T2 ,STUDENT T3 WHERE  T1.CID='001'AND T2.CID='002' AND T1.SID=T2.SID 
AND T1.SID=T3.SID 
ORDER BY 1 ;

--7、查询学过"叶平"老师所教的所有课的同学的学号、姓名-★★

SELECT A.SID,B.SNAME FROM (
  SELECT  DISTINCT T1.SID,T1.CID  FROM SC T1 
  WHERE  T1.CID IN(SELECT T2.CID FROM TEACHER T1 ,COURSE T2 WHERE T1.TID=T2.TID  AND  T1.TNAME ='叶平')--查询出叶平老师所教课程的课程ID
)A --临时表
LEFT JOIN STUDENT B ON A.SID=B.SID GROUP BY A.SID,B.SNAME 
HAVING COUNT(DISTINCT A.CID)>=
(SELECT COUNT(DISTINCT T2.CID ) FROM TEACHER T1 ,COURSE T2 WHERE T1.TID=T2.TID AND  T1.TNAME ='叶平')--查询出叶平老师所教课程的课程数量
ORDER BY 1  ;

--8、查询课程编号"002"的成绩比课程编号"001"课程低的所有同学的学号、姓名-★
 

SELECT T1.SID , T3.SNAME ,T1.SCORE AS SC_002 ,T2.SCORE  AS SC_001 
       FROM SC T1 
       LEFT JOIN SC T2 ON T1.SID=T2.SID 
       LEFT JOIN STUDENT T3 ON T1.SID=T3.SID AND T3.SID=T2.SID 
WHERE T1.CID='002' AND T2.CID='001' AND T1.SCORE<T2.SCORE 
ORDER BY 3 DESC