Self join: Employee - Manager

ID   Name        ManagerID

1 Balaji NULL
2 Ashish 1
3 Divya 1
4 Nidhish 2


Write a query to display ManagerName (ManagerID is foreign key to ID)/



SELECT     e.ID, e.Name, mgr.Name AS ManagerName
FROM         Employee AS e LEFT OUTER JOIN
                      Employee AS mgr ON e.ManagerID = mgr.ID

Result:


1 Balaji NULL
2 Ashish Balaji
3 Divya Balaji
4 Nidhish Ashish

SQL Sample question

1.       Find out Name of customer who has maximum number of product?

2.       Find out Name of customer who does not have product.

/*find customer who has maximum product*/

select top 1 c.Name , COUNT(c.ID) as CustomerCount from Customer c left outer join CustomerProduct cp

      on c.ID = cp.CustomerID

            group by c.ID ,c.Name

            order by CustomerCount desc

/*find all customers who does not have any product*/

select c.Name  from Customer c left outer join CustomerProduct cp

      on c.ID = cp.CustomerID

     

            where cp.CustomerID is null

Create a Project plan in 5 easy steps - Microsoft Office Project

http://office.microsoft.com/en-us/project-help/create-a-project-plan-in-5-easy-steps-HA001136153.aspx