YT YouTube2Text

YouTube2Text → Transcripts → Introduction to SQL/1

Introduction to SQL/1 — Transcript

by Data Base Management System - IITKGP · 3,787 words · 744 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00[Music]
0:15welcome to
0:17module six of
0:20database management systems
0:22this is the starting of ah week two
0:26in week one
0:28ah we have done five modules
0:32ah after the
0:33overview of the course we have primarily
0:35introduced the basic notions of
0:38dbms
0:39and we have discussed about the
0:42relational model the fundamentals of it
0:46in this background in
0:48this week
0:50we are
0:52primarily focusing on the
0:55query language
0:57the structured query language sql
1:00so all the five modules will relate to
1:03discussions on query language
1:06and
1:08this module 6 7 and eight the three
1:11modules will
1:13introduce sql at a first level
1:17and
1:18the last two modules
1:22eight and nine
1:23i am sorry nine and ten
1:25will discuss about intermediate that is
1:28somewhat advanced level of features in
1:31the sql
1:33the objective of the current module is
1:35to understand the relational query
1:37language
1:38and particularly the data definition and
1:42the basic query structure
1:44that
1:45will hold for all sql queries
1:49ah particularly this
1:53the modules this week
1:55would be important for writing any kind
1:58of database applications squaring the
2:01database to find information from the
2:04existing data and to manipulate it so
2:07please
2:09put a lot of focus in the whole material
2:12of this week and practice them well to
2:15understand the basic issues of database
2:18systems in a
2:20depth in depth in a well oriented manner
2:24ah in this module we will first talk
2:27about the history and then we will
2:30see how to define data and start
2:33manipulating them
2:36sql was
2:38originally called
2:40ibm sequence language sql language and
2:43was a part of system r
2:46it was subsequently renamed as
2:48structured query language
2:50and like any other good
2:54programming language that we have
2:57sql also gets standardized by ansi and
3:00iso and there have been several
3:03standards of sql
3:05that has come up
3:07with sql 92 being the most
3:10popular one
3:12and
3:13the commercial
3:15system most of them
3:17try to provide support for sql 92
3:20features
3:21but they do vary between themselves so
3:25it is possible that the examples that we
3:27show here
3:29may or may not
3:31all of them execute
3:33in the system that you are using so you
3:35will have to look at what standard
3:38your system is actually following
3:41ok so the first what we will talk about
3:44is the ddl data definition language as
3:47we had discussed earlier this is
3:51the features to
3:53create
3:54the schema the tables in a database
3:57management system
3:59so it allows for
4:00the creation or definition of the schema
4:03for each relation that we have in the
4:05database
4:06it specifies the domain of values
4:09associated with each attribute of the
4:12schema
4:13and it also defines a variety of
4:15integrity constraints
4:17later in the course we will see that it
4:20also has to specify other related
4:23information like indexing security
4:25authorization
4:26physical storage and so on
4:31so first the domain of possible values
4:34we have already specified that
4:37every domain in sql is more of an atomic
4:41nature
4:42so they are more like the primitive or
4:44built in data types of languages like c
4:47c plus plus java
4:49so the common
4:51domain types are character ah which are
4:54basically strings of character having a
4:56certain length
4:58then you can have
5:00variable character string which means
5:02that the length here specifies that
5:06the maximum length that the string can
5:08take but a string could be shorter than
5:10that
5:11integer obviously ah then small integer
5:14which in a system may give a smaller
5:17range of integer values
5:19then there is a numeric type which is
5:21often very important which says what is
5:25the
5:27precision of the numbers that are to be
5:30written in this format stored in this
5:32format so d basically gives that
5:35precision value and p gives a size ah
5:38then you can have a real and double
5:40precision numbers you have can have
5:42floating point numbers and so on and
5:45there are some more ah data types which
5:47will discuss later in the course
5:51given all these
5:53domain types so what we will try to do
5:55is
5:57here is a schema for the university
5:59database which has
6:01multiple different
6:03relations
6:04designed in that showing the attributes
6:07and marking out what are the keys and
6:09what are the foreign keys so we would
6:12take
6:13examples of some of these and try to
6:16code them in the sql
6:20now to create a table this is how you go
6:23about
6:25the
6:26sql keyword create table is a basic
6:29command
6:30so with the create table you have to
6:32specify a name
6:34here
6:35the name that
6:37is given is in terms of this
6:40name r which is the name of the relation
6:43and then you provide a
6:45series of
6:47attributes separating by comma
6:50a i's are different attributes and for
6:52every attribute there is a corresponding
6:56type domain type specified so it says
6:59that a 1 is of domain type d one a two
7:01is of domain type d two and so on
7:04and all of these attribute descriptions
7:06are then followed by
7:08a series of integrity constraints it is
7:11possible that a create table may not
7:14provide any constraint
7:16but often you will have a number of
7:18constraints to work with
7:21so
7:24here is one example
7:27so
7:28in this
7:29we are trying to
7:32code the creation of this instructor
7:35table
7:36as you can see it has
7:38four different fields
7:40id
7:41name department name and salary and for
7:45each one we have specified the domain
7:47type so id is care5 this means that the
7:50ident id of this table instructor
7:54will be strings of length
7:56five whereas the name or the department
7:59name
8:00are strings
8:02but
8:03they have a maximum length 20 but they
8:05could have been of variable length
8:07whereas salary is a is of numeric type
8:10having specification eight two so it can
8:13have two decimal places and be of size
8:15eight maximum
8:17so this is the basic uh form of ah
8:19definition that we have for creating a
8:22table defining a table or defining a
8:24schema
8:27now we can add a number of integrity
8:30constraints
8:31to the create table
8:333 integrity constraints
8:35we will discuss here one is not null one
8:39is primary key and other third is
8:42foreign key so not null will specify
8:44whether a field can be null or not
8:47primary key as we have seen will specify
8:49the attributes which form the primary
8:51key
8:52and the foreign key will specify the
8:54attributes which reference
8:56some other table
8:58and are
9:00key in that table
9:02so
9:03here is an example
9:05the instructor
9:07in the instructor
9:09[Music]
9:12relation here we have we had seen this
9:15part the attribute what we have added
9:18here is
9:19this not null
9:21so we say the name is not null which
9:23means that
9:24in the instructor table it is not
9:27possible to insert
9:29a record
9:31where the name of the instructor is null
9:34that is unknown but it is possible it
9:37the same thing is not said about ah
9:40department name same thing is not said
9:42about salary so it is possible that
9:44these could be null
9:46now
9:48we additionally say that
9:52primary id primary key is id so this
9:55field id is a primary key
9:58and it is a property of sql
10:01create table command that if an
10:04attribute is
10:06referred as a primary key then it cannot
10:09be not null so
10:11you do not need to specify that is here
10:14you do not need to write
10:16not null
10:17because it is a primary key it will
10:20be known to be not null because
10:23certainly
10:24we have discussed that key is the
10:26distinguishing attribute in a database
10:29table so it cannot be null so it will
10:31not be able to distinguish
10:34similarly
10:35we have finally we have the
10:37third integrity constant which is
10:39foreign key which says that it is
10:42referencing
10:43this table
10:45department
10:47and the foreign key of this is here the
10:50department name the depth name
10:53this particular field is a foreign key
10:55which will which is a key of the
10:58department table
11:00and
11:01so we will be able to
11:03refer this
11:05from this table as a foreign key and we
11:07know that it is a will be a key in the
11:10department table
11:12so these are the
11:14ways to specify the integrity constraint
11:18ah
11:21so
11:24here are a couple of more examples so i
11:26will not go through them in detail
11:30i will
11:31request you to take time and carefully
11:35understand them
11:36again these are
11:38about
11:39different
11:40relations that exist here about the
11:42student
11:44and about the courses that the student
11:46take
11:47and
11:48in every case we have specified the set
11:51of fields that you have in the table
11:53in the
11:54design of the schema are listed in the
11:57create table
11:58the id information about the
12:01primary key is provided and also the
12:04information about the foreign key here
12:06department name is the foreign key which
12:08is mapping to this point similar things
12:11can be observed about the text
12:13relationship which space show
12:16the
12:17how students are actually taking courses
12:21so it ah
12:22relates ah different
12:25it has a set of fields but
12:27it has two kinds of primary
12:29ah
12:30foreign keys
12:32one that relate to the student through
12:34the id
12:35and this combination this combination of
12:39attributes which refer to the section
12:44so this is how different
12:47[Music]
12:49tables can be created using the
12:52data definition language
12:54here is a note
12:56that you should observe that if you
12:59consider
13:01this section id
13:03the section id is a part of the primary
13:06key which means
13:07that two records cannot be
13:12same
13:13if they are
13:14if they if they are different in the
13:17section id
13:18then such records are allowed so which
13:20means that
13:22it is possible that a student can
13:25attend
13:27or take a course
13:29in the same semester in the same year
13:32with two different section ids because
13:34they are primary keys so they can be
13:36different
13:37so if we drop this from the primary key
13:40then we will enforce the condition
13:42that no student will be able to take a
13:46course
13:47in two sections in the same semester and
13:50the same year so this is these are the
13:52different design choices that we have
13:55and we will move on ah here is one more
13:59example trying to show you the create
14:02table command for the course
14:07relation that we have in the
14:09university database
14:11moving on let us look at how to update
14:15or
14:16actually put in
14:18different records in a table which has
14:21already been created the basic command
14:24is insert and
14:26the keywords
14:28for that is insert into
14:30and values in between you write the name
14:32of the relation
14:34where the record will have to be
14:35inserted and then the values will have
14:38to be
14:40put as a tuple
14:42in the same order in which you would
14:44have defined the attributes of that
14:47relation
14:48and certainly each of the values like
14:51this is id value next is the name value
14:53the department the salary each one of
14:56them
14:57should be from the same domain type as
15:00has been specified during the create
15:02table
15:03command
15:05so these things will have to remember
15:07and
15:08so every record will get inserted
15:10through one insert command
15:12similarly a
15:14deletion can be done by delete from
15:17students if you do delete from students
15:19without specifying
15:21ah which record you want to delete
15:23basically all records will get deleted
15:26we will see how selective deletion will
15:28happen that will come on later
15:31drop table is a command to remove a
15:34table a table that has been created can
15:36be removed from the database all
15:38together by doing drop table and the
15:40relation name
15:42you can also change the schema of a
15:44table by using alt table so the form is
15:49alter table is a
15:50the keywords
15:51you can add a new
15:54attribute to relation r
15:56by writing the name of the attribute and
15:58the domain of the attribute one after
16:01the other
16:02similarly it is possible also
16:06to drop an attribute that already exist
16:10and
16:11the
16:12syntax for that will be at alter table
16:15the relation name drop is the keyword
16:18and the name of the attribute mind you
16:20all database systems may not allow you
16:24to
16:24drop an attribute to alter table to
16:27remove attributes and so it works in
16:30some and it does not work in the rest
16:34now let us so that was about the ah
16:37definition
16:38of the table and the basic definition of
16:41the data so now we will get into the
16:45basics query structure which is ah with
16:47tables with existing data how do i query
16:51and find out different information
16:54so the structure of an sql query and
16:57this you should
16:59observe very carefully
17:01is
17:02normally said to be select from where
17:04colloquially we will often say ah let us
17:06have a select from where
17:08so
17:09it has three keywords select which is
17:12followed by a set of this is a set of
17:15attributes
17:16so this specifies that when a select
17:20query runs it will finally give us a new
17:23relation
17:25and in that relation
17:27the attributes that will be
17:31available are the attributes that
17:33feature in the select list
17:37the next
17:38clause or the next
17:40keyword in this is from
17:42which specifies a set of existing
17:45relations
17:46so r one r two r m represent different
17:50relations
17:51and these are the relations which will
17:53be used to actually find the information
17:57extract the information
17:59finally the where clause has a predicate
18:02as a condition
18:03which specify that what condition
18:07has to be satisfied so that
18:10certain tuples from the relations r 1 to
18:14r m
18:15will be chosen and put in this new
18:19selected result table in terms of the
18:21attributes a 1 to a n
18:24so this is the basic
18:26understanding of the or structure of the
18:29ah
18:31sql query
18:32and naturally as i have mentioned that
18:35it will result in a relation
18:37now we will go over each and every
18:39clause carefully the select clause as i
18:42said will list all the
18:44attributes so it is like a projection in
18:47terms of the relational algebra that we
18:49have done
18:50so
18:52if we write select name from instructor
18:55then
18:56this will result in
18:58finding the names of all instructors
19:02from the instructor table because this
19:04is
19:05ah this you know is a relation because
19:07it is happening
19:09it is featuring in the from clause
19:11and in select we are saying that the
19:13attribute that we want to select is the
19:15attribute name
19:16so it will
19:18the instructor table has four attributes
19:22id name
19:23depth name and salary from that it will
19:26simply take the name of the instructor
19:28and list that in the
19:30output table
19:33so the basic form of selection that
19:35happens
19:36ah at this point you may also note that
19:38in sql ah everything is ah case
19:42insensitive it does not matter whether
19:43you write in upper case or lower case so
19:46you can choose the style that you prefer
19:48ah to use
19:52this is a
19:53very important factor that you should
19:55keep in mind that we said
19:58while introducing relational algebra
20:00that in the relational algebra
20:04everything is a every relation is a set
20:07and which means that according to set
20:09theory we cannot have two tuples in the
20:13same relation which are identical in all
20:15its values because set theory does not
20:18allow that
20:19but please ah keep in mind that sql
20:22actually allows duplicates in relations
20:25so it is possible that in the same
20:28relation in the same table i may have
20:31more than one
20:32record which are identical in all the
20:36fields in all the attributes of that
20:39table
20:40and this will have lot of consequences
20:43and will see how often
20:44this property will
20:46have to be used so if you want a typical
20:50set theoretic kind of output
20:53that is if you want the relations to be
20:56the result to be distinct all records to
20:58be distinct then you have to explicitly
21:01say that
21:02you want
21:03distinct values to be selected so all
21:05that you are doing is you are after
21:08select and before the attribute name you
21:10introduce another keyword distinct
21:13so
21:14select distinct depth name from
21:15instructor will actually select
21:18the departments of all instructors
21:21and
21:22quite well if it just selects ah
21:24department name of all instructor then
21:26it is quite possible that the same
21:28department name will appear number of
21:30times because
21:32every department has multiple
21:33instructors
21:35but when we use
21:36distinct then the every name will
21:38feature only once in that selection
21:44then you can also specify another
21:47keyword all
21:48which
21:49ensures that the duplicates are not
21:52removed so if you do select all depth
21:54name then all the names will feature
21:57with duplicate so if some department had
22:00three instructors the name of that
22:02department will feature thrice
22:05you can use
22:06an asterisk
22:08after select to specify that you are
22:10interested in all the attributes that
22:13the relation
22:14or the collection of relations in the
22:16from clause has
22:19you can also specify
22:22a select
22:23with a literal and without a from clause
22:28if you do that then it will simply
22:30return you a table with a single row
22:33having that literal value and you can
22:35also rename that ah table using ah what
22:39is known as the as clause
22:42ah as
22:43command
22:44so this will give you a table foo
22:47where there is only one row and that row
22:50has an entry 437
22:56you can use that for other purposes also
22:59you can
23:00do a select of a literal from a table
23:03with
23:04using a from clause wherein ah you will
23:08get a single column table where as many
23:12a's as there are records in the
23:13instructor will be produced
23:16select clause can also use arithmetic
23:19basic arithmetic operations for example
23:22here we are showing ah
23:24a select where the third attribute as
23:26you can see
23:28the third attribute is salary by 12
23:31assuming that the instructor table has a
23:33salary number which is annual salary by
23:3512 naturally give you the monthly salary
23:38so those
23:40such
23:42arithmetic choices can also be made
23:46you can also rename that
23:48field that particular salary by 12 field
23:52in ah salary by 12 field by a new name
23:55as i said as can be used to rename
23:58so if we ah if you use that then
24:02when you get the output you will get the
24:05column names id name and monthly salary
24:09and in monthly salary you will actually
24:11have a computation which is salary by 12
24:14and in the same way you can use multiple
24:18different kinds of arithmetic operators
24:21now we come to the where clause where
24:23clause specifies the condition is a
24:25predicate which corresponds to the
24:28selection predicate of
24:29relational algebra so it will specify
24:32some condition here is an example if we
24:34want to find all instructors ah from the
24:37instructor table
24:39who are associated with computer science
24:42department then you can say select name
24:45from instructor
24:46and
24:47to specify that they are from the
24:50they are from
24:51the computer science
24:54department you will specify department
24:57name is equal to computer science so
25:00this will ensure that
25:02you select the records only when this
25:04condition is satisfied so all records
25:07for which department name is different
25:09from computer science will not be
25:11included
25:12here
25:15ok
25:20you can also write predicates using the
25:23different logical connectives and or not
25:26and so on so here is an example where
25:30you are finding all instructors in
25:31computer science with salary greater
25:33than eighty thousand
25:35so here we have used and clause so only
25:38records where the department name is
25:39computer science and salary is greater
25:41than 80 000 will be
25:44chosen in the result
25:46so and then the projection will be done
25:49on the name of those instructors
25:53you can apply
25:55comparisons ah of arithmetic expression
25:57so where clause can really write
25:59different kind of things
26:06finally the from clause is
26:08sets all the different
26:10relations from where you are actually
26:13looking for the records
26:15so it kind of corresponds to the
26:17cartesian product of the relational
26:20algebra
26:21so if we
26:23ah want to say
26:25compute
26:26instructor cartesian product teaches
26:29then you can say select star
26:32instructor
26:34one table comma teaches
26:36so this will choose
26:40records from instructor relation as well
26:43as from teachers relation and in all
26:46possible combined way
26:47it will put them in the output we have
26:50used a star so all fields of instructor
26:52and all fields of teachers will be there
26:55in the output
26:57and since
26:58some fields may have identical name like
27:01id
27:02there is an id in instructor and there
27:03is id in teachers
27:05they will be qualified by the name of
27:07the relation
27:11from can ah have one relation two
27:14relation any number of relations as you
27:16require
27:20so this will cause the cartesian product
27:22to be computed which may not be very
27:24useful ah
27:25as
27:26an independent feature but we will see
27:30in the next module how it can give very
27:32important computations ah in terms of
27:35computing joints and so on
27:37so here is an example ah of the
27:40cartesian product that we talked of so
27:42here is the instructor relation the
27:44teachers relation
27:46and as you can see when we have
27:48done this ah
27:50cartesian product that is select star
27:52from instructor comma teaches
27:55then all fields this is a
27:58id
27:59of the instructor it has
28:01there is an id in teaches so that is
28:04also specified here qualified by the
28:07name of the relation
28:09whereas name
28:10comes in directly because there is no
28:12nothing no attribute called name in
28:14teaches
28:15the department name comes in directly
28:17salary comes in directly course id comes
28:20in section id comes in semester comes in
28:23year comes in and so on
28:25and the combination of
28:28all tuples in the
28:31instructor
28:32relation against all tuples of the
28:35teachers relation
28:36all possible combinations have come in
28:39in this result
28:40which eventually is a cartesian product
28:44of the relational algebra
28:46so
28:48this is uh
28:50what we have
28:52to summarize we have introduced the
28:55relational query language
28:57and particularly familiarized ourselves
29:00with the data definition that is
29:02creation of the table creation of the
29:04schema
29:06with the attribute names domain types
29:08and constraints
29:09and
29:10the updates to the table in terms of
29:14insertion and deletion of values or
29:16addition or deletion of attributes or
29:20removing a table altogether and
29:23then we have
29:24given the basic structure of the select
29:27from where query of sql
29:30which will be the key
29:32language feature of a query language
29:35that we will continue to discuss all
29:37through this course

About this transcript

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