In this article we will learn about SELECT command with different commands.
- Select
- Select Distinct
- Select Where with Arithmetic Operator
- Select Where with Comparison Operator
- 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,
- + = Addition
- – = Subtraction / Minus
- * = Multiplication
- / = 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,
- = Equal to
- ! = / <> Not Equal to
- > Greater Than
- < Less Than
- >= Greater than and Equal to
- <= 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,
- AND
- BETWEEN
- EXISTS
- IN
- 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
- SQL Server IN vs. EXISTS Performance.
- Difference between IN and EXISTS.
IN:
Here, I will show you how to write query in two ways.
- Hardcoded PLACE
- 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'