Write the question for the following query and answer.
SELECT COUNT(DECODE(SIGN(total_capacity-20),
-1, 1, 0, 1)) "<=20",
COUNT(DECODE(SIGN(total_capacity-21),
0, 1, -1, NULL,
DECODE(SIGN(total_capacity-30), -1, 1)))
"21-30",
COUNT(DECODE(SIGN(total_capacity-30), 1, 1))
"31+"
FROM (SELECT SUM(capacity) total_capacity, course_no
FROM section
GROUP BY course_no)
<=20 21-30 31+
--------- --------- ---------
2 10 16
1 row selected.
Solution:
The question should be similar to one of the following: Determine the total capacity for each course and order them in three columns. List the number of courses with a total capacity of 20 or less in one column, the number of courses with a total capacity between 21 and 30 in another, and lastly show the number of courses with a capacity of over 31 in the third column. The result shows that there are two courses with a total capacity of 20 or less, 10 courses with a capacity between 21 and 30, and 16 courses with a capacity over 31 students.
Determine the top three zip codes where most of the students live. Use an analytical function.
Solution:
SELECT *
FROM (SELECT zip, COUNT(*),
DENSE_RANK() OVER(ORDER BY
COUNT(zip) DESC) AS rank
FROM student
GROUP BY zip)
WHERE rank <=3
ZIP COUNT(*) RANK
----- ---------- ----------
07024 9 1
07010 6 2
11373 6 2
11368 6 2
07042 5 3
11355 5 3
11209 5 3
07047 5 3
11375 5 3
11372 5 3
10 rows selected.
Explain the result of the following query.
SELECT 'Q'||TO_CHAR(start_date_time, 'Q') qtr,
TO_CHAR(start_date_time, 'DY') day, COUNT(*),
DENSE_RANK() OVER (
PARTITION BY 'Q'||TO_CHAR(start_date_time, 'Q')
ORDER BY COUNT(*) DESC) rank_qtr,
DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) rank_all
FROM enrollment e, section s
WHERE s.section_id = e.section_id
GROUP BY 'Q'||TO_CHAR(start_date_time, 'Q'),
TO_CHAR(start_date_time, 'DY')
ORDER BY 1
QT DAY COUNT(*) RANK_QTR RANK_ALL
-- --- ---------- ---------- ----------
Q2 FRI 42 1 1
Q2 SAT 36 2 2
Q2 WED 30 3 3
Q2 THU 28 4 5
Q2 SUN 15 5 7
Q2 MON 13 6 8
Q2 TUE 13 6 8
Q3 WED 29 1 4
Q3 SAT 20 2 6
9 rows selected.
Solution:
The query generates a listing that shows the starting quarter of any sections and within the respective quarter the number of enrollments. The RANK_QTR column indicates ranking of the enrollment number of each quarter and the RANK_ALL column shows the ranking for all time periods.
Name other hierarchical relationships you are familiar with.
Solution:
Examples of hierarchical relationships are a parts explosion, also referred to as bill of materials, where you show all the parts that go into the assembly of a final product. Another example is the hierarchy of an organization, showing all the employees and their respective managers. A financial profit and loss statement report can be another example of a tree, where summary accounts are made up of other summary accounts that finally result in posting-level accounts.