牛客网在线编程网址:https://www.nowcoder.com/activity/oj
(默认使用SQLite)
film表
字段 | 说明 |
film_id | 电影id |
title | 电影名称 |
description | 电影描述信息 |
CREATE TABLE IF NOT EXISTS film ( film_id smallint(5) NOT NULL DEFAULT '0', title varchar(255) NOT NULL, description text, PRIMARY KEY (film_id));
category表
字段 | 说明 |
category_id | 电影分类id |
name | 电影分类名称 |
last_update |
电影分类最后更新时间 |
CREATE TABLE category ( category_id tinyint(3) NOT NULL , name varchar(25) NOT NULL,
`last_update` timestamp, PRIMARY KEY ( category_id ));
film_category表
字段 | 说明 |
film_id | 电影id |
category_id | 电影分类id |
last_update | 电影id和分类id对应关系的最后更新时间 |
CREATE TABLE film_category ( film_id smallint(5) NOT NULL, category_id tinyint(3) NOT NULL, `last_update` timestamp);
题目1:
查找描述信息中包括robot的电影对应的分类名称以及电影数目,而且还需要该分类对应电影数量>=5部(指的是所有电影中该分类的电影数量>=5)
with category_num as( select category_id, count(*) num from film_category GROUP BY category_id having num >= 5 ) select category.name, count(*) FROM category, film_category WHERE category.category_id = film_category.category_id AND film_category.category_id IN (select category_id from category_num) AND film_id IN ( select film_id from film Where description LIKE '%robot%' ) GROUP BY name;
或者
select category.name, count(*) FROM category, film_category, ( select category_id, count(*) num from film_category GROUP BY category_id having num >= 5 ) as category_num WHERE category.category_id = film_category.category_id AND film_category.category_id = category_num.category_id AND film_id IN ( select film_id from film Where description LIKE '%robot%' ) GROUP BY name;
题目2:
使用join查询方式找出没有分类的电影id以及名称
select film_id, title FROM ( select film.film_id, film.title, film_category.category_id FROM film LEFT JOIN film_category ON film.film_id = film_category.film_id ) table_join WHERE category_id IS NULL;
题目3:
使用子查询的方式找出属于Action分类的所有电影对应的title,description
select title, description from film WHERE film_id IN ( select film_id FROM category INNER JOIN film_category ON category.category_id = film_category.category_id AND category.name = 'Action' );
或
select title, description FROM film, film_category WHERE film.film_id = film_category.film_id AND film_category.category_id IN (select category_id from category where name = 'Action');