经典SQL题及其答案 （使用Oracle数据库）

//上图表关系中学生表student改为students ，成绩表改为scores。

--1、自行创建测试数据
--表名：CLASS（班级表）,SCORES(成绩表),STUDENTS（学生表）,COURSE（课程名称表）,TEACHER（教师表）
--2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
SELECT SW.STUDENT_ID,SS,TYY FROM(
SELECT STUDENT_ID,"NUMBER" AS SS FROM SCORES,COURSE WHERE SCORES.CORSE_ID=COURSE.CID AND COURSE.CNAME='生物') SW,(
SELECT STUDENT_ID,"NUMBER" AS TYY FROM SCORES,COURSE WHERE SCORES.CORSE_ID=COURSE.CID AND COURSE.CNAME='物理')TY 
WHERE SW.STUDENT_ID=TY.STUDENT_ID AND SS > "NVL"(TYY, 0)--如果物理成绩为空则返回一个0分
--3、查询平均成绩大于60分的同学的学号和平均成绩；
SELECT  SCORES.STUDENT_ID,AVG("NUMBER") FROM SCORES GROUP BY SCORES.STUDENT_ID HAVING AVG("NUMBER")>60 

--4、查询所有同学的学号、姓名、选课数、总成绩；
SELECT*FROM (
SELECT SCORES.STUDENT_ID,"SUM"(SCORES."NUMBER"),"COUNT"(SCORES.STUDENT_ID) FROM SCORES GROUP BY SCORES.STUDENT_ID
) CC LEFT JOIN  STUDENTS on CC.STUDENT_ID = studentS.sid
--5、查询姓“李”的老师的个数；
select count(1) AS b from (select tid from teacher where tname like '李%') 
--6、查询没学过“叶平”老师课的同学的学号、姓名；
SELECT SID,SNAME FROM STUDENTS WHERE SID NOT IN (
SELECT DISTINCT SCORES.STUDENT_ID FROM SCORES WHERE SCORES.CORSE_ID IN(
SELECT CID FROM COURSE LEFT JOIN TEACHER ON COURSE.TEARCH_ID=TEACHER.TID WHERE TNAME='叶平'
)
)
--7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
SELECT SID,SNAME FROM(
SELECT STUDENT_ID FROM(
SELECT SCORES.STUDENT_ID,SCORES.CORSE_ID FROM SCORES WHERE SCORES.CORSE_ID = 1 OR SCORES.CORSE_ID= 2) B
LEFT JOIN STUDENTS ON B.STUDENT_ID = STUDENTS."SID" GROUP BY STUDENT_ID HAVING "COUNT"(STUDENT_ID) > 1) C 
LEFT JOIN STUDENTS ON C.STUDENT_ID=STUDENTS."SID"

--8、查询学过“叶平”(改为波多老师)老师所教的所有课的同学的学号、姓名；

SELECT SID,SNAME FROM STUDENTS WHERE SID IN (
SELECT DISTINCT STUDENT_ID FROM SCORES WHERE SCORES.CORSE_ID IN(
SELECT CID FROM COURSE LEFT JOIN TEACHER ON COURSE.TEARCH_ID=TEACHER.TID WHERE TNAME='波多'))
--9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
SELECT SID,SNAME FROM STUDENTS WHERE SID IN(
SELECT L.STUDENT_ID FROM (
SELECT STUDENT_ID,"NUMBER" AS KKK FROM SCORES WHERE CORSE_ID=001) L LEFT JOIN (
SELECT STUDENT_ID,"NUMBER" AS KK FROM SCORES WHERE CORSE_ID=002) LL ON L.STUDENT_ID=LL.STUDENT_ID WHERE KK<"NVL"(KKK, 0))
--10、查询有课程成绩小于60分的同学的学号、姓名；
SELECT SID,SNAME FROM STUDENTS WHERE SID IN(SELECT DISTINCT STUDENT_ID FROM SCORES WHERE "NUMBER"<60)
--11、查询没有学全所有课的同学的学号、姓名；
SELECT SID,SNAME FROM STUDENTS WHERE SID NOT IN(SELECT STUDENT_ID
from SCORES left join STUDENTS on SCORES.STUDENT_ID = STUDENTS."SID"
group by STUDENT_ID HAVING count(CORSE_ID) = (select count(1) from course))

--查询同学的学号姓名以及所选课程总数
SELECT JJJ."SID",STUDENTS.SNAME,CSUM FROM(--jjj
SELECT JJ."SID",count(JJ.CORSE_ID)AS CSUM FROM(--jj
SELECT DISTINCT STUDENTS."SID",SNAME,CORSE_ID,"NUMBER"from STUDENTS left join SCORES on SCORES.STUDENT_ID = STUDENTS."SID"
)JJ GROUP BY JJ."SID" HAVING count(JJ.CORSE_ID) <(select count(1) from course)
)JJJ LEFT JOIN STUDENTS ON JJJ."SID"=STUDENTS."SID" 

--查询没有学全所有课的同学的学号、姓名；（最简洁版）
select STUDENTS."SID",SNAME
from  STUDENTS left join SCORES on SCORES.STUDENT_ID = STUDENTS."SID"
group by STUDENTS."SID",SNAME HAVING count(corse_id) < (select count(1) from course)
--12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
SELECT SCORES.STUDENT_ID,SNAME FROM SCORES LEFT JOIN STUDENTS ON SCORES.STUDENT_ID=STUDENTS."SID" 
WHERE STUDENT_ID!=1 AND CORSE_ID IN(SELECT SCORES.CORSE_ID FROM SCORES WHERE SCORES.STUDENT_ID =001)--获取001号同学所学的科目
GROUP BY SCORES.STUDENT_ID,SNAME --HAVING "COUNT"(CORSE_ID)=(SELECT "COUNT"(SCORES.CORSE_ID) FROM SCORES WHERE SCORES.STUDENT_ID =001) 
--13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名；
SELECT SCORES.STUDENT_ID,SNAME FROM SCORES LEFT JOIN STUDENTS ON SCORES.STUDENT_ID=STUDENTS."SID" 
WHERE STUDENT_ID!=1 AND CORSE_ID IN(SELECT SCORES.CORSE_ID FROM SCORES WHERE SCORES.STUDENT_ID =001)--获取001号同学所学的科目
GROUP BY SCORES.STUDENT_ID,SNAME HAVING "COUNT"(CORSE_ID)=(SELECT "COUNT"(SCORES.CORSE_ID) FROM SCORES WHERE SCORES.STUDENT_ID =001)
--14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；

--15、删除学习“叶平”老师课的SC表记录；

--16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；

-- 17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

-- 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select corse_id, max("NUMBER") as max_num, min("NUMBER") as min_num from SCORES group by corse_id;
-- 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；

-- 20、课程平均分从高到低显示（现实任课老师）；
SELECT "AVG"("NUMBER"),TNAME,CORSE_ID FROM SCORES 
LEFT JOIN COURSE ON SCORES.CORSE_ID=COURSE.CID 
LEFT JOIN TEACHER ON COURSE.TEARCH_ID= TEACHER.TID GROUP BY
CORSE_ID,TNAME ORDER BY "AVG"("NUMBER") DESC
-- 21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT ROWNUM RN,IK.*FROM(SELECT * FROM SCORES  ORDER BY "NUMBER" DESC)IK WHERE ROWNUM<4
SELECT CORSE_ID FROM SCORES GROUP BY CORSE_ID
-- 22、查询每门课程被选修的学生数；
SELECT CORSE_ID,"COUNT"(1)FROM SCORES GROUP BY CORSE_ID 
-- 23、查询出只选修了一门课程的全部学生的学号和姓名；
SELECT STUDENTS."SID",SNAME,"COUNT"(1) FROM SCORES LEFT JOIN STUDENTS ON STUDENTS."SID"=SCORES.STUDENT_ID GROUP BY STUDENTS."SID",SNAME HAVING "COUNT"(1)=1
-- 24、查询男生、女生的人数；
SELECT GENDER,"COUNT"(GENDER)AS num FROM STUDENTS GROUP BY GENDER
-- 25、查询姓“张”的学生名单；
SELECT SNAME FROM STUDENTS WHERE SNAME LIKE '张%'
-- 26、查询同名同姓学生名单，并统计同名人数；
SELECT SNAME,"COUNT"(1) AS "COUNT" FROM STUDENTS GROUP BY SNAME
-- 27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
SELECT CORSE_ID,"AVG"("NVL"("NUMBER",0))AS "AVG" FROM SCORES GROUP BY CORSE_ID ORDER BY "AVG" DESC,CORSE_ID DESC
-- 28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
SELECT  STUDENT_ID,SNAME,"AVG"("NUMBER")AS "AVG" FROM SCORES LEFT JOIN STUDENTS ON SCORES.STUDENT_ID=STUDENTS."SID" GROUP BY STUDENT_ID,SNAME HAVING "AVG"("NUMBER")>85
-- 29、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
SELECT STUDENT_ID,SNAME,"NUMBER" FROM SCORES LEFT JOIN COURSE ON SCORES.CORSE_ID=COURSE.CID LEFT JOIN STUDENTS ON STUDENTS."SID"=SCORES.STUDENT_ID WHERE CNAME='体育' AND "NUMBER"<60
-- 30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
SELECT STUDENT_ID,SNAME FROM SCORES LEFT JOIN STUDENTS ON STUDENTS."SID"=SCORES.STUDENT_ID WHERE CORSE_ID=3 AND "NUMBER">80
-- 31、求选了课程的学生人数

-- 32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

-- 33、查询各个课程及相应的选修人数；

-- 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

-- 35、查询每门课程成绩最好的前两名；

-- 36、检索至少选修两门课程的学生学号；

-- 37、查询全部学生都选修的课程的课程号和课程名；
SELECT CORSE_ID,CNAME,"COUNT"(1) FROM SCORES LEFT JOIN COURSE ON SCORES.CORSE_ID=COURSE.CID GROUP BY CORSE_ID,CNAME HAVING "COUNT"(1) =(SELECT "COUNT"(1)FROM STUDENTS)
-- 38、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
SELECT SNAME FROM STUDENTS WHERE SNAME NOT IN (SELECT SNAME FROM TEACHER,SCORES,STUDENTS,COURSE WHERE TEACHER.TID=COURSE.TEARCH_ID AND COURSE.CID=SCORES.CORSE_ID AND SCORES.STUDENT_ID=STUDENTS."SID" AND TEACHER.TNAME='叶平')
-- 39、查询两门以上不及格课程的同学的学号及其平均成绩；
 SELECT STUDENT_ID,"AVG"("NUMBER"),"COUNT"(1) FROM SCORES WHERE "NUMBER"<60 GROUP BY STUDENT_ID HAVING "COUNT"(1)>2
-- 40、检索“004”课程分数小于60，按分数降序排列的同学学号；
SELECT STUDENT_ID FROM SCORES WHERE CORSE_ID=4 AND "NUMBER"<60 ORDER BY "NUMBER" DESC
-- 41、删除“002”同学的“001”课程的成绩；
DELETE FROM SCORES WHERE CORSE_ID=001 AND STUDENT_ID=002