Useful SQL Queries

The following queries are not related to any specific topic of SQL. But knowledge of such queries can solve some complex tasks and may be used in many scenarios, so I decided to write an article on these queries.

Let us start to read these queries.

Use Multiple CTE in Single Select Statement

CTE is known as Common Table Expression and works as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. CTE is not stored as an object and lasts only for the duration of the query. Now we learn how to use multiple CTE in one query or multiple CTE in SELECT statement.

Example:

  1. WITH CTE1 AS (SELECT e.Id, e.Name as Name FROM dbo.Emp e),  
  2. CTE2 AS (SELECT e.Id, e.Age as Age FROM dbo.Emp e)  
  3. SELECT c2.Name, c1.Age FROM CTE1 AS c2  
  4. CROSS JOIN CTE2 c1  
  5. WHERE c2.Id=c1.Id;  
Output:

Use Multiple CTE in Single Select Statement

Enable and Disable Check Constraint

Sometimes we are required to disable the check constraint for queries and after completion of the query we are required to set the check constraint in its previous state. Such a type of operation is needed when we insert data in bulk or check constraint is not required for some data. We can use the following code script for this.

Syntax:

//Disable Constraint
Alter Table Table_Name NOCHECK Constraint Constraint_Name
//Enable Constraint
Alter Table Table_Name
WITH CHECK CHECK Constraint Constraint_Name


Example:

  1. ALTER TABLE dbo.Emp  
  2. NOCHECK CONSTRAINT CK_Employee_Age  
  3. GO  
  4. -- Enable the constraint  
  5. ALTER TABLE dbo.Emp  
  6. WITH CHECK CHECK CONSTRAINT CK_Employee_Age  
Find out missing Value from a column:

Sometimes we need to find out all the values from a column that do not exist in the column. Let us consider the following table.

table

We can see that value in Col are starting from 1 and last value in Col is 15, but there are some values between 1 and 15 that do not exist . So now we learn how to find out the missing values that do not exist in Col.

Example:
  1. /*Find Minmum and Maximum vlaue From Column*/  
  2. DECLARE @Min int;  
  3. DECLARE @Max int;  
  4. SET @Max=(SELECT max(Col) FROM dbo.Table_Demo td);  
  5. SET @Min=(SELECT min(Col) FROM dbo.Table_Demo td);  
  6.   
  7. /*Findout Missing Values*/  
  8. WITH Sequence_ AS (  
  9. SELECT @Min AS num  
  10. UNION ALL  
  11. SELECT num+1 FROM Sequence_ WHERE num+1<=@Max  
  12. )  
  13. SELECT num FROM Sequence_ WHERE num NOT IN (SELECT td.Col FROM dbo.Table_Demo td);  
Output:

Find out missing Value From a column

Query to insert value in Identity column:

Query to insert value

In above table: Emp_IId” column is Identity. Let us try to insert some value into Employee table.
  1. Insert Into Employee(Emp_IId,First_Name,Last_Name,Salary,City) values(8,'Sonu''Singh',32basic-useful-sql-queriesbasic-useful-sql-queriesbasic-useful-sql-queries,'Delhi')  
  2. Insert Into Employee(Emp_IId,First_Name,Last_Name,Salary,City) values(9,'Ravi''Singh',35basic-useful-sql-queriesbasic-useful-sql-queriesbasic-useful-sql-queries,'Delhi')  
Output:

Msg 81basic-useful-sql-queries1, 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 above insert query then the system throws an error that we can’t insert value into Identity Column.

Now I tell you how to insert value in 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',32basic-useful-sql-queriesbasic-useful-sql-queriesbasic-useful-sql-queries,'Delhi')  
  4. Insert Into Employee(Emp_IId,First_Name,Last_Name,Salary,City) values(9,'Ravi''Singh',35basic-useful-sql-queriesbasic-useful-sql-queriesbasic-useful-sql-queries,'Delhi')  
  5.   
  6. SET IDENTITY_INSERT Employee OFF  
  7.   
  8. Select * From Employee  
Output:

Employee

In above query IDENTITY_INSERT ON allow insert to the identity column and DENTITY_INSERT OFF doesnot allow insertion to the identity column.

Find Out Number of occurrences of a word in String

In this script we will learn how to find out the number of occurrences of a word in a string. Let us take an example: we have a string ̶basic-useful-sql-query; Hello India! This is a Hello World example” and we want to find out the number of occurrences of the "Hello” word. Let us take an example.

Example:
  1. DECLARE @String [nvarchar](max);  
  2. DECLARE @Find [nvarchar](max);  
  3. SET @String='Hello India! This is a Hello World example';  
  4. SET @Find='Hello';  
  5. SELECT (len(@String)-len(REPLACE(@String,@Find,'')))/len(@Find) AS Frequence;  
Output:

Find Out Number

Transfer data from row level to column level

PIVOT is used when we want to transfer data from row level to column level. PIVOT is used to generate an interactive table that quickly combines and compares large amounts of data. PIVOT relational operator converts data from row level to column level. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. Using PIVOT operator we can perform aggregate operations where we are required.

Example:

Example

Syntax:

SELECT <non-pivoted column>,
<list of pivoted column>
FROM
(<SELECT query to produces the data>)
AS <alias name>
PIVOT
(
<aggregation function>(<column name>)
FOR
[<column name that become column headers>]
IN ( [list of pivoted columns])

) AS <alias name for pivot table>


Example:
  1. SELECT [Year], Pankaj,Rahul,Sandeep FROM   
  2. (SELECT Name, [Year] , Sales FROM Employee )Tab1  
  3. PIVOT  
  4. (  
  5. SUM(Sales) FOR Name IN (Pankaj,Rahul,Sandeep)) AS Tab2  
  6. ORDER BY [Tab2].[Year]  
Output:

result

Retrieve Duplicate Data from Table

In this query we will learn how to retrieve the duplicate data from a column. Let us consider the following table:

Retrieve Duplicate Data from Table

For the above table we find out the values that are present more than once in column.

Example:
  1. WITH CTE  
  2. AS  
  3. (  
  4. SELECT td.Col,ROW_NUMBER() OVER( Partition By td.Col ORDER BY td.Col) AS Rank_  
  5. FROM dbo.Table_Demo td)  
  6. SELECT c.Col FROM CTE c WHERE c.Rank_>=2;  
Output:

run


Retrieve Duplicate Data along with number of occurrence from column:

In the previous query we learned how to retrieve the duplicate data from a table. In this query we will learn how to retrieve duplicate data and also their number of occurrences. Consider the following table:

Retrieve Duplicate Data

We find the duplicate data for the above table using the following script.

Example:
  1. WITH CTE  
  2. AS  
  3. (  
  4. SELECT td.Col,ROW_NUMBER() OVER( Partition By td.Col ORDER BY td.Col) AS Rank_  
  5. FROM dbo.Table_Demo td)  
  6. SELECT c.Col, Count(c.Col) AS Count_ FROM CTE c GROUP BY c.Col  
  7. HAVING Count(c.Col)>=2;  
Output:

Output

I hope you liked this article. If you have any better solution or method for any query among the above queries then please share.

Thanks for reading the article.
 
Read more articles on SQL Server:

Up Next
    Ebook Download
    View all
    Learn
    View all