SQL Exercises: Determine who earns more than Mr. Ozer
SQL SUBQUERY: Exercise-29 with Solution
From the following table, write a SQL query to find all those employees who earn more than an employee whose last name is 'Ozer'. Sort the result in ascending order by last name. Return first name, last name and salary.
Sample table: employees
Sample Solution:
SELECT first_name, last_name, salary
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE last_name='Ozer')
ORDER BY last_name;
Sample Output:
first_name last_name salary Lex De Haan 17000.00 Alberto Errazuriz 12000.00 Nancy Greenberg 12000.00 Michael Hartstein 13000.00 Shelley Higgins 12000.00 Steven King 24000.00 Neena Kochhar 17000.00 Karen Partners 13500.00 John Russell 14000.00
Code Explanation:
The said query in SQL that retrieve the first name, last name, and salary of employees from the 'employees' table who have a salary higher than the salary of a specified employee whose last name "Ozer". The result is sorted in ascending order by last name.
The salary of the employee with the last name "Ozer" is retrieved using a subquery that selects the salary from the "employees" table where the last name is "Ozer".
Visual Presentation:

Alternative Statements:
Subquery with JOIN:
SELECT e.first_name, e.last_name, e.salary
FROM employees e
JOIN (
SELECT salary
FROM employees
WHERE last_name='Ozer'
) o ON e.salary > o.salary
ORDER BY e.last_name;
Using a JOIN:
SELECT e.first_name, e.last_name, e.salary
FROM employees e
JOIN employees o ON e.salary > o.salary AND o.last_name = 'Ozer'
ORDER BY e.last_name;
Practice Online
Query Visualization:
Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: IT employees who earn more than the average.
Next SQL Exercise: Manager who works for a department based in the US.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
Difference between natural join and inner join
One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-
Consider:
TableA TableB +------------+----------+ +--------------------+ |Column1 | Column2 | |Column1 | Column3 | +-----------------------+ +--------------------+ | 1 | 2 | | 1 | 3 | +------------+----------+ +---------+----------+
The INNER JOIN of TableA and TableB on Column1 will return
SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1); SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;
+------------+-----------+---------------------+ | a.Column1 | a.Column2 | b.Column1| b.Column3| +------------------------+---------------------+ | 1 | 2 | 1 | 3 | +------------+-----------+----------+----------+
The NATURAL JOIN of TableA and TableB on Column1 will return:
SELECT * FROM TableA NATURAL JOIN TableB +------------+----------+----------+ |Column1 | Column2 | Column3 | +-----------------------+----------+ | 1 | 2 | 3 | +------------+----------+----------+
Ref: https://bit.ly/3AG5CId
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook