YT YouTube2Text

YouTube2Text → Transcripts → Intermediate SQL/1

Intermediate SQL/1 — Transcript

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

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:06so
0:09[Music]
0:16welcome to module 9
0:18of
0:19database management systems
0:22we have discussed about the introductory
0:24level of
0:26sql the structured query language
0:29in this module and the next we will take
0:32up
0:33some more intermediate level features of
0:36sql
0:38so these modules are called intermediate
0:40sql
0:42so in the last module this is what we
0:45have done which was
0:47part of the closing part of the
0:49introductory
0:51sql the nested sub queries and
0:53modifications to the database
0:56ah today we will
0:58in this module learn about
1:00sql expressions for join and views
1:04and we will take a quick look
1:07into understanding the transaction
1:11this is the module outline as it will
1:13span
1:15so we start with the join expressions
1:18in sql
1:21ah join as
1:23we have already introduced
1:26takes two relations and returns a result
1:30as another relation so it is
1:33two different
1:35instances of two schemas
1:37and we try to connect them according to
1:41certain properties
1:43so a join operation is primarily a
1:46cartesian product
1:48which
1:49relates
1:50tuples
1:52in two relations under certain
1:54conditions of match
1:57it also specifies that after the joining
2:00has been done
2:02what are the tuples which will be
2:04present in the output
2:07join so join operation typically uses
2:10sub query
2:12is used in as sub query in the from
2:16clause we will see those
2:18uses later
2:20so
2:21if we look into the different types of
2:23join that
2:24sql
2:26support these are the different
2:27classifications so we have cross join
2:30we have inner join which specifically
2:33could be equi join and even more
2:36specifically natural join
2:38and we will see
2:40there are variety of outer join that are
2:42possible there could be a self join also
2:45where
2:46one relation is joined with itself
2:51cross join
2:52is just a formal join based name for
2:56cartesian product of two rows
2:58so
2:59you could explicitly do a cross join
3:02which you can see here or you can
3:05implicitly also do a cross join but by
3:09ah
3:10specifying two or more relations in the
3:12from clause and taking all the
3:14attributes from there we have seen
3:17these kind of cartesian products earlier
3:19so cross join here is more as
3:21placeholder in the context of the joint
3:23semantics that pure cartesian product is
3:27a cross join
3:29but what would be more interesting is ah
3:32when we take different kinds of inner
3:34and outer joins
3:36so
3:37let us start by
3:38with a simple example to understand the
3:41issues
3:42so there is a relation course
3:44which has four attributes and then this
3:46particular instance it has three tuples
3:49three rows
3:51and there is another relation prereq
3:54which specifies the prerequisite for
3:57every course
3:59so it has two attributes the course id
4:02and the corresponding prerequisite
4:04course id it also has three
4:07ah
4:08rows three tuples here
4:10and if you look at the instances
4:13carefully you will find
4:15that
4:16the three courses that are specified in
4:18the course relation
4:20all do not
4:21are not specified in the prereq relation
4:25ah bio 301 and cs 190 is present
4:29in prereq but cs315 is not present
4:33in
4:34at the same time the prereq has
4:37one
4:38particular tuple
4:41specifying the prerequisite of c s three
4:43four seven which in turn is not present
4:46in the course relation
4:48so the with this observation let us
4:50start
4:51trying to see what different joints mean
4:55so in a join
4:57is
4:58ah
4:59computed
5:00then
5:02in terms of the two relations that we
5:05have
5:06there are
5:08there is an attribute course id which is
5:10common
5:12so
5:13once we have taken the cross product
5:16we will
5:17from the cross product only retain
5:20those
5:21rows where
5:23the course id in relation course
5:27and the course id in relation prac
5:30prerequisite
5:31are same
5:33so
5:34when we do this
5:36this particular record
5:39when it gets
5:40mapped with
5:41this corresponding record it will
5:44generate
5:45the corresponding
5:47output record
5:49similarly cs190
5:52when it is mapped to the cs 190 in the
5:54prereq it will generate the second
5:56record
5:57we have already understood this
6:00the third record in the courses cs315
6:04has no match here in prereq so that will
6:07not feature in the output similarly in
6:09the prereq cs 347 that exist has no
6:13match in courses so that also will not
6:16appear in the output
6:18and also in the
6:20output you find that the course id has
6:23actually featured twice
6:25this is ah the first
6:28column course id comes from course so it
6:31should more formally be called course
6:33dot course id
6:34whereas the second one comes from prereq
6:37so it should that should formally be
6:38called prereq dot course id
6:41now if
6:42in addition to saying that this is an
6:45inner join if i also specify the word
6:48natural i can say natural here
6:51if i say natural
6:53then
6:54this second duplicate
6:57attribute course id will be dropped from
7:00the output that becomes a natural join
7:04inner join as the name suggest finds out
7:08the
7:09inner part of the two relations so if we
7:12look at the two relations as a and b
7:14only these rows records which
7:17are both
7:18have have instance in a as well as b in
7:21terms of equality of this course id
7:24attribute will come in the output
7:27so this is the
7:29ah basic
7:30type of join inner join which is most
7:32commonly used
7:35now we can extend this into
7:38a different kind of join known as outer
7:41join
7:42in the inner join as you have seen that
7:45courses that exist in the
7:47course relation but is not there in the
7:49prereq or the ones that exist in the
7:52prerequisite is not there in the course
7:54ah are not featuring in the final inner
7:57join
7:58output so there is some loss of
8:00information in terms of this
8:03so
8:04while we are doing this
8:06we can
8:07compute and add tuples from one relation
8:11that may not match
8:14with the
8:15with any tuple in the other relation
8:19and
8:19if we want to do that then naturally
8:23for
8:24the other attributes of that tuple in
8:26the target relation we will not know the
8:28values so we will use null values this
8:31is the basic idea of outer join
8:33so let us see what it specifically means
8:37we first talk about left outer join left
8:39in the sense
8:41that
8:42we have
8:43and this is how it is written
8:44ah left outer join
8:47is a
8:48sequence of commands that you give you
8:50are also saying it is natural which
8:52means that the common attribute will not
8:54feature twice in the output
8:56and this is the left relation and this
8:58is the right relation
9:00so left outer join
9:02specifies that in the output
9:05all records of
9:07the left relation in this case the
9:09course relation must feature
9:12so
9:12naturally when we do the join we will
9:14get these two records as we have got in
9:17terms of the inner join
9:19in terms of course three one five the cs
9:23cs315 the third course
9:26there is no instance
9:28in the prereq
9:30we will still have that in the output
9:32but since the prereq value for that the
9:35prerequisite value is not known the
9:37prerequisite id will be set to null here
9:40so left outer join ensure that all
9:43relations of the left relation all the
9:46tuples of the left relation will
9:48necessarily feature in the output and
9:50that is the reason if you see in the
9:52venn diagram the whole of this the whole
9:55of this
9:56set a is shown
9:57whereas this part certainly will not
10:00feature
10:05now similarly we can have a right outer
10:07join where the concept is the same
10:10except that now we ensure that all
10:13records of the right relation in this
10:16case the prereq relation will feature
10:19and therefore
10:20ah cs2347
10:23for which there is no entry in the
10:24course
10:26relation will also come as a record and
10:28since we do not know the title
10:30department name and credits
10:33for these fields we will put them as
10:36null
10:37and this again is a natural one so
10:39course id is featuring only once
10:43so you will understand that since we
10:44have a left version and we have a light
10:46version we can actually have a full
10:49version as well so if we look into the
10:51join relations
10:53in general it takes two relations and
10:55returns a result
10:57and those additional operations are used
11:00in the sub query in from
11:03and there is a set of join conditions so
11:05these are the join conditions that we
11:07are specifying whether
11:09it is natural and we will soon see that
11:12we can actually
11:14not depend only on the attributes that
11:17are common we can actually specify that
11:21which attributes should be used in
11:24ah computing the join so those are the
11:26on condition and the using ah clause we
11:29will just illustrate them soon and
11:32finally there are four types of join
11:35that
11:39can happen that is
11:42the
11:42inner join we have seen the left outer
11:44join right outer join and we will soon
11:46see the full outer join
11:52so full outer join as you must have
11:55guessed will ensure that ah
11:58you get ah
11:59certainly the tuples from the inner join
12:02which is here
12:04you will get the tuple from the left
12:07outer join
12:08that is here that is a tuple which exist
12:11in course and there is no corresponding
12:14matching tuple in the prereq
12:16and you will also get the tuple from the
12:18right outer join that is for tuple which
12:21exist in the
12:23prereq
12:24relation but there is no corresponding
12:26tuple in the
12:27ah course relation and corresponding ah
12:29missing values are all set to null so
12:32these three kinds of ah
12:36outer join are
12:37possible
12:39so you can also ah specify join by
12:42saying that ah explicitly saying what
12:46attribute we want to join on and if you
12:48specify that then you are saying its a
12:51course inner join prereq this part was
12:54same then you are putting an on clause
12:56saying in the on clause you will have to
12:57provide a predicate that is which field
13:01should equate or match with what field
13:04so you are saying course dot course id
13:06is equal to prereq dot course id so this
13:09result incidentally happens to be same
13:12as just doing the inner join but we are
13:14illustrating that on clause can
13:16explicitly use for example between the
13:18two relations we have more than one
13:20common attribute but we may want to
13:23actually do the inner join
13:25based on only one of them or equality on
13:28two of them and so on
13:32so this is this kind of a join where
13:35inner join where you
13:37set two fields to be equal or two or
13:40more fields to be equal is also known as
13:42equi join
13:43and since we have not specified natural
13:46you can again observe that the course id
13:48attribute has occurred twice if it was
13:51said natural then the second course id
13:53attribute would not have come in the
13:55result
13:57this is ah showing the left outer join
14:00in terms of a
14:02on clause and we have seen similar
14:05results and now this can be
14:08seen in terms of the
14:10on clause as well and you can see in the
14:13second course id field ah the
14:17this entry is null because actually you
14:21do not have that in the prerequisite set
14:25and obviously this set will be none this
14:27field will be null
14:33so this is another example showing you
14:36ah the natural right outage joint
14:39ah this is you showing you full
14:43outer joint and we are
14:45showing the use of the using clause you
14:48can see using and put a set of
14:51attributes
14:52and
14:53the meaning is
14:54the join will be performed based on
14:56those attributes so here in this case
14:59again the join will be based on course
15:00id
15:02ok so that was about different kinds of
15:05join that we can do which we going
15:07forward we will see that ah
15:09form a very critical
15:11as a credit critical place in terms of
15:13query formulation
15:15now we take you to a different concept
15:17known as views
15:19now we have seen
15:22that
15:23so far we have been computing certain
15:27query results based on one or more
15:29relations one or more instances
15:32now in some cases ah we may want
15:36the
15:36result to be restrictive in terms of
15:39based on the user or based on the
15:42context in which the result should be
15:44used so we may not want
15:47all fields of a result to be visible to
15:50all the users or to the application so
15:54we may not expose the whole logical
15:56model and in those cases we introduce a
16:00view so here we are
16:03showing one where
16:05from the instructor relation we are only
16:09picking up three fields and we are not
16:11picking up the salary field
16:13now you would ah
16:15think that well this is what we can do
16:18in terms of the normal query and
16:20certainly then what is the point of
16:23using this
16:24now
16:26what we can do is
16:28we can create this not just as a query
16:31but as a view
16:33one we once we create this as a view it
16:36actually this ah
16:39query expression is treated as what is
16:42known as a view expression
16:44and every time you want to use that view
16:48the
16:48actual tuples in that view are computed
16:51but this is not actually a relation that
16:54exist in the database so it is kind of
16:58can be
16:59thought of as a kind of
17:02virtual relation which
17:04exists which can be seen
17:07only when you use that
17:09so
17:10there is a subtle but very strong
17:12difference between actually computing a
17:15result through
17:17a select query
17:19and
17:20defining a view based on a select query
17:24and then making use of the view as if it
17:27were actually a relation that existed
17:30so to do this this is how we go about
17:34it is
17:34the syntax is very similar to the create
17:36table so you do a create view give a
17:39name and then
17:41ah you specify ah as is the connective
17:44and specify the query expression which
17:45is an sql query which will let you
17:48compute the view every time you actually
17:51need it
17:53so this is a view name once a view is
17:55defined the view name can be used as a
17:58virtual relation it can be used exactly
18:01as we use any of the
18:04really existing relation the conceptual
18:07relations that we have created through
18:09create table
18:10so
18:11it is the difference is this is what
18:14needs to be understood very well the
18:16view definition is
18:18not the same as creating a new relation
18:21once you create the new relation the
18:23time you have created it you get the
18:25result and that result is explicitly
18:28available as a set of tuples as a table
18:32rather a view is a definition
18:34which you store in the database as an
18:37expression so every time you make use of
18:40that view at that time
18:43the set of tuples are computed
18:46it is not existing in the database as
18:48stored like the real relations
18:50and based on that computation all the
18:54rest of the query will actually be
18:56executed so let us
18:58take a quick look this is a create view
19:01we have created the view of a of view
19:04called faculty from instructor
19:07relation instructor relation is the real
19:09one the
19:10existing one and faculty is a view
19:12expression being created
19:15and in that what we have done simply we
19:17have taken a done a projection we have
19:20left out the salary field
19:23now we can make use of that
19:26view you can see that we are doing from
19:29faculty so this actually is a view but
19:32this behaves as if this is valid
19:35relation so from faculty we are trying
19:38to find out the name of all those
19:39faculty who belong to the biology
19:42department so what will happen when i
19:44want to execute this query this will
19:46refer to this view
19:48so to execute this query it will have to
19:50first
19:51execute this query
19:53get the temporary virtual instance of
19:56the virtual relation created and based
19:58on that this query will be computed and
20:00the results will be given accordingly so
20:02that is the basic purpose of the view
20:04that the whole thing the whole view
20:06expression remains as an abstraction in
20:09the database and computed whenever it is
20:11used so this is showing you another view
20:14which
20:16shows certain computed information for
20:18example we are creating a view for
20:20departmental total salary which will
20:23show as two fields department name and
20:25total salary which has been created by
20:28aggregation so any time we ah make use
20:32of this this view in a from clause we
20:35will get we will feel as if
20:38such a relation really exist where the
20:40department name
20:41and the total salary of the instructors
20:43in that department are stored but it
20:46really does not exist it is computed
20:48whenever it is needed whenever it is
20:50used
20:53you can actually
20:54use views to create other views for
20:56example this is one view which is
20:59the view of physics fault 2009 which are
21:02all courses that are
21:04offered
21:05in physics from the physics department
21:08in the semester fall of year 2009 and
21:11using that
21:12we can
21:14create another view see here again we
21:17are in the from clause we are using this
21:19view so creating this using this view we
21:22are creating yet another view which show
21:25the courses that run in the
21:27watts and building so views can be used
21:30as i have already said as any other
21:33actual relation but they do not really
21:35exist
21:36so if you expand out if you just
21:40put
21:41the physics fall 2009
21:44expression within the
21:47within the view
21:50definition of physics fault 2009 watson
21:53this is the your earlier view relation
21:56so this is known as view expansion so
21:58this is actually the query that you are
22:00executing
22:05so we as we have said views can be
22:07defined in directly ah from one ah
22:10relation so these are called ah direct
22:14dependence or they could be defined in
22:17terms of a chain of relations v one in
22:19terms of v two v two in terms of v three
22:21and so on
22:22and
22:23a view relation can be recursive also
22:26that a view could be in terms of itself
22:28and that has a lot of
22:31value lot of power
22:32ah view expansion is a process that sql
22:35uses to evaluate a view so
22:38i would ah request you to study this and
22:41understand that this process works this
22:42is pretty much like
22:44ah pseudo code c program
22:48now moving to recursive views the views
22:50where the same relation can be
22:53used in the view
22:55to define another view
22:58we need like every other
23:00recursive structure we need first a non
23:04recursive statement which is called the
23:06seed statement
23:07we need a recursive statement which can
23:09recur
23:10we need a connection operator which can
23:12connect the
23:15non recursive and the recursive results
23:17together put them together
23:19the only connective that is valid is
23:21union all that is multiset union
23:25and we also need some kind of a terminal
23:28condition to guarantee that the
23:30recursion really
23:32terminates it does not go on forever so
23:36let us take an example so
23:38this is in context of a relation flights
23:41where the four fields are as specified
23:44and there is an instance shown which
23:45show that different source destination
23:48of different carriers ah carrying people
23:51from one source to the other destination
23:53and what we want to find is all
23:56destinations that can be reached from
23:58paris
23:59now you can see that
24:00from paris if i can reach detroit
24:04and from detroit i can reach san jose
24:06then i can actually reach
24:09san jose from paris so that is the basic
24:12reachability so that will necessarily if
24:15i want to compute that then i will be
24:18able to compute this ah very easily by
24:22doing natural join of
24:26flights with flights ah provided i take
24:31say
24:32source
24:36let us compute it like this flights
24:41f 1
24:42join
24:44flights f two
24:47and i will have f one dot
24:50destination
24:51equal to f two dot source
24:55so the idea is if something goes from
24:57paris to detroit
24:59that is in f1
25:01and if some flight goes from detroit to
25:03san jose that is in f2
25:05then the destination in f1 and the
25:07source in f2 have to be equated so if we
25:11do this kind of a self equi joint then
25:13we will be able to find out ah
25:17all flights that go from paris to san
25:20jose or all places that you can reach
25:22from paris
25:24in one hop
25:26naturally once you
25:28reach
25:29ah once you do that then
25:32you may be able to go to another
25:35destination in two hops
25:37and once you do that then you may be
25:39able to reach another yet another
25:40destination in three hops and so on so
25:42we do not really know how many hops
25:45maximum would be required to compute
25:47this reachability information so that is
25:50the reason we need to make use of the
25:52recursion
25:54and so this is how we express it
25:57so um if you if you look into this we
26:00are
26:00specifying that is a recursive view it
26:02will happen now with itself this is the
26:05name and this is what we want to compute
26:08ah source destination and we take
26:10another dummy attribute kind of ah which
26:13specify the depth of recursion so the
26:17present instance of the
26:19relation is at depth zero so which
26:21defines your non recursive seed part
26:26so say select
26:27so you have renamed is at flights as
26:29route you have specified that the it has
26:32to start from
26:33paris
26:34and
26:35you can find out the source destination
26:38pair at depth zero
26:40then you specify the recursive part
26:43that is the second hop has to be defined
26:46so he is saying that if you had the
26:48reachability
26:50then call lets call it ah
26:52in one this reachability may be in one
26:55hop that is at depth zero maybe in two
26:57hops that is a depth one maybe at three
26:59hops that is a depth two
27:01and you take another instance of flight
27:03as out one and what you need is the
27:06destination in the first in one
27:09has to be same as the
27:11source in the other so that they get
27:13connected
27:15and then you output the source
27:17from the first one destination from the
27:20second one and naturally the depth has
27:22got incremented because you have done
27:24added one more
27:26hop
27:27and
27:28so this is ah the
27:30and you add another condition saying
27:32that
27:33in one dot depth should be less than
27:35equal to hundred this is as i mentioned
27:37is a terminal condition which
27:39make sure that you do not get into
27:41infinite recursion so
27:43this view recursive view cannot be used
27:45to compute
27:47any reachability which is which has
27:50more more than 101 hops
27:53so that is uh to be noted and finally we
27:56need to connect these two results which
27:58is the initial start seed and the
28:02recursive one so this is the connection
28:04operator so this is ah basically the
28:07idea of the recursive view ah those of
28:10you who are
28:11more familiar with discrete structure
28:14would have known or i mean relations in
28:17some more depth you would know that we
28:18can define a transitive closure of a
28:21binary relation so this recursive view
28:23is necessarily computing the transitive
28:25closure from the fright relation so this
28:28is the instance
28:29of the flights and on the final
28:31computation this is what you get this
28:34gives you all the destinations that can
28:36eventually be reached
28:37from the from the source paris
28:41so the recursive is very very powerful
28:44in the sense that without recursion a
28:49a non-recursive version can only find
28:51flights up to a certain number of
28:54hops and whatever
28:57query you write it is always possible to
28:59write out a database
29:01instance which will have more hops and
29:03your query will necessarily fail
29:05so ah we make use of the
29:09recursion here to make sure that
29:12you can actually
29:14extend this to whatever depth you want
29:17and to compute this we ah keep on
29:21computing till no changes are possible
29:24and
29:25in that sense this recursive views are
29:27said to be monotonic in that every time
29:30you compute your result necessarily
29:32becomes larger and that is the reason
29:34you you for for the purpose of being
29:37being monotonic
29:38you are actually making use of the union
29:42all so that makes it all inclusive
29:47so
29:48now if i if we go and this is the
29:51instance and this you can here i have
29:53shown that how the iteration actually
29:56happens in the iteration 0 in the
29:57flights itself you had 3 destinations
30:00then you add 2 more in iteration 1 in
30:02iteration 2 you do not add anything else
30:04so your result henceforth will not
30:06change so you have reached a fixed point
30:08and your computations are over
30:11you can also update a view you can
30:13insert a
30:15record directly into a view but since
30:17view only is a partial information on
30:19the relation when you insert into a view
30:21since view is virtual there will have to
30:23be an insertion in the real relation and
30:26in the real relation you may not know
30:27certain fields so if you are doing this
30:29insertion into faculty which is a view
30:32of instructor then the salary field is
30:34not known so in the actual instructor a
30:36null will have to get inserted in the ah
30:39salary field so the salary field needs
30:42to be nullable kind of field so
30:45updates on views have certain
30:47restrictions
30:48so there are some more instances that i
30:50have given which you can
30:52study and
30:53try to understand that what are the
30:55difficulties of
30:57updating on the view so it can be done
31:00but it has to be done in a restrictive
31:02sense so these are the different
31:04conditions that has to happen for views
31:07to be updated so please ah go through
31:09these slides to understand what
31:12are there in terms of the views
31:15ah finally view is a virtual relation
31:17but it can be materialized also that is
31:20materializing is basically computing a
31:23physical relation ah at the at the
31:25instance of the view but naturally if
31:27you materialize then there is a certain
31:29point of time where you have
31:30materialized where you have ah made it
31:33into a physical relation and hence if
31:35your ah original source data in the view
31:38changes in future the materialized view
31:41also need to be updated otherwise your
31:43data will get bad
31:46finally
31:47in in this module
31:48we
31:49mentioned that there is something called
31:51transactions which we will take up at a
31:54at a later stage in much depth this is
31:56just to
31:57get you familiar with the term a
31:59transaction is a is a unit of work which
32:01is usually atomic which is either fully
32:04executed or if it fails it will be
32:07rolled back
32:08as if it never occurred
32:10and
32:11this is required for isolation in
32:13concurrent transactions so we will talk
32:16about this lot more when we take up
32:18concurrency and related issues so con
32:21transactions implicitly begin and they
32:24end by either committing the work that
32:26they have successfully finished or
32:28rolling back that this cannot be done
32:32so
32:32there are some features
32:35in the sql
32:37for
32:37doing transactions and
32:40but usually in transactions commit by
32:43default and they only raise exceptions
32:46when the rollback is happening and we
32:48will see more of that later
32:50so to summarize in this module we have
32:53learnt about
32:55two important sql features in terms of
32:58join and views and we just introduce the
33:00basic notion of committing transactions

About this transcript

This page contains the full transcript of Intermediate SQL/1 by Data Base Management System - IITKGP, generated from the public captions YouTube serves with the video. The transcript has 4,659 words across 832 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.