牛客

sql18. 获取当前薪水第二多的员工编号emp_no

# method 1
select e.emp_no, salary, last_name, first_name 
from employees e
inner join salaries s using(emp_no) 
where salary = 
(select max(salary) from salaries
where salary < (select max(salary) from salaries))

# method 2
select e.emp_no, salary, last_name, first_name 
from employees e
inner join salaries s using(emp_no) 
where salary = 
(select s1.salary 
from salaries s1, salaries s2
where s1.salary < s2.salary 
group by s1.salary 
having count(distinct s2.salary) = 1)

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

# method 1
select sub.emp_no, 
    ma_no as manager_no, 
    s1.salary as emp_salary, 
    s2.salary as manager_salary
from 
(select de.emp_no, de.dept_no, dm.emp_no as ma_no
from dept_emp de
inner join dept_manager dm
on de.dept_no = dm.dept_no) sub 
inner join salaries s1 
on sub.emp_no = s1.emp_no
inner join salaries s2
on sub.ma_no = s2.emp_no
group by sub.dept_no 
having(emp_salary>manager_salary) 

# method 2
select de.emp_no, dm.emp_no as manager_no,
    s1.salary as emp_salary,
    s2.salary as manager_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 dm.emp_no = s2.emp_no
having(emp_salary>manager_salary)

sql36.创建一个actor_name表，将actor表中的last_name和first_name导入该表

# method 1
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
# method 2
create table actor_name as select first_name, last_name from actor

sql37.对first_name和last_name分别创建唯一索引，和普通索引

# method 1
alter table actor  add unique uniq_idx_firstname (first_name);
alter table actor  add index  idx_lastname (last_name);
# method 2
CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);

sql38.针对actor表创建视图 actor_table_view

# method1
create view actor_name_view as 
select first_name as first_name_v, last_name as last_name_v from actor 
 
# method 2
create view actor_name_view (first_name_v, last_name_v) as 
select first_name, last_name from actor  

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

# method 1
update salaries s join emp_bonus e on s.emp_no  = e.emp_no 
set s.salary = s.salary*1.1
where s.to_date='9999-01-01'

# method 2
update salaries s 
set s.salary = s.salary*1.1
where s.to_date='9999-01-01'
and s.emp_no in (select emp_no from emp_bonus)

sql60. 统计salary的累计和

# method 1
select emp_no, salary, 
sum(salary) over(order by emp_no) as running_total
from salaries
WHERE to_date = '9999-01-01'

# method 2
select s1.emp_no, s1.salary, sum(s2.salary) as running_total
from salaries s1 join salaries s2 
on s1.emp_no >= s2.emp_no 
where s2.to_date = '9999-01-01' and s1.to_date = '9999-01-01' 
group by s1.emp_no
order by s1.emp_no

sql61. 对于employee表中，给出奇数行的first_name

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

# method 2

select first_name from 
(select emp_no, first_name, row_number() over(order by first_name asc) as rk
from employees) sub 
where mod(rk, 2)= 1
order by emp_no

sql63.刷题通过的题目排名

# method 1
select id, number, dense_rank() over(order by number desc) as t_rank 
from passing_number
order by t_rank asc, id asc

# method 2
select p1.id,p1.number,
count(distinct p2.number) as t_rank from 
passing_number as p1, passing_number as p2
where p1.number <= p2.number
group by p1.id
order by t_rank, p1.id