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:

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.