w3resource

SQL Subquery Exercises: Display the employee name and hiredate for all employees in the same department as Clara. Exclude Clara

SQL SUBQUERY: Exercise-13 with Solution

From the following tables, write a SQL query to find those employees who work in the same department as ‘Clara’. Exclude all those records where first name is ‘Clara’. Return first name, last name and hire date.

Sample table: employees


Sample Solution:

SELECT first_name, last_name, hire_date  
FROM employees  
WHERE department_id =  
( SELECT department_id  
FROM employees  
WHERE first_name = 'Clara')  
AND first_name <> 'Clara';

Sample Output:

first_name	last_name	hire_date
John		Russell		2004-10-01
Karen		Partners	2005-01-05
Alberto		Errazuriz	2005-03-10
Gerald		Cambrault	2007-10-15
Eleni		Zlotkey		2008-01-29
Peter		Tucker		2005-01-30
David		Bernstein	2005-03-24
Peter		Hall		2005-08-20
Christopher	Olsen		2006-03-30
Nanette		Cambrault	2006-12-09
Oliver		Tuvault		2007-11-23
Janette		King		2004-01-30
Patrick		Sully		2004-03-04
Allan		McEwen		2004-08-01
Lindsey		Smith		2005-03-10
Louise		Doran		2005-12-15
Sarath		Sewall		2006-11-03
Danielle	Greene		2007-03-19
Mattea		Marvins		2008-01-24
David		Lee	        2008-02-23
Sundar		Ande		2008-03-24
Amit		Banda		2008-04-21
Lisa		Ozer		2005-03-11
Harrison	Bloom		2006-03-23
Tayler		Fox		2006-01-24
William		Smith		2007-02-23
Elizabeth	Bates		2007-03-24
Sundita		Kumar		2008-04-21
Ellen		Abel		2004-05-11
Alyssa		Hutton		2005-03-19
Jonathon	Taylor		2006-03-24
Jack		Livingston	2006-04-23
Charles		Johnson		2008-01-04

Pictorial Presentation:

SQL Subqueries: Display the employee name and hiredate for all employees in the same department as Clara. Exclude Clara.

Practice Online


Query Visualization:

Duration:

Query visualization of Display the employee name and hiredate for all employees in the same department as Clara. Exclude Clara - Duration

Rows:

Query visualization of Display the employee name and hiredate for all employees in the same department as Clara. Exclude Clara - Rows

Cost:

Query visualization of Display the employee name and hiredate for all employees in the same department as Clara. Exclude Clara - Cost

Contribute your code and comments through Disqus.

Previous: From the following table, write a SQL query to find those employees who get second-highest salary. Return all the fields of the employees.
Next: From the following tables, write a SQL query to find those employees who work in a department where the employee’s first name contains a letter 'T'. Return employee ID, first name and last name.

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