Databases 2つのテーブル、結合、INNER JOIN、合計、平均、最大、最小
Practical Programming: An Introduction to Computer Science Using Python 3.6 (Paul Gries(著)、Jennifer Campbell(著)、Jason Montojo(著)、Pragmatic Bookshelf)のChapter 17(Databases)、Exercise 2-d、f、g、h.の解答を求めてみる。
コード
#!/usr/bin/env python3
import sqlite3 as sql
# cur.execute('''
# CREATE TABLE Density
# (ProvinceOrTerritory TEXT, Population INTGER, LandArea REAL)
# ''')
# cur.execute('''
# CREATE TABLE Capitals
# (ProvinceOrTerritory TEXT, Capital TEXT, Population Integer)
# ''')
print('2.')
con = sql.connect('census.db')
cur = con.cursor()
print('Density')
cur.execute('''select * from density''')
for row in cur.fetchall():
print(row)
print('Capitals')
cur.execute('''select * from capitals''')
for row in cur.fetchall():
print(row)
print('d.')
cur.execute('''
select density.ProvinceOrTerritory
from density inner join capitals
where density.ProvinceOrTerritory == capitals.ProvinceOrTerritory
and density.population / density.landarea < 2
and capitals.population > 500000
''')
for row in cur.fetchall():
print(row)
print('e.')
cur.execute('''
select sum(landarea)
from density
''')
for row in cur.fetchall():
print(row)
print('f.')
cur.execute('''
select AVG(population)
from capitals
''')
for row in cur.fetchall():
print(row)
print('g.')
cur.execute('''
select MIN(population) from capitals
''')
for row in cur.fetchall():
print(row)
cur.close()
con.close()
入出力結果
% ./sample2.py
2.
Density
('Newfoundland and Labrador', 512930, 370501.69)
('Prince Edward Island', 135294, 5684.39)
('Nova Scotia', 908007, 52917.43)
('New Brunswick', 729498, 71355.67)
('Quebec', 7237479, 1357743.08)
('Ontario', 11410046, 907655.59)
('Manitoba', 1119583, 551937.87)
('Saskatchewan', 978933, 586561.35)
('Alberta', 2974807, 639987.12)
('British Columbia', 3907738, 926492.48)
('Yukon Territory', 28674, 474706.97)
('Northwest Territories', 37360, 1141108.37)
('Nunavut', 26745, 1925460.18)
Capitals
('Newfoundland and Labrador', "St. John's", 172918)
('Prince Edward Island', 'Charlottetown', 58358)
('Nova Scotia', 'Halifax', 359183)
('New Brunswick', 'Fredericton', 81346)
('Quebec', 'Qeubec City', 682757)
('Ontario', 'Toronto', 4682897)
('Manitoba', 'Winnipeg', 671274)
('Saskatchewan', 'Regina', 192800)
('Alberta', 'Edmonton', 937845)
('British Columbia', 'Victoria', 311902)
('Yukon Territory', 'Whitehorse', 21405)
('Northwest Territories', 'Yellowknife', 16541)
('Nunavut', 'Iqaluit', 5236)
d.
e.
(9012112.19,)
f.
(630343.2307692308,)
g.
(5236,)
%