Question
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
| 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:
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) |
| ||||
| b) |
| ||||
| c) |
| ||||
| d) |
| ||||
| 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:
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) |
| |||||
| b) |
| |||||
| c) |
| |||||
| d) |
| |||||
| 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:
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) |
| ||||
| b) |
| ||||
| c) |
| ||||
| d) |
|
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Intelligent Databases Technologies And Applications
Authors: Zongmin Ma
1st Edition
1599041219, 978-1599041216
