pip install ipython-sql
pip install sqlalchemy
pip install psycopg2 ## Postgresql library
%load_ext sql
from sqlalchemy import create_engine
# %sql postgresql://postgres:password123@localhost/exercise
%sql select * from cd.facilities;
* postgres://postgres:***@localhost:5432/exercise
9 rows affected.
%sql select name,membercost from cd.facilities;
* postgres://postgres:***@localhost:5432/exercise
9 rows affected.
%sql select * from cd.facilities where membercost > 0;
* postgres://postgres:***@localhost:5432/exercise
5 rows affected.
%sql select facid,name,membercost,monthlymaintenance from cd.facilities where membercost > 0 and membercost< (monthlymaintenance/50);
* postgres://postgres:***@localhost:5432/exercise
2 rows affected.
%sql select * from cd.facilities where name like '%Tennis%';
* postgres://postgres:***@localhost:5432/exercise
3 rows affected.
%sql select * from cd.facilities where facid in (1,5);
* postgres://postgres:***@localhost:5432/exercise
2 rows affected.
%sql SELECT memid, surname, firstname, joindate FROM cd.members where joindate > '2012-09-01 00:00:00';
* postgres://postgres:***@localhost:5432/exercise
10 rows affected.
%sql SELECT DISTINCT surname from cd.members ORDER BY 1 LIMIT 10;
* postgres://postgres:***@localhost:5432/exercise
10 rows affected.
%sql select joindate as latest_signup from cd.members order by joindate desc limit 1;
* postgres://postgres:***@localhost:5432/exercise
1 rows affected.
%sql select count(*) from cd.facilities where guestcost >= 10;
* postgres://postgres:***@localhost:5432/exercise
1 rows affected.
%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;
* postgres://postgres:***@localhost:5432/exercise
21 rows affected.
%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';
* postgres://postgres:***@localhost:5432/exercise
34 rows affected.