sql多表查询练习题

建表语句

CREATE DATABASE `test2` 
USE `test2`;
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) 

insert  into `class`(`cid`,`caption`) values (1,'三年二班'),(2,'三年三班'),(3,'一年二班')
,(4,'二年九班');

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) 



insert  into `course`(`cid`,`cname`,`teacher_id`) values (1,'生物',1),(2,'物理',2),(3,'体育',3)
,(4,'美术',2);

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
)

insert  into `score`(`sid`,`student_id`,`course_id`,`num`) values (1,1,1,10),(2,1,2,9),
(5,1,4,66),(6,2,1,8),(8,2,3,68),(9,2,4,99),(10,3,1,77),(11,3,2,66),(12,3,3,87),(13,3,4,99),
(14,4,1,79),(15,4,2,11),(16,4,3,67),(17,4,4,100),(18,5,1,79),(19,5,2,11),
(20,5,3,67),(21,5,4,100),(22,6,1,9),(23,6,2,100)
,(24,6,3,67),(25,6,4,100),(26,7,1,9),(27,7,2,100),(28,7,3,67)
,(29,7,4,88),(30,8,1,9),(31,8,2,100),(32,8,3,67),
(33,8,4,88),(34,9,1,91),(35,9,2,88),(36,9,3,67),(37,9,4,22)
,(38,10,1,90),(39,10,2,77),(40,10,3,43),(41,10,4,87)
,(42,11,1,90),(43,11,2,77),(44,11,3,43),(45,11,4,87),
(46,12,1,90),(47,12,2,77),(48,12,3,43),(49,12,4,87),(52,13,3,87);

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) 

insert  into `student`(`sid`,`gender`,`class_id`,`sname`) values (1,'男',1,'理解'),
(2,'女',1,'钢蛋'),(3,'男',1,'张三'),(4,'男',1,'张一'),(5,'女',1,'张二'),(6,'男',1,'张四'
),(7,'女',2,'铁锤'),
(8,'男',2,'李三'),(9,'男',2,'李一'),(10,'女',2,'李二'),(11,'男',2,'李四')
,(12,'女',3,'如花'),(13,'男',3,'刘三'),(14,'男',3,'刘一'),(15,'女',3,'刘二'),(16,'男',3,'刘四');

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) 

insert  into `teacher`(`tid`,`tname`) values (1,'张磊老师'),(2,'李平老师')
,(3,'刘海燕老师'),(4,'朱云海老师'),(5,'李杰老师');

1、查询所有的课程的名称以及对应的任课老师姓名

    select
        course.cname,
        teacher.tname
    from
        course
    inner join teacher on course.teacher_id = teacher.tid;

2、查询学生表中男女生各有多少人

    select
        gender,
        count(1)   # count(sid)
    from
        student
    group by
        gender;

3、查询物理成绩等于100的学生的姓名

    select
        sname
    from
        student
    where
        sid in(
            select
                t1.student_id
            from
                score t1
            inner join(
                select
                    cid
                from
                    course
                where
                    cname = '物理'
            ) t2 on t1.course_id = t2.cid
            where
                t1.num = 100
        );

4、查询平均成绩大于八十分的同学的姓名和平均成绩

    select
        t1.sname,
        t2.avg_num
    from
        student as t1
    inner join(
        select
            student_id,
            avg(num) as avg_num
        from
            score
        group by
            student_id
        having
            avg(num) > 80
    ) as t2 on t1.sid = t2.student_id;

5、查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)

    select
        t1.sid,
        t1.sname,
        t2.count_course,
        t2.sum_num
    from
        student as t1
    left join(
        select
            student_id,
            count(course_id) as count_course,
            sum(num) as sum_num
        from
            score
        group by
            student_id
    ) as t2 on t1.sid = t2.student_id;

6、查询姓李老师的个数

    select
        count(tid)
    from
        teacher
    where
        tname like '李%';

7、查询没有报李平老师课的学生姓名

    select
        sname
    from
        student
    where
        sid not in(
            select distinct
                student_id
            from
                score
            where
                course_id in (
                    select
                        cid
                    from
                        course
                    inner join teacher on course.teacher_id = teacher.tid
                    where teacher.tname = '李平老师'
                )
        );

8、查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表，然后连表即可)

    select
        t1.student_id,
        t1.num,
        t2.num
    from
        (
            select
                student_id,
                num
            from
                score
            where
                course_id = (
                    select
                        cid
                    from
                        course
                    where
                        cname = '物理'
                )
        ) as t1
    inner join (
        select
            student_id,
            num
        from
            score
        where
            course_id = (
                select
                    cid
                from
                    course
                where
                    cname = '生物'
            )
    ) as t2 on t1.student_id = t2.student_id
    where
        t1.num > t2.num;

9、查询没有同时选修物理课程和体育课程的学生姓名

# 包含了物理，体育都没选得
select
    sname
from
    student
where
    sid not in (
        select
            t1.student_id
        from
            (
                select
                    student_id,
                    course_id
                from
                    score
                where
                    course_id in (
                        select
                            cid
                        from
                            course
                        where
                            cname in (
                                '物理',
                                '体育'
                            )
                    )
            ) as t1
        group by
            t1.student_id
        having
            count(t1.student_id) = 2
    );
    # 只选修了一门
    select
        sname
    from
        student
    where
        sid in (
            select
                t1.student_id
            from
                (
                    select
                        student_id,
                        course_id
                    from
                        score
                    where
                        course_id in (
                            select
                                cid
                            from
                                course
                            where
                                cname in (
                                    '物理',
                                    '体育'
                                )
                        )
                ) as t1
            group by
                t1.student_id
            having
                count(t1.student_id) = 1
        );
        # 另一种方法：
        SELECT
            student.sname
        FROM
            student
        WHERE
            sid IN (
                SELECT
                    student_id
                FROM
                    score
                WHERE
                    course_id IN (
                        SELECT
                            cid
                        FROM
                            course
                        WHERE
                            cname = '物理'
                        OR cname = '体育'
                    )
                GROUP BY
                    student_id
                HAVING
                    COUNT(course_id) = 1
            );

10、查询挂科超过两门(包括两门)的学生姓名和班级

    select
        t1.caption,
        t2.sname
    from
        class as t1
    inner join (
        select
            sname,
            class_id
        from
            student
        where
            sid in (
                select
                    student_id
                from
                    score
                where
                    num < 60
                group by
                    student_id
                having
                    count(sid) >= 2
            )
    ) as t2 on t1.cid = t2.class_id;
    #另一种方法：
    SELECT
        student.sname,
        class.caption
    FROM
        student
    INNER JOIN (
        SELECT
            student_id
        FROM
            score
        WHERE
            num < 60
        GROUP BY
            student_id
        HAVING
            count(course_id) >= 2
    ) AS t1
    INNER JOIN class ON student.sid = t1.student_id
    AND student.class_id = class.cid;

11、查询选修了所有课程的学生姓名

    select
        sname
    from
        student
    where
        sid in (
            select
                student_id
            from
                score
            group by
                student_id
            having
                count(sid) = (
                    select
                        count(cid)
                    from
                        course
                )
        );

12、查询李平老师教的课程的所有成绩记录

    select
        *
    from
        score
    where
        course_id in (
            select
                cid
            from
                course
            inner join teacher on course.teacher_id = teacher.tid
            where
                teacher.tname = '李平老师'
        );

13、查询全部学生都选修了的课程号和课程名(取所有学生数，然后基于score表的课程分组，找出count(student_id)等于学生数即可)

    select
        cid,
        cname
    from
        course
    where
        cid in (
            select
                course_id
            from
                score
            group by
                course_id
            having
                count(sid) = (select count(sid) from student)
        );

14、查询每门课程被选修的次数

    select
        course_id,
        count(sid)
    from
        score
    right join course on score.course_id = course.cid
    group by
        course_id
    order by
        course_id;

    SELECT
        course_id,
        COUNT(student_id)
    FROM
        score
    GROUP BY
        course_id;

15、查询之选修了一门课程的学生姓名和学号

    select
        sid,
        sname
    from
        student
    where
        sid in (
            select
                student_id
            from
                score
            group by
                student_id
            having
                count(sid) = 1
        );

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

    select distinct
        num
    from
        score
    order by
        num desc;

17、查询平均成绩大于85的学生姓名和平均成绩

    select
        student.sname,
        t1.avg_num
    from
        student
    inner join (
        select
            student_id,
            avg(num) as avg_num
        from
            score
        group by
            student_id
        having
            avg(num) > 85
    ) as t1 on student.sid = t1.student_id;

18、查询生物成绩不及格的学生姓名和对应生物分数

    select
        sname,
        t1.num
    from
        student
    inner join (
        select
            student_id,
            num
        from
            score
        inner join course on score.course_id = course.cid
        where course.cname = '生物'
            and score.num < 60
    ) as t1 on student.sid = t1.student_id;
    # 另一种方法：
    select
        student.sname,
        score.num
    from
        score
    left join course on score.course_id = course.cid
    left join student on score.student_id = student.sid
    where
        course.cname = '生物'
        and score.num < 60;

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

    select
        sname,
        t1.avg_num
    from
        student
    inner join (
        select
            student_id,
            avg(num) as avg_num
        from
            score
        where
            course_id in (
                select
                    cid
                from
                    course
                inner join teacher on course.teacher_id = teacher.tid
                where teacher.tname = '李平老师'
            )
        group by
            student_id
        order by
            avg_num desc
        limit 1
    ) as t1 on student.sid = t1.student_id;

20、查询每门课程成绩最好的前两名学生姓名

    select
        student.sname,
        num_table.course_id,
        num_table.first_num,
        num_table.second_num
    from
        student
    inner join (
        select
            score.sid,
            score.course_id as course_id,
            score.student_id,
            t.first_num as first_num,
            t.second_num as second_num
        from
            score
        left join
            (
                select
                    sid,
                    (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num,
                    (select num from score as s3 where s3.course_id = s1.course_id order by num desc limit 1,1) as second_num
                from
                    score as s1
            ) as t on score.sid = t.sid
        where score.num <= t.first_num and score.num >= t.second_num
    ) as num_table on student.sid = num_table.student_id;

21、查询不同课程但成绩相同的学号，课程号，成绩

    select distinct
        s1.student_id,
        s1.course_id,
        s1.num,
        s2.num
    from
        score as s1,
        score as s2
    where
        s1.num = s2.num
        and s1.course_id != s2.course_id;

    select distinct
        *
    from
        score as s1,
        score as s2
    where
        s1.num = s2.num

22、查询没学过李平老师课程的学生姓名以及选修的课程名称；

    select
        student.sname,
        course.cname
    from
        (
            select
                student_id,
                course_id
            from
                score
            where
                course_id not in (
                    select
                        cid
                    from
                        course
                    inner join teacher on course.teacher_id = teacher.tid
                    where teacher.tname = '李平老师'
                )
        ) as t1
    inner join course on t1.course_id = course.cid
    inner join student on t1.student_id = student.sid
    order by student.sname;

23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

     select
        student_id,
        sname,
        count(course_id)
    from
        score
    left join student on score.student_id = student.sid
    where
        student_id != 1
        and course_id in
            (
                select
                    course_id
                from
                    score
                where
                    student_id = 1
            )
    group by student_id
    # 另一种方法：
    select
        sid,
        sname
    from
        student
    where
        sid in (
            select
                student_id
            from
                score
            where
                course_id in (
                    select
                        course_id
                    from
                        score
                    where
                        student_id = 1
                )
        )
        and sid !=1

24、任课最多的老师中学生单科成绩最高的学生姓名

    select
        sid,
        sname
    from
        student
    where
        sid in (
            select distinct
                t1.id_students
            from
                (
                    select
                        group_concat(student_id) as id_students,
                        course_id,
                        max(num)
                    from
                        score
                    where
                        course_id in (
                            select
                                cid
                            from
                                course
                            where
                                teacher_id in (
                                    select
                                        teacher_id
                                    from
                                        course
                                    group by
                                        teacher_id
                                    having
                                        count(cid) = (
                                            select
                                                count(cid)
                                            from
                                                course
                                            group by
                                                teacher_id
                                            order by
                                                count(cid) desc
                                            limit 1
                                        )
                                )
                        )
                    group by
                        course_id
                ) as t1
        );

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 159,716评论 4赞 364
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,558评论 1赞 294
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 109,431评论 0赞 244
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 44,127评论 0赞 209
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,511评论 3赞 287
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,692评论 1赞 222
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,915评论 2赞 313
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,664评论 0赞 202
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,412评论 1赞 246
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,616评论 2赞 245
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,105评论 1赞 260
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,424评论 2赞 254
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,098评论 3赞 238
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,096评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,869评论 0赞 197
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,748评论 2赞 276
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,641评论 2赞 271

sql多表查询练习题

建表语句

1、查询所有的课程的名称以及对应的任课老师姓名

2、查询学生表中男女生各有多少人

3、查询物理成绩等于100的学生的姓名

4、查询平均成绩大于八十分的同学的姓名和平均成绩

5、查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)

6、 查询姓李老师的个数

7、 查询没有报李平老师课的学生姓名

8、 查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表，然后连表即可)

9、 查询没有同时选修物理课程和体育课程的学生姓名

10、查询挂科超过两门(包括两门)的学生姓名和班级

11、查询选修了所有课程的学生姓名

12、查询李平老师教的课程的所有成绩记录

13、查询全部学生都选修了的课程号和课程名(取所有学生数，然后基于score表的课程分组，找出count(student_id)等于学生数即可)

14、查询每门课程被选修的次数

15、查询之选修了一门课程的学生姓名和学号

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

17、查询平均成绩大于85的学生姓名和平均成绩

18、查询生物成绩不及格的学生姓名和对应生物分数

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

20、查询每门课程成绩最好的前两名学生姓名

21、查询不同课程但成绩相同的学号，课程号，成绩

22、查询没学过李平老师课程的学生姓名以及选修的课程名称；

23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

24、任课最多的老师中学生单科成绩最高的学生姓名

推荐阅读更多精彩内容

6、查询姓李老师的个数

7、查询没有报李平老师课的学生姓名

8、查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表，然后连表即可)

9、查询没有同时选修物理课程和体育课程的学生姓名