• sql 练习


       今天去面试发现自己的sql很差,多以打算恶补一下.

    建表:

    DROP TABLE IF EXISTS `course`;
    CREATE TABLE `course` (
      `Cno` varchar(10) DEFAULT NULL,
      `Cname` varchar(10) DEFAULT NULL,
      `Tno` varchar(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of course
    -- ----------------------------
    INSERT INTO `course` VALUES ('01', '语文', '02');
    INSERT INTO `course` VALUES ('02', '数学', '01');
    INSERT INTO `course` VALUES ('03', '英语', '03');
    
    -- ----------------------------
    -- Table structure for sc
    -- ----------------------------
    DROP TABLE IF EXISTS `sc`;
    CREATE TABLE `sc` (
      `Sno` varchar(10) DEFAULT NULL,
      `Cno` varchar(10) DEFAULT NULL,
      `score` decimal(18,1) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of sc
    -- ----------------------------
    INSERT INTO `sc` VALUES ('01', '01', '80.0');
    INSERT INTO `sc` VALUES ('01', '02', '91.0');
    INSERT INTO `sc` VALUES ('01', '03', '99.0');
    INSERT INTO `sc` VALUES ('02', '01', '70.0');
    INSERT INTO `sc` VALUES ('02', '02', '60.0');
    INSERT INTO `sc` VALUES ('02', '03', '80.0');
    INSERT INTO `sc` VALUES ('03', '01', '81.0');
    INSERT INTO `sc` VALUES ('03', '02', '82.0');
    INSERT INTO `sc` VALUES ('03', '03', '79.0');
    INSERT INTO `sc` VALUES ('04', '01', '50.0');
    INSERT INTO `sc` VALUES ('04', '02', '30.0');
    INSERT INTO `sc` VALUES ('04', '03', '20.0');
    INSERT INTO `sc` VALUES ('05', '01', '76.0');
    INSERT INTO `sc` VALUES ('05', '02', '87.0');
    INSERT INTO `sc` VALUES ('06', '01', '31.0');
    INSERT INTO `sc` VALUES ('06', '03', '34.0');
    INSERT INTO `sc` VALUES ('07', '02', '89.0');
    INSERT INTO `sc` VALUES ('07', '03', '98.0');
    
    -- ----------------------------
    -- Table structure for student
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student` (
      `Sno` varchar(10) DEFAULT NULL,
      `Sname` varchar(10) DEFAULT NULL,
      `Sage` datetime DEFAULT NULL,
      `Ssex` varchar(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '');
    INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '');
    INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20 00:00:00', '');
    INSERT INTO `student` VALUES ('04', '李云', '1990-08-06 00:00:00', '');
    INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '');
    INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01 00:00:00', '');
    INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01 00:00:00', '');
    INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20 00:00:00', '');
    INSERT INTO `student` VALUES ('09', '李云', '1992-10-01 10:34:18', '');
    
    -- ----------------------------
    -- Table structure for teacher
    -- ----------------------------
    DROP TABLE IF EXISTS `teacher`;
    CREATE TABLE `teacher` (
      `Tno` varchar(10) DEFAULT NULL,
      `Tname` varchar(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of teacher
    -- ----------------------------
    INSERT INTO `teacher` VALUES ('01', '张三');
    INSERT INTO `teacher` VALUES ('02', '李四');
    INSERT INTO `teacher` VALUES ('03', '王五');
    View Code

    sql:

    -- 1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
    SELECT
        *
    FROM
        (
            SELECT
                *
            FROM
                sc
            WHERE
                sc.Cno = '01'
        ) sc1
    JOIN (
        SELECT
            *
        FROM
            sc
        WHERE
            sc.Cno = '02'
    ) sc2 ON sc1.sno = sc2.sno
    WHERE
        sc1.score > sc2.score;
    
    --  1.1 查询同时存在" 01 "课程和" 02 "课程的情况
    SELECT
        *
    FROM
        (
            SELECT
                *
            FROM
                sc
            WHERE
                sc.Cno = '01'
        ) sc1
    LEFT JOIN (
        SELECT
            *
        FROM
            sc
        WHERE
            sc.Cno = '02'
    ) sc2 ON sc1.sno = sc2.sno
    WHERE
        sc2.sno IS NOT NULL;
    
    -- 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
    SELECT
        *
    FROM
        (
            SELECT
                *
            FROM
                sc
            WHERE
                sc.Cno = '01'
        ) sc1
    LEFT JOIN (
        SELECT
            *
        FROM
            sc
        WHERE
            sc.Cno = '02'
    ) sc2 ON sc1.sno = sc2.sno;
    
    -- 查询不存在" 01 "课程但存在" 02 "课程的情况
    SELECT
        *
    FROM
        sc
    WHERE
        sc.Cno = '02'
    AND sc.Sno NOT IN (
        SELECT
            sc.sno
        FROM
            sc
        WHERE
            sc.Cno = '01'
    );
    
    -- 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩  
    SELECT
        st.sno,
        st.Sname,
        sc2.score
    FROM
        student st
    JOIN (
        SELECT
            sc.sno,
            AVG(sc.score) AS score
        FROM
            sc
        GROUP BY
            sc.sno
        HAVING
            AVG(sc.score) >= 60
    ) sc2 ON sc2.sno = st.Sno;
    
    -- 3. 查询在 SC 表存在成绩的学生信息 
    SELECT
        *
    FROM
        student
    WHERE
        student.Sno IN (SELECT sno FROM sc GROUP BY sno);
    
    SELECT
        *
    FROM
        student
    WHERE
        student.Sno IN (SELECT DISTINCT sno FROM sc);
    
    SELECT
        st.*
    FROM
        student st
    WHERE
        EXISTS (
            SELECT
                *
            FROM
                sc s
            WHERE
                s.sno = st.sno
        );
    
    -- 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)  
    SELECT
        A.Sno AS 学生编号,
        A.Sname AS 学生姓名,
        B.`选课数量`,
        B.`总成绩`
    FROM
        student AS A
    LEFT JOIN (
        SELECT
            sc.Sno,
            COUNT(sc.score) AS 选课数量,
            SUM(sc.score) AS 总成绩
        FROM
            sc
        GROUP BY
            sc.Sno
    ) AS B ON A.Sno = B.Sno;
    
    -- 4.1 查有成绩的学生信息
    SELECT
        *
    FROM
        student S
    JOIN (
        SELECT
            sc.Sno
        FROM
            sc
        GROUP BY
            sc.Sno
    ) A ON S.Sno = A.Sno;
    
    SELECT
        *
    FROM
        student s
    WHERE
        EXISTS (
            SELECT
                *
            FROM
                (
                    SELECT
                        sc.Sno
                    FROM
                        sc
                    GROUP BY
                        sc.Sno
                ) A
            WHERE
                s.sno = A.sno
        );
    
    -- 5.查询「李」姓老师的数量   
    SELECT
        count(*)
    FROM
        teacher
    WHERE
        Tname LIKE '李%';
    
    -- 6.查询学过「张三」老师授课的同学的信息
    SELECT
        *
    FROM
        student
    WHERE
        student.Sno IN (
            SELECT
                Sno
            FROM
                sc
            WHERE
                sc.Cno IN (
                    SELECT
                        Cno
                    FROM
                        course
                    WHERE
                        course.Tno IN (
                            SELECT
                                Tno
                            FROM
                                teacher
                            WHERE
                                Tname = '张三'
                        )
                )
        ) -- 7.查询没有学全所有课程的同学的信息
        SELECT
            student.*
        FROM
            student
        LEFT JOIN (SELECT sno FROM sc GROUP BY sno) s1 ON s1.sno = student.Sno
        WHERE
            s1.sno IS NULL -- 查出没学的
        UNION
            SELECT
                student.*
            FROM
                student
            JOIN (
                SELECT
                    sno
                FROM
                    sc
                GROUP BY
                    sno
                HAVING
                    COUNT(1) < (SELECT COUNT(1) FROM course)
            ) s2 ON s2.sno = student.Sno;
    
    -- 查出没学全的
    -- 8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
    SELECT
        *
    FROM
        student
    WHERE
        student.Sno IN (
            SELECT DISTINCT
                sc.Sno
            FROM
                sc
            WHERE
                sc.Cno IN (
                    SELECT
                        cno
                    FROM
                        sc
                    WHERE
                        sc.Sno = '01'
                )
        );
    
    -- 9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
    SELECT
        *
    FROM
        Student
    WHERE
        Sno IN (
            SELECT
                Sno
            FROM
                SC
            WHERE
                Cno IN (
                    SELECT DISTINCT
                        Cno
                    FROM
                        SC
                    WHERE
                        Sno = '01'
                )
            AND Sno <> '01'
            GROUP BY
                Sno
            HAVING
                COUNT(Cno) >= 3
        );
    
    -- 10. 查询没学过「张三」老师讲授的任一门课程的学生姓名 
    SELECT
        *
    FROM
        student
    WHERE
        student.Sno NOT IN (
            SELECT
                sno
            FROM
                sc
            WHERE
                sc.Cno IN (
                    SELECT
                        course.cno
                    FROM
                        course
                    WHERE
                        course.Tno = (
                            SELECT
                                tno
                            FROM
                                teacher
                            WHERE
                                teacher.Tname = '张三'
                        )
                )
        );
    
    -- 11.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
    SELECT
        student.*, s2.avg
    FROM
        student
    JOIN (
        SELECT
            sc.Sno,
            AVG(sc.score) AS avg
        FROM
            sc
        WHERE
            sc.score < 60
        GROUP BY
            sc.Sno
        HAVING
            COUNT(1) >= 2
    ) s2 ON s2.Sno = student.Sno -- 12.检索" 01 "课程分数小于 60 ,按分数降序排列的学生信息   
    SELECT
        *
    FROM
        sc
    WHERE
        sc.Cno = '01'
    AND sc.score < 60
    ORDER BY
        sc.score DESC;
    
    -- 13. (静态写法)按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    SELECT
        info.sno AS 学号,
        info.Sname AS 学生姓名,
        SUM(
            CASE info.Cname
            WHEN '语文' THEN
                info.score
            END
        ) AS 语文,
        SUM(
            CASE info.Cname
            WHEN '数学' THEN
                info.score
            END
        ) AS 数学,
        SUM(
            CASE info.Cname
            WHEN '英语' THEN
                info.score
            END
        ) AS 英语,
        SUM(info.score)/3 AS 平均分
    FROM
        (
            SELECT
                student.sno,
                student.Sname,
                course.Cname,
                sc.score
            FROM
                sc
            JOIN student ON sc.Sno = student.Sno
            JOIN course ON sc.Cno = course.Cno
        ) AS info
    GROUP BY
        info.sno
    
    
    -- 19. 查询每门课程被选修的学生数  
    
    select  sc.Cno ,count(1) from  sc group by  sc.Cno ;
    
    
    -- 20. 查询出只选修两门课程的学生学号和姓名   
    
    
    SELECT
        *
    FROM
        student
    WHERE
        EXISTS (
            SELECT
                *
            FROM
                (
                    SELECT
                        sc.Sno
                    FROM
                        sc
                    GROUP BY
                        sc.Sno
                    HAVING
                        COUNT(1) = 2
                ) a
            WHERE
                a.Sno = student.Sno
        );
    
    
    
    SELECT
        *
    FROM
        student
    WHERE
        student.Sno IN (
            SELECT
                sc.Sno
            FROM
                sc
            GROUP BY
                sc.Sno
            HAVING
                COUNT(1) = 2
        );
    
    -- 21. 查询男生、女生人数  
      
    select Ssex,COUNT(Ssex)人数 from Student group by Ssex    ;
    
    -- 22. 查询名字中含有「风」字的学生信息 
    select  * from  student where student.Sname like  '%风%';
    
    -- 23. 查询同名同性学生名单,并统计同名人数 
    
    SELECT
        Sname,
        Ssex,
        COUNT(*) 同名人数
    FROM
        Student
    GROUP BY
        Sname,
        Ssex
    HAVING
        COUNT(*) > 1;
    
    -- 24.查询 1990 年出生的学生名单 
    select  * from  student where year(student.Sage)='1990';
    
    -- 25. 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列  
    
    select  sc.Cno,AVG(sc.score) as score from  sc  GROUP BY sc.Cno ORDER BY score desc ,cno asc;
    
    
    -- 26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
    
    select  student.sno,student.Sname,a.scoreavg from  student  join(
    select  sc.Sno,AVG(sc.score) as scoreavg  from  sc group  by  sc.Sno HAVING AVG(sc.score)>=85
    )a on  a.Sno=student.Sno;
    
    
    -- 27. 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
    SELECT
        student.Sno,
        student.Sname,
        course.Cname,
        sc.score
    FROM
        sc
    LEFT JOIN student ON student.Sno = sc.sno
    LEFT JOIN course  ON course.Cno=sc.Cno
    WHERE
        sc.score < 60
    AND EXISTS (
        SELECT
            *
        FROM
            (
                SELECT
                    *
                FROM
                    course
                WHERE
                    course.Cname = '数学'
            ) a
        WHERE
            a.cno = sc.Cno
    );
    
    
    -- 28. 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
    
    
    SELECT
        student.sno,
        student.Sname,
        course.Cname,
        sc.score
    FROM
        student
    LEFT JOIN sc ON sc.Sno = student.Sno
    LEFT JOIN course ON course.Cno = sc.Cno;
    
    
    -- 29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
    select  * from  sc where EXISTS (select  * from  (select  sno from  sc  where  sc.score>=80 GROUP BY sc.Sno HAVING COUNT(1)>=(select  count(1) from  course)) a where a.sno= sc.sno );
    
    
    -- 30. 查询不及格的课程
    select course.Cname  from  sc   join course  on  course.Cno=sc.Cno   where sc.score<60 GROUP BY course.Cname;
    
    
    -- 31. 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
    
    
    SELECT
        student.Sno,
        student.Sname,
        a.score
    FROM
        student
    JOIN (
        SELECT
            *
        FROM
            sc
        WHERE
            sc.Cno = '01'
        AND sc.score >= 80
    ) a ON a.sno = student.sno
    
    
    -- 32. 求每门课程的学生人数   
    select  sc.cno as 课程编号 ,COUNT(1)  as 人数 from  sc   GROUP BY sc.Cno
    
    --  33.选修张三老师的课程的学生中,成绩最高
    SELECT
        student.Sname, sc.score
    FROM
        sc
    JOIN student ON student.sno = sc.Sno
    WHERE
        sc.score = (
            SELECT
                MAX(sc.score)
            FROM
                sc
            WHERE
                sc.Cno IN (
                    SELECT
                        cno
                    FROM
                        course
                    WHERE
                        course.Tno = (
                            SELECT
                                tno
                            FROM
                                teacher
                            WHERE
                                teacher.Tname = '张三'
                        )
                )
        );
    
    
    -- 37.统计每门课程的学生选修人数(超过5人的课程才统计)。  
    -- 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列 
    select  sc.cno,COUNT(1) as num from  sc group  by  sc.Cno  HAVING COUNT(1)>5 order  by  num desc ,sc.cno ;
    
    -- 38. 检索至少选修两门课程的学生学号 
    select sc.Sno,COUNT(1) from  sc group  by  sc.Sno HAVING COUNT(1)>2;
    
    
    -- 39. 查询选修了全部课程的学生信息
    select  sc.Sno,COUNT(1) from  sc  group  by  sc.Sno HAVING COUNT(1)=(select  COUNT(1) from  course);
    
    
    -- 40. 查询各学生的年龄,只按年份来算 
    select  student.sno,student.Sname,(YEAR(SYSDATE())-YEAR(student.Sage)) as age from  student
  • 相关阅读:
    Java编译器API简介
    liblinear和libsvm区别
    spark和hadoop比较
    maxout激活函数
    FTRL算法
    NLP里面好的学习资料
    阿里妈妈MLR模型(论文)
    FM的推导原理--推荐系统
    GBDT+LR simple例子
    深度学习最全优化方法---来源于知乎
  • 原文地址:https://www.cnblogs.com/songfahzun/p/8718301.html
Copyright © 2020-2023  润新知