Niu Ke interview required real questions SQL chapter 72 full answers - advanced chapter

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

Tags: MySQL SQL leetcode Interview

Posted by glcarlstrom on Mon, 02 May 2022 21:55:06 +0300