说明:以下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