w3resource

MySQL Date and Time Exercises: Query to get employee ID, last name, and date of first salary of the employees

MySQL Date Time: Exercise-19 with Solution

Write a MySQL query to get employee ID, last name, and date of first salary of the employees.

Sample table: employees

Code:

 -- This SQL query retrieves the employee ID, last name, hire date, and the last day of the month for each hire date.

SELECT 
employee_id, -- Selecting the 'employee_id' column from the 'employees' table.
last_name, -- Selecting the 'last_name' column from the 'employees' table.
hire_date, -- Selecting the 'hire_date' column from the 'employees' table.
    LAST_DAY(hire_date) -- Using the LAST_DAY() function to calculate the last day of the month for each hire date.
FROM 
employees; -- Specifying the 'employees' table.

Explanation:

  • This SQL query retrieves the employee ID, last name, hire date, and the last day of the month for each hire date.
  • The LAST_DAY() function is used to calculate the last day of the month for each hire date.
  • The result set includes columns for employee ID, last name, hire date, and the last day of the month.

Sample Output:

employee_id	last_name		hire_date		LAST_DAY(hire_date)
100		King		1987-06-17T04:00:00.000Z	1987-06-30T04:00:00.000Z
101		Kochhar		1987-06-18T04:00:00.000Z	1987-06-30T04:00:00.000Z
102		De Haan		1987-06-19T04:00:00.000Z	1987-06-30T04:00:00.000Z
103		Hunold		1987-06-20T04:00:00.000Z	1987-06-30T04:00:00.000Z
104		Ernst		1987-06-21T04:00:00.000Z	1987-06-30T04:00:00.000Z
105		Austin		1987-06-22T04:00:00.000Z	1987-06-30T04:00:00.000Z
106		Pataballa	1987-06-23T04:00:00.000Z	1987-06-30T04:00:00.000Z
107		Lorentz		1987-06-24T04:00:00.000Z	1987-06-30T04:00:00.000Z
108		Greenberg	1987-06-25T04:00:00.000Z	1987-06-30T04:00:00.000Z
109		Faviet		1987-06-26T04:00:00.000Z	1987-06-30T04:00:00.000Z
110		Chen		1987-06-27T04:00:00.000Z	1987-06-30T04:00:00.000Z
111		Sciarra		1987-06-28T04:00:00.000Z	1987-06-30T04:00:00.000Z
112		Urman		1987-06-29T04:00:00.000Z	1987-06-30T04:00:00.000Z
113		Popp		1987-06-30T04:00:00.000Z	1987-06-30T04:00:00.000Z
114		Raphaely	1987-07-01T04:00:00.000Z	1987-07-31T04:00:00.000Z
115		Khoo		1987-07-02T04:00:00.000Z	1987-07-31T04:00:00.000Z
116		Baida		1987-07-03T04:00:00.000Z	1987-07-31T04:00:00.000Z
117		Tobias		1987-07-04T04:00:00.000Z	1987-07-31T04:00:00.000Z
118		Himuro		1987-07-05T04:00:00.000Z	1987-07-31T04:00:00.000Z
119		Colmenares	1987-07-06T04:00:00.000Z	1987-07-31T04:00:00.000Z
120		Weiss		1987-07-07T04:00:00.000Z	1987-07-31T04:00:00.000Z
121		Fripp		1987-07-08T04:00:00.000Z	1987-07-31T04:00:00.000Z
122		Kaufling	1987-07-09T04:00:00.000Z	1987-07-31T04:00:00.000Z
123		Vollman		1987-07-10T04:00:00.000Z	1987-07-31T04:00:00.000Z
124		Mourgos		1987-07-11T04:00:00.000Z	1987-07-31T04:00:00.000Z
125		Nayer		1987-07-12T04:00:00.000Z	1987-07-31T04:00:00.000Z
126		Mikkilineni	1987-07-13T04:00:00.000Z	1987-07-31T04:00:00.000Z
127		Landry		1987-07-14T04:00:00.000Z	1987-07-31T04:00:00.000Z
128		Markle		1987-07-15T04:00:00.000Z	1987-07-31T04:00:00.000Z
129		Bissot		1987-07-16T04:00:00.000Z	1987-07-31T04:00:00.000Z
130		Atkinson	1987-07-17T04:00:00.000Z	1987-07-31T04:00:00.000Z
131		Marlow		1987-07-18T04:00:00.000Z	1987-07-31T04:00:00.000Z
132		Olson		1987-07-19T04:00:00.000Z	1987-07-31T04:00:00.000Z
133		Mallin		1987-07-20T04:00:00.000Z	1987-07-31T04:00:00.000Z
134		Rogers		1987-07-21T04:00:00.000Z	1987-07-31T04:00:00.000Z
135		Gee		1987-07-22T04:00:00.000Z	1987-07-31T04:00:00.000Z
136		Philtanker	1987-07-23T04:00:00.000Z	1987-07-31T04:00:00.000Z
137		Ladwig		1987-07-24T04:00:00.000Z	1987-07-31T04:00:00.000Z
138		Stiles		1987-07-25T04:00:00.000Z	1987-07-31T04:00:00.000Z
139		Seo		1987-07-26T04:00:00.000Z	1987-07-31T04:00:00.000Z
140		Patel		1987-07-27T04:00:00.000Z	1987-07-31T04:00:00.000Z
141		Rajs		1987-07-28T04:00:00.000Z	1987-07-31T04:00:00.000Z
142		Davies		1987-07-29T04:00:00.000Z	1987-07-31T04:00:00.000Z
143		Matos		1987-07-30T04:00:00.000Z	1987-07-31T04:00:00.000Z
144		Vargas		1987-07-31T04:00:00.000Z	1987-07-31T04:00:00.000Z
145		Russell		1987-08-01T04:00:00.000Z	1987-08-31T04:00:00.000Z
146		Partners	1987-08-02T04:00:00.000Z	1987-08-31T04:00:00.000Z
147		Errazuriz	1987-08-03T04:00:00.000Z	1987-08-31T04:00:00.000Z
148		Cambrault	1987-08-04T04:00:00.000Z	1987-08-31T04:00:00.000Z
149		Zlotkey		1987-08-05T04:00:00.000Z	1987-08-31T04:00:00.000Z
150		Tucker		1987-08-06T04:00:00.000Z	1987-08-31T04:00:00.000Z
151		Bernstein	1987-08-07T04:00:00.000Z	1987-08-31T04:00:00.000Z
152		Hall		1987-08-08T04:00:00.000Z	1987-08-31T04:00:00.000Z
153		Olsen		1987-08-09T04:00:00.000Z	1987-08-31T04:00:00.000Z
154		Cambrault	1987-08-10T04:00:00.000Z	1987-08-31T04:00:00.000Z
155		Tuvault		1987-08-11T04:00:00.000Z	1987-08-31T04:00:00.000Z
156		King		1987-08-12T04:00:00.000Z	1987-08-31T04:00:00.000Z
157		Sully		1987-08-13T04:00:00.000Z	1987-08-31T04:00:00.000Z
158		McEwen		1987-08-14T04:00:00.000Z	1987-08-31T04:00:00.000Z
159		Smith		1987-08-15T04:00:00.000Z	1987-08-31T04:00:00.000Z
160		Doran		1987-08-16T04:00:00.000Z	1987-08-31T04:00:00.000Z
161		Sewall		1987-08-17T04:00:00.000Z	1987-08-31T04:00:00.000Z
162		Vishney		1987-08-18T04:00:00.000Z	1987-08-31T04:00:00.000Z
163		Greene		1987-08-19T04:00:00.000Z	1987-08-31T04:00:00.000Z
164		Marvins		1987-08-20T04:00:00.000Z	1987-08-31T04:00:00.000Z
165		Lee		1987-08-21T04:00:00.000Z	1987-08-31T04:00:00.000Z
166		Ande		1987-08-22T04:00:00.000Z	1987-08-31T04:00:00.000Z
167		Banda		1987-08-23T04:00:00.000Z	1987-08-31T04:00:00.000Z
168		Ozer		1987-08-24T04:00:00.000Z	1987-08-31T04:00:00.000Z
169		Bloom		1987-08-25T04:00:00.000Z	1987-08-31T04:00:00.000Z
170		Fox		1987-08-26T04:00:00.000Z	1987-08-31T04:00:00.000Z
171		Smith		1987-08-27T04:00:00.000Z	1987-08-31T04:00:00.000Z
172		Bates		1987-08-28T04:00:00.000Z	1987-08-31T04:00:00.000Z
173		Kumar		1987-08-29T04:00:00.000Z	1987-08-31T04:00:00.000Z
174		Abel		1987-08-30T04:00:00.000Z	1987-08-31T04:00:00.000Z
175		Hutton		1987-08-31T04:00:00.000Z	1987-08-31T04:00:00.000Z
176		Taylor		1987-09-01T04:00:00.000Z	1987-09-30T04:00:00.000Z
177		Livingston	1987-09-02T04:00:00.000Z	1987-09-30T04:00:00.000Z
178		Grant		1987-09-03T04:00:00.000Z	1987-09-30T04:00:00.000Z
179		Johnson		1987-09-04T04:00:00.000Z	1987-09-30T04:00:00.000Z
180		Taylor		1987-09-05T04:00:00.000Z	1987-09-30T04:00:00.000Z
181		Fleaur		1987-09-06T04:00:00.000Z	1987-09-30T04:00:00.000Z
182		Sullivan	1987-09-07T04:00:00.000Z	1987-09-30T04:00:00.000Z
183		Geoni		1987-09-08T04:00:00.000Z	1987-09-30T04:00:00.000Z
184		Sarchand	1987-09-09T04:00:00.000Z	1987-09-30T04:00:00.000Z
185		Bull		1987-09-10T04:00:00.000Z	1987-09-30T04:00:00.000Z
186		Dellinger	1987-09-11T04:00:00.000Z	1987-09-30T04:00:00.000Z
187		Cabrio		1987-09-12T04:00:00.000Z	1987-09-30T04:00:00.000Z
188		Chung		1987-09-13T04:00:00.000Z	1987-09-30T04:00:00.000Z
189		Dilly		1987-09-14T04:00:00.000Z	1987-09-30T04:00:00.000Z
190		Gates		1987-09-15T04:00:00.000Z	1987-09-30T04:00:00.000Z
191		Perkins		1987-09-16T04:00:00.000Z	1987-09-30T04:00:00.000Z
192		Bell		1987-09-17T04:00:00.000Z	1987-09-30T04:00:00.000Z
193		Everett		1987-09-18T04:00:00.000Z	1987-09-30T04:00:00.000Z
194		McCain		1987-09-19T04:00:00.000Z	1987-09-30T04:00:00.000Z
195		Jones		1987-09-20T04:00:00.000Z	1987-09-30T04:00:00.000Z
196		Walsh		1987-09-21T04:00:00.000Z	1987-09-30T04:00:00.000Z
197		Feeney		1987-09-22T04:00:00.000Z	1987-09-30T04:00:00.000Z
198		OConnell	1987-09-23T04:00:00.000Z	1987-09-30T04:00:00.000Z
199		Grant		1987-09-24T04:00:00.000Z	1987-09-30T04:00:00.000Z
200		Whalen		1987-09-25T04:00:00.000Z	1987-09-30T04:00:00.000Z
201		Hartstein	1987-09-26T04:00:00.000Z	1987-09-30T04:00:00.000Z
202		Fay		1987-09-27T04:00:00.000Z	1987-09-30T04:00:00.000Z
203		Mavris		1987-09-28T04:00:00.000Z	1987-09-30T04:00:00.000Z
204		Baer		1987-09-29T04:00:00.000Z	1987-09-30T04:00:00.000Z
205		Higgins		1987-09-30T04:00:00.000Z	1987-09-30T04:00:00.000Z
206		Gietz		1987-10-01T04:00:00.000Z	1987-10-31T05:00:00.000Z

Pictorial Presentation of the above query:

Pictorial: Query to get employee ID, last name, and date of first salary of the employees

MySQL Code Editor:

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

Previous:Write a MySQL query to get department name, manager name, and salary of the manager for all managers whose experience is more than 5 years.
Next:Write a MySQL query to get first name, hire date and experience of the employees.

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/mysql-exercises/date-time-exercises/write-a-query-to-get-employee-id-last-name-and-date-of-first-salary-of-the-employees.php