w3resource

SQL Subquery Exercises: Display the department number, name, job and department name for all employees in the Finance department

SQL SUBQUERY: Exercise-6 with Solution

From the following tables, write a SQL query to find all those employees who work in the Finance department. Return department ID, name (first name), job ID and department name.

Sample table: employees


Sample table: departments


Sample Solution:

SELECT e.department_id, e.first_name, e.job_id , d.department_name  
FROM employees e , departments d  
WHERE e.department_id = d.department_id  
AND  d.department_name = 'Finance';

Sample Output:

department_id	first_name	job_id		department_name
100		Nancy		FI_MGR			Finance
100		Daniel		FI_ACCOUNT		Finance
100		John		FI_ACCOUNT		Finance
100		Ismael		FI_ACCOUNT		Finance
100		Jose Manuel	FI_ACCOUNT		Finance
100		Luis		FI_ACCOUNT		Finance

Relational Algebra Expression:

Relational Algebra Expression: Display the department number, name, job and department name for all employees in the Finance department.

Relational Algebra Tree:

Relational Algebra Tree: Display the department number, name, job and department name for all employees in the Finance department.

Pictorial Presentation:

SQL Subqueries: Display the department number, name, job and department name for all employees in the Finance department

Practice Online


Query Visualization:

Duration:

Query visualization of Display the department number, name, job and department name for all employees in the Finance department - Duration

Rows:

Query visualization of Display the department number, name, job and department name for all employees in the Finance department - Rows

Cost:

Query visualization of Display the department number, name, job and department name for all employees in the Finance department - Cost

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to find those employees who report that manager whose first name is ‘Payam’. Return first name, last name, employee ID and salary.
Next:From the following table, write a SQL query to find the employee whose salary is 3000 and reporting person’s ID is 121. Return all fields.

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