牛客-SQL(1-30)

牛客-SQL(1-30)

题目1 ：查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天

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 emp_no,birth_date,first_name,last_name,gender,hire_date
from employees
order by hire_date desc
limit 1

题目2 ：查找入职员工时间排名倒数第三的员工所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天

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 *
from employees
order by hire_date desc
limit 2,1

题目3 ：查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no(注:请以salaries表为主表进行查询，输出结果以salaries.emp_no升序排序，并且请注意输出结果，dept_no列是最后一列)

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 TABLE dept_manager (
dept_no char(4) NOT NULL, – ‘部门编号’
emp_no int(11) NOT NULL, – ‘员工编号’
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));

##自己做的
select salaries.emp_no,salaries.salary,salaries.from_date,salaries.to_date,dept_manager.dept_no
from salaries,dept_manager
where salaries.emp_no = dept_manager.emp_no and dept_manager.to_date='9999-01-01' and salaries.to_date='9999-01-01'
order by salaries.emp_no

##别人写的
select s.*,d.dept_no
from salaries as s  inner join dept_manager as d
on s.emp_no=d.emp_no
where s.to_date='9999-01-01' and d.to_date='9999-01-01'

题目4 ：查找所有已经分配部门的员工的last_name和first_name以及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 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 last_name,first_name,dept_no
from dept_emp inner join employees##因为是分配部门的，所以dept_emp.emo一定不为空，所以要利用内联结
on dept_emp.emp_no = employees.emp_no

题目5 ：查找所有员工的last_name和first_name以及对应部门编号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 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 employees.last_name,employees.first_name,dept_emp.dept_no
from  employees left join  dept_emp ##注意谁在前面，因为dept_no可以为空，所以左连接
on dept_emp.emp_no = employees.emp_no

题目6 ：查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序(请注意，一个员工可能有多次涨薪的情况)

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

select employees.emp_no,salaries.salary
from salaries inner join employees
on employees.emp_no = salaries.emp_no and employees.hire_date=salaries.from_date##入职时候：hire_date=from_date
order by employees.emp_no desc

题目7 ：查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t

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

select emp_no ,count(*) t
from salaries
group by emp_no 
having count(*)>15##having之前要有group by

题目8: 找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

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

select distinct salary
from salaries
where to_date='9999-01-01'
order by salary desc

题目9: 获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况，给出dept_no, emp_no以及salary(请注意，同一个人可能有多条薪水情况记录)

CREATE TABLE dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
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));

select d.dept_no,d.emp_no,s.salary
from salaries s inner join dept_manager d
on s.emp_no = d.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01'

题目10: 获取所有非manager的员工emp_no

CREATE TABLE dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
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 emp_no
from employees
EXCEPT##差集
select emp_no
from dept_manager

##参考
select emp_no
from employees
where emp_no not in (select emp_no from dept_manager);

##参考：LEFTJOIN左连接+IS NULL
SELECT e.emp_no
FROM employees AS e
LEFT JOIN dept_manager AS d
ON e.emp_no=d.emp_no
WHERE dept_no IS NULL;

题目11:获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_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 dept_manager (
dept_no char(4) NOT NULL, – ‘部门编号’
emp_no int(11) NOT NULL, – ‘经理编号’
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));

思路：需要将自己的emp_no与经理的emp_no所对应起来，如果经历是自己则不显示，那么 只需确定在同一部门，并且dept_emp.emp_no != dept_manager.emp_no即可

SELECT dept_emp.emp_no,dept_manager.emp_no as manager_no
from dept_emp inner join dept_manager
on dept_emp.emp_no != dept_manager.emp_no and dept_emp.dept_no = dept_manager.dept_no
   and dept_manager.to_date='9999-01-01' 

题目12:获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

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

select dept_emp.dept_no,dept_emp.emp_no,max(salaries.salary)
from dept_emp inner join salaries
on dept_emp.emp_no = salaries.emp_no and dept_emp.to_date = '9999-01-01' and salaries.to_date='9999-01-01'
group by dept_emp.dept_no
##注：最大薪资，可用聚合函数max,当前部门需对部门进行分组

题目13:从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

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 title ,count(title) t
from titles
group by title
having count(title)>=2

题目14:从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。

select  title ,count(distinct emp_no) t
from titles
group by title
having count(title)>=2

题目15:查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列(题目不能使用mod函数)

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 *
from employees
where  last_name <> 'Mary' and emp_no % 2 <> 0 ## %表示取余，mod(emp_no,2)=1也表示奇数
order by hire_date desc

题目16:统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avgCREATE 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 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 titles.title,avg(salary) avg
from salaries,titles
where salaries.emp_no = titles.emp_no and titles.to_date='9999-01-01' and salaries.to_date='9999-01-01'
group by titles.title##有聚合必有分组

题目17:获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary

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

select emp_no,salary
from salaries
where to_date='9999-01-01' 
order by salary desc
limit 1,1

题目18: 查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗？

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

select employees.emp_no,max(salaries.salary),employees.last_name,employees.first_name
from employees inner join salaries
on employees.emp_no = salaries.emp_no and to_date='9999-01-01' 
and salary not in (select max(salary) from salaries where to_date='9999-01-01')
#注：第二薪资，不可以用order by,只需求最高薪资（不包括最高薪资），前提是薪资不在最高薪资里，即可

题目19:查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

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 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 employees.last_name,employees.first_name,departments.dept_name
from employees left  join dept_emp 
on dept_emp.emp_no = employees.emp_no left join departments 
on departments.dept_no = dept_emp.dept_no
##注：暂时没有分配部门的员工，所以dept_name是有空的，则一定其他两个表左连接  departments表，又因为employees和dept_emp表连接要利用emp_no，但是没有分配部门的也要显示，所以，employees的emp_no也要左连接，才能保证暂时没有分配部门的员工的信息也出现

题目20:查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)

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

select max(salary)-min(salary) growth
from salaries
where emp_no=10001

##参考：利用ORDER BY查询最后一次工资和第一次工资
SELECT
(SELECT salary
FROM salaries
WHERE emp_no=10001
ORDER BY to_date desc limit 1
) -
(SELECT salary
FROM salaries
WHERE emp_no=10001
ORDER BY to_date asc limit 1
) AS growth;

题目21:查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序，（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!=‘9999-01-01’，这样的数据不显示在查找结果里面）

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

SELECT employees.emp_no,(s1.salary-s2.salary) AS growth
FROM employees INNER JOIN salaries AS s1
ON employees.emp_no = s1.emp_no AND s1.to_date='9999-01-01' 
INNER JOIN salaries AS s2
ON employees.emp_no = s2.emp_no AND employees.hire_date = s2.from_date
ORDER BY growth ASC
注：入职以来的涨幅程度应该利用，当前的工资减去入职时的工资，当前的员工也就是to_date='9999-01-01' ，入职时的工资应该是入职时间 = 一条薪水记录开始的时候

题目22:统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum

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

select departments.dept_no,departments.dept_name,count(salaries.salary) as sum
from departments,dept_emp,salaries
where departments.dept_no = dept_emp.dept_no and dept_emp.emp_no = salaries.emp_no
group by departments.dept_no

题目23:对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

思路：窗口函数

select emp_no,salary,dense_rank() over(order by salary desc) as 'rank'
from salaries
where to_date='9999-01-01'
order by rank asc,emp_no asc;

题目24:获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=‘9999-01-01’

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 dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
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));
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));

select dept_emp.dept_no,salaries.emp_no,salaries.salary
from dept_emp,salaries
where  salaries.emp_no = dept_emp.emp_no and salaries.to_date = '9999-01-01'
       and dept_emp.emp_no not in(select emp_no from dept_manager where to_date='9999-01-01')
##注：可以不连接employees表

题目25:获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01’,结果第一列给出员工的emp_no，第二列给出其manager的manager_no，第三列给出该员工当前的薪水emp_salary,第四列给该员工对应的manager当前的薪水manager_salary

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 dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
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));

思路：

• 利用部门号，将经理与员工连接起来
• 再利用员工号将员工与薪资连接起来
• 再利用员工号将经理与薪资连接起来
• 筛选员工的薪资大于经理薪资的行，即得到所求。

select dept_emp.emp_no,dept_manager.emp_no as manager_no,s1.salary as emp_salary ,s2.salary as manager_salary
from dept_emp inner join dept_manager
on dept_emp.dept_no = dept_manager.dept_no 
inner join salaries as s1
on dept_emp.emp_no = s1.emp_no 
inner join salaries as s2 
on dept_manager.emp_no = s2.emp_no and s1.salary > s2.salary  and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'

题目26:汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的当前(dept_emp.to_date = ‘9999-01-01’)员工的当前(titles.to_date = ‘9999-01-01’)title以及该类型title对应的数目count(注：因为员工可能有离职，所有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);

思路：

• 利用部门号将部门表与部门员工表连接
• 利用员工号将部门员工和title连接
• 筛选出离职和晋升的员工（也就是日期不等于9999-01-01）
• 因为有count()函数，故需对select中除count()函数之外的字段进行分组

select departments.dept_no,departments.dept_name,titles.title,count(titles.title)
from departments inner join dept_emp
on departments.dept_no = dept_emp.dept_no
inner join titles
on dept_emp.emp_no = titles.emp_no
where titles.to_date = '9999-01-01' and dept_emp.to_date = '9999-01-01' 
group by departments.dept_no,departments.dept_name,titles.title

题目27:给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。提示：在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date)(数据保证每个员工的每条薪水记录to_data-from_data=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));

select s1.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
AND (STRFTIME('%Y', s2.from_date) - STRFTIME('%Y', s1.from_date) = 1
OR STRFTIME('%Y', s2.to_date) - STRFTIME('%Y', s1.to_date) = 1)
AND salary_growth>5000
order by salary_growth desc
#注1：因为要的是薪水变更开始日期，所以是s2.from_date
#注2：在sqlite中截取年份的函数是strftime，而在mysql中则是DATE_FORMAT(to_date, '%Y')
#注3：在mysql中，WHERE查询中的s2.salary-s1.salary不能改成salary_growth

题目28:查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数(count(film_category.category_id))>=5

SELECT c.name, COUNT(fc.film_id)
FROM film AS f, film_category AS fc, category AS c,
(select category_id
FROM film_category
GROUP BY category_id
HAVING count(film_id)>=5) AS cc
WHERE f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND c.category_id = fc.category_id
AND c.category_id=cc.category_id
GROUP BY c.name;
##注：仔细读题，其实要4个表进行连接

SELECT c.name, COUNT(fc.film_id)
FROM film AS f, film_category AS fc, category AS c
WHERE f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND c.category_id = fc.category_id
AND c.category_id IN (select category_id
FROM film_category
GROUP BY category_id
HAVING count(film_id)>=5)
GROUP BY c.category_id;
##注：方法2

题目29:使用join查询方式找出没有分类的电影id以及名称（表如28题）

SELECT f.film_id, f.title
FROM film AS f
LEFT JOIN film_category AS fc
ON f.film_id=fc.film_id
WHERE category_id IS NULL;
#注：将电影表和类别表进行连接，连接之后的id不在类别里即可

题目30:你能使用子查询的方式找出属于Action分类的所有电影对应的title,description吗？（表如题28）

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