SQL日常练习题

牛客网练习题
1、查找最晚入职员工的所有信息

select * from employees 
order by hire_date desc 
limit 1;

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

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列是最后一列)

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

4、查找所有已经分配部门的员工的last_name和first_name以及dept_no(请注意输出描述里各个列的前后顺序)

select e.last_name,e.first_name,d.dept_no
from employees e,dept_emp d
on d.emp_no = e.emp_no
where d.dept_no is not null;

5、查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序)

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

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

select s.emp_no,s.salary
from employees e , salaries s
where s.emp_no = e.emp_no
and e.hire_date = s.from_date
order by s.emp_no ;

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

select emp_no , count(from_date) t 
from salaries
Group by emp_no
having t>15;

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

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(请注意，同一个人可能有多条薪水情况记录)

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

10、获取所有非manager的员工emp_no

select e.emp_no
from employees e 
left join dept_manager d 
on d.emp_no = e.emp_no
where d.dept_no is null;

11、获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_no。

select em.emp_no , m.emp_no
from dept_emp em , dept_manager m 
where em.dept_no = m.dept_no
and em.emp_no <> m.emp_no
and m.to_date='9999-01-01';

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

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

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

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 t >=2;

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

select * from employees
where emp_no%2 = 1
and last_name <> 'Mary'
order by hire_date desc;

16、统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。

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

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

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完成吗

select e.emp_no,s.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'
and s.salary = (select max(s.salary) from salaries s 
                where s.salary < 
                (select MAX(s.salary)from salaries s)
                limit 0,1
);

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

select e.last_name , e.first_name , d.dept_name 
from employees e 
left outer  join dept_emp de on e.emp_no = de.emp_no
left outer  join departments d on de.dept_no = d.dept_no 
;

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

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

select (a.salary-b.salary) growth 
from (select salary from salaries 
      where emp_no = 10001
      order by to_date desc limit 1
) a ,(select salary from salaries 
       where emp_no = 10001
      order by to_date asc limit 1
) b ;

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

select a.emp_no , (a.salary-b.salary) as growth
from 
(select e.emp_no ,s.salary  from salaries s ,employees e 
on s.emp_no = e.emp_no
where s.to_date='9999-01-01'
) a 
,(select e.emp_no ,s.salary  from salaries s ,employees e 
on s.emp_no = e.emp_no
where e.hire_date = s.from_date
)  b 
where a.emp_no = b.emp_no
order by growth asc ;

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

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

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

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

select s1.emp_no , s1.salary , (
select count(distinct s2.salary) from salaries s2 
    where s2.to_date = '9999-01-01'
    and s2.salary>= s1.salary
)  rank
from salaries s1 
where s1.to_date = '9999-01-01'
order by s1.salary desc, s1.emp_no ;

24、获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示

select de.dept_no , a.emp_no ,s.salary
from (
    select e.emp_no from employees e 
    where  not exists (
        select * from dept_manager ma where ma.emp_no = e.emp_no
        and ma.to_date='9999-01-01')
  ) a  
join salaries s on s.emp_no = a.emp_no
join dept_emp de on de.emp_no = a.emp_no
where s.to_date='9999-01-01'
and de.to_date='9999-01-01'
 ;

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

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

1 领导的工资

select salary manager_salary ,dept_no , de.emp_no manager_no
from salaries s ,dept_manager de 
on s.emp_no = de.emp_no 

2 员工的工资

select salary emp_salary ,dept_no , em.emp_no emp_no
from salaries s ,dept_emp em 
on s.emp_no = em.emp_no 

3 员工工资>领导工资

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

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’，那么这个可能是员工之前的职位信息，也不计入统计)

select d.dept_no , d.dept_name , t.title , count(t.title) count
from departments d 
join dept_emp de on de.dept_no = d.dept_no 
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 d.dept_no , t.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)

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

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 f 
join film_category fc ON fc.film_id = f.film_id 
join category c ON c.category_id = fc.category_id 
WHERE f.description like '%robot%'
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
 ;

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

select  f.film_id 电影id ,  f.title 名称
from film f left join film_category fc
 on f.film_id = fc.film_id 
where fc.film_id is null ;

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

select f.title, f.description 
from (
select c.category_id , f.film_id
from film_category fc , category c , film f 
WHERE f.film_id =fc.film_id
AND fc.category_id = c.category_id
AND c.name  = 'Action'
) a , film f 
WHERE f.film_id = a.film_id 
;

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

explain select * from employees

32、将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分
(注：该数据库系统是sqllite,字符串拼接为 || 符号，不支持concat函数)

select last_name || ' ' || first_name as Name 
from employees ;

33、创建一个actor表，包含如下列信息(注：sqlite获取系统默认时间是datetime(‘now’,‘localtime’))

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

34、对于表actor批量插入如下数据(不能有2条insert语句哦!)

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

35、对于表actor批量插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)

insert OR IGNORE into actor values ('3','ED','CHASE','2006-02-15 12:34:33')
  REPLACE into actor values ('3','ED','CHASE','2006-02-15 12:34:33')

36、请你创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.
actor_name表结构如下：

create table if not exists 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;

37、对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname
(请先创建唯一索引，再创建普通索引)

CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);

38、针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：

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

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

CREATE INDEX idx_emp_no on salaries(emp_no);
SELECT * FROM salaries INDEXED BY idx_emp_no WHERE emp_no = 10005;

select * from salaries FORCE INDEX (idx_emp_no) where emp_no = 10005;

40、现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’0000-00-00 00:00:00’

alter table  actor ADD column
create_date datetime  NOT NULL default( '0000-00-00 00:00:00')

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

create trigger audit_log AFTER INSERT
ON employees_test FOR EACH ROW
begin
    insert into audit values(new.id, new.name);
end;

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

DELETE FROM titles_test where id not in
(SELECT MIN(id)  from titles_test
group by emp_no) 
;
DELETE FROM titles_test where id not in
(SELECT * FROM
(SELECT MIN(id)  from titles_test
group by emp_no) A )
;

43、将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

UPDATE  titles_test set to_date = NULL where to_date = '9999-01-01';
UPDATE  titles_test set from_date = '2001-01-01';

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

update titles_test set id = 5 , emp_no = '10001' where id = 5 , emp_no = '10005';
replace into titles_test values 
('5', '10005', 'Senior Engineer', '1986-06-26', '9999-01-01');

45、将titles_test表名修改为titles_2017。

alter table titles_test rename to titles_2017;

46、在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
(audit已经创建，需要先drop)

alter table audit add constraint foreign key audit(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)
);

47、存在如下的视图：
create view emp_v as select * from employees where emp_no >10005;
如何获取emp_v和employees有相同的数据？

select * from emp_v

48、请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)

UPDATE salaries SET salary=salary*1.1 where emp_no in
(SELECT e.emp_no 
 from salaries s , emp_bonus e 
 on e.emp_no = s.emp_no)
and to_date='9999-01-01';

49、针对库中的所有表生成select count(*)对应的SQL语句，如数据库里有以下表，
(注:在 SQLite 中用 “||” 符号连接字符串，无法使用concat函数)

select 'select count(*) from' || name || ';'  cnts
from sqlite_master WHERE type = 'table';
select comcat('select count(*) from', table_name , ';')  cnts
from (select table_name from information_schema.tables) as new;

50、将employees表中的所有员工的last_name和first_name通过(’)连接起来。(不支持concat，请用||实现)

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

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

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

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

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

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

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

54、查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary。

SELECT avg(salary) avg_salary FROM salaries
WHERE salary not in(
    select max(salary) from salaries where to_date = '9999-01-01')
AND salary not in(
    select min(salary) from salaries where to_date = '9999-01-01') )
AND to_date = '9999-01-01';

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

select * from employees 
limit 5,5

56、获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received，没有分配具体的员工不显示

select e.emp_no , d.dept_no , eb.btype , eb.received
from employees e  
join dept_emp d on d.emp_no = e.emp_no 
left join emp_bonus eb  on eb.emp_no = e.emp_no ;

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

用exists：

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

不用exists:

select * from employees e
left join dept_emp d on d.emp_no = e.emp_no
where d.emp_no is null;

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

select e.emp_no ,e.first_name , e.last_name , eb.btype , s.salary , (s.salary*eb.btype*0.1) bonus
from employees e , emp_bonus eb ,salaries s
where e.emp_no = eb.emp_no
and e.emp_no=s.emp_no
and eb.btype is not null
and s.to_date='9999-01-01'
;

60、按照salary的累计和running_total，其中running_total为前N个当前( to_date = ‘9999-01-01’)员工的salary累计和，其他以此类推

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

61、对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name

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