50道MySQL经典题，挑战你的SQL技能（50道mysql经典题）

50道MySQL经典题，挑战你的SQL技能

MySQL是一种广泛使用的关系型数据库管理系统。无论在工作中还是在实际生活中，我们都可以看到MySQL的身影。MySQL凭借其高效性、稳定性、易用性等优点，成为了广大企业和个人所青睐的数据库管理系统。而学习MySQL除了掌握基础操作，还需要掌握一些基本的SQL知识。下面我们就来看看50道MySQL经典题，看看你的SQL技能水平如何。

1. 查询employees表中年龄最大的员工名字和年龄。

SELECT first_name, last_name, birth_date FROM employees WHERE birth_date = (SELECT MAX(birth_date) FROM employees);

2. 查询employees表中所有电子邮件地址的末尾部分（用于判断是否来自同一域）。

SELECT DISTINCT SUBSTRING_INDEX(eml, ‘@’, -1) AS domn FROM employees;

3. 查询employees表中的所有数据，并按照hire_date字段倒序排序。

SELECT * FROM employees ORDER BY hire_date DESC;

4. 查询salaries表中工资最高的10个员工的数据。

SELECT * FROM salaries ORDER BY salary DESC LIMIT 10;

5. 查询dept_emp表中每个部门的员工数量。

SELECT dept_no, COUNT(*) AS num_employees FROM dept_emp GROUP BY dept_no;

6. 查询employees表中从未被分配到部门的员工的数量。

SELECT COUNT(*) FROM employees e LEFT JOIN dept_emp de ON e.emp_no = de.emp_no WHERE de.emp_no IS NULL;

7. 查询titles表中每个职位的员工人数。

SELECT title, COUNT(*) AS num_employees FROM titles GROUP BY title;

8. 查询salaries表中每个员工的平均年薪。

SELECT emp_no, AVG(salary) AS avg_salary FROM salaries GROUP BY emp_no;

9. 查询employees表中姓氏为“Smith”的员工数量。

SELECT COUNT(*) FROM employees WHERE last_name = ‘Smith’;

10. 查询employees表中所有员工的性别和雇佣期限。

SELECT gender, DATEDIFF(to_date, from_date) AS employment_days FROM dept_emp de JOIN employees e ON de.emp_no = e.emp_no;

11. 查询titles表中每种职位的平均工资。

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

12. 查询每个部门的最高薪资、平均薪资和最低薪资。

SELECT dept_no, MAX(salary), AVG(salary), MIN(salary) FROM dept_emp de JOIN salaries s ON de.emp_no = s.emp_no GROUP BY dept_no;

13. 查询部门“d009”中工作最长的员工。

SELECT first_name, last_name, MAX(DATEDIFF(to_date, from_date)) AS employment_days FROM dept_emp de JOIN employees e ON de.emp_no = e.emp_no WHERE dept_no = ‘d009’;

14. 查询电影表（movies）中根据发行年份和年龄推断的用户人均评分（假设年龄从出生开始）。

SELECT release_year, ROUND(AVG(rating), 2) AS avg_rating, AVG(rating) AS exact_avg_rating FROM movies m JOIN ratings r ON m.movie_id = r.movie_id JOIN users u ON r.user_id = u.user_id GROUP BY release_year, FLOOR((YEAR(CURDATE()) – u.birthyear));

15. 查询actors表中出演电影最多的10个演员。

SELECT actor_name, COUNT(*) AS num_movies FROM movies_actors ma JOIN actors a ON ma.actor_id = a.actor_id GROUP BY actor_name ORDER BY num_movies DESC LIMIT 10;

16. 查询借阅书籍最多的用户的名字、地址和借阅数量。

SELECT name, address, COUNT(*) AS num_borrowed FROM users u JOIN borrowings b ON u.user_id = b.user_id GROUP BY u.user_id ORDER BY num_borrowed DESC LIMIT 1;

17. 查询各州拥有最多高等院校的城市。

SELECT state, city, COUNT(*) AS num_colleges FROM colleges GROUP BY state, city ORDER BY num_colleges DESC;

18. 查询销售表（sales）中月销售额最高的10个月份。

SELECT DATE_FORMAT(sale_date, ‘%Y-%m’) AS month, SUM(price) AS total_sales FROM sales GROUP BY month ORDER BY total_sales DESC LIMIT 10;

19. 查询订单表（orders）中用户最常购买的3个产品。

SELECT product, COUNT(*) AS num_orders FROM orders GROUP BY product ORDER BY num_orders DESC LIMIT 3;

20. 查询每个州最富有的1%居民的平均资产。

SELECT state, AVG(net_worth) AS avg_net_worth FROM (SELECT state, net_worth, NTILE(100) OVER (PARTITION BY state ORDER BY net_worth DESC) AS percentile FROM residents) t WHERE percentile = 1 GROUP BY state;

21. 查询employees表中受到最多直接上司领导指导的员工。

SELECT first_name, last_name, COUNT(*) AS num_direct_reports FROM employees e JOIN dept_manager d ON e.emp_no = d.emp_no GROUP BY e.emp_no ORDER BY num_direct_reports DESC LIMIT 1;

22. 查询学生表中出勤率最高的学生。

SELECT student_name, ROUND(SUM(attendance) / COUNT(*), 2) AS attendance_rate FROM attendance GROUP BY student_name ORDER BY attendance_rate DESC LIMIT 1;

23. 查询每个州最高的房产税。

SELECT state, MAX(property_tax) FROM property_tax GROUP BY state;

24. 查询最受欢迎的10个电子商务网站的访问量。

SELECT website, SUM(visits) AS total_visits FROM ecommerce GROUP BY website ORDER BY total_visits DESC LIMIT 10;

25. 查询每个月份的总销售额，按照月份顺序排列。

SELECT DATE_FORMAT(sale_date, ‘%Y-%m’) AS month, SUM(price) AS total_sales FROM sales GROUP BY month;

26. 查询每个项目参与的总人数和总课时数。

SELECT project_name, COUNT(*) AS num_participants, SUM(hours) AS total_hours FROM projects GROUP BY project_name;

27. 查询每个州的人口数量、财富和平均薪资。

SELECT state, SUM(population) AS total_population, SUM(net_worth) AS total_wealth, AVG(salary) AS avg_salary FROM residents GROUP BY state;

28. 查询顾客表中最常购买的产品类别。

SELECT category, COUNT(*) AS num_purchases FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY category ORDER BY num_purchases DESC LIMIT 1;

29. 查询最高满意度的用户、产品和星级。

SELECT customer_name, product, star_rating FROM satisfaction JOIN customers ON satisfaction.customer_id = customers.customer_id WHERE satisfaction.rating = (SELECT MAX(rating) FROM satisfaction);

30. 查询每个国家的最短路程航班。

SELECT departure_country, arrival_country, MIN(distance) AS shortest_distance FROM flights GROUP BY departure_country, arrival_country;

31. 查询每个月份的新用户和活跃用户数量。

SELECT DATE_FORMAT(created_at, ‘%Y-%m’) AS month, COUNT(*) AS num_new_users FROM users WHERE created_at >= ‘2020-01-01’ GROUP BY month;

32. 查询每个月份的销售收入、成本和利润。

SELECT DATE_FORMAT(sale_date, ‘%Y-%m’) AS month, SUM(revenue) AS total_revenue, SUM(cost) AS total_cost, SUM(profit) AS total_profit FROM sales GROUP BY month;

33. 查询每个州最古老和最新的成员的平均年龄。

SELECT state, AVG(YEAR(CURDATE()) – YEAR(birthdate)) AS avg_age FROM (SELECT state, birthdate, DENSE_RANK() OVER (PARTITION BY state ORDER BY birthdate ASC) AS oldest, DENSE_RANK() OVER (PARTITION BY state ORDER BY birthdate DESC) AS newest FROM members) t WHERE oldest = 1 OR newest = 1 GROUP BY state;

34. 查询每个州的最大、最小和平均人口数量。

SELECT state, MAX(population) AS max_population, MIN(population) AS min_population, AVG(population) AS avg_population FROM residents GROUP BY state;

35. 查询每个品牌的销售额和销售量。

SELECT brand, SUM(price * quantity) AS total_sales, SUM(quantity) AS total_units_sold FROM orders GROUP BY brand;

36. 查询最有可能成为诊断医生的员工。

SELECT first_name, last_name, COUNT(*) AS num_diagnoses FROM employees e JOIN consultations c ON