7.

The table

Scores(Team, Day, Opponent, Runs)

Gives the scores in the Japanese Baseball League for two consecutive days. The data in this table is as follows:

Team	Day	Opponent	Runs
Dragons	Sunday	Swallows	4
Tigers	Sunday	Bay Stars	9
Carp	Sunday	Giants	2
Swallows	Sunday	Dragons	7
Bay Stars	Sunday	Tigers	2
Giants	Sunday	Carp	4
Dragons	Monday	Carp	6
Tigers	Monday	Bay Stars	5
Carp	Monday	Dragons	3
Swallows	Monday	Giants	0
Bay Stars	Monday	Tigers	7
Giants	Monday	Swallows	5

Determine the result of the query

 SELECT Team, Day FROM Scores S1 WHERE NOT EXISTS (SELECT * FROM Scores S2 WHERE S1.Runs = S2.Runs AND (S1.Team <> S2.Team OR S1.Day <> S2.Day) ) 

Then, identify, in the list below, one of the rows of the result.

Hint: When trying to understand what a query does, it is often easiest to work "inside-out." That is, first understand the subquery. Notice that the subquery in this problem has "inputs" S1.Day, S1.Runs, and S1.Team that are determined outside the subquery and depend on which row of scores the alias variable S1 refers to.

a)

Bay Stars

Monday

b)

Dragons

Monday

c)

Swallows

Sunday

d)

Tigers

Monday

8.

The table

Scores(Team, Day, Opponent, Runs)

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. The data in this table is as follows:

Team	Day	Opponent	Runs
Dragons	Sunday	Swallows	4
Tigers	Sunday	Bay Stars	9
Carp	Sunday	NULL	NULL
Swallows	Sunday	Dragons	7
Bay Stars	Sunday	Tigers	2
Giants	Sunday	NULL	NULL
Dragons	Monday	Carp	NULL
Tigers	Monday	NULL	NULL
Carp	Monday	Dragons	NULL
Swallows	Monday	Giants	0
Bay Stars	Monday	NULL	NULL
Giants	Monday	Swallows	5

What is the result of the following query?

 SELECT Team, Min(Opponent), Max(Runs) FROM Scores GROUP BY Team 

Note: When a column has a string type, Min and Max refer to lexicographic (alphabetic) order of strings. That is, Min(Opponent) is the opponent that comes first in alphabetical order, and Max(Opponent) would be the one that comes last in alphabetical order.

Identify in the list below one of the tuples in the result.

a)

Carp

Dragons

NULL

b)

Swallows

Giants

0

c)

Giants

NULL

5

d)

Carp

Bay Stars

9

9.

Consider relation R(a) containing a set of positive integers with no duplicates, and the following recursive SQL query (do not be concerned that in the SQL definition, nonlinear recursions such as this one are not required to be implemented): WITH RECURSIVE Mystery(X,Y) AS (SELECT A AS X, A AS Y FROM R) UNION (SELECT m1.X, m2.Y FROM Mystery m1, Mystery m2 WHERE m2.X = m1.Y + 1) SELECT MAX(Y-X) + 1 FROM Mystery; While the definition looks complicated, Mystery in fact computes a property of R that can be stated very succinctly. Try to determine what Mystery is computing about R, and demonstrate your understanding by identifying, from the list below, the correct example of the result returned by the above query.

		a)	If R = {1, 5, 9, 10, 12, 15} then the query returns 2.
		b)	If R = { 2, 3, 4, 5, 10, 11, 12} then the query returns 3.
		c)	If R = {1, 5, 9, 10, 12, 15} then the query returns 6.
		d)	If R = {7, 9, 10, 14, 15, 16, 18} then the query returns 12.

10.

For what values of x, y, and z, including NULL, does the boolean expression x <= 3 AND NOT(y >= 1 OR z = 5) have the truth value UNKNOWN? Identify one of those values from the list below.

		a)	x = 3, y = 0, z = 7.
		b)	x = NULL, y = NULL, z = 5.
		c)	x = NULL, y = 0, z = 4.
		d)	x = 4, y = 1, z = NULL.

11.

The relation R(a,b) may have duplicate tuples. Which of the following queries has a result that is guaranteed not to have duplicates, regardless of what tuples R contains?

		a)	SELECT * FROM R WHERE a <> b
		b)	SELECT a, b FROM R
		c)	SELECT b, MAX(a) FROM R GROUP BY b
		d)	SELECT b FROM R WHERE b NOT IN (SELECT a FROM R)

12.

The table

Scores(Team, Day, Opponent, Runs)

Gives the scores in the Japanese Baseball League for two consecutive days. The data in this table is as follows:

Team	Day	Opponent	Runs
Dragons	Sunday	Swallows	4
Tigers	Sunday	Bay Stars	9
Carp	Sunday	Giants	2
Swallows	Sunday	Dragons	7
Bay Stars	Sunday	Tigers	2
Giants	Sunday	Carp	4
Dragons	Monday	Carp	6
Tigers	Monday	Bay Stars	5
Carp	Monday	Dragons	3
Swallows	Monday	Giants	0
Bay Stars	Monday	Tigers	7
Giants	Monday	Swallows	5

Determine the result of the query

 SELECT Team, Day FROM Scores S1 WHERE Runs <= ALL (SELECT Runs FROM Scores S2 WHERE S1.Day = S2.Day ) 

Then, identify, in the list below, one of the rows of the result. one of these queries.

Hint: When trying to understand what a query does, it is often easiest to work "inside-out." That is, first understand the subquery. Notice that the subquery in this problem has an "input," S1.Day, that is determined outside the subquery, and depends on which row of scores the alias variable S1 refers to.

a)

Giants

Sunday

b)

Giants

Monday

c)

Swallows

Monday

d)

Bay Stars

Monday