编程知识 cdmana.com

如何快速掌握MYSQL?附牛客網精選的50道SQL題目詳解【新手推薦】

如何才能快速掌握MYSQL?如何熟悉使用SQL以滿足日常工作需求?

如果你目前啥也不會,只停留在知道SELECT用於查詢的層面的話,又想要快速掌握MYSQL,那麼刷題,並且過程中不會什麼補什麼,就是鞏固和提昇自己的SQL語言能力最快捷的方法。

在之前的一篇博客中,我給出了LeetCode上出現頻率最高的50道數據庫題目詳解,那麼這一次,我也SQ給出了牛客網上的50道SQL題,希望對大家有所收獲。

補充:牛客網相對於LeetCode而言題目較少,且難度較低一些,但勝在免費,並且有些公司筆試的時候會直接飲用牛客網上的原題,所以也非常值得我們去刷一刷題。

目錄

SQL1 入門 查找最晚入職員工的所有信息

原題鏈接
在這裏插入圖片描述

SELECT * FROM employees
WHERE hire_date = (SELECT max(hire_date) FROM employees)

SQL2 簡單 查找入職員工時間排名倒數第三的員工所有信息

原題鏈接
在這裏插入圖片描述

/* with tmp as ( SELECT *, rank() over(order by hire_date desc) rk FROM employees ) SELECT emp_no, birth_date, first_name, last_name, gender, hire_date FROM tmp WHERE rk=3 */
SELECT * 
FROM employees
order by hire_date DESC
LIMIT 2,1

SQL3 中等 查找當前薪水詳情以及部門編號dept_no

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

SELECT s.emp_no,s.salary, s.from_date, s.to_date, d.dept_no
FROM dept_manager d 
LEFT JOIN salaries s
ON d.emp_no = s.emp_no
ORDER BY s.emp_no

SQL4 簡單 查找所有已經分配部門的員工的last_name和first_name以及dept_no

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

SELECT e.last_name, e.first_name, d.dept_no
FROM dept_emp d
join employees e
on d.emp_no = e.emp_no

SQL5 中等 查找所有員工的last_name和first_name以及對應部門編號dept_no

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

/*較上一題多了兩行null,所以要將left join 改成right join*/
SELECT e.last_name, e.first_name, d.dept_no
FROM dept_emp d
RIGHT JOIN employees e
ON d.emp_no = e.emp_no

SQL7 簡單 查找薪水記錄超過15次的員工號emp_no以及其對應的記錄次數t

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

SELECT emp_no, COUNT(emp_no) t
FROM salaries
GROUP BY emp_no
HAVING COUNT(emp_no) > 15

SQL8 簡單 找出所有員工當前薪水salary情况

原題鏈接
在這裏插入圖片描述

SELECT distinct salary 
FROM salaries
order by salary DESC

SQL10 簡單 獲取所有非manager的員工emp_no

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

SELECT emp_no 
FROM employees
WHERE emp_no not in (select emp_no from dept_manager)

SQL11 中等 獲取所有員工當前的manager

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

select d1.emp_no, d2.emp_no manager
FROM dept_emp d1
join dept_manager d2
on d1.dept_no=d2.dept_no and d1.emp_no <> d2.emp_no

SQL12 困難 獲取每個部門中當前員工薪水最高的相關信息

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

with tmp as (
    SELECT d.dept_no, d.emp_no, s.salary
    FROM dept_emp d
    JOIN salaries s
    on d.emp_no = s.emp_no
), tmp2 as (
    select *, rank() over(PARTITION by dept_no order by salary desc, emp_no desc) rk
    FROM tmp
)
SELECT dept_no, emp_no, salary maxSalary
FROM tmp2
WHERE rk=1

SQL15 簡單 查找employees錶emp_no與last_name的員工信息

原題鏈接
在這裏插入圖片描述

SELECT * FROM employees
where emp_no % 2 = 1 and last_name <> 'Mary'
order by hire_date desc

SQL16 中等 統計出當前各個title類型對應的員工當前薪水對應的平均工資

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

select t.title title, avg(s.salary)
FROM titles t
join salaries s
on t.emp_no = s.emp_no
group by t.title
ORDER by avg(s.salary)

SQL17 簡單 獲取當前薪水第二多的員工的emp_no以及其對應的薪水salary

原題鏈接
在這裏插入圖片描述

select emp_no, salary
FROM salaries
where salary = (select DISTINCT salary FROM salaries order by salary desc LIMIT 1,1)

/* with tmp as ( SELECT emp_no, salary, rank() over(order by salary desc, emp_no desc) rk from salaries ) select emp_no, salary from tmp where rk = 2 */

SQL18 較難 獲取當前薪水第二多的員工的emp_no以及其對應的薪水salary

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

with tmp as (
    SELECT s.emp_no, s.salary, e.last_name, e.first_name
    FROM employees e
    join salaries s
    on e.emp_no = s.emp_no
)
select *
from tmp
where salary = (SELECT max(salary) FROM tmp where salary <> (select max(salary) FROM salaries))

SQL19 中等 查找所有員工的last_name和first_name以及對應的dept_name

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

select e.last_name, e.first_name, d.dept_name
from employees e
left join dept_emp on e.emp_no = dept_emp.emp_no
left join departments d on dept_emp.dept_no = d.dept_nojavascript:void(0);

SQL21 困難 查找在職員工自入職以來的薪水漲幅情况

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

with s1 as (
    select *
    FROM salaries
    group by emp_no
    having from_date = min(from_date)
),
s2 as (
    select *
    FROM salaries
    where to_date = '9999-01-01'
)
select distinct s1.emp_no, s2.salary - s1.salary growth
FROM s1, s2
where s1.emp_no=s2.emp_no and s2.to_date = '9999-01-01'
order by growth

SQL22 中等 統計各個部門的工資記錄數

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

WITH tmp as (
    select dept_emp.dept_no, departments.dept_name, salaries.emp_no
    from salaries
    left join dept_emp
    on salaries.emp_no = dept_emp.emp_no
    left join departments
    on dept_emp.dept_no = departments.dept_no
)
select dept_no, dept_name, count(emp_no) sum
FROM tmp
group by dept_no
order by dept_no

SQL23 較難 對所有員工的薪水按照salary降序進行1-N的排名

原題鏈接
在這裏插入圖片描述

select emp_no, salary, DENSE_RANK() over(order by salary desc) t_rank
FROM salaries

SQL24 較難 獲取所有非manager員工當前的薪水情况

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述
在這裏插入圖片描述

SELECT d1.dept_no, d1.emp_no, s.salary
FROM dept_emp d1
join salaries s
on d1.emp_no = s.emp_no
where d1.emp_no not in (select emp_no from dept_manager)

SQL25 困難 獲取員工其當前的薪水比其manager當前薪水還高的相關信息

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

SELECT d1.emp_no, d2.emp_no manager_no, s1.salary emp_salary, s2.salary manager_salary
FROM dept_emp d1
join dept_manager d2
on d1.dept_no = d2.dept_no and d1.emp_no <> d2.emp_no
join salaries s1
on s1.emp_no = d1.emp_no
join salaries s2
on s2.emp_no = d2.emp_no
where s1.salary > s2.salary

SQL26 困難 匯總各個部門當前員工的title類型的分配數目

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

SELECT d1.dept_no, d2.dept_name, t.title, count(t.emp_no) COUNT
from dept_emp d1
join departments d2
on d1.dept_no = d2.dept_no
join titles t
on d1.emp_no = t.emp_no
group by d1.dept_no, t.title
order by dept_no, title

SQL29 中等 使用join查詢方式找出沒有分類的電影id以及名稱

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

with tmp as (
    select f.film_id, f.title
    from film f
    join film_category fc
    on f.film_id = fc.film_id
    join category c
    on fc.category_id = c.category_id
)
select film_id, title
from film
where film_id not in (select film_id from tmp)

SQL30 中等 使用子查詢的方式找出屬於Action分類的所有電影對應的title,description

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

select f.title, f.description
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 c.name = 'Action'

SQL32 簡單 將employees錶的所有員工的last_name和first_name拼接起來作為Name

原題鏈接
在這裏插入圖片描述

/*主要就是考察concat函數 */
select CONCAT(last_name, ' ', first_name) NAME
FROM employees

SQL33 中等 創建一個actor錶,包含如下列信息

原題鏈接
在這裏插入圖片描述

CREATE TABLE actor(
actor_id smallint(5) primary key,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update date not null);

SQL38 中等 針對actor錶創建視圖actor_name_view

原題鏈接
在這裏插入圖片描述

CREATE VIEW actor_name_view AS
SELECT first_name first_name_v ,last_name last_name_v
FROM  actor;

SQL50 中等 將employees錶中的所有員工的last_name和first_name通過引號連接起來。

原題鏈接
在這裏插入圖片描述

select CONCAT(last_name, '\'', first_name) name
from employees

SQL51 中等 查找字符串 10,A,B 中逗號,出現的次數cnt

原題鏈接
在這裏插入圖片描述

select 2

/* select (length('10,A,B') - length(replace('10,A,B',',',''))) AS cnt */

SQL52 中等 獲取Employees中的first_name

原題鏈接
在這裏插入圖片描述

select first_name
FROM employees
ORDER BY RIGHT(first_name, 2)

SQL53 中等 按照dept_no進行匯總

原題鏈接
在這裏插入圖片描述

/* 本次重點是GROUP_CONCAT函數 語法:group_concat( [distinct] 要連接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] ) */
select dept_no, GROUP_CONCAT(emp_no) employees
FROM dept_emp
group by dept_no

SQL54 中等 平均工資

原題鏈接
在這裏插入圖片描述

select avg(salary) avg_salary
from salaries
where to_date='9999-01-01' 
    and (to_date, salary) not in (select to_date, max(salary) from salaries where to_date='9999-01-01')
    and (to_date, salary) not in (select to_date, min(salary) from salaries where to_date='9999-01-01')

SQL55 中等 分頁查詢employees錶,每5行一頁,返回第2頁的數據

原題鏈接
在這裏插入圖片描述

select * 
from employees
limit 5,5

SQL57 中等 使用含有關鍵字exists查找未分配具體部門的員工的所有信息。

原題鏈接
在這裏插入圖片描述

/* EXISTS語句:執行employees.length次 指定一個子查詢,檢測行的存在。遍曆循環外錶,然後看外錶中的記錄有沒有和內錶的數據一樣的。匹配上就將結果放入結果集中。 IN 語句:只執行一次 確定給定的值是否與子查詢或列錶中的值相匹配。 in在查詢的時候,首先查詢子查詢的錶,然後將內錶和外錶做一個笛卡爾積,然後按照條件進行篩選。 所以相對內錶比較小的時候,in的速度較快。 */
select *
from employees
where not EXISTS(
    select emp_no
    from dept_emp
    where dept_emp.emp_no = employees.emp_no
)

SQL59 較難 獲取有獎金的員工相關信息。

原題鏈接
在這裏插入圖片描述

with s as (
    select * 
    FROM salaries
    where to_date = '9999-01-01'
)
SELECT e1.emp_no, e2.first_name, e2.last_name, e1.btype, s.salary, 
    (case
         when  e1.btype=1 then s.salary * 0.1
         when  e1.btype=2 then s.salary * 0.2
         else s.salary * 0.3
     end
    ) bonus
FROM emp_bonus e1
JOIN employees e2
on e1.emp_no = e2.emp_no
join s
on e1.emp_no = s.emp_no

SQL60 較難 統計salary的累計和running_total

原題鏈接
在這裏插入圖片描述

with emp as (
    select * 
    FROM salaries
    where to_date = '9999-01-01'
), tmp as (
    select e1.emp_no, e1.salary, e2.salary s2
    FROM emp e1
    join emp e2
    on e1.emp_no >= e2.emp_no
)
select emp_no, salary, sum(s2) running_total
from tmp
group by emp_no

SQL61 較難 對於employees錶中,給出奇數行的first_name

原題鏈接
在這裏插入圖片描述

with tmp as (
    select first_name, ROW_NUMBER() over(order by first_name) rk
    from employees
)
select e.first_name

FROM employees e
join tmp 
on e.first_name = tmp.first_name
where rk % 2 = 1
/* 注意題目說“輸出的結果不需排序”,但是我們用了ROW_NUMBER(),所以已經排了序,需要重新連接原來的錶,保證順序不變 */

SQL65 較難 异常的郵件概率

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述
在這裏插入圖片描述

select date, round(sum(if(type='completed', 0, 1)) / count(type), 3) p
from email
where send_id in (select id from user where is_blacklist=0) and receive_id in (select id from user where is_blacklist=0)
group by date
order by date

SQL66 簡單 牛客每個人最近的登錄日期(一)

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

select user_id, max(date) d
from login
group by user_id
order by user_id

SQL67 較難 牛客每個人最近的登錄日期(二)

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述
在這裏插入圖片描述

select u.name u_n, c.name c_n, l.date
from login l
join user u
on l.user_id = u.id
join client c
on l.client_id = c.id
where (l.user_id, l.date) in (select user_id, max(date) FROM login group by user_id)
ORDER by u.name

SQL68 較難 牛客每個人最近的登錄日期(三)

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

with tmp as (
    select user_id, min(date) date
    from login
    group by user_id
)
select round((select count(user_id) from tmp where (user_id, date+1) in (select user_id, date from login)) / (select count(user_id) from tmp), 3) 

SQL69 較難 牛客每個人最近的登錄日期(四)

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

select date, sum(if((user_id, date) in (select user_id, min(date) from login group by user_id), 1, 0)) new
from login
group by date
order by date

SQL70 困難 牛客每個人最近的登錄日期(五)

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

select date, ifnull(round(sum(if((user_id, date+1) in (select user_id, date from login group by user_id), 1, 0))  / sum(if((user_id, date) in (select user_id, min(date) from login group by user_id), 1, 0)), 3), 0) p
from login
group by date
order by date

SQL71 較難 牛客每個人最近的登錄日期(六)

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述
在這裏插入圖片描述

with tmp as (
    select p1.user_id, p1.date, p2.number
    from passing_number p1, passing_number p2
    where p1.user_id = p2.user_id and p1.date >= p2.date
), tmp2 as (
    select user_id, date, sum(number) ps_number
    from tmp
    group by user_id, date
)
select u.name u_n, date, ps_number
from tmp2 t
join user u
on t.user_id = u.id
order by date, name 

SQL87 中等 最差是第幾名(一)

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

with tmp as (
    select c1.grade, c2.number
    from class_grade c1
    join class_grade c2
    on c1.grade >= c2.grade
)
select grade, sum(number) t_rank
FROM tmp
group by grade
order by grade

SQL88 較難 最差是第幾名(二)

原題鏈接
在這裏插入圖片描述

select grade
from
    (select grade,
    (select sum(number) from class_grade) as total,
    sum(number)over(order by grade) a, -- 求正序
    sum(number)over(order by grade desc) b  -- 求逆序
     from class_grade
    order by grade)t
where a >= total/2 and b >= total/2  -- 正序逆序均大於整個數列數字個數的一半
order by grade;

SQL89 中等 獲得積分最多的人(一)

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

with tmp as (
    select user_id, sum(grade_num) grade_sum
    FROM grade_info
    group by user_id
)
select u.name, t.grade_sum
from user u
join tmp t
on u.id = t.user_id
order by grade_sum DESC
limit 1

SQL90 較難 獲得積分最多的人(二)

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

with tmp as (
    select user_id, sum(grade_num) grade_sum
    FROM grade_info
    group by user_id
)
select id, name, grade_sum
FROM
(
    select u.id, u.name, t.grade_sum, rank() over(order by grade_sum desc) rk
    from user u
    join tmp t
    on u.id = t.user_id
) t
where rk = 1

SQL91 困難 獲得積分最多的人(三)

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

with tmp as (
    select user_id, sum(if(type='add', grade_num, 0)) grade_add, sum(if(type='reduce', grade_num, 0)) grade_reduce
    FROM grade_info
    group by user_id
)
select id, name, grade_sum
FROM
(
    select u.id, u.name, (t.grade_add - t.grade_reduce) grade_sum, rank() over(order by (t.grade_add - t.grade_reduce) desc) rk
    from user u
    join tmp t
    on u.id = t.user_id
) t
where rk = 1

SQL92 中等 商品交易(網易校招筆試真題)

原題鏈接
在這裏插入圖片描述

SELECT g.id, g.name, g.weight, sum(t.count) total
FROM goods g
JOIN trans t
on g.id = t.goods_id
where g.weight < 50
GROUP by t.goods_id
HAVING total > 20
order by g.id

SQL93 較難 網易雲音樂推薦(網易校招筆試真題)

原題鏈接
在這裏插入圖片描述
在這裏插入圖片描述

with tmp as (
    select f.user_id, music_name, music_id
    from follow f
    join music_likes m
    on f.follower_id = m.user_id
    join music m2
    on m2.id = m.music_id
)
select distinct music_name
FROM tmp
where user_id = 1 and music_id not in (select music_id from music_likes where user_id = 1)
order by music_id

結束語

感謝收看,祝學業和工作進步!


推薦關注的專欄

機器學習:分享機器學習實戰項目和常用模型講解
數據分析:分享數據分析實戰項目和常用技能整理


CSDN@報告,今天也有好好學習

版权声明
本文为[報告,今天也有好好學習]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/10/20211014004931535U.html

Scroll to Top