How to write nested SQL queries for max salary

TL;DR

The inner query computes the maximum salary first, returning a single value. The outer query then selects the employee name where the salary equals that maximum, producing the person who earns the most. This demonstrates how nested queries operate step by step and how to reference inner results in the outer query.

Transcript

Hello Friends! Welcome to Gate Smashers Today I am going to discuss The practice questions on SQL query Actually this topic will help you a lot For your UGC NET exam For your GATE exam Even for university level exam Because at many places SQL query questions are asked And, if I say a simple thing Then in DBMS In whole database management system If ... Read More

Key Insights

• The inner query runs first and returns a single value, which is the maximum salary in the dataset.
• The outer query uses a where salary = (inner query result) to fetch matching employees by name.
• Nested queries are used to solve problems where a simple aggregate output must be related to other attributes in the same table.
• A subquery can be used to isolate the computation (maximum salary) from the filtering condition (finding the corresponding name).
• The example demonstrates how to combine aggregate functions with subqueries to produce meaningful results.
• This approach makes it possible to handle cases where a value depends on an aggregated computation.
• The outer query compares each row against the inner query’s output to determine which rows meet the condition.
• Understanding inner and outer queries lays the groundwork for more advanced topics like correlated subqueries and source-based queries.
• More videos from this channel:
  • What is software maintenance: perfective, preventive, adaptive
  • What is a batch operating system and how it works
  • What Are SQL Command Types and Their Uses in DBMS
• More from this channel: Gate Smashers

Questions & Answers

Q: How to find the maximum salary in the employee table?

The inner query select max(salary) from employee computes the highest salary, returning a single numeric result such as 50,000. The outer query then uses this value to filter the employee table and retrieve the matching employee name. This demonstrates a basic subquery pattern that first derives a value and then applies it.

Q: What is the purpose of a subquery in this example?

The subquery computes an intermediate value, the maximum salary, which the outer query uses to identify which employee(s) earn that amount. This two-step approach separates calculation from selection and shows how a value derived from one query can drive another query's filtering criteria.

Q: What happens when the inner query is executed?

The inner query runs exactly once and returns the maximum salary from the employee table. This single value is then used by the outer query to compare against each row, enabling the correct selection of the employee who earns the maximum amount.

Q: Why can't we directly write where salary = 50000 in the outer query?

Because 50000 is a literal value that might not represent the maximum salary in the current dataset. The outer query needs to compare salaries to the actual result of the inner query, which reflects the true maximum, ensuring the correct employee is selected even if the maximum changes.

Q: How does the outer query use the inner query result?

The outer query selects the employee name from the table where salary equals the inner query’s output. This creates a direct link between the computed maximum and the corresponding record in the dataset, yielding the person who earns the top salary.

Q: Can this pattern be used with other aggregates besides max?

Yes, the same subquery pattern can be used with other aggregate functions such as min, avg, sum, or count. You compute the aggregate in the inner query and then reference that result in the outer query to filter or display related data.

Q: What is the overall flow of a nested query like this?

First, the inner query executes to produce a single value. Next, the outer query uses that value in a comparison or filter condition to retrieve related data from the same table. The result is a precise row or set of rows tied to the intermediate computation.

Q: What concepts are foundational to understanding this video’s topic?

Foundational concepts include subqueries, nested queries, and the use of aggregate functions. The example shows how to frame a query with a subquery inside the outer query’s where clause, and it paves the way for more advanced topics such as correlated subqueries and source-based queries.

Summary & Key Takeaways

• The video starts with a basic explanation of SQL queries and the role of aggregate functions, particularly max, to find the highest salary in an employee table. It then introduces subqueries as a way to reuse the inner result in an outer query, providing a concrete example.

• Next, the instructor demonstrates a nested query that first finds the maximum salary and then filters the employee table to fetch the corresponding name. The explanation covers the logic of comparing outer salary values to the inner query result.

• Finally, the video clarifies how the inner query runs once, its output is used in the outer query, and how this approach helps locate the employee associated with the maximum salary and serves as a foundation for more complex nested and correlated queries.