SQL刷题笔记day5

SQL218题目

我的错误代码：

select de.dept_no,de.emp_no,s.salary
from employees e
join dept_emp de on de.emp_no = e.emp_no
join salaries s on s.emp_no = e.emp_no
where de.dept_no not in dept_manager.dept_no #not in 好像不能直接这样用 这里报错

正确代码

SELECT de.dept_no, de.emp_no, s.salary
FROM dept_emp AS de, salaries AS s
WHERE de.emp_no = s.emp_no 
AND de.to_date = '9999-01-01' 
AND s.to_date = '9999-01-01'
AND s.emp_no NOT IN 
(SELECT emp_no FROM dept_manager dm WHERE dm.to_date = '9999-01-01');

我的代码改正 

select de.dept_no,de.emp_no,s.salary
from employees e
join dept_emp de on de.emp_no = e.emp_no  
join salaries s on s.emp_no = e.emp_no 
where de.emp_no not in (SELECT emp_no FROM dept_manager dm WHERE dm.to_date = '9999-01-01') 

复盘：难点是找非manager员工，涉及not in的用法， not in后面是个范围，而不是dept_manager.dept_no这个某个值。如：

 where subject not in ('Chemistry','Medicine')

如果后面括号只有一个，就要用等于了，如：

where continent = 'South America' 

SQL219题目

我的错误代码

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

正确代码

SELECT es.emp_no,ms.emp_no manager_no,
es.salary emp_salary,ms.salary manager_salary FROM
# es表：员工薪资表 
(SELECT de.dept_no,de.emp_no,s.salary FROM dept_emp de INNER JOIN salaries s
 ON de.emp_no=s.emp_no WHERE s.to_date='9999-01-01') es,

(SELECT dm.dept_no,dm.emp_no,s.salary FROM dept_manager dm INNER JOIN salaries s
 ON dm.emp_no=s.emp_no WHERE s.to_date='9999-01-01') ms
 
WHERE es.dept_no=ms.dept_no 
AND es.salary>ms.salary

理解思路后改正我的代码：

select es.emp_no,ms.emp_no as manager_no,
       es.salary as emp_salary,ms.salary as manager_salary
from 
(select de.emp_no,de.dept_no ,s.salary from dept_emp de join salaries s on s.emp_no = de.emp_no and de.to_date = '9999-01-01')es,
(select m.dept_no,m.emp_no,s.salary from dept_manager m join salaries s on s.emp_no = m.emp_no and m.to_date = '9999-01-01')ms
where es.salary > ms.salary and es.dept_no = ms.dept_no 

复盘：知道要引用两个不同的薪资，分别建立员工和经理的薪资表，不知道咋写，淦！
/*这个正确代码大结构就是：
select ,,,
from ()es,()ms #陌生点就在这里不知道怎么写
where.. 
*/

SQL220题目

我的错误代码

select d.dept_no,d.dept_name,t.title,count(t.title) as count
from departments d
join dept_emp de on d.dept_no = de.dept_no and de.to_date='9999-01-01'
join titles t on t.emp_no = de.emp_no and t.to_date='9999-01-01'
group by d.dept_no # 不知道按照什么进行分组
order by d.dept_no asc,t.title desc

正确代码

select d.dept_no,d.dept_name,t.title,count(t.title) as count
from departments d
join dept_emp de on d.dept_no = de.dept_no and de.to_date='9999-01-01'
join titles t on t.emp_no = de.emp_no and t.to_date='9999-01-01'
group by d.dept_no,t.title # 多字段分组
order by d.dept_no ,t.title #两个都是升序啦(默认也是升序)

复盘：这道题根据 多个字段 进行分组，将三张表内连接，通过 dept_no 和 title 为依据进行分组，然后计算count。因为可能会有下面这种情况：所以还要按照title也进行分组

 SQL223题目

我的代码

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

正确代码1

select film_id,title 
from film
where film_id not in #film_id不应该写成字符串的形式，这里表示的相当于数组名
(select fc.film_id from  film_category fc join category c
 on c.category_id = fc.category_id) #后面这两个表一个是关于类别的

 正确代码2

SELECT film.film_id,film.title
FROM film
LEFT JOIN film_category
on film.film_id = film_category.film_id
where film_category.category_id is null;

复盘：电影如果有分类，那么应该在 类别表category+电影分类表film_category 中有非空信息。

SQL224题目

我的代码

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

我这种思路的正确代码

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

不用子查询的直白方法：

select title, description
from film left 
join film_category as fc on film.film_id=fc.film_id
join category as c on c.category_id=fc.category_id
where c.name="Action";

复盘：其实这个题直接3个表连接起来，然后输出就行，但是题目要用子查询。

SQL226题目——拼接字符串(SQLlite平台)

我的代码

select ('first_name'| |'last_name') from employees

题目要求的方法：

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

使用concat函数：

SELECT CONCAT(last_name,' ',first_name) as name from   employees 

 复盘：'first_name'和'last_name'不应该加引号！不是字符串，是相当于数组名的变量！

SQL227题目——创建表

我的代码

CREATE TABLE actor(
actor_id smallint(5)  NOT NULL DEFAULT '0',
first_name	varchar(45) NOT NULL,
last_name	varchar(45) NOT NULL,
last_update	date not null, 
PRIMARY KEY (actor_id))

完美代码：

create table if not exists actor (
    actor_id smallint(5) not null primary key COMMENT'主键id',
    first_name varchar(45) not null COMMENT'名字',
    last_name varchar(45) not null COMMENT '姓氏',
    last_update date not null COMMENT '日期' 
)

复盘：注意创建数据表时，表名和字段名不需要用引号' '括起来。日期是date，其他类型都不变，照着写。 

SQL228——插入数据

我的代码

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

复盘:插入的是什么就是什么，不能多复制，哪怕是空格也不能多，因为类型可能不同和报错。

SQL229——插入数据部分已存在的情况

我的代码

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

复盘：

mysql中常用的三种插入数据的语句

1. insert into - 插入数据

2. replace into - 插入替换数据

3. insert ignore into - 如果已存在，忽略当前新数据