1. 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.

2. 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.
```
3. 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.

4. 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.