YT YouTube2Text

YouTube2Text → Transcripts → Introduction to SQL/3

Introduction to SQL/3 — Transcript

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

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00[Music]
0:16welcome to module 8 of database
0:19management systems
0:20we have been discussing
0:22basic sql queries and this is the third
0:26and
0:27closing part of that introductory
0:29discussion that we started in the sixth
0:31module
0:33so just to quickly recap this is ah
0:36these are the things that we did in the
0:38last module completing the understanding
0:41of basic operations the null values and
0:43aggregate functions
0:45in the current module we want to
0:47understand a feature which is very very
0:50important in sql query forming is called
0:53the
0:54nested query or more
0:56formally nested sub query in sql
1:00and
1:00we would like to understand the process
1:02of data modification
1:05and those are the two things that are
1:07outlined here so lets start with nested
1:10sub queries
1:15a sub query is necessarily
1:18a select from where expression
1:21that is nested within another query
1:24this is these are the key things
1:28where expression
1:30so its nothing new
1:32over what we have already learnt
1:35but
1:36it is a part of another query it is
1:38nested within another query and that is
1:40the reason is called a sub query
1:43so it by itself is not the result
1:46this will be used
1:47this is a select from where expression
1:50that will be used in a nested form in
1:52another
1:53some other query
1:55to actually generate the result
1:58so thats a nested sub query
2:04so
2:05the nesting can be done in
2:09one or more of the three clauses that
2:12a
2:13select from where
2:15sql query has
2:18an attribute can be replaced
2:20a relation ah can be
2:23any valid sub query
2:24or
2:25a sub query could form the part of a
2:28predicate in the where clause
2:30all of these are possible so we will
2:32discuss them one by one
2:35so first we will start by discussing how
2:38sub queries
2:40work in the where clause
2:44the common use for you having sub
2:47queries in the where clause
2:49is to perform different kind of tests
2:52for membership comparison
2:55cardinality and so on
3:00so let us look at this you have already
3:02seen this query before find courses
3:04offered in fall 2009
3:07and in fall in spring 2010 earlier we
3:11have shown ah different ways of coding
3:13this now we are showing yet another way
3:15to be able to code this in sql
3:18so first
3:20from the from the beginning certainly we
3:22need the courses so the select clause is
3:25trivial
3:26it has to be the distinct course ids
3:28certainly the information will come from
3:30section which has a
3:33information about offering of courses as
3:35we have also seen already
3:38ah
3:40so those two are
3:42no brainer
3:44now let us see
3:45how do i find courses that are offered
3:47in fall 2009 and in spring 2010. so
3:51again the first part the courses offered
3:54in fall 2009 is coded in this part
3:58in part of the where where clause
4:00predicate where is the semester has to
4:01be fault and year is two thousand nine
4:04so this part is also done
4:07now i need to ensure that ah whatever
4:10i mean if i assume that ah
4:14after this this part were not there
4:17then this will only give me courses
4:20which are offered in fall 2009
4:24but we want the courses that are in fall
4:252009
4:27and in
4:28spring 2010
4:30so we do something interesting what i do
4:32is we write a separate query here
4:37which is courses that are offered in
4:40spring 2010 select course id just in the
4:43same way select course id section
4:45semester ah year
4:47so this particular query will give me
4:51the
4:52courses offered in spring two thousand
4:54nine so what do we have in one part
4:58i have so if you look at this part
5:01this courses ah that happened in fall
5:03two thousand nine if you look at this
5:05part
5:07courses that happened
5:08in spring 2010
5:11good
5:12now what i want i need that
5:15the it i am interested in the courses
5:18that happen in both
5:20so
5:21for a course that exists here
5:24i want to specify that that course id
5:28that course id
5:30must be present here
5:34so what i am checking for i am checking
5:36for it
5:39set membership
5:40this is a set right
5:43so i am trying to check whether that
5:45course id which is
5:47being selected in the first part
5:49exist in in is a is another keyword in
5:54this particular
5:56this particular relation
5:57that is specified by the second
6:00part of this query which is courses
6:03offered in spring 2010
6:05if it is
6:07if the core side is present then that
6:08course must have been offered in both
6:10the semesters
6:12if it is not present then it is offered
6:14only in fall 2009 and not in spring 2010
6:18and certainly the courses that were not
6:20offered in fall 2009 and only offered in
6:23spring 2010 will feature here
6:25but they do not exist here so they will
6:27never come up for test
6:29so as a result what i get finally is the
6:32effect of computing courses that are
6:35offered in fall 2009 and in spring 2010
6:40this part of the query which i used
6:43as a part of the where clause
6:45is my nested sub query and in this case
6:48as you have seen it is used for
6:50set membership
6:52and this is a basic idea of ah
6:56using nested sub queries that is a
6:58nested sub query will always give you a
7:00relation
7:01so you try to put that relation in the
7:03right context of the where clause from
7:06clause or select clause and then make
7:08use of it in building up your logic
7:11so let us run through some examples
7:14ah this is what you are saying is is ah
7:17earlier one was the courses offered in
7:19both here we are trying to do kind of
7:21the difference saying the course is
7:23offered in fall two thousand nine but
7:25not in spring two thousand ten certainly
7:27we easily get that by changing the
7:30membership to negation of the membership
7:33earlier it was in now you do not in you
7:35will simply get that it is ah up to you
7:38to take some examples and convince
7:40yourself that this kind of a nesting
7:42will work
7:44ah we find the set of the find the total
7:47number of
7:48distinct students we have taken the
7:49course
7:51section taught by the instructor id some
7:54id is given
7:55so again we form a nested query
7:58this is a nested query which
8:01ah tells me the courses taught by
8:04this particular teacher one zero one
8:05zero one
8:07and then we check
8:11set membership in terms of
8:13this course id section id that is fields
8:16of the
8:16takes ah relation
8:19to see that whether that particular
8:23tuple can exist
8:25in the course offered by the specific
8:28teacher
8:29if it does
8:30then take out
8:32that id
8:33which is which will turn out to be
8:36the student id in this case
8:39because that is the text relation has
8:41the student id take out the student id
8:43and count it as distinct so this can
8:45simply give you the answer to this query
8:52obviously we we agree that this can be
8:55written in a simpler form also but we
8:58are including it here just for the
9:00sake of illustrating the feature
9:04ah
9:06there is another
9:08clause
9:10called the sum clause ah look at this
9:13find names of instructor salary is
9:14greater than
9:15that of sum which means at least one
9:18instructor in biology department and we
9:21have already seen this coding before
9:24now we can do this in terms of the
9:26nested query by using
9:29again
9:31this is
9:32certainly the salary of instructors in
9:35biology department
9:37and we are doing greater than sum
9:39that means that the salary here
9:43being checked
9:45must find at least one record here
9:48so that it is greater than that salary
9:50value
9:51so its greater than sum
9:53is a nice way to find existential
9:56records
9:57using the nested sub query
10:01the logic of some clause ah i have
10:04detailed out here so we will not go
10:06through each one of them
10:08ah in this discussion i leave it on to
10:10you to study and convince yourself that
10:12you understand
10:13the semantics of some
10:16so similarly we have an all clause
10:18which say that ah if we want to say the
10:21find the names of all instructors whose
10:23salary is greater than the salary of all
10:25instructors in the biology department in
10:27place of sum we can write
10:29we will write all and it will check
10:32every salary will check with
10:34the whole
10:36set of salaries in this sub query and
10:38only if that is greater
10:41then
10:42that particular record that particular
10:44name will be included in the result
10:46otherwise it will be excluded from the
10:48result
10:52similar to sum there is a
10:55basic semantics of all which is also
10:57worked out here and i leave that to your
11:02study at home
11:06you can test for empty relations by
11:08using the exists
11:10construct
11:12so if you say exists r
11:14then that is a predicate
11:16which
11:17mean that r is not empty if r is empty
11:22then
11:23exist is false
11:26and not exist is the
11:29negation of exist
11:30so it can be used to
11:34specify
11:36the query like find all courses taught
11:38both in
11:39fall 2009 and spring 2010
11:43so all that you have to do earlier you
11:46did it by set membership
11:48so
11:49now you are trying to do this by this
11:51exist
11:52so you are saying that this is again the
11:54same query which gives you the courses
11:58that
11:58are in spring
12:002010
12:01and
12:02also in
12:04this
12:06fall fall 2009
12:09and
12:11you check whether
12:13this
12:15relation whether this particular
12:18nested query
12:20is an empty one or not if it is an empty
12:22one then exist will fail and the whole
12:25clause will fail it is not an empty one
12:28then you have found such an entry it was
12:30offered and therefore it will get
12:32included
12:33so these are just different ways of
12:35expressing similar things but what you
12:38should note is
12:40the nested sub query is a very
12:42convenient way to frame up the logic in
12:44multiple different ways
12:46that you would like to do
12:48so these are the different names given
12:50the correlation name and the correlated
12:52sub query ah incidentally
12:55the nested query is often referred to as
12:57the inner query
12:59and
13:00the query in which the nesting has
13:02happened is known as the outer query
13:05ah here is another example which
13:06illustrate the use of not exist
13:10so which i leave it for your own study
13:17we can check for uniqueness that is test
13:20for absence of duplicate tuples
13:23by using the unique keyword so we can
13:27you can see here that here is a nested
13:30query and
13:32using unique to find out all courses
13:34that were offered at most once in 2009
13:38so
13:39if it a course was offered more than
13:41once then naturally multiple records
13:43will feature
13:45and the result the unique will fail
13:48unique will be true only if there is
13:50only one entry which shows that it is
13:53offered at most once in that semester
14:00now we move on ah so we have been
14:01discussing about ah
14:04sub queries in the where clause now we
14:06move on to sub queries in the from
14:08clause
14:09so
14:11as we have already seen a nested sub
14:14query is a relation so it can
14:17naturally be used in the place of any
14:20relation that we have in the from clause
14:24so
14:25we are trying to find out
14:27average
14:29instructor salaries of those departments
14:31where the average salary is greater than
14:33forty two thousand
14:36so
14:36look at
14:41this is a nested sub query so where what
14:45is been found here this will compute
14:48the average salary department wise
14:51average salary which we have already
14:53seen group by department name and then
14:55you do average on the salary and you
14:57give it a give me that field a new name
15:00so which means that this is equivalent
15:03to having
15:04a
15:06ah relation which has two attributes
15:16depth name
15:17and avj salary
15:20so from that you are now trying to
15:23do the
15:24selection
15:25and what is the condition that the
15:27average salary has to be written so you
15:29already have that as a part of the field
15:31the average salary
15:32so or you just need to put that in the
15:34where clause
15:36and you have
15:37only those
15:39coming out of this particular relation
15:41where average salary is greater than 42
15:43000
15:44to be selected in this select query so
15:46these will
15:48feature in the output of this selection
15:52so that is how you can very easily use
15:54ah
15:57a nested sub query in the from clause
16:00and
16:01for this we did earlier we solve this
16:03problem using the having clause but here
16:06we will not
16:07need we did not need the having clause
16:09to do this
16:16there is ah this is another way
16:19so
16:20here what we have done is
16:22the same
16:23if you if you look into the
16:27nested sub query
16:29this is
16:32actually the same
16:34all that we have done we have given it a
16:36new name
16:38by
16:39the renaming feature and then this as if
16:42becomes a relation and on that the
16:44computation is done
16:46rest of it is similar
16:50there is a width clause
16:52that provides a way of
16:56computing a temporary relation and that
16:58can be subsequently used so let us look
17:00at example
17:02so
17:03we are trying to find
17:04all departments with maximum budget
17:08so this
17:10is my basic query
17:13we want to find department name
17:16department dot name that is the
17:18department's name
17:20from
17:21the department table
17:25and
17:27the
17:30budget must be same as a
17:32maximum budget
17:35so for that
17:37i need to know
17:39the maximum budget
17:42that
17:43exist across the department
17:46so look at what has been done here
17:49we have a nested query
17:52which aggregates
17:56the maximum
17:57budget from the departments so this
18:00gives you the value of the
18:03maximum budget
18:05we make that
18:07into a temporary table max budget
18:12with an attribute value so this is
18:14renaming
18:16so you can see the renaming is being
18:17used in in very interesting ways so this
18:20is my nested query that gives me a t
18:22relation
18:23and this is my
18:25definition of the relation so max budget
18:28now is a temporary relation a relation
18:31that i use subsequently in my from
18:33clause
18:34and width allows me to do that
18:37this relation will not be available
18:39otherwise after this query this relation
18:41will not exist this is just a temporary
18:43one computed for this query
18:45so this gives me the budget value this
18:47gives me the department and department
18:49specific budget
18:51and
18:53this condition tells me that
18:56i can choose all the departments which
18:58has the maximum budget
19:01very nice way of
19:02using this
19:04so
19:05with clause ah can be used in ah even
19:08more involved way again this is an
19:10example which is
19:12ah more complex use and i leave it to
19:14you to
19:15practice
19:16study and understand
19:20we move on to sub queries in the select
19:23clause finally
19:27a scalar sub query is one where
19:30there is a single value is expected
19:32so
19:33we can very easily use that
19:36in the select so
19:38what if you look at
19:41this part which is the sub query
19:43so you are saying list all departments
19:45along with number of instructors each
19:47department has
19:49so
19:54this condition
19:56tells that
19:58the
19:59from the instructor we are taking out
20:02those
20:03that department name where
20:07the instructor works
20:10so you count them
20:12and then you form that in as a
20:15new attribute mind you in while we were
20:18using this in the from clause
20:21we were treating that as a relation
20:23because nested query will give a
20:24relation
20:26but here in the select clause
20:28the entities are attributes
20:31so
20:32this as
20:35is renaming of attribute which mean but
20:37this is a relation
20:39that is why this notion of scalar sub
20:41query is required
20:43that is
20:44the though this is a relation
20:47what does the relation compute it
20:48computes a single value
20:51so that
20:52value
20:53we are putting as
20:55an
20:56attribute name
20:58num instructors
21:00so we have department name and the
21:03number of instructor there in
21:07for each and every department that we
21:10actually have from the department list
21:13so it is a very
21:15interesting way of using
21:17this ah nested sub query in terms of the
21:22select loss
21:26naturally since this in the select loss
21:28i cannot have
21:30ah
21:31i mean every entry in the select clause
21:34has to be an attribute
21:36pure relations are not possible
21:38so
21:39if if
21:40the sub query returns more than one
21:42tuple which cannot be conceived as a
21:45as a
21:47as one or more attributes
21:49then it will be a runtime error that
21:52will not be allowed
21:54because we do not know how to handle
21:56multiple
21:57tuples in terms of a select clause
22:01ok
22:03ah next we move on to ah discussing the
22:07modifications
22:09to the database how do we modify the
22:12database
22:13so we will look into some of the ways
22:16of
22:18changing the records or removing records
22:21from that earlier we saw a delete of
22:24record which removed all records from a
22:26relation
22:27but now we will see selective deletion
22:30insertion and update of values
22:34now deleting all uh instructors are easy
22:36delete from instructor all records are
22:39released and this becomes an empty table
22:41but
22:42suppose we want to delete all
22:44instructors from the finance department
22:46then
22:48like we do in the select from where
22:51we again
22:52use the where clause as a predicate and
22:55say that delete from instructor but you
22:57do the deletion provided this condition
22:59is satisfied that is department name is
23:02same as finance
23:04so it is very similar to the select from
23:06where but the effect is unlike select
23:08from where
23:10where no tables change in the database
23:13here the table is actually changing
23:16because these instructor records are
23:18deleted whose department name was
23:20financed
23:22the third example shows delete all
23:23tuples in the instructed relation for
23:26those instructor associated with the
23:28department
23:29located in the watson building
23:32so a dwetson building may have multiple
23:34departments so all ah
23:37instructors
23:39who worked on those departments which
23:41are located in the watson building that
23:43you remove
23:44so you do
23:46this is again you are using nested query
23:49now you know how to use an estate query
23:51so
23:53you use nested query which will give you
23:55the names of departments
23:58which it gives you a relation with the
24:00single attribute with names of
24:02departments housed in the watson
24:04building
24:05then you use
24:07the set membership to check whether a
24:09particular department
24:11belongs to that set
24:12if it does then it is in watson building
24:14otherwise it is not in watson building
24:17if it does belong to the watson building
24:19then this where clause becomes true and
24:21the corresponding instructor record is
24:23deleted
24:24and that is how this whole
24:27ah different kinds of selective deletion
24:29can happen
24:32delete all the instructors so salary is
24:34less than the average salary of
24:36instructor
24:37again this is
24:38so you compute
24:40the selection
24:44sub query which computes the average
24:46salary
24:47and check if the salary is less than
24:50the average salary and delete that
24:53sounds ah straight forward but just just
24:56wait a while just just wait just wait i
24:58mean did we do it do a right thing
25:02an average salary is
25:04computed
25:05by
25:06taking
25:08the sum of all salaries in the relation
25:11and then dividing it by the number of
25:13relations this has to be the average
25:14salary
25:16certainly if i remove a record
25:19then the average itself will change
25:24so if i write the query in this manner
25:29then what i am saying
25:31on the face of it looks correct but then
25:34actually can it be correct because the
25:36moment a condition is satisfied
25:38and that record is deleted
25:41this average value itself has changed
25:45so that is not so that will depend
25:47then the result will depend on the order
25:49in which the deletion is happening but
25:52that is not what was meant what is meant
25:53is
25:54take all the records for the present
25:56find out the average find out all
26:00records which have a salary less than
26:02that average and remove them
26:04so this
26:06initially you know easy trivial looking
26:09solution is not actually correct
26:12so
26:13you will have to do the solution in two
26:16stages first compute the average salary
26:19find all tuples to delete next delete
26:22all tuples found above
26:24without recomputing the average
26:27in the present solution the average is
26:29recomputed which is the wrong thing
26:33so again i will leave that for you to
26:36solve
26:37we move on ah to looking at
26:39modifications in terms of insertion
26:42so
26:43we had seen ah this earlier we can add a
26:45new tuple by insert into
26:48ah then the relation name then you say
26:50values and the tuple of values
26:52we can
26:54specify the
26:56if we
26:57if we do not remember the order of
27:00attributes in the relation
27:03then we can also specify
27:05the order in which we are speci
27:07actually giving the information so you
27:09are saying insert into course and what
27:11we have done here is we have actually
27:13specified the order in which the
27:15attributes occur and that order and the
27:17order of values must be the same
27:20in the first case
27:22this order of values is
27:24decided
27:25by the order of the attributes that
27:28exist
27:29in terms of the period table
27:34ah add a new tuple to student with total
27:37credits set to null
27:39that is i do not know if you are adding
27:41a student initially it does not have a
27:42credit right
27:43the credit is a nullable field the
27:45credit will be earned after the student
27:47has gone through the courses and all
27:49that
27:50so
27:51if i do not know the value of a field
27:52then i can write n u l l null which is a
27:55special value
27:56designating unknown for at the time of
27:59insertion
28:01add all instructors to the student
28:03relation with total credit set to 0.
28:06so
28:07i can also combine insert with select
28:12so we are taking the first part this
28:15part
28:16is selection
28:18which generates a whole lot of records
28:20having
28:21id name department name and the total
28:24credit set to 0
28:25from the instructor
28:27and
28:28ah insert them into the students
28:33so these will get instruct in inserted
28:36into the students
28:39select fire statement is evaluated fully
28:42so this first select form will be done
28:45before any of its results are inserted
28:47in the relation so that is the basic
28:49condition that sql
28:52guarantees
28:54because if that were not the case then
28:57such situations will become circular and
29:00will cause problem
29:05updates can be done
29:06based on particular values so you can
29:09update a table based
29:11and what it means that it you could
29:14update the values of specific fields
29:17so here in the in the first case we are
29:20giving trying to give a three percent
29:23salary raise
29:25for salaries which are more than hundred
29:27thousand and
29:29some five percent raise for salaries
29:30which are less than equal to hundred
29:32thousand
29:33and mind you
29:35this order
29:37in which you do the update is important
29:39because if you do the
29:40later update first then someone who was
29:44what qualified in the later part was
29:45less than hundred thousand
29:47with the increase will become more than
29:49hundred thousand and will also qualify
29:51for the second one so that will become
29:52wrong so update often
29:55is dependent on the order and therefore
29:58you have yet another
30:00ah
30:00[Music]
30:02feature to take care of this when you
30:04have a specific order to do things
30:07it is called the case
30:09so you say
30:10when salary case is a new keyword
30:13when is a keyword when salary is less
30:16than equal to hundred thousand then this
30:18is how you hike otherwise this is how
30:20you hike so
30:21it can its looks more like the if
30:24statement of c c plus plus
30:29you can do updates with
30:31scalar sub queries we have seen scalar
30:34sub queries ah already so you can use a
30:37scalar sub query again ah i would not go
30:41through the details ah please study and
30:44you will be able to understand
30:48so these are different examples
30:51so ah to summarize ah we have introduced
30:54a very powerful feature
30:57ah in sql query known as a nested sub
31:00query where we can write a
31:02select from where expression
31:05as a part of the where
31:08predicate
31:09or as a relation in the from clause
31:12or
31:13as
31:14one or more collection of attributes in
31:16the
31:17select clause and it can be used in
31:20several other places also we have seen
31:23the ways to perform data modification in
31:27terms of deleting inserting and updating
31:29records
31:30and we have also seen how nested sub
31:33query often may be very useful not only
31:36in terms of performing a query but also
31:39in terms of performing certain data
31:42modifications

About this transcript

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