AdventureWorks Database: Records of employees, order on hiredate
30. From the following table write a query in SQL to retrieve records of employees. Order the output on year (default ascending order) of hiredate. Return BusinessEntityID, JobTitle, and HireDate.
Sample table: HumanResources.Employee
businessentityid|nationalidnumber|loginid |jobtitle |birthdate |maritalstatus|gender|hiredate |salariedflag|vacationhours|sickleavehours|currentflag|rowguid |modifieddate |organizationnode|
----------------+----------------+----------------------------+----------------------------------------+----------+-------------+------+----------+------------+-------------+--------------+-----------+------------------------------------+-----------------------+----------------+
1|295847284 |adventure-works\ken0 |Chief Executive Officer |1969-01-29|S |M |2009-01-14|true | 99| 69|true |f01251e5-96a3-448d-981e-0f99d789110d|2014-06-30 00:00:00.000|/ |
2|245797967 |adventure-works\terri0 |Vice President of Engineering |1971-08-01|S |F |2008-01-31|true | 1| 20|true |45e8f437-670d-4409-93cb-f9424a40d6ee|2014-06-30 00:00:00.000|/1/ |
3|509647174 |adventure-works\roberto0 |Engineering Manager |1974-11-12|M |M |2007-11-11|true | 2| 21|true |9bbbfb2c-efbb-4217-9ab7-f97689328841|2014-06-30 00:00:00.000|/1/1/ |
4|112457891 |adventure-works\rob0 |Senior Tool Designer |1974-12-23|S |M |2007-12-05|false | 48| 80|true |59747955-87b8-443f-8ed4-f8ad3afdf3a9|2014-06-30 00:00:00.000|/1/1/1/ |
5|695256908 |adventure-works\gail0 |Design Engineer |1952-09-27|M |F |2008-01-06|true | 5| 22|true |ec84ae09-f9b8-4a15-b4a9-6ccbab919b08|2014-06-30 00:00:00.000|/1/1/2/ |
6|998320692 |adventure-works\jossef0 |Design Engineer |1959-03-11|M |M |2008-01-24|true | 6| 23|true |e39056f1-9cd5-478d-8945-14aca7fbdcdd|2014-06-30 00:00:00.000|/1/1/3/ |
7|134969118 |adventure-works\dylan0 |Research and Development Manager |1987-02-24|M |M |2009-02-08|true | 61| 50|true |4f46deca-ef01-41fd-9829-0adab368e431|2014-06-30 00:00:00.000|/1/1/4/ |
8|811994146 |adventure-works\diane1 |Research and Development Engineer |1986-06-05|S |F |2008-12-29|true | 62| 51|true |31112635-663b-4018-b4a2-a685c0bf48a4|2014-06-30 00:00:00.000|/1/1/4/1/ |
9|658797903 |adventure-works\gigi0 |Research and Development Engineer |1979-01-21|M |F |2009-01-16|true | 63| 51|true |50b6cdc6-7570-47ef-9570-48a64b5f2ecf|2014-06-30 00:00:00.000|/1/1/4/2/ |
10|879342154 |adventure-works\michael6 |Research and Development Manager |1984-11-30|M |M |2009-05-03|true | 16| 64|true |eaa43680-5571-40cb-ab1a-3bf68f04459e|2014-06-30 00:00:00.000|/1/1/4/3/ |
-- more --
Sample Solution:
-- Selecting specific columns from the Employee table
SELECT BusinessEntityID, JobTitle, HireDate
-- From the HumanResources schema's Employee table
FROM HumanResources.Employee
-- Ordering the results by the year portion of the HireDate
-- DATE_PART function extracts a part of the date (in this case, the year)
-- 'year' argument specifies that we want the year portion
ORDER BY DATE_PART('year',HireDate);
Explanation:
- The SQL query retrieves data from the Employee table within the HumanResources schema.
- It selects three columns: BusinessEntityID, JobTitle, and HireDate.
- The ORDER BY clause sorts the results based on the year portion of the HireDate.
- The DATE_PART function is used to extract the year from the HireDate column.
- 'year' specifies that we're interested in extracting the year portion.
- The result set will be ordered in ascending order based on the year an employee was hired.
Sample Output:
businessentityid|jobtitle |hiredate |
----------------+----------------------------------------+----------+
28|Production Technician - WC60 |2006-06-30|
17|Marketing Assistant |2007-01-26|
16|Marketing Manager |2007-12-20|
3|Engineering Manager |2007-11-11|
40|Production Supervisor - WC60 |2007-12-26|
4|Senior Tool Designer |2007-12-05|
12|Tool Designer |2007-12-11|
194|Production Technician - WC40 |2008-12-12|
193|Production Technician - WC40 |2008-12-06|
188|Production Technician - WC45 |2008-12-27|
187|Production Technician - WC45 |2008-12-09|
93|Production Supervisor - WC50 |2008-12-27|
94|Production Technician - WC50 |2008-12-12|
95|Production Technician - WC50 |2008-12-19|
235|Human Resources Manager |2008-12-06|
228|Maintenance Supervisor |2008-12-14|
42|Production Technician - WC60 |2008-12-27|
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Fetch result set on an unspecified column.
Next: Retrieve persons last name begins with R.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics