Answer / ganesh

A join joins with itself is called self join
Working with self joins we use Alias tables

Answer / dhivya

lets take a table A which is joined to itself with its
alias is said to be self join.Most familiar example is
finding the manager of the employee..

select e1.emp_id manager_id,e2.emp_id employee_id from
employee e1,employee e2
where e1.emp_id=e2.emp_id

Answer / mangi

answer 1 is wrong

Answer / sanchit aggarwal(oracle dev.)

CREATE TABLE EMPLOYEE(

[EMPLOYEE_ID] INT PRIMARY KEY,

[NAME] NVARCHAR(50),

[MANAGER_ID] INT

)

GO

INSERT INTO EMPLOYEE VALUES(101,'Mary',102)

INSERT INTO EMPLOYEE VALUES(102,'Ravi',NULL)

INSERT INTO EMPLOYEE VALUES(103,'Raj',102)

INSERT INTO EMPLOYEE VALUES(104,'Pete',103)

INSERT INTO EMPLOYEE VALUES(105,'Prasad',103)

INSERT INTO EMPLOYEE VALUES(106,'Ben',103)


select e1.name from employee e1,employee e2
where e1.employee_id = e2.manager_id

Answer / lalithg

CREATE TABLE candytest
(kidId char(2),
candycolor varchar(10)
)
GO

INSERT INTO candytest SELECT 'K1', 'Yellow'
INSERT INTO candytest SELECT 'K1', 'Red'
INSERT INTO candytest SELECT 'K2', 'Red'
INSERT INTO candytest SELECT 'K2', 'Blue'
INSERT INTO candytest SELECT 'K3', 'White'
INSERT INTO candytest SELECT 'K3', 'Red'
INSERT INTO candytest SELECT 'K3', 'Yellow'
Go
--Now to find those kidid's which has both yellow and red
colored candies, we can write query as:

SELECT c1.kidid
FROM candytest AS c1 JOIN candytest AS c2
ON c1.candycolor = 'Red' AND c2.candycolor = 'Yellow'
AND c1.kidid = c2.kidid

Answer / madhavi

Joining the table to itself is self join.
Example:

To find the manager name for each employee in the employee
table:
select e1.empno employee_id,e1.ename employee_name,e2.ename
manager_name,e2.empno employee_num from emp e1,emp e2 where
e1.empno=e2.mgr;

Answer / krishna prasad a

For this Answer 1 is incorrect.

CREATE TABLE candytest
(kidId char(2),
candycolor varchar(10)
)
GO

INSERT INTO candytest SELECT 'K1', 'Yellow'
INSERT INTO candytest SELECT 'K1', 'Red'
INSERT INTO candytest SELECT 'K2', 'Red'
INSERT INTO candytest SELECT 'K2', 'Blue'
INSERT INTO candytest SELECT 'K3', 'White'
INSERT INTO candytest SELECT 'K3', 'Red'
INSERT INTO candytest SELECT 'K3', 'Yellow'
Go
--Now to find those kidid's which has both yellow and red
colored candies, we can write query as:

Select c1.candytest from
candytest c1,candytest c2
where c1.candycolor=c2.candycolor
and c1.kidid=c2.kidid

Answer / kannan

CREATE TABLE IF NOT EXISTS `emp` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) collate latin1_general_ci NOT NULL,
`dept_id` int(11) NOT NULL,
`mgr_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
)


SELECT e2.name Employee_name, e1.name Manager_name
FROM emp e1, emp e2
WHERE e2.mgr_id = e1.id

Answer / anuj barthwal

A self join applies on one table only

Example:

emp_id emp_name emp_manager_id

1 Anuj NULL

2 Sunny 1

3 Suraj 1

4 Narban 2


if we want to get the name of the managers

select e1.emp_name 'manager',e2.emp_name 'employee'
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id

manager employee

Anuj Sunny

Anuj Suraj

Sunny Narban


Self join means joining the single table to itself

Answer / sushant more (sybase dba)

CREATE TABLE EMPLOYEE(

[EMPLOYEE_ID] INT PRIMARY KEY,

[NAME] NVARCHAR(50),

[MANAGER_ID] INT

)

GO

INSERT INTO EMPLOYEE VALUES(101,'Mary',102)

INSERT INTO EMPLOYEE VALUES(102,'Ravi',NULL)

INSERT INTO EMPLOYEE VALUES(103,'Raj',102)

INSERT INTO EMPLOYEE VALUES(104,'Pete',103)

INSERT INTO EMPLOYEE VALUES(105,'Prasad',103)

INSERT INTO EMPLOYEE VALUES(106,'Ben',103)


select e1.NAME from EMPLOYEE e1,EMPLOYEE e2
where e1.EMPLOYEE_ID = e2.MANAGER_ID

What are triggers? How many triggers you can have on a table?

2 Answers   Ramco,

How is data stored in dbms?

0 Answers  

What is dbms explain in brief?

0 Answers  

Does QTP Support the Propjects which are running in Dot Net? As we are starting with new project in Dot net need to use automation tool?Please advice & let me know what would be the best.

0 Answers  

There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly insterted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?

0 Answers  

What is the difference between a primary index and a secondary index? What is a duplicate data entry in an index? Can a primary index contain duplicates?

5 Answers   IBM,

What are the types of database model?

0 Answers  

What are the three types of database design?

0 Answers  

Explain about post-relational databases?

0 Answers  

Explain about a primary key?

0 Answers  

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

0 Answers  

How do you implement one-to-one, one-to-many and many-to- many relationships while designing tables?

1 Answers   Flextronics, Hexaware, SQL Star,