41 Essential SQL 面试问题 *

UNION 将两个结构兼容的表的内容合并为一个合并表. 两者的区别 UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.

值得注意的是，的性能 UNION ALL will typically be better than UNION, since UNION 要求服务器执行删除任何重复项的额外工作. So, 在确定不会有任何重复的情况下, 或者有副本不是问题的地方, use of UNION ALL 会因为性能原因而被推荐吗.

ansi标准SQL指定了五种类型 JOIN 条款如下:

• 内连接 (a.k.a. " simple join "):返回两个表中至少有一个匹配的所有行. 如果没有指定，这是默认的连接类型 JOIN 指定类型.

• LEFT JOIN (or 左外连接): Returns all rows from the left table, and the matched rows from the right table; i.e.，结果将包含 all 从左表中删除记录，即使 JOIN 条件在正确的表中找不到任何匹配的记录. 这意味着如果 ON 子句与正确表中的任何记录都不匹配 JOIN 仍然会返回一行的结果为记录在左表, 但是右表的每一列都为NULL.

• RIGHT JOIN (or 右外连接):返回右表中的所有行，以及左表中匹配的行. This is the exact opposite of a LEFT JOIN; i.e.，结果将包含 all 从右表中获取记录，即使 JOIN 条件在左表中找不到任何匹配的记录. 这意味着如果 ON 子句与左表中的任何记录都不匹配 JOIN 仍然会返回一行的结果为记录在正确的表, 但是左表中的每一列都为NULL.

• FULL JOIN (or 全外连接):返回在任意一个表中有匹配的所有行. 从概念上讲,一个 FULL JOIN 结合了两者的作用 LEFT JOIN and a RIGHT JOIN; i.e.，其结果集等价于执行a UNION 左外部查询和右外部查询结果的.

• CROSS JOIN:返回第一个表中的每一行与第二个表中的每一行组合在一起的所有记录(i.e.，返回连接表中行集的笛卡尔积). 请注意 CROSS JOIN can either be specified using the CROSS JOIN 语法(“显式连接表示法”)或(b)列出 FROM 用逗号分隔的子句，不使用 WHERE 子句提供连接标准(“隐式连接符号”).

令人惊讶的是，给定所提供的样例数据，该查询的结果将是一个空集. 这样做的原因如下:如果集合由 SQL NOT IN 条件包含 any 为空的值, 那么这里的外部查询将返回一个空集合, 即使有许多跑步者id与winner_id匹配 races table.

知道了这一点，可以避免这个问题的查询如下:

从winner_id不为空的比赛中查询winner_id。

注意，这是假设您在不修改默认值的情况下获得标准SQL行为 ANSI_NULLS setting.

查询的结果如下:

Idnum pageseq doctext
1      5        5
2      6        6
0 0 0

The EXISTS 子句中的上述查询是转移注意力. It will always 是真的 ID is not 的成员 dbo.docs. As such, it will refer to the envelope table comparing itself to itself!

The idnum value of NULL will not be set since the join of NULL 当尝试匹配任何值时，将不返回结果 envelope.

该查询将以“笛卡尔积”或“交叉连接”的形式生成50行。, 当省略' where '子句时，哪个是默认值.

在SQL Server, PostgreSQL和SQLite中，可以使用 except 关键词如下:

从test_a中选择*
except
Select * from test_b;

在Oracle中， minus 用关键字代替. 注意，如果有多个列, 说ID和Name, 该列应该在Oracle查询中明确声明: Select ID from test_a - Select ID from test_b

MySQL不支持 except function. 然而，有一个标准的SQL解决方案适用于所有上述引擎，包括MySQL:

select a.id
从test_a a
左连接test_b b在a上.id = b.id
where b.Id为空;

This can be done as follows:

SELECT TOP (1) Salary FROM
(
    从员工薪水排序中选择不同的最高(10)薪水
)按薪水排序

其工作原理如下:

首先, 从员工薪水排序中选择不同的最高(10)薪水 查询将选择表中排名前10位的受薪员工. 不过，这些薪水会列在 descending order. 这是第一个查询工作所必需的, 但是现在从列表中选择最上面的1会给你 highest 薪水不是 10日最高 salary.

因此，第二个查询将对中的10条记录重新排序 ascending 顺序(默认排序顺序)和 then 选择最高的记录(现在将是这10个工资中最低的).

Not all databases support the TOP keyword. 例如，MySQL和PostreSQL使用 LIMIT 关键字如下:

选择薪金
(
    根据薪资DESC上限10从员工订单中选择不同的薪资
)按薪水排序 LIMIT 1;

或者更简洁地说，在MySQL中可以是:

从员工订单中查询不同的薪水

And in PostgreSQL this can be:

从员工订单中选择不同的工资，按工资DESC限制1 OFFSET 9;

You can avoid duplicates using UNION ALL and still run much faster than 结合不同的 (这实际上与UNION相同)通过运行这样的查询:

SELECT * FROM mytable WHERE b=Y AND a!=X

关键是 AND a!=X part. This gives you the benefits of the UNION (a.k.a., 结合不同的)命令，同时避免了大部分性能损失.

SELECT
      u.user_id,
      username,
      training_id,
      training_date,
      count( user_training_id ) AS count
  FROM users u JOIN training_details t ON t.User_id = u.user_id
  分组.user_id,
           username,
           training_id,
           training_date
  HAVING count( user_training_id ) > 1
  ORDER BY training_date DESC;

User_id username training_id training_date计数
4莉萨·罗梅罗2015年8月2日00:00:00
4莉萨·罗梅罗2015年8月3日00:00:00
1无名氏2015年8月1日00:00:00
3爱丽丝·琼斯2015年8月2日00:00:00

执行计划基本上是一个路线图，它以图形或文本方式显示SQL服务器的查询优化器为存储过程或临时查询选择的数据检索方法. 执行计划对于帮助开发人员理解和分析查询或存储过程的性能特征非常有用, 因为计划用于执行查询或存储过程.

在许多SQL系统中，文本执行计划可以使用关键字获得，例如 EXPLAIN，并且通常也可以获得视觉表示. 在Microsoft SQL Server中, 查询分析器有一个名为“显示执行计划”的选项(位于查询下拉菜单上). If this option is turned on, 当查询运行时，它将在一个单独的窗口中显示查询执行计划.

ACID(原子性、一致性、隔离性、持久性) 一组保证数据库事务得到可靠处理的属性. They are defined as follows:

• Atomicity. 原子性要求每个事务是“全或无”:如果事务的一部分失败, the entire transaction fails, 数据库状态保持不变. 原子系统必须保证在每种情况下都具有原子性, 包括电力故障, errors, 和崩溃.
• 一致性. 一致性属性确保任何事务都将数据库从一种有效状态带到另一种有效状态. 根据所有定义的规则，写入数据库的任何数据都必须是有效的, 包括约束, cascades, triggers, and any combination thereof.
• Isolation. 隔离属性确保事务的并发执行将导致如果事务串行执行将获得的系统状态, i.e.，一个接一个. 提供隔离是并发控制的主要目标. 根据并发控制方法(i.e. 如果它使用严格的(而不是宽松的)序列化性), 不完整事务的影响甚至可能对另一个事务不可见.
• Durability. 持久性意味着一旦事务被提交, 它将一直如此, even in the event of power loss, crashes, or errors. 在关系数据库中, 例如, 一旦一组SQL语句执行, 结果需要永久存储(即使数据库随后立即崩溃)。. To defend against power loss, 事务(或其影响)必须记录在非易失性存储器中.

SELECT TOP 100 user_id FROM dbo.WHERE user_id % 2 = 1

Both the NVL (exp1 exp2) and NVL2(exp1, exp2, exp3) 函数检查值 exp1 看看它是否为空.

With the NVL (exp1 exp2) 功能,如果 exp1 is not 的值为空 exp1 返回; otherwise, the value of exp2 返回，但大小写与的数据类型相同 exp1.

With the NVL2(exp1, exp2, exp3) 功能,如果 exp1 is not null, then exp2 返回; otherwise, the value of exp3 返回.

选择所有 even number records from a table:

Select * from table where id % 2 = 0 

选择所有 odd number records from a table:

Select * from table where id % 2 != 0

The only 两者的区别 RANK() and DENSE_RANK () functions is in cases where there is a “tie”; i.e.，在一个集合中的多个值具有相同排名的情况下. 在这种情况下， RANK() 将为集合中的值分配非连续的“排名”(当存在平局时，将导致整数排名值之间的差距), whereas DENSE_RANK () 将为集合中的值分配连续的排名(因此在平局的情况下，整数排名值之间不会有差距).

For example, consider the set {25, 25, 50, 75, 75, 100}. 对于这样一个集合， RANK() 将返回 {1, 1, 3, 4, 4, 6} (注意，跳过了值2和5)，而 DENSE_RANK () 将返回 {1,1,2,3,3,4}.

When GROUP BY 是不使用的， WHERE and HAVING clauses are essentially equivalent.

然而,当 GROUP BY is used:

• The WHERE 子句用于从结果中筛选记录. 筛选在进行任何分组之前进行.
• The HAVING 子句用于从组(例如.e.，以检查聚合到组后的条件)。.

“Order by 2”只有在select语句中至少有两列被使用时才有效. 然而，在这个查询中，即使 Employee 表有2列, 查询只选择了1个列名, 因此，“Order by 2”将导致语句在执行上述sql查询时抛出错误.

This query 将返回 10 records as TRUNCATE was executed in the transaction. TRUNCATE does not itself keep a log but 开始事务 跟踪 TRUNCATE command.

1. 单行函数一次只处理一行. 多行函数一次处理多行数据.
2. The group by 子句组合在特定字段或任何字段组中具有相同值的所有记录.

SELECT col, case when upper(col) = lower(col) then 'Fizz' else 'Buzz' end as FizzBuzz from table;

当存储在数据库中时， varchar2 uses only the allocated space. E.g. 如果你有 varchar2 (1999) 在表中放入50个字节，它将使用52个字节.

But when stored in a database, char 总是使用最大长度并填充空白. E.g. 如果你有 char(1999) 在表中放入50个字节，它将消耗2000个字节.

Declare @a nvarchar(100)='capone';
声明@length INT;
声明@i INT=1;
设置@lenght = LEN (@a)
while @i<=@length
BEGIN
print (substring (@a @i 1));
设置@i = @i + 1;
END

在Oracle SQL中，可以这样做:

SELECT SUBSTR('CAPONE', LEVEL, 1)
FROM DUAL CONNECT BY LEVEL <= LENGTH('CAPONE');

是的，像这样:

SET IDENTITY_INSERT TABLE1 ON

INSERT INTO TABLE1 (ID,NAME)
SELECT ID,NAME FROM TEMPTB1

将identity_insert设置为关闭

5
10
15

SELECT * FROM表
WHERE 'Yellow' IN (C1, C2, C3)

更新表设置col2 = case，当col1 = 1，则0，否则1结束

或者如果类型是数字:

update table set col2 = 1 - col1

In MySQL:

从表中按id desc limit 1顺序选择id

在SQL Server:

按id desc顺序从表中选择top 1 id

IN:

• 在列表结果集上工作
• 不适用于产生多列虚拟表的子查询
• 比较结果列表中的每个值
• 对于较大的子查询结果集，性能相对较慢

EXISTS:

• 在虚拟表上工作
• Is used with co-related queries
• Exits comparison when match is found
• 对于较大的子查询结果集，性能相对较快

是的，这是可能的，使用DBCC命令:

创建表
(id int identity;
 名字nvarchar (50)
)
insert into tableA values ('ram')
insert into tableA values ('rahim')
insert into tableA values ('roja')
insert into tableA values ('rahman')
insert into tableA values ('rani')
insert into tableA values ('raja')
insert into tableA values ('raga')
select * From tableA
DBCC CHECKIDENT(tableA,RESEED,9)
insert into tableA values ('roli')
insert into tableA values ('rosy')
insert into tableA values ('raka')
insert into tableA values ('rahul')
insert into tableA values ('rihan')
insert into tableA values ('bala')
insert into tableA values ('gala')

声明@N int
set @N = 5;
以cte为例
(
    查询姓名，薪水，工作编号, RN = ROW_NUMBER() 
	OVER (ORDER BY Salary DESC)
    从员工
)
查询姓名，薪水，工作编号
FROM CTE
其中rn = @n

select sum(case when x>0 then x else 0 end)sum_pos,sum(case when x<0 then x else 0 end)sum_neg from a;

选择weight, trunc(weight)为kg, nvl(substr(weight - trunc(weight)， 2)， 0)为GMS
从mass_table;

select b.emp_id为“Manager_Id”，
          b.emp_name为“Manager”， 
          avg(a.作为“Average_Salary_Under_Manager”
从雇员a， 
     Employee b
where a.Manager_id = b.emp_id
group by b.emp_id, b.emp_name
order by b.emp_id;

INSERT INTO table2 (column1, column2, column3) ...)
SELECT column1, column2, column3, ...
从表1
条件;

4. SELECT name FROM customer WHERE state IN ('VA');

虽然有4个客户不是Jane Smith推荐的(包括Jane Smith自己), 查询将只返回一个:帕特理查兹. 没有人引用的所有客户(因此在ReferredBy列中为NULL)都不会出现. 但这些顾客肯定不是简·史密斯介绍的, and certainly NULL is not equal to 2, 那为什么他们没有出现呢?

SQL Server uses three-valued logic, 对于习惯于大多数编程语言使用的更令人满意的两值逻辑(TRUE或FALSE)的程序员来说，这可能会很麻烦. 在大多数语言中, if you were presented with two predicates: ReferredBy = 2 and ReferredBy <> 2, 你会期望其中一个为真，另一个为假, given the same value of ReferredBy. 在SQL Server中, however, 如果ReferredBy为NULL, 它们都不是真的，也都不是假的. 任何与NULL比较的值都是三值逻辑中的第三个值:UNKNOWN.

该查询应该以以下两种方式之一编写:

SELECT Name 从客户s WHERE ReferredBy IS NULL OR ReferredBy <> 2

…or:

SELECT Name 从客户s WHERE ISNULL(ReferredBy, 0) <> 2; -- (Or COALESCE() )

Watch out for the following, though!

SELECT Name 从客户s WHERE ReferredBy = NULL OR ReferredBy <> 2

这将返回与原来相同的故障集. Why? 我们已经介绍了这一点:任何与NULL比较的结果都是三值逻辑中的第三个值:UNKNOWN. That “anything” includes NULL itself! 这就是为什么SQL Server提供IS NULL和IS NOT NULL操作符来专门检查NULL的原因. 那些特定的运算符总是求值为真或假.

即使应聘者没有大量的SQL Server经验, 一般来说，深入研究复杂的三值逻辑可以很好地表明他们是否有能力快速学习它，或者他们是否会为此而挣扎.

This can be done as follows:

更新TBL设置Nmbr = case，当Nmbr = 0时，则Nmbr+2，否则Nmbr+3结束;

SELECT CustomerName+ '; ' 
从客户
对于XML PATH(")

这是接近的，但会有一个不希望的落后 ;. One way of fixing that could be:

SELECT top 1
LTRIM(((选择的东西 '; ' + c1.从客户c1获取客户名'')), 1, 1,''))作为SSV
从客户c2;

在PostgreSQL中也可以使用这种语法来获得完全正确的结果:

SELECT array_to_string(array_agg(CustomerName), '; '::text) 
从客户

SELECT Employee order by salary (

这将给出Employee表中第三高的工资. 据此，我们可以用 限制(n - 1), 1.

但是MS SQL Server不支持这种语法，所以在这种情况下:

根据工资DESC从员工订单中选择工资
偏移2行
只获取下一行

OFFSET’s parameter corresponds to the (N-1) above.

1. duplicate records with one field

    SELECT name, COUNT(电子邮件) 
    FROM users
    通过电子邮件分组
    HAVING COUNT(email) > 1 
   

2. 具有多个字段的重复记录

    SELECT name, email, COUNT(*)
    FROM users
    组的名字，电子邮件
    HAVING COUNT(*) > 1
   

SELECT i.Id, i.BillingDate c.Name, r.名称为ReferredByName
发票i
 加入客户c ON i.CustomerId = c.Id
 LEFT JOIN Customers r ON c.ReferredBy = r.Id
按i排序.BillingDate;

这个问题只是测试应试者接受简单英语要求并编写相应SQL查询的能力. 这篇文章没有什么棘手的，它只涵盖了基本的内容:

• 当为引用的客户名连接客户表时，候选对象是否记得使用LEFT JOIN而不是inner JOIN? 如果没有，任何客户的发票都将被完全遗漏.

• 候选表在JOIN中别名了吗? 大多数有经验的T-SQL程序员都会这样做, 因为每次需要引用时重复完整的表名很快就会变得乏味. 在这种情况下, 如果至少Customer表没有别名，查询实际上就会中断, 因为它在不同的上下文中被引用了两次(一次作为包含已开具发票的客户姓名的表), 一次作为表，其中包含引用客户的名称).

• 候选人是否在SELECT中消除了Id和Name列的歧义? Again, 这是大多数有经验的程序员会自动做的事情, 是否会有冲突. And again, 在这种情况下，就会有冲突, 因此，如果候选人忽略这样做，查询将中断.

注意，此查询不会返回没有关联Customer的发票. 在大多数情况下，这可能是正确的行为.g., 保证每个发票都与一个客户相关联, 或不匹配的发票不感兴趣). However, 以保证所有发票无论如何都能被退回, 发票表应该使用LEFT JOIN与客户连接:

SELECT i.Id, i.BillingDate c.Name, r.名称为ReferredByName
发票i
 LEFT 加入客户c ON i.CustomerId = c.Id
 LEFT JOIN Customers r ON c.ReferredBy = r.Id
按i排序.BillingDate;