AdventureWorks Database: Return the employee with the fewest number of vacation hours
SQL Query - AdventureWorks: Exercise-103 with Solution
103. From the following table write a query in SQL to return the employee with the fewest number of vacation hours compared to other employees with the same job title. Partitions the employees by job title and apply the first value to each partition independently.
Sample table: HumanResources.Employee
Sample table: Person.Person
Sample Solution:
SELECT JobTitle, LastName, VacationHours,
FIRST_VALUE(LastName) OVER (PARTITION BY JobTitle
ORDER BY VacationHours ASC
ROWS UNBOUNDED PRECEDING
) AS FewestVacationHours
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID
ORDER BY JobTitle;
Sample Output:
jobtitle |lastname |vacationhours|fewestvacationhours| ----------------------------------------+-----------------+-------------+-------------------+ Accountant |Moreland | 58|Moreland | Accountant |Seamans | 59|Moreland | Accounts Manager |Liu | 57|Liu | Accounts Payable Specialist |Tomic | 63|Tomic | Accounts Payable Specialist |Sheperdigian | 64|Tomic | Accounts Receivable Specialist |Poe | 60|Poe | Accounts Receivable Specialist |Spoon | 61|Poe | Accounts Receivable Specialist |Walton | 62|Poe | Application Specialist |Bueno | 71|Bueno | Application Specialist |Bacon | 72|Bueno | Application Specialist |Meyyappan | 73|Bueno | Application Specialist |Berg | 74|Bueno | ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Return the name of the product that is the least expensive.
Next: Return the difference in sales quotas for a specific employee over previous years.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
How to request a random row in SQL?
Select a random row with MySQL:
SELECT column FROM table ORDER BY RAND() LIMIT 1
Select a random row with PostgreSQL:
SELECT column FROM table ORDER BY RANDOM() LIMIT 1
Select a random row with Microsoft SQL Server:
SELECT TOP 1 column FROM table ORDER BY NEWID()
Select a random row with IBM DB2:
SELECT column, RAND() as IDX FROM table ORDER BY IDX FETCH FIRST 1 ROWS ONLY
Select a random record with Oracle:
SELECT column FROM ( SELECT column FROM table ORDER BY dbms_random.value ) WHERE rownum = 1
Database: SQL Server, PostgreSQL Server, MySQL
Ref: https://bit.ly/39n35HP
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook