T-SQL - Sample Interview Question (Senior Level)

By querying the HumanResources.EmployeePayHistory table of the AdventureWorks2012 database, you want to generate a ranked list of pay rates for employees who are paid every two weeks. Employees should be ranked sequentially even if they have the same rate.

Which of the following Transact-SQL statements can you use to accomplish this task?

  1. SELECT BusinessEntityID, Rate, ROW_NUMBER() OVER (ORDER BY Rate DESC) as Rank FROM Adventureworks2012.HumanResources.EmployeePayHistory WHERE PayFrequency = 2;
  2. SELECT BusinessEntityID, Rate, DENSE_RANK() OVER (ORDER BY Rate DESC) as Rank FROM Adventureworks2012.HumanResources.EmployeePayHistory WHERE PayFrequency = 2;
  3. SELECT BusinessEntityID, Rate, NTILE(5) OVER (ORDER BY Rate DESC) as Rank FROM Adventureworks2012.HumanResources.EmployeePayHistory WHERE PayFrequency = 2;
  4. SELECT BusinessEntityID, Rate, RANK() OVER (ORDER BY Rate DESC) as Rank FROM Adventureworks2012.HumanResources.EmployeePayHistory WHERE PayFrequency = 2;
Tests in T-SQL for Seniors

Test Name Difficulty Time (minutes) No. of Questions
T-SQL Online Test for Seniors T-SQL Senior 60 30

More Specific Tests in T-SQL for Seniors

Test Name Difficulty Time (Minutes) No. of Questions
SQL-Server-SQL Online Test for Seniors SQL Server - SQL Senior 20 10