Useful SQL Queries

Article

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:

WITH CTE1 AS (SELECT e.Id, e.Name as Name FROM dbo.Emp e),
CTE2 AS (SELECT e.Id, e.Age as Age FROM dbo.Emp e)
SELECT c2.Name, c1.Age FROM CTE1 AS c2
CROSS JOIN CTE2 c1
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:

ALTER TABLE dbo.Emp
NOCHECK CONSTRAINT CK_Employee_Age
GO
-- Enable the constraint
ALTER TABLE dbo.Emp
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:

/*Find Minmum and Maximum vlaue From Column*/
DECLARE @Min int;
DECLARE @Max int;
SET @Max=(SELECT max(Col) FROM dbo.Table_Demo td);
SET @Min=(SELECT min(Col) FROM dbo.Table_Demo td);
/*Findout Missing Values*/
WITH Sequence_ AS (
SELECT @Min AS num
UNION ALL
SELECT num+1 FROM Sequence_ WHERE num+1<=@Max
)
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:

In above table: Emp_IId” column is Identity. Let us try to insert some value into Employee table.

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

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

DECLARE @String [nvarchar](max);
DECLARE @Find [nvarchar](max);
SET @String='Hello India! This is a Hello World example';
SET @Find='Hello';
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:

SELECT [Year], Pankaj,Rahul,Sandeep FROM
(SELECT Name, [Year] , Sales FROM Employee )Tab1
PIVOT
(
SUM(Sales) FOR Name IN (Pankaj,Rahul,Sandeep)) AS Tab2
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:

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

Example:

WITH CTE
AS
(
SELECT td.Col,ROW_NUMBER() OVER( Partition By td.Col ORDER BY td.Col) AS Rank_
FROM dbo.Table_Demo td)
SELECT c.Col FROM CTE c WHERE c.Rank_>=2;

Output:

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: