Answer / 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.

Explain about post-relational databases?

0 Answers  

---

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

0 Answers  

---

Write the fastest query to find out how many rows exist in a table?

0 Answers  

---

hi in which phase of mload can i use .FILLER

1 Answers   IBM,

---

Explain the general syntax for a SELECT statements covering all the options.

3 Answers  

---

---

Explain the role of indexing in databases?

0 Answers  

---

What is an extended stored procedure?

1 Answers  

---

Explain about xml databases?

0 Answers  

---

How to use timestamp datatypes

0 Answers  

---

Explain the family trees and connection by clause.

1 Answers  

---

how to find the first two highest salaries in deptno in emp table

2 Answers  

---

A) Which two are benefits of Teradata's support for ANSI Standard SQL? (Choose two.) 1.data is distributed automatically 2.queries get optimized to better plans 3.submit queries from other database systems 4.can interface with BI tools B) Which statement is true when comparing the advantages of third normal form to star schema? 1.Star schema tends to have fewer entities. 2.Star schema requires additional data storage. 3.Third normal form tends to have fewer entities. 4.Third normal form requires additional data storage. C)Which two sets of functions does the Parsing Engine (PE) perform? (Select two.) 1.sorting, formatting, and aggregating of the final answer set 2.flow control of the data to and from the participating tables 3.SQL statement interpretation, syntax validation, and semantic evaluation 4.dispatching the step execution sequence to the AMP via the BYNET D) Which two can be achieved with Teradata Active System Management (TASM)? (Choose two.) 1.disable hardware 2.react to hardware failure 3.influence response times 4.collect metadata E) Which three mechanisms can be used to ensure security within the Teradata Database? (Choose three.) 1.views 2.spool limits 3.roles 4.access rights 5.profiles

0 Answers  

---