Oracle 11g Release 1 (11.1) SQL _ hierarchical query (details)

Content

• HR Mode
• Oracle sample Schema
• Install HR Mode
• Demo level Query
• Hierarchical Query
• Reference

HR mode Oracle sample Schema

Oracle has several examplesSchema, Including,HR (Human Resources),OE (Order Entry),OC (Online Catalog),PM (Product Media),IX (Information Exchange)AndSH (Sales History). These schemas have dependencies. Together is a complete database. For more information, see http://docs.oracle.com/cd/b28359_01/server.111/b28328/toc.htm#begin.

You can install these schemas as follows:

• Use Database Configuration Assistant)
• Manual Installation
• Use the mksample. SQL script to reset the instance to its initial state.

Install HR Mode

This article demonstrates how to useHRMode. See examplesHRMode script, andHRObjects and tables in the schema. Manual InstallationHRMode. For more information, see.

 

Demo level Query

Example 1: DemoCONNECT. UseCONNECTTo define the relationship between employees and managers.

SELECT employee_id, last_name, manager_id

   FROM employees

   CONNECT BY PRIOR employee_id = manager_id;

 

(Not all results .)

Example 2: DemoLEVEL. This example is the same as Example 1. Just usedLEVELPseudo column.

SELECT employee_id, last_name, manager_id, LEVEL

   FROM employees

   CONNECT BY PRIOR employee_id = manager_id;

(Not all results .)

Example 3: DemoSTART. UseSTARTClause to specify the level of root data rows and useORDER SIBLINGSMaintain the order in the hierarchy.

Not all results. InHr. employeesTable, employee Steven King is the highest position in the company. In the preceding result:

1 2 3 Next Page