Database Quiz on “Recursive Queries and Aggregation Features”.
1. Any recursive view must be defined as the union of two subqueries: a _______ query that is nonrecursive and a __________ query.
a) Base, recursive
b) Recursive, Base
c) Base, Redundant
d) View, Base
Answer: a
Clarification: First compute the base query and add all the resultant tuples to the recursively defined view relation.
2. Ranking of queries is done by which of the following?
a) Group by
b) Order by
c) Having
d) Both Group by and Order by
Answer: b
Clarification: Order by clause arranges the values in ascending or descending order where a default is ascending order.
3. In rank() function if one value is shared by two tuples then
a) The rank order continues as counting numbers
b) The rank order continues by leaving one rank in the middle
c) The user specifies the order
d) The order does not change
Answer: b
Clarification: Example. rank() over (order by (GPA) desc).
4. The __________ function that does not create gaps in the ordering.
a) Intense_rank()
b) Continue_rank()
c) Default_rank()
d) Dense_rank()
Answer: d
Clarification: For dense_rank() the tuples with the second highest value all get rank 2, and tuples with the third highest value get rank 3, and so on.
5.
SELECT ID, GPA FROM student grades ORDER BY GPA ____________;
Inorder to give only 10 rank on the whole we should use
a) Limit 10
b) Upto 10
c) Only 10
d) Max 10
Answer: a
Clarification: However, the limit clause does not support partitioning, so we cannot get the top n within each partition without performing ranking; further, if more than one student gets the same GPA, it is possible that one is included in the top 10, while another is excluded.
6. If there are n tuples in the partition and the rank of the tuple is r, then its ________ is defined as (r −1)/(n−1).
a) Ntil()
b) Cum_rank
c) Percent_rank
d) rank()
Answer: c
Clarification: Percent rank of a tuple gives the rank of the tuple as a fraction.
7. Inorder to simplify the null value confusion in the rank function we can specify
a) Not Null
b) Nulls last
c) Nulls first
d) Either Nulls last or first
Answer: d
Clarification: select ID, rank () over (order by GPA desc nulls last) as s rank from student grades;.
8. Suppose we are given a view tot credits (year, num credits) giving the total number of credits taken by students in each year. The query that computes averages over the 3 preceding tuples in the specified sort order is
a)
SELECT YEAR, avg(num credits) OVER (ORDER BY YEAR ROWS 3 preceding) AS avg total credits FROM tot credits;
b)
SELECT YEAR, avg(num credits) OVER (ORDER BY YEAR ROWS 3 unbounded preceding) AS avg total credits FROM tot credits;
c) All of the mentioned
d) None of the mentioned
Answer: a
Clarification: Suppose that instead of going back a fixed number of tuples, we want the window to consist of all prior years we use rows unbounded preceding.
9. The functions which construct histograms and use buckets for ranking is
a) Rank()
b) Newtil()
c) Ntil()
d) None of the mentioned
Answer: c
Clarification: For each tuple, ntile(n) then gives the number of the bucket in which it is placed, with bucket numbers starting with 1.
10. The command ________________ such tables are available only within the transaction executing the query and are dropped when the transaction finishes.
a) Create table
b) Create temporary table
c) Create view
d) Create label view
Answer: b