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

Leave a comment

Filed under SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s