DB Related Questions (SQL, MySql, Oracle)

 SQL Questions ::

1.Constraints in SQL.

2.Difference between unique key and primary key.

3.How can we get name, id & manager-name fields in a employee table were we have name, id & manger-id fields in employee table. Every employee of the company have their respective details in employee details except CEO

Soln :: Self Join

A self join is a join in which a table is joined with itself (which is also called Unary relationships), specially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.

The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.

The syntax of the command for joining a table to itself is almost same as that for joining two different tables. To distinguish the column names from one another, aliases for the actual the table name are used, since both the tables have same name. Table name aliases are defined in the FROM clause of the SELECT statement

self join is a join in which a table is joined with itself (which is also called Unary relationships), specially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.
The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.
The syntax of the command for joining a table to itself is almost same as that for joining two different tables. To distinguish the column names from one another, aliases for the actual the table name are used, since both the tables have same name. Table name aliases are defined in the FROM clause of the SELECT statement. See the syntax :
- See more at: http://www.w3resource.com/sql/joins/perform-a-self-join.php#sthash.xWmie4wh.dpuf

SELECT a.column_name, b.column_name... FROM table1 a, table1 b WHERE a.common_filed = b.common_field; 

self join is a join in which a table is joined with itself (which is also called Unary relationships), specially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.
The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.
The syntax of the command for joining a table to itself is almost same as that for joining two different tables. To distinguish the column names from one another, aliases for the actual the table name are used, since both the tables have same name. Table name aliases are defined in the FROM clause of the SELECT statement. See the syntax :
- See more at: http://www.w3resource.com/sql/joins/perform-a-self-join.php#sthash.xWmie4wh.dpuf

self join is a join in which a table is joined with itself (which is also called Unary relationships), specially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.
The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.
The syntax of the command for joining a table to itself is almost same as that for joining two different tables. To distinguish the column names from one another, aliases for the actual the table name are used, since both the tables have same name. Table name aliases are defined in the FROM clause of the SELECT statement. See the syntax :
- See more at: http://www.w3resource.com/sql/joins/perform-a-self-join.php#sthash.xWmie4wh.dpuf

self join is a join in which a table is joined with itself (which is also called Unary relationships), specially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.
The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.
The syntax of the command for joining a table to itself is almost same as that for joining two different tables. To distinguish the column names from one another, aliases for the actual the table name are used, since both the tables have same name. Table name aliases are defined in the FROM clause of the SELECT statement. See the syntax :
- See more at: http://www.w3resource.com/sql/joins/perform-a-self-join.php#sthash.xWmie4wh.dpuf

EXAMPLE::

CREATE TABLE COMPANY(ID INT NOT NULL, NAME TEXT NOT NULL, MANAGER_ID INT NOT NULL);

INSERT INTO COMPANY (ID,NAME,MANAGER_ID) VALUES (1, 'Suresh', 5);
INSERT INTO COMPANY (ID,NAME,MANAGER_ID) VALUES (2, 'Ramu', 5);
INSERT INTO COMPANY (ID,NAME,MANAGER_ID) VALUES (3, 'SureshAV', 4);
INSERT INTO COMPANY (ID,NAME,MANAGER_ID) VALUES (4, 'Kalidoss', 6);
INSERT INTO COMPANY (ID,NAME,MANAGER_ID) VALUES (5, 'Ramesh', 6);
INSERT INTO COMPANY (ID,NAME,MANAGER_ID) VALUES (6, 'Ramki', 7);

select b.NAME, b.ID, a.NAME as MANAGER_NAME from COMPANY a, COMPANY b where a.ID = b.MANAGER_ID;