每个优秀的数据库/SQL 开发人员都应该能够回答的问题

Questions every good Database/SQL developer should be able to answer

提问by Rachel

I was going through Questions every good .Net developer should be able to answerand was highly impressed with the content and approach of this question and so in the same spirit, I am asking this question for Database/SQL Developer.

我正在研究每个优秀的 .Net 开发人员都应该能够回答的问题，并且对这个问题的内容和方法印象深刻，因此本着同样的精神，我为数据库/SQL 开发人员提出了这个问题。

What questionsdo you think should a good Database/SQL programmer be able to respond to?

您认为优秀的数据库/SQL 程序员应该能够回答哪些问题？

采纳答案by marc_s

The different types of JOINs:

不同类型的 JOIN：

• INNER JOIN
• LEFT and RIGHT OUTER JOIN
• FULL JOIN
• CROSS JOIN

• 内部联接
• 左外连接和右外连接
• 全面加入
• 交叉连接

See Jeff Atwood's Visual Explanation of JOINs

参见 Jeff Atwood对 JOIN的视觉解释

• What is a key? A candidate key? A primary key? An alternate key? A foreign key?

• What is an index and how does it help your database?

• What are the data types available and when to use which ones?

• 什么是钥匙？候选键？一个主键？备用钥匙？外键？

• 什么是索引，它如何帮助您的数据库？

• 有哪些可用的数据类型以及何时使用哪些类型？

回答by OMG Ponies

A reprint of my answer here, as general guidelines for topics.

在此处重印我的答案，作为主题的一般准则。

Basics

基本

---

---

1. SELECTing columns from a table
2. Aggregates Part 1: COUNT, SUM, MAX/MIN
3. Aggregates Part 2: DISTINCT, GROUP BY, HAVING

1. SELECT表中的列
2. 聚合第 1 部分：COUNT, SUM, MAX/MIN
3. 聚合第 2 部分：DISTINCT, GROUP BY,HAVING

Intermediate

中间的

---

---

1. JOINs, ANSI-89 and ANSI-92 syntax
2. UNIONvs UNION ALL
3. NULLhandling: COALESCE& Native NULL handling
4. Subqueries: IN, EXISTS, and inline views
5. Subqueries: Correlated
6. WITHsyntax: Subquery Factoring/CTE
7. Views

4. JOINs、ANSI-89 和 ANSI-92 语法
5. UNION对比 UNION ALL
6. NULL处理：COALESCE& 原生 NULL 处理
7. 子查询：IN，EXISTS，和内嵌的意见
8. 子查询：相关
9. WITH语法：子查询分解/CTE
10. 观看次数

Advanced Topics

高级主题

---

---

• Functions, Stored Procedures, Packages
• Pivoting data: CASE & PIVOT syntax
• Hierarchical Queries
• Cursors: Implicit and Explicit
• Triggers
• Dynamic SQL
• Materialized Views
• Query Optimization: Indexes
• Query Optimization: Explain Plans
• Query Optimization: Profiling
• Data Modelling: Normal Forms, 1 through 3
• Data Modelling: Primary & Foreign Keys
• Data Modelling: Table Constraints
• Data Modelling: Link/Corrollary Tables
• Full Text Searching
• XML
• Isolation Levels
• Entity Relationship Diagrams (ERDs), Logical and Physical
• Transactions: COMMIT, ROLLBACK, Error Handling

• 函数、存储过程、包
• 透视数据：CASE 和 PIVOT 语法
• 分层查询
• 游标：隐式和显式
• 触发器
• 动态 SQL
• 物化视图
• 查询优化：索引
• 查询优化：解释计划
• 查询优化：分析
• 数据建模：范式，1 到 3
• 数据建模：主键和外键
• 数据建模：表约束
• 数据建模：链接/关联表
• 全文检索
• XML
• 隔离级别
• 实体关系图 (ERD)，逻辑和物理
• 事务：COMMIT, ROLLBACK, 错误处理

回答by Phil Sandler

Here are a few:

以下是一些：

• What is normalization and why is it important?
• What are some situations where you would de-normalize data?
• What is a transaction and why is it important?
• What is referential integrity and why is it important?
• What steps would to take to investigate reports of slow database performance?

• 什么是规范化，为什么它很重要？
• 在哪些情况下您会非规范化数据？
• 什么是交易，为什么它很重要？
• 什么是参照完整性，为什么它很重要？
• 将采取哪些步骤来调查数据库性能缓慢的报告？

回答by Phil Sandler

What is sql injection and how do you prevent it?

什么是 sql 注入以及如何防止它？

What is a cursor and when would you use it (or not) and why?

什么是游标，什么时候使用（或不使用），为什么？

回答by Disillusioned

I've placed this answer because Erwin Smout posted a answerthat was so wrong it highlighted that there is probably a need to specifically guard against it.

我之所以放置这个答案，是因为 Erwin Smout 发布了一个错误的答案，它强调可能需要特别防范它。

Erwin suggested:

埃尔文建议：

"Why should every SELECT always include DISTINCT ?"

“为什么每个 SELECT 都应该包含 DISTINCT ？”

A more appropriate question would be: If someone were to make the claim that: "every SELECT always include DISTINCT"; how would you comment on the claim?

一个更合适的问题是：如果有人声称：“每个 SELECT 总是包含 DISTINCT”；你如何评论这项索赔？

If a candidate is unable to shoot the claim down in flames they either:

如果候选人无法在火焰中驳回索赔，他们要么：

• Don't understand the problem with the claim.
• Lack in critical thinking skills.
• Lack in ability to communicate technical issues.

• 不明白索赔的问题。
• 缺乏批判性思维能力。
• 缺乏沟通技术问题的能力。

For the record

作为记录

1. Suppose your query is correct, and does not return any duplicates, then including DISTINCT simply forces the RDBMS to check your result (zero benefit, and a lot of additional processing).
2. Suppose your query is incorrect, and doesreturn duplicates, then including DISTINCT simply hidesthe problem (again with additional processing). It would be better to spot the problem and fix your query... it'll run faster that way.

1. 假设您的查询是正确的，并且不返回任何重复项，那么包含 DISTINCT 只会强制 RDBMS 检查您的结果（零收益，以及许多额外的处理）。
2. 假设您的查询不正确，并且确实返回重复项，那么包含 DISTINCT 只会隐藏问题（再次进行额外处理）。最好发现问题并修复您的查询......这样它会运行得更快。

回答by HLGEM

I would give a badly written query and ask them how they would go about performance tuning it.

我会给出一个写得很糟糕的查询，并询问他们将如何进行性能调整。

I would ask about set theory. If you don't understand operating in sets, you can't effectively query a relational database.

我想问一下集合论。如果您不了解在集合中操作，则无法有效地查询关系数据库。

I would give them some cursor examples and ask how they would rewrite them to make them set-based.

我会给他们一些游标示例，并询问他们如何重写它们以使其基于集合。

If the job involved imports and exports I would ask questions about SSIS (or other tools involved in doing this used by other datbases). If it involved writing reports, I would want to know that they understand aggregates and grouping (As well as Crystal Reports or SSRS or whatever ereporting tool you use).

如果工作涉及导入和导出，我会询问有关 SSIS（或其他数据库使用的执行此操作的其他工具）的问题。如果涉及编写报告，我想知道他们了解聚合和分组（以及 Crystal Reports 或 SSRS 或您使用的任何电子报告工具）。

I would ask the difference in results between these three queries:

我会问这三个查询之间的结果差异：

select  a.field1
        , a.field2
        , b.field3
from table1 a
join table2 b
    on a.id = b.id
where a.field5 = 'test'
    and b.field3 = 1

select  a.field1
        , a.field2
        , b.field3
from table1 a
left join table2 b
    on a.id = b.id
where a.field5 = 'test'
    and b.field3 = 1

select  a.field1
        , a.field2
        , b.field3
from table1 a
left join table2 b
    on a.id = b.id and b.field3 = 1
where a.field5 = 'test'

回答by Randy Minder

At our company, instead of asking a lot of SQL questions that anyone with a good memory can answer, we created a SQL Developers test. The test is designed to have the candidate put together a solid schema with normalization and RI considerations, check constraints etc. And then be able to create some queries to produce results sets we're looking for. They create all this against a brief design specification we give them. They are allowed to do this at home, and take as much time as they need (within reason).

在我们公司，我们没有问很多记忆力好的人都能回答的 SQL 问题，而是创建了一个 SQL Developers 测试。该测试旨在让候选人将具有规范化和 RI 考虑因素、检查约束等的可靠模式放在一起。然后能够创建一些查询以生成我们正在寻找的结果集。他们根据我们提供的简短设计规范创建了所有这些。他们被允许在家中这样做，并根据需要花费尽可能多的时间（在合理范围内）。

回答by Jose Chama

What is the difference between a clustered index and a nonclustered index?

聚集索引和非聚集索引有什么区别？

Another question I would ask that is not for a specific server would be:

我要问的另一个不是针对特定服务器的问题是：

What is a deadlock?

什么是死锁？

回答by Hyman Marchetti

Knowing not to use, and WHY not to use:

知道不使用，以及为什么不使用：

SELECT *

回答by Jord?o

An interesting question would involve relational division, or how to express a "for all" relationship, which would require nested not existsclauses.

一个有趣的问题将涉及关系除法，或者如何表达“for all”关系，这需要嵌套not exists子句。

The question comes straigh from this link.

问题直接来自此链接。

Given the following tables, representing pilots that can fly planes and planes in a hangar:

给定下表，代表可以驾驶飞机和机库中的飞机的飞行员：

create table PilotSkills (
  pilot_name char(15) not null,
  plane_name char(15) not null
)

create table Hangar (
  plane_name char(15) not null
)

Select the names of the pilots who can fly every plane in the hangar.

选择可以驾驶机库中每架飞机的飞行员的名字。

The answer:

答案：

select distinct pilot_name
from PilotSkills as ps1 
where not exists (
  select * from hangar
  where not exists (
    select * from PilotSkills as ps2 where 
      ps1.pilot_name = ps2.pilot_name and 
      ps2.plane_name = hangar.plane_name
  )
)

Or ...

或者 ...

Select all stack overflow users that have accepted answers in questions tagged with the 10 most popular programming languages.

选择所有已接受以10 种最流行的编程语言标记的问题的答案的堆栈溢出用户。

The (possible) answer (assuming an Accepted_Answersview and a Target_Language_Tagstable with the desired tags):

（可能的）答案（假设一个Accepted_Answers视图和一个Target_Language_Tags带有所需标签的表格）：

select distinct u.user_name
from Users as u
join Accepted_Answers as a1 on u.user_id = a1.user_id
where not exists (
  select * from Target_Language_Tags t
  where not exists (
    select * 
      from Accepted_Answers as a2
      join Questions as q on a2.question_id = q.question_id
      join Question_Tags as qt on qt.question_id = q.question_id 
    where 
      qt.tag_name = t.tag_name and
      a1.user_id = a2.user_id
  )
)