• 针对开发人员的SQL Server面试问题


    问题

    在某个时候,我们需要采访高级数据库开发人员,并检查他们的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
  • 相关阅读:
    Python学习第61天(html之form标签)
    Python学习第60天(html之body标签)
    Python学习第59天(web前端html /1))
    Python学习第58天(selector版本的ftp习题实现)
    Python学习第57天(异步IO)
    Python学习第56天(configpraser模块复习)
    Python学习第55天(IO多路复用)
    Python学习第54天(阻塞(blocking) IO和非阻塞(non-blocking)IO)
    如何通过Git Bash的命令行将电脑本地项目上传到自己的GitHub上
    第10周周博客
  • 原文地址:https://www.cnblogs.com/Javi/p/13470487.html
Copyright © 2020-2023  润新知