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.
What is the difficulty level of this exercise?
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics