Chapter 15: Databases (Solutions to Even-Numbered Exercises)

Question 2

CREATE TABLE Density(name TEXT, population REAL, area REAL);
INSERT INTO Density VALUES("Newfoundland and Labrador", 512930, 370501.69);
INSERT INTO Density VALUES("Prince Edward Island", 135294, 5684.39);
INSERT INTO Density VALUES("Nova Scotia", 908007, 52917.43);
INSERT INTO Density VALUES("New Brunswick", 729498, 71355.67);
INSERT INTO Density VALUES("Quebec", 7237479, 1357743.08);
INSERT INTO Density VALUES("Ontario", 11410046, 907655.59);
INSERT INTO Density VALUES("Manitoba", 1119583, 551937.87);
INSERT INTO Density VALUES("Saskatchewan", 978933, 586561.35);
INSERT INTO Density VALUES("Alberta", 2974807, 639987.12);
INSERT INTO Density VALUES("British Columbia", 3907738, 926492.48);
INSERT INTO Density VALUES("Yukon Territory", 28674, 474706.97);
INSERT INTO Density VALUES("Northwest Territories", 37360, 1141108.37);
INSERT INTO Density VALUES("Nunavut", 26745, 1925460.18);

CREATE TABLE Capital(provter TEXT, capital TEXT, population REAL);
INSERT INTO Capital VALUES("Newfoundland and Labrador", "St. John's", 172918);
INSERT INTO Capital VALUES("Prince Edward Island", "Charlottetown", 58358);
INSERT INTO Capital VALUES("Nova Scotia", "Halifax", 359183);
INSERT INTO Capital VALUES("New Brunswick", "Fredericton", 81346);
INSERT INTO Capital VALUES("Quebec", "Quebec", 682757);
INSERT INTO Capital VALUES("Ontario", "Toronto", 4682897);
INSERT INTO Capital VALUES("Manitoba", "Winnipeg", 671274);
INSERT INTO Capital VALUES("Saskatchewan", "Regina", 192800);
INSERT INTO Capital VALUES("Alberta", "Edmonton", 937845);
INSERT INTO Capital VALUES("British Columbia", "Victoria", 311902);
INSERT INTO Capital VALUES("Yukon Territory", "Whitehorse", 21405);
INSERT INTO Capital VALUES("Northwest Territories", "Yellowknife", 16541);
INSERT INTO Capital VALUES("Nunavut", "Iqaluit", 5236);

a)

SELECT * FROM Capital

b)

SELECT Density.population, Capital.population
FROM Density JOIN Capital
WHERE Density.name = Capital.provter

c)

SELECT Density.area
FROM Density JOIN Capital
WHERE (Density.name = Capital.provter)
AND (Capital.population > 100000)

d)

SELECT Density.name
FROM Capital JOIN Density
WHERE (Density.name = Capital.provter)
AND ((Density.population/Density.area) < 2.0)
AND (Capital.population > 500000)

e)

SELECT SUM(area) FROM Density

f)

SELECT AVG(population) FROM Capital

g)

SELECT MIN(population) FROM Capital

h)

SELECT MAX(population) FROM Density

i)

SELECT A.name, B.name
FROM Density A JOIN Density B
WHERE (A.name < B.name)
AND ABS((A.population/A.area) - (B.population/B.area)) <= 0.5
ORDER BY A.name, B.name

Question 4

import sqlite3 as dbapi

def run_query(db, query, args=None):
    '''Return the results of running the given query on database db.'''

    con = dbapi.connect(db)
    cur = con.cursor()
    if args is None:
        cur.execute(query)
    else:
        cur.execute(query, args)
    data = cur.fetchall()
    cur.close()
    con.close()
    return data