w3resource

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 Expression: Display those employees who contain a letter z to their first name and also display their last name, department, city, and state province.

Relational Algebra Tree:

Relational Algebra Tree: Display those employees who contain a letter z to their first name and also display their last name, department, city, and state province.

Pictorial Presentation:

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

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display those employees who contain a letter z to their first name and also display their last name, department, city, and state province - Duration

Rows:

Query visualization of Display those employees who contain a letter z to their first name and also display their last name, department, city, and state province - Rows

Cost:

Query visualization of Display those employees who contain a letter z to their first name and also display their last name, department, city, and state province - 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.