牛客网SQL实战练习——26~40

声明：练习牛客网SQL实战题目，整理笔记！
26.汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE IF NOT EXISTS `titles` (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

分析：本题的关键句在于当前员工所有的title以及该类型title对应的数目count，所以应该以dept_no和title进行分组。即group by b.dept_no,t.title，然后依次加上其他条件即可。
参考代码：

select b.dept_no,b.dept_name,t.title,count(*) count
from titles as t,dept_emp as a,departments as b
where a.dept_no=b.dept_no 
and t.emp_no=a.emp_no
and a.to_date='9999-01-01' 
and t.to_date='9999-01-01'
group by b.dept_no,t.title;

27.给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。
提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

分析：很显然，本题要复用salaries表，两张表内连接，限定为同一名员工，工资变更开始的日期 限定为1年，工资差大于5000，按照工资差降序排列。
参考代码：

select s2.emp_no, s2.from_date,(s2.salary-s1.salary) as salary_growth
from salaries as s1
inner join salaries as s2
on s1.emp_no = s2.emp_no
where strftime('%Y', s2.to_date)-strftime('%Y', s1.to_date)=1
and salary_growth > 5000
order by salary_growth desc

28.查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

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

分析：尤其注意：5部指各分类下面的电影总数>=5，不是指经robot等条件筛选后，分类下面的电影数目>=5，所以，筛选后的分类必须是分类后电影总数>=5的分类。使用嵌套查询控制各分类下面的电影总数>=5.
参考代码：

select c.name,count(f.film_id)
from film as f,category as c,film_category as fc
where f.film_id=fc.film_id and c.category_id=fc.category_id
and f.description like '%robot%'
and fc.category_id in (
select fi.category_id
from film_category as fi
group by fi.category_id
having count(*)>=5
    )

29.使用join查询方式找出没有分类的电影id以及名称

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

分析：LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。所以这里使用left join on 关键字。最后使用not in 关键字以及嵌套查询，除去分类的电影。
参考代码：

select f.film_id,f.title
from film as f left join film_category as fc
on f.film_id = fc.film_id
where f.film_id not in (select film_id from film_category);

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

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

分析：本题中题目要求使用子查询，那么按照要求，使用两次子查询，第一次子查询使用film_id连接film与film_category两个表，第二次子查询使用category_id 连接film_category与category两个表，最后加上限制条件 where name like 'Action'即可。
参考代码：

select title,description
from film
where film_id in
(
    select film_id 
    from film_category
    where category_id in 
    (
        select category_id 
        from category
        where name like 'Action'
    )
)

31.获取select * from employees对应的执行计划
分析：explain可以描述表的细节。查看SQL的执行计划，多用于发现优化性能的点。
参考代码：

explain select * 
from employees

32.将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

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

分析：MySQL、SQL Server、Oracle等数据库支持CONCAT方法，
而本题所用的SQLite数据库只支持用连接符号"||"来连接字符串
参考代码：

select last_name||" "||first_name as Name 
from employees

33.创建一个actor表，包含如下列信息
列表 类型 是否为NULL 含义
actor_id smallint(5) not null 主键id
first_name varchar(45) not null 名字
last_name varchar(45) not null 姓氏
last_update timestamp not null 最后更新时间，默认是系统的当前时间
分析：获取系统默认时间是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)
)

34.对于表actor批量插入如下数据

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')))
actor_id    first_name  last_name   last_update
1   PENELOPE    GUINESS 2006-02-15 12:34:33
2   NICK    WAHLBERG    2006-02-15 12:34:33

分析：固定格式不做过多解释。
参考代码：

insert into actor 
values
(1,"PENELOPE","GUINESS","2006-02-15 12:34:33"),
(2,"NICK","WAHLBERG","2006-02-15 12:34:33");

35.对于表actor批量插入如下数据,如果数据已经存在，请忽略，不使用replace操作

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')))

分析：在 SQLite 中，用 INSERT OR IGNORE 来插入记录，或忽略插入与表内UNIQUE字段都相同的记录。
参考代码：

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

36.对于如下表actor，其对应的数据为:
actor_id first_name last_name last_update
1 PENELOPE GUINESS 2006-02-15 12:34:33
2 NICK WAHLBERG 2006-02-15 12:34:33
创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下：
列表 类型 是否为NULL 含义
first_name varchar(45) not null 名字
last_name varchar(45) not null 姓氏
分析：题目使用的是sqlite3，如果是mysql，那么as可以去掉，也可以不去掉。
参考代码：

create table actor_name as
select first_name,last_name from actor;

37.针对如下表actor结构创建索引：

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')))

对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname
分析：create (unique) index 索引名 on 表名（列名）
参考代码：

create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);

38.针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：

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')))

分析：
CREATE VIEW <视图名称> (<视图列名1>,<视图列名2>…)
AS
<select 语句>；
参考代码:

create view actor_name_view as
select first_name as first_name_v, last_name as last_name_v 
from actor
或者
create view actor_name_view(first_name_v,last_name_v) as
select first_name, last_name 
from actor

39.针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
create index idx_emp_no on salaries(emp_no);

分析：MYSQL中强制索引查询使用：FORCE INDEX(indexname);
SQLite中强制索引查询使用：INDEXED BY indexname;
参考代码：

select * from salaries
indexed by idx_emp_no
where emp_no = '10005';

40.存在actor表，包含如下列信息：

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

现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'0000 00:00:00'
分析：用 ALTER TABLE ... ADD ... 语句可以向已存在的表插入新字段，并且能够与创建表时一样，在字段名和数据类型后加入NOT NULL、DEFAULT等限定。
参考代码：

alter table actor
add `create_date` datetime not null default '0000-00-00 00:00:00'

欢迎关注微信公众号：蛋炒番茄
同步更新！！！