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

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

Normal Select Command

Syntax1

Select * From <TableName>

Example1

Select * From tblFriends

Above command will return all rows and columns from tblFriends.



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.



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.



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,



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

Select Name,
Mobile,
Place,
(PackingCharge + TransportCharge) as Charges
From tblFriends

 – = Subtraction/Minus

Select Name,
Mobile,
Place,
((PackingCharge +TransportCharge)-DiscountAmt ) as ChargesAfterLessDiscount
From tblFriends

* = Multiplication

Select
Name,
Mobile,
Place,
Qty,
Rate,
(Qty * Rate) As ItemAmount
From tblFriends

/ = Divide

Select 55/5 as Result.



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

Select Name,Place,Mobile,Qty From tblFriends where Qty = 70,

!= / <> Not Equal to

Select Name,Place,Mobile,Qty From tblFriends where Qty <> 70,

> Greater Than

SelectName,Place,Mobile,Qty From tblFriends where Qty > 70

< Less Than

SelectName,Place,Mobile,Qty From tblFriends where Qty < 70

>= Greater Than Equal To

Select Name,Place,Mobile,Qty From tblFriends where Qty >= 70

<= Less Than Equal To

Select Name,Place,Mobile,Qty From tblFriends where Qty <= 70

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,

Select Name,Place,Mobile,Qty,Rate From tblFriends where Qty > 70 And Rate <= 60

BETWEEN

This command will display records where QTY between 50 and 70.

SelectName,Place,Mobile,Qty,Rate From tblFriends where (Qty Between 50 And 70)

EXISTS

This command will display all records because PHALODI record exists.

SELECT Name, Mobile, Place, Qty, Rate FROM tblFriends WHERE exists
    (SELECT * FROM tblFriends WHERE Place = 'Phalodi')

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

SELECT Name, Mobile, Place, Qty, Rate FROM tblFriends WHERE exists,

(SELECT * FROM tblFriends WHERE Place = 'RAMPUR')

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:

Select * From tblFriends Place In ('Phalodi','Jodhpur')

Sub-Query

This command will display records a sper SubQuery where Qty > 70 and Qty < 120

Select * From tblFriends Where Qty In (Select Distinct Qty From tblFriends Where Qty > 70 and Qty<120)

LIKE

Like command can be used as per the following example.

This command will display records which PLACE first alphabet as “P”.

Select * from tblFriends where Place like 'P%'

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

Select * from tblFriends where Place like '%al%'

This command will display records whose PLACE having ending character “r”.

Select * From tblFriends where Place like '%r'