Hello and greetings. In our ongoing explorations of databases, we had started out exploring
the default or the standard for database querying namely the structured query language or SQL.
So let us continue with SQL in this session today and look at some of the more advanced
aspects and what kinds of queries that we can express within simple SQL statements.
So before we begin let us summarize what we have studied until now in terms of SQL. Now
we looked into an introduction to the SQL standard and what are the building blocks
of SQL namely tables, attributes and values which or tables, rows and columns and we also
saw how to create schema in SQL and what is meant by a schema and how do we create tables
and what kinds of column descriptions can we give when we create tables and what kinds
of constraints can we specify when creating tables and so on.
We also saw how to specify entity constraints specifying what is a primary key and what
are the secondary keys in a relation or a table. We also saw how to specify constraints
like something should not be null or something should have a default value and we also saw
how to specify foreign key constraints so that referential integrity is maintained and
we also started by looking into the different forms of the select operation which is perhaps
the most frequently used SQL operation for retrieving tuples from the database.
So the most generic form of select operation is the select from where clause where you
say select attribute list and say from table list where a given condition holds true. We
also saw how we can disambiguate attribute names especially when we would be using multiple
tables and two or more tables have attributes with the same name. One simple way of disambiguating
attributes names is to prepened the name of the table before the attribute name. So we
saw something like instead of saying name, we say employee dot name or department dot
name and so on. So, on the other hand we can also use aliasing for disambiguating names
mainly because when table names are also the same, when we saw the example of a join between
employee and employee tables.
So we basically use the notion of aliasing where you say use the term or use the table
employee and call it as some other name like say e or boss or whatever. Then we also saw
how we can select from multiple tables, the Cartesian products of multiple tables and
what happens when we omit the where clause and so on. And we also saw the different kinds
of set operations on tables. By default in SQL and very unlike relational algebra, in
SQL tables are treated as multi-sets or bags that means it can tolerate or it is valid
to have different tuples of the same having the same data in a SQL table which is not
valid in or which is not valid by default in relational algebra.
So we can make a table into a set by using the keyword called distinct. So when we say
select distinct some query, a query is returned with all duplicates removed from the query.
We also saw how to perform set operations on tables and sets by default, these operations
by default assume that table are sets like union, intersection and except for set difference.
If we have to specify that the tables are not sets but instead they are bags, we have
to specify, we have to use the qualifier called all. So we say union all whatever, so we say
employee union all department and so on or employee union all managers and so on.
And we also saw how to compare substrings using the percentage symbol or the underscore
symbol which matches a single character and we also saw some arithmetic operators like
addition, subtraction, multiplication and so on which need not just be used within the
where clause but they can also be used right after the select clause itself.
So let us move on further today and in today's talk, I will be using two example tables in
order to illustrate several features of SQL. So let us look into these tables once again.
The first table that we are going to be considering is the employee table. So the employee table
contains employee number which is the second field shown in the slide here as the primary
key. It also has a secondary key called pan number of the employee which obviously has
to be unique and non null and employee is given a name, gender, date of birth, address,
salary and reports to which is a foreign key.
That is reports to contains another employee number which refers to the manager to which
this particular employee reports to. If reports to is null then it means that this employee
is the head of the company or that is he doesn't report to any other employee. Then there is
a department number or the dnumber which shows where the employee works in. The second table
that we are going to be considering today for our examples is the department table.
The department table as shown in the slide here is indexed by or uses as primary key
the field called department number or dnumber which is the same as the dnumber used in the
employee relation that means the same domain. Department also has the name, address and
head which is again a foreign key which points to the employee number of the employee who
heads a particular department. So here are the specific declarations which says primary
key is dnumber and head is a foreign key which references employee number in the employee
So we had started out with looking at arithmetic operator, so let us continue from that point
on. And we had seen how you can use plus, minus, star and slashes to perform arithmetic
operations namely addition, subtraction, multiplication and division. Similarly you can use the operator
called or you can use the keyword called between to check for a range that is to check whether
a given parameter lies within a given range.
The slide here shows an example which is of the form select star from employee where salary
between 3 lakh and 4 lakh. Therefore this query returns all employee records select
star in this case note that the star here is not the multiplication operator but star
here refers to the entire tuple in this when star occurs by itself. Therefore this query
selects the entire set of tuples, the entire set of columns for all tuples where the salary
field or the salary attribute is between 3 lakhs and 4 lakhs. It is also possible to
sort the output of a given query using one or more parameters.
The sorting is achieved by a new construct called order by, this construct is shown in
this slide here. This slide shows another small query which says select star from employee
where salary is greater than 3 lakhs and order by name and employee number. Therefore this
query returns the same result as the previous query that is the set of all tuples, the complete
tuples for all employees well not exactly the same of course for all employees whose
salary is greater than 3 lakh but not necessarily less than 4 lakhs and the output here is ordered
first by name and next by employee number. So that means that it is first ordered, the
records are first ordered by name and wherever there is a tie that is two or more employees
having the same name, it then orders those tuples using employee number as the ordering
We now come to the next aspect of SQL querying perhaps what gives it compositionality so
to say that is by which you can compose queries, bigger queries from small queries this is
what is called as the nested query. Note that just like in relational algebra, the output
of an SQL statement is a table especially the select statement. The output of a select
statement is a table and the input is also a table. It can be multiple tables which are
treated as a Cartesian product in which case. Therefore the output of a particular query
can be used as part of another query to perform further searches.
So the slide here shows such an example where a query has two different parts in it. For
the sake of convenience they are shown in two different colors white and yellow. There
is an outer queries so called outer query which says select name from department that
is select the names of departments where dnumber in, so in is a new keyword that we are also
introducing here which essentially stands for set membership, that is where the dnumber
or the department number belongs to the set of all tuples that are returned by the query
which is shown in yellow. That is where dnumber in the query called select dnumber from employee
where salary is greater than 3 lakhs.
So let us analyze this query. What does this query do? Firstly take a look at the inner
query. The inner query says select department numbers of all employees whose salary is greater
than 3 lakhs and the outer query is saying select the names of those departments which
are returned by the inner query. So essentially this query is asking the database show me
all departments which pays greater than 3 lakhs as a salary. So the highlighted query,
the yellow part of the query here is called the nested query and the non-highlighted query
is called the outer query in this case.
So let us continue with nested queries and note that a nested query contains two different
queries and the inner query or the nested query in this case returns a complete relation
or returns a complete table. For all practical purposes we can consider the table as sets
or bags comprising of different tuples and the in clause that we used in the previous
slide performed exactly that, that is did exactly that. That is it considered the table
that is returned by the inner query as set and essentially performed a test for set membership.
That is whether the dnumbers specified in the outer query belongs to the set that is
run in the inner query. Similarly one can do a check for a particular attribute against
all elements in a set using the keyword called all. This is shown in the slide here. The
outer query says select name from employee where salary is greater than and then the
inner query begins this is shown in the slide here. So the inner query here says select
salary from employee where supervisor equal to 007. So let us try to analyze the query
step by step, first take the nested query or the inner query here. It says select salary
from employee that is give me the salaries of all employees where it should actually
be reports to there is a small bug in this slide where reports to equal to 007 that is
give me the salaries of all employees who report to 007. So we just want to know what
is this employee number 007 or agent 007 pays all of his subordinates. And then the outer
query says select name from employee that is give me the names of all employees where
salary greater than all of this inner query. That is whose salary is greater than all of
the salary that is paid by 007 that is it is greater than the maximum salary that is
paid by 007. So it returns the names of all employees whose salaries are more than the
salaries of all those who report to 007.
Now what happens to disambiguation of attribute names when we are considering nested queries?
So this slide here shows such an example. Firstly, the rule which says that any unqualified
attribute name in a nested query applies to the inner most block of the query. So what
does it mean? Have a look at the query here. There are again two levels to this query,
the outer query says select E. name and then I say from employee as E that is I am aliasing
employee table as E where E.employee number in and the inner query begins which says selects
reports to from employee where E.name. Note that I am using the alias called E inside
the inner query as well, while the alias is actually defined in the outer query and then
I say E.name equal to name.
So what is this query do actually? So if you notice closely, the outer query as well as
the inner query works on the same table called employee. The outer query calls itself as
E and the inner query does not change the name of the table, it is still called employee.
Therefore in the inner query when I say select reports to that is give me the set of all
employee ids of the bosses of all employees where the name of the employee is the same
as the name of the boss that is E.name here would be the boss here because I am looking
for E.employee number to occur within this set of all employee numbers who are bosses.
So the query essentially returns all employee names who have the same name as their boss.
So let us look at few more definitions pertaining to nested queries. The kind of query which
we just saw now that is where a particular alias is defined in an outer query and then
used inside an inner query, such kinds of queries are called correlated nested queries.
That is there is a greater correlation other than the fact that an inner query or a nested
query occurs within a condition, there is a greater correlation between these two queries.
Now how do we understand or how do we analyze the behavior of a nested query. Now to understand
how a nested query begins, it should be noted that or it is sufficient if you just note
that every select query is performed or every select condition is performed exactly once
on each tuple that is specified in the table that, for the table specified in the query.
Therefore let us have a look at the previous slide once again. In the previous slide for
the time being consider just the outer query. Assume that there is just one select statement
which is the outer query. So this statement says select some attribute that is E.name
from the set of all tuples in employee where some condition that is E.employee number in
whatever. So let us not worry about what the condition is let us just assume that there
is some condition called C.
Now this condition that is in this case the nested query and the set membership function
that is E.employee number in that nested query, this whole condition is checked once for every
tuple that forms the employee record that is that forms the employee table. So for every
employee, this particular condition is checked that is for every employee we are checking
whether his employee number occurs in the set of all employee ids of people who have
the same name as one of the subordinates. So in that way as long as we remember this
fact that select query or the select condition is checked once for each tuple, its easy to
understand nested queries in some kind of recursive fashion that is you have to understand
this for each level in a given nested query.
So continuing further with next nested queries we can use a term or the keyword called exists
or of course not exists to check whether the output of a given nested query is empty or
not. So if the output is not empty then exists returns true that is there exists some results
from this query and if the output is empty then exist returns false or not exists returns
true. So there is an example given in this slide regarding this which just says select
D.head where from department as D of course that is alias department as D where not exists
where the following condition does not exist.
What is the following condition which is the inner query, select employee number from employee
where reportsto equal to D.employee number. So that means essentially I am looking at
the head of all departments that is I am looking at D.head, so the employee numbers of the
heads of all departments where this does not exist. What is this? Select employee number
from employee where reportsto equal to, this should actually be a, there is again another
bug here, this should actually be employee.employee number that is or D.head rather. So where
where reportsto equal to this head that means give me the set of all heads of departments
who do not have anything, who do not have anyone working under them because this condition
should not exist is what we are checking for.
We can also specify explicit sets. Until now we have been looking at implicitly defined
sets, we have looked at set membership in the form of in condition, we have looked at
set comparison in the form of all condition and we have also checked for empty sets using
the exist condition.
But all of these sets where actually specified in the form of a query that is the nested
We can actually specify sets in a more explicit fashion using just parentheses that is we
just parenthesize and enumerate all elements of the set explicitly. The slide here shows
one such example which says select name from employee where reportsto in 007, 008, 009.
Therefore what it says is that give me the names of all employees who report to either
007, 008 or 009 or that is whether the reportsto field or the reportsto attribute holds one
of these values in this.
one can check for null values in an SQL table using an SQL command called null or a SQL
keyword called null capital n u l l and you can also check for whether something is null
or is not null. So remember what constitutes a null value for a particular attribute. a
null value is a value which is not applicable or an attribute which has no value or no semantic
value associated with for a particular tuple. This is different from saying that the value
the attribute is zero or unknown.
So the example here shows a query which says select E.name E.salary from employee has E,
so that is select the names and salaries of employees where something exists. What is
that exists or that is where the following set is not null. What is the set which should
not be null? The set which says select employee number from employee where E.reports to equal
to employee number and reportsto is null. So let us analyze the interior query or the
inner query again.
It says select or give me the set of all employee numbers from the employee table where E.reportsto
that is the employee from the outer query is a subordinate of me that is the employee
in the inner query and reportsto is null that is I don't report to anybody else. So essentially
the query returns the names and salaries of all people who report directly to the head
of the company.
That is this the name and salary of all people where the following condition exists. The
following condition here is the set of all their bosses who also happens to be the head
of the company that is who doesn't have any other supervisor himself or other self. So
note that here the use of the condition is null that is whether reportsto is null. This
is different from saying reportsto is 000 suppose 000 is the valid employee number,
this is different from null that it means that this employee has a boss. But in this
case this is looking for the fact where this employee does not have a boss or does not
have anyone to report to.
Just like we have been using aliasing for renaming table names, the as key word or the
as clause can be used to rename attribute names as well. So the slide here shows such
an example. It says select name as employee underscore name from employee. So what does
it do? It simply selects the set of all employee attributes from this table, however while
returning it when the table is returned the name of this attribute is changed from name
to employee underscore name.
Now this in turn may probably be as part of a larger query were this would matter or even
if it is just printed out on the output, the name of the attribute would have changed from
name to employee name or employee underscore name. Note that when a select query is run
over multiple tables that is the from clause contains multiple tables select name, salary
from employee, department. By default it assumes that we are having a Cartesian product between
the tables that are specified. Therefore if we have to compute a join between employee
and department for example we have to identify, we have to explicitly identify which is the
attribute and which has to be compared.
Therefore in a simple select operation, let us say we have to join, we have to compute
a join between employee and department we would say something like select star from
employee, department where employee.dnumber dno.
Remember what the employee record look like. The employee record had one of the fields
as the dnumber or the department number of the employee where he is working in, so where
employee.dnumber equal to department.dnumber. So we have to explicitly equate these two
attributes to perform a join. Otherwise it is constitute as a Cartesian join between
employee and department.
On the other hand we can specify a join that is rather than a Cartesian product using the
keyword called join, the slide here shows such an example. The slide shows a query which
says select name, address from employee join department. Now when we just say join without
any further qualifiers, there is still not enough information to identify which attributes
to use for join condition. What is the theta or what is the join condition here? Therefore
we specify that condition explicitly. So we say as shown in the slide here select name,
address from employee join department on dno equal to dnumber.
Note that dno is an attribute of employee and dnumber is an attribute of department.
So this is specifying an equijoin condition that is it is equating dnumber dno to dnumber
and of course there is a where condition which says department.name equal to research. Therefore
it is computing the join between employee and department and selecting only those tuples
where department name is called research and then printing the set of all names and addresses
of employees who work in this department.
It is also possible to specify natural join directly without having to specify the equality
condition. Note that in a natural join if we have two tables, at least one of the attribute
names should be the same or should be common between the two tables. So natural join which
in relational algebra was depicted by the star operator just performs in equijoin between
two relations where the set of all, where it has some subsets of attributes having the
same name. So the same condition also holds true in SQL that is if you are performing
a natural join, you have to have at least one attribute name which is the same.
You can always change the name of a table and its attributes names using the as clause
which we have already seen. So a natural join may be specified using the natural join construct.
It automatically finds attributes having the same names for performing the join and of
course relations can be renamed, relations as well as attribute names can be renamed
in order to accommodate natural join. So there is a specific example here which says select
employee.name, department.name from employee natural join department.
In our specific example though, the only attribute name which was common between these two was
the name clause itself. Therefore for our particular example schemas that we have taken,
this query may not make sense but the syntax of the query is illustrated. The main idea
behind the example is to illustrate the syntax of the query which just says select employee.name,
department.name from a natural join between employee and department. That is to identify
all attributes which have common names between employee and department, we compute a natural
join and then project the set of required set of attributes from them.
Similarly other kinds of joins in which we saw in the session on relational algebra can
also be specified using the appropriate keywords in SQL. Just like you have natural joins,
you can also specify left outer join, right outer join and full outer join as part of
any SQL statement. We next come to the notion of aggregate functions in SQL. Until now we
have been working on generating a set of tuples or the set of all tuples that match a particular
Sometimes we may need aggregate properties of a query rather than the set of all query
results. So there are number of aggregate function in SQL, some of them are shown here.
There for example count which counts the number of tuples in the query result, sum which computes
the sum of the set of all values in the query result in which case the query result should
return single numeric attribute. Average which computes the average of all values in the
query result. Again the query should return numeric attributes, max and min which computes
the maximum and minimum values of all the query results and of course max and min will
work if the query results are numeric or they are ordinal which basically means that they
have some kinds of total ordering that is specified among them.
For example date, date has a total ordering you can always compare two or more dates.
The query can result the maximum and minimum among dates and of course char, you can always
compare characters using their ASCII equivalent. So the maximum and minimum can be returned.
Here is a small example query shown in the slide which just says select count of salary
min of salary max of salary sum of salary and average of salary from employee which
is obvious. It just counts the number of salary elements, as you can see I could have as well
set count star, it does not make any difference because it just counts the number of tuples
in the relation and min of salary, min of the salary field max of the salary, salary
attributes, sum and average of all the salaries.
Since aggregate functions return a single value and not a table, they can actually be
used as a part of a logical operator within a where clause. Until now when we have been
talking about nested queries, we have been treating the nested query as the set and we
have only been applying set theoretic operators like in and all and exist and not exist and
so on. But once we use aggregation function and reduce our query result to a single value,
I might as well use it as part of a logical operator.
The example in this slide shows such a case which says the outer query says select E.
name from employee as E where the inner query says select count star that is select the
count of all, the set of all tuples that are returned by this query and what is the query?
From department as D where department. head equal to E.employee number. That is it is
selecting the set of all employee numbers who heads departments and counting the set
of all such queries that is it is count, it is for each employee that is search in the
outer query it is first searching the set of all departments that the employee heads
and return in their count. And this count is compared with two here that is greater
than or equal to two. Therefore the semantics of this query is return the names, note that
we are finally retuning E.name. Return the names of all employees who head two or more
In some cases when we are talking about aggregate functions, it is not really desirable to apply
the aggregate function to the set of all query results, set of all tuples that have been
returned by the query. It may be more desirable to apply this, the query or the aggregation
functions two different sub groups of the query results. Now you can specify such sub
groups using what is called as the group by clause. The group by clause is shown in this
query here in this slide.
The slide shows a small example, select dnumber dno, count star, average salary from employee
group by dnumber. What it does is for each department that is it first checks the or
it first scans the set of all tuples in the employee table and then groups the set of
tuples according to dnumber or dno. So for each department that are contained in the
employee table count the number of people working in it, that is because we are counting
the set of all employee records which have this dno. So count the number of people working
in this department and also count the average salary of the people who are working in this
But note that the group by function is computed after computing the results of a query. That
is the query first scans the set of all employee records as in the previous example and then
groups those records or groups those tuples based on dno and then performs the select
operation. However if you want to select certain tuples based on some aggregate property, not
an individual property note the important difference here.
Suppose they want to select a set of tuples from a table based on some aggregate property,
we can use different keyword called having clause. The slide here shows such an example,
it says select dno that is select the department number count and average salary from employee
group by dnumber that is it's the same query as earlier that is for each department return
me the count and number of people working in it and average of salary having count greater
than 20. That is there is a further constraint here. For all departments having more than
20 people working, show me the department number, the number of people that are working
there and their average salaries.
Note the difference between having and where clause. You might you might be wondering we
could have just used the where clause, select from where, where actually specifies a particular
condition and having is also specifying a particular condition. Now what is the difference
between having and where?
Having versus where: Where conditions apply to individual tuples independently that is
the where condition where I say something like salary greater than 3 lakhs is applied
to each tuple independently. However the having condition applies to groups of tuples, its
an aggregate property that is having count greater than 20 that is the number of tuples
is greater than 20 or the average of salary is greater than something else and so on.
So having is specifies a condition that applies to a group of tuples, whereas where specifies
conditions that apply only to individual tuples.
So let us look back at select now. How does select look like after going through all this
different variants of select? Now once I look back looking at all this different variants,
we see that the select condition has the following constructs. Select attribute and or function
list I can also give a function remember, I can always say select 10 times 10 star salary.
That is what happens if my salary increases by 10 times and so on.
So select attribute list or function list from table list and the rest are all optional,
where condition is an option. if you don't specify where then every tuple is checked.
Group by grouping attributes. How should the tuples or how should the output be grouped?
Having group condition that is some kind of aggregate properties that we can check. Then
finally order by which is the sorting condition for the output.
We now move into other operations within SQL, select obviously is the most widely used operation
and hence the most detailed in terms of some its syntax but we still need operations for
inserting or adding data into tables and modifying tables, deleting data from tables and so on.
By modifying tables here I mean modifying data in tables not modifying the structure
of tables which can be done using the alter table command. So insertion of data into tables
can be performed using the insert command. The insert command is shown in the slide.
Just like we have select from, we have insert into.
So we say insert into employee values and I give the entire record for the employee
within parenthesis. So I am inserting a complete record where the first 1002 stands for the
pan number, the second 002 stands for employee id, the third field Bharath Kumar stands for
the name then M stands for the gender, 9-5-1973 stands for date of birth and so on. So we
can specify the entire tuple in line into the query itself and corresponding fields
and the corresponding attributes are matched. So inserts an entire employee record with
On the other hand I can insert or I can specify only partial set of attributes within the
within the tuple. So this slide shows such an example which says select into employee
or rather insert into employee name, address and dnumber values Arun K Ysehwanthpur and
5. Therefore it returns or it inserts only those fields or only those attributes called
name, address and department number or dno. What happens to the other attributes? The
other attributes will either get a null value or the default value if a default is specified.
Therefore you can see that there is an implicit constraint in this select operation. That
is I cannot leave out, when I am inserting a tuple I cannot leave out any attribute name
which contains a not null constraint and does not have a default value. If it does not have
a default value and it is not allowed to be null then I have to specify a value during
insertion or else insertion is going to fail.
An insert or insertion of more than one tuples to a table can also be performed using a select
operation. Note that select actually returns a table. Now if I return and table is nothing
but a set of tuples. Now if a select operation returns a set of tuples and these tuples are
in a format that a ready to be inserted into another table I can directly specify the select
command within the insert command. This is shown in this example here. First I just create
a table called EMD which contains just three attributes employee, manager and dno that
is the department number. Then I give an insert command from the employee and department table
using the following syntax.
I just say insert into EMD that is insert into the new table name and what should I
insert? The output of the following select operation then I just give the select command
that is select E.employee number as a employee, E.reports to as manager and D. dnumber as
dno and then from employee as E join department and so on. So basically I am getting a set
of employee number, manager number and department number which is what is going into the new
So what are the properties of the insert command? Let us summarize the insert command once again.
So insert tuples that are specified as part of the command and all attributes which are
not null and do not have a default value have to be specified as part of the insert statement.
And what happens if I give the same insert statement twice? That is the same insert statement
with the same set of data elements and it is actually performed twice. It doesn't, insert
does not do any checks that is remember that tables are treated as multi-sets rather than
sets. Therefore insert just goes and inserts the tuple again. That is second occurrence
of the same tuple. This is done as long as the second insertion does not violate any
unique constructs. That is if I give a set of data values in
the first tuple and give the same set of data values in the second and one of the attributes
has to be unique then the condition, then the constraint fails and in turn insert also
fails. So as long as the unique construct is not violated, insert will just insert multiple
tuples into the table. Insert also fails if referential integrity is violated, this if
your DBMS supports it of course that is if I try to insert an employee number as manager
where which refers to a manager entity which does not exist, referential integrity fails.
Multiple tuples can be inserted within a single insert command, first of course by using the
select statement or by just giving multiple tuples one after the other separated by commas
and each tuple is enclosed within parenthesis.
Deletion of tuples: How do we delete tuples from a table? Deletion of tuples is very similar
to the select statement and has the same structure which is of the form delete from where. It
says delete from employee where some condition that is employee number equal to 007, the
first one which just deletes one tuple. Note that employee number is the primary key therefore
it is unique and therefore it just deletes one tuple.
On the other hand the second tuple says delete from employee where department number in the
set of all departments that are headed by 007, so it can delete possibly more than one
tuple in this statement. On the other hand if I remove the condition and I just say delete
from employee, it deletes all tuples. Note that deleting all tuples is different from
dropping the table. Here deleting all tuples corresponds to truncating the table that is
the table exists but it has no tuples in it. Whereas, if I drop the table the table itself
does not exist in the database.
Updation of tuples: How do we modify tuples? You can update tuples by using the update
operation and the update operation also has a very simple syntax which is of the form
update set where that is update employee as shown in this example here. So update employee
set whatever updation I need to make that is set salary equal to salary times 1.1 where
reportsto equal to 007.
Therefore what I am doing here is that for all people working under 007, I am updating
their salary by or I am increasing their salary by 10 %, I am giving them a 10 % rise by this
update statement here.
We now come to the last leg of this session where we take up the notion of views or virtual
tables. This is again an important concept when designing large databases. View as you
can typically or intuitively understand is one particular view of the database that is
one particular projection of the database which is suitable for certain kinds of work.
That is let us say a HR manager needs to know only the employee that is the HR related details
of an employee. He doesn't need to know the technical details of an employee or the project
related details of an employee.
Therefore we have basically created a view of the employee record for the HR manager
which is different from the view created for let us say the project manager. So table or
a view is also called a virtual table, this is a table that is derived from other tables.
In contrast a table that exists in the database is called a base table. So a view can be derived
from either other base tables or other views. Views are need not be stored, it's not that
they are not stored but views need not be stored in the database. That is the data contained
in the views need not be stored in the database. But they are typically stored as queries and
not as tables and update operations for views are limited, this is as the result of storing
them as queries but querying is not, you can query but updation is limited in terms of
So views can be created in SQL using the create view command. This is again pretty simple
construct which is shown in the slide here which says create view and EMD which is the
name of the view and there are three fields EMPL, MGR and department. So this is the structure
of the view that is the name and the attribute list and this view is created as this query
that is whenever I need to compute this view, I just need to run this or execute this query.
This query is going to return a table which will populate the data that is required by
What are some of the properties of views? Because a view is stored as a query, a view
is always up to date. I don't need to modify a view, when I modify some data elements in
my table because there is no data element that is stored in a view. Hence characterization
of a view is not done during view definition time that is when I define a view I just leave
it like this. When I define a view like this, it is just left like that. It is not characterized,
it is not computed.
But this computation is done during the time of query that is when I give a query on a
view this characterization is done. Views can be deleted using the drop view command
just like we can delete a table. The efficient implementation of a view is a pretty tricky
problem and we shall be addressing this view maintenance as a separate session in itself
because how do we efficiently maintain a view and execute queries over.
So that brings us to the end of this this session. So let us briefly summarize or look
at the titles of all the different topics that we have studied. We looked into a SQL
two standards tables, attributes, values and constraints, entity constraints, foreign key
constraints and so on. We looked at several different kinds of select operations select
from where and disambiguation, aliasing, selecting from multiple tables, set operations, multi-set
operations, substring operations, arithmetic operations, existence checks and null checks
nested queries and aliasing and scope in nested queries and group by constructs and having
constructs and so on.
And then we also looked at SQL statements for insertion, deletion and updation of tuples
from database. Finally we looked at the notion of views or virtual tables or rather I should
say that we have just scratch the surface of views or virtual tables and saw how we
can specify a view in SQL using the create view command. So that brings us to the end
of this session. Thank you.