Select Command In SQL Server

In this article we will learn about SELECT command with different commands.

  1. Select
  2. Select Distinct
  3. Select Where with Arithmetic Operator
  4. Select Where with Comparison Operator
  5. Select Where with Logical Operator
SQL: Select command with Where clause with different options.

SQL Select command

As command itself define its functionalities Select command select the rows and columns as per given in command.

Table : tblFriends structure with Data Records

  1. CREATE TABLE[dbo].[tblFriends]  
  2. (  
  3.     [FriendID][int] IDENTITY(1, 1) NOT NULL, [Name][varchar](50) NULL, [Place][varchar](25) NULL, [Mobile][varchar](15) NULL, [EmailAddress][varchar](150) NULL, [Qty][intNULL, [Rate][intNULL, [DiscountAmt][intNULL, [PackingCharge][intNULL, [TransportCharge][intNULL  
  4. ON[PRIMARY]  
  5. GO  
Normal Select Command

Syntax1

Select * From <TableName>

Example1

Select * From tblFriends

Above command will return all rows and columns from tblFriends.

Table

Note: This is not a good practice, Always specify your columns in select command while you use in programming.

Syntax 2

Select <ColumnName>, <ColumnName> …. From <TableName>

Example 2

Select Name, Place, Phone From tblFriends

Above command will return all rows but selected columns are: Name, Place, Phone.

Table

How to display distinct, Non-duplicate records from table

Syntax:

Select Distinct <ColumnName>, <ColumnName> …. From <TableName>

Example:

Select Distinct Place From tblFriends

Above command will not display duplicate records. It will display unique place name from tblFriends table.

Table

Total 14 records but distinct PLACE value come around 11 records.

Duplicate Places

Lodiyo    2 Time
Phalodi    2 Time
Pokaran    2 Time

You can check repeated PLACE with this command also.

Select Count(*) as Total, Place from tblFriends group by Place,

TABLE

Select Where with Arithmetic Operator

The following are Arithmetic operator,
  1. + = Addition
  2. – = Subtraction / Minus
  3. * = Multiplication
  4.  / = Divide

+ = Addition

Now, I am going to add two different fields value into one in query.

PackingCharges + TransportCharges = TotalCharges

Query
  1. Select Name,  
  2. Mobile,  
  3. Place,  
  4. (PackingCharge + TransportCharge) as Charges  
  5. From tblFriends  
table

 – = Subtraction/Minus
  1. Select Name,  
  2. Mobile,  
  3. Place,  
  4. ((PackingCharge +TransportCharge)-DiscountAmt ) as ChargesAfterLessDiscount   
  5. From tblFriends 
table

* = Multiplication
  1. Select  
  2. Name,  
  3. Mobile,  
  4. Place,  
  5. Qty,  
  6. Rate,  
  7. (Qty * Rate) As ItemAmount  
  8. From tblFriends  
table

/ = Divide

Select 55/5 as Result.

table

Select Where with Comparison Operator
 
The following are Arithmetic operators,
  1. = Equal to
  2. ! = / <> Not Equal to
  3. > Greater Than
  4. < Less Than
  5. >= Greater than and Equal to
  6. <= Less than and equal to
= Equal to
  1. Select Name,Place,Mobile,Qty From tblFriends where Qty = 70,  
table

!= / <> Not Equal to
  1. Select Name,Place,Mobile,Qty From tblFriends where Qty <> 70,  
table

> Greater Than
  1. SelectName,Place,Mobile,Qty From tblFriends where Qty > 70  
table

< Less Than
  1. SelectName,Place,Mobile,Qty From tblFriends where Qty < 70
table

>= Greater Than Equal To
  1. Select Name,Place,Mobile,Qty From tblFriends where Qty >= 70  
table

<= Less Than Equal To
  1. Select Name,Place,Mobile,Qty From tblFriends where Qty <= 70  
table

Select Where with Logical Operator

The following are Logical operator,
  1. AND
  2. BETWEEN
  3. EXISTS
  4. IN
  5. LIKE
AND

This command will display records which QTY > 70 and RATE <= 60,
  1. Select Name,Place,Mobile,Qty,Rate From tblFriends where Qty > 70 And Rate <= 60  
AND

BETWEEN

This command will display records where QTY between 50 and 70.
  1. SelectName,Place,Mobile,Qty,Rate From tblFriends where (Qty Between 50 And 70)  
between

EXISTS

This command will display all records because PHALODI record exists.
  1. SELECT Name, Mobile, Place, Qty, Rate FROM tblFriends WHERE exists  
  2.     (SELECT * FROM tblFriends WHERE Place = 'Phalodi')  
EXISTS

This command will not display any records because RAMPUR record do not exist.

SELECT Name, Mobile, Place, Qty, Rate FROM tblFriends WHERE exists,
  1. (SELECT * FROM tblFriends WHERE Place = 'RAMPUR')  
result

INTERVIEW QUESTION
  1. SQL Server IN vs. EXISTS Performance.
  2. Difference between IN and EXISTS.
IN: 

Here, I will show you how to write query in two ways.
  1. Hardcoded PLACE
  2. Sub-Query
Hardcoded PLACE

This command  display records of two place data of PHALODI and JODHPUR:
  1. Select * From tblFriends Place In ('Phalodi','Jodhpur')  
Hardcoded PLACE

Sub-Query

This command will display records a sper SubQuery where Qty > 70 and Qty < 120
  1. Select * From tblFriends Where Qty In (Select Distinct Qty From tblFriends Where Qty > 70 and Qty<120)  
Sub-Query

LIKE

Like command can be used as per the following example.

This command will display records which PLACE first alphabet as “P”.
  1. Select * from tblFriends where Place like 'P%'  
LIKE

This command will display records whose PLACE having character “al”

  1. Select * from tblFriends where Place like '%al%'  
command

This command will display records whose PLACE having ending character “r”.
  1. Select * From tblFriends where Place like '%r'  
command