SQL INTERVIEW QUESTION

tbl_employee

 EmployeeId EmployeeName Gender
1                     ABC                          male
2                      YYC                       Female
Question: update all male into female and all female into Male
Ans';-
Update tbl_employee
Set Gender=case Gender When 'Male' Then Female When  'Female' Then 'Male' Else gender End.

----------------------------------------------------------------------------------------------------------------------------------------


Different between Store procedure and function are

SNo	Function	Store Procedure
1	Function must return a value.	Stored procedure may or not return values.
2	Will allow only Select statement, it will not allow us to use DML statements.	Can have select statements as well as DML statements such as insert, update, delete etc
3	It will allow only input parameters, doesn’t support output parameters.	It can have both input and output parameters.
4	It will not allow us to use try-catch blocks.	For exception handling, we can use try-catch blocks.
5	Transactions are not allowed within functions.	Can use transactions within Stored procedures.
6	We can use only table variables, it will not allow using temporary tables.	Can use both table variables as well as a temporary table in it.
7	Stored procedures can’t be called from function.	Stored Procedures can call functions.
8	Functions can be called from the select statement.	Procedures can’t be called from Select/Where/Having etc statements. Execute/Exec the statement can be used to call/execute the stored procedure.
9	function can be used in join clause as a result set.	Procedures can’t be used in Join clause

Q- HowTo find out the Nth highest salary (for example: here I am finding 3rd highest salary),

A-

SELECT EmpId, LastName, Salary FROM Employee

EmpId LastName Salary

1 Agarwal 50000

2 Agarwal 50000

4 Kumar 60000

5 Agarwal 75000

6 Agarwal 75000

SELECT EmpId,LastName, Salary FROM Employee a1 WHERE 3-1= (SELECT COUNT(DISTINCT Salary) FROM Employee a2 WHERE a2.Salary > a1.Salary)

EmpId LastName Salary

1 Agarwal 50000

2 Agarwal 50000

SELECT TOP 1 SAL FROM(SELECT DISTINCT TOP 2 SAL FROM EMP ORDER BY SAL DESC) AS TEMP
Order by SAL ASC

----------------------------------------------------------------------------------
-- PAGING---
----------------------------------------------------------------------------------
SELECT * FROM (SELECT ROW_NUMBER() OVER( Order By OrderItems_PKey) AS RowNum,* FROM OrderItems) AS RESULT
WHERE RowNUM  >=10 AND RowNum <=20
order by RowNum

------------------------------------------------------------------------------
select * from InventoryItem-- DELETIG THE DUPLICATE REOCRED
------------------------------------------------------------------------------
DELETE from InventoryItem
where InventoryItem_ID Not in
(select max (InventoryItem_ID)
from InventoryItem
group by Name)
-------------------------------------------------------------------------
SELECT * FROM Orders--- SECOND COSTLY ORDER VALUE

SELECT max(GrandTotal)FROM Orders WHERE GrandTotal NOT IN (SELECT MAX(GrandTotal) FROM Orders);
------------------------------------------------------------------------------------------------
--To get list of primary AND  FOREIGN KEY IN ENTIRE DATABASE------------
--------------------------------------------------------------------------------------------
select distinct
constraint_Name as [ConstraintName],
Table_Name As [Table Name]from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
------------------------------------------------------------------------------

How to find second or nth maximum salary from  Employee table?
Here EMP is Table and SAL Is Salary Filed


SELECT * FROM EMP emp1
WHERE(2)=(SELECT COUNT(DISTINCT(emp2.SAL)) FROM EMP emp2 WHERE emp2.SAL >=emp1.SAL)


----------------------------------------------------------------------------------------------------------------