SQL Exercises: Employees in departments in the United Kingdom
SQL SUBQUERY: Exercise-27 with Solution
From the following tables, write a SQL query to find employees who work in departments located in the United Kingdom. Return first name.
Sample table: employees
Sample table: departments
Sample table: locations
Sample table: countries
SELECT first_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id IN (SELECT location_id FROM locations WHERE country_id = (SELECT country_id FROM countries WHERE country_name='United Kingdom')));
This is a SQL query used to retrieve the first names of employees from the 'employees' table based on the departments, locations, and countries they belong to.
The query starts by selecting the "first_name" column from the 'employees' table. The subquery in the WHERE clause retrieves the department IDs from the "departments" table where the location IDs belong to locations in the country "United Kingdom". The location IDs are retrieved using another subquery that selects the location IDs from the 'locations' table based on the country ID for "United Kingdom". The country ID is retrieved from the 'countries' table using yet another subquery.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Departments that have one or more employees.
Next SQL Exercise: IT employees who earn more than the average.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
How to request a random row in SQL?
Select a random row with MySQL:
SELECT column FROM table ORDER BY RAND() LIMIT 1
Select a random row with PostgreSQL:
SELECT column FROM table ORDER BY RANDOM() LIMIT 1
Select a random row with Microsoft SQL Server:
SELECT TOP 1 column FROM table ORDER BY NEWID()
Select a random row with IBM DB2:
SELECT column, RAND() as IDX FROM table ORDER BY IDX FETCH FIRST 1 ROWS ONLY
Select a random record with Oracle:
SELECT column FROM ( SELECT column FROM table ORDER BY dbms_random.value ) WHERE rownum = 1
Database: SQL Server, PostgreSQL Server, MySQL
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook