SQL JOINS on HR Database: Display the first and last name, department, city, and state province for each employee
SQL JOINS on HR Database: Exercise-2 with Solution
2. From the following tables, write a SQL query to find the first name, last name, department, city, and state province for each employee.
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;
Sample Output:
first_name last_name department_name city state_province Steven King Executive Seattle Washington Neena Kochhar Executive Seattle Washington Lex De Haan Executive Seattle Washington Alexander Hunold IT Southlake Texas Bruce Ernst IT Southlake Texas David Austin IT Southlake Texas Valli Pataballa IT Southlake Texas Diana Lorentz IT Southlake Texas Nancy Greenberg Finance Seattle Washington Daniel Faviet Finance Seattle Washington John Chen Finance Seattle Washington Ismael Sciarra Finance Seattle Washington Jose Manuel Urman Finance Seattle Washington Luis Popp Finance Seattle Washington Den Raphaely Purchasing Seattle Washington Alexander Khoo Purchasing Seattle Washington Shelli Baida Purchasing Seattle Washington Sigal Tobias Purchasing Seattle Washington Guy Himuro Purchasing Seattle Washington Karen Colmenares Purchasing Seattle Washington Matthew Weiss Shipping South San Francisco California Adam Fripp Shipping South San Francisco California Payam Kaufling Shipping South San Francisco California Shanta Vollman Shipping South San Francisco California Kevin Mourgos Shipping South San Francisco California Julia Nayer Shipping South San Francisco California Irene Mikkilineni Shipping South San Francisco California James Landry Shipping South San Francisco California Steven Markle Shipping South San Francisco California Laura Bissot Shipping South San Francisco California Mozhe Atkinson Shipping South San Francisco California James Marlow Shipping South San Francisco California TJ Olson Shipping South San Francisco California Jason Mallin Shipping South San Francisco California Michael Rogers Shipping South San Francisco California Ki Gee Shipping South San Francisco California Hazel Philtanker Shipping South San Francisco California Renske Ladwig Shipping South San Francisco California Stephen Stiles Shipping South San Francisco California John Seo Shipping South San Francisco California Joshua Patel Shipping South San Francisco California Trenna Rajs Shipping South San Francisco California Curtis Davies Shipping South San Francisco California Randall Matos Shipping South San Francisco California Peter Vargas Shipping South San Francisco California John Russell Sales OX9 9ZB Oxford Karen Partners Sales OX9 9ZB Oxford Alberto Errazuriz Sales OX9 9ZB Oxford Gerald Cambrault Sales OX9 9ZB Oxford Eleni Zlotkey Sales OX9 9ZB Oxford Peter Tucker Sales OX9 9ZB Oxford David Bernstein Sales OX9 9ZB Oxford Peter Hall Sales OX9 9ZB Oxford Christopher Olsen Sales OX9 9ZB Oxford Nanette Cambrault Sales OX9 9ZB Oxford Oliver Tuvault Sales OX9 9ZB Oxford Janette King Sales OX9 9ZB Oxford Patrick Sully Sales OX9 9ZB Oxford Allan McEwen Sales OX9 9ZB Oxford Lindsey Smith Sales OX9 9ZB Oxford Louise Doran Sales OX9 9ZB Oxford Sarath Sewall Sales OX9 9ZB Oxford Clara Vishney Sales OX9 9ZB Oxford Danielle Greene Sales OX9 9ZB Oxford Mattea Marvins Sales OX9 9ZB Oxford David Lee Sales OX9 9ZB Oxford Sundar Ande Sales OX9 9ZB Oxford Amit Banda Sales OX9 9ZB Oxford Lisa Ozer Sales OX9 9ZB Oxford Harrison Bloom Sales OX9 9ZB Oxford Tayler Fox Sales OX9 9ZB Oxford William Smith Sales OX9 9ZB Oxford Elizabeth Bates Sales OX9 9ZB Oxford Sundita Kumar Sales OX9 9ZB Oxford Ellen Abel Sales OX9 9ZB Oxford Alyssa Hutton Sales OX9 9ZB Oxford Jonathon Taylor Sales OX9 9ZB Oxford Jack Livingston Sales OX9 9ZB Oxford Charles Johnson Sales OX9 9ZB Oxford Winston Taylor Shipping South San Francisco California Jean Fleaur Shipping South San Francisco California Martha Sullivan Shipping South San Francisco California Girard Geoni Shipping South San Francisco California Nandita Sarchand Shipping South San Francisco California Alexis Bull Shipping South San Francisco California Julia Dellinger Shipping South San Francisco California Anthony Cabrio Shipping South San Francisco California Kelly Chung Shipping South San Francisco California Jennifer Dilly Shipping South San Francisco California Timothy Gates Shipping South San Francisco California Randall Perkins Shipping South San Francisco California Sarah Bell Shipping South San Francisco California Britney Everett Shipping South San Francisco California Samuel McCain Shipping South San Francisco California Vance Jones Shipping South San Francisco California Alana Walsh Shipping South San Francisco California Kevin Feeney Shipping South San Francisco California Donald OConnell Shipping South San Francisco California Douglas Grant Shipping South San Francisco California Jennifer Whalen Administration Seattle Washington Michael Hartstein Marketing Toronto Ontario Pat Fay Marketing Toronto Ontario Susan Mavris Human Resources London Hermann Baer Public Relations Munich Bavaria Shelley Higgins Accounting Seattle Washington William Gietz Accounting Seattle Washington
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 the first name, last name, department number, and department name for each employee.
Next: From the following table, write a SQL query to find the first name, last name, salary, and job grade for all employees.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework