Answer Posted / santhosh kumar

On its face, the relational database management system would appear to be a very poor tool for representing and manipulating trees. This chapter is designed to accomplish the following things:
show you that a row in an SQL database can be thought of as an object
show you that a pointer from one object to another can be represented by storing an integer key in a regular database column
demonstrate the Oracle tree extensions (CONNECT BY … PRIOR)
show you how to work around the limitations of CONNECT BY with PL/SQL
The canonical example of trees in Oracle is the org chart.
create table corporate_slaves (
slave_id integer primary key,
supervisor_id references corporate_slaves,
name varchar(100)
);
insert into corporate_slaves values (1, NULL, ‘Big Boss Man’);
insert into corporate_slaves values (2, 1, ‘VP Marketing’);
insert into corporate_slaves values (3, 1, ‘VP Sales’);
insert into corporate_slaves values (4, 3, ‘Joe Sales Guy’);
insert into corporate_slaves values (5, 4, ‘Bill Sales Assistant’);
insert into corporate_slaves values (6, 1, ‘VP Engineering’);
insert into corporate_slaves values (7, 6, ‘Jane Nerd’);
insert into corporate_slaves values (8, 6, ‘Bob Nerd’);
SQL> column name format a20
SQL> select * from corporate_slaves;
SLAVE_ID SUPERVISOR_ID NAME
———- ————- ——————–
1 Big Boss Man
2 1 VP Marketing
3 1 VP Sales
4 3 Joe Sales Guy
6 1 VP Engineering
7 6 Jane Nerd
8 6 Bob Nerd
5 4 Bill Sales Assistant
8 rows selected.
The integers in the supervisor_id are actually pointers to other rows in the corporate_slaves table. Need to display an org chart? With only standard SQL available, you’d write a program in the client language (e.g., C, Lisp, Perl, or Tcl) to do the following:
query Oracle to find the employee where supervisor_id is null, call this $big_kahuna_id
query Oracle to find those employees whose supervisor_id = $big_kahuna_id
for each subordinate, query Oracle again to find their subordinates.
repeat until no subordinates found, then back up one level
With the Oracle CONNECT BY clause, you can get all the rows out at once:
select name, slave_id, supervisor_id
from corporate_slaves
connect by prior slave_id = supervisor_id;
NAME SLAVE_ID SUPERVISOR_ID
——————– ———- ————-
Big Boss Man 1
VP Marketing 2 1
VP Sales 3 1
Joe Sales Guy 4 3
Bill Sales Assistant 5 4
VP Engineering 6 1
Jane Nerd 7 6
Bob Nerd 8 6
VP Marketing 2 1
VP Sales 3 1
Joe Sales Guy 4 3
Bill Sales Assistant 5 4
Joe Sales Guy 4 3
Bill Sales Assistant 5 4
VP Engineering 6 1
Jane Nerd 7 6
Bob Nerd 8 6
Jane Nerd 7 6
Bob Nerd 8 6
Bill Sales Assistant 5 4
20 rows selected.
This seems a little strange. It looks as though Oracle has produced all possible trees and subtrees. Let’s add a START WITH clause:
select name, slave_id, supervisor_id
from corporate_slaves
connect by prior slave_id = supervisor_id
start with slave_id in (select slave_id
from corporate_slaves
where supervisor_id is null);
NAME SLAVE_ID SUPERVISOR_ID
——————– ———- ————-
Big Boss Man 1
VP Marketing 2 1
VP Sales 3 1
Joe Sales Guy 4 3
Bill Sales Assistant 5 4
VP Engineering 6 1
Jane Nerd 7 6
Bob Nerd 8 6
8 rows selected.
Notice that we’ve used a subquery in the START WITH clause to find out who is/are the big kahuna(s). For the rest of this example, we’ll just hard-code in the slave_id 1 for brevity.
Though these folks are in the correct order, it is kind of tough to tell from the preceding report who works for whom. Oracle provides a magic pseudo-column that is meaningful only when a query includes a CONNECT BY. The pseudo-column is level:
select name, slave_id, supervisor_id, level
from corporate_slaves
connect by prior slave_id = supervisor_id
start with slave_id = 1;
NAME SLAVE_ID SUPERVISOR_ID LEVEL
——————– ———- ————- ———-
Big Boss Man 1 1
VP Marketing 2 1 2
VP Sales 3 1 2
Joe Sales Guy 4 3 3
Bill Sales Assistant 5 4 4
VP Engineering 6 1 2
Jane Nerd 7 6 3
Bob Nerd 8 6 3
8 rows selected.
The level column can be used for indentation. Here we will use the concatenation operator (||) to add spaces in front of the name column:
column padded_name format a30
select
lpad(‘ ‘, (level – 1) * 2) || name as padded_name,
slave_id,
supervisor_id,
level
from corporate_slaves
connect by prior slave_id = supervisor_id
start with slave_id = 1;
PADDED_NAME SLAVE_ID SUPERVISOR_ID LEVEL
—————————— ———- ————- ———-
Big Boss Man 1 1
VP Marketing 2 1 2
VP Sales 3 1 2
Joe Sales Guy 4 3 3
Bill Sales Assistant 5 4 4
VP Engineering 6 1 2
Jane Nerd 7 6 3
Bob Nerd 8 6 3
8 rows selected.
If you want to limit your report, you can use standard WHERE clauses:
select
lpad(‘ ‘, (level – 1) * 2) || name as padded_name,
slave_id,
supervisor_id,
level
from corporate_slaves
where level <= 3
connect by prior slave_id = supervisor_id
start with slave_id = 1;
PADDED_NAME SLAVE_ID SUPERVISOR_ID LEVEL
—————————— ———- ————- ———-
Big Boss Man 1 1
VP Marketing 2 1 2
VP Sales 3 1 2
Joe Sales Guy 4 3 3
VP Engineering 6 1 2
Jane Nerd 7 6 3
Bob Nerd 8 6 3
7 rows selected.
Suppose that you want people at the same level to sort alphabetically. Sadly, the ORDER BY clause doesn’t work so great in conjunction with CONNECT BY:
select
lpad(‘ ‘, (level – 1) * 2) || name as padded_name,
slave_id,
supervisor_id,
level
from corporate_slaves
connect by prior slave_id = supervisor_id
start with slave_id = 1
order by level, name;
PADDED_NAME SLAVE_ID SUPERVISOR_ID LEVEL
—————————— ———- ————- ———-
Big Boss Man 1 1
VP Engineering 6 1 2
VP Marketing 2 1 2
VP Sales 3 1 2
Bob Nerd 8 6 3
Jane Nerd 7 6 3
Joe Sales Guy 4 3 3
Bill Sales Assistant 5 4 4
select
lpad(‘ ‘, (level – 1) * 2) || name as padded_name,
slave_id,
supervisor_id,
level
from corporate_slaves
connect by prior slave_id = supervisor_id
start with slave_id = 1
order by name;
PADDED_NAME SLAVE_ID SUPERVISOR_ID LEVEL
—————————— ———- ————- ———-
Big Boss Man 1 1
Bill Sales Assistant 5 4 4
Bob Nerd 8 6 3
Jane Nerd 7 6 3
Joe Sales Guy 4 3 3
VP Engineering 6 1 2
VP Marketing 2 1 2
VP Sales 3 1 2
SQL is a set-oriented language. In the result of a CONNECT BY query, it is precisely the order that has value. Thus it doesn’t make much sense to also have an ORDER BY clause.

Which data type cannot be used as a parameter in a udf?

978

---

1. Using the XML Document below, with the URI “recipe.xml” define the following queries in XQuery: a.) Give the names of all breakfast in the menu. b.) Select breakfasts that have price lower than $7.00? Belgian Waffles $5.95 two of our famous Belgian Waffles with plenty of real maple syrup 650 Strawberry Belgian Waffles $7.95 light Belgian waffles covered with strawberries and whipped cream 900 Berry-Berry Belgian Waffles $8.95 light Belgian waffles covered with an assortment of fresh berries and whipped cream 900 French Toast $4.50 thick slices made from our homemade sourdough bread 600 Homestyle Breakfast $6.95 two eggs, bacon or sausage, toast, and our ever-popular hash browns 950

2144

---

Who are naive users?

1001

---

explain detail about ftp in datastage?

2245

---

Hi my program is accesing data from two db2 databases.what is the bind card for this program to genarate plan? how to specify the two owners and two qualifiers in bind card

2168

---

How would you design a database for an online site, which would average a million hits a day?

1013

---

How to use timestamp datatypes

2040

---

What are the 18 schemas?

1121

---

Explain what a database is?

1049

---

How long can an error message be in raiseerror function?

1003

---

What are the three types of database design?

1025

---

Which four data types cannot be used as a return type from a user-defined function?

1025

---

fact table and dimension table containg one to many relationship or many to one relastionship

2497

---

Explain the role of indexing in databases?

1134

---

Explain about the database management systems.

972

---