AdventureWorks Database: Records of employees, order on hiredate

SQL Query - AdventureWorks: Exercise-30 with Solution

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.

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 --

Click to view Full table

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.