SQL Queries and Relational Calculus: A Comprehensive Guide
(i) If A is an attribute of type integer in a table, then A*0 always evaluates
to 0 in SQL.
False (think of nulls)
(ii) Aliases (tuple variables) in SQL do not increase the power of the lan-
guage and are provided just for convenience. False
(iii) In SQL, all nested queries without correlated subqueries can be unnested. False
(iv) Nested queries in the from clause of SQL queries are provided just for
convenience and can always be eliminated. True
(v) SQL can express some queries that relational calculus cannot express. True
(vi) Universal quantification is redundant in relational calculus (it can be
simulated by the other operators). True
(vii) In SQL, the exists clause can be simulated with the count function. True
(viii) Employee is a relation with attributes name, salary. The SQL query
select name from employee where salary = salary
always produces the same answer as the query select name from employee: False (null salaries)
(ix) Employee is a relation with attributes name, salary. The SQL query
select name from employee where salary = salary OR 0 = 0
always produces the same answer as the query select name from employee: True(x)
R is a relation with a single attribute A. The query select A from R group by A
always produces the same answer as the query select A from R. False (group by eliminates duplicates)
Problem 2 (3 points) (Gradiance practice homework) Consider the relations
R,S and T below:
| R | A | B | S | C | D | T | E | F | Sol | A | F | S(C) | S(D) |
| 0 | 1 | 0 | 1 | 0 | 1 | 0 | 1 | 2 | 1 | ||||
| 1 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 1 | 2 | ||||
| 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 |
Compute the result of the query: SELECT A, F, SUM(C), SUM(D) FROM R, S, T
WHERE B = C AND D = E GROUP BY A, F HAVING COUNT(*) > 1
Problem 3 (3 points) Consider the movie database with relations
schedule|theater title, movie|title director actor
Write an SQL query without joins whose result is schedule left outer join movie
This essentially shows that join is syntactic sugar in SQL. Select * from schedule, movie
where schedule.Title = movie.Title union select theater, title, null as director, null as actor
from schedule where title not in (select title from movie)
Problem 4 (8 points) Consider a bank database with these relations:
Branch| branch–
Id city manager, Account| account–
Id branch-id customer–
Id balance
Customer|customer-id name city. The following constraints hold:
branch-id is the primary key of Branch. Account-id is the primary key of Account
customer-id is the primary key of Customer. Branch-id is a foreign key of Account referencing Branch
customer-id is a foreign key of Account referencing Customer. All attributes are non null
Consider the query:
Find the ids of customers having accounts only in the city where they live.
Note that customers who have no accounts must be included in the an-
swer. For this query, do the following (write your answers on the next page):
(i) determine whether the query is monotonic (explain). The query is not monotonic. If a given customer
is in the answer and we add an account for that customer in a branch located in a city where he/she doesnt live, the customer will no longer be in the answer.
(ii) express the query in tuple calculus, using 3 and V; {n : c-id | 3c E Cust(n(c-id) = c(c-id) A Va E Acc Vb E Branch [
(c(c-id) = a(c-id) A a(b-id) = b(b-id)) -> c(city) = b(city)]}
(iii) rewrite the query in (ii) using only 3. {n : c-id | 3c E Cust(n(c-id) = c(c-id) A Va E Acc Vb E Branch [
(c(c-id) = a(c-id) A a(b-id) = b(b-id)) -> c(city) 6= b(city)]}
(iv) write the SQL query corresponding directly to the tuple calculus query
in (iii), using not exists tests on nested queries.
SELECT c.Customer-id FROM Customer c WHERE NOT EXISTS (SELECT * FROM Account a, Branch b
WHERE c.Customer-id = a.Customer-id AND a.Branch-id = b.Branch-id AND c(city) <> b(city))
Problem 5 (4 points) Consider again the database in Problem 4 and the query
Find the managers of the richest branches
The richest branches are those with the largest total amount of money in
all accounts held at that branch. The query should return a relation with
a single attribute named manager. Write the above query in SQL. Please
discuss how your query handles the case when the Accounts relation is empty.
WITH Assets AS SELECT branch-id, SUM(balance) AS total FROM Account GROUP BY branch-id
SELECT manager FROM Branch, Assets WHERE Branch.Branch-id = Assets.Branch-id AND total IN
(SELECT Max(total) FROM Assets)
If Accounts is empty, the query can be given two reasonable interpretations:
(i) since there are no accounts, there is no richest branch, so there are no
managers of the richest branches. The answer is then empty, which is
what the above query produces.
(ii) since there are no accounts, the assets of all branches are zero, so all
branchest are the richest and all managers should be in the answer. A
query that conforms to this interpretation is obtained form the above
by taking its union with the query SELECT manager FROM Branch
WHERE NOT EXISTS (SELECT * FROM Account)
Problem 6 (2 points) (Gradiance practice homework) The table below gives
the scores in the Japanese Baseball League for two consecutive days. The
Opponent is NULL if the Team did not play on that day. The number of
Runs is given as NULL if either the team did not play, or will play on that
day but the game is not yet concluded.
Scores Team Day Opponent Runs
Dragons Sunday Swallows 4, Tigers Sunday BayStars 9, Carp Sunday NULL NULL
Swallows Sunday Dragons 7, BayStars Sunday Tigers 2, Giants Sunday NULL NULL
Dragons Monday Carp NULL, Tigers Monday NULL NULL, Carp Monday Dragons NULL
Swallows Monday Giants 0, BayStars Monday NULL NULL, Giants Monday Swallows 5
What is the result of executing on this data the query:
SELECT Team, Day FROM Scores WHERE Opponent IS NULL OR NOT (Runs >= 0)
Solution:The first condition in the WHERE clause asks for all rows where the
Opponent column is NULL (rows 3, 6, 8, and 11) or the Runs column is
negative. Even though we know that it is impossible to score a negative
number of runs, the truth value of Runs >=0 is unknown if Runs is
NULL. Thus, the truth value of NOT Runs >=0 is also unknown, and
the second condition of the WHERE clause is not true for any of the rows.
Thus, only the four rows with NULL in the Opponent column meet the
condition. The resulting output is:
Team Day, Carp Sunday, Giants Sunday, Tigers Monday, Bay Stars Monday
Problem 7 (5 points) Consider a query whose input is a relation T with
attributes A,B (of type char), in which tuples represent the edges of a
directed tree, and whose output is a relation Least-Common-Ancestor with
attributes E, F,G such that (e, f, g) belongs to the answer iff e and f are
distinct nodes of the tree, e and f are not descendants or ancestors of each
other, and g is their closest common ancestor. For example, on input
T A B, r a, r f, a b, a c, b d
representing the tree
r
a. B, d, c, f
the answer to the query is
Least-Common-Ancestor E F G
a f r, b f r, b c a, d c a, d f r, c f r
(repetitions such as (a, f, r) and (f, a, r), are omitted). Write an SQL state-
ment using a with recursive clause that computes the Least-Common-
Ancestor query (without repetitions (x, y, z) and (y, x, z)). Write your answer
on the next page. Hint: the with recursive clause should define a tem-
porary table providing descendants of each node, which can then be used to
find the least common ancestors.
In this problem the recursion is very easy (the descendant relation D
is just the transitive closure of T) but the way D is used is trickier (see
comments in the SQL).
WITH RECURSIVE D(A,B) as SELECT * FROM T UNION
SELECT D.A, T.B FROM D, T WHERE D.B = T.A % end of the recursive definition of D
select x.B as E, y.B as F, x.A as G % x.A = y.A is a common ancestor
from D x, D y % of x.B and y.B where x.A = y.A and x.B < y.B=””>
NOT EXISTS % there is no common ancestor (select * from D u, D v, D w % u.A = v.A
where u.A = v.A and and u.B = x.B and v.B = y.B % of x.B and y.B which is also
and w.A = x.A and w.B = u.A) % a descendant of x.A = y.A