Select Statement  «Prev  Next»

Hierarchical Retrieval Using Connect By- Exercise

Course Project: Write two queries

Objective: Write two queries using operators, conditions, and hierarchies.

Exercise Scoring

This exercise is worth a total of 16 points. Each question is worth eight points; five points for the correct query and three points for the answer to the question. You will be graded on the syntax of your SQL and on the use of certain important techniques in your SQL, such as the CONNECT BY clause. Once you have completed your answer you will submit it.

Background | Overview

This exercise works with the pet store schema (our course project) and tests your new skills in using Oracle exclusive operators and conditions. It also assesses your ability to create a hierarchical query.
Download files
Download the PETSTORE schema files from the Resources page if you plan to install them into your own database.

Instructions

Submit two queries according to these directions:
1. Create a query that joins the CUSTOMER and the CUSTOMER_SALE tables. Use at least two of the following operators in the WHERE clause:
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 is a hierarchical query.

Submitting your Exercise

Enter your queries into the text box below. Click the Submit button to submit your answers. Remember that you must submit all your responses to this exercise at once.