SQL JOINS on HR Database: Display those employees who contain a letter z to their first name and also display their last name, department, city, and state province

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 a letter ‘z’. Return first name, last name, department, city, and state province.

Sample table: departments

Sample table: employees

Sample table: locations

Sample Solution:

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%';

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

Relational Algebra Expression:

Relational Algebra Tree:

Pictorial Presentation:

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous: From the following tables, write a SQL query to find all those employees who work in department ID 80 or 40. Return first name, last name, department number and department name.
Next: From the following table, write a SQL query to find all departments including those without any employee. Return first name, last name, department ID, department name.