Important Queries in SQL

In this article we learn some interesting and useful SQL queries.

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.

  1. CREATE TABLE Employee  
  2. (  
  3.    Emp_IId Int identity(1,1),  
  4.    First_Name Nvarchar(MAXNot NUll,  
  5.    Last_Name Nvarchar(MAXNot Null,  
  6.    Salary Int Not Null,  
  7.    City Nvarchar(MaxNot Null  
  8. )  
Now insert some values into the Employee table.
  1. Insert Into Employee  
  2. Select 'Pankaj','Choudhary',25000,'Alwar' Union All  
  3. Select 'Rahul','Prajapat',23000,'Alwar' Union All  
  4. Select 'Sandeep','Jangid',27000,'Alwar' Union All  
  5. Select 'Sanjeev','Baldia',24000,'Alwar' Union All  
  6. Select 'Neeraj','Saini',22000,'Alwar' Union All  
  7. Select 'Narendra','Sharma',23000,'Alwar' Union All  
  8. Select 'Divyanshu','Gupta',25000,'Alwar'   
Now Employee looks such as:
  1. 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:
  1. Select * From Employee Where  
  2. Salary= (Select Top 1 Salary From (Select distinct Top 2 Salary From Employee Order by Salary Desc)Tab Order By salary Asc )  
Output

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:
  1. Select * From Employee Where  
  2. 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.
  1. Update Employee Set Last_Name=First_Name ,First_Name =Last_Name  
  2.   
  3. 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.
  1. Insert Into Employee(Emp_IId,First_Name,Last_Name,Salary,City) values(8,'Sonu''Singh',32000,'Delhi')  
  2. 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.

  1. SET IDENTITY_INSERT Employee ON  
  2.   
  3. Insert Into Employee(Emp_IId,First_Name,Last_Name,Salary,City) values(8,'Sonu''Singh',32000,'Delhi')  
  4. Insert Into Employee(Emp_IId,First_Name,Last_Name,Salary,City) values(9,'Ravi''Singh',35000,'Delhi')  
  5.   
  6. SET IDENTITY_INSERT Employee OFF  
  1. 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.
  1. Declare @My_List Nvarchar(MAX);  
  2. Select @My_List= Coalesce(@My_List+ ',',' ')+ First_Name +' '+ Last_Name From Employee  
  3. 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)

  1. Insert Into Employee(First_Name,Last_Name,Salary,City) values('Sonu''Singh',32000,'Delhi')  
  2. Insert Into Employee(First_Name,Last_Name,Salary,City) values('Ravi''Singh',35000,'Delhi')  
  1. 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

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.

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:

  1. Select EMp_IId, First_Name,Last_Name,Salary=(  
  2. Case   
  3. When Salary<24000 then salary +1000  
  4. When Salary >=24000 and Salary<25000 then salary +1500  
  5. else  
  6. salary+2000  
  7. 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:
  1. Declare @Drop_SP Nvarchar(MAX)  
  2. Declare My_Cursor Cursor For Select [nameFrom sys.objects where type = 'p'   
  3. Open My_Cursor   
  4. Fetch Next From My_Cursor Into @Drop_SP   
  5. While @@FETCH_STATUS= 0   
  6. Begin   
  7.    Exec('DROP PROCEDURE ' + @Drop_SP)   
  8.    Fetch Next From My_Cursor Into @Drop_SP   
  9. End  
  10. Close My_Cursor   
  11. Deallocate My_Cursor   
Output

Query To Remove all Stored Procedure

Query To Remove all Views From Specific database

We can remove all views using a cursor.
  1. Declare @Drop_View Nvarchar(MAX)  
  2. Declare My_Cursor Cursor For Select [nameFrom sys.objects where type = 'v'   
  3. Open My_Cursor   
  4. Fetch Next From My_Cursor Into @Drop_View   
  5. While @@FETCH_STATUS = 0   
  6. Begin   
  7.    Exec('DROP VIEW ' + @Drop_View)   
  8.    Fetch Next From My_Cursor Into @Drop_View   
  9. End  
  10. Close My_Cursor   
  11. Deallocate My_Cursor   
Output
Output