My solutions to jitbit's SQL interview questions:http://www.jitbit.com/news/181-jitbits-sql-interview-questions/Uses SQLAlchemy to populate a sqlite DB with random data to try out the queries with.

jitbit.py

"""
A module to populate a DB schema using sqlalchemy for the problems on this webpage:
http://www.jitbit.com/news/181-jitbits-sql-interview-questions/

Questions and my answers:

-- List employees (names) who have a bigger salary than their boss
 select e.name from Employees as e
  join Employees as b on e.BossId = b.EmployeeID
  where e.Salary > b.Salary

-- List employees who have the biggest salary in their departments
 select e.name, e.DepartmentID, e.Salary from Employees as e
 inner join (
  select DepartmentID, max(Salary) as Salary from Employees
  group by DepartmentID
 ) as g on e.DepartmentID = g.DepartmentID and e.Salary = g.Salary


-- List departments that have less than 3 people in it
 select d.Name, count(e.DepartmentID) as count from Employees as e
 join Departments as d on e.DepartmentID = d.DepartmentID
 group by e.DepartmentID
 having count(e.DepartmentID) <=3

-- List all departments along with the number of people there (tricky - people often do an "inner join" leaving out empty departments)
 select d.Name, ifnull(g.count,0) as count  from Departments d
 left outer join (
  select e.DepartmentID, count(e.DepartmentID) as count from Employees as e
  group by e.DepartmentID
 ) g on d.DepartmentID = g.DepartmentID
 order by count desc

-- List employees that don't have a boss in the same department
 select e.Name from Employees as e
 join Employees as b on e.BossID = b.EmployeeID
 where b.DepartmentID != e.DepartmentID

-- List all departments along with the total salary there
 select d.Name, ifnull(g.sum, 0) as sum  from Departments d
 left outer join (
  select e.DepartmentID, sum(e.Salary) as sum from Employees as e
  group by e.DepartmentID
 ) g on d.DepartmentID = g.DepartmentID
 order by sum desc
"""

from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref, relationship
from faker import Factory
from sqlalchemy.orm import sessionmaker
import random
import os

Base = declarative_base()
fake = Factory.create()


class Employee(Base):
    __tablename__ = 'Employees'

    EmployeeID = Column(Integer, primary_key=True)
    DepartmentID = Column(Integer, ForeignKey('Departments.DepartmentID'))
    BossID = Column(Integer, ForeignKey('Employees.EmployeeID'), nullable=True)
    Name = Column(String)
    Salary = Column(Integer)

    Underlings = relationship("Employee", backref=backref('Boss', remote_side=EmployeeID, lazy='joined', join_depth=1) )

    def __init__(self, **args):
        self.__dict__.update(args)


class Department(Base):
    __tablename__ = 'Departments'

    DepartmentID = Column(Integer, primary_key=True)
    Name = Column(String)

    Department = relationship("Employee", backref=backref('Department'))

    def __init__(self, **args):
        self.__dict__.update(args)


def fake_department():
    return Department(
        Name = ' '.join(fake.words())
    )


def fake_employee(departments=None):
    return Employee(
        Name = fake.name(),
        Salary = random.randint(20,100)*1000
    )


def populate(session):

    #Make departments first
    departments = [fake_department() for i in range(10)]
    for d in departments:
        session.add(d)
    session.commit()

    #Bosses
    bosses = [fake_employee() for i in range(20)]
    for b in bosses:
        b.Department = random.choice(departments)
        session.add(b)
    session.commit()

    #Employees
    employees = [fake_employee() for i in range(80)]
    for e in employees:
        e.Department = random.choice(departments)
        e.Boss = random.choice(bosses)
        session.add(e)
    session.commit()

    #Add an empty deparment
    session.add(fake_department())
    session.commit()

if __name__ == '__main__':
    os.unlink('jitbit.sqlite')
    engine = create_engine('sqlite:///jitbit.sqlite')
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)()
    populate(Session)

Thank you, as someone panicking about a SQL interview tomorrow morning!

Hey, I found this really useful! However, I feel that you could rewrite #3 to account for empty departments as well, like this:

SELECT D.Name, COUNT(E.DepartmentID) AS People FROM Departments AS D LEFT JOIN Employees AS E ON E.DepartmentID = D.DepartmentID GROUP BY D.DepartmentID HAVING COUNT(E.DepartmentID) < 3;

you sure #1 isnt > instead of < ?