02-SQL Basic

In [1]:

Copied!

%load_ext sql
%load_ext sql

In [2]:

Copied!

import os
import os

Connecting to the database¶

In [3]:

Copied!





host = "localhost"
database = "sdb"
user = os.getenv('SQL_USER')
password = os.getenv('SQL_PASSWORD')
host = "localhost"
database = "sdb"
user = os.getenv('SQL_USER')
password = os.getenv('SQL_PASSWORD')

In [4]:

Copied!

connection_string = f"postgresql://{user}:{password}@{host}/{database}"
%sql $connection_string
connection_string = f"postgresql://{user}:{password}@{host}/{database}"
%sql $connection_string

Out[4]:

'Connected: postgres@sdb'

In [5]:

Copied!

%%sql 

SELECT * FROM cities5000 LIMIT 10
%%sql 

SELECT * FROM cities5000 LIMIT 10

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

Out[5]:

id	geonameid	name	asciiname	alternatenames	latitude	longitude	feature_class	feature_code	country_code	admin1_code	population	dem	timezone	modification_date
1	3039163	Sant Julià de Lòria	Sant Julia de Loria	San Julia,San Julià,Sant Julia de Loria,Sant Julià de Lòria,Sant-Zhulija-de-Lorija,sheng hu li ya-de luo li ya,Сант-Жулия-де-Лория,サン・ジュリア・デ・ロリア教区,圣胡利娅-德洛里亚,圣胡利娅－德洛里亚	42.46372	1.49129	P	PPLA	AD	06	8022	921	Europe/Andorra	2013-11-23
2	3039678	Ordino	Ordino	Ordino,ao er di nuo,orudino jiao qu,Ордино,オルディノ教区,奥尔迪诺	42.55623	1.53319	P	PPLA	AD	05	3066	1296	Europe/Andorra	2018-10-26
3	3040051	les Escaldes	les Escaldes	Ehskal'des-Ehndzhordani,Escaldes,Escaldes-Engordany,Les Escaldes,esukarudesu=engorudani jiao qu,lai sai si ka er de-en ge er da,Эскальдес-Энджордани,エスカルデス＝エンゴルダニ教区,萊塞斯卡爾德-恩戈爾達,萊塞斯卡爾德－恩戈爾達	42.50729	1.53414	P	PPLA	AD	08	15853	1033	Europe/Andorra	2008-10-15
4	3040132	la Massana	la Massana	La Macana,La Massana,La Maçana,La-Massana,la Massana,ma sa na,Ла-Массана,ラ・マサナ教区,马萨纳	42.54499	1.51483	P	PPLA	AD	04	7211	1245	Europe/Andorra	2008-10-15
5	3040686	Encamp	Encamp	Ehnkam,Encamp,en kan pu,enkanpu jiao qu,Энкам,エンカンプ教区,恩坎普	42.53474	1.58014	P	PPLA	AD	03	11223	1257	Europe/Andorra	2018-10-26
6	3041204	Canillo	Canillo	Canillo,Kanil'o,ka ni e,kaniryo jiao qu,Канильо,カニーリョ教区,卡尼略	42.5676	1.59756	P	PPLA	AD	02	3292	1561	Europe/Andorra	2018-10-26
7	3041563	Andorra la Vella	Andorra la Vella	ALV,Ando-la-Vyey,Andora,Andora la Vela,Andora la Velja,Andora lja Vehl'ja,Andoro Malnova,Andorra,Andorra Tuan,Andorra a Vella,Andorra la Biella,Andorra la Vella,Andorra la Vielha,Andorra-a-Velha,Andorra-la-Vel'ja,Andorra-la-Vielye,Andorre-la-Vieille,Andò-la-Vyèy,Andòrra la Vièlha,an dao er cheng,andolalabeya,andwra la fyla,Ανδόρρα,Андора ла Веля,Андора ла Веља,Андора ля Вэлья,Андорра-ла-Велья,אנדורה לה וולה,أندورا لا فيلا,አንዶራ ላ ቬላ,アンドラ・ラ・ヴェリャ,安道爾城,안도라라베야	42.50779	1.52109	P	PPLC	AD	07	20430	1037	Europe/Andorra	2020-03-03
8	290594	Umm Al Quwain City	Umm Al Quwain City	Oumm al Qaiwain,Oumm al Qaïwaïn,Um al Kawain,Um al Quweim,Umm Al Quwain City,Umm al Qaiwain,Umm al Qawain,Umm al Qaywayn,Umm al-Quwain,Umm-ehl'-Kajvajn,Yumul al Quwain,am alqywyn,mdynt am alqywyn,Умм-эль-Кайвайн,أم القيوين,مدينة ام القيوين	25.56473	55.55517	P	PPLA	AE	07	62747	2	Asia/Dubai	2019-10-24
9	291074	Ras Al Khaimah City	Ras Al Khaimah City	Julfa,Khaimah,RAK City,RKT,Ra's al Khaymah,Ra's al-Chaima,Ras Al Khaimah City,Ras al Khaimah,Ras al-Khaimah,Ras el Khaimah,Ras el Khaïmah,Ras el-Kheima,Ras-ehl'-Khajma,Ra’s al Khaymah,Ra’s al-Chaima,mdynt ras alkhymt,ras alkhymt,Рас-эль-Хайма,رأس الخيمة,مدينة رأس الخيمة	25.78953	55.9432	P	PPLA	AE	05	351943	2	Asia/Dubai	2019-09-09
10	291279	Muzayri‘	Muzayri`	Mezaira'a,Mezaira’a,Mizeir`ah,Mizeir‘ah,Mozayri`,Mozayri‘,Muzairi,Muzayri`,Muzayri‘,Музаири	23.14355	53.7881	P	PPL	AE	01	10000	123	Asia/Dubai	2013-10-24

The SQL SELECT statement¶

In [6]:

Copied!





%%sql

SELECT name, country_code, dem FROM cities5000 
WHERE country_code LIKE 'C_'
ORDER BY dem DESC 
LIMIT 10
%%sql

SELECT name, country_code, dem FROM cities5000 
WHERE country_code LIKE 'C_'
ORDER BY dem DESC 
LIMIT 10

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

Out[6]:

name	country_code	dem
Nagqu	CN	4512
Jiangzi	CN	4036
Dêqên	CN	3974
Burang	CN	3892
Rikaze	CN	3847
Daocheng	CN	3756
Lhasa	CN	3651
Gahai	CN	3546
Awancang	CN	3504
Maima	CN	3495

In [7]:

Copied!

%%sql

SELECT DISTINCT country_code FROM cities5000 
LIMIT 10
%%sql

SELECT DISTINCT country_code FROM cities5000 
LIMIT 10

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

Out[7]:

country_code
RU
FK
KP
DK
SN
SI
PN
CZ
KR
VE

In [8]:

Copied!

%%sql

SELECT COUNT(DISTINCT country_code) FROM cities5000
%%sql

SELECT COUNT(DISTINCT country_code) FROM cities5000

 * postgresql://postgres:***@localhost/sdb
1 rows affected.

Out[8]:

count
245

In [9]:

Copied!

%%sql

SELECT MAX(dem) FROM cities5000
%%sql

SELECT MAX(dem) FROM cities5000

 * postgresql://postgres:***@localhost/sdb
1 rows affected.

Out[9]:

max
5022

In [10]:

Copied!

%%sql

SELECT SUM(population) FROM cities5000;
%%sql

SELECT SUM(population) FROM cities5000;

 * postgresql://postgres:***@localhost/sdb
(psycopg2.errors.UndefinedFunction) function sum(character varying) does not exist
LINE 1: SELECT SUM(population) FROM cities5000;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

[SQL: SELECT SUM(population) FROM cities5000;]
(Background on this error at: http://sqlalche.me/e/14/f405)

In [11]:

Copied!

%%sql

SELECT SUM(CAST(population AS INTEGER)) FROM cities5000;
%%sql

SELECT SUM(CAST(population AS INTEGER)) FROM cities5000;

 * postgresql://postgres:***@localhost/sdb
1 rows affected.

Out[11]:

sum
3228361637

In [12]:

Copied!

%%sql

SELECT AVG(CAST(population AS INTEGER)) FROM cities5000;
%%sql

SELECT AVG(CAST(population AS INTEGER)) FROM cities5000;

 * postgresql://postgres:***@localhost/sdb
1 rows affected.

Out[12]:

avg
62503.371415848677

In [13]:

Copied!

%%sql

SELECT name, country_code, dem, CAST(population AS INTEGER) FROM cities5000 
ORDER BY dem DESC, population DESC
LIMIT 10
%%sql

SELECT name, country_code, dem, CAST(population AS INTEGER) FROM cities5000 
ORDER BY dem DESC, population DESC
LIMIT 10

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

Out[13]:

name	country_code	dem	population
La Rinconada	PE	5022	29678
La Rinconada Ana Maria (La Rinconada)	PE	5016	12183
Lobujya	NP	4972	8767
Hacienda Huancane	PE	4531	7831
Nagqu	CN	4512	30000
Morococha	PE	4512	7890
Macusani	PE	4360	6044
Yanacancha	PE	4348	24105
Cerro de Pasco	PE	4336	78910
Paragsha- San Andres - Jose Carlos Mariategui	PE	4336	6316

The WHERE Clause¶

In [14]:

Copied!

%%sql

SELECT * FROM cities5000 WHERE country_code='US' LIMIT 10
%%sql

SELECT * FROM cities5000 WHERE country_code='US' LIMIT 10

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

Out[14]:

id	geonameid	name	asciiname	alternatenames	latitude	longitude	feature_class	feature_code	country_code	admin1_code	admin2_code	population	elevation	dem	timezone	modification_date
45910	4702785	Keene	Keene		32.39681	-97.3239	P	PPL	US	TX	251	6181	272.0	279	America/Chicago	2017-03-09
43474	4046255	Bay Minette	Bay Minette	Bay Minette,Bay Minette Station,Bej Minet,Bej-Minett,bay mynyty,be minetta,bei mi nei te,by mynt,Бей-Мінетт,Беј Минет,باي مينيتي,بی مینت,बे मिनेत्त,贝米内特	30.88296	-87.77305	P	PPLA2	US	AL	003	9118	82.0	85	America/Chicago	2017-03-09
43475	4046274	Edna	Edna	Edna,Ednaville,Macaroni Station,adna,adna tgzas,Една,إدنا,ادنا، تگزاس	28.97859	-96.64609	P	PPLA2	US	TX	239	5792	20.0	24	America/Chicago	2017-03-09
43476	4046332	Henderson	Henderson	Khenderson,hndrswn,hndrswn tgzas,Хендерсон,هندرسون,هندرسون، تگزاس	32.15322	-94.79938	P	PPLA2	US	TX	401	13529	156.0	160	America/Chicago	2017-03-09
43477	4046704	Fort Hunt	Fort Hunt		38.73289	-77.05803	P	PPL	US	VA	059	16045	10.0	16	America/New_York	2011-05-14
43478	4047906	Trinity	Trinity		28.18085	-82.68177	P	PPL	US	FL	101	10907	6.0	15	America/New_York	2011-05-14
43479	4047914	Villas	Villas		26.55035	-81.8687	P	PPL	US	FL	071	11569	3.0	6	America/New_York	2011-05-14
43480	4048023	Bessemer	Bessemer	Besemer,Bessemer,bei se mo,bes'semara,beseomeo,bsmr,bysmyr,Бесемер,Бессемер,بسمر,بيسمير,बेस्सेमर,贝瑟默,베서머	33.40178	-86.95444	P	PPL	US	AL	073	26730	155.0	161	America/Chicago	2017-03-09
43481	4048662	Paducah	Paducah	PAH,Padaka,Padjuka,Paducah,Paduka,Pekin,badwka,pa di you ka,padeyuka,pdwka kntaky,peodyuka,Падака,Падука,Падјука,بادوكا,پاڈوکاہ، کینٹکی,پدوکا، کنتاکی,パデューカ,帕迪尤卡,퍼듀카	37.08339	-88.60005	P	PPLA2	US	KY	145	24864	104.0	104	America/Chicago	2018-01-03
43482	4048888	Red Chute	Red Chute		32.55598	-93.61323	P	PPL	US	LA	015	6261	65.0	67	America/Chicago	2011-05-14

In [15]:

Copied!

%%sql

SELECT * FROM cities5000 WHERE country_code='US' OR country_code='CA' LIMIT 5
%%sql

SELECT * FROM cities5000 WHERE country_code='US' OR country_code='CA' LIMIT 5

 * postgresql://postgres:***@localhost/sdb
5 rows affected.

Out[15]:

id	geonameid	name	asciiname	alternatenames	latitude	longitude	feature_class	feature_code	country_code	admin1_code	admin2_code	admin3_code	population	dem	timezone	modification_date
5902	5901272	Binbrook	Binbrook		43.12135	-79.81104	P	PPLX	CA	08	3525		8796	216	America/Toronto	2018-01-20
5903	5903510	Blainville	Blainville		45.66678	-73.88249	P	PPL	CA	10	15	73015	46493	73	America/Toronto	2019-02-26
5904	5904501	Bluewater	Bluewater		43.46679	-81.59977	P	PPL	CA	08			7044	249	America/Toronto	2013-05-05
5872	5881791	Abbotsford	Abbotsford	Abbotsford,Abotsford,Abotsfordas,YXX,abottsufodo,abwtsfwrd,abwtsfwrd brytysh klmbya,aebeocheupeodeu,ya bo si fu,Абботсфорд,Аботсфорд,أبوتسفورد,ابوتسفورد، بریتیش کلمبیا,ایباٹسفورڈ,ایبٹس فورڈ، برٹش کولمبیا,アボッツフォード,亞博斯福,애버츠퍼드	49.05798	-122.25257	P	PPL	CA	02	5909	5909052	141397	114	America/Vancouver	2021-12-05
5873	5882142	Acton Vale	Acton Vale	Akton Veil,Akton Vejl,Aktonvejl,aktwn wal kbk,Άκτον Βέιλ,Актон Вејл,Актонвейл,اکتون وال، کبک,ایکٹن والی	45.65007	-72.56582	P	PPL	CA	10	16	48028	7656	90	America/Toronto	2021-11-12

In [16]:

Copied!

%%sql 

SELECT name,population,country_code,dem FROM cities5000 
WHERE country_code='US' AND CAST(population AS INTEGER)>1000000
%%sql 

SELECT name,population,country_code,dem FROM cities5000 
WHERE country_code='US' AND CAST(population AS INTEGER)>1000000

 * postgresql://postgres:***@localhost/sdb
14 rows affected.

Out[16]:

name	population	country_code	dem
Philadelphia	1567442	US	40
Dallas	1300092	US	139
Houston	2296224	US	30
San Antonio	1469845	US	202
Chicago	2720546	US	180
The Bronx	1385108	US	19
Brooklyn	2300664	US	18
Manhattan	1487536	US	38
New York City	8175133	US	57
Queens	2272771	US	13
Phoenix	1563025	US	366
Los Angeles	3971883	US	96
San Diego	1394928	US	20
San Jose	1026908	US	23

In [17]:

Copied!

%%sql

SELECT name,population,country_code,dem  FROM cities5000 
WHERE name LIKE 'U%' LIMIT 5
%%sql

SELECT name,population,country_code,dem  FROM cities5000 
WHERE name LIKE 'U%' LIMIT 5

 * postgresql://postgres:***@localhost/sdb
5 rows affected.

Out[17]:

name	population	country_code	dem
Umm Al Quwain City	62747	AE	2
Uruzgān	13388	AF	2026
Uíge	60008	AO	825
Uacu Cungo	10970	AO	1304
Urdinarrain	7992	AR	68

In [18]:

Copied!

%%sql

SELECT name,population,country_code,dem FROM cities5000 
WHERE country_code LIKE 'C_' LIMIT 10
%%sql

SELECT name,population,country_code,dem FROM cities5000 
WHERE country_code LIKE 'C_' LIMIT 10

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

Out[18]:

name	population	country_code	dem
Sinfra	59919	CI	265
Hekou	47281	CN	41
Songlou	79763	CN	40
Sunlou	46941	CN	40
Zhuzhai	56908	CN	40
Wanchao	12067	CN	835
Binbrook	8796	CA	216
Blainville	46493	CA	73
Bluewater	7044	CA	249
Abbotsford	141397	CA	114

In [19]:

Copied!

%%sql 

SELECT name,population,country_code,dem FROM cities5000 
WHERE name LIKE 'He__' LIMIT 10
%%sql 

SELECT name,population,country_code,dem FROM cities5000 
WHERE name LIKE 'He__' LIMIT 10

 * postgresql://postgres:***@localhost/sdb
8 rows affected.

Out[19]:

name	population	country_code	dem
Hepu	192813	CN	12
Hepo	131238	CN	41
Heze	1346717	CN	53
Hede	89107	CN	2
Hebi	634721	CN	138
Heba	32538	CN	1772
Heek	8272	DE	52
Heer	7615	NL	58

In [20]:

Copied!

%%sql 

SELECT name,population,country_code,dem FROM cities5000 
WHERE dem BETWEEN 3500 AND 3600
%%sql 

SELECT name,population,country_code,dem FROM cities5000 
WHERE dem BETWEEN 3500 AND 3600

 * postgresql://postgres:***@localhost/sdb
6 rows affected.

Out[20]:

name	population	country_code	dem
Gahai	5257	CN	3546
Awancang	6619	CN	3504
Padam	25000	IN	3560
Leh	37475	IN	3502
Sicuani	33575	PE	3551
San Miguel de Cauri	5326	PE	3586

SQL Joins¶

Reference: https://www.w3schools.com/sql/sql_join.asp

Here are the different types of the JOINs in SQL:

(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

Get number of rows

In [21]:

Copied!

%%sql 

SELECT COUNT(*) FROM cities
%%sql 

SELECT COUNT(*) FROM cities

 * postgresql://postgres:***@localhost/sdb
1 rows affected.

Out[21]:

count
1249

In [22]:

Copied!

%%sql 

SELECT * FROM cities LIMIT 10
%%sql 

SELECT * FROM cities LIMIT 10

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

Out[22]:

id	name	country	latitude	longitude	population
1	Bombo	UGA	0.5833	32.5333	75000
2	Fort Portal	UGA	0.671	30.275	42670
3	Potenza	ITA	40.642	15.799	69060
4	Campobasso	ITA	41.563	14.656	50762
5	Aosta	ITA	45.737	7.315	34062
6	Mariehamn	ALD	60.097	19.949	10682
7	Ramallah	PSE	31.90294	35.20621	24599
8	Vatican City	VAT	41.90001	12.44781	832
9	Poitier	FRA	46.58329	0.33328	85960
10	Clermont-Ferrand	FRA	45.77998	3.08001	233050

In [23]:

Copied!

%%sql 

SELECT COUNT(*) FROM countries
%%sql 

SELECT COUNT(*) FROM countries

 * postgresql://postgres:***@localhost/sdb
1 rows affected.

Out[23]:

count
243

In [24]:

Copied!

%%sql 

SELECT * FROM countries LIMIT 10
%%sql 

SELECT * FROM countries LIMIT 10

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

Out[24]:

id	Country	Alpha2_code	Alpha3_code	Numeric_code	Latitude	Longitude
1	Afghanistan	AF	AFG	4	33	65
2	Albania	AL	ALB	8	41	20
3	Algeria	DZ	DZA	12	28	3
4	American Samoa	AS	ASM	16	-14.3333	-170
5	Andorra	AD	AND	20	42.5	1.6
6	Angola	AO	AGO	24	-12.5	18.5
7	Anguilla	AI	AIA	660	18.25	-63.1667
8	Antarctica	AQ	ATA	10	-90	0
9	Antigua and Barbuda	AG	ATG	28	17.05	-61.8
10	Argentina	AR	ARG	32	-34	-64

Inner Join¶

In [25]:

Copied!

%%sql

SELECT * FROM cities INNER JOIN countries ON cities.country = countries."Alpha3_code" LIMIT 10
%%sql

SELECT * FROM cities INNER JOIN countries ON cities.country = countries."Alpha3_code" LIMIT 10

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

Out[25]:

id	name	country	latitude	longitude	population	id_1	Country	Alpha2_code	Alpha3_code	Numeric_code	Latitude	Longitude
1	Bombo	UGA	0.5833	32.5333	75000	226	Uganda	UG	UGA	800	1	32
2	Fort Portal	UGA	0.671	30.275	42670	226	Uganda	UG	UGA	800	1	32
3	Potenza	ITA	40.642	15.799	69060	106	Italy	IT	ITA	380	42.8333	12.8333
4	Campobasso	ITA	41.563	14.656	50762	106	Italy	IT	ITA	380	42.8333	12.8333
5	Aosta	ITA	45.737	7.315	34062	106	Italy	IT	ITA	380	42.8333	12.8333
7	Ramallah	PSE	31.90294	35.20621	24599	167	Palestinian Territory, Occupied	PS	PSE	275	32	35.25
8	Vatican City	VAT	41.90001	12.44781	832	94	Holy See (Vatican City State)	VA	VAT	336	41.9	12.45
9	Poitier	FRA	46.58329	0.33328	85960	72	France	FR	FRA	250	46	2
10	Clermont-Ferrand	FRA	45.77998	3.08001	233050	72	France	FR	FRA	250	46	2
11	Besancon	FRA	47.23	6.03001	128426	72	France	FR	FRA	250	46	2

In [26]:

Copied!

%%sql

SELECT name, country, countries."Country" 
FROM cities INNER JOIN countries ON cities.country = countries."Alpha3_code"
LIMIT 20
%%sql

SELECT name, country, countries."Country" 
FROM cities INNER JOIN countries ON cities.country = countries."Alpha3_code"
LIMIT 20

 * postgresql://postgres:***@localhost/sdb
20 rows affected.

Out[26]:

name	country	Country
Bombo	UGA	Uganda
Fort Portal	UGA	Uganda
Potenza	ITA	Italy
Campobasso	ITA	Italy
Aosta	ITA	Italy
Ramallah	PSE	Palestinian Territory, Occupied
Vatican City	VAT	Holy See (Vatican City State)
Poitier	FRA	France
Clermont-Ferrand	FRA	France
Besancon	FRA	France
Artigas Base	ATA	Antarctica
Chipata	ZMB	Zambia
Jinja	UGA	Uganda
Arua	UGA	Uganda
Mbale	UGA	Uganda
Moroto	UGA	Uganda
Masaka	UGA	Uganda
Mbarara	UGA	Uganda
Otjiwarongo	NAM	Namibia
Bologna	ITA	Italy

Left Join¶

In [27]:

Copied!

%%sql

SELECT * FROM cities LEFT JOIN countries ON cities.country = countries."Alpha3_code"
LIMIT 10
%%sql

SELECT * FROM cities LEFT JOIN countries ON cities.country = countries."Alpha3_code"
LIMIT 10

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

Out[27]:

id	name	country	latitude	longitude	population	id_1	Country	Alpha2_code	Alpha3_code	Numeric_code	Latitude	Longitude
1	Bombo	UGA	0.5833	32.5333	75000	226	Uganda	UG	UGA	800	1	32
2	Fort Portal	UGA	0.671	30.275	42670	226	Uganda	UG	UGA	800	1	32
3	Potenza	ITA	40.642	15.799	69060	106	Italy	IT	ITA	380	42.8333	12.8333
4	Campobasso	ITA	41.563	14.656	50762	106	Italy	IT	ITA	380	42.8333	12.8333
5	Aosta	ITA	45.737	7.315	34062	106	Italy	IT	ITA	380	42.8333	12.8333
6	Mariehamn	ALD	60.097	19.949	10682	None	None	None	None	None	None	None
7	Ramallah	PSE	31.90294	35.20621	24599	167	Palestinian Territory, Occupied	PS	PSE	275	32	35.25
8	Vatican City	VAT	41.90001	12.44781	832	94	Holy See (Vatican City State)	VA	VAT	336	41.9	12.45
9	Poitier	FRA	46.58329	0.33328	85960	72	France	FR	FRA	250	46	2
10	Clermont-Ferrand	FRA	45.77998	3.08001	233050	72	France	FR	FRA	250	46	2

Right Join¶

In [28]:

Copied!

%%sql

SELECT * FROM cities RIGHT JOIN countries ON cities.country = countries."Alpha3_code"
LIMIT 10
%%sql

SELECT * FROM cities RIGHT JOIN countries ON cities.country = countries."Alpha3_code"
LIMIT 10

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

Out[28]:

id	name	country	latitude	longitude	population	id_1	Country	Alpha2_code	Alpha3_code	Numeric_code	Latitude	Longitude
1	Bombo	UGA	0.5833	32.5333	75000	226	Uganda	UG	UGA	800	1	32
2	Fort Portal	UGA	0.671	30.275	42670	226	Uganda	UG	UGA	800	1	32
3	Potenza	ITA	40.642	15.799	69060	106	Italy	IT	ITA	380	42.8333	12.8333
4	Campobasso	ITA	41.563	14.656	50762	106	Italy	IT	ITA	380	42.8333	12.8333
5	Aosta	ITA	45.737	7.315	34062	106	Italy	IT	ITA	380	42.8333	12.8333
7	Ramallah	PSE	31.90294	35.20621	24599	167	Palestinian Territory, Occupied	PS	PSE	275	32	35.25
8	Vatican City	VAT	41.90001	12.44781	832	94	Holy See (Vatican City State)	VA	VAT	336	41.9	12.45
9	Poitier	FRA	46.58329	0.33328	85960	72	France	FR	FRA	250	46	2
10	Clermont-Ferrand	FRA	45.77998	3.08001	233050	72	France	FR	FRA	250	46	2
11	Besancon	FRA	47.23	6.03001	128426	72	France	FR	FRA	250	46	2

Full Join¶

In [29]:

Copied!

%%sql

SELECT * FROM cities FULL JOIN countries ON cities.country = countries."Alpha3_code"
LIMIT 10
%%sql

SELECT * FROM cities FULL JOIN countries ON cities.country = countries."Alpha3_code"
LIMIT 10

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

Out[29]:

id	name	country	latitude	longitude	population	id_1	Country	Alpha2_code	Alpha3_code	Numeric_code	Latitude	Longitude
1	Bombo	UGA	0.5833	32.5333	75000	226	Uganda	UG	UGA	800	1	32
2	Fort Portal	UGA	0.671	30.275	42670	226	Uganda	UG	UGA	800	1	32
3	Potenza	ITA	40.642	15.799	69060	106	Italy	IT	ITA	380	42.8333	12.8333
4	Campobasso	ITA	41.563	14.656	50762	106	Italy	IT	ITA	380	42.8333	12.8333
5	Aosta	ITA	45.737	7.315	34062	106	Italy	IT	ITA	380	42.8333	12.8333
6	Mariehamn	ALD	60.097	19.949	10682	None	None	None	None	None	None	None
7	Ramallah	PSE	31.90294	35.20621	24599	167	Palestinian Territory, Occupied	PS	PSE	275	32	35.25
8	Vatican City	VAT	41.90001	12.44781	832	94	Holy See (Vatican City State)	VA	VAT	336	41.9	12.45
9	Poitier	FRA	46.58329	0.33328	85960	72	France	FR	FRA	250	46	2
10	Clermont-Ferrand	FRA	45.77998	3.08001	233050	72	France	FR	FRA	250	46	2

Union and Intersection¶

In [30]:

Copied!





%%sql

SELECT COUNT(country) FROM (
    SELECT country FROM cities
    INTERSECT 
    SELECT "Alpha3_code" FROM countries
) AS intersection_result;
%%sql

SELECT COUNT(country) FROM (
    SELECT country FROM cities
    INTERSECT 
    SELECT "Alpha3_code" FROM countries
) AS intersection_result;

 * postgresql://postgres:***@localhost/sdb
1 rows affected.

Out[30]:

count
196

In [31]:

Copied!





%%sql

SELECT COUNT(country) FROM (
    SELECT country FROM cities
    UNION 
    SELECT "Alpha3_code" FROM countries
) AS union_result;
%%sql

SELECT COUNT(country) FROM (
    SELECT country FROM cities
    UNION 
    SELECT "Alpha3_code" FROM countries
) AS union_result;

 * postgresql://postgres:***@localhost/sdb
1 rows affected.

Out[31]:

count
247

In [32]:

Copied!

%%sql

SELECT COUNT(country) AS difference_result  FROM cities
WHERE country NOT IN (SELECT "Alpha3_code" FROM countries);
%%sql

SELECT COUNT(country) AS difference_result  FROM cities
WHERE country NOT IN (SELECT "Alpha3_code" FROM countries);

 * postgresql://postgres:***@localhost/sdb
1 rows affected.

Out[32]:

difference_result
5

Aggregation¶

Group By¶

In [33]:

Copied!





%%sql

SELECT COUNT(name), country 
FROM cities 
GROUP BY country 
ORDER BY COUNT(name) DESC
LIMIT 10
%%sql

SELECT COUNT(name), country 
FROM cities 
GROUP BY country 
ORDER BY COUNT(name) DESC
LIMIT 10

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

Out[33]:

count	country
114	USA
100	CHN
81	RUS
69	IND
46	BRA
45	CAN
40	ATA
36	AUS
30	FRA
27	MEX

In [34]:

Copied!





%%sql

SELECT countries."Country", COUNT(name)
FROM cities
LEFT JOIN countries ON cities.country = countries."Alpha3_code"
GROUP BY countries."Country"
ORDER BY COUNT(name) DESC
LIMIT 10
%%sql

SELECT countries."Country", COUNT(name)
FROM cities
LEFT JOIN countries ON cities.country = countries."Alpha3_code"
GROUP BY countries."Country"
ORDER BY COUNT(name) DESC
LIMIT 10

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

Out[34]:

Country	count
United States	114
China	100
Russia	81
India	69
Brazil	46
Canada	45
Antarctica	40
Australia	36
France	30
Mexico	27

Having¶

In [35]:

Copied!





%%sql 

SELECT COUNT(name), countries."Country"
FROM cities
LEFT JOIN countries ON cities.country = countries."Alpha3_code"
GROUP BY countries."Country"
HAVING COUNT(name) > 40
ORDER BY COUNT(name) DESC
%%sql 

SELECT COUNT(name), countries."Country"
FROM cities
LEFT JOIN countries ON cities.country = countries."Alpha3_code"
GROUP BY countries."Country"
HAVING COUNT(name) > 40
ORDER BY COUNT(name) DESC

 * postgresql://postgres:***@localhost/sdb
6 rows affected.

Out[35]:

count	Country
114	United States
100	China
81	Russia
69	India
46	Brazil
45	Canada

In [36]:

Copied!





%%sql 

SELECT COUNT(name), country
FROM cities
GROUP BY country
HAVING COUNT(name) > 40
ORDER BY COUNT(name) DESC
%%sql 

SELECT COUNT(name), country
FROM cities
GROUP BY country
HAVING COUNT(name) > 40
ORDER BY COUNT(name) DESC

 * postgresql://postgres:***@localhost/sdb
6 rows affected.

Out[36]:

count	country
114	USA
100	CHN
81	RUS
69	IND
46	BRA
45	CAN

Conditional statements¶

In [37]:

Copied!





%%sql

SELECT name, population,
CASE
    WHEN CAST(population AS INTEGER) > 10000000 THEN 'Megacity'
    WHEN CAST(population AS INTEGER) > 1000000 THEN 'Large city'
    ELSE 'Small city'
END AS category
FROM cities
LIMIT 20
%%sql

SELECT name, population,
CASE
    WHEN CAST(population AS INTEGER) > 10000000 THEN 'Megacity'
    WHEN CAST(population AS INTEGER) > 1000000 THEN 'Large city'
    ELSE 'Small city'
END AS category
FROM cities
LIMIT 20

 * postgresql://postgres:***@localhost/sdb
20 rows affected.

Out[37]:

name	population	category
Bombo	75000	Small city
Fort Portal	42670	Small city
Potenza	69060	Small city
Campobasso	50762	Small city
Aosta	34062	Small city
Mariehamn	10682	Small city
Ramallah	24599	Small city
Vatican City	832	Small city
Poitier	85960	Small city
Clermont-Ferrand	233050	Small city
Besancon	128426	Small city
Artigas Base	60	Small city
Chipata	85963	Small city
Jinja	301619	Small city
Arua	250000	Small city
Mbale	402368	Small city
Moroto	371	Small city
Masaka	65373	Small city
Mbarara	83700	Small city
Otjiwarongo	24815	Small city

Saving results¶

In [38]:

Copied!





%%sql

SELECT *
INTO cities_usa
FROM cities
WHERE country = 'USA'
%%sql

SELECT *
INTO cities_usa
FROM cities
WHERE country = 'USA'

 * postgresql://postgres:***@localhost/sdb
(psycopg2.errors.DuplicateTable) relation "cities_usa" already exists

[SQL: SELECT *
INTO cities_usa
FROM cities
WHERE country = 'USA']
(Background on this error at: http://sqlalche.me/e/14/f405)

SQL Comments¶

Single line coments¶

In [39]:

Copied!

%%sql

SELECT * FROM cities LIMIT 10 -- This is a comment;
%%sql

SELECT * FROM cities LIMIT 10 -- This is a comment;

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

Out[39]:

id	name	country	latitude	longitude	population
1	Bombo	UGA	0.5833	32.5333	75000
2	Fort Portal	UGA	0.671	30.275	42670
3	Potenza	ITA	40.642	15.799	69060
4	Campobasso	ITA	41.563	14.656	50762
5	Aosta	ITA	45.737	7.315	34062
6	Mariehamn	ALD	60.097	19.949	10682
7	Ramallah	PSE	31.90294	35.20621	24599
8	Vatican City	VAT	41.90001	12.44781	832
9	Poitier	FRA	46.58329	0.33328	85960
10	Clermont-Ferrand	FRA	45.77998	3.08001	233050