# 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
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
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)
where (user_id, date) in (select user_id, date_add(min(date), interval 1 day)
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
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
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
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
group by date) a
left join
(select date_add(min(date), interval -1 day) as date, count(user_id) as count_2
where (user_id, date) in (select user_id, date_add(min(date), interval 1 day)
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