Posted on January 24, 2023 by Kevin Guisarde .

SQL Server Subqueries are a critical aspect of SQL that enables developers and data analysts to write nested queries within their primary query. By allowing for the creation of complex queries, SQL Server Subqueries provide developers with more significant control and flexibility in their data analysis. These subqueries can be used to perform various operations, such as calculating the average salary of employees in a specific department, retrieving the second-highest salary in a company, or even joining multiple tables to retrieve specific data sets. This functionality can help data analysts to efficiently retrieve, organize and analyze large amounts of data from multiple sources. Ultimately, SQL Server Subqueries are an indispensable tool in the SQL language, providing developers and data analysts with the power and flexibility to handle complex data sets and perform precise, efficient data analysis.

Agenda

1. Introduction to SQL Server Subqueries
2. Different Concept Types with Industry Examples and Coding Examples
3. Real-World Example Questions in the Healthcare Industry
4. Most Commonly Asked Interview Question
5. Conclusion

Introduction to SQL Server Subqueries

SQL Server Subqueries are a fundamental component of SQL. They allow you to write nested queries within your main query. They can be used to perform complex operations, such as finding the average salary of employees in a specific department or finding the second-highest salary in a company.

Different Concept Types with Industry Examples and Coding Examples:

Different Concept Types with Industry Examples and Coding Examples

Simple Subqueries

A simple subquery is a query within a query. It returns a single value, and it is used to solve a specific problem.

Example Question:
Find the department with the highest average salary in a healthcare company.

Coding Example:

SELECT
    department, AVG(salary)
FROM
    employees
GROUP BY
    department
HAVING
    AVG(salary) = (SELECT MAX(AVG(salary))
                   FROM employees
                   GROUP BY department)

Explanation:
In this example, the main query groups the employees by department and calculates the average salary for each department. The subquery inside the HAVING clause returns the maximum average salary. The main query then only returns departments with the highest average salary, which is the result of the subquery.

Correlated Subqueries

A correlated subquery is a subquery that depends on the values from the main query. It returns a set of values, and it is used to compare values between the main query and the subquery.

Example Question:
Find the names of employees who earn more than the average salary of their department in a healthcare company.

Coding Example:

SELECT
    name, salary, department
FROM
    employees e1
WHERE
    salary > (SELECT AVG(salary)
              FROM employees e2
              WHERE e1.department = e2.department)

Explanation:
In this example, the main query returns the name, salary, and department of all employees. The subquery inside the WHERE clause returns the average salary for each department. The main query then only returns the names and salaries of employees who earn more than the average salary of their department, which is the result of the subquery.

Multi-level Subqueries

A multi-level subquery is a subquery within a subquery. It is used to perform complex operations, such as finding the second-highest salary in a company.

Example Question:
Find the second-highest salary in a healthcare company.

Coding Example:

SELECT
    MAX(salary)
FROM
    employees
WHERE
    salary < (SELECT MAX(salary) FROM employees)

Explanation:
In this example, the subquery inside the WHERE clause returns the highest salary in the company. The main query then returns the second highest salary, which is the maximum salary that is less than the highest salary.

Real-World Example Questions in the Healthcare Industry

Script to generate tables and records:

-- Create Patients table
CREATE TABLE Patients (
  PatientID INT PRIMARY KEY,
  Name VARCHAR(50),
  VisitDate DATE
);

-- Insert records into Patients table
INSERT INTO Patients (PatientID, Name, VisitDate)
VALUES
(1, 'John Doe', '2022-01-01'),
(2, 'Jane Doe', '2022-01-15'),
(3, 'Jim Smith', '2022-02-01'),
(4, 'Jane Smith', '2022-02-15'),
(5, 'John Brown', '2022-03-01'),
(6, 'Jane Brown', '2022-03-15'),
(7, 'Jim Wilson', '2022-04-01'),
(8, 'Jane Wilson', '2022-04-15');

-- Create Treatments table
CREATE TABLE Treatments (
  TreatmentID INT PRIMARY KEY,
  PatientID INT,
  Cost MONEY,
  FOREIGN KEY (PatientID) REFERENCES Patients (PatientID)
);

-- Insert records into Treatments table
INSERT INTO Treatments (TreatmentID, PatientID, Cost)
VALUES
(1, 1, 100.00),
(2, 1, 200.00),
(3, 2, 150.00),
(4, 3, 125.00),
(5, 4, 175.00),
(6, 5, 225.00),
(7, 6, 200.00),
(8, 7, 175.00),
(9, 8, 150.00);
  

1. What is the total number of patients visited for each month in 2022, in the “Patients” table?

SELECT MONTH(VisitDate) AS [Month], COUNT(PatientID) AS TotalPatients
FROM Patients
WHERE YEAR(VisitDate) = 2022
GROUP BY MONTH(VisitDate)
ORDER BY [Month];

2. What is the average cost of treatments for patients who visited the hospital in 2022, in the “Patients” and “Treatments” tables?

SELECT AVG(Cost) AS AverageCost
FROM Patients
JOIN Treatments ON Patients.PatientID = Treatments.PatientID
WHERE YEAR(VisitDate) = 2022;

3. How many patients have visited the hospital more than once in 2022, in the “Patients” table?

WITH PatientVisits AS (
  SELECT PatientID, COUNT(VisitDate) AS TotalVisits
  FROM Patients
  WHERE YEAR(VisitDate) = 2022
  GROUP BY PatientID
)
SELECT COUNT(PatientID) AS RepeatVisitors
FROM PatientVisits
WHERE TotalVisits > 1;

Most Commonly Asked Interview Question and Answer in SQL Server

Q: What is a subquery in SQL Server and how have you used it in a previous project?

A: subquery in SQL Server is a query within another query. It returns a result set that can be used as input to the main query. I have used subqueries in a previous project to find the average cost of treatments for each patient, based on the total number of treatments they have received. To do this, I created a subquery to find the total cost of treatments for each patient and then used that result set as an input to the main query to find the average cost.

SELECT AVG(TotalCost) AS AverageCost
FROM (
  SELECT PatientID, SUM(Cost) AS TotalCost
  FROM Treatments
  GROUP BY PatientID
) AS Subquery

Conclusion

In this blog, we have discussed the different types of SQL Server subqueries and provided examples from the healthcare industry. We have also posted three real-world example questions, along with the script to generate the tables and records needed to answer them. Finally, we have answered the most commonly asked interview question about subqueries and provided a concrete example from a previous project.