25 道谷歌 SQL 面试题与答案解析（下）-天涯论坛

nykek5i 发表于 2024-9-3 18:45:24

25 道谷歌 SQL 面试题与答案解析（下）

<img src="https://mmbiz.qpic.cn/mmbiz_gif/bL2iaicTYdZn7gtxSFZlfuCW6AdQib8Q1onbR0U2h9icP1eRO6wH0AcyJmqZ7USD0uOYncCYIH7ZEE8IicAOPxyb9IA/640?wx_fmt=gif&tp=webp&wxfrom=5&wx_lazy=1" style="width: 50%; margin-bottom: 20px;">
<h2 style="color: black; text-align: left; margin-bottom: 10px;">13、解释一下 SQL 中区别类型的连接</h2>
JOIN是 SQL 的子句，按照表之间一起的列，能够连接两个或多个表，用于合并表及检索数据。最平常的连接类型如下。
内连接（Inner Join） ：内连接是最平常的，用于返回两个或多个表中满足 JOIN 要求的所有行。
左连接（Left Join）：这种连接在右表满足 JOIN 要求时返回左表的行。
右连接（Right Join） ：类似于左连接，但在左表满足 JOIN 要求时返回右表的行。
全连接（Full Join） ：全连接在任何一个表中有匹配时返回左表和右表的所有行。
<h2 style="color: black; text-align: left; margin-bottom: 10px;">14、什么是主键（PRIMARY KEY）？</h2>
主键是独一标识每条记录的约束要求。主键不可有 NULL 值，并且所有值必须独一。
一个表只能有一个主键，但主键能够由单个或多个列构成。
<h2 style="color: black; text-align: left; margin-bottom: 10px;">15、什么是约束（constraints）？</h2>
SQL 中的约束是能够应用于表中特定数据类型的规则，用于限制特定列中的数据类型。SQL 中平常的约束类型如下。
NOT NULL - 禁止列中存储空值。
UNIQUE- 规定列中的值必须独一。主键运用 UNIQUE 约束。
PRIMARY KEY - 指定哪个字段是主键。
FOREIGN KEY - 独一标识另一个表中的一行。
<h2 style="color: black; text-align: left; margin-bottom: 10px;">16、SQL 中的 DELETE 和 TRUNCATE 语句有什么区别？</h2>
DELETE 用于从表中删除特定数据。该语句是 DML 命令，比 TRUNCATE 慢。
TRUNCATE 是 DDL命令，用于删除表中的所有行。
两者的重点区别是运用 DELETE 后能够回滚数据。
<h2 style="color: black; text-align: left; margin-bottom: 10px;">17、什么是查找优化？</h2>
低效的 SQL 查找会耗尽数据库资源，引起性能下降和服务中断。
查找优化是使 SQL 查找更有效的过程。
查找的效率越高，输出的速度就越快，并最大限度地减少对数据库的影响。
<h2 style="color: black; text-align: left; margin-bottom: 10px;">18、给定以下表格，选取最少有 10 名员工的前三个分部，并按照其员工中年薪超过 10 万美元的百分比进行排名。</h2>
employees 表
<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/hTYhvCJkfDfQs5OnB7zxd4s9z7FdiaDJ9FW1oj3GK7R5ntJOWd2zjvyOkdAyd0Wbg8iaGnqlzfCuToQHOKDuKBicA/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
departments 表
<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/hTYhvCJkfDfQs5OnB7zxd4s9z7FdiaDJ96dk3jBbN8eL6QxtNkVEiatF6Oy1GicDbeAJuffYF1w5lic3spJiclvibCEg/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
输出结果
<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/hTYhvCJkfDfQs5OnB7zxd4s9z7FdiaDJ9rRPqaHgnvgekKu03qqgaI1YgGMricIxYsRmnVOrESJZib8RpYCsgAicMQ/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
提示 
将其分解成区别的要求子句。
前 3 个分部
年薪超过 10 万美元的员工百分比
分部最少有 10 名员工
答案
首要，运用 JOIN 连接 employees 和 departments 表。以获取每一个员工的工资和分部。1SELECT
 *2FROM departments AS
 d3LEFT JOIN employees AS
 e4    ON
 d.id = e.department_id
而后，运用 GROUP BY子句对分部进行聚合，并运用 HAVING 子句过滤掉员工少于 10 人的分部。1SELECT2
    d.name3FROM departments AS
 d4LEFT JOIN employees AS
 e5    ON
 d.id = e.department_id6GROUP BY
 d.name7HAVING COUNT(*) >= 10
为何运用 HAVING 子句而不是 WHERE 子句？
这是由于 HAVING 能够在 GROUP BY 之后应用过滤，不必将原始查找包装在子查找中。
最后，运用 CASE WHEN 子句和AVG 函数计算百分比。1SELECT AVG(CASE WHEN salary > 1000002        THEN 1 ELSE 0 END) AS
percentage_over_100k
AVG() 函数将表达式的每一个值相加，再将其除以值的总数。
CASE WHEN 的表达式针对薪水超过 10 万美元的员工返回 1，针对其他员工返回 0。
完整代码如下。 1SELECT AVG(CASE WHEN salary > 100000 2        THEN 1 ELSE 0 END) AS
percentage_over_100k 3      , d.name as
 department_name 4      , COUNT(*) AS
 number_of_employees 5FROM departments AS
 d 6LEFT JOIN employees AS
 e 7    ON
 d.id = e.department_id 8GROUP BY
 d.name 9HAVING COUNT(*) >= 1010ORDER BY 1 DESC11LIMIT 3
<h2 style="color: black; text-align: left; margin-bottom: 10px;">19、给定一个用户表，编写 SQL 查找，获取每日新增用户的累计数，每月重新起始计数。</h2>
users 表
<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/hTYhvCJkfDfQs5OnB7zxd4s9z7FdiaDJ9aHfd8Xbq2GDxsqulnoTZSEG3RS1lMCeodBtiamdXxiayffPgQNq1zAFw/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
输出：
<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/hTYhvCJkfDfQs5OnB7zxd4s9z7FdiaDJ9DhOgNhnKHsK7G0gcabicyxtfWuLt3goYBbxH0b2q7rqb6IbZlfEd8ibQ/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
提示 
这个问题乍一看似乎能够经过 COUNT(*)，并按日期分组处理。或运用累积分布函数亦能够处理。
但实质上，要按特定的月日间隔进行分组，在月底将用户的统计数据重置为 0。
这种方式的优良在于能够得到留存率表，用于比较每一个月的累积用户数。
答案 1
WITH daily_total AS ( 2    SELECT 3        DATE(created_at) AS
 dt  4       , COUNT(*) AS
 cnt 5    FROM users 6    GROUP BY 1 7
) 8 9SELECT10t.dtAS date11    , SUM(u.cnt) AS
 monthly_cumulative12FROM daily_total AS
 t13LEFT JOIN daily_total AS
 u14    ON
 t.dt >= u.dt15        AND MONTH(t.dt) = MONTH
(u.dt)16        AND YEAR(t.dt) = YEAR
(u.dt)17GROUP BY 1
<h2 style="color: black; text-align: left; margin-bottom: 10px;">20、给定一个包括用户订阅起始和结束日期的表，查找每一个用户的订阅日期范围是不是与其他用户重叠</h2>
subscriptions 表
<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/hTYhvCJkfDfQs5OnB7zxd4s9z7FdiaDJ95cSibD3NSiaguXu0TBVAticDRkQPSSTqEXnSicX5EKUYYECb25S45K4QIw/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
示例
<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/hTYhvCJkfDfQs5OnB7zxd4s9z7FdiaDJ9L8nhkToyfDCl2a2lBGK0pk222jvic9r2Mp2ethPEtiaNd1K4Gwia163Ag/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
输出结果
<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/hTYhvCJkfDfQs5OnB7zxd4s9z7FdiaDJ9MKN8mIx3Y5SCRyKiaWFLqr77SicOGgXTjSiaZuxicro29zTdwVnPial7VNA/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
提示 
将其视为自连接。要连接 user_id 和 start_date 字段，但要重视不要让用户与自己匹配。
答案 1SELECT2
s1.user_id3    , MAX(CASE WHEN s2.user_id IS NOT NULL THEN 1 ELSE 0 END) AS
 overlap4FROM subscriptions AS
 s15LEFT JOIN subscriptions AS
 s26    ON
 s1.user_id != s2.user_id7        AND
s1.start_date <= s2.end_date8        AND
 s1.end_date >= s2.start_date9GROUP BY 1
<h3 style="color: black; text-align: left; margin-bottom: 10px;">21、给定学生及其 SAT 考试成绩的表格，查找测试成绩最接近的两个学生及其成绩差</h3>
倘若有多个学生的最小成绩差相同，选取字母次序靠前的学生组合。
scores 表
<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/hTYhvCJkfDfQs5OnB7zxd4s9z7FdiaDJ9LpficrgQMZfSic3hSlgBCI8w2ZjxtUCJosWicTXb45XUfwyCaqrian5GsA/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
输入
<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/hTYhvCJkfDfQs5OnB7zxd4s9z7FdiaDJ9JDfIV5jRvibLxI2UyWnh4CCEylDhg5dTMH4d1jjahXZZqPNVDbhFgVw/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
输出结果
<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/hTYhvCJkfDfQs5OnB7zxd4s9z7FdiaDJ9Om0vMXKdD6xic5Cmib8E9VqsDhyDrel0zEwHiar9eHUTajd1gEqOyGIvA/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
提示
给定的问题引用了一个仅包括两列的表格，此时，必须对同一个表进行自引用，亦便是将之视为包括相同值的两个区别表格。
答案 1
WITH ScoreDifferences AS ( 2    SELECT 3        a.student AS
 one_student, 4        b.student AS
 other_student, 5        ABS(a.score - b.score)AS
 score_diff 6    FROM 7
        scores a 8    JOIN 9
        scores b10    ON11
        a.id < b.id12
)13SELECT14
    one_student,15
    other_student,16
    score_diff17FROM18
    ScoreDifferences19ORDER BY20    score_diff ASC
,21    one_student ASC
,22    other_student ASC23LIMIT 1
;
<h2 style="color: black; text-align: left; margin-bottom: 10px;">22、给定两个表，一个包括用户的基本信息及其所在社区，另一个包括社区信息。查找无用户的社区</h2>
users 表
<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/hTYhvCJkfDfQs5OnB7zxd4s9z7FdiaDJ9znIQOz1A3qrP4hfwic3CnuRfDOc9nibWBm7LNp8nOnxjCnNibpqc3HiaUA/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
neighborhoods 表
<img src="https://mmbiz.qpic.cn/sz_mmbiz_png/hTYhvCJkfDfQs5OnB7zxd4s9z7FdiaDJ9EOE1OKu12gBYZ7sHDma0mF8RnbZcgDy7h15nAua8HaubF77MC8Q5Cg/640?wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
输出
<img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;">
提示
要找到无用户居住的社区，即一个表中的数据在另一个表中不存在。
答案 1SELECT
 n.id, n.name2FROM
 neighborhoods n3LEFT JOIN users u ON
 n.id = u.neighborhood_id4WHERE u.id IS NULL
;
<h3 style="color: black; text-align: left; margin-bottom: 10px;">23、给定交易表和制品表，查找制品 ID、制品价格和制品价格高于所有平均交易价格的制品的平均交易价格</h3>
transactions 表
<img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;">
products 表
<img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;">
提示: 
需要找到所有交易的平均价格。
交易总价是商品价格X数量。
因此呢，需要编写子查找获取所有交易的平均值。
答案 1
WITH avg_transaction_price AS ( 2    SELECT AVG(p.price * t.quantity)AS
 avg_price 3    FROM transactions AS
 t 4    JOIN products AS p ON
 t.product_id = p.id 5
) 6SELECT 7    p.id AS
 product_id,  8    p.price AS
 product_price, 9    (SELECT AVG
(p.price * t.quantity)10     FROM transactions AS
 t11     WHERE
 t.product_id = p.id12    ) AS
 avg_transaction_price13FROM products AS
 p14WHERE p.price > (SELECT avg_price FROM
avg_transaction_price);
<h3 style="color: black; text-align: left; margin-bottom: 10px;">24、给定 `transactions` 和 `products` 两个表。假设 `transactions` 表包括超过十亿行用户购买商品的记录</h3>
查找同一用户购买的配对商品。例如，酒和开瓶器、薯片和啤酒等。
需求查找前 5 对配对商品及其名叫作。
重视 
为了满足测试用例，P1 应该是字母次序靠前的商品。
transactions 表
<img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;">
products 表
<img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;">
输出结果
<img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;">
提示
需要将其分成几个过程。
首要，要找到用户在同一时间购买两个或更加多制品的所有实例。
而后，利用 user_id 和 created_at 进行查找。
答案 1SELECT 2    LEAST(p1.name, p2.name) AS
 P1, 3    GREATEST(p1.name, p2.name) AS
 P2, 4    COUNT(*) AS count 5FROM 6
    transactions t1 7JOIN 8
    transactions t2 9    ON t1.user_id = t2.user_id AND t1.created_at = t2.created_at AND
t1.id < t2.id10JOIN
 products p111    ON
 t1.product_id = p1.id12JOIN
 products p213    ON
 t2.product_id = p2.id14GROUP BY15
    P1, P216ORDER BY17    count DESC18LIMIT 5
;
<h3 style="color: black; text-align: left; margin-bottom: 10px;">25、查找每一个用户每日播放的歌曲数量</h3>
给定 song_plays 表，记录用户播放歌曲的数据。
查找每一个用户每日播放的歌曲数量。
重视 
倘若某个用户在同一天播放了同一首歌两次，计数应该是两次。
答案首要，创建 song_plays 表。1CREATE TABLE
 song_plays (2    id INT PRIMARY KEY
,3    user_id INT
,4    song_id INT
,5
    played_at DATETIME6
);
而后，插进模拟数据。 1INSERT INTO song_plays (id, user_id, song_id, played_at) VALUES 2(1, 1, 101, 2024-07-01 10:00:00
), 3(2, 1, 102, 2024-07-01 11:00:00
), 4(3, 2, 101, 2024-07-01 12:00:00
), 5(4, 1, 101, 2024-07-01 12:30:00
), 6(5, 2, 103, 2024-07-02 09:00:00
), 7(6, 1, 104, 2024-07-02 10:00:00
), 8(7, 1,101, 2024-07-02 11:00:00
), 9(8, 2, 102, 2024-07-02 11:30:00
),10(9, 2, 101, 2024-07-02 12:00:00
);
最后，SQL 查找。 1SELECT 2
    user_id, 3    DATE(played_at) AS
 play_date, 4    COUNT(*)AS
 song_count 5FROM 6
    song_plays 7GROUP BY 8
    user_id, 9    DATE
(played_at)10ORDER BY11
    user_id,12
    play_date;
<img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;">

星☆雨 发表于 2024-9-25 14:50:55

外链论坛的成功举办，是与各位领导、同仁们的关怀和支持分不开的。在此，我谨代表公司向关心和支持论坛的各界人士表示最衷心的感谢！

4lqedz 发表于 2024-10-17 21:43:32

感谢您的精彩评论，为我带来了新的思考角度。

b1gc8v 发表于 2024-10-19 20:26:55

软文发布平台 http://www.fok120.com/

wrjc1hod 发表于 2024-10-25 19:33:08

外链发布论坛学习网络优化SEO。

页: [1]

天涯论坛's Archiver

25 道谷歌 SQL 面试题与答案解析（下）