Distinct Keyword:
It gives unique records from database
It remove repeated rows
example:
Find unique merchant names form Account table
select distinct MerchantName
from Account
Order by Keyword:
It is used to sort rows based on column names
Sort account table by deposit amount in ascending order
select * from Account
order by DepositAmount asc
***asc means ascending
By default it will be ascending
Sort account table by deposit amount in descending order
select * from Account
order by DepositAmount desc
*** desc means descending
Top keyword
Will give the only the top rows you need
Question:
select top three maximum deposits
select top(3) *
from Account
order by DepositAmount desc
Question:
select top three minimum deposits
select top(3) *
from Account
order by DepositAmount asc
Question:
Select all rows from account table where deposit amount is between (500 and 5000)
Between keyword allows you to select data from a range of values.
example
select * from account
where depositamount between 500 and 5000
We can rewrite this with out between keyword like this:
select * from account
where depositamount >= 500 and depositamount <= 5000
Question: select records from account where WithdrawAmount is between 10 and 90:
select * from Account where WithdrawAmount between 10 and 90
Question: select records from account where TransactionDate is between '1/1/2001' and '1/1/2020'
select * from Account where TransactionDate between '1/1/2001' and '1/1/2020'
Count keyword
will give us the number records in our database
select count(*)
from Account
Alias: use the “as” keyword to give column names where there is no name
select count(*) as NoofRecords
from Account
Calculated Column
If you want get a new column that is not present in the table, you can create it using Alias and some math operations
select DepositAmount, WithdrawAmount, (DepositAmount - WithdrawAmount) as Balance
from Account
The Like keyword
It will search for items with similar values. You have to use wild characters (eg %)
Question: Find all customer(s) whose name starts with J
select customername
from Account
where customername like 'J%'
Find all customer name who has ‘m’ in the middle or end
select customername
from Account
where customername like '%m%'
Find all merchants whose name contains ‘mart’
select merchantname
from Account
where merchantname like '%mart%'
The “In” operator
We use it in the where condition and it helps us to match items from a list
It is the same as using multiple “or” conditions
Example:
select DepositAmount, WithdrawAmount, MerchantName, MerchantAddress
from Account
where MerchantName = 'Giant' or MerchantName = 'Lows'
Rewrite this using the IN operator
select DepositAmount, WithdrawAmount, MerchantName, MerchantAddress
from Account
where MerchantName in ( 'Giant' , 'Lows')
It will qualify to pick the item if merchant name belongs to this list ( 'Giant' , 'Lows')
Not in Operator
It will select only if the item is not contained in the list
select DepositAmount, WithdrawAmount, MerchantName, MerchantAddress
from Account
where MerchantName not in ( 'Giant' , 'Lows')
This means I want a list of accounts where merchant name is not in this list ( 'Giant' , 'Lows')