牛客網SQL練習題（Mysql-8）

文章目錄

• 單表
• • SQL1
  • SQL 2
  • SQL 7：錯誤次數 1
  • SQL17
  • SQL 32：出錯次數 1
  • SQL 34：出錯次數 1
  • SQL 42：出錯次數 1
  • SQL43：出錯次數 1
  • SQL 45：出錯次數 1
  • SQL 62
  • SQL 66
  • SQL 72：出錯次數 1
  • SQL 77：出錯次數 1
  • SQL 84：出錯次數 1
• 兩個表
• • SQL 3
  • SQL 4
  • SQL 5
  • SQL 8：錯誤次數 1
  • SQL10：錯誤次數 1
  • SQL11：出錯次數 1
  • SQL15：出錯次數 1
  • SQL16
  • SQL 64
• 三個表
• • SQL19：出錯次數 1
  • SQL22 ！

單表

SQL1

題目：最大

答案1

SELECT *
FROM employees
ORDER BY hire_date DESC
LIMIT 1;
           

答案2：考慮到最晚雇傭不止一個員工

SELECT *
FROM employees
WHERE hire_date = (SELECT MAX(hire_date)
        			FROM employees);
           

SQL 2

題目：第三大

LIMIT m,n

SELECT *
FROM employees
WHERE hire_date = (SELECT hire_date
			        FROM employees
			        ORDER BY hire_date DESC
			        LIMIT 2, 1);
           

SQL 7：錯誤次數 1

題目：group by

• GROUP BY
• 聚集函數（count）不可用于WHERE語句中，隻能用在 HAVING 中。

答案

SELECT emp_no, count(emp_no) AS t
FROM salaries
GROUP BY emp_no HAVING t>15;
           

錯誤示例

SELECT emp_no, count(emp_no) AS t
FROM salaries
WHERE count(emp_no)>15;
           

SQL17

題目：第二大

考慮到第二高工資不止一個員工

SELECT emp_no, salary
FROM salaries
WHERE salary = (SELECT salary
                FROM salaries
                ORDER BY salary DESC
                LIMIT 1,1);
           

SQL 32：出錯次數 1

拼接

SELECT CONCAT(last_name,' ',first_name) AS Name
FROM employees;
           

SQL 34：出錯次數 1

題目：批量insert

INSERT INTO actor
VALUES (1,
        'PENELOPE',
        'GUINESS',
        '2006-02-15 12:34:33'),
        (2,
        'NICK',
        'WAHLBERG',
        '2006-02-15 12:34:33');
           

SQL 42：出錯次數 1

題目：delete, 子查詢

mysql 中，delete、update 資料中若出現 select子句，不能為同一張表

解決：将子語句作為 from 表再包裹一層，

select * from（子句） as t

，給表起别名

答案：

DELETE FROM titles_test
WHERE id NOT IN (SELECT *
                 FROM (SELECT MIN(id)
                FROM titles_test
                GROUP BY emp_no)AS t);		# 起别名
           

錯誤示例：

DELETE FROM titles_test
WHERE id NOT IN (SELECT MIN(id)
                FROM titles_test			# 不能與 delete 同一張表
                GROUP BY emp_no);

           

SQL43：出錯次數 1

update

UPDATE titles_test
SET to_date=NULL,
     from_date='2001-01-01'
WHERE to_date='9999-01-01';
           

SQL 45：出錯次數 1

改名

SQL 62

題目：group by

SELECT number
FROM grade
GROUP BY number HAVING COUNT(*)>=3;
           

SQL 66

題目：group by

SELECT user_id, MAX(date) AS d
FROM login
GROUP BY user_id
ORDER BY user_id;
           

SQL 72：出錯次數 1

題目：group by、保留小數點

處理函數 round()：保留小數幾位

SELECT job, ROUND(AVG(score),3) AS avg
FROM grade
GROUP BY job
ORDER BY avg DESC;
           

SQL 77：出錯次數 1

題目：比較時間

處理函數 datediff()：計算兩個date相差幾天

SELECT *
FROM order_info
WHERE DATEDIFF(date, '2025-10-15')>0            # date>'2025-10-15' 
AND status='completed'
AND product_name IN ('Java', 'Python', 'C++')
ORDER BY id;
           

SQL 84：出錯次數 1

題目：group by、時間

SELECT job, sum(num) AS cnt
FROM resume_info
WHERE YEAR(date)=2025
GROUP BY job
ORDER BY cnt DESC;
           

兩個表

SQL 3

題目：inner join

SELECT salaries.*, dept_no
FROM salaries INNER JOIN dept_manager
ON salaries.emp_no = dept_manager.emp_no
ORDER BY salaries.emp_no;
           

SQL 4

題目：inner join

聯合查詢

答案1

SELECT last_name, first_name, dept_no
FROM employees, dept_emp
WHERE dept_emp.emp_no = employees.emp_no;
           

答案2：與答案1等效

SELECT last_name, first_name, dept_no
FROM employees 
INNER JOIN dept_emp
ON dept_emp.emp_no = employees.emp_no;
           

SQL 5

題目：left join

SELECT last_name, first_name, dept_no
FROM employees LEFT JOIN dept_emp
ON employees.emp_no = dept_emp.emp_no;
           

SQL 8：錯誤次數 1

題目：distinct 和 group by

• GROUP BY 可代替 DISTINCT （答案1、2）
• WHERE 和 HAVING 的差別（答案3、答案4）

答案1：不建議用 DISTINCT ，效率低

SELECT DISTINCT salary
FROM salaries
ORDER BY salary DESC;
           

答案2：推薦

SELECT salary
FROM salaries
GROUP BY salary 
ORDER BY salary DESC;
           

SQL10：錯誤次數 1

題目：left join，差

• 多表查詢盡量用 JOIN ON，效率更高（答案1、2）
• LEFT JOIN … ON…WHERE …IS NULL

答案1

SELECT emp_no
FROM employees
WHERE emp_no NOT IN (SELECT emp_no
                    FROM dept_manager);
           

答案2

SELECT employees.emp_no
FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no
WHERE dept_no IS NULL;
           

SQL11：出錯次數 1

題目：兩個表有兩個列相關（兩個外鍵）, left join

SELECT t1.emp_no, t2.emp_no AS manager
FROM dept_emp AS t1 LEFT JOIN dept_manager AS t2
ON t1.dept_no = t2.dept_no
WHERE t1.emp_no != t2.emp_no;
           

SQL15：出錯次數 1

題目：奇數

• 過濾奇數：取餘為1

SELECT *
FROM employees
WHERE emp_no%2=1 AND last_name!='Mary'
ORDER BY hire_date DESC;
           

SQL16

題目：inner join

SELECT t.title, AVG(s.salary)
FROM titles AS t INNER JOIN salaries AS s
ON t.emp_no = s.emp_no
GROUP BY title
ORDER BY AVG(s.salary);
           

SQL 64

題目：left join

SELECT person.id, person.name, task.content
FROM person LEFT JOIN task
ON person.id = task.person_id
ORDER BY person.id;
           

三個表

SQL19：出錯次數 1

三個表的聯結

SELECT e.last_name, e.first_name, d2.dept_name
FROM (employees AS e LEFT JOIN dept_emp AS d1 
      ON e.emp_no = d1.emp_no)
LEFT JOIN departments AS d2
ON d1.dept_no = d2.dept_no;
           

SQL22 ！

SELECT d2.dept_no, d2.dept_name, COUNT(s.salary) AS sum
FROM (dept_emp AS d1 INNER JOIN salaries AS s
     ON d1.emp_no = s.emp_no) INNER JOIN departments AS d2
ON d1.dept_no = d2.dept_no
GROUP BY d2.dept_no
ORDER BY d2.dept_no;