Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Easy Way:How to find 3rd higest salary in sql server

Find 3rd highest salary in SQL server

In this tutorial, I am going to show how we can find the 3rd highest salary in SQL Server. In below, the method I want to show you a very easy method so it will help to understand better. The method will take three step so please understand every step carefully.




create table EmployeeSalary 
( 
   ID int identity primary key, 
   Name varchar(20), 
   Salary Decimal  
) 

Insert into EmployeeSalary(Name,Salary)values('Ram',21000) 
Insert into EmployeeSalary(Name,Salary)values('Jhon',50000) 
Insert into EmployeeSalary(Name,Salary)values('Simant',40000) 
Insert into EmployeeSalary(Name,Salary)values('Tom',35000) 
Insert into EmployeeSalary(Name,Salary)values('Alia',25000) 
Insert into EmployeeSalary(Name,Salary)values('Mohan',22000) 
Insert into EmployeeSalary(Name,Salary)values('Raj',23000)





#Step 1:
In this query, I execute to show the record of  EmployeeSalary table



select
 * from EmployeeSalary



How to find 3rd higest salary in sql server
3rd higest salary in sql server
                                                         





#Step 2: Now, Firstly find the top 3 salaries with descending order


Select  top 3 Name,Salary from EmployeeSalary order by salary desc



How to find 3rd higest salary in sql server
3rd higest salary
                                



Let suppose the above record is a table, now we need to find a salary from current table record.

#Step 3: Find the top 1 record in ascending order from the current record 




select top 1 Name,Salary from (select  top 3 Name,Salary from EmployeeSalary order by salary desc) as T  order by salary asc


How to find 3rd higest salary in sql server
3rd highest salary 
                                


Finally, we find the 3rd highest salary from SQL Server table


READ MORE - KNOW MORE





Read More

How to convert vertical data to horizontal in sql

Conversion of vertical data to horizontal in sql: Today I am going to show you how to convert vertical data to horizontal in ms sql. Here we have multiple ways by which we can convert vertical data to horizontal in sql. Here are some simple transformation of data from vertical to horizontal. 

Table structure


 display data vertically in sql
Insert Some data in table and select it

How to convert vertical data to horizontal in sql

Method1: to convert vertical data to horizontal in sql
Select * from PartsItem

Declare @ProductSKU varchar(MAX)='  '
select @ProductSKU= @ProductSKU+ ISNULL
(ProductSKU+', ' ' ') from PartsItem Select @ProductSKU as ProductSKUCode



 display data vertically in sql


Method2: to transformation of vertical data to horizontal in sql
It will show the record is in form of XML


Select ProductSKU + ' , '  from PartsItem for xml path ('  ')

How to convert vertical data to horizontal in sql





Read More

Function in SQL and use of SQL function at project level

Function in MS SQL Server,Use of function

Introduction:Function is used to perform the set of task or we can write multiple queries in a function and we can call n-number of times , So function gives me reusability of queries.

Why we create(make) function in MS SQL Server

Reusability:-Once we make a function we can call many times from asp.net application or any other application.

Actual Use of function at project level:

1.Function can return single value, it can’t return multiple value due to “returns” keyword.
2.Function can call another function.
3.At the project level the actual use of function is to perform the computational or calculation like to findout the no. of leave taken by the employee, sum of employee salary etc.
4.By the function we can fetch a record or search a record.
5.By the function we can’t perform DML operation(like insert,delete,update). This is the reason I wann go for Store procedure.


Note:

When we make a function it will go within DBO (DataBase Owner), became if we want to call the function we must include dbo.function name let’s see.

Create function [SearchName](@code int)
returns varchar(20)
as
   begin
       return 'Deepak'
   end

select dbo.SearchName(107) 

So, how many ways we can call function in Sql: There are following ways we can call function in MS SQL Server.

1.select dbo.SearchName(107)
2.print dbo.SearchName(107)
3.Declare @detail varchar(50)
SET @detail=dbo.SearchName(107)
print @detail




Function in SQL and use of SQL function at project level

Type of function in MS SQL Server:

If you go under programmability section,then you can see function is 3-Type

1.System Function
2.Table-Valued Function
3.Scaler-Valued Function


Function in SQL and use of SQL function at project level



Read More

[Updated:2018] Most Popular Sql interview question for freshers

In this tutorial I going to show you some tricky sql interview queries which is ask in fresher's interviews. These query are very simple and easy, I hope it will help you to crack the SQL Server Interview.


Sql interview question for freshers
Sql interview question for freshers



create database DBTest
use DBTest

--Check all information about any database
exec sp_helpdb @dbname='Test11718'

create table Employee
(
 EID int primary key identity(1,1),
 Name varchar(50),
 Age int,
 salary int
)
insert into Employee values('Amar',21,5000),('Carry',22,3000),('Deepak',28,2000),('Ram',21,1000),('XYZ',25,4000)

insert into Employee(Name,Age,Salary) values('Simant',25,500)

Rename a table

sp_rename 'Emp','Employee'

Rename column name

sp_rename 'Employee.salary','EmpSalary'

Add new column

alter table Employee add address varchar(20)

Change the DataType of column

alter table employee alter column address int

Note:

But there is one problem a datatype of column can not be changed when it will have data inside it.
It can be changed also when it will have data too but only in case of upcasting  ex:- int to varchar.

Drop the column

alter table Employee drop column address

--------------------Some Select Command ask in interview-------------------------


  

Get all record of table

select * from Employee

Find Maximum Salary

select MAX(EmpSalary) from Employee

Find Minimum Salary

select MIN(EmpSalary) from Employee

Find Avarage Salary
select AVG(EmpSalary) from Employee

How to find Avarage of string(Like name,address)

select AVG(Name) from Employee

No, you can't find the avarage of name because Operand data type varchar is invalid for avg operator.

Count the record of any column, count function naver count the null value

select count(Name) from Employee

Try MIN and MAX function on another place

select MIN(Name) from Employee
select MAX(Name) from Employee



READ MORE - KNOW MORE


Read More

Stored procedure to insert data into multiple tables in sql server

Inserting into multiple tables:

Introduction: In this article I am going to explain how to insert data into multiple tables . For this you have to basic knowledge of store procedure. Let's get start step by step.

insert data into multiple tables in sql server


Step1:

Create first table: 


create table Registraion
(
regid int primary key identity,
name varchar(50)
)

Step2:

Create second table: 


create table Registraion_Exp
(
regexpid int primary key identity,
regid int foreign key references Registraion(regid),
current_company varchar(50),
current_salary int,
expected_salary int,
notice_period int
)

Step3:

Now, I am creating a store procedure to insert data in two tables at a time in mssql , this is very simple.


Create proc usp_registration_insert
@name varchar(50),
@current_company varchar(50),
@current_salary int,
@expected_salary int,
@notice_period int
as
begin
     insert into Registraion(name)values(@name)
     declare @id int
     select @id = scope_identity()
     insert into Registraion_Exp(regid,current_company,
                        current_salary,expected_salary,
                                    notice_period)                                            values(@id,@current_company,
                      @current_salary,@expected_salary,
                                    @notice_period)         
end



Select @id = scope_identity(): (Use of scope_identity)
Used for selecting the latest value(primary key which is also a foregin key) of table first, if their will no reference between these tables it means that we can not get latest value of first table.


Read More - Know More







Read More