Important Queries in SQL


These queries are not related to any specific topic. Each query can be used in some specific conditions. I promise that this article will build some good concepts.

Now we start to learn.

First of all, we create an Employee Table.

   Emp_IId Int identity(1,1),  
   First_Name Nvarchar(MAX) Not NUll,  
   Last_Name Nvarchar(MAX) Not Null,  
   Salary Int Not Null,  
   City Nvarchar(Max) Not Null  

Now insert some values into the Employee table.

Insert Into Employee  
Select 'Pankaj','Choudhary',25000,'Alwar' Union All  
Select 'Rahul','Prajapat',23000,'Alwar' Union All  
Select 'Sandeep','Jangid',27000,'Alwar' Union All  
Select 'Sanjeev','Baldia',24000,'Alwar' Union All  
Select 'Neeraj','Saini',22000,'Alwar' Union All  
Select 'Narendra','Sharma',23000,'Alwar' Union All  
Select 'Divyanshu','Gupta',25000,'Alwar'   

Now Employee looks such as:

Select * From Employee  

Insert some Value

Query to get (nth) Highest Value

Assume we want to find the information of all employees with the second highest salary from the Employee table. So the query will be:

Select * From Employee Where  
Salary= (Select Top 1 Salary From (Select distinct Top 2 Salary From Employee Order by Salary Desc)Tab Order By salary Asc )  


Order By salary

Query to get (nth) Lowest Value

Assume we want to find the information of all employees with the second lowest salary from the Employee table. The So query will be:

Select * From Employee Where  
Salary= (Select Top 1 Salary From (Select distinct Top 2 Salary From Employee Order by Salary Asc)Tab Order By salary Desc )  



Query to swap the values of two columns

Sometimes we must swap the values of two columns. So I will show you how to interchange the values of two columns.

In our Employee table, we swap the values for First_Name and Last_Name.

Update Employee Set Last_Name=First_Name ,First_Name =Last_Name  
Select * From Employee  


Query to swap value of two column

Query to insert a value into an Identity column

Let us try to insert a value into the Employee table.

Insert Into Employee(Emp_IId,First_Name,Last_Name,Salary,City) values(8,'Sonu', 'Singh',32000,'Delhi')  
Insert Into Employee(Emp_IId,First_Name,Last_Name,Salary,City) values(9,'Ravi', 'Singh',35000,'Delhi')  


Msg 8101, Level 16, State 1, Line 1.

An explicit value for the identity column in table 'Employee' can only be specified when a column list is used, and IDENTITY_INSERT is ON.

When executing the preceding insert query, the system throws an error that we can't insert values into the Identity Column.

Now I show you how to insert a value into an Identity column.

Insert Into Employee(Emp_IId,First_Name,Last_Name,Salary,City) values(8,'Sonu', 'Singh',32000,'Delhi')  
Insert Into Employee(Emp_IId,First_Name,Last_Name,Salary,City) values(9,'Ravi', 'Singh',35000,'Delhi')  
Select * From Employee  


Employee OFF

In the preceding query, IDENTITY_INSERT ON allows insertion into the identity Column, and DENTITY_INSERT OFF does not allow insertion into the identity column.

Query to Create Comma Separated List

Assume we want to create a list that holds the First_Name and Last_Name of each employee, and the Employee's names are separated with a Comma (,). So for this query, we use Coalesce to make comma-separated values.

Declare @My_List Nvarchar(MAX);  
Select @My_List= Coalesce(@My_List+ ',',' ')+ First_Name +' '+ Last_Name From Employee  
Print @My_List  


Pankaj Choudhary, Rahul Prajapat, Sandeep Jangid, Sanjeev Baldia, Neeraj Saini, Narendra Sharma, Divyanshu Gupta.

Query to Reseed the identity Column

Assume we want to reseed the Emp_Id column to 10. We can also reseed the identity field value. By doing so the identity field values will start with a newly defined value. So now all the new records contain the Emp_Id 11,12,13 and soon.

DBCC checkident (Employee, RESEED, 10)

Insert Into Employee(First_Name,Last_Name,Salary,City) values('Sonu', 'Singh',32000,'Delhi')  
Insert Into Employee(First_Name,Last_Name,Salary,City) values('Ravi', 'Singh',35000,'Delhi')  
Select * From Employee  


DBCC checkident

Query to remove all tables of a Specific Database

Assume we have many tables in our database, and we want to delete (remove) all the tables. It is a large and time-consuming task to remove each table separately. So we can use the following SQL query to remove all the tables from the DB.

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"



Query with Case Expressions

Sometimes we must modify the data based on some conditions. In such a condition, we can use "case" expressions.



WHEN Expression1 THEN Result1 

WHEN expression2 THEN Result2 

ELSE ResultN 


Our employee each employee, has a basic salary. Now we want to provide a bonus to each employee on the bases of their basic salary such that:

Salary Bonus
Salary<24000 1000
24000<=Salary<25000 1500
Salary>=25000 2000

So the query will be

Select EMp_IId, First_Name,Last_Name,Salary=(  
When Salary<24000 then salary +1000  
When Salary >=24000 and Salary<25000 then salary +1500  
END ) ,City From Employee  


City From Employee

Query To Remove all Stored Procedures From a Specific Database

Assume we have many Stored Procedures in the database, and we want to delete all the Stored Procedures. We can't delete each Stored Procedure separately using a "Drop" command because it will take a long time.

We can use another approach. We can create a Cursor that will drop all the Stored Procedures.

So the cursor will be.

Declare @Drop_SP Nvarchar(MAX)  
Declare My_Cursor Cursor For Select [name] From sys.objects where type = 'p'   
Open My_Cursor   
Fetch Next From My_Cursor Into @Drop_SP   
While @@FETCH_STATUS= 0   
   Exec('DROP PROCEDURE ' + @Drop_SP)   
   Fetch Next From My_Cursor Into @Drop_SP   
Close My_Cursor   
Deallocate My_Cursor   


Query To Remove all Stored Procedure

Query To Remove all Views From Specific database

We can remove all views using a cursor.

Declare @Drop_View Nvarchar(MAX)  
Declare My_Cursor Cursor For Select [name] From sys.objects where type = 'v'   
Open My_Cursor   
Fetch Next From My_Cursor Into @Drop_View   
While @@FETCH_STATUS = 0   
   Exec('DROP VIEW ' + @Drop_View)   
   Fetch Next From My_Cursor Into @Drop_View   
Close My_Cursor   
Deallocate My_Cursor   




This article taught us some exciting and valuable SQL queries in SQL Server


Similar Articles