餐掌柜项目 - 项目三sql的练习 - 《Java 面试总结 & 项目实战》

-- 1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
-- 1.1分别查询"01","02"课程的学生id和分数
SELECT SId,score FROM SC WHERE CId = '01'; 
SELECT SId,score FROM SC WHERE CId = '02'; 
-- 1.2再查询"01"比"02"课程成绩高的学生id和分数
SELECT tmp1.SId,tmp1.score AS '语文',tmp2.score AS '数学' FROM (SELECT SId,score FROM SC WHERE CId = '01') AS tmp1,
(SELECT SId,score FROM SC WHERE CId = '02') AS tmp2 WHERE tmp1.score > tmp2.score AND tmp1.SId = tmp2.SId;   
-- 1.3关联学生表查询学生的信息
SELECT *
FROM student AS stu RIGHT JOIN (SELECT tmp1.SId,tmp1.score AS '语文',tmp2.score AS '数学' FROM (SELECT SId,score FROM SC WHERE CId = '01') AS tmp1,
(SELECT SId,score FROM SC WHERE CId = '02') AS tmp2 WHERE tmp1.score > tmp2.score AND tmp1.SId = tmp2.SId)AS tmp ON stu.SId = tmp.SId;
-- 2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
-- 2.1在成绩表查询每位学生的平均成绩
SELECT avg(score),SId FROM SC GROUP BY SId;
-- 2.2查询出平均成绩大于等于60分的学生id和平均成绩
SELECT avg(score) AS `avg`,SId FROM SC GROUP BY SId HAVING AVG(score) >= 60;
-- 2.3将2.2作为临时表关联学生表查询出学生姓名,学生编号和平均成绩
SELECT stu.*,tmp.avg
FROM Student AS stu
RIGHT JOIN (SELECT avg(score) AS `avg`,SId FROM SC GROUP BY SId HAVING AVG(score) >= 60) AS tmp
ON stu.SId = tmp.SId;
-- 3.查询在 SC 表存在成绩的学生信息
select DISTINCT student.*
from student,sc
where student.SId=sc.SId AND sc.score >= 0;
-- 4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
-- 4.1在成绩表查询每位同学的成绩总和和选课总数
SELECT sc.SId,COUNT(CId) AS cou,SUM(score) AS sum FROM SC GROUP BY sc.SId;
-- 4.2将4.1作为临时表关联查询学生表使用外连接
SELECT *
FROM Student AS stu
LEFT JOIN (SELECT sc.SId,COUNT(CId) AS cou,SUM(score) AS sum FROM SC GROUP BY sc.SId) AS tmp
ON stu.SId = tmp.SId;
-- 4.3补充查询字段
SELECT stu.SId,stu.Sname,stu.Sage,stu.Ssex,tmp.cou,tmp.sum
FROM Student AS stu
LEFT JOIN (SELECT sc.SId,COUNT(CId) AS cou,SUM(score) AS sum FROM SC GROUP BY sc.SId) AS tmp
ON stu.SId = tmp.SId;
-- 5.查有成绩的学生信息
SELECT * FROM sc WHERE EXISTS(score);
-- 6.查询「李」姓老师的数量
SELECT COUNT(*) FROM teacher WHERE Tname LIKE '李%';
-- 7.查询学过「张三」老师授课的同学的信息
-- 7.1确定查询的表(老师表,成绩表,学生表);
SELECT * FROM teacher AS te,sc,student AS stu WHERE sc.SId = stu.SId;
-- 7.2在7.1加入查询条件张三老师,并按照学生id进行分组
SELECT * FROM teacher AS te,sc,student AS stu WHERE sc.SId = stu.SId AND te.Tname = '张三' GROUP BY sc.SId; 
-- 7.3确定最终查询的字段学生信息
SELECT stu.SId,stu.Sname,stu.Sage,stu.Ssex FROM teacher AS te,sc,student AS stu WHERE sc.SId = stu.SId AND te.Tname = '张三' GROUP BY sc.SId; 
-- 8.查询没有学全所有课程的同学的信息
-- 8.1使用聚合函数在课程表中查询一共有几门课
SELECT COUNT(CId) FROM course;
-- 8.2在成绩表根据课程查询每位学生学习课程的数量并筛选出学完课程的学生ID
SELECT sc.SId FROM sc GROUP BY sc.SId HAVING COUNT(sc.CId) = (SELECT COUNT(CId) FROM course);
-- 8.3在8.2查询出了已学完课程的学生ID,根据学生ID在学生表查询出还未学完课程的学生信息(NOT IN)
SELECT
    stu.*
FROM
    student AS stu
WHERE
    stu.SId NOT IN (
        SELECT
            sc.SId
        FROM
            sc
        GROUP BY
            sc.SId
        HAVING
            COUNT(sc.CId) = (SELECT COUNT(CId) FROM course)
)
-- 9.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
-- 9.1在成绩表查询学号01的同学所学习的课程
SELECT CId FROM sc WHERE SId = '01'
-- 9.2在成绩表继续查询至少有同学学习一门9.1查询的课程的每位学生ID
SELECT SId FROM sc WHERE CId IN (SELECT CId FROM sc WHERE SId = '01')
-- 9.3根据9.2查询出的学生ID,在学生表查询查询学生信息
SELECT * FROM student
WHERE SId IN (SELECT SId FROM sc WHERE CId IN (SELECT CId FROM sc WHERE SId = '01'))
-- 10.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
-- 10.1在成绩表查询学号01的同学所学习的课程
SELECT CId FROM sc WHERE SId = '01'
-- 10.2在成绩表查询和'01'同学课程相同且课程数等于3的学生ID
SELECT SId FROM sc WHERE CId IN (SELECT CId FROM sc WHERE SId = '01')GROUP BY sc.SId HAVING COUNT(sc.CId) > 2
-- 10.3在学生表根据10.2的学生ID查询学生信息
SELECT * FROM student 
WHERE SId IN (SELECT SId FROM sc WHERE CId IN (SELECT CId FROM sc WHERE SId = '01') GROUP BY sc.SId HAVING COUNT(sc.CId) > 2)
-- 11.查询没学过"张三"老师讲授的任一门课程的学生姓名
-- 11.1在教师表查询'张三'老师的id
SELECT TId FROM teacher WHERE Tname = '张三'
-- 11.2根据张三老师的id在成绩表查询所教授的课程id
SELECT CId FROM course WHERE TId = (SELECT TId FROM teacher WHERE Tname = '张三')
-- 11.3在成绩表根据课程id查询学习过这门课程的学生id
SELECT SId FROM sc WHERE CId = (SELECT CId FROM course WHERE TId = (SELECT TId FROM teacher WHERE Tname = '张三'))
-- 11.4在学生表查询在11.3以外的学生id
SELECT * FROM student WHERE SId NOT IN(SELECT SId FROM sc WHERE CId = (SELECT CId FROM course WHERE TId = (SELECT TId FROM teacher WHERE Tname = '张三')))
- 12.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
-- 12.1在成绩表查询成绩不及格的学生ID
SELECT SId,CId FROM sc WHERE score < 60
-- 12.2在12.1基础上根据学生进行分组并查询其平均成绩
SELECT SId,AVG(score) FROM sc WHERE score < 60 GROUP BY SId 
-- 12.3根据12.2查询的学生ID关联学生表查询学生姓名学号和平均成绩
SELECT stu.SId,stu.Sname,tmp.avg as '平均成绩'
FROM student AS stu LEFT JOIN (SELECT SId,AVG(score) AS avg FROM sc WHERE score < 60 GROUP BY SId HAVING COUNT(*) >= 2) AS tmp
ON stu.SId = tmp.SId
-- 13.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
-- 13.1在成绩查询课程为'01'的且分数小于60的学生ID
SELECT SId,score FROM sc WHERE CId = '01' AND score < 60
-- 13.2在13.1的基础上把分数按照分数降序排序
SELECT SId FROM sc WHERE CId = '01' AND score < 60 ORDER BY score DESC
-- 13.3根据13.2查询出的学生ID,在学生表查询出学生信息
SELECT * FROM student
WHERE SId IN (SELECT SId FROM sc WHERE CId = '01' AND score < 60 ORDER BY score DESC)
-- 14.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-- 14.1在成绩表查询出每位学生的平均成绩并按照平均成绩降序排序
SELECT SId,AVG(score) FROM sc GROUP BY SId ORDER BY AVG(score) DESC
-- 14.2在14.1的基础上关联成绩表查询所有学生ID和课程成绩和平均成绩
SELECT s.SId,s.CId,s.score,tmp.avg as '平均成绩'
from sc AS s LEFT JOIN (SELECT SId,AVG(score) AS avg FROM sc GROUP BY SId ) AS tmp
ON s.SId = tmp.SId ORDER BY tmp.avg DESC
-- 15.查询各科成绩最高分、最低分和平均分:以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT sc.CId AS '课程ID',MAX(sc.score)AS '最高分',MIN(sc.score) AS '最低分',AVG(sc.score) AS '平均分',COUNT(*) AS '选修人数',
SUM(CASE WHEN sc.score > 60 THEN 1 ELSE 0 END)/COUNT(*) AS '及格率',
SUM(CASE WHEN 70 < sc.score < 80 THEN 1 ELSE 0 END)/COUNT(*) AS '中等',
SUM(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END)/COUNT(*) AS '优秀'
FROM sc GROUP BY CId ORDER BY COUNT(*) DESC,sc.CId ASC
-- 16.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
-- 16.1在成绩表查询每位学生的总成绩,并按照总成绩降序排序
SET @i = 0;
SELECT tmp.SId,total,@i:= @i + 1 AS rank FROM(
SELECT sc.SId,SUM(sc.score) AS total
FROM sc GROUP BY SId ORDER BY SUM(sc.score) DESC)tmp