w3resource

SQL Subquery Exercises: Display the first and last name, and department code for all employees who work in the department Marketing

SQL SUBQUERY: Exercise-41 with Solution

From the following table, write a SQL query to find those employees who work in the department 'Marketing'. Return first name, last name and department ID.

Sample table: employees


Sample table: departments


Sample Solution:

SELECT first_name, last_name, department_id 
 FROM employees 
   WHERE department_id = 
       (SELECT department_id 
          FROM departments 
           WHERE department_name = 'Marketing');

Sample Output:

first_name	last_name	department_id
Michael		Hartstein	20
Pat		Fay		20

Pictorial Presentation:

SQL Subqueries: Display the first and last name, salary, and department ID for all those employees who work in that department where the employee works who hold the ID 201.

Practice Online


Query Visualization:

Duration:

Query visualization of Display the first and last name, and department code for all employees who work in the department Marketing - Duration

Rows:

Query visualization of Display the first and last name, and department code for all employees who work in the department Marketing - Rows

Cost:

Query visualization of Display the first and last name, and department code for all employees who work in the department Marketing - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following table, write a SQL query to find those employees whose salary matches to the salary of the employee who works in that department of ID 40. Return first name, last name, salary, and department ID.
Next: From the following table, write a SQL query to find those employees who earn more than the minimum salary of a department of ID 40. Return first name, last name, salary, and department ID.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

How to count occurrences of a column value in SQL?

Input table:

id | age
--------
0  | 25
1  | 25
2  | 23
SELECT age, count(age) 
  FROM Students 
 GROUP by age

Output:

id | age | count
----------------
0  | 25  | 2
1  | 25  | 2
2  | 23  | 1

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