数据库刷题笔记（2）

1. 查找入职时间最晚的员工

注意点：对于limit的使用，因为limit x只能输出x个员工，但是可能会有多个相同时间最晚入职的员工，所以不能使用。

limit使用介绍：

select *
from employees order by hire_date desc limit 0,1

desc: 降序

asc：升序

limit使用 limit(a, b)，表示从a开始取b个（不是第b个）；

2. 查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no

select s.*, d.dept_no 

from salaries s join dept_manager d

on s.emp_no = d.emp_no
where s.to_date = '9999-01-01' and d.to_date = '9999-01-01'

可以顺利AC，但是下面报错

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

可能选择 select 中的顺序和from的重新命名的顺序要一直；

还有一种比较优雅的方式：

select s.*, d.dept_no
from salaries s, dept_manager d
where s.emp_no  = d.emp_no
and s.to_date = '9999-01-01' and d.to_date = '9999-01-01'

上述代码没有直接使用join子句，而且注意 s.* 的用法。

3. 查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工

select e.last_name, e.first_name, d.dept_no
from employees e left join dept_emp d
on e.emp_no = d.emp_no

只有应该保存employees表中所有行，因此使用左连接，而不是右连接；

如果允许左表出现空值，使用 left join。

4. 查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序

select e.emp_no, s.salary
from employees e join salaries s
on e.emp_no = s.emp_no and e.hire_date = s.from_date
order by e.emp_no desc

此题唯一的考点在：e.hire_date = s.from_date

雇佣日期等于该员工的起始日期；

5. 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

此题有一处漏洞，count（emp_no）默认每次都是涨薪，而不是降薪

HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

having语句使用。

select emp_no,count(emp_no) as t
from salaries group by emp_no
having count(emp_no) >15

6. 获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'。

该题只是数据判别条件有点奇怪，比较容易

e.emp_no != m.emp_no and e.dept_no = m.dept_no

7. 获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

select e.dept_no, e.emp_no, max(s.salary)
from dept_emp e join salaries s
on e.emp_no = s.emp_no and e.to_date='9999-01-01' and s.to_date='9999-01-01'
group by dept_no

（2）注意在使用max会自动筛选出该行，而不仅仅是一个数值

（3）注意on的连续条件

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

select title,count(emp_no) as t
from titles
group by title
having t > 1

对于大小选择不能够使用 count（title）> 1

如果要排除重复的emp_no，可以直接使用count(distinct emp_no)

9. 查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

对于奇数和偶数的处理方式：

where emp_no % 2 != 0

10. 查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

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 now.emp_no, (now.salary - starts.salary) as growth
from (select emp_no,salary from salaries where to_date='9999-01-01') as now,
     (select a.emp_no, b.salary from employees a, salaries b where a.emp_no =       b.emp_no and a.hire_date = b.from_date) as starts
where now.emp_no = starts.emp_no
order by growth

11. 统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及次数sum

该题涉及多个表的 join

select d.dept_no, d.dept_name,count(s.emp_no) as sum
from departments d join dept_emp emp on d.dept_no = emp.dept_no
        join salaries s on emp.emp_no = s.emp_no
group by d.dept_no

12. 对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

第一道有点难度的题：计算排名，实质是实现对于salary的复用

select a.emp_no, a.salary, count(distinct b.salary) as rank
from salaries a, salaries b
where a.salary <= b.salary and
a.to_date = '9999-01-01' and b.to_date = '9999-01-01'
group by a.emp_no
order by a.salary desc, a.emp_no asc

本题的关键在于：

a.salary <= b.salary

1. 在输出a.salary的情况下，有多少个b.salary大于等于a.salary，所以有多少个就是多少名，不过在计算的时候需要count(==distinct== b.salary)，需要去除重复的薪资。
2. 因为使用了count()函数，所以需要进行group操作；
3. 不要忘记要求，还需要对salary和emp_no进行排序的排序要求；

13. 汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count

注意一下最后的group操作:

group by emp.dept_no,t.title

<img src="../../AppData/Roaming/Typora/typora-user-images/image-20200306155923848.png" alt="image-20200306155923848" style="zoom:67%;" />

不能少一个group操作，注意查看输出；

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

需要注意的问题：

1. select中的顺序需要和from的join顺序相一致

2. 使用下面的代码报错：

select c.name, count(distinct f.film_id)
from category c join film_category fc on c.category_id = fc.category_id
    join film f on fc.film_id = f.film_id and f.description like '%robot%'  
group by c.category_id
having count(f.film_id) > 4

但是复用film_category是可以通过的：

select c.name,count(f.film_id)  as fCount
from film f inner join film_category fc on f.film_id = fc.film_id and f.description like '%robot%'
inner join film_category fc2 on fc.category_id = fc2.category_id
inner join category c on fc2.category_id = c.category_id
group by fc2.category_id
having count(fc2.film_id)>=5;

暂时不明白为什么需要复用，应该是对于join的操作，理解不够

自己更改之后的代码：

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

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

    查询结果的拼接，使用“||”，使用空格拼接是“ " " ”

    空格使用：" "

16. 最后更新时间，默认是系统当前时间

    last_update timestamp not null default (datetime('now','localtime'))
    

17. mysql的各种插入

    1. 删除旧数据，然后插入数据，插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复

replace into

​ 2. 避免重复插入，插入的数据会导致UNIQUE索引或PRIMARY KEY发生 冲突/重复

insert or ignore into 

直接使用 insert ignore的方式在sql 3.7.9下会报错。

18. 创建一个actor_name表，将actor表中的所有first_name以及last_name导入改表。 actor_name表结构如下；

最开始自己的代码是：

create table actor_name(first_name varchar(45) not null,last_name varchar(45) not null)
go
create proc insert_name
as
begin
    select first_name,last_name from actor order by actor_id;
end
go
insert into actor_name exec insert_name

但是报错，后来自己稍微实验了一下好像sql的go在运行时都不能通过，所以后面更改代码:

create table actor_name(first_name varchar(45) not null,last_name varchar(45) not null);

insert into actor_name select first_name,last_name from actor order by actor_id

19. 对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

更改索引：

alter table actor add unique first_name(column_list);
alter table actor add index last_name(column_list);

创建索引：

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

20. 视图view虚拟表的一般创建

create view actor_name_view
as
select first_name as first_name_v, last_name as last_name_v
from actor

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

强制使用索引，使用方法是 indexed by语句

select * 
from salaries 
indexed by idx_emp_no
where emp_no = 10005

22. 构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。

create trigger audit_log after insert on employees_test
begin
insert into audit values(new.ID,new.NAME);
end

需要注意的地方:

1. 注意关键字“after”或者“before”，而且后面所跟的是“on”
2. 注意begin和end代码段中间是需要增加“;”
3. ==使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录==

23. 删除emp_no重复的记录，只保留最小的id对应的记录

注意sql的删除重复记录

delete from titles_test 

where emp_no in

(select emp_no from titles_test group by emp_no having count(emp_no)>1)

and id not in

(select min(id) from titles_test group by emp_no having count(emp_no)>1)

注意一点group的操作，查找的是emp_no的数目是否重复，删除emp_no的重复。

所以操作是：

group by emp_no having count(emp_no)>1

24. 列表更新的一般操作

update titles_test
set to_date = null, from_date = '2001-01-01'
where to_date = '9999-01-01'

25. 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。

update titles_test
set emp_no = replace(emp_no,10001,10005)
where id = 5

关于replace函数使用

replace(替换的字段名称,该字段被替换需要满足的条件值,应该被修改的值)

26. 将titles_test表名修改为titles_2017

alter table titles_test rename to titles_2017

关键字 rename；

27. 如何获取emp_v和employees有相同的数据？

intersect为产生交集的方法；

28. update使用的时候直接加 表名；

28. 使用sql输出语句注意空格问题

select "select count(*) from"||name||";"
as cnts
from sqlite_master
where type = 'table'

对于题目中要求生成语句，可以直接使用select语句，使用“||”但是可能oj判定的时候，会对可执行语句进行执行判定，||连接是没有空格，所以注意在语句from后保留空格。当然也有可能就是字符串的匹配。

29. 获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

代码：

SELECT first_name FROM employees ORDER BY substr(first_name,-2,2) asc

此处有一个问题，题目中说的是最后两个字母，那就应该是从-1开始，但是不能通过，从-2开始可以。不知道是不是存储上，有什么问题？希望有人解答一下

30. 按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

在group聚合操作字段进行合并操作使用 group_concat(x, y)

其中x使用连接字段，y是分隔符，可以不写，默认是 “ ，”分割

select dept_no, group_concat(emp_no) as employees
from dept_emp 
group by dept_no 

31. 查找排除当前最大、最小salary之后的员工的平均工资avg_salary。

如下代码是不能通过的：

select (sum(salary)-max(salary)-min(salary))/(count(emp_no)-2) as avg_salary
from salaries

使用如下代码，比较蠢，但却能通过

select avg(salary) as avg_salary
from salaries
where salary not in (select min(salary) from salaries) 
and salary not in (select max(salary) from salaries)
and to_date = "9999-01-01"

32. Mysql的分页查询；分页查询employees表，每5行一页，返回第2页的数据

select * from table limit (start-1)*number,number;

start：页码

number：每页能够显示的条数

select *
from employees limit (2-1)*5,5

33. exist的简单用法

使用含有关键字exists查找未分配具体部门的员工的所有信息。

select * 
from employees e 
where not exists
(select emp_no from dept_emp where e.emp_no = emp_no)

不是not in的用法：

select * 
from employees e 
where emp_no not exists
(select emp_no from dept_emp where e.emp_no = emp_no)

34. bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。

注意 case when 的使用

select e.emp_no, e.first_name, e.last_name, eb.btype, s.salary,
      ( case eb.btype
        when 1 then s.salary * 0.1
        when 2 then s.salary * 0.2
        else s.salary * 0.3 end) as bonus
from employees e join salaries s on e.emp_no = s.emp_no and s.to_date = '9999-01-01' 
     join emp_bonus eb on eb.emp_no = s.emp_no 

35. 按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。

对于同一个对象的累计求和，使用复用

select s1.emp_no, s1.salary, 
       (select sum(s2.salary)
        from salaries s2
        where s2.to_date = '9999-01-01' and s1.emp_no >= s2.emp_no
       ) as running_total
    from salaries s1
    where s1.to_date = '9999-01-01'

36. 对于employees表中，给出奇数行的first_name

在mysql中进行行数的奇偶数查询是一件比较困难的事情，因为本身mysql是没有rownumber的。

查找了一下，该题大多数通过code使用复用的方式。但是对于复用没有完全了解，使用的时候还是有问题。初始自己写的代码如下:

select e1.first_name
from employees e1
where ( select count(*)
        from employees e2
        where e1.emp_no <= e2.emp_no) % 2 = 1

但实际上上述代码不能通过，猜测e1.first_name <= e2.first_name并不是字段值得比较，应该是记录，这个地方后面补充一下具体复用原理。

select e1.first_name
from employees e1
where ( select count(*)
        from employees e2
        where e1.first_name <= e2.first_name) % 2 = 1

代码草稿,很久没有使用sql，很多语法错误，错误代码也保存下来，便于后面查看

select s.*, d.dept_no 
from salaries s join dept_manager d
/* from dept_manager d join salaries s */
on s.emp_no=d.emp_no
where s.to_date = '9999-01-01' and d.to_date = '9999-01-01'


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


select s.*, d.dept_no
from salaries s, dept_manager d
where s.emp_no  = d.emp_no
and s.to_date = '9999-01-01' and d.to_date = '9999-01-01'

select e.last_name, e.first_name, d.dept_no
from employees e join dept_emp d
on e.emp_no = d.emp_no

select e.emp_no, s.salary
from employees e join salaries s
on e.emp_no = s.emp_no and e.hire_date = s.from_date
order by e.emp_no desc

select emp_no,count(emp_no) as t from salaries group by emp_no 
and count(emp_no) >15;



select emp_no,count(emp_no) as t
from salaries group by emp_no
having count(emp_no) >15


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

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

select emp_no
from employees 
where emp_no not in (select emp_no from dept_manager)


select e.emp_no as emp_no, m.emp_no as manager_no
from dept_emp e, dept_manager m
where e.emp_no != m.emp_no and e.dept_no = m.dept_no
        and e.to_date='9999-01-01' and m.to_date='9999-01-01'


select e.dept_no, e.emp_no, max(s.salary)
from dept_emp e join salaries s
on e.emp_no = s.emp_no and e.to_date='9999-01-01' and s.to_date='9999-01-01'
group by dept_no




select title,count(title) as t
from titles
group by title
having t > 1


select *
from employees
where emp_no % 2 != 0
order by hire_date desc

select t.title, avg(s.salary) as avg
from salaries s,titles t
on s.emp_no = t.emp_no and s.to_date = '9999-01-01' and t.to_date = '9999-01-01'
group by title


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


select e.emp_no, max(s.salary) as salary,e.last_name,e.first_name
from employees e, salaries s
on e.emp_no = s.emp_no and s.to_date = '9999-01-01'
where s.salary not in(
select max(salary)
from salaries
where s.to_date = '9999-01-01'
)



select a.last_name, a.first_name, c.dept_name
from employees a left join dept_emp b on a.emp_no = b.emp_no
left join departments c on b.dept_no = c.dept_no


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

select e.emp_no, (s.salary - s.salary) as growth
from employees e,salaries s
where e.emp_no = s.emp_no
order by growth asc



select now.emp_no, (now.salary - starts.salary) as growth
from (select emp_no,salary from salaries where to_date='9999-01-01') as now,
     (select a.emp_no, b.salary from employees a, salaries b where a.emp_no = b.emp_no
        and b.from_date = a.hire_date) as starts
where now.emp_no = starts.emp_no
order by growth

select sCurrent.emp_no,(sCurrent.salary-sStart.salary)as growth
from(select emp_no,salary from salaries where to_date='9999-01-01')as sCurrent,
    (select a.emp_no,b.salary from employees a,salaries b where a.emp_no=b.emp_no 
        and c.from_date=e.hire_date)as sStart
where sCurrent.emp_no=sStart.emp_no
order by growth


select d.dept_no, d.dept_name,count(d.emp_no) as sum
from departments d join dept_emp emp on d.dept_no = emp.dept_no
        join salaries s on emp.emp_no = s.emp_no
group by d.dept_no


select emp_no, salary, (@rowno:=@rowno+1) as rank
from salaries
order by salary


select a.emp_no, a.salary, count(distinct b.salary) as rank
from salaries a, salaries b 
where a.salary <= b.salary and a.to_date = '9999-01-01' and b.to_date = '9999-01-01'
group by a.emp_no
order by a.salary desc,a.emp_no asc


select a.emp_no, a.salary, count(distinct a.salary) as rank
from salaries a, salaries b 
where a.salary > b.salary and a.to_date = '9999-01-01' and b.to_date = '9999-01-01'
group by a.emp_no
order by a.salary desc,a.emp_no asc



select emp.dept_no, emp.emp_no, s.salary
from dept_emp emp join salaries s on emp.emp_no = s.emp_no
and emp.to_date = '9999-01-01' and s.to_date = '9999-01-01'
where emp.emp_no not in
(select emp_no
from dept_manager)
 

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


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



select a.emp_no, a.from_date, (a.salary - b.salary) as salary_growth
from salaries a,salaries b
where a.emp_no = b.emp_no and salary_growth > 5000
        and strftime("%Y",a.to_date) - strftime("%Y",b.to_date) = 1
        or strftime("%Y",a.to_date) - strftime("%Y",a.to_date) = 1
order by salary_growth desc



select f.title, c.name, count(c.category_id)
from film f join film_category fc on f.film_id = fc.fim_id and f.description like '%,robot,%'
    join category c on c.category_id = fc.category_id
group by fc.category_id
having count(c.category_id) > 4



select c.name, count(f.film_id)
from film f join film_category fc on f.film_id = fc.fim_id and f.description like '%robot%'
    join category c on c.category_id = fc.category_id
group by c.category_id
where c.category_id in
(select category_id from film_category 
group by category_id having count(category_id) > 4)



select c.name, count(f.film_id)

from film f join film_category fc on f.film_id = fc.fim_id and f.description like '%robot%'
    join category c on c.category_id = fc.category_id
    
    
    
select c.name, count(f.film_id)
from category c join film_category fc on c.category_id = fc.category_id
    join film f on fc.film_id = f.film_id and f.description like '%robot%'  
where c.category_id in
(select category_id from film_category 
group by category_id having count(category_id) > 4)
group by c.category_id



select c.name, count(f.film_id)
from film f join film_category fc on f.film_id = fc.fim_id and f.description like '%robot%'
    join category c on c.category_id = fc.category_id
where c.category_id in
(select category_id from film_category 
group by category_id having count(category_id) > 4)
group by c.category_id


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


select c.name, count(distinct f.film_id)
from category c join film_category fc on c.category_id = fc.category_id
    join film f on fc.film_id = f.film_id and f.description like '%robot%'  
group by c.category_id
having count(f.film_id) > 4

select c.name,count(f.film_id)  as fCount
from film f inner join film_category fc on f.film_id = fc.film_id and f.description like '%robot%'
inner join film_category fc2 on fc.category_id = fc2.category_id
inner join category c on fc2.category_id = c.category_id
group by fc2.category_id
having count(fc2.film_id)>=5;

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

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


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

create table 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 into actor(actor_id, first_name,last_name,last_update)
            values(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
                  (2, 'NICK','WAHLBERG', '2006-02-15 12:34:33')



insert ignore into actor(actor_id, first_name, last_name, last_update)
                   values('3','first_name','last_name','last_update')


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




create table actor_name(first_name varchar(45) not null,last_name varchar(45) not null)
go
create proc insert_name
as
begin
    select first_name,last_name from actor order by actor_id
end
go
insert into actor_name exec insert_name




alter table actor add unique first_name(column_list);
alter table actor add index last_name(column_list);


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

create view actor_name_view
as
select first_name as first_name_v, last_name as last_name_v
from actor



select * 
from salaries 
indexed by idx_emp_no
where emp_no = 10005


alter table 'actor' 
add column 'create_date' datetime not null default '0000-00-00 00:00:00' after 'last_update'


create trigger audit_log after insert on employees_test
begin
insert into audit values(new.ID,new.NAME);
end

delete from titles_test 
where emp_no in 
(select emp_no from titles_test group by emp_no having count(emp_no)>1)
and id not in 
(select min(id) from titles_test group by emp_no having count(emp_no)>1)


update titles_test
set to_date = null, from_date = '2001-01-01'
where to_date = '9999-01-01'


update titles_test t
t.emp_no = 
case when t.emp_no = 10001 
then (select tt.emp_no from titles_test tt where tt.emp_no = 10005)



update titles_test
set emp_no = replace(emp_no,10001,10005)
where id = 5

alter table title_test rename to titles_2017


alter table audit add constraint FK_emp_no foreign key emp_no references employees_test(id)

DROP TABLE audit;
CREATE TABLE audit(
    EMP_no INT NOT NULL,
    create_date datetime NOT NULL,
    FOREIGN KEY(EMP_no) REFERENCES employees_test(ID));


drop table audit;
create table audit(
    EMP_no int not null,
    create_date datetime not null,
    foreign key(EMP_no) references employees_test(ID));


select * from emp_v intersect select * from employees


update salaries 
set salary = salary * 1.1
where emp_no in (select emp_no from emp_bonus)


select "select count(*) from "||name||";"
as cnts
from sqlite_master
where type = 'table'

select last_name||"'"||first_name
from employees

create table tmp(string varchar(10))
insert into tmp values('10,A,B');
select (len(string) - len(replace(string, ',', '')) as cnt
from tmp

select length('10,A,B') - length(replace('10,A,B',',',''))

select first_name
from employees
order by (cast(substring(first_name,-1,2)as unsigned)) desc

select first_name
from employees
order by substring(first_name,-1,2) asc



select dept_no, group_concat(emp_no) as employees
from dept_emp 
group by dept_no 


select (sum(salary)-max(salary)-min(salary))/(count(emp_no)-2) as avg_salary
from salaries


select *
from table limit (2-1)*5,5



select dm.emp_no, dm.dept_no, eb.btype,eb.recevied
from dept_emp dm left join emp_bonus eb on dm.emp_no = eb.emp_no



select * 
from employees e 
where emp_no not exists
(select emp_no from dept_emp e.emp_no = emp_no)



select * 
from employees
where emp_no in 
(select emp_no
from emp_v
)




select e.emp_no, e.first_name, e.last_name, eb.btype, s.salary,
      ( case eb.btype
        when 1 then s.salary * 0.1
        when 2 then s.salary * 0.2
        else s.salary * 0.3 end) as bonus
from employees e join salaries s on e.emp_no = s.emp_no and s.to_date = '9999-01-01' 
     join emp_bonus eb on eb.emp_no = s.emp_no 



select a.emp_no,a.first_name,a.last_name,b.btype,c.salary,
(case b.btype
 when 1 then c.salary*0.1
 when 2 then c.salary*0.2
 else c.salary*0.3 end
)as bonus
from employees as a 
inner join emp_bonus as b 
on a.emp_no=b.emp_no
inner join salaries as c on a.emp_no=c.emp_no
where c.to_date='9999-01-01'


select s1.emp_no, s1.salary, 
       (select sum(s2.salary)
        from salaries s2
        where s2.to_date = '9999-01-01' and s1.emp_no < s2.emp_no
       ) as running_total
    from salaries s1
    where s1.to_date = '9999-01-01'



select e1.first_name
from employees e1
where (select count(*) from employees e2 where e1.emp_no >= e2.emp_no)%2 = 1


set @rownum = 0;
select first_name
from employees
where (@rownum:=@rownum+1)%2=1



select name ,time 
from A ,(select @rownum :=0) tmp_table where (@rownum :=@rownum+1)%2=1; 


select e1.first_name
from employees e1
where ( select count(*)
        from employees e2
        where e1.first_name <= e2.first_name) % 2 = 1