INTERMEDIATE

SQL ‘-

Silberschatz, Chapter 4

1
Join Expressions

• Join operations take two relations and return as a result another relation.
• A join operation is a Cartesian product which requires that tuples in the two relations match (under some
condition). It also specifies the attributes that are present in the result of the join
• The join operations are typically used as subquery expressions in the‘-from clause
• Three types of joins:
• Natural join
• Inner join
• Outer join

2
Natural Join

• Unlike Cartesian product of two relations, which concatenates each tuple of the first relation with every
tuple of the second, Natural join matches tuples with the same values for all common attributes, and
retains only one copy of each common column.
• List the names of instructors along with the course ID of the courses ‘-
that they taught
• select name, course_id
from students, takes
where [Link] = [Link];
• The following query in SQL with “natural join” construct
• select name, course_id
from student natural join takes;

• Do you see any difference in the outcome?

3
Example

‘-

Student Relation
Takes Relation

4
 ‘-

student natural join takes

5
Natural Join in SQL

• The from clause can have multiple relations combined using natural join:
select A1, A2, … An
from r1 natural join r2 natural join .. natural join rn
where P ;
‘-
Or,
select A1, A2, … An
from E1, E2 … En
each Ei is a single relation or an expression involving natural joins.

6
Dangers of Natural Join
List the names of students along with the titles of courses that they have taken

select *
from student natural join takes, course
where takes.course_ID = course.course_ID; ‘- students national join takes

How about the following?

course
select name, course_id from student natural join takes natural join course

Put your understanding of the differences in the class Activity

sheet
The natural join would require the dept_name and course_id attributes from both the relations should be same in
addition to requiring the [Link] s also to be same. So, students taking courses outside their own dept.7 will be
omitted
Handling the Danger of Natural Join using construct Using

• Beware of unrelated attributes with same name which get equated incorrectly
• To avoid the danger of equating attributes erroneously, we can use the “using”
construct that allows us to specify exactly which columns should be equated.
‘-
• Query example
select name, title
from (student natural join takes) join course using (course_id)
Both relations must have attributes with the
Both these joins are similar except that join specified name (course_id in this case), which
does not need the common attributes to have needs to be equated. Even if there are some
the same value other shared attributes, they are not required to
be same.

8
Join Condition
▪ The on condition allows a general predicate over the relations being joined
▪ This predicate is written like a where clause predicate except for the use of the keyword on
▪ Query example:
• Query
select [Link]
* as ID, name, dept_name, tot_cred
from student join takes on student_ID = takes_ID
‘-
• The on condition above specifies that a tuple from student matches a tuple from takes if their ID
values are equal.
• Though this is similar to student natural join takes , do you see any changes?
- This is same as natural join, as natural join also required that for a student tuple and a takes tuple to
match, the ID column should be equal. Only difference is that the result has the ID attribute listed twice, in
the join result, once for student and once for takes, even though the values are same.
▪ Equivalent to:
select *[Link] as ID, name, dept_name, tot_cred
from student , takes
where student_ID = takes_ID

9
Outer Join

▪ Do you still observe any risk of missing information?

▪ An extension of the join operation that avoids loss of information.
▪ Computes the join and then adds tuples form one relation that does not match tuples in the other
relation to the result of the join. ‘-
▪ Uses null values.
▪ Three forms of outer join:
• left outer join
• right outer join
• full outer join

10
Outer Join
▪ Relation course

‘-
▪ Relation prereq

▪ Observe that
course information is missing CS-437
prereq information is missing CS-315

▪ 11
 • The operation woks in a manner similar to the join
operations we have already studied, but it
preserves those tuples that would be lost in a join
by creating tuples in the result containing null
values.
• left outer join-> preserves tuples only in the
relation named before ( to the left of) the left
‘-
outer join operation)
Outer Joins • right outer join-> preserves tuples only in the
relation named after ( to the right of) the right
outer join
• full outer join->: preserves tuples in both
directions

12
13

Outerjoin

• Suppose we join R ⋈ C S.

• A tuple of R that has no tuple of S with which it joins is said to be

dangling. ‘-
• Similarly for a tuple of S.
• Outerjoin preserves dangling tuples by padding them NULL.

13
14

Example: Outer Join

R= (A B) S= (B C)
1 2 2 3
4 5 6 7
‘-
(1,2) joins with (2,3), but the other two tuples
are dangling.
R OUTERJOIN S = A B C
1 2 3
4 5 NULL
NULL 6 7
14
Left Outer Join

• course natural left outer join prereq

‘-

▪ In relational algebra: course ⟕ prereq

Check for Class Activity! The differences among the following queries :
select * from course left outer join prereq on course.course_id=prereq.course_id;
select * from course left outer join prereq using (course_id);
select * from course natural left outer join prereq; 15
Right Outer Join

• course natural right outer join prereq

‘-

▪ In relational algebra: course ⟖ prereq

▪ Example:
▪ select * from student natural right outer join takes
▪ Class Activity! Check the difference with the followings:
▪ select * from course right outer join takes using (id);
▪ select * from course right outer join takes on [Link]=[Link];

16
Full Outer Join

• course natural full outer join prereq

‘-

• In relational algebra: course ⟗ prereq

17
 OUTER JOINS with WHERE

• On and where behave differently in

outer join, as outer join adds null
padded tuples only for those tuples
that DO NOT contribute the result of
the corresponding inner join ‘-
• You can retain or eliminate those
NULL entries by specifying
appropriate WHERE clause.

select * from student left outer join takes on [Link]=[Link]

select * from student left outer join takes on true where [Link]=[Link] 18
Joined Types and Conditions
• Join operations take two relations and return as a result another relation.
• These additional operations are typically used as subquery expressions in the from clause
• Join condition – defines which tuples in the two relations match.
• Join type – defines how tuples in each relation that do not match
‘- any tuple in the other relation (based
on the join condition) are treated.

19
Joined Relations – Examples
• course natural right outer join prereq

‘-

• course full outer join prereq using (course_id)

20
Joined Relations – Examples
• course inner join prereq on
course.course_id = prereq.course_id

‘-
• What is the difference between the above, and a natural join?
• course left outer join prereq on
course.course_id = prereq.course_id To distinguish normal joins from outer joins, normal
joins are called inner joins. Similarly natural join is
equivalent to natural inner join

21
Joined Relations – Examples
• course natural right outer join prereq

‘-

• course full outer join prereq using (course_id)

22
23

Example

• Display a list of students in CS dept along with the other course sections, that they
have taken in Spring 2017. All the course sections must be present even if nobody
has taken that section
‘-
• Select everything from student
• Select everything relevant from the takes
• Combine them using natural full outer join (because you do not want to miss any student
from CS dept, even if they have not taken any course and you also do not want to miss
any course section details present in takes, which no one from CS dept has taken.)

23
24

Example

• Display a list of students in CS dept along with the other course sections, that they
have taken in Spring 2017. All the course sections must be present even if nobody
has taken that section
‘- * from takes where
• select * from (select * from student natural full outer join (select
semester='Spring' and year=2017) Sp2017) allSp2017;

24
In Summary
‘-

25
Example: [Link]
• [Link]

• Associate movies with its language

• Combine customer table with payment ‘-

• Output the details of those staffs whose payment amount is atleast $6.

• Count the number of copies of a film in a given store (inventory). Sort them in
descending order.
• Find those film details for which there are no copies in inventory.

26
27

Aggregation Operators

• Aggregation operators are not operators of relational algebra.

• Rather, they apply to entire columns of a table and produce a single result.
• The most important examples: SUM, AVG, COUNT, MIN, and
‘- MAX.
• SUM, AVG, COUNT, MIN, and MAX can be applied to a column in a SELECT clause to
produce that aggregation on the column.
• Also, COUNT(*) counts the number of tuples.

27
Example: Aggregation Operators

R= (A B)
1 3
3 4 ‘-
3 2

SUM(A) = 7
COUNT(A) = 3
MAX(B) = 4
AVG(B) = 3

28
Class Activity

• Find the average salary of Finance dept.

• Find the total number of instructors who taught a course in Spring 2018
select count(distinct(id)) from teaches where semester='Spring' and
Year='2018'; ‘-

29
30

Grouping Operator

• R1 := γ L (R2). L is a list of elements that are either:

1. Individual (grouping ) attributes.
2. ‘-
AGG(A ), where AGG is one of the aggregation operators and A is an attribute.
- An arrow and a new attribute name renames the component.

30
 Unless specifically mentioned, the database may give
Aggregate some awkward name. Therefore, it is good to provide a
meaningful name

▪ Find the average salary of instructors in each department

• select dept_name, avg (salary) as avg_salary Attributes in select clause outside of
from instructor aggregate functions must appear in group
group by dept_name; by list ‘-
Without group by
clause, everything will
be treated as a single
group.

Can you try the

above query without
group by?

31
32

Example Contd.

Find the number of instructors in each dept who teach a course in Spring 2018.
• Select dept_name, count(distinct([Link])) as inst_count
• from instructor, teaches
‘-
• where [Link]=[Link] and semester='Spring' and year=2018
• Group by dept_name
Find the number of students in each dept who took a course in Spring 2018.
• Select dept_name, count(distinct([Link])) as stud_count
• from student, takes
• where [Link]=[Link] and semester='Spring' and year=2018
• Group by dept_name

32
Example with [Link]

• What is the average (min or max, sum) amount paid, As stored in the database?

‘- customer, order by their total

• Using GROUP BY: Find the average (total) rental amount paid by each
payments.

• Count the number of transactions each staff is processing and sort them in a list descending order of the
amount of total transaction amount.

• What is the distribution of different types of rental durations in film table?

• What is the average rental rate for each rating type films?

33
Example with [Link]

• How many tuples are stored in table Payments? How many distinct types of amounts are
stored in the database?

‘-

• Count the number of transactions made by the customer with ID 341

34
35

Grouping

• We may follow a SELECT-FROM-WHERE expression by GROUP BY and a list of

attributes.
• The relation that results from the SELECT-FROM-WHERE is grouped according to the
‘- only within each group.
values of all those attributes, and any aggregation is applied

35
36

More Examples: Grouping

• From Sells(bar, beer, price), find the average price for each beer:
SELECT beer, AVG(price)
FROM Sells ‘-
GROUP BY beer;

beer AVG(price)
Bud 2.33
… …

36
37

More Examples: Grouping

• From Sells(bar, beer, price) and Frequents(drinker, bar), find for
each drinker the average price of Bud at the bars they frequent:
Compute all
SELECT drinker, AVG(price) drinker-bar-
FROM Frequents, Sells price‘-triples
for Bud.
WHERE beer = ’Bud’ AND
Then group
[Link] = [Link] them by
GROUP BY drinker; drinker.

37
38

Restriction on SELECT Lists With Aggregation

• If any aggregation is used, then each element of the

SELECT list must be either:
1. Aggregated, or ‘-
2. An attribute on the GROUP BY list.

38
39

Illegal Query Example

You might think you could find the bar that sells Bud the cheapest
by:
SELECT bar, MIN(price)
FROM Sells ‘-

WHERE beer = ’Bud’;

But this query is illegal in SQL.

39
Having Clause

• Used in conjunction with the group by clause to shortlist group rows that satisfies certain
condition.
• This is similar to Where clause. However, Where clause sets the condition for individual
rows before GROUP BY, whereas HAVING appears after ‘-
• If so, the condition applies to each group, and groups not satisfying the condition are
eliminated.

40
Having Clause

▪ Find the names and average salaries of all departments whose average salary is greater than 42000
select dept_name, avg (salary) asvalues,
Null avg_salary
when exist, complicate the processing of
from instructor aggregate operator processing. In general,
group by dept_name ‘- ignore nulls. Count() returns 0,
aggregate functions
having avg (salary) > 42000; while others return null.
▪ Note: predicates in the having clause are applied after the formation of groups whereas predicates in
the where clause are applied before forming groups

41
42

Example

• For each course sectyiuon offered in 2017, find the average total credits of all students
enrolled in that section, if the section has atleast 2 students

‘-
Select course_id, semester, year, sec_id, avg(tot_cred) as avg_TotCredit
from student, takes
Where student. ID=[Link] and year 2017
group by course_id, semester, year, sec_id
having count(ID)>2

42
Example: [Link]

• Find the customers spending more than $100

‘-

• Find the average rental rate for those rating type films, for which rental rates are
higher than $3?

43
44

Example: HAVING

• From Sells(bar, beer, price) and Beers(name, manf), find the average price of those beers
that are either served in at least three bars or are manufactured by Pete’s.

‘-

44
45

Solution
Beer groups with at least
SELECT beer, AVG(price) 3 non-NULL bars and also
beer groups where the
FROM Sells manufacturer is Pete’s.

GROUP BY beer ‘-

HAVING COUNT(bar) >= 3 OR

beer IN (SELECT name Beers manu-
factured by
FROM Beers Pete’s.

WHERE manf = ’Pete’’s’);

45
46

Requirements on HAVING Conditions with a Subquery

• Anything goes in a subquery.
• Outside subqueries, they may refer to attributes only if they are
either:
1. A grouping attribute, or
‘-
2. Aggregated
(same condition as for SELECT clauses with aggregation).

46
47

With Clause

• Provides a way to define a temporary relation whose definition is available only to the
query in which the with clause occurs.
Find those departments with highest budget
‘-
• With max_budget (value) as (select max(budget) from department)
• select dept_name, budget from department, max_budget
• where [Link]=max_budget.value;

47
48

Example:

• Find all departments where the total salary is greater than the average of the total salary
at all departments.
• With dept_total(dept_name, value) as (select dept_name, sum(salary) from instructor
group by dept_name), ‘-
• dept_total_avg(value) as (select avg(value) from dept_total)
• select dept_name,dept_total.value
• from dept_total,dept_total_avg
• where dept_total.value>dept_total_avg.value;

48
49

Database Modifications

• A modification command does not return a result (as a query does), but changes the
database in some way.
• Three kinds of modifications:
‘-
1. Insert a tuple or tuples.
2. Delete a tuple or tuples.
3. Update the value(s) of an existing tuple or tuples.

49
50

Insertion

• To insert a single tuple:

INSERT INTO <relation>
VALUES ( <list of values> ); ‘-
• Example: add to Likes(drinker, beer) the fact that Sally likes Bud.
INSERT INTO Likes
VALUES(’Sally’, ’Bud’);

50
51

Specifying Attributes in INSERT

• We may add to the relation name a list of attributes.

• Two reasons to do so:
1. We forget the standard order of attributes for the relation.
2. We don’t have values for all attributes, and
‘- we want the system
to fill in missing components with NULL or a default value.

51
52

Example: Specifying Attributes

• Another way to add the fact that Sally likes Bud to Likes(drinker,
beer):

INSERT INTO Likes(beer, drinker) ‘-

VALUES(’Bud’, ’Sally’);

52
53

Adding Default Values

• In a CREATE TABLE statement, we can follow an attribute by DEFAULT and a value.

• When an inserted tuple has no value for that attribute, the default will be used.
‘-

53
54

Example: Default Values

CREATE TABLE Drinkers (
name CHAR(30) PRIMARY KEY,
addr CHAR(50)
‘-
DEFAULT ’123 Sesame St.’,
phone CHAR(16)
);

54
55

Example: Default Values

INSERT INTO Drinkers(name)

VALUES(’Sally’);
Resulting tuple: ‘-

name address phone

Sally 123 Sesame St NULL

55
56

Inserting Many Tuples

• We may insert the entire result of a query into a relation, using the form:
INSERT INTO <relation>
( <subquery> ); ‘-

56
57

Example: Insert a Subquery

• Using Frequents(drinker, bar), enter into the new relation PotBuddies(name) all of Sally’s
“potential buddies,” i.e., those drinkers who frequent at least one bar that Sally also
frequents.
‘-

57
58

Solution
The other Pairs of Drinker
drinker tuples where the
first is for Sally,
the second is for
someone else,
INSERT INTO PotBuddies ‘- and the bars are
the same.
(SELECT [Link]
FROM Frequents d1, Frequents d2
WHERE [Link] = ’Sally’ AND
[Link] <> ’Sally’ AND
[Link] = [Link]); 58
Insertion

▪ Add a new tuple to course

insert into course Values are specified in the order
in which the corresponding
values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
attributes are listed in the
‘- schema
▪ or equivalently
insert into course (course_id, title, dept_name, credits)
values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);

▪ Add a new tuple to student with tot_creds set to null

insert into student
values ('3003', 'Green', 'Finance', null);

59
60

Deletion

• To delete tuples satisfying a condition from some relation:

DELETE FROM <relation>
WHERE <condition>; ‘-

60
Modification of the Database

▪ delete from r
where P
Finds all tuples t in r so that P(t) is True, and delete them from r
If the where clause is omitted, all tuples in r are deleted.
▪
▪
It needs one delete operation for each relation
Delete all instructors
‘-
delete from instructor

▪ Delete all instructors from the Finance department

delete from instructor
where dept_name= 'Finance’;

▪ Delete all tuples (cannot delete only the values of specific attributes) in the instructor relation for those instructors associated
with a department located in the Watson building.
delete from instructor
where dept name in (select dept name First finds all departments in
from department Watson, and then deletes all
where building = 'Watson'); instructors pertaining to those
departments

61
Deletion

▪ Delete all instructors whose salary is less than the average salary of instructors
delete from instructor
where salary < (select avg (salary)
from instructor); ‘-
• Problem: as we delete tuples from deposit, the average salary changes
• Solution used in SQL:
1. First, compute avg (salary) and find all tuples to delete
2. Next, delete all tuples found above (without recomputing avg or retesting the tuples)

62
63

Example: Deletion

• Delete from Likes(drinker, beer) the fact that Sally likes Bud:
DELETE FROM Likes
WHERE drinker = ’Sally’ AND ‘-
beer = ’Bud’;

63
64

Example: Delete all Tuples

• Make the relation Likes empty:

DELETE FROM Likes; ‘-

• Note no WHERE clause needed.

64
65

Example: Delete Some Tuples

• Delete from Beers(name, manf) all beers for which there is another beer
by the same manufacturer.
DELETE FROM Beers b
Beers with the same
WHERE EXISTS ( manufacturer and
SELECT name FROM Beers ‘-
a different name
from the name of
WHERE manf = [Link] AND the beer represented
name <> [Link]); by tuple b.

65
66

Semantics of Deletion --- (1)

• Suppose Anheuser-Busch makes only Bud and Bud Lite.
• Suppose we come to the tuple b for Bud first.
• The subquery is nonempty, because of the Bud Lite tuple, so we
delete Bud. ‘-
• Now, when b is the tuple for Bud Lite, do we delete that tuple
too?

66
67

Semantics of Deletion --- (2)

• Answer: we do delete Bud Lite as well.

• The reason is that deletion proceeds in two stages:
1. Mark all tuples for which the WHERE condition is satisfied.
‘-
2. Delete the marked tuples.

67
68

Updates

• To change certain attributes in certain tuples of a relation:

UPDATE <relation>
SET <list of attribute assignments> ‘-
WHERE <condition on tuples>;

68
69

Example: Update

• Change drinker Fred’s phone number to 555-1212:

UPDATE Drinkers ‘-
SET phone = ’555-1212’
WHERE name = ’Fred’;

69
70

Example: Update Several Tuples

• Make $4 the maximum price for beer:

UPDATE Sells ‘-
SET price = 4.00
WHERE price > 4.00;

70
Update
▪ Make each student in the Music department who has earned more than 144 credit hours an instructor in
the Music department with a salary of $18,000.
insert into instructor
select ID, name, dept_name, 18000
from student ‘-
where dept_name = 'Music' and total_cred > 144;

▪ Insert tuple on the basis of the result of a [Link] select from where statement is evaluated fully
before any of its results are inserted into the relation.
Otherwise queries like
insert into table1 select * from table1
would cause problem

71
Updates

▪ Give a 5% salary raise to all instructors

update instructor
set salary = salary * 1.05
‘-
▪ Give a 5% salary raise to those instructors who earn less than 70000
update instructor
set salary = salary * 1.05
where salary < 70000;
▪ Give a 5% salary raise to instructors whose salary is less than average
update instructor
set salary = salary * 1.05
where salary < (select avg (salary)
from instructor);

72
Updates Contd.

▪ Increase salaries of instructors whose salary is over $100,000 by 3%, and all others by a 5%
• Write two update statements:
update instructor
set salary = salary * 1.03 ‘-
where salary > 100000;
update instructor
set salary = salary * 1.05
where salary <= 100000;
• The order is important
• Can be done better using the case statement (next slide)

73
Case statement for Conditional Updates

▪ Same query as before but with case statement

update instructor
set salary = case
when salary <= 100000 then salary * 1.05 ‘-
else salary * 1.03
end

74
Updates with Scalar subqueries

▪ Recompute and update tot_creds value for all students

update student S
set tot_cred = (select sum(credits)
from takes, course
where takes.course_id = course.course_id and ‘-
[Link]= [Link]
[Link] <> 'F' and
[Link] is not null);
▪ Sets tot_creds to null for students who have not taken any course
▪ Instead of sum(credits), use:
case
when sum(credits) is not null then sum(credits)
else 0
end

75
User-Defined Types

▪ create type construct in SQL creates user-defined type

▪ Two forms: distinct types, Structured data types

create type Dollars2 as (value numeric (12,2)) final (Specify FINAL if no further subtypes can be created for
this type.)
create type Pounds as numeric (12,2) final ‘-
(comparing the monetary value in terms of dollars and pounds are program error)
▪ Example:
create table department
(dept_name varchar (20),
building varchar (15),
budget Dollars);
([Link]+20) is not acceptable.
select dept_name, budget+20 from department3; produces error
We could do the addition on numeric type but to save the result back to an attribute of type dollars. Type conversion is required for
this.

76
Domain

▪ create domain construct in SQL-92 creates user-defined domain types

create domain person_name char(20) not null

▪
‘-
The domain person_name can be used as an attribute type, just as the type DDollarsTypes and domains are
similar.
▪ Domains can have constraints, such as not null, specified on them. Default values can also defined for the
variables of this domain type, where user defined types cannot have constraints or default values specified on
them.
▪ Domains are not strongly typed. As a result, values of one domain type can be assigned to values of another
domain type as long as the underlying types are compatible.
▪ Example: Permits the schema designer to specify a predicate that must
be satisfied by any attribute declared to be from the domain.
create domain degree_level varchar(10) degree_level_test gives a name to the constraint.
constraint degree_level_test
check (value in ('Bachelors', 'Masters', 'Doctorate'));

77
Index Creation

▪ Many queries reference only a small proportion of the records in a table. Therefore, It is inefficient for the
system to read every record to find a record with particular value
▪ An index on an attribute of a relation is a data structure that allows the database system to find those tuples in
the relation that have a specified value for that attribute efficiently, without scanning through all the tuples of
the relation. ‘-
▪ For example, is we create an index on attribute dept-name of relation instructor, the database system can find the record
with any specified dept-name value , such as ‘Physics’ , or ‘Music’, without reading all the tuples of the instructor relation.
▪ Part of physical schema of the database, as opposed to its logical schema. Not required for its correctness as
this is a redundant information
▪ We create an index with the create index command
create index <name> on <relation-name> (attribute-list);
- attribute-list is the list of the attributes of the relations that form the search key for the index.
create index dept_index on instructor (dept-name);
• drop index drops an index

78
Index Example

▪ create table student

(ID varchar (5),
name varchar (20) not null,
dept_name varchar (20),
tot_cred numeric (3,0) default 0, ‘-
primary key (ID))
▪ create index studentID_index on student(ID)
▪ The query:
select *
from student
where ID = '12345'
can be executed by using the index to find the required record, without looking at all records of student

79
Authorization

▪ We may assign a user several forms of authorizations on parts of the database.

• Read - allows reading, but not modification of data.
• Insert - allows insertion of new data, but not modification of existing
‘- data.
• Update - allows modification, but not deletion of data.
• Delete - allows deletion of data.
▪ Each of these types of authorizations is called a privilege. We may authorize the user all, none, or a
combination of these types of privileges on specified parts of a database, such as a relation or a view.

80
Authorization Contd.

▪ Forms of authorization to modify the database schema

• Index - allows creation and deletion of indices, (CONTROL ON INDEX).
• Resources - allows creation of new relations (GRANT RESOURCE TO Alex).
‘-
• Alteration - allows addition or deletion of attributes in a relation.
• Drop - allows deletion of relations.

81
Authorization Specification in SQL

▪ The grant statement is used to confer authorization

grant <privilege list> on <relation or view > to <user list>
▪ <user list> is: Allows granting of
several privileges in
• a user-id one command
‘-
• public, which allows all valid users the privilege granted
• Can also be a role (more on this later)
▪ Example:
• grant select on department to Amit, Satoshi
• Select authorization is required to read tuples on a relation. This allows those users to run queries on the
department relation.
▪ Granting a privilege on a view does not imply granting any privileges on the underlying relations.
▪ The grantor of the privilege must already hold the privilege on the specified item (or be the database
administrator).

82
Privileges in SQL

▪ select allows read access to relation, or the ability to query using the view
• Example: grant users U1, U2, and U3 select authorization on the instructor relation:
grant select on instructor to U1, U2, U3
▪ update: the ability to update using the SQL update statement. Can‘-be given on all attributes or some of
them.
grant update (budget) on department to Amit, Satoshi
▪ insert: the ability to insert tuples. Can be given on all attributes or some of them. Any insert must specify
only these attributes, and the system either gives each of the remaining attributes default values ( if
default values are defined for the attributes) or set as Null
▪ delete: the ability to delete tuples.
▪ all privileges: used as a short form for all the allowable privileges

83
Revoke in SQL

▪ The revoke statement is used to revoke authorization.

revoke <privilege list> on <relation or view> from <user list>
<privilege-list> may be all to revoke all privileges the revokee may hold.
▪ Example: ‘-
revoke select on student from U1, U2, U3
▪ If <revokee-list> includes public, all users lose the privilege except those granted it explicitly.
▪ If the same privilege was granted twice to the same user by different grantees, the user may retain the
privilege after the revocation.
▪ All privileges that depend on the privilege being revoked are also revoked.

84
Roles

▪ A role is a way to distinguish among various users as far as what these users can access/update in the
database.
▪ To create a role we use:
create a role <name>
▪ Example:
‘-
▪ Each instructor must be given the same types of authorizations on the same set of relations. Whenever a new instructor
is appointed, she will have to be given all the authorizations individually.
• create role instructor
▪ Once a role is created, we can assign “users” t o the role using:
• grant <role> to <users>
▪ Example:
• create role instructor;
• grant instructor to Amit;

85
Roles

▪ Privileges can be granted to roles:

• grant select on takes to instructor
▪ Roles can be granted to users, as well as to other roles
• create role teaching_assistant ‘-
• grant teaching_assistant to instructor;
▪ Instructor inherits all privileges of teaching_assistant
▪ Chain of roles
• create role dean;
• grant instructor to dean;
• grant dean to Satoshi;

86