SQL

Referential Inegrity
http://www.programmerinterview.com/index.php/database-sql/what-is-referential-integrity/

Having clause & Where clause
http://www.programmerinterview.com/index.php/database-sql/having-vs-where-clause/

Inner Join vs Outer Join
http://www.programmerinterview.com/index.php/database-sql/inner-vs-outer-joins/

Self Join
http://www.programmerinterview.com/index.php/database-sql/what-is-a-self-join/

Correlated vs Uncorrelated query
http://www.programmerinterview.com/index.php/database-sql/correlated-vs-uncorrelated-subquery/

Index
http://www.programmerinterview.com/index.php/database-sql/what-is-an-index/

SELECT b.Emp_id, b.Emp_name,e.emp_id as managerID, e.emp_name as managerName
FROM Employee b
JOIN Employee e ON b.Emp_ID = e.emp_mgr_id

This is a classic self-join, try the following:

http://blog.sqlauthority.com/2010/07/08/sql-server-the-self-join-inner-join-and-outer-join/

SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
emp e, emp m
WHERE e.mgr = m.empno

And if you want to include the president which has no manager then instead of an inner join use an outer join in Oracle syntax:

SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
emp e, emp m
WHERE e.mgr = m.empno(+)
Or in ANSI SQL syntax:

SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
emp e
LEFT OUTER JOIN emp m
ON e.mgr = m.empno

Advertisements