w3resource
SQL exercises

SQL JOINS on HR Database: Display the first name, last name, department number, and department name for each employee

SQL JOINS on HR Database: Exercise-1 with Solution

1. Write a query in SQL to display the first name, last name, department number, and department name for each employee.

Sample table: departments


Sample table: employees


Sample Solution:

SELECT E.first_name , E.last_name , 
       E.department_id , D.department_name 
        FROM employees E 
         JOIN departments D 
          ON E.department_id = D.department_id;

Sample Output:

first_name	last_name	department_id	department_name
Steven		King		90		Executive
Neena		Kochhar		90		Executive
Lex		De Haan		90		Executive
Alexander	Hunold		60		IT
Bruce		Ernst		60		IT
David		Austin		60		IT
Valli		Pataballa	60		IT
Diana		Lorentz		60		IT
Nancy		Greenberg	100		Finance
Daniel		Faviet		100		Finance
John		Chen		100		Finance
Ismael		Sciarra		100		Finance
Jose 	Manuel	Urman		100		Finance
Luis		Popp		100		Finance
Den		Raphaely	30		Purchasing
Alexander	Khoo		30		Purchasing
Shelli		Baida		30		Purchasing
Sigal		Tobias		30		Purchasing
Guy		Himuro		30		Purchasing
Karen		Colmenares	30		Purchasing
Matthew		Weiss		50		Shipping
Adam		Fripp		50		Shipping
Payam		Kaufling	50		Shipping
Shanta		Vollman		50		Shipping
Kevin		Mourgos		50		Shipping
Julia		Nayer		50		Shipping
Irene		Mikkilineni	50		Shipping
James		Landry		50		Shipping
Steven		Markle		50		Shipping
Laura		Bissot		50		Shipping
Mozhe		Atkinson	50		Shipping
James		Marlow		50		Shipping
TJ		Olson		50		Shipping
Jason		Mallin		50		Shipping
Michael		Rogers		50		Shipping
Ki		Gee		50		Shipping
Hazel		Philtanker	50		Shipping
Renske		Ladwig		50		Shipping
Stephen		Stiles		50		Shipping
John		Seo		50		Shipping
Joshua		Patel		50		Shipping
Trenna		Rajs		50		Shipping
Curtis		Davies		50		Shipping
Randall		Matos		50		Shipping
Peter		Vargas		50		Shipping
John		Russell		80		Sales
Karen		Partners	80		Sales
Alberto		Errazuriz	80		Sales
Gerald		Cambrault	80		Sales
Eleni		Zlotkey		80		Sales
Peter		Tucker		80		Sales
David		Bernstein	80		Sales
Peter		Hall		80		Sales
Christopher	Olsen		80		Sales
Nanette		Cambrault	80		Sales
Oliver		Tuvault		80		Sales
Janette		King		80		Sales
Patrick		Sully		80		Sales
Allan		McEwen		80		Sales
Lindsey		Smith		80		Sales
Louise		Doran		80		Sales
Sarath		Sewall		80		Sales
Clara		Vishney		80		Sales
Danielle	Greene		80		Sales
Mattea		Marvins		80		Sales
David		Lee		80		Sales
Sundar		Ande		80		Sales
Amit		Banda		80		Sales
Lisa		Ozer		80		Sales
Harrison	Bloom		80		Sales
Tayler		Fox		80		Sales
William		Smith		80		Sales
Elizabeth	Bates		80		Sales
Sundita		Kumar		80		Sales
Ellen		Abel		80		Sales
Alyssa		Hutton		80		Sales
Jonathon	Taylor		80		Sales
Jack		Livingston	80		Sales
Charles		Johnson		80		Sales
Winston		Taylor		50		Shipping
Jean		Fleaur		50		Shipping
Martha		Sullivan	50		Shipping
Girard		Geoni		50		Shipping
Nandita		Sarchand	50		Shipping
Alexis		Bull		50		Shipping
Julia		Dellinger	50		Shipping
Anthony		Cabrio		50		Shipping
Kelly		Chung		50		Shipping
Jennifer	Dilly		50		Shipping
Timothy		Gates		50		Shipping
Randall		Perkins		50		Shipping
Sarah		Bell		50		Shipping
Britney		Everett		50		Shipping
Samuel		McCain		50		Shipping
Vance		Jones		50		Shipping
Alana		Walsh		50		Shipping
Kevin		Feeney		50		Shipping
Donald		OConnell	50		Shipping
Douglas		Grant		50		Shipping
Jennifer	Whalen		10		Administration
Michael		Hartstein	20		Marketing
Pat		Fay		20		Marketing
Susan		Mavris		40		Human Resources
Hermann		Baer		70		Public Relations
Shelley		Higgins		110		Accounting
William		Gietz		110		Accounting

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the first name, last name, department number, and department name for each employee - Duration

Rows:

Query visualization of Display the first name, last name, department number, and department name for each employee - Rows

Cost:

Query visualization of Display the first name, last name, department number, and department name for each employee - Cost

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

Previous: SQL JOINS on HR Database Exercises Home
Next: Write a query in SQL to display the first and last name, department, city, and state province for each employee.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming