Hierarchical Retrieval Using CONNECT BY – Exercise

This exercise is worth a total of 16 points. Each question is worth eight points—five points for a correct query and three points for the accompanying answer. You will be graded on SQL syntax and on the proper use of required techniques, especially the CONNECT BY clause. Submit your completed answers when you are finished.

• Background | Overview
  This exercise uses the pet-store schema (our course project). It tests your ability to apply Oracle-specific operators and conditions and to write a hierarchical query.
• Download files:
  Download the PETSTORE schema files from the Resources page if you plan to install them in your own database.
• Instructions
  Submit two queries that meet the following requirements:
  
  1. Create a query that joins the CUSTOMER and CUSTOMER_SALE tables. In the WHERE clause, use at least two of the following operators:
     

         IS NULL
          IS NOT NULL
          BETWEEN param1 AND param2
          LIKE
          IN
          NOT IN
          +, - , *, / (arithmetic)
         

     
     
  2. Create a query that displays the packaged products and their sub-products.
• Hints
  1. The first query does not have to return any rows.
  2. The second query must be hierarchical.

Enter your queries in the text box below. Click the Submit button to send your answers. You must submit all responses for this exercise at the same time.