Important Queries in SQL

Pankaj Kumar Choudhary
Jun 08, 2015
5.9k
0
2
Article

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

salary

Query to swap the values of two columns

Sometimes we must swap the values of two columns. So I 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 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')

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 we execute the preceding insert query then 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

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 names of the Employee is 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

We can also reseed the identity field value. By doing so the identity field values will start with a new defined value. Assume we want to reseed the Emp_Id column to 10. 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

DBCC checkident

Query to remove all table of a Specific Database

Assume we have a large number of 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

In 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

City From Employee

Query To Remove all Stored Procedures From a Specific database

Assume we have a large number of 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: