YT YouTube2Text

YouTube2Text → Transcripts → Introduction to SQL/2

Introduction to SQL/2 — Transcript

by Data Base Management System - IITKGP · 4,528 words · 843 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00[Music]
0:15welcome to module 7 of database
0:18management systems
0:20ah this is a second
0:22part
0:23out of total three of introduction to
0:26sql
0:27in the
0:28last module we have discussed about
0:31the evolution of sql
0:34the data definition language part of it
0:37and the basic structure of queries
0:40in the
0:41current module we will
0:43complete the understanding of the basic
0:45query structure and will see how
0:48common
0:49set theoretic operations can be
0:51performed in terms of queries
0:54we will familiarize ourselves with the
0:57handling of null values and aggregation
1:01operation that will be frequently
1:03required for forming queries
1:05so this is a module outline these are
1:08the topics that we will discuss so we
1:10start with the ah discussion of some
1:12more basic operations in the query
1:16we
1:17have already discussed this that
1:20if we do select
1:21star from two tables then it results in
1:24a cartesian product we have seen this
1:27ah result earlier
1:29the
1:30now by itself as we had said that by
1:32itself the cartesian product may not
1:34really make a lot of
1:38you may not be very useful
1:40but suppose we want to ah answer this
1:44kind of a query that find
1:46all names of all instructors who have
1:49taught some course
1:51and
1:52ah for those
1:55you also write the course id
1:57so what we are interested in is the name
1:59of the instructor and course id so we
2:02put that on the select class
2:04ah these are the two tables that are
2:06required because ah the name of the
2:08instructor is there in the instructor
2:11table relation and
2:13then the relationship between
2:16which instructor teach which course is
2:20in the teachers table so in the
2:23instructor table we have the name
2:25in the
2:26teachers table here the course id and
2:28also this relationship as to
2:31which
2:32course is taught by which teacher and
2:36here we have
2:37the
2:38relationship between
2:40which id of the instructor has which
2:43name
2:44so
2:46what we do is when we
2:48do this cartesian product we will get
2:50something like this as we have already
2:52seen
2:52but we want to qualify it by with a
2:55predicate we say that we will
2:58out of all these combination will choose
3:00only those where the instructor id
3:02equals the teacher's id
3:05so if we look at
3:07say
3:08in a in a in the first row the
3:11instructor id is this
3:15teaches id
3:17is this
3:18which is id of the teachers table they
3:20are same so it says that this instructor
3:23srinivasan
3:24actually teaches the course cs101
3:28whereas if you look into this row
3:33it says that instructor id is this and
3:36teaches id is this
3:37and we are really not interested in this
3:39combination because this combination
3:41does not convey anything meaningful
3:44so
3:45by the use of this where clause we will
3:47try to choose only those records where
3:51these two ids are same which will tell
3:53us that this particular instructor
3:55actually teaches that particular course
3:58so
3:59if we do that
4:01ah then
4:05you will find that
4:06majority of the records that
4:09actually
4:10came about in the cartesian product are
4:15eliminated from this result so i have
4:17struck them out
4:19so you can currently see in this part
4:21you can see only four records the three
4:24courses taught by srinivasan
4:27so and the one core stored by you so in
4:31the output you will have
4:37in the output
4:39you will
4:41have this name
4:43this name part
4:46this
4:48name part
4:49and
4:50this course id part
4:52because you are projecting on these two
4:54so this will be the output table which
4:56will get generated
4:58and just to remind you this is the
5:01notion of natural join that we had
5:04discussed in relational algebra ah in
5:06this case we will actually call it
5:08equijoin because we are using a equality
5:11condition
5:12after the cartesian product to join
5:15these two relationships
5:17so this is a very critical
5:20ah operation in many of cases in our
5:23database query system
5:26there is and this is another extension
5:27of this ah
5:29similar ah example
5:31so here we have added another
5:34predicate in the where clause specifying
5:37that instructor dot department name
5:40is art so which means that this will now
5:43give the names of all instructors
5:46in the art department only who have
5:48taught some course and specify their
5:51course id so in different such ways you
5:53can manipulate and create
5:55queries
5:57it is possible to read them we have
5:59already seen examples you can rename a
6:01relation you can rename an attribute and
6:04the style is to use as so here you can
6:07see that in the select
6:10query we have said from instructor as t
6:13so
6:14the name of this relation can be treated
6:16as t and we again see that as instructor
6:20instructor as s so actually what we are
6:23doing we are doing a
6:25join between
6:27the same relation instructor and
6:30instructor
6:32so and we are trying to find out all
6:34instructors who have higher salary than
6:36some instructor in computer science so
6:39the sum instructor in computer science
6:41is specified by this condition because
6:43department has to be computer science
6:45and the fact that salary is higher so as
6:48if you treat that though it is actually
6:52a join between
6:54a
6:55product between
6:57instructor and instructor the same
6:59relation but you by renaming you treat
7:01them as if they are two different
7:04ah tables having name t and s and then
7:07it becomes easier to write this kind of
7:09query so ah otherwise it is it is quite
7:11difficult to write
7:13this query to find out because you need
7:16to actually create a product of one
7:19relation with itself
7:21keyword as is optional you can just
7:24write instructor and then the name the
7:27new name that you want to give and that
7:28itself will work
7:31here is another cartesian product
7:33example ah here given a relation ah
7:36which is which list a person and
7:40he is or her supervisor we want to find
7:42out
7:43all supervisors direct or indirect of
7:46that person so i leave this as an
7:49exercise to you to think over as to how
7:52we can actually compute this
7:55query
7:58supports several string operations and
8:00of particular interest are two specific
8:04symbols characters which allow
8:08us doing certain match percentage is
8:10used to match any substring and
8:13underscore is used to match any
8:14particular character and we use a
8:18keyword like to find out different
8:21string patterns that can be matched
8:24so
8:25here we want to find the names of all
8:27instructors whose name includes the
8:29substring d a r
8:31and ah by by writing this so you are
8:34saying the predicate is formed is name
8:37like this so
8:39what is there is a percentage before
8:41there is a percentage after so anywhere
8:44dar will feature in the name
8:46this predicate will turn out to be true
8:49otherwise if there is no dir in the name
8:52the predicate will turn out to be false
8:53and that particular record will not get
8:56selected
8:57so in this way we can using like we can
9:00actually
9:02do different kinds of string operations
9:04as conditions in the where clause or
9:07else so
9:09now naturally this
9:10brings in an issue of what if my string
9:13itself
9:15has a percentage or an underscore
9:17character so the rule followed is you
9:20will need to escape that with the
9:23escape character that you define this is
9:25a style which you have seen in c
9:27programming as well
9:30patterns are certainly case sensitive so
9:33it depends
9:34it will distinguish between uppercase as
9:37well as lowercase
9:38and these are different examples of
9:41string matching that you can do where
9:43you can match at this beginning of a
9:45string end of a string anywhere in the
9:47string specific number of characters in
9:50a string and so on
9:52ah sql supports ah
9:54concatenation
9:56conversion of lower to upper case and
9:58vice versa and different other common
10:01string operations those are available as
10:04functions in sql and can be used for
10:07convenience
10:10now let us address a different question
10:12let us say we have computed a query
10:15and then often we would want that the
10:18result
10:19be ordered
10:21in according to certain order
10:22particularly the value of certain field
10:25if you want the result to be ordered
10:27then sql allows you to do that by
10:29another clause that you add to the query
10:32which is called order by so what this
10:34will do we have already seen this query
10:36this will find out the names of all the
10:38instructors ah
10:40and
10:42the names will occur in a distinct
10:43manner because distinct is specified but
10:46then the output will be in terms ordered
10:49by the name
10:51and the ordering can be
10:53[Music]
10:55descending or ascending
10:57by you can control that by specifying
10:59whether you want descending or ascending
11:01by default the ordering is ascending
11:04so that makes the presentation of the
11:06result often very easy
11:08and you can certainly sort on multiple
11:11fields as well so it can be ordered
11:13based on combination of fields
11:16sql ah
11:17where clause also allows
11:19between as a comparison parameter so
11:22between can specify two values so that
11:25whenever the field value will be between
11:27these two
11:28ah given values the condition will be
11:31predicate will be taken to be true
11:33otherwise is taken to be false
11:35you can compare based on tuple as well
11:38so
11:39in this case you could have written
11:43you could have
11:45checked for equality of instructor id
11:47with teachers id and
11:50department name with
11:52the literal biology but you can compact
11:55it by writing a tuple notation as is
11:57shown here so these are common
11:59convenient ways of writing different ah
12:02where clauses
12:04now we have ah
12:05specified that
12:06sql does ah
12:09carry duplicates so
12:12unlike relational algebra
12:14which said theoretically specify that
12:17their duplicates should not be there a
12:20an sql there could be duplicate entries
12:22in the same relation
12:24so
12:25there is a
12:26this is called when duplicates are
12:28allowed in set theory then such sets
12:31where duplicates are allowed and known
12:32as multi sets
12:34so
12:35there are multiset versions of the sql
12:38queries or so to say the relational
12:41algebra operations so you have a
12:43selection um which
12:46can be multiset selection which means
12:48that
12:49if there are certain c one number of
12:52copies of a tuple in the relation which
12:54satisfy the condition theta then all of
12:56them will feature in the result
12:59and all those ah
13:02copies can be seen simultaneously
13:04because it is a multiset condition
13:06similar definitions are
13:08hold for projection as well as for
13:11cartesian product so i will leave it to
13:13you to go through the details and
13:15convince yourself that these multiset
13:18relations really extend the
13:21traditional single set distinct
13:24definition of the relational algebra
13:28so here is an example where there are
13:30two multiset relations as you can
13:33see
13:34particularly this one which has
13:37identical duplicate entries so using
13:40that
13:41you can define a
13:45cartesian you can define a projection ah
13:47of ah
13:49r one
13:51on b
13:56r one one b which will ah certainly
13:59ah give you its you are doing projection
14:01on b so it will give you a only
14:04so you will have this result itself will
14:06be a multiset because you will get two
14:08a's so this result will be like a
14:11a
14:14and then you have r two with which you
14:17are doing the cartesian product so you
14:19will have all possible
14:21combinations all these six are the
14:24result in the sql
14:26whereas in set theoretically
14:28the result should have been only
14:30these two tuples
14:39now we
14:39take a quick look into the common set
14:42operations
14:43so it is possible to ah do union ah
14:47intersection difference kind of
14:49operations very easily with sql queries
14:52so ah suppose we want to find
14:55all courses that
14:56ran in fall 2009
14:59or in spring 2010
15:02so certainly the first part of the query
15:04is simple this will give you all courses
15:06that
15:07ran so you are taking out the course id
15:10from section is where the course
15:14running information is provided and you
15:16are putting two conditions which say
15:17that they actually this courses ran in
15:20ah
15:21fall 2009
15:23so this is the first query the second
15:25query says the courses that ran in
15:27spring
15:28ah 2010
15:30and you are you have an or condition in
15:32the
15:33statement of what you are looking for so
15:35you do a union union is another keyword
15:38so this will simply give you a relation
15:41of ah the course id attribute as the
15:44only attribute which has records from
15:46the first as well as the second query
15:50similarly you can
15:53find out uh
15:56the courses that ran both in fall 2009
15:59and spring 2010 by using intersect
16:03which basically give you the
16:04intersection of the result of the first
16:06and the second query
16:09you could also do
16:11difference set difference by doing fine
16:13courses that ran in fall 2009 but not in
16:172010. so what will that mean that will
16:20mean that the result of the result of
16:22this first query
16:24from the result of the first query the
16:26results of the second query be
16:28subtracted
16:29be done a difference from so those
16:32that
16:34had run in the fall 2009
16:37and then was again run in spring 2010
16:40will get removed we so that is done
16:43through the accept
16:45keyword so in this way you can very
16:47easily do set operations whenever that
16:51is easy to conceive obviously you can
16:53write these queries in
16:54several other different forms but this
16:56is just to show you how set theoretic
16:58operations can be easily written
17:02ah you can do ah set operations
17:05like
17:05this in terms of
17:07ah find salaries of all instructions
17:09that are less than a largest salary so
17:12again we are using renaming
17:14to ah think of the same relation as 2
17:18and then as if
17:19from the
17:20relation t
17:22we are trying to look at relation s and
17:24finding out what are the salaries which
17:27are smaller than that and certainly
17:29whatever comes in
17:30out is
17:32[Music]
17:34the one which is not the largest because
17:36certainly the largest will not satisfy
17:38this particular condition because it
17:40will get compared with itself
17:46you can find salaries of all instructors
17:49and then you can find the largest salary
17:52so
17:53this is
17:55all salaries which are less than largest
17:58this is all salaries including the
18:00largest so what happens if you subtract
18:04that is from from this if you subtract
18:07this
18:07from all salaries if you remove the
18:10salaries that are not largest naturally
18:12what you get is the largest salary so
18:14this is a interesting way to find the
18:17largest salary we will see later on that
18:18there could be several other ways
18:20particularly the use of aggregate
18:22function which make these computations
18:24easier to perform
18:26but these are the typical ways to use
18:28set theoretic operations
18:30the set operations ah
18:32so we have seen three of them union
18:35intersect and accept
18:37ah they automatically these operations
18:39are set theoretics so each of them
18:41automatically eliminate the duplicate
18:44unlike what
18:45sql by default scale by default does
18:49what
18:50allows duplicates but set operations
18:52will eliminate duplicates because they
18:54are set operations so if you want the
18:56sql type of behavior if you want the
18:59duplicates to be preserved
19:01then you can have a multi set version of
19:03this set operations which are known as
19:05union all intersect all except all like
19:08that
19:09and naturally ah if you do
19:12these operations then here is the simple
19:16formula of the number of tuples that
19:18will get computed in different cases so
19:21you can study and convince yourself that
19:23these are the correct numbers
19:27ah let us go to ah the treatment of we
19:29we talked about null values that we said
19:32that it is possible that
19:34certain
19:37records
19:38in a relation may have one or more
19:41attributes where the value is not known
19:43and to represent that the value is not
19:45known
19:46we are putting a placeholder called null
19:50so let us see what is the consequence of
19:52that null value
19:54in terms of doing this query operations
19:57so the null signifies an unknown value
20:00so if i
20:00do 5 plus null then naturally the result
20:03is null
20:04so what you are saying that i am adding
20:06an unknown quantity to 5
20:09so then what would you say is the result
20:10is unknown so that is the basic
20:12semantics of adding null to a number
20:16so it is possible to check
20:19if
20:20particularly a field
20:22is null for a record and that is done by
20:25a predicate is null so
20:27ah in this particular query we are
20:30trying to find all instructors
20:33whose
20:34salary is null that is not not known so
20:38this is a predicate so for a particular
20:40record for which salary is null
20:42this will become true and that will get
20:44included in the result
20:46but for all records for which there is
20:48some value for the salary so salary is
20:50known it is not null those will not get
20:53included in the result
20:55so
20:58the basic ah
21:01semantics of null is then
21:04ah combined with the
21:06truth values
21:07because we know our basic predicate
21:10logic is two valued true and false but
21:12now you have a third value unknown that
21:14is you may not know the value of a
21:16predicate so
21:18how does it ah play around with the true
21:20and false values ah you can
21:23reason through that quite easily if you
21:25are comparing with the null in whatever
21:27way
21:28ah then naturally the result is unknown
21:30so it returns a null
21:32ah if you are doing any connectives for
21:34example if you are doing or of
21:37null or true
21:39then the result should be true because
21:41in or
21:42ah we say that if any of the components
21:45is true then the result is true so here
21:47you do not need to know what is that
21:49unknown value you can say it is true
21:51but if you do
21:54if you do
21:56or with false or of unknown with false
22:00the the second row or of
22:03unknown with false
22:05if you do this
22:07then naturally this is
22:09unknown because
22:11since this is false
22:13the result would be true only if unknown
22:16value is true
22:17and the result would be false if the
22:19unknown value is false you do not know
22:20what that unknown value is so you have
22:22to say that your result is unknown
22:24so using that same ah
22:26logic you could ah see verify i would ah
22:30ask you to verify offline
22:32at home you please verify that all these
22:36combinations of true false with unknown
22:38are valid so
22:40if p is unknown is ah evalu will is as a
22:44predicate will evaluate to true if p is
22:47not known
22:56now ah we come to the aggregate
22:58functions ah there are several aggregate
23:01functions they can be used for
23:04convenience
23:05and these are the
23:08common ones that
23:09operate on the multi set values
23:12naturally aggregate functions
23:14operate on a particular column they try
23:16to aggregate on a particular column
23:18and return a single value for example
23:21average would be meaning that you are
23:23trying to find average of the values of
23:26a particular column
23:29so here is an example
23:31so we are trying to find the
23:34average salary of instructors in
23:37computer science department
23:39so naturally what you output
23:42is average salary so mind you this will
23:45this output relation will have one
23:48attribute which is average salary
23:50and
23:51since average salary
23:54is a
23:55single quantity it will only have one
23:57record
23:59and here i have made use of this
24:02aggregate function average so it says
24:04you do average
24:05on the attribute salary
24:09and where do you get that attribute from
24:10you get fit from the
24:12table instructor
24:14and then we are saying that we are not
24:16interested to find average of salary of
24:18all instructors
24:19we are interested to find the average
24:22salary of those instructors who work for
24:25computer science
24:26so you put this where clause
24:28so this will ensure that you find the
24:30average salary of instructors in
24:32computer science department
24:35so
24:36in
24:37similar way you can use other
24:40[Music]
24:42aggregate functions like
24:44if you want to know the total number of
24:46instructors
24:48who teach a course in the semester
24:51so you
24:53first
24:54put the where clause naturally you where
24:56will you find this information you will
24:58find this information in
25:01teachers
25:02teachers is the relation
25:04which tells you which instructor is
25:06teaching what course so that comes in
25:09the from
25:11then you have to specify that
25:13teaching a course in spring 2010
25:15semester so the where clause specifies
25:18that the semester is spring and the year
25:20is 2010.
25:22so this will give you all records
25:25which show
25:26that the some instructor is teaching the
25:30course in spring 2010 semester
25:33now naturally there could be multiple
25:36the same instructor could happen
25:38multiple times because an instructor may
25:41be teaching more than one course
25:43so you make the
25:45instructor id instructor id that you
25:48have here you make that distinct
25:51so that you get only those instructors
25:55every instructor who is ah teaching one
25:59course
26:00or more than one course will feature
26:02only once in this total list
26:05and then you simply count it
26:07use aggregate function count on that so
26:09that will tell you how many instructors
26:11are
26:12teaching some course in spring 2010 mind
26:16you if this is this here is is critical
26:18to use this keyword
26:20distinct because unless you use that
26:23then all that you will eventually find
26:26out is not the number of instructors who
26:29are teaching the course you will find
26:30out the number of courses that are being
26:32offered in spring 2010 because there
26:35could be the same instructor teaching
26:37more than one course
26:44if you just want to count the number of
26:46ah tuples you can do
26:48count on star because what is star star
26:50is ah all the attributes
26:53so from
26:55if you want to find out the number of
26:56courses you have to count star on course
27:03so this is showing you the computation
27:05of ah average salary of instructors in
27:08each department so now what you want to
27:10do
27:11is earlier you try to find out the
27:14average salary
27:16in one department now you want that for
27:18all the departments for each department
27:20i want so
27:22my result now is not a single
27:26row its not a single
27:28value it is a pair where i show the
27:31department and the average salary in
27:34that department so this is what i want
27:36this
27:37and this is what i have
27:40so naturally
27:42the information comes from instructor
27:43that is from
27:45what i want is a department name and the
27:48average salary
27:50and i want to give it a nice name abj
27:52salary so i have done a rename so i get
27:55a avj salary here
27:57but then what i want is i do not want an
28:00average done over this whole set of
28:02fields
28:04i want separate average to be done here
28:07to be done here to be done on this to be
28:10this so these are these are basically
28:12groupings by the
28:14department as you can see that this
28:18particular
28:19relation has been sorted according to
28:21the department name
28:23so when i want to do
28:26apply an aggregate function on certain
28:29subgroups of records
28:32i use this
28:34particular
28:36clause group by
28:38and use a
28:40name of a field so what it does is
28:43if the values in the group by field in
28:45this case department name are identical
28:48those records are put together
28:50and
28:51over those records an average is
28:53completed so the average that is
28:55computed over these records are put in
28:57here
28:58average that is computed in terms of
29:00these records are
29:02put in here
29:03these only one records average that is
29:05computed in terms of that is put in here
29:08so group by is a very
29:10useful feature along with the
29:12aggregation functions and it allows you
29:15to club
29:17information
29:18based on certain attribute and then
29:20compute the
29:23aggregation on
29:25some other field
29:29mind you ah you will have to when you do
29:32group by and
29:34create the result id
29:36result table you have to make sure that
29:38all your resultable attributes are used
29:41in the group by which is not an
29:43aggregate function so here id is not
29:45used so this is not a
29:47ah query that sql would support
29:56you can further
29:58refine your result we are saying that
30:00find names and
30:02average salary of all departments
30:04this much you have already done
30:07now you are qualifying that whose
30:08average salary is greater than forty two
30:10thousand
30:11so of all that we have
30:14ah for example if we look in here
30:17ah for example in this music department
30:20the average salary is less than forty
30:21two thousand so you do not want that in
30:23the result
30:24you want ah only those
30:26where the average salary is ah greater
30:28than forty two thousand and the way to
30:30do that is to have add another clause
30:33called having
30:35we say that the average salary is
30:37greater than forty two thousand so you
30:40are adding another predicate
30:42for actually qualifying the aggregated
30:46value
30:48now
30:50the having clause ah actually applies
30:53after
30:54along with the group by because
30:56naturally the having
30:58relates to the grouping
31:00so
31:01once the grouping has happened groups
31:04have been formed then
31:06the having clause will be evaluated on
31:09that
31:10in contrast
31:12where clause also has a predicate but
31:15the where clause is
31:17applied before forming the groups so
31:20this point this note has to be
31:22understood carefully because ah if you
31:24have a wire clause to choose the records
31:26they will first apply
31:28then out of those records chosen the
31:31grouping will happen and once the
31:33grouping has happened then
31:35the aggregate function will evaluate and
31:38the having clause will get evaluated the
31:40predicate of having clause will get
31:42evaluated
31:45certainly if there are null values ah in
31:47terms of aggregates then ah there is a
31:50question of what will happen
31:51so
31:52the
31:54general strategy is that whenever you
31:57perform aggregation then the null values
32:00are all ignored
32:01so if on that field there is no value
32:05which is not null that is if all values
32:07are null then the result is null
32:09otherwise the result is computed by
32:11ignoring the null values
32:14so these are
32:15what you have of course
32:18if you count
32:19then
32:21if the collection has only null values
32:23the count will return you 0 but all
32:26other aggregates will return you simply
32:28null
32:30so to summarize ah we have we had
32:33started the basic
32:34understanding of the basics query
32:36structure in the last module now we have
32:38completed that with some more additional
32:41ah operations we have understood the set
32:44theoretic operations
32:46and very importantly we have
32:48familiarized with
32:49the treatment of null values and
32:52aggregation functions particularly the
32:55group by and having clauses and how do
32:58null values and aggregation interact in
33:01terms of an sql query

About this transcript

This page contains the full transcript of Introduction to SQL/2 by Data Base Management System - IITKGP, generated from the public captions YouTube serves with the video. The transcript has 4,528 words across 843 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.