Get highest and lowest salary of an employee using Top,MAX and CTE in SQLServer

==>> One of most interview question Get  highest and lowest salary of an employee using Top and without Top ,Using Max without Max. So I am posting the all solution for get salary according question :----


EMP table:

SELECT * FROM dbo.EMP ORDER BY Salary

• Query for Get 3rd Highest and 3rd Lowest Salary using TOP Keyword in Sql Server

For Get 3rd Highest Salary :
 
SELECT TOP 1 EName,salary FROM (SELECT DISTINCT TOP 3 EName,salary FROM dbo.EMP ORDER BY Salary DESC) a ORDER BY salary ASC

For Get 3rd Lowest Salary
 
SELECT TOP 1 EName,salary FROM(SELECT DISTINCT TOP 3 EName,salary FROM emp ORDER BY Salary ASC  ) a ORDER BY salary DESC

• Query for Get 2rd Highest  using MAX in Sql Server

   

    SELECT MAX(Salary) as Salary
   FROM   dbo.EMP
   WHERE  Salary NOT IN ( SELECT  MAX(Salary)
                          FROM    dbo.EMP ) ;

  
  

•    Query for Get Nth Highest and Nth Lowest salary  using CTE in Sql Server

   

   WITH    Salaries
            AS ( SELECT   Salary ,
                          EName ,
                          ROW_NUMBER() OVER ( ORDER BY Salary ASC ) AS 'RowNum'
                 FROM     dbo.EMP
               )
      SELECT  Salary ,
              EName
      FROM    Salaries
      WHERE   RowNum = 5

   

  

   

   So in this way can select value using different-2 manner.The best way you can use CTE and find Nth number Value from table.

  
  

  Hope thei post will helpfull.....
  

Delete Remove duplicate records or rows in sql server

==>> Some time we need Delete or Remove the duplicate rows in sql server this type of problem when occur our data table does not contain any primary key column because of that it contains duplicate records that would be like this:----

:- For get duplicate records you need to run this query this query return how many records are multiple  The RowNumber column indicate the number of rows .......


SQLQuery:-

 WITH tempTable as
(
SELECT ROW_NUMBER() Over(PARTITION BY EName,dptName ORDER BY EName) As RowNumber,* FROM dbo.EMP
)
SELECT * FROM tempTable


Result:-

:-Now You See we having the two row are multiple in RowNumber column  greater then 1 mean the row in this table exist more then one time.

Now we need to execute another query for unique value from datatable for that we need write sql query like :-----


SQLQuery:-

 WITH tempTable as
(
SELECT ROW_NUMBER() Over(PARTITION BY EName,dptName ORDER BY EName) As RowNumber,* FROM EMP
)
DELETE FROM tempTable where RowNumber >1
SELECT * FROM EMP order by EId asc 




Result:-

So in this way we can remove the duplicate rows from sql table .

Hope this will help you for find your solution.

 

Query for Delete All tables,procedures,views,function from database in SQL Server

==>> In this article I am posting SQL Query for Delete All tables,procedures,views,function from database in SQL Server. Some time we need to Delete All tables,procedures,views,function from database in SQL Server so this query will heplfull.



 --For Delete Tables

DECLARE @Sql NVARCHAR(500)
DECLARE @Cursor CURSOR
SET
@Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @Sql
WHILE ( @@FETCH_STATUS = 0 )
    BEGIN
        EXEC SP_EXECUTESQL @Sql
        FETCH NEXT FROM @Cursor INTO @Sql
    END
CLOSE @Cursor
DEALLOCATE @Cursor
GO
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO

--For Delete Procedures
GO
DECLARE @procName VARCHAR(500)
DECLARE cur CURSOR
FOR SELECT [name] FROM sys.objects WHERE type = 'p'
OPEN cur

FETCH NEXT FROM cur INTO @procName
WHILE @@fetch_status = 0
    BEGIN
        IF @procName <> 'DeleteAllProcedures'
            EXEC('drop procedure ' + @procName)
        FETCH NEXT FROM cur INTO @procName
    END

CLOSE cur
DEALLOCATE cur

--For Delete Views

GO
DECLARE @procName VARCHAR(500)
DECLARE cur CURSOR
FOR SELECT [name] FROM sys.objects WHERE type = 'v'
OPEN cur

FETCH NEXT FROM cur INTO @procName
WHILE @@fetch_status = 0
    BEGIN
        EXEC('drop view ' + @procName)
        FETCH NEXT FROM cur INTO @procName
    END
CLOSE cur
DEALLOCATE cur



--For Delete functions
GO
DECLARE @procName VARCHAR(500)
DECLARE cur CURSOR
FOR SELECT [name] FROM sys.objects WHERE type = 'fn'
OPEN cur

FETCH NEXT FROM cur INTO @procName
WHILE @@fetch_status = 0
    BEGIN
        EXEC('drop function ' + @procName)
        FETCH NEXT FROM cur INTO @procName
    END

CLOSE cur
DEALLOCATE cur

SQL Query for get all tables name and table rows

==>> In this article I am posting SQL Query for get all tables name and table rows. Some time we need to get all table name and it's rows so this query will heplfull.


SELECT  scs.name + '.' + tas.name TableNames ,
        SUM(pas.rows) RowsCounted
FROM    sys.tables tas
        INNER JOIN sys.partitions pas ON pas.OBJECT_ID = tas.OBJECT_ID
        INNER JOIN sys.schemas scs ON tas.schema_id = scs.schema_id
WHERE   tas.is_ms_shipped = 0
        AND pas.index_id IN ( 1, 0 )
GROUP BY scs.name ,
        tas.name
ORDER BY SUM(pas.rows) DESC


Hope this post will help you...

User Defined Function in Sql Server

==>> In this article I am going for taking about User Defined Function .It's type,use with example.
         
        Users can create their own functions and make use of it inside T-SQL statements in system
         in system database or in user defined database.    

Three types of user-defined functions : -

1. Scalar Functions
2. Inline Table-Valued Functions
3. Multistatement Table-Valued Functions . 

1. Scalar Functions 

         User defined scalar function returns single value in the result by function . It return   
         value defined in function by the user.

*For Example we use EMP table here.

SELECT * FROM dbo.EMP ORDER BY EID  

In this table we select the all record from the EMP table Now we apply all function on this table.

Now can create a UDF Scalar Function in this way:-


Create FUNCTION GetEmpNameLocation
(
@FirstName VARCHAR(50),
@Location VARCHAR(50)
)
RETURNS VARCHAR(101)
AS
BEGIN
RETURN (SELECT @FirstName+'  '+@Location );
END


output:-


SELECT dbo.GetEmpNameLocation(EName,Location) AS Name ,Salary FROM dbo.EMP

So you can see we get the result Name with adding location of EMP.




         2. Inline Table-Valued Functions

             User defined inline table-valued function Return a set of rows as TABLE data type. The 
             value of table variable should be derived from a single SELECT statement.

Create a Inline Table-Valued Function





Create function fnGetEmp()
returns Table
AS
RETURN  (SELECT  * from dbo.EMP)


Output:-



Select * from fnGetEmp()

So in this way we can use Inline Table-Valued Function. It return table data type in single select statement.







        3. Multistatement Table-Valued Functions

            Returns a set of rows as TABLE data type. Unlike Inline Table-Valued function. In this 
           table variable must be explicitly declared and defined whose value can be derived from a
           multiple sql statements.






Create a Multistatement Table-Valued Functions


Create function fnGetMulEmp()
returns @Emplpyee Table
(
EID int,
EmpName varchar(50),
Salary int
)
As
begin
 Insert @Emplpyee Select EID,EName,Salary from Emp;
--Update Operation
 update @Emplpyee set Salary=25000 where EID=1;
--Update only in @Emplpyee table not in the Main Emp table
return
end



Output:-




Select * from fnGetMulEmp()


SELECT * FROM dbo.EMP

In the result image you see in the table get by using  Multistatement Table-Valued Functions updated salary but affected on Main table.


Note:-

• Function returns only single value.
• Function can be nested up to 32 level.UDF can not returns XML Data Type.
• Function accepts only input parameters.
• UDF does not support Exception handling.
• Function can not perform  Insert, Update, Delete  on table table.
• UDF can have upto 1023 input parameters while a Stored Procedure can have upto 2100 input parameters.

Hope this will help you.

RowNumber in Select Query in Sql Server

==>> In this article I am posting how we can fatch record from sql with Row Number this is common issue with many developer . It is really very simple So first i select the whole EMP  table




SELECT * FROM EMP

We use in built function ROW_NUMBER  for get RowID in select Command so see how we apply ROW_NUMBER in select command and get row number on EMP Table : -


 SELECT ROW_NUMBER() OVER (ORDER BY EID DESC) AS 'RowID',* FROM dbo.EMP

Now you can see first column RowID add in the result.now we perform many operations on on this table basis on RowID .

 Now we try to fetch the record according to RowID column that the benefits of ROW_NUMBER Column

SELECT  B.RowID ,
        A.*
FROM    dbo.EMP A
        INNER JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY EID DESC ) AS 'RowID' ,
                            *
                     FROM   dbo.EMP
                   ) B ON A.EID = B.EID
                          AND B.RowID BETWEEN 5 AND 8
ORDER BY RowID

Now you can see the record from RowID 5 to 8 showing below .



Hope this post would be helpful.  Keep Reading.