0:02
[Music] thank you
0:09
[Music] Hello friends welcome back to our channel so in this session we'll see
0:16
select command in SQL [Music]
0:23
so in the previous sessions we have seen a create command and insert command so
0:29
create command is used to create a table and insert command is used to insert the data into a table now
0:35
select command is a one more DML command that is a data manipulation language
0:40
command which will retrieve the data
0:48
we will retrieve the data from the table right so in order to get the data from the table we need to use this select
0:54
command so here the Syntax for this select command is so select
1:03
star from table name
1:14
table name and here you can write a where class where
1:20
the condition so here you can write the condition
1:28
order by order by the column name
1:38
and also having so we can write a having
1:44
and a group by right so all these classes we can write
1:49
but uh in this session we'll see this three and after we will see this having
1:55
end Group by okay so first we'll see this one so here this is the normal syntax and
2:02
here the star the star is nothing but our projection so we have
2:08
discussed this projection Concept in relational algebra right so projection means restricting the column names
2:15
so here we need to specify whatever the columns we required to display that
2:21
particular columns we have to specify here so if you give a star it will display
2:28
all the columns all the columns
2:33
right and if you want to restrict a few columns here you need to specify it as specific columns right so select
2:41
some column name one column name 2 Etc
2:47
from table table name right so only these
2:53
particular columns will be displayed on the result resultant table right and here this is not mandatory where where
3:00
class is not mandated so if you want to get the details based upon the condition then you have to write this where class
3:07
and this condition can be this condition can be a simple condition
3:14
other compound condition so we know what is exactly the condition so so if our
3:21
condition means if our expression is having some is equal to not equal to greater than less than
3:29
greater than or equal to less than or equal to so if you use any field with
3:34
these operators relational operators so these are called as a relational operators
3:40
relational operators so if if our condition is having this relational operators this expression is having
3:47
these relational operators so that we call it as a condition so here we can use either simple condition or a
3:53
compound condition so if you are using the compound condition then
3:58
if you are using the compound condition that comparison between two different simple conditions so you can use all
4:05
and end so you can use these operations these
4:12
operators that means a logical operators we can we call them as a logical operator so these logical operators are
4:18
used to compare two different conditions So based upon the result of the condition the complete results will be
4:24
displayed in the resultant table right so don't worry I will execute all the
4:30
queries so I will execute a query with a simple condition I'll execute a query with the compound condition and I'll
4:36
execute with the query without the conditions I will execute a query with some columns and I will execute the
4:42
query to display all the columns every case each and every case I am going to execute in the MySQL right so this is
4:48
only the introductory part I am showing the syntax and apart from these things
4:54
also have like
5:00
so we can use a like here okay where condition or some this condition in the
5:05
place of condition we can use this like so this is to search
5:11
for a pattern for a pattern so that means if you want to get the details of an employees or a
5:17
student whose name starts with some character so that is a pattern match so
5:23
for that purpose we are going to use this light and in so in means we'll give
5:29
a multiple values and if the condition satisfies with those values the result will be displayed right so search
5:37
in multiple values multiple values and also between
5:47
so between means the name itself indicates will give such in a Range
6:04
excuse me so here we are supposed to give a range of values So based upon
6:09
that particular range the result will be ready okay so here the next one is an
6:15
order by order by two arrange
6:21
arrange in ascending order
6:28
or descending order So based upon the given column the
6:34
complete data will be arranged either in ascending order or a descending order right so all these are the not mandatory
6:40
so this is a normal Syntax for retrieving the data so select star so
6:46
star can be a single column I mean a particular columns or a multiple columns
6:52
all the columns so from a table name so that from this particular table the details will be displayed in the
6:58
resultant table and if you want to filter the changes filter the values so
7:03
for example if you consider any student table if you want to get the student details whose percentage is greater than
7:10
90 so there we are supposed to use a condition so there we have to use this where class
7:16
okay so apart from this uh order by that means if you want to arrange the student
7:21
details based upon the percentages so either ascending model or a descending order you can use this one
7:28
right and also we can search the student details based upon the pattern given
7:34
pattern we can search the student detail based upon in the multiple values giving the multiple values as I input from that
7:41
multiple values we can get the data and also we can give a range of values so the I mean the match or the search which
7:49
satisfies this particular range will be displayed on the screen so I'll execute
7:55
each and every query of this select statement so accept having end Group by
8:01
so we'll see the having group by and the nested queries in our further sessions so this is all about our select command
8:09
it's a very very important because if you want to get the data from the table this is most important but through the
8:15
select command itself we are getting the data from the table so we which is com which comes under the data manipulation
8:21
language right so let's let's move on to the SQL I'll demonstrate each and every case of
8:28
this select statement right so let's move on to the MySQL
8:33
Hello friends just now we have seen the Syntax for select command in SQL and
8:40
this select command is used to retrieve the data from the tables so in the previous session we have seen the
8:46
command how to create the table and how to insert the data into the table now we'll see how can we retrieve the data
8:53
from the table right so one command is a select command which comes under the DML
8:58
commands data manipulation language so just now we have seen the syntax so how many ways we can get the data so let
9:07
us see in the implementation by executing the queries in MySQL so let me open the msql command line
9:13
so give the password I'll clear the screen
9:22
and here we need to use a database so we have created one database called a YouTube database so from this YouTube
9:28
database we have created one table we have that is a student table and we have
9:34
inserted some sort of data into the student table let us check with that one so show tables
9:43
so there is one table that is a student right in order to get the data
9:49
in order to get the data from the table so if you use the star if you use the
9:56
star that implies it will get all the columns the data from all the columns
10:01
see let us check so select command
10:07
star from the table name is a student if you execute this one it will display all
10:13
The Columns of a student table see so if you describe the student
10:18
foreign fields that means the three attributes
10:24
we got all the three data right three the data of all three Fields And if you want to restrict the number of fields
10:30
that means in the relational algebra we have studied about the operations like projection right so that means we can
10:37
restrict The Columns so we can get only the specified columns then you have to
10:43
use this query so select the required column names from the table name so
10:48
select if I just want to get only the roll number and the percentage so select
10:54
Sid comma percentage from student so that it will display
11:00
only the required columns right only student ID and a percentage only the student ID and percentage so if you want
11:08
to specify the data for a particular columns then you have to use this query
11:14
select column name one column name two column name three etcetera etcetera what are the columns you need to be displayed
11:20
so that column names should be represented here from the table name and there is a one more case to get the data
11:26
that is a distinct distinct so that means If You observe
11:31
the data so select a star from student you will get up to the complete data you can observe here so total uh some eight
11:40
student details are there eight student details among these eight student details see what are the distinct values
11:50
that means without any repetition for example if you go with the Sid there is no repetition because it's a primary key
11:55
so we'll get all the eight and coming to the s name you can see there is a one
12:02
one name which is repeated Harry so among this total eight
12:07
data you will get only seven because if you use this distinct okay without
12:12
repetition what are the values right see select
12:18
distinct s name from student
12:24
so you will get only eight see one two three four five six seven because total
12:30
eight among these eight the name Hari has been repeated two times so it will
12:35
not be reflected here only seven rows inside similarly coming to the percentage
12:43
select distinct percentage from student so here also we'll get only
12:51
six because there are repetitions I think see 99 is multiplication 88 is
12:56
another repetition so two repetition repeated values will be deleted so that will get only the
13:02
unique values so for that purpose for to getting that unique count we can go with
13:08
this distinct distinct right
13:13
then the general Syntax for retrieving the data so select the column names from
13:20
the table and where we have to use a where class and give the condition So based upon this condition the data will
13:25
be retrieved and here the condition can be a single condition or a compound condition and single condition means
13:31
using the relational operators right so all these are the relational operator and apart from these relational
13:37
operators we can also use a different cases that is a between so that we can give a certain range and like which can
13:44
search the pattern search for the given pattern and in to select from the
13:51
multiple set of values right so I'll show you the syntax and I will execute
13:56
the query don't worry about that say first let us see the simple condition
14:03
so select star from a student where simple condition that means if I need to
14:10
get the details of a student whose percentage is greater than 85. so
14:15
that's a percentage so select star from student C
14:23
display student details whose
14:29
percentage greater than 85
14:35
right so let's let first one so what you have to do select star from
14:41
student where percentage greater than 85. so you'll get the complete details
14:46
of a student whose percentage is greater than 85 so you can cross check it for so select star from student so you will get
14:55
this one so we are checking for percentage greater than 85 so one zero
15:00
one zero three one zero seven and one zero eight see
15:07
one node zero one three seven and eight total four rows are there right so this is how we have to write
15:14
this this condition so here we are checking only one condition so that's why we are giving with a single
15:20
condition single condition right next so display
15:28
student details whose percentage
15:34
is greater than 70 and
15:42
s name is equal to
15:49
Hari okay s name is so we are checking this one percentage greater than 70 and
15:54
S name so it should satisfy both the conditions so the name should be Hari
15:59
and the percentage should be 70. okay or simply you can see uh we'll check Sid
16:05
greater than one zero three okay so we'll check with the student ID
16:11
whose student ID is ID is greater than 103 and whose percentage is 70. see
16:17
select star from student so if you need to get only a few things we can simply use the
16:23
columns I mean if here you need to give the column I'm giving star so that it will display all the three columns so
16:29
where where percentage greater than 70
16:35
and Sid greater than one zero three
16:42
sorry here the percentage is p r c
16:48
right see we got the details so 1 0 3 and percentage is greater than 70. so
16:55
103 greater than 70 is 1 2 and 3. so the
17:00
total three rows have been retrieved okay so end end means both the
17:06
conditions should be satisfied and if you give the same query
17:12
with or or means so either percentage is greater
17:17
than 70 or SID greater than one zero three we'll get everything so if any one of the condition is true
17:24
the the same result will be given here so that means we will get the details of students whose percentage is greater
17:31
than 70 and whose per whose Sid is one zero three that means a union operation
17:37
so you can simply for better understanding you can see the percentage greater than 70 the result will be there
17:43
Sid greater than one zero three result will be there so you just you apply the
17:48
union operation in between these two operations so it will automatically avoid the duplicate so we'll get the
17:53
complete details right so I hope you understood this one
18:00
and then see this is the third query
18:06
display the student so this is this we call it as a compound condition because we are applying the two different conditions so we can simply say this as
18:14
a compound condition right so similarly less than or equal to
18:19
greater than or equal to not equal to everything you can write it see condition one and condition two and
18:26
condition three and so on condition one or condition two or condition three and so on right and not not that means
18:34
if it is true then it will be false if it is false it will be true so you can see
18:41
so display student details whose percentage
18:48
is not greater than okay not greater than
18:57
uh 90. okay so for this we can simply write so
19:03
select star from student where
19:10
see what is the syntax here simply you can see where not a condition
19:15
where not percentage greater than 70. or 90.
19:24
see so simply we will get the student details whose percentage is not greater
19:30
than 90. right so we are getting only six rows because so there are two rows with a
19:37
percentage 99 which is greater than 90. right so hope you understood so first it
19:43
will check for the condition percentage greater than 90 and not we are applying not that means other than the percentage
19:49
greater than 90 will be written so this is one case okay and the next one is order by order
19:57
by means simply that's an uh arranging the data in ascending order or
20:03
additioning order by default it will be the ascending order so see select
20:10
star from student so let let us arrange this percentage in
20:17
ascending and descending order so if you want to arrange the percentage in ascending or descending order you can use a order by
20:25
so our next query so display
20:30
student details in ascending or
20:39
descending order right based on percentage So based upon percentage we
20:47
need to display the student details so for that you can simply use a select star from student
20:52
so where sorry order by
20:58
percentage so by default it will be the ascending order sorry
21:03
PRC okay so always I am giving the wrong field name so perc right so you can
21:12
observe here it is an ascending order that means lower to higher lower to higher so it will display all the
21:18
student details with the ascending order percentage with ascending order and if you want to get the internet descending
21:25
order okay if you want to get it in a descending order you need to specify this particular desc
21:32
let us say so select star from student
21:40
order by percentage Des here you need to specify
21:47
desc so that we will get the details in a decent border designing model similarly you can also use a select a
21:54
star from student order by
21:59
yes name so yes name means based upon the alphabetical order details the student details will be arranged g h r s
22:06
v if you want to give in the descending order simply you can use so desc so that we'll get the complete
22:14
details in a descending order with respect to the name with respect to the name
22:19
so this is how we can arrange the data in ascending order descending order and the next one
22:25
limit the number that means so based upon the condition we may get a number
22:31
of values okay we may get the number of values but if you want to limit the number of data right so if you want to
22:36
get only the few data some uh top two data right so we can
22:42
limit that data to be retrieved by using this limit class for example
22:48
C so select star from student so there are a lot of data so you can
22:55
get see select start from student where percentage greater than
23:03
80. so we are getting the complete details right from the starting of the Sid we are getting
23:10
the complete result whose percentage is greater than 80. now you can limit this
23:16
one so I just want to get only the three details the first three details so then
23:21
so select star from student
23:26
student where percentage greater than 80 limit
23:32
to 3 that means I need to get the first three values sorry
23:39
yes just a second
23:44
yes
23:53
from student right see we'll get only three we'll get only the three because we are
24:00
limiting the result to 3. right so the query will be display
24:08
student details okay whose percentage
24:15
is greater than 80 so this is also display
24:22
first three student details first three student details whose percentage is
24:27
greater than 80. So based upon the Sid okay based upon the Sid that means a student ID will get the details if you
24:34
Google here so greater than 16. limit to 3 so we'll get only limited to
24:40
3. so if you go with the limit to 5
24:46
we'll get a 5 top 5. so here you can observe one zero five is missing
24:53
select star from
24:58
student okay where Sid
25:03
is equal to one zero five let us check only the percentage okay why this percentage I mean why this one zero five
25:09
is not getting right so select percentage student from Sid is equal to
25:16
105 so the percentage of 105 is 40 which is
25:22
not greater than 60 so that's why we didn't get this value 105 results in the
25:28
result right okay I hope you understood we can limit the number of results
25:35
so in a class there might be some 100 students so we need to get only a few
25:40
students so that's why we got this one okay so hope you got this one
26:30
C so this is how we can get the data that means a top three
26:37
rankers in the class okay so let us write down the query here
26:43
first so that you can understand so display first
26:49
three ranks based on percentage okay
26:55
from the student table right from the student table display first three that means the top
27:03
three ranks top three ranks based on percentage from the student
27:08
table right
27:14
so for that what we have done so simply we have created one query select star
27:19
from student order by percentage descending so this will automatically display all the details in the
27:26
descending order based upon the percentage in additional order here we are limiting the three limit to 3 so
27:32
that we'll get the top three rankets here right so hope you understood this one why we are using this limit
27:39
okay so if you want to get a list last three ranks so simply you can use a
27:44
select star from student order by percentage so it will be in
27:51
ascending order so limit three so I will get
27:57
the least three ranks in the table you can observe you have select star from
28:02
table student so the last ranks are 40 65 40 and 77
28:11
only these three so see 40 65 and 77 so this is the one use of using this
28:18
limit that means we can limit the result so we may get a multiple values multiple
28:23
rows but we can limit these multiple rows and then
28:29
like it's a pattern pattern that means search pattern if you want to search a
28:37
particular pattern we can use this like okay so like pattern here we'll write
28:43
the query first so if you want to display the
28:48
display student details whose name
28:55
starts with a or some alphabet yes
29:01
okay so if you want to search this particular pattern you can you have to use use this like okay so select a star
29:09
from student where s name like and here the
29:16
percentage symbol gives a multiple values okay percentage
29:21
symbols give them multiple values so a like pattern so what what is the query here so starts with the S so like yes
29:32
percentage that means it will it should starts with yes and followed by there can be any number of characters
29:40
see any number of characters it should starts with yes and followed by any number of characters
29:47
right so this is a pattern search pattern search here we are giving a pattern
29:53
here we are giving a pattern so if you want to give this double e if you want to get the details of a student where
29:59
there are consecutive ease so display
30:06
student details
30:11
whose name how e e
30:20
anyway whose name have ee anywhere so for that
30:27
purpose we can select this one so select the query where you can simply use a
30:32
percentage that means multiple elements or give here e e percentage e e
30:41
and a percentage okay and also e
30:47
so that implies sorry so here you can see so it can have multiple values and ee
30:56
consecutive ease and multiple values so you'll get the result you'll get the
31:02
result here okay so this is how we can use this pattern search pattern search if you
31:10
want to get the data of a student details whose names ends with some particular I
31:16
so you can use percentage I so that you'll get the
31:21
complete student details whose name ends with I right so hope you understood this one
31:28
that is a like like to search for the pattern and
31:33
in column name in you can give multiple values if the column name is having
31:39
these values it will execute
31:44
see so display student details who's
31:52
percentage is either 90 99
31:57
88 75 okay so we have to display the
32:03
student details whose percentage is either 1999 88 and 75 so you can simply
32:08
use a simple condition okay compound condition by checking the percentage is equal to 90 or percentage is equal to 99
32:15
or percentage is equal to 88 or percentage is equal to 75 but by using this in also we can execute the query so
32:23
let us check this one so select star from student where
32:29
percentage in so give the values here what are the values we have given here so 90
32:37
99 and 75. so you'll get the complete
32:43
details whose percentage is satisfies with these particular values so for example you can also give the 40
32:49
here see so you get the details 9988 40 all
32:55
the three has been there right so there are no student details whose percentage is 90. okay
33:02
this is exact match it's not a between okay we are not giving any range we are
33:08
checking the percentage with exact matches either 90 or 99 or 88 effort
33:14
right so the next one is a between that means a range range so
33:22
the query will be like this so display student details
33:28
whose percentage is between
33:34
85 and 90. or 95 85 and 95
33:42
so here we have to give the range right so in order to give the range you can use this BTV
33:49
between okay so you simply will execute the query so select
33:57
star from student where percentage
34:05
between 85 and 95 so it will display all the
34:11
student details so whose percentage is in between 85 and 95 and if you change this 95 to
34:19
100 you'll get the details see in between 85 and 100
34:26
right so this this is how we can execute the query to retrieve the data that
34:33
means there are a lot of cases so still there are a lot of cases so there are we
34:39
are having a two more cases that is by using uh have have by okay Group by and
34:46
having group by and having classes so we can also retrieve the data by using
34:52
these two classes so that means a ha having n Group by and also we can use a
34:57
sub queries okay sub queries nested queries so we'll
35:02
see all these things in the further classes right so hope you understood this uh select statement how many ways
35:08
we can get the data from the table we can use a simple condition we can use a
35:13
compound condition or we can use a between we can use a like we can use in
35:18
we can use the order by that means we can change
35:23
the order right so based upon ascending order or the descending order and also we can limit the number of results we
35:29
can limit the number of results so hope you understood this one so if you are having any doubts regarding this queries
35:35
select statement so feel free to post your doubts in the comment section definitely I will try to clarify all
35:42
your thoughts if you really enjoyed my session like my session share my session with your friends and don't forget to
35:48
subscribe to our Channel thanks for watching thank you very much