Just for fun test your T-SQL knowledge with these 3 simple questions

Are you ready to put your T-SQL skills to the test?

Photo by Rubaitul Azad on Unsplash

Just for fun in this article I will ask you three questions that cover different aspects of T-SQL programming.

Given the following employee and department tables:

Entity Relationship Diagram

USE master;

DROP DATABASE IF EXISTS tsql_three_questions;

CREATE DATABASE tsql_three_questions;

USE tsql_three_questions;

-- DROP CREATE tables

DROP TABLE IF EXISTS dbo.department;

CREATE TABLE dbo.department
(
  [key] INT IDENTITY(1, 1) NOT NULL
  , [id] NVARCHAR(32) NOT NULL
  , [name] NVARCHAR(128) NULL
  , CONSTRAINT [PK_department_key] PRIMARY KEY ([key])
  , CONSTRAINT [AK_department_id] UNIQUE ([id])
)
;
GO

DROP TABLE IF EXISTS dbo.employee;

CREATE TABLE dbo.employee
(
  [key] INT IDENTITY(1, 1) NOT NULL
  , [id] NVARCHAR(32) NOT NULL
  , [name] NVARCHAR(128) NULL
  , [department_key]INT NULL
  , [salary] FLOAT NULL
  , CONSTRAINT [PK_employee_key] PRIMARY KEY ([key])
  , CONSTRAINT [AK_employee_id] UNIQUE ([id])
  , CONSTRAINT [FK_employee_department_key] FOREIGN KEY (department_key) REFERENCES department([key])
)
;
GO

Let’s populate these tables with some data:

-- INSERT sample data

INSERT INTO dbo.department ([id], [name]) VALUES
('HR', 'Human Resources')
, ('IT', 'Information Technology')
;

INSERT INTO dbo.employee ([id], [name], [department_key], [salary]) VALUES
('e1', 'Pippo', 1, 3000.00)
, ('e2', 'Pluto', 2, 2500.00)
, ('e3', 'Paperino', 1, 4000.00)
, ('e4', 'Topolino', NULL, 3500.00)
, ('e5', 'Minni', 1, 3500.00)
, ('e6', 'Clarabella', 2, 3000.00)
, ('e7', 'Orazio', NULL, 4000.00)
;

Let’s check the data:

-- Check data

SELECT *
FROM dbo.department
;

SELECT *
FROM dbo.employee
;

Example data

The questions:

Question 1: Retrieve the names of employees who work in the HR department creating an appropriate index to improve performances.

Question 2: How many employees do not work in IT department? The answer should be 5 but the following query returns 3. Why and how can we modify it in order to get 5?

Question 2 query

Question 3: Retrieve the top earning employee for each department along with the corresponding salary.

Before checking my solutions try to answer them yourself.

Photo by Hannes Richter on Unsplash

My answers:

Answer to question 1

-- Question 1: Retrieve the names of employees who work in the HR department creating an appropriate index to improve performance

DROP INDEX IF EXISTS IX_employee_department_key ON employee;

CREATE NONCLUSTERED INDEX IX_employee_department_key ON employee(department_key) INCLUDE([name]);
GO

SELECT e.[name] AS [employee_name]
FROM dbo.employee AS e
INNER JOIN dbo.department AS d
 ON e.department_key = d.[key]
WHERE d.[id] = 'HR'
;

The index IX_employee_department_key is created as a non clustered index on the department_key column in the employee table. The INCLUDE clause includes the name column. This index will allow for faster retrieval of employee records based on the department. In the query, the employee and department tables are joined based on the foreign key relationship between the department_key column in the employee table and the key column in the department table. The WHERE clause filters the result set to retrieve employees from the desired department based on the id column in the department table.

Query answer 1

Of course in this example we do not have enough data to really require the creation of an index but in a scenario in which both tables have a large number of records using the index should improve performances.

Query answer 1 execution plan

Answer to question 2

SELECT COUNT(*) AS [row_count]
FROM dbo.employee AS e
LEFT JOIN dbo.department AS d
 ON e.department_key = d.[key]
WHERE d.[id] != 'IT' 
 OR e.[department_key] IS NULL
;

Since it is not possible to check if a NULL value is equal or not equal to a specific value the rows of the table employee where department_key is NULL do not get checked. Therefore to get these rows we need to apply another condition just for them OR e.[department_key] IS NULL

Query answer 2

Answer to question 3

SELECT a.[department_name]
 , a.[employee_name]
 , a.salary
FROM
(
    SELECT d.[name] AS [department_name]
 , e.[name] AS [employee_name]
 , e.salary
 ,ROW_NUMBER() OVER (PARTITION BY e.department_key ORDER BY e.salary DESC) AS row_num
 FROM dbo.employee AS e
 INNER JOIN dbo.department AS d
  ON e.department_key = d.[key]
) AS a
WHERE row_num = 1
;

Query answer 3

I hope you had fun and maybe learned something answering these simple questions.

Outro

I hope the story was interesting and thank you for taking the time to read it. On my Blogspot you can find the same post in Italian. Let me know if you have any question and if you like the content that I create feel free to buy me a coffee.