C H A P T E R 5
A
d
v
a
n
c
e
d
S
Q
L
E
x
e
r
c
i
s
e
s
5.1 The following example shows how difficult it is to do some simple things in
SQL.
Suppose
you have a relation
t
r
a
n
s
a
c
t
i
o
n
(
a
cco
u
n
t
n
o
,
seqno,
a
m
o
u
n
t
)
where
5.2 Give a (small) example of a crosstab with summaries, and show how to repre-
sent the same example data, including summaries, as a normal relation.
. .
.2+ 2
5.3 Write a query in basic SQL equivalent to the following query in SQL with OLAP
extensions (which generates a “data cube”)
5.4
SQL
/
OLAP
a. Suppose I have a table
g
r
a
d
e
(
r
o
ll
n
o
,
c
o
u
r
s
e
,
e
x
a
m
,
m
a
r
k
s
)
giving the marks
of students in different exams. Assume the only exams are named quiz,
midsem and final. Assume each student has taken each exam for every
8 Chapter 5 Advanced SQL
with each cell containing the marks for that
course
/
student
for that exam.
Sort the output by rollno. You need not output summary results.
. .
.7
b. Extend the above query to output the crosstab with scaled marks, where the
5.5 Suppose you are given a relation
p
a
r
e
n
t
(
P
,
C), where P is the name of a parent
and C the name of a child of
P
. Assume that names are unique, that is, no two
people have the same name.
a. Write a recursive SQL query to compute all
descendants
of each person, and
record with each descendant the level of descent (1 for child, 2 for grand-
child, etc.). The program should define a view
d
e
s
c
e
n
d
a
n
t
(
A
,
D, L). Here,
A
5.6 a. Give table definitions of
r(A,
B) and s(A, B), with all attributes of type
in-
5.7 Given the relation person(name, sex, father, mother), with name as
the primary key, and sex taking on values ’M’ and ’F’, write recursive views in
5.8 Suppose you are given a relation
p
a
r
e
n
t
(
X
,
Y
) indicating that X is a parent
5.9 Suppose you are given a relation
co
nn
e
c
t
i
o
n
(
X
,
Y,
Dep,
Arr),
where a tuple in
the relation indicates that there is a train from X to
Y
, departing at time
D
e
p
Exercises
9
b. Modify the program to find cities reachable using a sequence of connections such
5.11 Explain why you need a references privilege to create a foreign key.
. .
.3
5.12 What are
authentication, authorization, non-repudiation,
and non-duplicatability and
why are they important in secure electronic transactions? How are these properties
achieved when paper cheques are used?
. .
.3+ 1
5.13 Give a trigger definition that will result in a infinite work.
. .
.3
5.14 Why is a
“references”
privilege required to declare an attribute of a relation as a
foreign key referencing another relation?
. .
.2
5.15 Suppose a relation
r
has an attribute user–id. Show how to use a before trig- ger on
5.16 Give a simple example of authorization using SQL statements, which involves
r
o
l
e
s
as well as the use of the
g
r
a
n
t
o
p
t
i
o
n.
. . .3
5.17 Give an example of an application requirement of authorization which cannot be
5.18 Suppose relations
r
and s have only inserts on them (no
deletes
/
updates).
Write