Some Popular SQL Queries

Home >> Dot Net Interview Questions >> Mix Dot Net Questions

Posted on May 27th , 2009 by Admin

(1)Write a SQL Query to find first Week Day of month?

SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay

(2)How to find 6th highest salary from Employee table

SELECT TOP 1 salary FROM (SELECT DISTINCT TOP 6 salary FROM employee ORDER BY salary DESC) a ORDER BY salary

(3)How to get number of column in table:--

select * from information_schema.columns where table_name='t

able_name'

sp_columns 'table_name'

(4)Delete duplicate row from table

INSERT INTO #temp SELECT DISTINCT * FROM yourtable

select * into #temp select distinct * from yourtable

truncate table yourtable

insert into yourtable select * from #temp

(5)display all undeleted rows.

SELECT * FROM Table WHERE isnull(Is_deleted,0) = 0

(6)How to Select top 3 salry from tables

SELECT TOP 3 emp_id, emp_name, salary , dept_id FROM employee WHERE dept_id = training dept ORDER BY salary DESC

(7)How to know how many tables contains empno as a column in a database?

SELECT COUNT(*) AS Counter FROM syscolumns WHERE (name = 'empno')

(8)Write a query to round up the values of a number. For example even if the user enters 7.1 it should be rounded up to 8.

SELECT CEILING (7.1)

(9)Write a query to convert all the letters in a word to upper case

SELECT UPPER('test')

(10)How to get duplicate row in a table

select name from emp group by name having count(name)>1

(11)How to rename a table from query

exec sp_rename 'oldTableName' , 'newTableName'