SQL(1/6)

汇总各个部门当前员工的title类型的分配数目

汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的当前(dept_emp.to_date = ‘9999-01-01’)员工的当前(titles.to_date = ‘9999-01-01’)title以及该类型title对应的数目count，结果按照dept_no升序排序
(注：因为员工可能有离职，所有dept_emp里面to_date不为’9999-01-01’就已经离职了，不计入统计，而且员工可能有晋升，所以如果titles.to_date 不为 ‘9999-01-01’，那么这个可能是员工之前的职位信息，也不计入统计)
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);

select de.dept_no,d.dept_name,t.title,count(t.title)
from dept_emp de
left join departments d
on de.dept_no=d.dept_no
left join titles t
on de.emp_no=t.emp_no
where de.to_date='9999-01-01'
and t.to_date='9999-01-01'
group by de.dept_no,t.title
order by de.dept_no;
# group by 1,2
# 先按照1分组，剩下的按照2分组

给出每个员工每年薪水涨幅超过5000的员工

给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。
提示：在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date)
(数据保证每个员工的每条薪水记录to_date-from_date=1年，而且同一员工的下一条薪水记录from_data=上一条薪水记录的to_data)

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));
如：插入
INSERT INTO salaries VALUES(10001,52117,‘1986-06-26’,‘1987-06-26’);
INSERT INTO salaries VALUES(10001,62102,‘1987-06-26’,‘1988-06-25’);
INSERT INTO salaries VALUES(10002,72527,‘1996-08-03’,‘1997-08-03’);
INSERT INTO salaries VALUES(10002,72527,‘1997-08-03’,‘1998-08-03’);
INSERT INTO salaries VALUES(10002,72527,‘1998-08-03’,‘1999-08-03’);
INSERT INTO salaries VALUES(10003,43616,‘1996-12-02’,‘1997-12-02’);
INSERT INTO salaries VALUES(10003,43466,‘1997-12-02’,‘1998-12-02’);

select s1.emp_no,s1.from_date,s1.salary-s2.salary sg
from salaries s1
join salaries s2
on s1.emp_no=s2.emp_no
and s1.from_date=s2.to_date
where (s1.salary-s2.salary)>5000
order by sg desc;

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

查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部
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));

CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, last_update timestamp,
PRIMARY KEY ( category_id ));

CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, last_update timestamp);

select c.name,count(fc.film_id) as num
from film_category as fc
inner join category as c
on fc.category_id = c.category_id
inner join (select * from film where description like '%robot%') as f
on fc.film_id = f.film_id
inner join (select *,count(film_id) as num from film_category group by category_id having num >= 5) as cfc
on fc.category_id = cfc.category_id

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

使用join查询方式找出没有分类的电影id以及名称
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));
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, last_update timestamp,
PRIMARY KEY ( category_id ));
CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, last_update timestamp);

select f.film_id,f.title
from film f
left join film_category fc
on f.film_id=fc.film_id
where fc.film_id is null;

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

你能使用子查询的方式找出属于Action分类的所有电影对应的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));
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, last_update timestamp,
PRIMARY KEY ( category_id ));
CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, last_update timestamp);

select f.title,f.description
from film f
where f.film_id in (select fc.film_id
                   from film_category fc
                   where fc.category_id in (select c.category_id
                      from category c
                      where c.name='Action'));
# select a.title,a.description
# from film a,category b,film_category c
# where a.film_id=c.film_id and c.category_id=b.category_id and b.name='Action'