虽然SQL并不是数据类工作中最吸引人的部分,但对它有深刻理解并能熟练使用还是很有必要的,因为它是所有数据岗位成功的必经之路。

实际上,除了SELECT FROM、 WHERE、 GROUP BY、ORDER BY之外,SQL还有很多其他功能。也就是说,你知道的函数越多,就越容易操作和查询你想要的任何东西。

在这篇文章中,我们主要来学习和探讨以下两点:

  • 1.讲解除基础原理以外的 SQL函数相关知识
  • 2.学习几个SQL面试的练习题
Photo by Markus Spiske on Unsplash

问题1:查询第二高的薪水

编写一个SQL查询,从Employee表中找到第二高的薪水。比如,对于下面的Employee表,查询应该返回200作为第二高薪水。但是如果表里没有第二高薪水,那么返回结果应为null。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

解决方案 A:使用 IFNULL, OFFSET

  • IFNULL(expression, alt) : ifnull() 的结果为空就返回你指定的值;若不是,就返回预期的值。所以如果没有第二高薪水,它就会返回结果为null。
  • OFFSET : offset 与 ORDER BY 一起用,可以跳过你之前指定的前n行。这个功能很有用,因为你想要的结果就是第二行(第二高的薪水)
SELECT
    IFNULL(
        (SELECT DISTINCT Salary
        FROM Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1
        ), null) as SecondHighestSalary
FROM Employee
LIMIT 1

解决方案 B:使用 MAX()

下面的MAX查询找到的是除开最高薪水以外的最高薪水,所以它其实筛选的就是第二高薪水!

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary != (SELECT MAX(salary) FROM Employee)

问题2:查找重复的电子邮件地址

编写一个SQL查询,来查找 Person 表中的所有重复的电子邮件。

+----+---------+
| Id | Email   |
+----+---------+
| 1  | [email protected] |
| 2  | [email protected] |
| 3  | [email protected] |
+----+---------+

解决方案 A:子查询中使用 COUNT()

首先,创建一个子查询,显示每个电子邮件地址出现的频率。然后过滤掉计数大于1的情况。

SELECT Email
FROM (
    SELECT Email, count(Email) AS count
    FROM Person
    GROUP BY Email) as email_count
WHERE count > 1

解决方案 B:HAVING子句

HAVING本质上可以让你将WHERE和GROUP BY结合起来使用。

SELECT Email
FROM Person
GROUP BY Email
HAVING count(Email) > 1

问题3:查找气温升高的组

给定一个天气表格,编写一个 SQL 查询来查找与之前(昨天)相比温度更高的所有日期ID。

+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
|     1 |     2015-01-01 |          10 |
|     2 |     2015-01-02 |          25 |
|     3 |     2015-01-03 |           20 |
|     4 |     2015-01-04 |          30 |
+---------+------------------+------------------+

解决方案:DATEDIFF()

DATEDIFF 会计算两个日期之间的气温差异,让我们比较今天和昨天的气温变化。

简单地说,这个查询就是选出所有温度大于昨天温度的日期ID。

SELECT DISTINCT a.Id
FROM Weather a, Weather b
WHERE a.Temperature > b.Temperature
AND DATEDIFF(a.Recorddate, b.Recorddate) = 1

问题4:部门里的最高工资

Employee表格中包含了一个公司的所有员工。而且每个员工都有自己的个人Id、薪资情况和一个部门ID。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1      |
| 2  | Jim   | 90000  | 1      |
| 3  | Henry  | 80000  | 2      |
| 4  | Sam   | 60000  | 2      |
| 5  | Max   | 90000  | 1      |
+----+-------+--------+--------------+

Department表格中包含了公司的所有部门。

+----+----------+
| Id | Name    |
+----+----------+
| 1  | IT     |
| 2  | Sales  |
+----+----------+

编写一个SQL查询,查找每个部门中工资最高的员工。你的结果应返回以下内容(行的顺序无关紧要)

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT       | Max    | 90000  |
| IT      | Jim    | 90000  |
| Sales    | Henry    | 80000  |
+------------+----------+--------+

解决方案:IN子句

  • IN可以让你在WHERE中使用多个OR子句。比如:WHERE country = ‘ Canada ‘or country = ‘ USA ‘,也可以用WHERE country IN (‘ Canada ‘,’ USA ‘)。
  • 本例中,我们要过滤Department表,只显示每个部门(部门ID)的最高工资。然后,通过department Id 、Salary和已过滤的Department表格join到一起。
SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    SalaryFROM Employee
INNER JOIN Department ON Employee.DepartmentId = Department.Id
WHERE (DepartmentId , Salary)
     IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId 
)

问题5:交换座位

Mary是一名中学老师,她有一个专门的表格,里面有学生的名字和他们对应的座位ID。ID都是连续递增的。最近Mary想给相邻的学生换座。

编写一个SQL来为Mary输出结果

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+

得出以下输出结果:

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+

注意:如果人数是奇数,就不需要交换最后一个人的座位。

解决方案:CASE WHEN语句

  • 可以把“CASE WHEN ,THEN”语句理解成编程中的IF语句。
  • 第一个WHEN语句是用来检查行数是否为奇数的,如果是,就不改动这个ID号。
  • 第二个 WHEN 语句给每个现有ID加1(也就是,1、3、5 变成了2、4、6)
  • 同理,第三个WHEN语句在每个ID基数上减1(也就是,2、4、6变成1、3、5)
SELECT
     CASE
         WHEN((SELECT MAX(id) FROM seat)%2 = 1) AND id = (SELECT MAX(id) FROM seat) THEN id
        WHEN id%2 = 1 THEN id + 1
        ELSE id - 1
    END AS id, student
FROM seat
ORDER BY id

以上就是全部内容这些!如有任何不清楚的地方,请在下方评论,我会竭尽所能解释清楚,感谢你的阅读!

原文作者:Terence Shin
翻译作者:Lea
美工编辑:过儿
校对审稿:Jiawei Tong
原文链接:https://medium.com/data-science-rush/data-science-books-you-should-start-reading-in-2021-73b40a76aad9

北美求职指北-E周报(9月第4周)

Sep 27, 2021
  • 美国对外国公民的旅行禁令将于11月解除
  • Google创纪录重金收购纽约办公楼
  • 美股餐饮业科技独角兽上市表现亮眼

大数据在金融行业的应用

Apr 23, 2021

如果你想从事金融方向的数据科学工作,这里有几个数据科学的知识,你可能需要知道。首先,由社交媒体所衍生出的非结构化大数据集合,使得越来越多的战略优势可以被实时获取。

大数据时代的金融科技领域

Jun 18, 2021

如果你想在投资银行找到一份科技岗位,你很有可能需要处理风险或者交易等技术内容,正因为银行销售、交易功能是公司中最看重的板块。同时它们受到法规遵从的严格约束。

Leave a Comment

Your email address will not be published. Required fields are marked *

Comment *