Select statement in sql server - Part 10 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to presume technologies I
am blanket this is part 10 of signal
server and this session will talk
everything about the select statement in
signal server well first see how to
select specific columns or all columns
okay now if we want to select all the
columns from all columns and all drawers
from TBL person table we'll say select
star from TBL person when we execute
that query we get all the results all
the columns and all the rows now another
way to select all the columns from a
table instead of specifying the star
here you can specify the column list and
if you're lazy typing in all the column
names you can ask sequel server to write
the query for you automatically
so go into the database go into that
table right click on the table script
table s select two new query editor
window so what it is going to do for us
it's it will write the query for us look
at this these are all the columns I want
from this table but then if you look at
the table name here in the from class we
have a three part table name okay the
first one sample is nothing but the name
of the database and the second part is
the schema and the third part is the
name of the table itself now we haven't
spoken about database schemas yet we'll
be talking about them in a later session
okay so this is the fully qualified name
of the table now what's the advantage of
having a fully qualified name even if
you don't have the context of the
database selected look at this I am in
the context of the master database but
since I have a fully qualified name and
I give this query the query gets
executed fine but on the other hand if I
don't have the fully qualified name if I
just have the table name and if I try to
execute this query obviously it will try
to find the table in the master database
and we get an error so the advantage of
having a fully qualified name of the
table is that you can run it from any
database context okay so obviously to
select all the columns from a table you
can either use star or you can specify
the list of columns okay for performance
reasons it's always better to receive to
specify the entire column
rather than just saying stop all right
so that's the first thing
selecting specific columns are all
columns now how to select distinct roles
let's understand what we mean by that
okay
in TBL person table you see that you
know if you look at the city there are
some duplicates there are two people
living in London now let's say I want
all the distinct cities that are
available in this table how do I do that
I can use the distinct keyword so select
distinct city from TBL person so when I
execute that look at this we have a
total of five rows out of which London
is repeated twice and I want distinct
cities from this table so when I execute
this I only end up getting four rows
okay London is repeated only once London
Mumbai New York Sydney now let's execute
this one again now let's say I want
distinct city and the name of the person
okay so let's say now let us see how
many rows we get and what result we get
so when I execute this query look at
this I got five rows back and why did it
give five rows and if you look at this
London is repeated twice now it's not
giving me distinct the city okay but we
have used distinct keywords so what
happened to that now when you are saying
distinct keyword and multiple columns
you are actually telling sequel server
the value should be distinct across
these two columns so if you take these
two columns you know they will be
distinct so when you use distinct
keyword across more than one column you
are specifying it's enough for the
values to be distinct across all those
columns you know when you put them
together so that's selecting distinct
records using the distinct keyword
filtering the values with the bayit
class now when I say select star from
TBL person I get all the records from
the table now let's say I want the
people
I want only the people who are living in
London how do i filter them you use the
vert class okay so you use the weight
loss and you specify your condition in
the weight class so what's your
condition in the weight class I want all
the people who are residing in the city
of London so London so what's going to
happen this expression will return a
true or a false if it returns true then
that record is you know selected for the
result set otherwise not so there are
two people living in London so when we
execute this query we should get only
two records okay and you can also we'll
talk about that in a bit anyways so so
you can use the where clause basically
to limit the number of rows that you you
don't want to return now if you if you
want to select all the people you know
who are not residing in London instead
of specifying multiple city names here
what you can do is you can specify where
city not equal to by the way that's
still not equal to operator in sequel
server greater than and less than symbol
so not equal to London when I press f5 I
get every person you know who are not
living in the city of London okay and
you can use another operator that is you
know exclamation and equal to even this
is considered as not equal to so you can
either use greater than or less than or
exclamation equal to all right so now
when you use the ver class to filter the
rows there are several operators that
you can use with the via class we have
just seen how to use equal to n not
equal to let's see what other operators
are available for us so these are the
variety of operators that are available
so we have seen how to use equal to n
not equal to
similarly you can also specify greater
than or greater than equal to less than
or less than or equal to and these are
very easy to use obviously they are
self-explanatory
apart from these operators we also have
other operators for example in in
operator you know this is extremely
useful now if we look at all the people
that we have in our database okay there
is an age column and there are several
people with
different ages now let us say I want to
select all the people whose age is 20 23
and 25 so how do I do that
you want to specify multiple filters so
you can say where age is equal to 20 or
age is equal to 23 or age is equal to 25
instead of specifying multiple
conditions look at this if I have to do
that I want to select people with age is
equal to 20 or age is equal to 23 or age
is equal to maybe 29 so if I have to do
that I will get all the people whose age
is 20 23 and 29 okay but it's many
conditions you know many conditions can
catenate using the or symbol okay
instead of doing this you can actually
rewrite this query using the in operator
select star from TBL person where age n
you can just say 20 comma 23 comma 29
and this is much simpler and easier to
understand as well so I want all the
people whose ages within that list so if
you want to specify a list of values
then you use the in operator okay now
you can also use the between operator if
you want you know let's say for example
I want all the people you know whose age
is between 20 and 25 I can use the
between keyword okay so select star from
TBL person where age between
20 and 25 so when I execute this query I
get all the people whose ages between 20
and 25 and this these boundary
conditions 20 and 25 are inclusive okay
so to specify a range of values then we
use the between operator now and the
interesting and very useful operator is
the like operator which can be used to
specify a pattern now let's see how to
use the like operator let's select all
the rules that are present in TBL person
now let's say I want all the people
whose city name starts with L okay so
how do I I do that select star from TBL
person where city is equal to you know I
want all the people who resides in a
city that starts with letter L so you
can say L so it starts with L but
percentage okay
now you cannot use equal to here I want
to use like operator so what you are
saying is the city column should have
this pattern it should start with L and
after L I it can have any character okay
so if we execute this query all the
people living in London okay let's get
all of them back again let's say I want
to find out all valid email addresses
now obviously you know one of the quick
things that we can check if to find out
if an email is valid or not we can find
an @ symbol if there is an @ symbol in
their email address you know you can at
least say okay it's the right format of
the email so I want to find out all the
email addresses which has got an @
symbol within that how do I do that
I can say @ symbol there but they should
be something in front of it any
character in front of an @ symbol
any character after the @ symbol but
they should be an @ symbol in the email
column so where email like so when we
plus you know total we have five rows
with
the table when I execute this query I am
only getting four rows which means there
is one invalid email now let's find out
what is that invalid email you can use
the not operator you know give me all
the rows which you know not like this
pattern so when I press f5 so Mary
didn't provide a valid email address she
is missing the @ symbol we know that
every valid email should have that @
symbol so basically you can use the like
operator with you know with for for
specifying a pattern so we use the like
operator to specify a pattern and with
the like operator we use something
called wildcards
so here the percentage is a wildcard we
are not specifying what exactly is the
character it's it's just like a
placeholder or a substitute for some
characters it could be zero or more
characters okay so there are several
wild character wild cards in sequence
server that we can use one of them is
the percentage which can be used as a
replacement or a substitute for zero or
more characters and along the same lines
we have an underscore this is underscore
not dash underscore which is used as a
substitute for exactly one character
let's understand what we mean by this so
if you look at TBL person table okay now
let's say I want to select all the email
addresses that has exactly one character
before the @ symbol and one character
after the @ symbol how do I do that you
can say okay I want to select all the
email addresses which has got one
character before the @ symbol okay if I
have an email address something like
this a at a dot-com or b @ be com
I want to retrieve all the email
addresses like this which has got one
character before the @ symbol and one
character after the @ symbol after that
it needs to be calm so calm so I want
something like this okay so you are
using the underscore to specify there
should be only one character before and
after that @ symbol now if I execute
this
you should say we get all the email
addresses okay now if you look at this
we got the negative results that's
because we use the not operator there so
when I plus f5 we get those email
addresses which has exactly one
character before and after the @ symbol
so we can use underscore for that to
specify exactly one character and you
can use a square brackets you can use
the square brackets to specify a list of
characters now let's say I want to
select all the people from TBL person
table maybe whose name starts with
specific characters now let's say if a
person name starts with T I want that
person if an if a person name starts
with M or s I want that person name okay
so basically we want all the people
whose name starts with M s T okay how do
we do that
select star from T be a person their
name like you specify the square
brackets and within the square brackets
you can specify and obviously you need
to have single code by the way keep in
mind any time you are using a character
or a text data type okay that has to be
specified within single quotes if it's
numeric values then you don't have to
specify you know single quotes but if it
is text data text values you will have
to specify that in single quotes okay so
we wants all the people whose names
starts with M s T and after that it can
be any characters we don't really care
but the name should start with M s T so
when we execute this query now you
should say everybody whose name starts
with M s and T and after that it can be
Tom Mary you know ary r sa ra so
basically we use square brackets to
specify a list of characters and if you
want to select all the people whose
names doesn't start with MSD all you do
is you specify this symbol here okay
specifies not in this list so when we
execute this query you should see that
which we will get everybody whose name
doesn't start with MSD in this case Josh
records so these are the wild card
operators that can be used with you know
a pattern so we have seen how to you
know filter the rows using the weight
class and we also seen how to use the
wild cards that are available in sequel
server and we can also join multiple
conditions using and and our operators
have seen an example of that in a bet
now let's say I want to select all
people people who are living in London
or Mumbai and whose age is greater than
25 greater than or equal to 25 okay so
how do we do that
so here the conditions are there are
multiple conditions I want all the
people who live in London or Mumbai and
their age should be greater than 25 so
select star from TBL person where city
is equal to London or city is equal to
Mumbai and age should be greater than 25
okay so you have to put this parenthesis
here between these two conditions
because what we said the city should be
equal to London or Mumbai and at the
same time their age should be greater
than 25 so one of these two conditions
should be true and the rate should be 25
so you can join multiple conditions
using or and and operators so if you
select this query obviously will get all
those people who are living in city or
you know London or Mumbai and at the
same time their age greater than 25
and we can use order by Clause to order
by Clause to sort the results of a query
so let's say when I select all these
rows from the stable okay
they are coming in this in the order of
this primary key one two three four five
only this primary key is ordered now
sorted now let's say you know I want to
retrieve all the people from this
database but their names should be in
ascending order of alphabets okay how do
I do that select star from t-bill person
or the by we use order by class to you
know sort the results name so when I
press f5 what it what it's going to do
it's going to sort these results by the
name and look at this it's sorted by
default in the ascending order okay if
you don't specify this sort order by
default it's the ascending order you can
also specify to sort the name in
descending order by using the des a
keyword so when I plus a five now the
names will be sorted in descending order
and it's also possible you can sort by
multiple columns for example I want to
sort first by name in the descending
order
then by age in ascending order is that
possible absolutely okay order by name
in descending order
then by age in ascending order you can
specify as many columns as you want so
it sorts first by name and then by age
and descending order look at this you
know name is in descending order and
within this name Josh is repeated twice
so their ages are sorted in ascending
order because you specified sort by age
in ascending order and selecting top n
or top percentage of s this top keyword
is especially useful if the table is
large okay let's say I have a table
where I have thousands and thousands of
records and I just want to see a sample
set of records in that table but when I
say select star from TBL person it's
going to retrieve all the rows from that
table and it might take maybe two to
three seconds depending on the size of
the table and it can affect performance
negatively so I if you want to see just
the top you know
ten or five records of a table you can
use the top keyword how do we use that
you can just say select top maybe I want
the top 10 records from this table so
select top 10 star from TBL person which
only gives 10 records we don't even have
10 records here so let's say select top
2 records so select top 2 star so it
gives you all the columns you can either
specify star here are the columns that
you want let's say I just want the name
column and maybe age column you can
specify the columns as well so select
top 2 name comma age or if you want all
the columns you just say top to start so
you can specify an absolute number here
or you can specify a percentage let's
say I want only top 1% of the rows ok
you can also use the percentage so when
I press f5 top 1% in 5 is only 1 okay on
the other hand let's say top 50% it's
about 2 to 3 records in the five records
that we have so we get 3 records top 50
percent so you can either specify an
absolute number top 10 records or top
you know n percentage not ten percent 50
percent etc now you know the stop
keyword is especially very useful to to
achieve some things let's say you know
one of the common interview questions is
how do you find the top salary of an
employee ok in our table we don't have a
no salary column but we have an age
column let's say I want to find the
eldest person in this table how do I do
that you can use the top keyword for
that actually so let's see the select
star from TBL person order by age de se
so what is this going to do this is
going to select all the records from TBL
person table ordered by age in
descending order so if you look at this
josh is the one who has got 29 years of
age and that's the top record so what
you can do here is select top 1 star so
what it's going to do on this ordered
result it's going to give you the top
record which is nothing but the eldest
person
in our table okay similarly you can do
this you can apply the same logic for an
employee table and salary column where
you get the top or you know paid
employee so we got Josh whose age is 29
years all right on this slide you can
find resources for asp.net and C sharp
interview questions that's it for today
thank you for listening have a great day