スッキリわかるSQL入門第2版のデータ構築


参考書籍
[スッキリわかるSQL入門第2版](https://www.amazon.co.jp/%E3%82%B9%E3%83%83%E3%82%AD%E3%83%AA%E3%82%8F%E3%81%8B%E3%82%8BSQL%E5%85%A5%E9%96%80-%E7%AC%AC2%E7%89%88-%E3%83%89%E3%83%AA%E3%83%AB222%E5%95%8F%E4%BB%98%E3%81%8D-%E3%82%B9%E3%83%83%E3%82%AD%E3%83%AA%E3%82%B7%E3%83%AA%E3%83%BC%E3%82%BA-%E4%B8%AD%E5%B1%B1%E6%B8%85%E5%96%AC/dp/4295005096/ref=sr_1_1?adgrpid=65391235197&gclid=EAIaIQobChMIzYq5p4uM-gIVzXeLCh3C4gjZEAAYASAAEgLAnfD_BwE&hvadid=618553198077&hvdev=c&hvlocphy=1009285&hvnetw=g&hvqmt=e&hvrand=6029834748685608343&hvtargid=kwd-641895382795&hydadcr=27268_14598057&jp-ad-ap=0&keywords=%E3%82%B9%E3%83%83%E3%82%AD%E3%83%AA%E3%82%8F%E3%81%8B%E3%82%8Bsql%E5%85%A5%E9%96%80%E7%AC%AC2%E7%89%88&qid=1662876936&sr=8-1)
中山清喬  (著)
飯田理恵子 (著)
株式会社フレアリンク (監修)
ISBN-13:‎978-4295005094


スッキリわかるSQL入門第2版のデータ構築を
Python/sqlite3環境下でしたので残しておく


```python:make家計簿.py
import sqlite3

conn = sqlite3.connect("test.db", isolation_level=None)

#テーブル作成
sql = """
    CREATE TABLE IF NOT EXISTS 家計簿 (
        日付 TEXT,
        費目 VARCHAR(20),
        メモ VARCHAR(100),
        入金額 INTEGER,
        出金額 INTEGER
    );
    """
conn.execute(sql)


#データ挿入
sql = "INSERT INTO 家計簿 VALUES (?,?,?,?,?)"
data = [
        ("2018-02-03","食費","コーヒーを購入",0,380),
        ("2018-02-10","給料","1月の給料",28000,0),
        ("2018-02-11","教育娯楽費","書籍を購入",0,2800),
        ("2018-02-14","交際費","同期会の回避",0,5000),
        ("2018-02-18","水道光熱費","1月の電気代",0,7560)
        ]
conn.executemany(sql, data)


#データ確認
c=conn.cursor()
c.execute("SELECT * FROM 家計簿")

for row in c:
    print(row)
    

conn.close()
```






```python:make家計簿アーカイブ.py
import sqlite3

conn = sqlite3.connect("test.db", isolation_level=None)

#テーブル作成
sql = """
    CREATE TABLE IF NOT EXISTS 家計簿アーカイブ (
        日付 TEXT,
        費目 VARCHAR(20),
        メモ VARCHAR(100),
        入金額 INTEGER,
        出金額 INTEGER
    );
    """
conn.execute(sql)


#データ挿入
sql = "INSERT INTO 家計簿アーカイブ VALUES (?,?,?,?,?)"
data = [
        ("2017-12-10","給料","11月の給料",280000,0),
        ("2017-12-18","水道光熱費","水道代",0,4200),
        ("2017-12-24","食費","レストランみやび",0,5000),
        ("2017-12-25","居住費","1月の家賃支払い",0,80000),
        ("2018-01-10","給料","12月の給料",280000,0),
        ("2018-01-13","教育娯楽費","スッキリシネマズ",0,1800),
        ("2018-01-13","食費","新年会",0,5000),
        ("2018-01-25","居住費","2月の家賃支払い",0,80000),
        ]
conn.executemany(sql, data)


#データ確認
c=conn.cursor()
c.execute("SELECT * FROM 家計簿アーカイブ")

for row in c:
    print(row)


conn.close()
```









```python:make家計簿集計.py
import sqlite3

conn = sqlite3.connect("test.db", isolation_level=None)

#テーブル作成
sql = """
    CREATE TABLE IF NOT EXISTS 家計簿集計 (
        費目 VARCHAR(20),
        合計 INTEGER,
        平均 INTEGER,
        最大 INTEGER,
        最小 INTEGER,
        回数 INTEGER
    );
    """
conn.execute(sql)

#データ挿入
##出金データ
sql = """
    INSERT INTO 家計簿集計 
        SELECT 費目,SUM(出金額),AVG(出金額),MAX(出金額),MIN(出金額),COUNT(出金額)
        FROM (
            SELECT * FROM 家計簿
            UNION
            SELECT * FROM 家計簿アーカイブ
            )
        GROUP BY 費目
        HAVING SUM(出金額) > 0
    """
conn.execute(sql)

##入金データ
sql = """
    INSERT INTO 家計簿集計 
        SELECT 費目,SUM(入金額),AVG(入金額),MAX(入金額),MIN(入金額),COUNT(入金額)
        FROM (
            SELECT * FROM 家計簿
            UNION
            SELECT * FROM 家計簿アーカイブ
            )
        GROUP BY 費目
        HAVING SUM(入金額) > 0
    """
conn.execute(sql)

#データ確認
c=conn.cursor()
c.execute("SELECT * FROM 家計簿集計")

for row in c:
    print(row)

conn.close()
```

- 家計簿集計は家計簿と家計簿アーカイブを合わせたものだと思っていたが、微妙にデータが違うので修正する(家計簿には交際費の費目があるが家計簿集計にはないなど)

```python:modify家計簿集計.py
import sqlite3

conn = sqlite3.connect("test.db", isolation_level=None)

#データ修正1
sql = """
    DELETE 
    FROM 家計簿集計 
    WHERE 費目 = '交際費'
    """
conn.execute(sql)

#データ修正2
sql = """
    UPDATE 家計簿集計 
    SET 回数=3
    WHERE 費目 = '居住費'
    """
conn.execute(sql)

#データ確認
c=conn.cursor()
c.execute("SELECT * FROM 家計簿集計")

for row in c:
    print(row)

conn.close()
```