SQL - 高度なSELECT文: 新たな目でデータを見る(ORDER BY句)

Head First SQL
頭とからだで覚えるSQLの基本
(オライリージャパン)
Lynn Beighley (著)
佐藤 直生 (監訳), 松永 多苗子 (翻訳)

開発環境

• OS X Mavericks - Apple、たまにFreeBSD 10(OS)
• Emacs (CUI)、BBEdit - Bare Bones Software, Inc. (GUI) (Text Editor)
• Python 3.4(プログラミング言語)
• SQLite (RDBMS(Relational Database Management System))

Head First SQL ―頭とからだで覚えるSQLの基本 (Lynn Beighley(著)、 佐藤 直生 (監訳)、 松永 多苗子 (翻訳)、オライリージャパン)の6章(高度なSELECT文: 新たな目でデータを見る)、自分で考えてみよう(p.263)を解いてみる。

自分で考えてみよう(p.263)

コード(BBEdit, Emacs)

sample263.py

#!/usr/bin/env python3
#-*- coding: utf-8 -*-

import sqlite3

connection = sqlite3.connect('chapter6.sqlite')
cursor = connection.cursor()

def p_all(table):
    cursor.execute("""SELECT * FROM {0}""".format(table))
    print(tuple(map(lambda header: header[0], cursor.description)))
    p(table)

def p(msg):
    print(msg)
    for row in cursor.fetchall():
        print(row)

p_all('cookie_sales')

cursor.execute("""
SELECT first_name, sales
FROM cookie_sales
ORDER BY first_name
""")

p('ORDER BY first_name')

connection.close()

入出力結果(Terminal, IPython)

$ ./sample263.py
('ID', 'first_name', 'sales', 'sale_date')
cookie_sales
(1, 'Lindsey', 32.02, '2007-03-12')
(2, 'Nicole', 26.53, '2007-03-12')
(3, 'Britney', 11.25, '2007-03-12')
(4, 'Ashley', 18.96, '2007-03-12')
(5, 'Lindsey', 9.16, '2007-03-11')
(6, 'Nicole', 1.52, '2007-03-11')
(7, 'Britney', 43.21, '2007-03-11')
(8, 'Ashley', 8.05, '2007-03-11')
(9, 'Lindsey', 17.62, '2007-03-10')
(10, 'Nicole', 24.19, '2007-03-10')
(11, 'Britney', 3.4, '2007-03-10')
(12, 'Ashley', 15.21, '2007-03-10')
(13, 'Lindsey', 0, '2007-03-09')
(14, 'Nicole', 31.99, '2007-03-09')
(15, 'Britney', 2.58, '2007-03-09')
(16, 'Ashley', 0, '2007-03-09')
(17, 'Lindsey', 2.34, '2007-03-08')
(18, 'Nicole', 13.44, '2007-03-08')
(19, 'Britney', 8.78, '2007-03-08')
(20, 'Ashley', 26.82, '2007-03-08')
(21, 'Lindsey', 3.71, '2007-03-07')
(22, 'Nicole', 0.56, '2007-03-07')
(23, 'Britney', 34.19, '2007-03-07')
(24, 'Ashley', 7.77, '2007-03-07')
(25, 'Lindsey', 16.23, '2007-03-06')
(26, 'Nicole', 0, '2007-03-06')
(27, 'Britney', 4.5, '2007-03-06')
(28, 'Ashley', 19.22, '2007-03-06')
ORDER BY first_name
('Ashley', 18.96)
('Ashley', 8.05)
('Ashley', 15.21)
('Ashley', 0)
('Ashley', 26.82)
('Ashley', 7.77)
('Ashley', 19.22)
('Britney', 11.25)
('Britney', 43.21)
('Britney', 3.4)
('Britney', 2.58)
('Britney', 8.78)
('Britney', 34.19)
('Britney', 4.5)
('Lindsey', 32.02)
('Lindsey', 9.16)
('Lindsey', 17.62)
('Lindsey', 0)
('Lindsey', 2.34)
('Lindsey', 3.71)
('Lindsey', 16.23)
('Nicole', 26.53)
('Nicole', 1.52)
('Nicole', 24.19)
('Nicole', 31.99)
('Nicole', 13.44)
('Nicole', 0.56)
('Nicole', 0)
$