SQL Exercise: Employees who contain a letter z to their first name

SQL JOINS on HR Database: Exercise-5 with Solution

5. From the following tables, write a SQL query to find those employees whose first name contains the letter ‘z’. Return first name, last name, department, city, and state province.

Sample table: departments

Sample table: employees

Sample table: locations

Sample Solution:

-- Selecting specific columns (E.first_name, E.last_name, D.department_name, L.city, L.state_province) from the 'employees' table, aliased as 'E', the 'departments' table, aliased as 'D', and the 'locations' table, aliased as 'L'
SELECT E.first_name, E.last_name, D.department_name, L.city, L.state_province 

-- Performing an INNER JOIN between the 'employees' table (aliased as 'E') and the 'departments' table (aliased as 'D') based on the 'department_id' column
FROM employees E 

JOIN departments D  
  ON E.department_id = D.department_id 

-- Performing another INNER JOIN between the 'departments' table (aliased as 'D') and the 'locations' table (aliased as 'L') based on the 'location_id' column
JOIN locations L 
  ON D.location_id = L.location_id 

-- Filtering rows based on the condition that the 'first_name' column contains the letter 'z'
WHERE E.first_name LIKE '%z%';

Sample Output:

first_name	last_name	department_name	city		   state_province
Mozhe		Atkinson	Shipping	South San Francisco	California
Hazel		Philtanker	Shipping	South San Francisco	California
Elizabeth	Bates		Sales		OX9 9ZB			Oxford

Code Explanation:

The said query in SQL that selects the first name, last name, department name, city, and state/province of employees whose first name contains the letter "z". The query retrieves data from the employees, departments, and locations tables.
The first JOIN clause joins the employees table with the departments table using the department_id column, which is common to both tables and the second JOIN clause joins the departments table with the locations table using the location_id column, which is common to both tables.
The WHERE clause filters the result set to include only those employees whose first name contains the letter "z". The % symbol is a wildcard character that matches any sequence of characters, so %z% matches any string that contains the letter "z".

Relational Algebra Expression:

Relational Algebra Tree:

Visual Presentation:

Alternative Solutions:

Using WHERE Clause with Subquery:

SELECT E.first_name, E.last_name, D.department_name, L.city, L.state_province
FROM employees E, departments D, locations L
WHERE E.department_id = D.department_id
AND D.location_id = L.location_id
AND E.first_name LIKE '%z%';

Using ANSI-92 JOIN Syntax and WHERE Clause:

SELECT E.first_name, E.last_name, D.department_name, L.city, L.state_province
FROM employees E
JOIN departments D ON E.department_id = D.department_id
JOIN locations L ON D.location_id = L.location_id
WHERE E.first_name LIKE '%z%';

Using Subquery with EXISTS Clause:

SELECT E.first_name, E.last_name, D.department_name, L.city, L.state_province
FROM employees E
JOIN departments D ON E.department_id = D.department_id
JOIN locations L ON D.location_id = L.location_id
WHERE EXISTS (
    SELECT 1
    FROM employees
    WHERE first_name LIKE '%z%'
    AND employee_id = E.employee_id
);

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Display all employees for departments 80 or 40.
Next SQL Exercise: Departments which does not have any employee.