Useful SQL Queries

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.
Output:



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:
Output:



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:



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:
Output:



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:
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:



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

Example:
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.