Some SQL Queries asked in Interview.

Query for getting column count of Table in SQL

select  COUNT(*) from SYSCOLUMNS where id=(select id from SYSOBJECTS where name='EmployeeMaster')

Find same columns within two tables

select b.name

from sys.tables a, sys.columns b

where a.name = 'Employeemaster'

and a.object_id = b.object_id

INTERSECT

select b.name

from sys.tables a, sys.columns b

where a.name = 'SalaryMaster'

and a.object_id = b.object_id

Interchange the values of Gender column/Field.  Wherever ‘F’ is there in the Gender column/field it should be changed to ‘M’ and wherever ‘M’ is there is the gender column/field it should be changed to ‘F’.

UPDATE

Employee

SET

gender  =

(

CASE

WHEN

(gender = 'M')

THEN

'F'

WHEN

(gender = 'F')

THEN

'M'

END

)      

Write a query to make exact replica of an existing table EMP in the same database

select * into employee_replica from employee

Finding the nth highest salary of an employee.

- To find the highest salary as:-

Select max (Salary) from Employee

-To find the 3rd highest salary as:-

Select min (Salary) from Employee where Salary in

(Select distinct top 3 Salary from Employee order by Salary desc)

-To find the nth highest salary, replace the top 3 with top n (n being an integer 1, 2, 3 etc.)

Select min (Salary) from Employee where Salary in

(Select distinct top n Salary from Employee order by Salary desc)