From an old Stanford Infolab paper Consider a relation R(A). (a) Using conventio

Question

From an old Stanford Infolab paper

Consider a relation R(A). (a) Using conventional SQL, write a query to compute the largest value in R, without using the "Max" operator. You may assume there are no duplicates in R. Your answer will be graded on simplicity as well as correctness. (b) Using conventional SQL, write a query to compute the median value in R. You may assume there are no duplicates in R, and R contains an odd number r of tuples. Note there is no "Median" operator in conventional SQL. Your answer will be graded on simplicity as well as correctness. (c) Now assume R may have duplicate values. Write a query to compute the "mode" of R i.e., the value (returned just once) that appears most often. If multiple values satisfy the criteria (for example, if two values appear four times each, and no values appear more than four times), then all of them should be returned. Note there is no "Mode" operator in conventional SQL. Your answer will be graded on simplicity as well as correctness.

Explanation / Answer

a) select top 1 result

MySQL:  SELECT column FROM R ORDER BY column_name DESC LIMIT 1

b) Median:

SELECT r1.median from R r1, R r2 GROUP BY r1.val HAVING SUM(SIGN(1-SIGN(r2.val-r1.val))) = (COUNT(*)+1)/2

c)Mode:

SELECT col, rept FROM (SELECT value, count(*) as rept FROM R GROUP BY col ) HAVING rept = max(rept).

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.