15 T-SQL Interview Questions and Answers

T-SQL, or Transact-SQL, is an extension of SQL used primarily with Microsoft SQL Server. It enhances the capabilities of standard SQL by adding procedural programming, local variables, and various support functions for string processing, data manipulation, and error handling. Mastery of T-SQL is essential for database professionals who need to manage and query relational databases efficiently.

This article provides a curated selection of T-SQL interview questions designed to test and improve your understanding of key concepts and practical applications. By working through these questions, you will be better prepared to demonstrate your proficiency in T-SQL during technical interviews, showcasing your ability to handle complex database tasks.

T-SQL Interview Questions and Answers

1. Write a query to join two tables, Orders and Customers, on the CustomerID column.

To join two tables, Orders and Customers, on the CustomerID column, use the INNER JOIN clause. This returns only the rows where there is a match in both tables.

Example:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

2. Write a query to count the number of orders per customer using GROUP BY.

To count the number of orders per customer, use the GROUP BY clause:

SELECT CustomerID, COUNT(OrderID) AS OrderCount
FROM Orders
GROUP BY CustomerID;

This query groups the results by CustomerID and uses the COUNT function to count the number of orders for each customer.

3. Write a query that uses a subquery to find employees who have placed more than five orders.

A subquery in T-SQL is a query nested inside another query. To find employees who have placed more than five orders, use a subquery to count the number of orders per employee and filter based on this count.

Example:

SELECT EmployeeID, EmployeeName
FROM Employees
WHERE EmployeeID IN (
    SELECT EmployeeID
    FROM Orders
    GROUP BY EmployeeID
    HAVING COUNT(OrderID) > 5
);

4. Write a query using a window function to calculate the running total of sales in a Sales table.

Window functions perform calculations across a set of table rows related to the current row. Use the SUM() function with the OVER() clause to calculate running totals.

Example:

SELECT 
    SalesDate,
    SalesAmount,
    SUM(SalesAmount) OVER (ORDER BY SalesDate) AS RunningTotal
FROM 
    Sales
ORDER BY 
    SalesDate;

5. Write a query using a common table expression (CTE) to find the top 3 highest-paid employees.

A Common Table Expression (CTE) is a temporary result set. To find the top 3 highest-paid employees, use a CTE to order employees by salary and select the top 3.

WITH EmployeeCTE AS (
    SELECT 
        EmployeeID, 
        EmployeeName, 
        Salary,
        ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
    FROM 
        Employees
)
SELECT 
    EmployeeID, 
    EmployeeName, 
    Salary
FROM 
    EmployeeCTE
WHERE 
    RowNum <= 3;

6. Write a query to find all orders placed in the last 30 days.

To find all orders placed in the last 30 days, use the GETDATE() function to get the current date and DATEADD() to subtract 30 days.

Example:

SELECT OrderID, OrderDate, CustomerID, TotalAmount
FROM Orders
WHERE OrderDate >= DATEADD(day, -30, GETDATE());

7. Write a stored procedure to insert a new employee record into the Employees table.

To create a stored procedure for inserting a new employee record, use the following example:

CREATE PROCEDURE InsertEmployee
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @BirthDate DATE,
    @HireDate DATE,
    @JobTitle NVARCHAR(50)
AS
BEGIN
    INSERT INTO Employees (FirstName, LastName, BirthDate, HireDate, JobTitle)
    VALUES (@FirstName, @LastName, @BirthDate, @HireDate, @JobTitle)
END

Execute this stored procedure with:

EXEC InsertEmployee 'John', 'Doe', '1980-01-01', '2023-01-01', 'Software Engineer'

8. Write a trigger that updates the LastUpdated column of a table whenever a row is updated.

A trigger automatically runs when certain events occur in the database. To update the LastUpdated column whenever a row is updated, use the following T-SQL code:

CREATE TRIGGER trg_UpdateLastUpdated
ON YourTable
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE YourTable
    SET LastUpdated = GETDATE()
    FROM YourTable
    INNER JOIN inserted i ON YourTable.PrimaryKey = i.PrimaryKey;
END;

9. Write a recursive CTE to generate a hierarchical list of employees and their managers.

A recursive Common Table Expression (CTE) allows for hierarchical data queries. It consists of an anchor member and a recursive member.

Example:

WITH EmployeeHierarchy AS (
    SELECT EmployeeID, ManagerID, EmployeeName, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, ManagerID, EmployeeName, Level
FROM EmployeeHierarchy
ORDER BY Level, ManagerID, EmployeeID;

10. Write a query to pivot sales data from rows to columns.

Pivoting data transforms row data into a columnar format. Use the PIVOT operator for this transformation.

Example:

SELECT 
    ProductID,
    [2021] AS Sales_2021,
    [2022] AS Sales_2022
FROM 
    (SELECT 
         ProductID, 
         Year, 
         SalesAmount 
     FROM 
         SalesData) AS SourceTable
PIVOT
    (SUM(SalesAmount)
     FOR Year IN ([2021], [2022])
    ) AS PivotTable;

11. Write a query to extract values from an XML column in a table.

To extract values from an XML column, use the nodes() and value() methods.

Example:

SELECT 
    EmployeeData.value('(/Employee/ID)[1]', 'INT') AS EmployeeID,
    EmployeeData.value('(/Employee/Name)[1]', 'VARCHAR(100)') AS EmployeeName,
    EmployeeData.value('(/Employee/Position)[1]', 'VARCHAR(100)') AS EmployeePosition
FROM 
    Employees

12. Explain the best practices for securing a T-SQL database.

Securing a T-SQL database involves several practices:

• Authentication: Use strong methods and multi-factor authentication.
• Authorization: Grant minimum necessary permissions.
• Encryption: Encrypt data at rest and in transit.
• Auditing: Enable auditing and review logs regularly.
• Regular Updates: Keep software and systems updated.
• Backup and Recovery: Implement a robust strategy.
• Network Security: Use firewalls and limit access.
• Monitoring and Alerts: Set up mechanisms for real-time detection.

13. Describe the different types of indexes and their use cases.

Indexes improve query performance. Types include:

• Clustered Index: Sorts and stores data rows based on the index key.
• Non-Clustered Index: Creates a separate structure with pointers to data rows.
• Unique Index: Ensures unique values in indexed columns.
• Full-Text Index: Used for full-text searches on large text columns.
• Filtered Index: Includes only a subset of rows based on a condition.
• XML Index: Improves performance of queries against XML data types.

14. Explain how transactions and locking mechanisms work in SQL Server.

Transactions ensure reliable execution of operations, adhering to ACID properties. Locking mechanisms manage concurrent access to data:

• Shared Locks: Used for read operations.
• Exclusive Locks: Used for write operations.
• Update Locks: Prevent deadlocks during updates.
• Intent Locks: Indicate intent to acquire a more restrictive lock.

Lock escalation converts fine-grained locks into a single coarse-grained lock to reduce overhead.

15. Write a query using advanced joins like CROSS APPLY or OUTER APPLY.

CROSS APPLY and OUTER APPLY are advanced join operations. CROSS APPLY works like an INNER JOIN, while OUTER APPLY works like a LEFT JOIN.

Example:

-- Sample tables
CREATE TABLE Employees (
    EmployeeID INT,
    Name NVARCHAR(50)
);

CREATE TABLE Orders (
    OrderID INT,
    EmployeeID INT,
    OrderDate DATE
);

-- Sample data
INSERT INTO Employees (EmployeeID, Name) VALUES (1, 'John Doe'), (2, 'Jane Smith');
INSERT INTO Orders (OrderID, EmployeeID, OrderDate) VALUES (101, 1, '2023-01-01'), (102, 1, '2023-02-01');

-- Using CROSS APPLY
SELECT e.Name, o.OrderID, o.OrderDate
FROM Employees e
CROSS APPLY (
    SELECT TOP 1 OrderID, OrderDate
    FROM Orders o
    WHERE o.EmployeeID = e.EmployeeID
    ORDER BY o.OrderDate DESC
) o;

-- Using OUTER APPLY
SELECT e.Name, o.OrderID, o.OrderDate
FROM Employees e
OUTER APPLY (
    SELECT TOP 1 OrderID, OrderDate
    FROM Orders o
    WHERE o.EmployeeID = e.EmployeeID
    ORDER BY o.OrderDate DESC
) o;