w3resource

SQL Subquery Exercises: Display all the information for those employees whose id is any id who earn the second highest salary

SQL SUBQUERY: Exercise-12 with Solution

From the following table, write a SQL query to find those employees who get second-highest salary. Return all the fields of the employees.

Sample table: employees


Sample Solution:

SELECT * 
FROM employees 
WHERE employee_id IN 
(SELECT employee_id 
FROM employees  
WHERE salary = 
(SELECT MAX(salary) 
FROM employees 
WHERE salary < 
(SELECT MAX(salary) 
FROM employees)));

Sample Output:

employee_id	first_name	last_name	email	phone_number	hire_date	job_id	salary	commission_pct	manager_id	department_id
101		Neena		Kochhar		NKOCHHAR515.123.4568	2005-09-21	AD_VP	17000.00	0.00	100		90
102		Lex		De Haan		LDEHAAN	515.123.4569	2001-01-13	AD_VP	17000.00	0.00	100		90

Pictorial Presentation:

SQL Subqueries: Display all the information for those employees whose id is any id who earn the second highest salary.

Practice Online


Query Visualization:

Duration:

Query visualization of Display all the information for those employees whose id is any id who earn the second highest salary - Duration

Rows:

Query visualization of Display all the information for those employees whose id is any id who earn the second highest salary - Rows

Cost:

Query visualization of Display all the information for those employees whose id is any id who earn the second highest salary - Cost

Contribute your code and comments through Disqus.

Previous: From the following tables, write a SQL query to find those employees who do not work in those departments where manager ids are in the range 100, 200 (Begin and end values are included.) Return all the fields of the employees.
Next: From the following tables, write a SQL query to find those employees who work in the same department where 'Clara' works. Exclude all those records where first name is ‘Clara’. Return first name, last name and hire date.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

"where 1=1" statement?

It's usually when folks build up SQL statements.

When you add and value = "Toyota" you don't have to worry about whether there is a condition before or just WHERE.

Ref: https://bit.ly/3cayyhJ