SQL basics

Setup

  • Download ipython-sql from here or use pip install ipython-sql
  • Download Iris dataset in csv format from here
  • Install postgresql locally
import pandas as pd
from pandas import DataFrame
%load_ext sql
%config SqlMagic.feedback = False
%sql postgresql://jloach@/
'Connected: jloach@'

Make a database to play with

%%sql
DROP TABLE parishes; -- Delete existing table under this name
CREATE TABLE parishes(
  id          INT PRIMARY KEY NOT NULL, 
  name        TEXT,
  population  INT,
  loc_n       REAL,
  loc_w       REAL
);
INSERT INTO parishes VALUES (1, 'Blymhill & Weston-under-Lizard', 823, 52.702, 2.284);
INSERT INTO parishes VALUES (2, 'Lapley, Stretton and Wheaton Aston', 2548, 52.71, 2.2);
INSERT INTO parishes VALUES (3, 'Church Eaton', 680, 52.757, 2.222);
INSERT INTO parishes VALUES (4, 'Penkridge', 8526, NULL, NULL);
INSERT INTO parishes VALUES (5, 'Sheriffhales', 722, NULL, NULL);
[]

SELECT

%sql SELECT * FROM parishes
id name population loc_n loc_w
1 Blymhill & Weston-under-Lizard 823 52.702 2.284
2 Lapley, Stretton and Wheaton Aston 2548 52.71 2.2
3 Church Eaton 680 52.757 2.222
4 Penkridge 8526 None None
5 Sheriffhales 722 None None
%%sql
SELECT name as "Parish",
       population as "Population" 
  FROM parishes
Parish Population
Blymhill & Weston-under-Lizard 823
Lapley, Stretton and Wheaton Aston 2548
Church Eaton 680
Penkridge 8526
Sheriffhales 722
%%sql
SELECT name as "Parish",
       ROUND(population/10.) as "Population/10" 
  FROM parishes
Parish Population/10
Blymhill & Weston-under-Lizard 82
Lapley, Stretton and Wheaton Aston 255
Church Eaton 68
Penkridge 853
Sheriffhales 72

LIMIT

%%sql
SELECT *
  FROM parishes 
 LIMIT 2
id name population loc_n loc_w
1 Blymhill & Weston-under-Lizard 823 52.702 2.284
2 Lapley, Stretton and Wheaton Aston 2548 52.71 2.2

WHERE

%%sql
SELECT * 
  FROM parishes
 WHERE population > 1000
id name population loc_n loc_w
2 Lapley, Stretton and Wheaton Aston 2548 52.71 2.2
4 Penkridge 8526 None None
%%sql
SELECT * 
  FROM parishes 
 WHERE name > 'L'
id name population loc_n loc_w
2 Lapley, Stretton and Wheaton Aston 2548 52.71 2.2
4 Penkridge 8526 None None
5 Sheriffhales 722 None None

LIKE / ILIKE

%%sql
SELECT * 
  FROM parishes 
 WHERE name 
  LIKE 'B%'
id name population loc_n loc_w
1 Blymhill & Weston-under-Lizard 823 52.702 2.284
%%sql
SELECT * 
  FROM parishes 
 WHERE name 
  LIKE 'P_______e'
id name population loc_n loc_w
4 Penkridge 8526 None None
%%sql
SELECT * 
  FROM parishes 
 WHERE name 
 ILIKE 'b%'
id name population loc_n loc_w
1 Blymhill & Weston-under-Lizard 823 52.702 2.284

IN

%%sql
SELECT * 
  FROM parishes 
 WHERE name IN ('Penkridge', 'Church Eaton')
id name population loc_n loc_w
3 Church Eaton 680 52.757 2.222
4 Penkridge 8526 None None

BETWEEN

%%sql
SELECT * 
  FROM parishes 
 WHERE population BETWEEN 500 AND 2000
id name population loc_n loc_w
1 Blymhill & Weston-under-Lizard 823 52.702 2.284
3 Church Eaton 680 52.757 2.222
5 Sheriffhales 722 None None

NULL

%%sql
SELECT *
  FROM parishes 
 WHERE loc_n IS NULL
id name population loc_n loc_w
4 Penkridge 8526 None None
5 Sheriffhales 722 None None

OR / AND / NOT

%%sql
SELECT * 
  FROM parishes 
 WHERE (name LIKE 'P%' OR population < 800) AND NOT loc_n IS NULL
id name population loc_n loc_w
3 Church Eaton 680 52.757 2.222

ORDER BY / DESC

%%sql
SELECT *
  FROM parishes
 ORDER BY population DESC
id name population loc_n loc_w
4 Penkridge 8526 None None
2 Lapley, Stretton and Wheaton Aston 2548 52.71 2.2
1 Blymhill & Weston-under-Lizard 823 52.702 2.284
5 Sheriffhales 722 None None
3 Church Eaton 680 52.757 2.222
%%sql
SELECT name, population
  FROM parishes
 ORDER BY 2 -- Doesn't' work in all SQL variants 
name population
Church Eaton 680
Sheriffhales 722
Blymhill & Weston-under-Lizard 823
Lapley, Stretton and Wheaton Aston 2548
Penkridge 8526
%%sql
SELECT name, population, loc_n
  FROM parishes
 ORDER BY population, loc_n -- Though this isn't meaningful here
name population loc_n
Church Eaton 680 52.757
Sheriffhales 722 None
Blymhill & Weston-under-Lizard 823 52.702
Lapley, Stretton and Wheaton Aston 2548 52.71
Penkridge 8526 None

If you spot any errors please let me know on twitter.