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.

Select Another Chapter

  1. SQL and Data
  2. SQL: The Basics
  3. Character and Number Functions
  4. Date, Conversion, and Miscellaneous Functions
  5. Aggregate Functions, GROUP BY, and HAVING
  6. Equijoins
  7. Subqueries
  8. Set Operators
  9. Complex Joins
  10. Insert, Update, and Delete
  11. Create, Alter, and Drop Tables
  12. Views, Indexes, and Sequences
  13. The Data Dictionary and Dynamic SQL Scripts
  14. Security
  15. Advanced SQL Queries
  16. SQL Optimization