问题
在某个时候,我们需要采访高级数据库开发人员,并检查他们的SQL查询知识。在这些情况下,重要的是要提出与他们在该领域的经验相对应的问题,以便能够评估他们的知识是否符合您的期望。此外,面试的时间通常很有限,因此选择一些涵盖该主题主要方面的高级(但不太复杂)问题至关重要。问题的定义应该明确,以便受访者可以专注于解决方案,而不是浪费时间了解要求。另外,我认为表的示例架构应该很简单,以免使候选者结构复杂。
在本文中,将为高级SQL查询面试定义问题,并提供答案。这对面试官以及开发人员提高自己的技能很有用。请注意,这不是完整的数据库面试问题,因为它仅测试查询技能。应该询问与存储过程,函数,触发器,事务等有关的其他问题。
解
在定义问题和提供答案之前,我们需要创建一个示例和简单的环境。假设我们有一个包含2个表“ Item”和“ Sales”的模式。第一个表是项目的描述,第二个表按日期显示了项目的销售额。架构图如下:
这些表的结构如下:
USE master GO CREATE DATABASE TestDB GO USE [TestDB] GO --Tables creation CREATE TABLE [dbo].[Item] ( [ItemID] [int] NOT NULL, [ItemCode] [nchar](5) NOT NULL, [ItemPrice] [money] NULL, CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED (ItemID) ) CREATE UNIQUE NONCLUSTERED INDEX [UIX_Item_ItemCode] ON [dbo].[Item]([ItemCode] ASC) CREATE TABLE [dbo].[Sales] ( [SalesID] [int] IDENTITY(1,1) NOT NULL, [ItemID] [int] NOT NULL, [SoldDate] [date] NOT NULL, CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED (SalesID) ) ALTER TABLE [dbo].[Sales] WITH CHECK ADD CONSTRAINT [FK_Sales_Item] FOREIGN KEY([ItemID]) REFERENCES [dbo].[Item] ([ItemID]) ALTER TABLE [dbo].[Sales] CHECK CONSTRAINT [FK_Sales_Item]
是示例数据。
-- insert rows into dbo.Item INSERT [dbo].[Item] ([ItemID], [ItemCode], [ItemPrice]) VALUES (1, N'A0010', 17455.2900) INSERT [dbo].[Item] ([ItemID], [ItemCode], [ItemPrice]) VALUES (2, N'B0020', 24500.0000) INSERT [dbo].[Item] ([ItemID], [ItemCode], [ItemPrice]) VALUES (3, N'C0030', 12450.3200) INSERT [dbo].[Item] ([ItemID], [ItemCode], [ItemPrice]) VALUES (4, N'D0040', 37784.0000) INSERT [dbo].[Item] ([ItemID], [ItemCode], [ItemPrice]) VALUES (5, N'E0050', 128000.0000) INSERT [dbo].[Item] ([ItemID], [ItemCode], [ItemPrice]) VALUES (6, N'F0060', 92000.0000) -- insert rows into dbo.Sales SET IDENTITY_INSERT [dbo].[Sales] ON INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (1, 1, CAST(N'2016-01-20' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (2, 1, CAST(N'2016-02-22' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (3, 3, CAST(N'2016-03-17' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (4, 5, CAST(N'2016-04-01' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (5, 3, CAST(N'2017-01-03' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (6, 1, CAST(N'2017-02-20' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (7, 2, CAST(N'2016-05-18' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (8, 1, CAST(N'2016-06-22' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (9, 1, CAST(N'2016-07-01' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (10, 4, CAST(N'2017-03-10' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (11, 4, CAST(N'2017-04-20' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (12, 1, CAST(N'2016-07-01' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (13, 3, CAST(N'2017-05-03' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (14, 1, CAST(N'2017-05-12' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (15, 1, CAST(N'2017-06-09' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (16, 4, CAST(N'2016-08-20' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (17, 3, CAST(N'2016-09-30' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (18, 2, CAST(N'2016-09-18' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (19, 4, CAST(N'2016-10-22' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (20, 2, CAST(N'2016-10-17' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (21, 6, CAST(N'2016-11-01' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (22, 3, CAST(N'2017-07-23' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (23, 1, CAST(N'2017-07-03' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (24, 1, CAST(N'2017-08-28' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (25, 1, CAST(N'2017-09-20' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (26, 2, CAST(N'2017-10-03' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (27, 2, CAST(N'2017-10-20' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (28, 3, CAST(N'2017-11-25' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (29, 1, CAST(N'2017-11-23' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (30, 2, CAST(N'2017-12-24' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (31, 1, CAST(N'2017-12-09' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (32, 3, CAST(N'2019-01-11' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (33, 1, CAST(N'2016-12-13' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (34, 1, CAST(N'2019-02-23' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (35, 4, CAST(N'2019-02-03' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (36, 4, CAST(N'2019-03-20' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (37, 2, CAST(N'2019-03-28' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (38, 3, CAST(N'2019-04-16' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (39, 4, CAST(N'2019-05-03' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (40, 6, CAST(N'2018-06-01' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (41, 3, CAST(N'2016-12-04' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (42, 3, CAST(N'2018-01-22' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (43, 1, CAST(N'2018-02-01' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (44, 2, CAST(N'2019-07-10' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (45, 3, CAST(N'2019-08-08' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (46, 3, CAST(N'2018-03-20' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (47, 3, CAST(N'2018-04-16' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (48, 4, CAST(N'2019-09-12' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (49, 4, CAST(N'2019-10-18' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (50, 3, CAST(N'2019-11-15' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (51, 2, CAST(N'2019-12-23' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (52, 4, CAST(N'2019-12-02' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (53, 4, CAST(N'2018-05-16' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (54, 3, CAST(N'2018-07-12' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (56, 2, CAST(N'2018-08-16' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (57, 1, CAST(N'2018-09-19' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (58, 1, CAST(N'2018-10-18' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (59, 2, CAST(N'2018-11-11' AS Date)) INSERT [dbo].[Sales] ([SalesID], [ItemID], [SoldDate]) VALUES (60, 1, CAST(N'2018-12-22' AS Date)) SET IDENTITY_INSERT [dbo].[Sales] OFF
问题
定义好方案后,我们准备提出问题了。
1.获取2019年未售出的商品清单(ItemCode,ItemPrice)。
结果:
2.获取仅在2016年出售的商品清单(ItemCode,ItemPrice)。
结果:
3.显示每年的销售总额(年,RunningTotalCountofSales)。
结果:
4.在每个季度的单独列(年度,FirstQuarter,SecondQuarter,ThirdQuarter,ForthQuarter)中获取2018年和2019年已售物品的季度计数。
结果:
5.显示每年的已售商品数量,以及上一年的已售商品数量和上一年的销售数量(年,SoldItemsCount,PreviousSoldItemsCount,增长率(%))的增长(百分比)。如果没有有关上一年销售额的信息,请显示0。
结果:
6.更新项目表,将价格第三高的项目的价格提高1000。更新后,项目表中的数据如下。
结果:
答案
以下是上述问题的答案。尽管每个任务可以有不同的解决方案,但每个问题只能提供一个答案。如果您有其他解决方案,请在本文结尾的注释中输入它们。
1. 2019年未售出的物品清单。
SELECT i.ItemCode, i.ItemPrice FROM dbo.Item i LEFT JOIN dbo.Sales s ON i.ItemID=s.ItemID AND YEAR(s.SoldDate)=2019 WHERE s.SoldDate IS NULL
2.仅在2016年出售的物品清单。
SELECT ItemCode, ItemPrice FROM dbo.Item WHERE ItemID IN ( SELECT ItemID FROM dbo.Sales EXCEPT SELECT ItemID FROM dbo.Sales WHERE YEAR(SoldDate)<>2016 )
3.每年的总销售额。
SELECT
DISTINCT YEAR(SoldDate) AS [Year],
COUNT(SalesID) OVER(ORDER BY YEAR(SoldDate)) AS RunningTotalCountofSales
FROM dbo.Sales
4. 2018年和2019年的已售物品季度计数。
SELECT YEAR(SoldDate) AS [Year], SUM(CASE WHEN DATEPART(QUARTER, SoldDate)=1 THEN 1 ELSE 0 END) AS FirstQuarter, SUM(CASE WHEN DATEPART(QUARTER, SoldDate)=2 THEN 1 ELSE 0 END) AS SecondQuarter, SUM(CASE WHEN DATEPART(QUARTER, SoldDate)=3 THEN 1 ELSE 0 END) AS ThirdQuarter, SUM(CASE WHEN DATEPART(QUARTER, SoldDate)=4 THEN 1 ELSE 0 END) AS ForthQuarter FROM dbo.Sales WHERE YEAR(SoldDate) BETWEEN 2018 AND 2019 GROUP BY YEAR(SoldDate)
5.每年的销售数量和与上一年相比的销售增长。
SELECT [Year], SoldItemsCount, ISNULL(LAG(SoldItemsCount) OVER(ORDER BY [Year]),0) AS PreviousSoldItemsCount, ISNULL(((SoldItemsCount-LAG(SoldItemsCount) OVER(ORDER BY [Year]))*100)/LAG(SoldItemsCount) OVER(ORDER BY [Year]),0) AS 'Growth(%)' FROM ( SELECT YEAR(SoldDate) AS [Year], COUNT(*) AS SoldItemsCount FROM dbo.Sales GROUP BY YEAR(SoldDate) ) t
6.将价格第三高的商品更新1000。
UPDATE ItemWith3rdPrice SET ItemPrice = ItemPrice + 1000 FROM ( SELECT TOP 1 ItemID, ItemPrice FROM ( SELECT TOP 3 ItemID, ItemPrice FROM dbo.Item ORDER BY ItemPrice DESC ) t ORDER BY ItemPrice ) ItemWith3rdPrice SELECT * FROM dbo.Item