:::: MENU ::::

Posts Categorized / T-SQL Interview Questions

  • Jan 07 / 2010
  • 0
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

Finding Nth highest value in a column through T-SQL

Here is a situation, to find a specific highest value in a column. Most common example may be that of a salary column where one may need 7th highest salary.
To make short of this short problem i have selected pubs..Sales.qty column to work with.
Apply following query and you will get total 11 distinct values arranged

 
USE Pubs  
 GO  
 SELECT DISTINCT qty  
 FROM sales  
 ORDER by qty DESC  
 GO  

Now suppose we have to find 7th highest value in qty column. The simple query for this is

 SELECT min(qty)  
 FROM sales  
 WHERE qty IN  
 (SELECT distinct TOP 7 qty  
 FROM sales  
 ORDER BY qty DESC)  
 GO  

So its done.
It is appropriate to mention here another query that i found on a blog.

 
SELECT TOP 1 qty  
 FROM ( SELECT DISTINCT TOP 7 qty  
 FROM sales  
 ORDER BY qty DESC) ABC  
 ORDER BY qty ASC  
 GO  

So in above queries you may find nth highest value where n is always greater than 1.
That is all for finding nth highest value in a column.

  • Jan 07 / 2010
  • 0
dbDigger, Null Values, T-SQL Interview Questions

Using order by with nulls in T-SQL

You have a table with EmpName and DateOfLeaving columns.
The EmpName column is mandatory, but DateOfleaving will have null value for those employees who have not left the company.
The question is to have list off all employees with the employees with DateOfLeaving coming last and all others sorted ascending order of date of leaving, Name sorted in ascending order for each group.
Consider the Example:

Table: Employees

EmpName DateOfLeaving

Abc 10 Oct 1999

Bcd 11 Nov 1998

Ccd null

Dcd 10 Aug 2000

Eed null

The solution should be:

EmpName DateOfLeaving

Bcd 11 Nov 1998

Abc 10 Oct 1999

Dcd 10 Aug 2000

Ccd null

Eed null

Which query will produce this output?

Answer:

  • Select EmpName, DateOfLeaving from Employees order by isnull(DateOfLeaving,’10/10/9999’),EmpName asc
  • Select EmpName, DateOfLeaving from Employees order by DateOfLeaving desc, EmpName asc

Explanation:
In SQL Server T-SQL, by default, a query takes null as first when the order is ascending (the default). So we just give some value of greater date in order by so that it will come at last.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Jan 07 / 2010
  • 0
DBA Interview questions, dbDigger, SQL Server Agent scheduled Jobs, T-SQL Interview Questions

SQLAgent status through T-SQL

Question:

Using Transact-SQL, how can you find out whether SQLAgent is running?

Answer:

  • sysprocesses table for program_name like SQLAgent%
  • Use xp_servicecontrol querystate SQLSERVERAGENT

Explanation: Querying the sysprocesses table will return a row if sql agent is running, else it will not return any rows. The xp_servicecontrol procedure will return the status of the service (running/stopped) on 2005 / 2008 editions except express. On a SQL Server Express instance this command will return an error. So you need to check for errors as well as the status to determine whether the agent is running.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Jan 07 / 2010
  • 0
dbDigger, T-SQL Interview Questions

union for different data types in T-SQL

What happens with this code?

select ‘B’

union

select 4

Answer: The varchar is converted in int (error results)

Explanation: This is an example of an implicit conversion in T-SQL. In this case, an error is returned as the varchar is converted to an int, or an attempt is made, which returns an error. The int is of higher precedence than a varchar, so that is the order of conversions.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Jan 07 / 2010
  • 1
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

Call it ad-hoc or out of box thinking

Although is does not always work but, some times ad-hoc thinking makes the solution very simple. Consider the following scenario for which a very simplified solution is provided for. We may call it out of box thinking as well.

Question:
Is there a condition for the WHERE clause to display the details about the students born in the month numbers of 1[Jan], 3,4,5,7,8? The condition should not use both IN and OR operators.

Answer: Yes

Explanation: We can give the condition as follows:
where datename(m,dob) not like ‘%e%’
because these month(s) specified do not have the letter ‘e’, but all the other months have at least one ‘e’ in month name.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Jan 07 / 2010
  • 0
Data Modeling and Database Design, Data Types, DBA Interview questions, dbDigger, T-SQL Interview Questions

Find columns with identity property

Question:

In SQL Server 2005, how can you easily determine which columns have the identity property set?

Answer:

  • Query sys.identity_columns for the rows.
  • Query sys.columns.is_identity for a value of 1

Explanation: There is a table, sys.identity_columns that contains a row for each column in your database that has the identity property set. There is also a column called is_identity in the sys.columns view that contains a 1 if the column has the identity property set.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

Consult us to explore the Databases. Contact us