Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation

Answer: Aggregate Records Using MIN/MAX

Explore how to use SQL aggregate functions MIN and MAX to retrieve minimum and maximum values from a dataset. Understand different query techniques including subqueries, aliases, WHERE clauses, and common table expressions to find records like the second-highest salary. Gain practical skills to write precise SQL queries for interview questions.

We'll cover the following...

Solution

The solution is given below:

MySQL
/* The query to find the second-highest number */
SELECT MAX(Salary) AS SecondHighestSalary
FROM   Employees 
WHERE  Salary NOT IN (SELECT Max(Salary) 
                      FROM   Employees);

Explanation

The explanation of the code solution is given below:

  • Line 2: The SELECT query selects the maximum value in the Salary column using the MAX() function. We use AS to set an alias for the column.

  • Line 3: The FROM clause specifies the table name as Employees.

  • Line 4: We selected the second highest salary using the subquery in the where clause to filter. The NOT IN operator is a combination of two SQL keywords, NOT and IN. The NOT operator allows the retrieval of the records that don’t match the condition and the IN operator is used to specify a list of items from which we decide on the values to retrieve.

Recall of relevant concepts

We have covered the following concepts in this question:

  • Selective columns

  • Aliases

  • Sorting the data

  • Limiting the records ... ...