数据库——刷题-常规题-面试题-数据工程师-SQL

1、题目描述

使用join查询方式找出没有分类的电影id以及名称
如输入为：

INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');

INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');
INSERT INTO category VALUES(7,'Drama','2006-02-14 20:46:27');
INSERT INTO category VALUES(8,'Family','2006-02-14 20:46:27');
INSERT INTO category VALUES(9,'Foreign','2006-02-14 20:46:27');
INSERT INTO category VALUES(10,'Games','2006-02-14 20:46:27');
INSERT INTO category VALUES(11,'Horror','2006-02-14 20:46:27');

INSERT INTO film_category VALUES(1,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,11,'2006-02-14 21:07:09');

输出为：

实现描述：

select film.film_id,film.title
from film 
where film.film_id not in (
    select fc.film_id from film_category fc 
    inner join category c on fc.category_id=c.category_id 
    inner join film f on fc.film_id=f.film_id )

2、题目描述同上

你能使用子查询的方式找出属于Action分类的所有电影对应的title,description吗

输入：

INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');

INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');

INSERT INTO film_category VALUES(1,1,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,1,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(3,6,'2006-02-14 21:07:09');

输出：

实现描述：

select f.title,f.description
from film f,category c,film_category fc
where fc.film_id=f.film_id and fc.category_id=c.category_id
and c.name='Action'

select title,description
from (
    select ff.title,ff.description,c.name
    from film_category fc
    join film ff
    on fc.film_id=ff.film_id
    join category c
    on fc.category_id =c.category_id
) f 
where f.name='Action';

select ff.title,ff.description
from 
(film_category fc inner join film f on fc.film_id=f.film_id
 inner join category c on fc.category_id=c.category_id) ff
where ff.name='Action'

3、题目描述

将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分
(注：该数据库系统是sqllite,字符串拼接为 || 符号，不支持concat函数)

CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

输出描述:

实现代码：

select (e.last_name)||(' ')||(e.first_name) as name 
from 
employees e

4、题目描述
创建一个actor表，包含如下列信息(注：sqlite获取系统默认时间是datetime(‘now’,‘localtime’))

create table 'actor'(
 'actor_id' smallint(5) NOT NULL ,
'first_name' varchar(45) NOT NULL,
'last_name' varchar(45) NOT NULL,
'last_update' timestamp NOT NULL default (datetime('now','localtime')),
primary key('actor_id'))

5、题目描述

对于表actor批量插入如下数据(不能有2条insert语句哦!)

CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

实现描述：

insert or ignore into actor values
('3','ED','CHASE','2006-02-15 12:34:33')

6、题目描述

对于如下表actor，其对应的数据为:

请你创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.
actor_name表结构如下：

create table 'actor_name'
as select first_name,last_name from actor