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");

 

 

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)