This article mainly contains the advanced part of the 72 questions in the SQL article, which is a must-tested question in the interview. It mainly includes the more difficult level (18 questions) and the difficult level (4 questions). The questions are more complicated, and my answer may not be the best solution. , Welcome everyone to correct me!
Question link: Niu Ke selected database SQL combat
SQL Feature Article Forwarding Door: Niu Ke interview must-test real questions SQL chapter 72 full answers - basics
Difficult level (18 tracks)
SQL18: Find the current employee number emp_no with the second highest salary.
select employees.emp_no, salary, last_name, first_name from (select row_number() over(order by salary desc) num, emp_no, salary from salaries where to_date='9999-01-01') as temp right join employees on temp.emp_no = employees.emp_no where num = 2
SQL21: Find the salary increase of all employees since they were hired.
Law one:(self-solve)
select employees.emp_no, max(salary)-min(salary) as growth
from (select row_number() over(partition by emp_no order by to_date) num1,
row_number() over(partition by emp_no order by to_date desc) num2,
emp_no, salary, to_date from salaries) as temp right join employees
on temp.emp_no = employees.emp_no
where num1 = 1 or num2 = 1
group by employees.emp_no
having max(to_date)='9999-01-01'
order by growth
Method 2: (refer to the answer, divided into two queries)
select a.emp_no, b.salary - a.salary as growth
from
(select e.emp_no, s.salary
from employees e join salaries s
on e.emp_no=s.emp_no
where hire_date=from_date) as a
join
(select e.emp_no, s.salary
from employees e join salaries s
on e.emp_no=s.emp_no
where to_date='9999-01-01') as b
on a.emp_no = b.emp_no
order by growth
SQL23: Rank 1-N for the current salary of all employees according to salary.
select emp_no, salary, dense_rank() over(order by salary desc) t_rank from salaries where to_date='9999-01-01'
SQL24: Get the current salary of all non-manager employees.
# Employees with no assigned department are not counted select dept_no, t.emp_no, salary from (select emp_no, salary from salaries where to_date='9999-01-01' and emp_no not in (select emp_no from dept_manager where to_date='9999-01-01')) as t join dept_emp d on t.emp_no = d.emp_no
SQL25: Get information about an employee whose current salary is higher than his manager's current salary.
# Reference idea: Use two tables to count the salaries of employees and manager s select a.emp_no, b.emp_no, a.salary, b.salary from (select d.emp_no, d.dept_no, s.salary from dept_emp d join salaries s on d.emp_no = s.emp_no where s.to_date='9999-01-01') a join (select m.emp_no, m.dept_no, s.salary from dept_manager m join salaries s on m.emp_no = s.emp_no where s.to_date='9999-01-01') b on a.dept_no = b.dept_no where a.salary>b.salary
SQL27: Give the employee number emp_no for each employee whose annual salary increase exceeds 5000.
# Reference idea: self-connection select s1.emp_no, s2.from_date, s2.salary-s1.salary as salary_growth from salaries s1 join salaries s2 on s1.to_date=s2.from_date and s1.emp_no=s2.emp_no where s2.salary-s1.salary>5000 order by salary_growth desc
SQL28: Find the category name and the number of movies corresponding to movies whose description information includes robot.
# Reference answer: The three tables are fully connected and then filtered. Note that group by must be added
select c.name, count(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
where f.description like '%robot%' and fc.category_id in (select category_id
from film_category
group by category_id
having count(film_id)>=5)
group by c.name
SQL56: Get emp_no of all employees.
select e.emp_no, d.dept_no, btype, received from employees e join dept_emp d on e.emp_no = d.emp_no left join emp_bonus b on e.emp_no = b.emp_no
SQL59: Get information about employees with bonuses.
select e.emp_no, first_name, last_name, btype, salary, (case btype
when 1 then salary*0.1
when 2 then salary*0.2
else salary*0.3
end) bonus
from employees e join salaries s
on e.emp_no = s.emp_no
join emp_bonus b
on e.emp_no = b.emp_no
where s.to_date='9999-01-01'
Note: the usage of case
SQL60: Statistics cumulative sum of salary and running_total.
select emp_no, salary, sum(salary) over(order by emp_no) running_total from salaries where to_date = '9999-01-01'
Note: window function + self-connection to take unequal conditions
SQL61: For the employees table, give the first_name of odd rows.
select e.first_name from (select row_number() over(order by first_name asc) num, first_name from employees) as t join employees e on t.first_name=e.first_name where num%2=1
SQL65: Unusual message probability.
select e.date, round(sum(case e.type when 'no_completed' then 1 else 0 end)/count(type),3) as p from email e join user u1 on e.send_id=u1.id join user u2 on e.receive_id=u2.id where u1.is_blacklist=0 and u2.is_blacklist=0 group by e.date order by e.date
SQL67: The latest login date of each Niuke (2)
select u_n, c_n, d from (select u.name as u_n, c.name as c_n , l.date as d, row_number() over(partition by l.user_id order by l.date desc) num from login l join user u on l.user_id=u.id join client c on l.client_id=c.id) as t where num=1 order by u_n
SQL68: The latest login date of each Niuke (3)
select round(count(distinct user_id)/(select count(distinct user_id) from login), 3)
from login
where (user_id, date) in (select user_id, date_add(min(date), interval 1 day)
from login
group by user_id)
Note: The usage of finding the day after a certain day in MySQL is: date_add(yyyy-mm-dd, interval 1 day)
SQL69: The most recent login date of each Niuke (4)
select date, sum(case num when 1 then 1 else 0 end) as new from (select user_id, date, row_number() over(partition by user_id order by date) num from login) as t group by date order by date
SQL71: The most recent login date of each Niuke (6)
select u.name as u_n, p.date as date, sum(number) over(partition by user_id order by date) ps_num from passing_number p join user u on p.user_id = u.id order by date, u_n
SQL74: Exam Score (3)
select id, name, score from (select g.id id, l.name name, score, dense_rank() over(partition by g.language_id order by score desc) num from grade g join language l on g.language_id=l.id) as t where num<=2 order by name, score desc, id
SQL75: Exam Score (4)
select job, (case when count(id)%2=0 then cast(count(id)/2 as signed) else cast((count(id)+1)/2 as signed) end) as start, (case when count(id)%2=0 then cast((count(id)+2)/2 as signed) else cast((count(id)+1)/2 as signed) end) as end from grade group by job order by job
Note: Convert floating point numbers to integers: cast(col as signed)
Difficulty level (4 lanes)
SQL12: Get information about the current employee with the highest salary in all departments.
select dept_no, emp_no, salary from(select e.dept_no, e.emp_no, s.salary, rank() over(partition by e.dept_no order by s.salary desc) num from dept_emp e join salaries s on e.emp_no=s.emp_no where e.to_date='9999-01-01' and s.to_date='9999-01-01') as t where num=1 order by dept_no
SQL26: Summarize the assigned number of title types of current employees in each department.
select t.dept_no, d.dept_name, t.title, t.count from(select e.dept_no, t.title, count(title) as count from dept_emp e join titles t on e.emp_no=t.emp_no where e.to_date='9999-01-01' and t.to_date='9999-01-01' group by e.dept_no, t.title) t join departments d on t.dept_no = d.dept_no order by t.dept_no, t.title
SQL70: The most recent login date of each Nioke (5)
select a.date, round(ifnull(ifnull(count_2, 0)*1.0/count_1, 0), 3) p
from
(select date, sum(case num when 1 then 1 else 0 end) as count_1
from(select user_id, date, row_number() over(partition by user_id order by date) num
from login) as t
group by date) a
left join
(select date_add(min(date), interval -1 day) as date, count(user_id) as count_2
from login
where (user_id, date) in (select user_id, date_add(min(date), interval 1 day)
from login
group by user_id)
group by date) b
on a.date = b.date
Note: ifnull() is used to determine whether the first expression is null, if it is null, it returns the value of the second parameter, and if it is not null, it returns the value of the first parameter. Specific form: ifnull(expression, alt_value)
SQL76: Exam Score (5)
# Note: Pay attention to the repeated use of partition by and group by select B.* from (select job, (case when count(id)%2=0 then cast(count(id)/2 as signed) else cast((count(id)+1)/2 as signed) end) as start, (case when count(id)%2=0 then cast((count(id)+2)/2 as signed) else cast((count(id)+1)/2 as signed) end) as end from grade group by job) A join (select id, job, score, row_number() over(partition by job order by score desc) t_rank from grade) B on A.job=B.job and B.t_rank between A.start and A.end order by B.id