w3resource

SQL Subquery Exercises: Display the employee name, employee id, and job title for all employees whose department location is Toronto

SQL SUBQUERY: Exercise-16 with Solution

From the following table, write a SQL query to find those employees whose department is located at ‘Toronto’. Return first name, last name, employee ID, job ID.

Sample table: employees


Sample table: departments


Sample table: locations


Sample Solution:

SELECT  first_name, last_name, employee_id, job_id  
FROM employees  
WHERE department_id = 
(SELECT department_id  
FROM departments 
WHERE location_id = 
(SELECT location_id 
FROM locations  
WHERE city ='Toronto'));

Sample Output:

first_name	last_name	employee_id	job_id
Michael		Hartstein	201		MK_MAN
Pat		Fay		202		MK_REP

Pictorial Presentation:

SQL Subqueries: Display the employee name, employee id, and job title for all employees whose department location is Toronto.

Practice Online


Query Visualization:

Duration:

Query visualization of Display the employee name, employee id, and job title for all employees whose department location is Toronto - Duration

Rows:

Query visualization of Display the employee name, employee id, and job title for all employees whose department location is Toronto - Rows

Cost:

Query visualization of Display the employee name, employee id, and job title for all employees whose department location is Toronto - Cost

Contribute your code and comments through Disqus.

Previous: From the following tables, write a SQL query to find those employees who earn more than the average salary and work in a department with any employee whose first name contains a character a 'J'. Return employee ID, first name and salary.
Next: From the following table, write a SQL query to find those employees whose salary is lower than any salary of those employees whose job title is 'MK_MAN'. Return employee ID, first name, last name, job 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