w3resource

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:

SQL Subqueries: Determine who earns more than Mr. Ozer.

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:

Query visualization of Determine who earns more than Mr. Ozer - Duration

Rows:

Query visualization of Determine who earns more than Mr. Ozer - Rows

Cost:

Query visualization of Determine who earns more than Mr. Ozer - 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.



Follow us on Facebook and Twitter for latest update.

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

 





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