Answers - Class 3

Let’s tack some functionality on now and try to answer some queries:

Who has been employed the longest?

SELECT TOP 1 last_name, first_name, hire_date
FROM people
ORDER BY hire_date

Who has been employed the shortest?

SELECT TOP 1 last_name, first_name, hire_date
FROM people
ORDER BY hire_date DESC

Which people do not have email addresses listed?

SELECT last_name, first_name, email
FROM people
WHERE email IS NULL

How many entries in the people table?

SELECT COUNT(*) FROM people

Who is vested in the insurance plan (assume 20 years)?

SELECT last_name, first_name, hire_date, DATEADD(year, 20, hire_date) AS vested
FROM people
WHERE  DATEADD(year, 20, hire_date) <= CURRENT_TIMESTAMP
ORDER BY vested DESC, hire_date

DATEADD(interval, number, date) Reference

Was anyone hired today?

SELECT TOP 30 last_name, first_name, hire_date
FROM people
WHERE hire_date = CAST(CURRENT_TIMESTAMP AS DATE)

CAST(field AS type)

Does anyone have a work anniversary today? If so, how long?

SELECT TOP 30 id, last_name, first_name, hire_date, DATEDIFF(year, hire_date, CURRENT_TIMESTAMP) AS years
FROM people
WHERE DATEPART(d, hire_date) = DATEPART(d, GetDate()) 
  AND DATEPART(m, hire_date) = DATEPART(m, GetDate())
ORDER BY years DESC

DATEDIFF(part, start_date, end_date) Reference

DATEPART(part, field) where part is d = day, m = month, y = year, etc. Reference

---

Back to Class 3 Contents