jibitsql.py

#! /usr/bin/env python

from sqlalchemy import create_engine
sqlite = create_engine('sqlite:///:memory:', echo=True)

from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
metadata = MetaData()

from sqlalchemy.sql import select, func

employees = Table(
    'employees', metadata,
    Column('id', Integer, primary_key=True),
    Column('department_id', None, ForeignKey('departments.id')),
    Column('boss_id', None, ForeignKey('employees.id')),
    Column('name', String),
    Column('salary', Integer)
    )

departments = Table(
    'departments', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String)
    )

metadata.create_all(sqlite)
metadata.bind = sqlite

depscolumns = [c.name for c in departments.columns]
depsdata = list(map(
    lambda l: dict(zip(depscolumns, l)),
    [[0, 'Sales'], [1, 'IT'], [2, 'dummy']]))

departments.insert().execute(depsdata)

emplscolumns = [c.name for c in employees.columns]
emplsdata = list(map(
    lambda l: dict(zip(emplscolumns, l)),
    [[0, 0, None, 'Frank', 1000],
     [1, 0, 0, 'John', 1100],
     [2, 1, 0, 'Matt', 800],
     [3, 0, 0, 'Josh', 900],
     [4, 0, 3, 'Alan', 450]]))

employees.insert().execute(emplsdata)


# end of setup
# now, the solution to these questions:
# http://www.jitbit.com/news/181-jitbits-sql-interview-questions/

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

bosses = employees.alias('bosses')
query = select([employees.c.name]).select_from(
    employees.join(
        bosses, employees.c.boss_id == bosses.c.id)
    ).where(
    employees.c.salary > bosses.c.salary)
print(query)
print(list(query.execute()))

# List employees who have the biggest salary in their departments

query = select([employees.c.name, func.max(employees.c.salary)]).group_by(employees.c.department_id)

print(query)
print(list(query.execute()))

# List departments that have less than 3 people in it

depcount = select(
    [departments.c.name, func.count(employees.c.id).label('empcount')]
    ).select_from(
    departments.outerjoin(employees)).group_by(
    employees.c.department_id
    ).alias('depcount')
query = select([depcount]).where(depcount.c.empcount < 3)

print(query)
print(list(query.execute()))


# List all departments along with the number of people there (tricky - people often do an "inner join" leaving out empty departments)

query = select([depcount])

print(query)
print(list(query.execute()))

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

query = select([employees.c.name]).select_from(employees.join(bosses, bosses.c.id == employees.c.boss_id)).where(employees.c.department_id != bosses.c.department_id)

print(query)
print(list(query.execute()))

# List all departments along with the total salary there

query = select([departments.c.name, func.sum(employees.c.salary)]).select_from(departments.outerjoin(employees)).group_by(employees.c.department_id)

print(query)
print(list(query.execute()))