1.

Suppose relations R(A,B) and S(B,C,D) are as follows:

R =

A	B
1	2
3	4
5	6

S =

B	C	D
4	5	1
6	7	2
8	9	3

Compute the full outer natural join on B, the left outer natural join on B, and the right outer natural join on B. In each case, R is the left operand and S is the right operand. Then, answer the following questions for each of the three results:

How many rows are there in the result?

How many NULL's appear in the result.

Finally, find the correct statement in the list below.

		a)	The left outer natural join has 5 rows.
		b)	The right outer natural join has 3 NULL's.
		c)	The full outer natural join has 4 rows.
		d)	The right outer natural join has 2 NULL's.

2.

Suppose relation R(a,b,c) has the following tuples: (1,1,3), (1,2,3), (2,1,4), (2,3,5), (2,4,1), (3,2,4), and (3,3,6). Define the view V by: CREATE VIEW V AS SELECT a+b AS d, c FROM R; What is the result of the query: SELECT d, SUM(c) FROM V GROUP BY d HAVING COUNT(*) <> 1; Identify, from the list below, a tuple in the result.

		a)	(5,11)			b)	(3,7)			c)	(3,12)			d)	(6,9)

3.

The latest scores from the Japanese Baseball League are in the table with schema

Scores(Team, Opponent, RunsFor, RunsAgainst)

The data in this table is as follows:

Team	Opponent	RunsFor	RunsAgainst
Dragons	Tigers	5	3
Carp	Swallows	4	6
Bay Stars	Giants	2	1
Marines	Hawks	5	3
Ham Fighters	Buffaloes	1	6
Lions	Golden Eagles	8	12
Tigers	Dragons	3	5
Swallows	Carp	6	4
Giants	Bay Stars	1	2
Hawks	Marines	3	5
Buffaloes	Ham Fighters	6	1
Golden Eagles	Lions	12	8

What is the result of executing on this data the query:

 SELECT S1.Team, S2.Team FROM Scores S1, Scores S2 WHERE S1.Team < S2.Team AND (S1.RunsFor = S2.RunsFor OR S1.RunsAgainst = S2.RunsAgainst) 

Remember that when strings are compared, "<" means "precedes in alphabetical order.

Identify, in the list below, a row of the result.

a)

Ham Fighters

Carp

b)

Giants

Ham Fighters

c)

Buffaloes

Bay Stars

d)

Giants

Hawks

4.

Suppose relations R(A,B) and S(B,C,D) are as follows:

R =

A	B
1	2
3	4
5	6

S =

B	C	D
2	4	6
4	6	8
4	7	9

Compute the result of the following query:

 SELECT R.A, R.B, S.B, S.C, S.D FROM R OUTER JOIN S ON (R.A > S.B AND R.B = S.C) 

Which of the following tuples of R or S is dangling (and therefore needs to be padded in the outer join)?

		a)	There are no dangling tuples.
		b)	(4,6,8) of S.
		c)	(5,6) of R.
		d)	(1,2) of R.

5.

The relation Parent(par,ch) contains parent-child facts. At the moment, the set of pairs in Parent is {(10, 9), (10,8), (9,7), (9,6), (8,6), (8,5), (7,4), (7,3), (6,3), (6,2), (5,2), (5,1)}. The following WITH-statement defines the Cousins relation: WITH RECURSIVE Cousins(X,Y) AS ((SELECT p1.ch, p2,ch FROM Parent p1, Parent p2 WHERE p1.par = p2.par AND p1.ch < p2.ch) UNION (SELECT p3.ch, p4.ch FROM Cousins, Parent p3, Parent p4 WHERE Cousins.X = p3.par AND Cousins.Y = p4.par AND p3.ch < p4.ch)) Simulate the computation of the Cousins relation, as defined by this recursion. If we think of the basis (first term of the UNION) as "round 1" and each subsequent round of the recursion as "round 2," "round 3," and so on, which of the following is a correct assertion about the round on which some pair first gets added to Cousins?

		a)	(1,4) is added on round three.
		b)	(1,3) is added on round three.
		c)	(10,10) is added at round one.
		d)	(6,7) is added on round two.

6.

Suppose we have a relation with schema R(A, B, C, D, E) If we issue a query of the form SELECT ... FROM R WHERE ... GROUP BY B, E HAVING ??? What terms can appear in the HAVING condition (represented by ??? in the above query)? Identify, in the list below, the term that CAN NOT appear.

a)

SUM(D)

b)

B+E

c)

E

d)

D