In this series I will explain the syntax by using the SELECT statement in SQL Server. Using this statement is very useful and productive when trying to do certain tasks efficiently.
1. Select specific or all columns
2. Distinct rows
3. Filtering with where clause.
4. Wild Cards in SQL Server
5. Joining multiple conditions using AND and OR operators
6. Sorting rows using order by
7. Selecting top n or top n percentage of rows
--To SELECT ALL COLUMNS from Table--
select * from tblPerson
--How to SELECT specific or all columns--
--1 right click on table, go to Script
Table as --> SELECT TO-> New Query Editor Window--
--It will write the query for us--
--How to select distinct rows--
Select distinct Country from tblPerson
--Select two distinct columns--
Select distinct Name, Country from
tblPerson
--Select people who live in Portland--
Select * from tblPerson where
Country= 'Portland'
--Select people not living in
Portland--
Select * from tblPerson where
Country <> 'Portland'
Select * from tblPerson where
City <> 'Portland'
Select * from tblPerson where
City != 'Portland'
--Select Age 21, 22, 23--
Select * from tblPerson Where
Age IN (20, 22, 23)
--Using the BETWEEN operator to SELECT
a range for example(20 and 30)--
Select * from tblPerson Where
Age BETWEEN 20 AND
30
--Using the LIKE operator--
Select * from tblPerson where
City LIKE 'L%'
--Using the NOT LIKE operator--
Select * from tblPerson where
City NOT LIKE 'L%'
ID
Name Email GenderID Age City
1 John j@j.com 1 21 Sydney
3 Ron ron@r.com 1 41 Miami
4 Sara s@s.com 2 33 Buffalo
6 Kathy k@k.com 2 42 Seattle
7 Rich rich@r.com NULL 52 Portland
8 Mike mike@m.com 3 56 Austin
9 Sarah s@r.com 1 60 San
Francisco
10 Johnny john@r.com NULL 38 Kansas
City
11 Val v@v.com 2 100 Portland
12 Glen g@g.com 1 39 Honolulu
13 Donna d@d.com 2 50 Tempe
14 David david@d.com 1 49 Trenton
15 Tony tony@t.com 1 22 Singapore
--All values with City that don’t have
the letter L in the City column—
--Using the underscore operator--
--I want to select all the emails like
this 'A@A.com' one character before and after the @ symbol--
Select * from tblPerson where
email LIKE '_@_.com'
1 John j@j.com 1 21 Sydney
2 Mary m@m.com NULL 23 Los
Angeles
4 Sara s@s.com 2 33 Buffalo
5 Linda l@l.com 2 35 Lincoln
6 Kathy k@k.com 2 42 Seattle
9 Sarah s@r.com 1 60 San
Francisco
11 Val v@v.com 2 100 Portland
12 Glen g@g.com 1 39 Honolulu
13 Donna d@d.com 2 50 Tempe
--How to select characters from a name
that letters begin with M, S, T--
Select * from tblPerson where
Name LIKE '[MST]%'
2 Mary m@m.com NULL 23 Los
Angeles
4 Sara s@s.com 2 33 Buffalo
8 Mike mike@m.com 3 56 Austin
9 Sarah s@r.com 1 60 San
Francisco
15 Tony tony@t.com 1 22 Singapore
--How to NOT select letters with MST
using the ^ operator--
Select * from tblPerson where
Name LIKE '[^MST]%'
1 John j@j.com 1 21 Sydney
3 Ron ron@r.com 1 41 Miami
5 Linda l@l.com 2 35 Lincoln
6 Kathy k@k.com 2 42 Seattle
7 Rich rich@r.com NULL 52 Portland
10 Johnny john@r.com NULL 38 Kansas
City
11 Val v@v.com 2 100 Portland
12 Glen g@g.com 1 39 Honolulu
13 Donna d@d.com 2 50 Tempe
14 David david@d.com 1 49 Trenton
--I want all the people that live in
Portland whose age is > 25--
--Using AND & OR operator--
Select * from tblPerson where (City= 'Portland' OR City= 'Honolulu') AND Age > 25
7 Rich rich@r.com NULL 52 Portland
11 Val v@v.com 2 100 Portland
12 Glen g@g.com 1 39 Honolulu
--To sort in ascending order--
Select * from tblPerson ORDER BY Name
14 David david@d.com 1 49 Trenton
13 Donna d@d.com 2 50 Tempe
12 Glen g@g.com 1 39 Honolulu
1 John j@j.com 1 21 Sydney
10 Johnny john@r.com NULL 38 Kansas
City
6 Kathy k@k.com 2 42 Seattle
5 Linda l@l.com 2 35 Lincoln
2 Mary m@m.com NULL 23 Los
Angeles
8 Mike mike@m.com 3 56 Austin
7 Rich rich@r.com NULL 52 Portland
3 Ron ron@r.com 1 41 Miami
4 Sara s@s.com 2 33 Buffalo
9 Sarah s@r.com 1 60 San
Francisco
15 Tony tony@t.com 1 22 Singapore
11 Val v@v.com 2 100 Portland
--To sort in descending order--
Select * from tblPerson ORDER BY Name DESC
11 Val v@v.com 2 100 Portland
15 Tony tony@t.com 1 22 Singapore
9 Sarah s@r.com 1 60 San
Francisco
4 Sara s@s.com 2 33 Buffalo
3 Ron ron@r.com 1 41 Miami
7 Rich rich@r.com NULL 52 Portland
8 Mike mike@m.com 3 56 Austin
2 Mary m@m.com NULL 23 Los
Angeles
5 Linda l@l.com 2 35 Lincoln
6 Kathy k@k.com 2 42 Seattle
10 Johnny john@r.com NULL 38 Kansas
City
1 John j@j.com 1 21 Sydney
12 Glen g@g.com 1 39 Honolulu
13 Donna d@d.com 2 50 Tempe
14 David david@d.com 1 49 Trenton
--Select top 10 records--
Select top 10 * from tblPerson
--Select top 2 records--
select top 2 * from tblPerson
--Select top 2 with specific columns
Name, Age--
Select top 2 Name, Age from tblPerson
--Select top 1 percent from table--
Select top 1 percent * from tblPerson
No comments:
Post a Comment