Important Queries in SQL

Introduction

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.

CREATE TABLE Employee  
(  
   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  

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 )  

Output

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 )  

Output

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  

Output

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')  

Output

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.

SET IDENTITY_INSERT Employee ON  
  
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')  
  
SET IDENTITY_INSERT Employee OFF  

Select * From Employee  

Output

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  

Output

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  

Output

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 ?"

Output

Query with Case Expressions

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

Syntax

CASE

WHEN Expression1 THEN Result1 

WHEN expression2 THEN Result2 

ELSE ResultN 

END

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=(  
Case   
When Salary<24000 then salary +1000  
When Salary >=24000 and Salary<25000 then salary +1500  
else  
salary+2000  
END ) ,City From Employee  

Output

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   
Begin   
   Exec('DROP PROCEDURE ' + @Drop_SP)   
   Fetch Next From My_Cursor Into @Drop_SP   
End  
Close My_Cursor   
Deallocate My_Cursor   

Output

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   
Begin   
   Exec('DROP VIEW ' + @Drop_View)   
   Fetch Next From My_Cursor Into @Drop_View   
End  
Close My_Cursor   
Deallocate My_Cursor   

Output

Conclusion

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

Reference

• 50 Important Queries in SQL Server