Database Questions and Answers – Recursive Queries and Aggregation Features

This set of Database Quiz focuses 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
View Answer

Answer: a
Explanation: 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
View Answer

Answer: b
Explanation: 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
View Answer

Answer: b
Explanation: Example. rank() over (order by (GPA) desc).
advertisement
advertisement

4. The __________ function that does not create gaps in the ordering.
a) Intense_rank()
b) Continue_rank()
c) Default_rank()
d) Dense_rank()
View Answer

Answer: d
Explanation: 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.

Note: Join free Sanfoundry classes at Telegram or Youtube
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
View Answer

Answer: a
Explanation: 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.
advertisement

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()
View Answer

Answer: c
Explanation: 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
View Answer

Answer: d
Explanation: select ID, rank () over (order by GPA desc nulls last) as s rank from student grades;.
advertisement

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
View Answer

Answer: a
Explanation: 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
View Answer

Answer: c
Explanation: 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
View Answer

Answer: b
Explanation: None.

Sanfoundry Global Education & Learning Series – Database Management System.

If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.