Post
Ask Question

SQL Basic Queries

Introduction

These queries are not related to any specific topic. Each query is used in some specific conditions to perform a specific task. I promise that this article will build some good concepts.

This is my second article on "SQL Basic Query." If you didn't read my first article, then read that article using the following link—basic SQL Query Part 1.

Now we start today's session.

Find the Identity Column of Table

Sometimes we need to find out the identity column of a table. In such a case, we can use the "$identity" option to find out the identity column of a table.

Let us take an example.

First, we create two tables.

CREATE TABLE Temp1  
( Id int IDENTITY(1,1),  
Name [nvarchar](max)  
)  
  
CREATE TABLE Temp2  
( Id int ,  
Name [nvarchar](max)  
)  

We can see that table Temp1 contains an identity column, but table Temp2 doesn't contain any identity column.

Now insert data into both tables:

INSERT INTO Temp1 VALUES ('A')  
INSERT INTO Temp1 VALUES ('B')  
  
  
INSERT INTO Temp2 VALUES (1,'A')  
INSERT INTO Temp2 VALUES (2,'B')  

Let us check the identity column for both tables.

query

In the above query, the SQL server returns the identity column for the Temp1 table.

This query returns the column name with data. We can retrieve only column names without their data using the following query.

column name

The above query only returns the name of the identity column without data.

Now we find the identity column for the Temp2 table. Let us check.

identity

When we execute the above command, you will find an error because the "$IDENTITY" command throws an error if the table doesn't contain any identity column.

You can find more about Identity and its properties here. Identity in SQL Server.

Generate Row Number Without Effecting the Ordering of Column

Sometimes we must generate a unique serial number for a given record set. SQL Server provides various types of Rank functions to generate serial numbers such as RANK, ROW_NUMBER, DASH_RANK, and NTILE.

But when we use the above rank function, we must supply the "ORDER BY" column (s) name. SQL Server sorts the record on behalf of this column(s) name and provides a serial number for each record.

Let us take an example.

--Declare Table--  
DECLARE @TAB TABLE(  
Name_ [nvarchar](max),  
Age [int]  
)  
--Insert Data--  
INSERT INTO @TAB VALUES ('Pankaj',21)  
INSERT INTO @TAB VALUES ('Sandeep',22)  
INSERT INTO @TAB VALUES ('Nitin',23)  
INSERT INTO @TAB VALUES ('Rahul',20)  
INSERT INTO @TAB VALUES ('Amit',22)  
--Select data--  
SELECT * FROM @TAB t  

Output

data

Now we want to create a unique serial number for this table; we can use any Rank function; we take ROW_NUMBER. Let us try to generate a unique serial number.

SELECT t.Name_ ,t.Age , ROW_NUMBER() OVER(ORDER BY t.Name_) AS "Rank"  
FROM @TAB t  

Output

Rank function

We can see that a rank has been generated for each record, but the order of data has been changed. What can we do to maintain the original order of data? Can we neglect the "ORDER BY" clause? Let us try.

record

We can see that we can't neglect the "Order By" clause. But we can solve this problem using the ORDER BY clause with any literal value.

SELECT t.Name_ ,t.Age , ROW_NUMBER() OVER(ORDER BY(SELECT 1)) AS "Rank"  
FROM @TAB t  

Output

clause

We can see that we generate a Rank for each record without affecting the original order of data.

Case Sensitive Search SQL Query

Let us take an example:

--Declare Table--  
DECLARE @TAB TABLE(  
Name_ [nvarchar](max)  
) --Insert Data--  
INSERT INTO @TAB  
VALUES  
('Search') INSERT INTO @TAB  
VALUES  
('SEARCH') INSERT INTO @TAB  
VALUES  
('SeArCh') INSERT INTO @TAB  
VALUES  
('sEaRcH') --Select Data--  
SELECT  
t.Name_  
FROM  
@TAB t  

Output

VALUES

We can see that the Table contains a single word many times, but the case of each word is different. We want to search the record from the table where Name_ is "SEARCH." Let us try.

SELECT t.Name_ FROM @TAB t  
WHERE t.Name_='SEARCH'  

Output

Table contain

The above query returns all the records from the table where Name_ is "SEARCH" without checking the case. To make a query case-sensitive, we need to change the collation of the query. The default collation of SQL Server is "SQL_Latin1_General_CP1_CI_AS," which is selected at the time of Installation. We add the "Latin1_General_CS_AS" collation to make the query case sensitive.

The query is the following.

SELECT t.Name_ FROM @TAB t   
WHERE t.Name_ COLLATE Latin1_General_CS_AS ='SEARCH'  

Output

SEARCH

Remove Numeric Value From String

Suppose we have a table Tab, which contains a column Name_, and the data type of this column is nvarchar. The name_ column contains the string that is the combination of Numeric and Characters. Now we want to retrieve only characters from each column value.

The tab table contains the following data:

Numeric Value

We can see that the value of the Name_ column is a combination of characters and numeric data type. Now we create a function that will remove the numeric value from each string, and we use this function in the select command.

Here's the function.

CREATE FUNCTION Return_String(@Str [nvarchar](max))  
RETURNS [nvarchar](max)  
AS   
BEGIN  
DECLARE @Count int;  
SET @Count=0  
WHILE @Count<10  
BEGIN  
SET @Str=REPLACE(@Str,CHAR(49+@Count),'')  
SET @Count=@Count+1  
END  
RETURN @Str  
END  

Now we use the above function for the Tab table and remove the Numeric value from the Name_ column.

Tab table

Remove All Characters From the String

This is similar to the previous method, but we remove the characters from a string in this query. We use the same Tab table for this example.

Here's the function to remove all characters from a string.

CREATE FUNCTION Return_NUMERIC(  
@Str [nvarchar](max)  
) RETURNS [nvarchar](max) AS BEGIN DECLARE @Count int;  
SET  
@Count = 0 WHILE @Count < 26 BEGIN   
SET   
  @Str = REPLACE(  
    @Str,   
    CHAR(65 + @Count),   
    ''  
  )   
SET   
  @Count = @Count + 1 END RETURN @Str END  

Now we use the above function for the Tab table and remove all the characters from the Name_ column.

characters

Insert Data from the Stored procedure into the Table

Sometimes we must insert the result of the stored procedure in a table. We have two methods to perform this task.

First of all, we create a table.

--Declare Table--  
CREATE TABLE TAB(  
IID int,  
Name_ [nvarchar](max)  
  
)  
--Insert Data--  
INSERT INTO TAB VALUES (1,'Pankaj')  
INSERT INTO TAB VALUES (2,'Sandeep')  
INSERT INTO TAB VALUES (3,'Rahul')  
INSERT INTO TAB VALUES (4,'Sanjeev')  
--Select Data--  
  
  
SELECT * FROM dbo.TAB t  

The tab table looks like the following.

table

Now we create a stored procedure that will return the table.

CREATE PROCEDURE Return_Data  
AS   
BEGIN  
SELECT t.IID,t.Name_ FROM dbo.TAB t  
END  

Now we retrieve data from Return_Data stored procedure and insert it in a Table.

Method 1. When Table Already Exists.

This method is useful when a table already exists, and we know the schema of the resultant retrieved from the stored procedure.

Example

First, we create another table similar to the TAB table.

CREATE TABLE TAB1(  
IID int,  
Name_ [nvarchar](max)  
)  

Now we insert data into this table.

--Execuet Stored Procedure  
INSERT INTO TAB1  
EXEC Return_Data  
--Select Data  
SELECT * FROM dbo.TAB1 t  

Output

dbo

In the above method, we execute the stored procedure and insert the result in the TAB1 table.

This method is helpful if we know the schema of stored procedure returns. But we can't use this method if we don't know the schema of the stored procedure result or if the schema is dynamic. For such a condition, we can use the following method.

Method 2. Create Table RunTime.

In this method, we create a table run time and insert the result of the stored procedure in the table; for this, we use the "OPENROWSET" method.

Let us take an example.

First, we need to enable the ad hoc query distribution in SQL Server. For this, execute the following query first.

--Enable Ad Hoc Query  
sp_configure 'Show Advanced Options', 1  
GO  
RECONFIGURE  
GO  
sp_configure 'Ad Hoc Distributed Queries', 1  
GO  
RECONFIGURE  
GO  

Now we execute Main Query as in the following code snippet:

SELECT  
* INTO TAB1  
FROM  
OPENROWSET(  
'SQLNCLI', 'Server=Your_Server_Name;Trusted_Connection=yes;',  
'EXEC Demo.dbo.Return_Data'  
) -- Select Table  
SELECT  
*  
FROM  
TAB1;  

Output

Create Table RunTime

Reverse String Word By Word

In this query, I will explain how to reverse a string word by word like if the string is "Pankaj Kumar Choudhary," then it should be reversed as "Choudhary Kumar Pankaj."

First, we create a table and insert some data into this table.

Declare @StringTab TABLE (  
Name [nvarchar](max)  
) INSERT INTO @StringTab  
SELECT  
'I AM PANKAJ KUMAR CHOUDHARY'  
UNION ALL  
SELECT  
'I AM IN 4TH YEAR OF GRADUATION'  
UNION ALL  
SELECT  
'I LIVE IN ALWAR'  
SELECT  
*  
FROM  
@StringTab st  

The table looks like the following.

name

Now we create a function that will revert the string.

CREATE FUNCTION Reverse_String(
     @String  VARCHAR(MAX)   
)  RETURNS VARCHAR(MAX)  AS BEGIN DECLARE @Str [nvarchar](max);
   DECLARE @Index int;
   DECLARE @Return_Strint [nvarchar](max);
   SET   @Return_Strint   =  NULL;
   SET   @String   +=   ' '  WHILE CHARINDEX(' ',  @String)   >  0 BEGIN   SET   @Index   =  CHARINDEX(' ',  @String)    SET   @Str   =  SUBSTRING(@String,  1,  @Index  - 1)+   ' '   +  ISNULL(@Str,   '')    SET   @Return_Strint   =  @Str   SET   @String   =  SUBSTRING(
     @String, 
     @Index   +  1, 
     LEN(@String)   
)  END RETURN @Str END  

Now we use the above function to obtain the reverse of a string. 

SELECT st.Name ,dbo.Reverse_String(st.Name) AS "Reversed String" FROM @StringTab st  

Output

revert the string

Top Command With Ties

Suppose we want to find the top two results from a table. If we use the top command for a table, the SQL server returns the top two records according to sorting order. But if we use the top command with Ties, then SQL Server returns the top two results and all the records containing values equal to the last row.

For better practice, we take two examples, one for the Top command and the second for the Top command with Ties, and find out the difference between the result of both commands.

Let us take an example.

Firstly, we create a table and insert some data in that table.

DECLARE @Table TABLE   (   IID int  )       INSERT INTO @Table   SELECT 1 UNION ALL   SELECT 5 UNION ALL   SELECT 2 UNION ALL   SELECT 1 UNION ALL   SELECT 3 UNION ALL   SELECT 3 UNION ALL   SELECT 5 UNION ALL   SELECT 6 UNION ALL   SELECT 7 UNION ALL   SELECT 6 UNION ALL   SELECT 1  

Top Command without Ties

If we use the Top command to select the Top two records from the table, then the result will be the following:

SELECT TOP 2 t.IID FROM @Table t  
ORDER BY t.IID DESC  

Execution Plan for the query.

Execution

Output

id

Top Command with Ties

SELECT TOP 2 WITH TIES t.IID FROM @Table t  
ORDER BY t.IID DESC  

Execution Plan for the query.

Output

Output

result

We can see that the Top command with Ties contains more records compared to the Top command without Ties. Because the top command without ties for a table only returns the record equal to the number provided with the Top command, the record obtained by the Top command with ties also contains that record with a value equal to the last row.

Conclusion

We read some basic queries; I hope you enjoyed today's session. If you have a better solution for any of the above queries, please share that; it will be beneficial.

Thank you for reading the article. 

Reference


Similar Articles