• SQL面试题


    1.创建表(movie,category,m_category)

    create table movie (
        id int primary key auto_increment,
        name varchar(20),
        movie_info varchar(100));
    insert into movie (name,movie_info) values
        ("肖申克的救赎","希望让人自由"),
        ("霸王别姬","风华绝代"),
        ("阿甘正传","一部美国近现代史"),
        ("机器人总动员","机器人小瓦力,大人生"),
        ("这个杀手不太冷","怪蜀黍和小萝莉不得不说的故事"),
        ("美丽人生","最美的谎言"),
        ("阿凡达","绝对意义上的美轮美奂"),
        ("盗梦空间","诺兰给了我们一场无法盗取的梦"),
        ("楚门的世界","如果再也不能见到你,祝你早安,午安,晚安"),
        ("星际穿越","爱是一种力量,让我们超越时空感知它的存在");
    create table category (
        category_id int primary key auto_increment,
        category_name varchar(20),
        category_time date);
    insert into category (category_name,category_time) values
        ("犯罪","2020-05-05"),
        ("爱情","2020-05-06"),
        ("科幻","2020-05-10");
    create table m_category (
        id int primary key auto_increment,
        category_id int,
        m_time date);
    insert into m_category (category_id,m_time) values
        (1,"2020-05-01"),
        (2,"2020-05-02"),
        (2,"2020-05-03"),
        (3,"2020-05-04"),
        (1,"2020-05-05"),
        (2,"2020-05-06"),
        (3,"2020-05-07"),
        (3,"2020-05-08"),
        (3,"2020-05-09"),
        (3,"2020-05-10");
    movie,category,m_category

    2.题目:
    --查找“电影表”中电影描述信息包含“机器人”的电影,
    --以及对应的电影类别名称和电影数目(count(电影表.电影编号))。
    --同时,还需要该电影类别名称对应电影数量(count(电影类别表.电影类别编号))>=5部。
    注意:而题目中的上述分类对应电影数量>=5部,是指该电影类别在原始表中的电影数量>= 5,
    而不是先用where子句筛选以后的表。

    select movie.name,category.category_name,count(movie.id) as 电影数目
    from movie  inner join m_category
    on movie.id = m_category.id 
    inner join category 
    on m_category.category_id = category.category_id
    right join (select category_id
    from m_category
    group by category_id
    having count(category_id) >= 5) as cc
    on m_category.category_id = cc.category_id
    where movie.movie_info like '%机器人%'
    group by category.category_name;
    答案

    其他建议(https://blog.csdn.net/yangzhongblog/article/details/107551671)

    [其他面试题](https://blog.csdn.net/u010565545/article/details/100785261?utm_medium=distribute.pc_relevant_t0.none-task-blog-OPENSEARCH-1.control&dist_request_id=1328575.10608.16146617976350185&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-OPENSEARCH-1.control)

  • 相关阅读:
    win8 tips
    从win10体验到重装win8
    win10 体验 日志
    磁盘分区与多系统安装(windows ubuntu)
    使用ultraISO制作ISO镜像文件
    C++中const用法总结
    4月8号的打卡
    Java第二次作业
    第一次Java作业
    NX二次开发-改变自制UI界面大小
  • 原文地址:https://www.cnblogs.com/wuxiping2019/p/14486847.html
Copyright © 2020-2023  润新知