w3resource

SQL Subquery Exercises: Display the name, salary, department id, job id for those employees who works in the same designation as the employee works whose id is 169

SQL SUBQUERY : Exercise-2 with Solution

From the following table, write a SQL query to find out which employees have the same designation as the employee whose ID is 169. Return first name, last name, department ID and job ID.

Sample table: employees


Sample Solution :


SELECT first_name, last_name, salary, department_id, job_id 
FROM employees 
WHERE job_id =  
( SELECT job_id  
FROM employees  
WHERE employee_id=169
);

Sample Output:

first_name	last_name	salary		department_id	job_id
Peter		Tucker		10000.00	80		SA_REP
David		Bernstein	9500.00		80		SA_REP
Peter		Hall		9000.00		80		SA_REP
Christopher	Olsen		8000.00		80		SA_REP
Nanette		Cambrault	7500.00		80		SA_REP
Oliver		Tuvault		7000.00		80		SA_REP
Janette		King		10000.00	80		SA_REP
Patrick		Sully		9500.00		80		SA_REP
Allan		McEwen		9000.00		80		SA_REP
Lindsey		Smith		8000.00		80		SA_REP
Louise		Doran		7500.00		80		SA_REP
Sarath		Sewall		7000.00		80		SA_REP
Clara		Vishney		10500.00	80		SA_REP
Danielle	Greene		9500.00		80		SA_REP
Mattea		Marvins		7200.00		80		SA_REP
David		Lee		6800.00		80		SA_REP
Sundar		Ande		6400.00		80		SA_REP
Amit		Banda		6200.00		80		SA_REP
Lisa		Ozer		11500.00	80		SA_REP
Harrison	Bloom		10000.00	80		SA_REP
Tayler		Fox		9600.00		80		SA_REP
William		Smith		7400.00		80		SA_REP
Elizabeth	Bates		7300.00		80		SA_REP
Sundita		Kumar		6100.00		80		SA_REP
Ellen		Abel		11000.00	80		SA_REP
Alyssa		Hutton		8800.00		80		SA_REP
Jonathon	Taylor		8600.00		80		SA_REP
Jack		Livingston	8400.00		80		SA_REP
Kimberely	Grant		7000.00		0		SA_REP
Charles		Johnson		6200.00		80		SA_REP

Pictorial Presentation:

SQL Subqueries: Display the name, salary, department id, job id for those employees who works in the same designation as the employee works whose id is 169.

Practice Online


Query Visualization:

Duration:

Query visualization of Display the name , salary, department id, job id for those employees who works in the same designation as the employee works whose id is 169 - Duration

Rows:

Query visualization of Display the name , salary, department id, job id for those employees who works in the same designation as the employee works whose id is 169 - Rows

Cost:

Query visualization of Display the name , salary, department id, job id for those employees who works in the same designation as the employee works whose id is 169 - Cost

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to find those employees who get higher salary than the employee whose ID is 163. Return first name, last name.
Next: From the following table, write a SQL query to find those employees whose salary matches the smallest salary of any of the departments. Return first name, last name and department ID.

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