• 学生表、课程表、 成绩表 、教师表sql练习

    转自：http://yuncode.net/code/c_58df7a8ca687e58

    1、查询“1”课程比“2”课程成绩高的所有学生的学号；
    SELECT t1.student_id
    FROM
        (SELECT score, student_id FROM student_score WHERE course_id = 1 ) t1,
        (SELECT score, student_id FROM student_score WHERE course_id = 2 ) t2
    WHERE t1.score > t2.score AND t1.student_id = t2.student_id
     
    2、查询平均成绩大于60分的同学的学号和平均成绩：
    select student_id, avg(score) from student_score group by student_id HAVING avg(score)>=60 ;
     
    3、查询所有同学的学号、姓名、选课数、总成绩；
    select ss.student_id, st.student_name, count(course_id), sum(score) from student_score ss
    left join student st on ss.student_id=st.student_id
    group by ss.student_id, st.student_name
    order by ss.student_id
     
    4、查询姓“李”的老师的个数；
    select count(teacher_name) from teacher where teacher_name like '李%'
     
    5、查询没学过“叶平”老师课的同学的学号、姓名；
    select student_id,student_name from student where student_id not in (
        select ss.student_id from
        student_score ss
        left join course co on ss.course_id=co.course_id
        left join teacher te on te.teacher_id= co.teacher_id
        where te.teacher_name='叶平'
        group by ss.student_id
    )
     
    6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
    select st.student_id,st.student_name from student st
    inner join (select student_id from student_score where course_id=1) t1 on st.student_id=t1.student_id
    inner join (select student_id from student_score where course_id=2) t2 on t1.student_id=t2.student_id
     
    7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
    SELECT
        st.student_id,st.student_name
    FROM student_score ss
    inner join student st on ss.student_id=st.student_id
    inner join course co on ss.course_id=co.course_id
    inner join teacher te on te.teacher_id=co.teacher_id
    where te.teacher_name='叶平'
     
    8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
    select st.student_id,st.student_name from student st
    inner join (select student_id, score from student_score where course_id=1) t1 on st.student_id=t1.student_id
    inner join (select student_id, score from student_score where course_id=2) t2 on t1.student_id=t2.student_id
    where t2.score<t1.score
     
    9、查询所有课程成绩小于60分的同学的学号、姓名；
    SELECT st.student_id,st.student_name FROM student st WHERE st.student_id not in (
        SELECT DISTINCT ss.student_id FROM student_score ss
        WHERE ss.score>=60
    )
     
    10、查询没有学全所有课的同学的学号、姓名；
    select st.student_id, st.student_name from student_score ss
    inner join student st on st.student_id=ss.student_id
    group by st.student_id,st.student_name
    having count(ss.course_id)<(select count(course_id) from course)
     
    11、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
    select st.student_id,st.student_name from
    student st inner join student_score ss on st.student_id=ss.student_id
    where course_id in(select course_id from student_score where student_id=1)
    group by st.student_id,st.student_name
     
    14、查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名；
    select ss.student_id from student_score ss
    inner join student st on ss.student_id=st.student_id
    where ss.course_id in (select course_id from student_score where student_id=1)
    group by ss.student_id
    having count(ss.course_id)=(select count(course_id) from course)
     
    17、按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门课程成绩
    SELECT ss.student_id,
        avg(ss.score) as 平均成绩,
        (select score from student_score
        where course_id = (SELECT course_id FROM course  WHERE course_name ='语文')
        and student_id = ss.student_id) as 语文,
        (select score from student_score
        where course_id = (SELECT course_id FROM course  WHERE course_name ='数学')
        and student_id = ss.student_id) as 数学,
        (select score from student_score
        where course_id = (SELECT course_id FROM course  WHERE course_name ='英语')
        and student_id = ss.student_id) as 英语
    FROM student_score ss
    GROUP BY ss.student_id,语文,数学,英语
    ORDER BY avg(ss.score)  DESC
     
    18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
    SELECT course_id AS 课程id, MAX (score) AS 最高分, MIN (score) AS 最低分
    FROM student_score
    GROUP BY course_id
     
    19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
    SELECT
        co.course_name AS 科目名称,
        avg (ss.score) as 平均成绩,
        CAST (CAST (SUM(case WHEN COALESCE(ss.score,0)>=60 THEN 1 ELSE 0 END) AS FLOAT)/
        (SELECT COUNT(*) FROM course) AS DECIMAL (10, 2))
    FROM
        student_score ss
    INNER JOIN course co ON ss.course_id = co.course_id
    GROUP BY
        co.course_name
     
    或者
     
    SELECT
        student_id,
        CAST (AVG(score) AS DECIMAL(10, 2)) AS 平均成绩,
        CAST (CAST (COUNT(*) AS FLOAT) / (SELECT COUNT(*) FROM course) AS DECIMAL (10, 2)) AS 及格率
    FROM student_score
    WHERE   score >= 60
    GROUP BY student_id
    ORDER BY AVG (score),CAST (CAST (COUNT(*) AS FLOAT) / (SELECT COUNT(*) FROM course) AS DECIMAL (10, 2)) desc
     
    24、查询学生平均成绩及其名次
    SELECT 1 + (
            SELECT COUNT (DISTINCT 平均成绩)
            FROM
                ( SELECT student_id, AVG (score) AS 平均成绩 FROM student_score GROUP BY student_id ) AS T1
            WHERE 平均成绩 > T2.平均成绩
        ) AS 名次,
        student_id AS 学生学号,
        平均成绩
    FROM
        ( SELECT student_id, AVG (score) 平均成绩 FROM student_score GROUP BY   student_id ) AS T2
    ORDER BY
        平均成绩 DESC;
     
    25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
    SELECT ss.student_id, ss.course_id, ss.score
    FROM student_score ss
    GROUP BY ss.student_id,     ss.course_id, ss.score
    HAVING student_id in (select student_id from student_score where ss.course_id=course_id LIMIT  3)
    ORDER BY ss.course_id, ss.score
     
    26、查询每门课程被选修的学生数
    SELECT course_id, COUNT (course_id)
    FROM student_score
    GROUP BY    course_id
    ORDER BY    course_id
     
    27、查询出只选修了一门课程的全部学生的学号和姓名
    SELECT ss.student_id,st.student_name, COUNT (ss.course_id)
    FROM student_score ss
    inner join student st on st.student_id=ss.student_id
    GROUP BY    ss.student_id,st.student_name
    having COUNT (ss.course_id)=1
    ORDER BY    ss.student_id
     
    28、查询男生、女生人数
    SELECT student_sex, COUNT (student_id)
    FROM student
    GROUP BY student_sex
     
    29、查询姓“张”的学生名单
    SELECT * FROM   student WHERE student_name LIKE '张%'
     
    30、查询同名同性学生名单，并统计同名人数
    SELECT student_name,count(*) FROM   student group by student_name having count(*)>1
     
    32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
    select course_id,avg(score) from student_score group by course_id order by avg(score) asc,course_id
     
    33、查询平均成绩大于70的所有学生的学号、姓名和平均成绩
    SELECT ss.student_id,st.student_name, AVG (ss.score)
    FROM student_score ss
    inner join student st on ss.student_id=st.student_id
    GROUP BY ss.student_id,st.student_name
    ORDER BY AVG (ss.score) ASC, ss.student_id
     
    34、查询课程名称为“语文”，且分数低于60的学生姓名和分数
    select st.student_name,ss.score
    from student_score ss
    inner join student st on ss.student_id=st.student_id
    inner join course co on ss.course_id=co.course_id
    where co.course_name='语文' and ss.score<60
     
    35、查询所有学生的选课情况；
    select st.student_name,co.course_name,ss.score
    from student_score ss
    inner join student st on ss.student_id=st.student_id
    inner join course co on ss.course_id=co.course_id
     
    36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
    select st.student_name,co.course_name,ss.score
    from student_score ss
    inner join student st on ss.student_id=st.student_id
    inner join course co on ss.course_id=co.course_id
    where ss.score>70
     
    40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
    select st.student_name,co.course_name,te.teacher_name,ss.score
    from student_score ss
    inner join student st on ss.student_id=st.student_id
    inner join course co on ss.course_id=co.course_id
    inner join teacher te on co.teacher_id=te.teacher_id
    where te.teacher_name='叶平'
    order by ss.score desc
    limit 1
     
    41、查询各个课程及相应的选修人数
    select course_id,count(course_id) from student_score group by course_id order by course_id
     
    43、查询每门功成绩最好的前两名
    SELECT
        t0.student_id, t0.course_id, t0.score
    FROM student_score t0
    WHERE
        t0.student_score_id IN (
            SELECT student_score_id
            FROM student_score
            WHERE t0.course_id = course_id
            ORDER BY    score DESC
            limit 2
        )
     
    44、统计每门课程的学生选修人数，要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人    数相同，按课程号升序排列
    select course_id,count(student_id) from student_score group by course_id order by count(student_id) desc, course_id
     
    45、检索至少选修两门课程的学生学号
    select student_id,count(*)
    from student_score
    group by student_id
    having count(*)>=2
    order by count(*) desc, student_id
     
    46、查询全部学生都选修的课程的课程号和课程名
    SELECT co.course_id, co.course_name
    FROM student_score ss
    INNER JOIN course co ON co.course_id = ss.course_id
    GROUP BY co.course_id, co.course_name
    HAVING COUNT(co.course_id) = (SELECT COUNT (student_id) FROM student)
     
    48、查询两门以上不及格课程的同学的学号及其平均成绩
    select student_id,avg(score) from student_score where score<60 group by student_id having count(1)>=2
  • 相关阅读:
    Delphi XE5 android 蓝牙通讯传输
    Delphi XE5 android toast
    Delphi XE5 android openurl(转)
    Delphi XE5 如何设计并使用FireMonkeyStyle(转)
    Delphi XE5 android 捕获几个事件
    Delphi XE5 android listview
    Delphi XE5 android 黑屏的临时解决办法
    Delphi XE5 android popumenu
    Delphi XE5 android 获取网络状态
    Delphi XE5 android 获取电池电量
  • 原文地址：https://www.cnblogs.com/sharpest/p/9981193.html
Copyright © 2020-2023  润新知