Interview questions can be tough. I’ve been grilled on technical and personal skills. When it comes to technical tests all I can recommend is to practice before you go to the interview.

There will be things you don’t know. Don’t panic. Make sure you nail the questions you know then come back to the trickier ones. If you really don’t know the answer try to demonstrate some knowledge and try to show how you would go about solving this problem (without Google!).

This is a technical test I was given recently. I was given 30 minutes to HAND write the answers. It is a pure T-SQL test and in my opinion pretty tricky. I’ve given it to several people now, all have struggled, time being one of the biggest factors.

Feel free to use it as a practice.

To start with I was given this schema. DBSchema

The told I would be asked 10 questions, the only advice given was:

“The SQL you write should be as efficient as possible. You should avoid using temporary tables and cursors where possible. If
you can make your answers more generic by the addition of variables, please do so. Make as few assumptions in your
answers as possible (e.g. Assumptions about data types, data volumes, etc).
If you complete all 10 questions inside the allotted 30 minutes, for each question, where would you apply indexes to the
tables in order to make the select operation as efficient as possible? What sort of indexes would you apply?”

Questions:

1) Return the first name, last name, product name, and sale price for all products sold in the month of July 2008.

2) Return the customer ID, first name and last name of those individuals in the customer table who have made no
purchases to date.

3) Return the first name, last name, sale price, recommended sale price and the difference between the sale price and the recommended sale price, expressed as a percentage, for all sales.

4) Delete the customers from the database who’s only purchases have been prior to 2007.

5) Return the average sales price, by product category

6) Return the product category and the average sales price, for those customers who have returned two or more products.

7) Update the sale price to the recommended sale price for those sales occurring between April 2007 and April 2008.

8) Return the number of sales by product category where the average recommended price is £10 or more greater than the average sales price.

9) Return the sales date and the running total for all sales, ordered by sales date in ascending order.

10) For each product, return the hierarchy of products, starting with the top-most product in the hierarchy and then continuing to all level-1 products in the hierarchy, then level-2 etc. Assume there is an indefinite number of
products to the hierarchy.

Let me know how you do.

Tagged with →  

6 Responses to T-SQL Technical Test

  1. SQL_Joe_Harding says:

    The questions seem ok but I agree that it is tough in the time. Number 6 is badly worded though.
    I assume they wanted a recursive cte for the last question. No way I could do that on paper.

  2. Simon says:

    Yeah 6 is bad. By return they mean query results not returned stock.

  3. yup took me too long too. Herewith my TSQL for people to critique

    — USE FooBar
    GO
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    SET ANSI_PADDING ON
    GO
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N’dbo.Customers’) AND type in (N’U’))
    CREATE TABLE dbo.Customers
    ( customerID int IDENTITY(1,1) NOT NULL,
    firstName varchar(50) NOT NULL,
    lastName varchar(50) NOT NULL,
    city varchar(50) NULL,
    state varchar(50) NULL,
    postCode varchar(10) NULL,
    CONSTRAINT PK_Customers PRIMARY KEY NONCLUSTERED
    (customerID
    ) WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N’dbo.Products’) AND type in (N’U’))
    CREATE TABLE dbo.Products
    ( productID int IDENTITY(1,1) NOT NULL,
    parentProductID int NULL,
    productName varchar(50) NOT NULL,
    recommendedPrice money NULL,
    category tinyint NULL,
    CONSTRAINT PK_Products PRIMARY KEY NONCLUSTERED
    ( productID )
    WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N’dbo.Sales’) AND type in (N’U’))
    CREATE TABLE dbo.Sales
    ( saleID int IDENTITY(1,1) NOT NULL,
    productID int NOT NULL,
    customerID int NOT NULL,
    salePrice money NULL,
    saleDate date NULL,
    CONSTRAINT PK_Sales PRIMARY KEY NONCLUSTERED
    ( saleID )
    WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID(N’dbo.Customers’) AND name=N’CI_Customers_lastName_firstName’)
    CREATE UNIQUE NONCLUSTERED INDEX CI_Customers_lastName_firstName ON dbo.Customers
    ( lastName,
    firstName
    )
    WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, SORT_IN_TEMPDB=OFF, IGNORE_DUP_KEY=OFF, DROP_EXISTING=OFF, ONLINE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]
    GO
    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID(N’dbo.Products’) AND name=N’CI_Products_productName’)
    CREATE UNIQUE NONCLUSTERED INDEX CI_Products_productName ON dbo.Products
    ( productName )
    WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, SORT_IN_TEMPDB=OFF, IGNORE_DUP_KEY=OFF, DROP_EXISTING=OFF, ONLINE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]
    GO
    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID(N’dbo.Sales’) AND name=N’CI_Sales_customerID_productID’)
    CREATE NONCLUSTERED INDEX CI_Sales_customerID_productID ON dbo.Sales
    ( customerID,
    productID
    )
    WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, SORT_IN_TEMPDB=OFF, DROP_EXISTING=OFF, ONLINE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]
    GO

    IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N’dbo.FK_Products_Products’) AND parent_object_id=OBJECT_ID(N’dbo.Products’))
    ALTER TABLE dbo.Products WITH CHECK ADD CONSTRAINT FK_Products_Products FOREIGN KEY(parentProductID)
    REFERENCES dbo.Products (productID)
    GO
    IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N’dbo.FK_Products_Products’) AND parent_object_id=OBJECT_ID(N’dbo.Products’))
    ALTER TABLE dbo.Products CHECK CONSTRAINT FK_Products_Products
    GO
    IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N’dbo.FK_Sales_Customers’) AND parent_object_id=OBJECT_ID(N’dbo.Sales’))
    ALTER TABLE dbo.Sales WITH CHECK ADD CONSTRAINT FK_Sales_Customers FOREIGN KEY(customerID)
    REFERENCES dbo.Customers (customerID)
    GO
    IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N’dbo.FK_Sales_Customers’) AND parent_object_id=OBJECT_ID(N’dbo.Sales’))
    ALTER TABLE dbo.Sales CHECK CONSTRAINT FK_Sales_Customers
    GO
    IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N’dbo.FK_Sales_Products’) AND parent_object_id=OBJECT_ID(N’dbo.Sales’))
    ALTER TABLE dbo.Sales WITH CHECK ADD CONSTRAINT FK_Sales_Products FOREIGN KEY(productID)
    REFERENCES dbo.Products (productID)
    GO
    IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N’dbo.FK_Sales_Products’) AND parent_object_id=OBJECT_ID(N’dbo.Sales’))
    ALTER TABLE dbo.Sales CHECK CONSTRAINT FK_Sales_Products
    GO

    — Q1
    SELECT C.firstName, C.lastName, P.productName, S.saleDate
    FROM dbo.Customers AS C
    JOIN dbo.Sales AS S ON C.customerID = S.customerID
    JOIN dbo.Products AS P ON S.productID = P.productID
    WHERE S.saleDate BETWEEN ‘2008-01-07’ AND ‘2008-01-08’
    ORDER BY C.lastName, C.firstName
    GO

    — Q2
    SELECT C.customerID, C.firstName, C.lastName
    FROM dbo.Customers AS C
    LEFT OUTER JOIN
    dbo.Sales AS S ON C.customerID = S.customerID
    WHERE S.customerID IS NULL
    ORDER BY C.lastName, C.firstName
    GO

    — Q3
    SELECT C.firstName, C.lastName, S.salePrice, P.recommendedPrice, (S.salePrice – P.recommendedPrice) * 100 / P.recommendedPrice AS diff_price_perc
    FROM dbo.Customers AS C
    JOIN dbo.Sales AS S ON C.customerID = S.customerID
    JOIN dbo.Products AS P ON S.productID = P.productID
    ORDER BY C.firstName, C.lastName, P.productName, S.saleDate
    GO

    — Q4
    DELETE dbo.Customers
    WHERE EXISTS
    ( SELECT 1 AS oldsale
    FROM dbo.Sales AS Osales
    WHERE dbo.Customers.customerID = customerID
    AND saleDate = ‘2008-01-04’
    )
    GO

    — Q5
    SELECT P.category, AVG(S.salePrice) AS Avg_salePrice
    FROM dbo.Products AS P
    JOIN dbo.Sales AS S ON P.productID = S.productID
    GROUP BY P.category
    ORDER BY P.category
    GO

    — Q6
    SELECT P.category, AVG(S.salePrice) AS Avg_salePrice
    FROM dbo.Sales AS S
    JOIN dbo.Products AS P ON S.productID = P.productID
    WHERE exists
    ( SELECT 1
    FROM dbo.Customers AS C
    JOIN dbo.Sales AS S ON S.customerID = C.customerID
    WHERE C.customerID = S.customerID
    AND
    ( SELECT count(*)
    FROM dbo.Products AS P
    WHERE S.productID = P.productID
    ) >= 2
    )
    GROUP BY P.category
    ORDER BY P.category
    GO

    — Q7
    UPDATE dbo.Sales set
    salePrice =
    ( SELECT P.recommendedPrice
    FROM dbo.Products AS P
    WHERE P.productID = dbo.Sales.productID
    )
    WHERE saleDate >= ‘20070401’
    AND saleDate < '20080401'
    GO

    — Q8
    SELECT P.category, COUNT(*) AS N
    FROM dbo.Products AS P
    JOIN dbo.Sales AS S ON P.productID = S.productID
    GROUP BY P.category, P.recommendedPrice
    HAVING AVG(S.salePrice) <= P.recommendedPrice – 10
    ORDER BY P.category
    GO

    — Q9
    SELECT saleDate, SUM(salePrice) AS DailySaleTotal
    FROM dbo.Sales AS S
    GROUP BY saleDate
    ORDER BY saleDate
    GO

    — Q10
    WITH ProdHier
    AS
    ( SELECT productID, productName, 1 AS Lvl
    FROM dbo.Products
    WHERE parentProductID IS NULL
    UNION ALL
    SELECT C.productID, C.productName, P.Lvl +1 AS Lvl
    FROM dbo.Products C
    JOIN ProdHier AS P ON P.productID = C.parentProductID
    )
    SELECT *
    FROM ProdHier
    ORDER BY Lvl, productID, productName
    GO

  4. Tobi says:

    I think Q2 was answered wrongly. no need for left outer join. you can easily phrase it as:
    SELECT C.customerID, C.firstName, C.lastName
    FROM dbo.Customers C WHERE customerID NOT IN (SELECT customerID FROM Sales)
    GO

  5. Lona says:

    Q1 was incorrect. Q1 query result would include 2008-08-01 sales data.

  6. Uma says:

    Question 9 is answered incorrectly,what is reuquired is a running total (me+everybody before me) in the order of sales date. This would need a correlated subquery with correlation based on the col to aggregate,which is the sales price here.

Leave a Reply

Your email address will not be published. Required fields are marked *