pip install ipython-sql pip install sqlalchemy pip install psycopg2 ## Postgresql library

%load_ext sql

from sqlalchemy import create_engine

%sql select * from cd.facilities;

%sql select name,membercost from cd.facilities;

%sql select * from cd.facilities where membercost > 0;

%sql select facid,name,membercost,monthlymaintenance from cd.facilities where membercost > 0 and membercost< (monthlymaintenance/50);

%sql select * from cd.facilities where name like '%Tennis%';

%sql select * from cd.facilities where facid in (1,5);

%sql SELECT memid, surname, firstname, joindate FROM cd.members where joindate > '2012-09-01 00:00:00';

%sql SELECT DISTINCT surname from cd.members ORDER BY 1 LIMIT 10;

%sql select joindate as latest_signup from cd.members order by joindate desc limit 1;

%sql select count(*) from cd.facilities where guestcost >= 10;

%sql select facid,sum(slots) from cd.bookings where starttime > '2012-09-01' AND starttime < '2012-10-01' group by facid ORDER BY SUM(slots) ;

%sql select facid,sum(slots) as total_slots from cd.bookings group by facid having sum(slots)>1000 order by facid;

%sql select starttime as start ,name from cd.bookings left join cd.facilities on cd.facilities.facid=cd.bookings.facid where cd.facilities.name like '%Tennis%' and cd.bookings.starttime between'2012-09-21 00:00:00' and '2012-09-21 24:00:00' order by start;

%sql select starttime from cd.bookings left join cd.members on cd.members.memid=cd.bookings.memid where firstname like 'David' and surname like 'Farrell';