Skip over navigation to the main content.

Oracle SQL Interactive Workbook, 2nd Edition

Solutions Chapter 15: Advanced SQL Queries Test Your Thinking

  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.

Further Reading

Read Prentice Hall PTR to stay on the cutting edge of computer science and engineering.