Comment

Comment by codegeek

"List employees (names) who have a bigger salary than their boss"

    SELECT e1.Name FROM Employees e1 LEFT OUTER JOIN     Employees e2
    ON (e1.BossID = e2.EmployeeID)
    WHERE e1.Salary > e2.Salary

"List departments that have less than 3 people in it"

    SELECT d.Name, COUNT(e.EmployeeID) FROM Department d LEFT   OUTER JOIN Employees e
    ON (d.DepartmentID = e.DepartmentID)
    GROUP BY d.Name HAVING COUNT(e.EmployeeID) < 3

"List all departments along with the total salary there"

    SELECT d.Name, SUM(e.Salary) FROM Department d INNER JOIN Employees e
    ON (d.DepartmentID = e.DepartmentID)
    GROUP BY d.Name

"List employees that don't have a boss in the same department"

    SELECT e1.Name FROM Employees e1 LEFT OUTER JOIN        Employees e2
    ON (e1.BossID = e2.EmployeeID)
    WHERE e1.DepartmentID <> e2.DepartmentID

"List all departments along with the number of people there"

    SELECT d.Name, COUNT(e.EmployeeID) FROM Department d 
    LEFT OUTER JOIN Employees e
    ON (d.DepartmentID = e.DepartmentID)
    GROUP BY d.Name

parent codegeek 13 years ago

Comment by codegeek

Discussion