Skip to content
Tutorials Center
My learning

Topics

Lesson 38 of 39 ยท Projects and Practice

SQL Server Practice: Exercises with Solutions

Practice SQL Server skills with graded exercises on filtering, grouping, window functions, data changes, transactions, triggers and indexes. Every exercise has a runnable solution.

Reading about SQL Server is not the same as writing T-SQL. These exercises let you practice what chapters 5 to 8 taught: changing data, transactions, procedural logic, indexes and performance reasoning. Each one gives you a task and a dataset, then a solution you can run and edit.

Try the task before you open the solution: write the query in the editor, run it, and compare. Where SQL Server syntax differs from the editor's SQLite, the exercise says so and shows the T-SQL equivalent in a read-only block.

All exercises use these two tables. Press Run on any solution, or paste the setup into your own editor:

SQL
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER, hired TEXT);
INSERT INTO employees VALUES
 (1,'Ada','Eng',9000,NULL,'2019-03-01'), (2,'Ben','Eng',7000,1,'2020-06-15'), (3,'Cleo','Eng',7500,1,'2021-01-10'),
 (4,'Dan','Sales',5000,1,'2018-09-01'), (5,'Eve','Sales',5200,4,'2022-02-20'), (6,'Fay','Sales',4800,4,'2023-05-05'),
 (7,'Gus','Ops',4000,1,'2017-11-30'), (8,'Hal','Ops',4100,7,'2024-01-08');

Part 1: querying warm-up

Exercise 1. List the top 3 earners (in T-SQL: SELECT TOP (3) ...; here use LIMIT).

Exercise 2. For each department show the headcount, average salary and the highest salary, but only departments with more than 2 people.

Exercise 3. Show each employee with the difference between their salary and their department's average (a window function).

SQL Solutions 1 to 3

Press Run (or Ctrl + Enter) to see the result here.

Part 2: changing data

Exercise 4. Give everyone in Sales a 10% raise, and show the old and new salary in the same statement. (T-SQL uses OUTPUT deleted.Salary, inserted.Salary; SQLite has RETURNING.)

Exercise 5. Employees Gus and Hal leave. Copy them to an alumni table, then delete them from employees, both inside one transaction.

SQL Solutions 4 and 5

Press Run (or Ctrl + Enter) to see the result here.

The T-SQL form of exercise 5 uses OUTPUT to do both steps in one statement:

SQL
DELETE FROM dbo.Employees
OUTPUT deleted.* INTO dbo.Alumni
WHERE Name IN (N'Gus', N'Hal');

Part 3: hierarchies and reporting

Exercise 6. Show each employee next to their manager's name (a self join), including the CEO who has no manager.

Exercise 7. Show the whole reporting chain below Ada with a depth number (a recursive CTE; in T-SQL leave out the word RECURSIVE).

SQL Solutions 6 and 7

Press Run (or Ctrl + Enter) to see the result here.

Part 4: procedural thinking

Exercise 8. Write a T-SQL stored procedure dbo.GiveRaise @Dept, @Percent that raises salaries in one department, rejects percentages over 20, and returns the number of rows changed through an OUTPUT parameter. Write it yourself first.

SQL
CREATE OR ALTER PROCEDURE dbo.GiveRaise
    @Dept    nvarchar(50),
    @Percent decimal(5,2),
    @Changed int OUTPUT
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;

    IF @Percent <= 0 OR @Percent > 20
        THROW 50001, 'Percent must be between 0 and 20', 1;

    UPDATE dbo.Employees
    SET    Salary = Salary * (1 + @Percent / 100.0)
    WHERE  Dept = @Dept;

    SET @Changed = @@ROWCOUNT;
END;

Its rule can be checked in SQL: only valid percentages pass. The runnable stand-in validates a list of requests:

SQL Stand-in: validate raise requests

Press Run (or Ctrl + Enter) to see the result here.

Part 5: indexes and performance reasoning

Exercise 9. The query WHERE YEAR(hired) = 2022 is slow on a million-row table, even though hired is indexed. Rewrite it to be SARGable. (Hint: covering indexes and sargability.)

Exercise 10. Which single index best serves WHERE dept = 'Eng' ORDER BY salary DESC while returning only name? Check with the plan.

SQL Solutions 9 and 10

Press Run (or Ctrl + Enter) to see the result here.

The second plan should name the covering index and need no separate sort. The T-SQL equivalent is CREATE INDEX IX_Dept_Salary ON dbo.Employees (Dept, Salary DESC) INCLUDE (Name);.

Part 6: challenge round

Try these without hints, then verify with the solution below:

  1. For each department, find the employee hired most recently (ties by id).
  2. Find departments whose total salary is more than 30% of the company total.
  3. Show the gap in days between each employee's hire date and the previous hire in the same department.
SQL Challenge solutions

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which T-SQL clause returns the old and new values of rows changed by an UPDATE?

    OUTPUT with the deleted and inserted pseudo-tables is the T-SQL feature; RETURNING is the SQLite and PostgreSQL equivalent.
  2. 2. How do you make WHERE YEAR(hired) = 2022 able to use an index on hired?

    Leaving the column bare on one side of a range comparison makes the predicate SARGable.
  3. 3. What does ROW_NUMBER() OVER (PARTITION BY dept ORDER BY hired DESC) = 1 select?

    PARTITION BY restarts numbering per department, and the descending order puts the newest hire first.
  4. 4. Why wrap exercise 5 (copy to alumni, then delete) in a transaction?

    Without a transaction a failure between the two statements could lose or duplicate rows.

Summary

  • Practice the cycle: write, run, compare, then read the plan.
  • Use OUTPUT, transactions and set-based statements for changes; recursive CTEs and window functions for hierarchies and rankings.
  • Keep predicates SARGable and design indexes for the filter, the sort and the returned columns.

Next: SQL Server interview questions