牛客网-数据库SQL实战

查找最晚入职员工的所有信息

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

查找入职员工时间排名倒数第三的员工所有信息

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

查找当前薪水详情以及部门编号dept_no

select salaries.*,dept_manager.dept_no from 
salaries, dept_manager 
where salaries.emp_no = dept_manager.emp_no
and salaries.to_date = "9999-01-01"
and dept_manager.to_date = "9999-01-01";

查找所有已经分配部门的员工的last_name和first_name

select employees.last_name, employees.first_name, dept_emp.dept_no 
from employees, dept_emp  
where employees.emp_no = dept_emp.emp_no;

查找所有员工的last_name和first_name以及对应部门编号dept_no

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

查找所有员工入职时候的薪水情况

select employees.emp_no, salaries.salary  
from employees, salaries 
where employees.emp_no = salaries.emp_no 
and employees.hire_date = salaries.from_date 
order by employees.emp_no DESC

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

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

找出所有员工当前薪水salary情况

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

获取所有部门当前manager的当前薪水情况

select dept_manager.dept_no, dept_manager.emp_no, salaries.salary 
from dept_manager, salaries 
where dept_manager.emp_no = salaries.emp_no 
and salaries.to_date = '9999-01-01' 
and dept_manager.to_date = '9999-01-01' 
order by dept_manager.emp_no;

获取所有非manager的员工emp_no

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

获取所有员工当前的manager

select dept_emp.emp_no, dept_manager.emp_no 
from dept_emp, dept_manager where
dept_emp.dept_no = dept_manager.dept_no and
dept_emp.emp_no != dept_manager.emp_no and
dept_emp.to_date = '9999-01-01' and
dept_manager.to_date = '9999-01-01' ;

获取所有部门中当前员工薪水最高的相关信息

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

从titles表获取按照title进行分组

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

从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略。

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

查找employees表

select * from employees where 
emp_no%2 == 1 and 
last_name != 'Mary' 
order by hire_date DESC;

统计出当前各个title类型对应的员工当前薪水对应的平均工资

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

获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no, salary from salaries 
order by salary DESC 
limit 1,1;

获取当前薪水第二多的员工的emp_no以及其对应的薪水salary，不使用order by

select e.emp_no,max(s.salary),e.last_name,e.first_name from
employees as e inner join salaries as s on e.emp_no = s.emp_no 
where salary != (select max(salary) from salaries where to_date = '9999-01-01');

查找所有员工的last_name和first_name以及对应的dept_name

select e.last_name, e.first_name, d.dept_name from 
(employees as e left join dept_emp on e.emp_no = dept_emp.emp_no) 
left join departments as d on d.dept_no = dept_emp.dept_no;

查找员工编号emp_now为10001其自入职以来的薪水salary涨幅值

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

查找所有员工自入职以来的薪水涨幅情况

select sCurrent.emp_no, (sCurrent.salary - sStart.salary) as growth
from (select s.emp_no, s.salary from employees as e left join salaries as s on e.emp_no = s.emp_no where s.to_date = '9999-01-01') as sCurrent
inner join (select s.emp_no, s.salary from employees as e left join salaries as s on e.emp_no = s.emp_no where s.from_date = e.hire_date) as sStart
on sCurrent.emp_no = sStart.emp_no
order by growth

统计各个部门对应员工涨幅的次数总和

select de.dept_no, dp.dept_name, count(sa.salary) as sum 
from (dept_emp as de inner join salaries as sa on de.emp_no = sa.emp_no) 
inner join departments as dp on de.dept_no = dp.dept_no 
group by de.dept_no

对所有员工的薪水按照salary进行按照1-N的排名

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

获取所有非manager员工当前的薪水情况

select de.dept_no, em.emp_no, sa.salary from 
(employees em inner join salaries sa on em.emp_no = sa.emp_no and sa.to_date = '9999-01-01') 
inner join dept_emp de on de.emp_no = sa.emp_no where 
em.emp_no not in (select emp_no from dept_manager where to_date = '9999-01-01');

获取员工其当前的薪水比其manager当前薪水还高的相关信息

select de.emp_no, dm.emp_no, s1.salary, s2.salary from 
dept_emp de, dept_manager dm, salaries s1, salaries s2 where 
de.dept_no = dm.dept_no and 
de.emp_no = s1.emp_no and s1.to_date = '9999-01-01' and
dm.emp_no = s2.emp_no and s2.to_date = '9999-01-01' and
s1.salary > s2.salary;

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

select de.dept_no, de.dept_name, ti.title, count(ti.title) as cnt 
from titles as ti inner join dept_emp as d 
on ti.emp_no = d.emp_no and ti.to_date = '9999-01-01' and d.to_date = '9999-01-01' 
inner join departments as de on de.dept_no = d.dept_no 
group by de.dept_no, ti.title;

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

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

查找描述信息中包括robot的电影对应的分类名称以及电影数目

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

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

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

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

select f.title, f.description from 
film as f, film_category as fc, category c where 
f.film_id = fc.film_id and 
fc.category_id = c.category_id and 
c.name = 'Action';

获取select * from employees对应的执行计划

explain select * from employees

将employees表的所有员工的last_name和first_name拼接起来作为Name

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

创建一个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')) 

批量插入数据

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

批量插入数据，不使用replace操作

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

创建一个actor_name表

create table actor_name as select first_name, last_name from actor;

对first_name创建唯一索引uniq_idx_firstname

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

针对actor表创建视图actor_name_view

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

针对上面的salaries表emp_no字段创建索引idx_emp_no

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

在last_update后面新增加一列名字为create_date

alter table actor add column create_date datetime not null default('0000-00-00 00:00:00');

构造一个触发器audit_log

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

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

delete from titles_test where id not in(select min(id) from titles_test group by emp_no);

将所有to_date为9999-01-01的全部更新为NULL

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

将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

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

将titles_test表名修改为titles_2017

alter table titles_test rename to titles_2017;

在audit表上创建外键约束，其emp_no对应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));

如何获取emp_v和employees有相同的数据no

select * from emp_v intersect select * from employees;

将所有获取奖金的员工当前的薪水增加10%

update salaries set salary = salary*1.1 where emp_no in 
(select s.emp_no from emp_bonus as e, salaries as s where
s.to_date = '9999-01-01' and s.emp_no = e.emp_no);

针对库中的所有表生成select count(*)对应的SQL语句

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

将employees表中的所有员工的last_name和first_name通过(‘)连接起

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

查找字符串’10,A,B’ 中逗号’,’出现的次数cnt

select length('10,A,B') - length(replace('10,A,B',',',"")) as cnt;

获取Employees中的first_name

select first_name from employees order by substr(first_name, -2);

按照dept_no进行汇总

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

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

select avg(salary) from salaries where 
to_date = '9999-01-01' and 
salary not in(select max(salary) from salaries) and (select min(salary) from salaries);

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

select * from employees limit 5,5

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

select * from employees where emp_no not in (select emp_no from dept_emp)
//不知道怎么使用exist。。

获取employees中的行数据，且这些行也存在于emp_v中

select * from employees where emp_no > 10005;

获取有奖金的员工相关信息。

select em.emp_no, em.first_name, em.last_name, eb.btype, sa.salary,
(case when eb.btype<=2 then eb.btype*0.1*sa.salary when eb.btype>=3 then 0.3*sa.salary end ) as bonus 
from employees as em inner join salaries as sa on em.emp_no=sa.emp_no 
inner join emp_bonus as eb on em.emp_no=eb.emp_no and sa.to_date='9999-01-01';

统计salary的累计和running_total

select s2.emp_no, s2.salary, (select sum(s3.salary) from salaries s3 where s2.emp_no >= s3.emp_no and s3.to_date = '9999-01-01') as runing_total 
from salaries s2 where s2.to_date = '9999-01-01' 
order by s2.emp_no;

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

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