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.

explain detail about ftp in datastage?

2288

---

How can you log an error to the server event log from a stored procedure?

1170

---

What is the physical representation for a many-to-many relationship?

1133

---

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

2182

---

What is normalization? Explain different levels of normalization?

63395

---

Can an extended stored procedure be called from inside a user-defined function?

1110

---

You are testing the performance of a query the first time you run the query, the performance is slow. the second time you run the query, the performance is fast. why is this?

1016

---

Explain about relational database management systems?

1012

---

How to replace not in with not exist?

2172

---

How to combine two function together?

2200

---

You have a table with three columns: amount1, amount2, and amount3 in a single row only one of these three columns will have a value and the other two will be null. Write a sql query to retrieve the values from these columns?

1020

---

What is the database development process?

1265

---

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

6630

---

What you can do to remove data from the cache and query plans from memory for testing the performance of a query repeatedly?

1071

---

Who are end users in dbms?

1033

---