{ "cells": [ { "cell_type": "markdown", "id": "lightweight-fruit", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "# SQL 的五十道練習：初學者友善的資料庫入門\n", "\n", "> 從資料表選擇" ] }, { "cell_type": "markdown", "id": "88a14a91", "metadata": {}, "source": [ "讀者如果是資料科學的初學者，可以略過下述的程式碼；讀者如果不是資料科學的初學者，欲使用 JupyterLab 執行本章節內容，必須先執行下述程式碼載入所需模組與連接資料庫。" ] }, { "cell_type": "code", "execution_count": 1, "id": "matched-spyware", "metadata": { "slideshow": { "slide_type": "skip" } }, "outputs": [], "source": [ "%LOAD sqlite3 db=../databases/imdb.db timeout=2 shared_cache=true" ] }, { "cell_type": "code", "execution_count": 2, "id": "be32a1df", "metadata": {}, "outputs": [], "source": [ "ATTACH \"../databases/nba.db\" AS nba;" ] }, { "cell_type": "code", "execution_count": 3, "id": "26767006", "metadata": {}, "outputs": [], "source": [ "ATTACH \"../databases/twElection2020.db\" AS twElection2020;" ] }, { "cell_type": "code", "execution_count": 4, "id": "95c21d81", "metadata": {}, "outputs": [], "source": [ "ATTACH \"../databases/covid19.db\" AS covid19;" ] }, { "cell_type": "markdown", "id": "threaded-police", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "## 複習一下\n", "\n", "在第二章「建立學習環境」我們寫作了 SQL 敘述完成哈囉世界、檢視學習資料庫中第一個資料表（依照英文字母順序排列）的「前五列、所有欄」，藉此確認學習環境能夠妥善運行。" ] }, { "cell_type": "code", "execution_count": 5, "id": "3e4a3340", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
'Hello, World!'
Hello, World!
\n", "1 row in set (0.00 sec)" ], "text/plain": [ "+-----------------+\n", "| 'Hello, World!' |\n", "+-----------------+\n", "| Hello, World! |\n", "+-----------------+\n", "1 row in set (0.00 sec)" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT 'Hello, World!';" ] }, { "cell_type": "code", "execution_count": 6, "id": "wired-consciousness", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
idname
1Aamir Khan
2Aaron Eckhart
3Aaron Lazar
4Abbas-Ali Roomandi
5Abbey Lee
\n", "5 rows in set (0.00 sec)" ], "text/plain": [ "+----+--------------------+\n", "| id | name |\n", "+----+--------------------+\n", "| 1 | Aamir Khan |\n", "+----+--------------------+\n", "| 2 | Aaron Eckhart |\n", "+----+--------------------+\n", "| 3 | Aaron Lazar |\n", "+----+--------------------+\n", "| 4 | Abbas-Ali Roomandi |\n", "+----+--------------------+\n", "| 5 | Abbey Lee |\n", "+----+--------------------+\n", "5 rows in set (0.00 sec)" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT *\n", " FROM actors\n", " LIMIT 5;" ] }, { "cell_type": "markdown", "id": "b7d073ce", "metadata": {}, "source": [ "在第二章「建立學習環境」我們寫作了 SQL 敘述查詢四個學習資料庫每一個資料表的元資料（Metadata）來獲得每一欄的資訊。" ] }, { "cell_type": "code", "execution_count": 7, "id": "6fd2756c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
cidnametypenotnulldflt_valuepk
0idINTEGER0NULL1
1nameTEXT0NULL0
\n", "2 rows in set (0.00 sec)" ], "text/plain": [ "+-----+------+---------+---------+------------+----+\n", "| cid | name | type | notnull | dflt_value | pk |\n", "+-----+------+---------+---------+------------+----+\n", "| 0 | id | INTEGER | 0 | NULL | 1 |\n", "+-----+------+---------+---------+------------+----+\n", "| 1 | name | TEXT | 0 | NULL | 0 |\n", "+-----+------+---------+---------+------------+----+\n", "2 rows in set (0.00 sec)" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT *\n", " FROM PRAGMA_TABLE_INFO('actors');" ] }, { "cell_type": "markdown", "id": "55398fe0", "metadata": {}, "source": [ "## SQL 敘述的組成\n", "\n", "藉由觀察這三個 SQL 敘述，我們可以將 SQL 敘述歸納為以下幾個部分的組成：\n", "\n", "- 保留字：具有特定功能的指令，例如 `SELECT`、`FROM` 與 `LIMIT`。\n", "- 符號：具有特定功能的符號，例如 `*` 與 `;`。\n", "- 常數：由使用者給予的資料，例如 `'Hello, World!'`。\n", "- 函數：具有特定邏輯的輸入與輸出對應，例如 `PRAGMA_TABLE_INFO()`。\n", "\n", "其中 `SELECT` 是「選擇」欄的保留字，`FROM` 是指定「從」哪個資料表查詢，`LIMIT m` 是讓查詢結果顯示前 `m` 列，`*` 表示「所有欄」，`;` 表示一段 SQL 敘述的結束。我們習慣以 `(m, n)` 來描述一個具有 `m` 列、`n` 欄的資料表或者查詢結果，其中 `m` 不包含欄名那一列，舉例來說 `SELECT 'Hello, World!';` 的查詢結果是 `(1, 1)`。" ] }, { "cell_type": "code", "execution_count": 8, "id": "1877f829", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
'Hello, World!'
Hello, World!
\n", "1 row in set (0.00 sec)" ], "text/plain": [ "+-----------------+\n", "| 'Hello, World!' |\n", "+-----------------+\n", "| Hello, World! |\n", "+-----------------+\n", "1 row in set (0.00 sec)" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT 'Hello, World!';" ] }, { "cell_type": "markdown", "id": "3610b497", "metadata": {}, "source": [ "`SELECT * FROM actors LIMIT 5;` 的查詢結果是 `(5, 2)`" ] }, { "cell_type": "code", "execution_count": 9, "id": "7a93d79b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
idname
1Aamir Khan
2Aaron Eckhart
3Aaron Lazar
4Abbas-Ali Roomandi
5Abbey Lee
\n", "5 rows in set (0.00 sec)" ], "text/plain": [ "+----+--------------------+\n", "| id | name |\n", "+----+--------------------+\n", "| 1 | Aamir Khan |\n", "+----+--------------------+\n", "| 2 | Aaron Eckhart |\n", "+----+--------------------+\n", "| 3 | Aaron Lazar |\n", "+----+--------------------+\n", "| 4 | Abbas-Ali Roomandi |\n", "+----+--------------------+\n", "| 5 | Abbey Lee |\n", "+----+--------------------+\n", "5 rows in set (0.00 sec)" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT *\n", " FROM actors\n", " LIMIT 5;" ] }, { "cell_type": "markdown", "id": "lasting-fantasy", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "## 查詢結果顯示常數：`SELECT constants`\n", "\n", "使用單獨存在的 `SELECT` 保留字指定希望在查詢結果中顯示的常數，常用的常數類別有四種，分別是整數、浮點數、文字與空值，我們可以使用 `TYPEOF()` 函數顯示常數或者資料表欄位的類別，當 `SELECT` 之後有不只一個資料的時候就用逗號 `,` 分隔。\n", "\n", "|常數類別|範例|\n", "|:------|:---|\n", "|整數 `integer`|`7`, `19`, `5566`, ...etc.|\n", "|浮點數 `real`|`2.718`, `3.14159`, ...etc.|\n", "|文字 `text`|`'Hello, World!'`, `'SQL'`, ...etc.|\n", "|空值 `null`|`NULL`|" ] }, { "cell_type": "code", "execution_count": 10, "id": "350dc657", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
5566TYPEOF(5566)
5566integer
\n", "1 row in set (0.00 sec)" ], "text/plain": [ "+------+--------------+\n", "| 5566 | TYPEOF(5566) |\n", "+------+--------------+\n", "| 5566 | integer |\n", "+------+--------------+\n", "1 row in set (0.00 sec)" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT 5566,\n", " TYPEOF(5566);" ] }, { "cell_type": "code", "execution_count": 11, "id": "4cca6806", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
2.718TYPEOF(2.718)
2.718real
\n", "1 row in set (0.00 sec)" ], "text/plain": [ "+-------+---------------+\n", "| 2.718 | TYPEOF(2.718) |\n", "+-------+---------------+\n", "| 2.718 | real |\n", "+-------+---------------+\n", "1 row in set (0.00 sec)" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT 2.718,\n", " TYPEOF(2.718);" ] }, { "cell_type": "code", "execution_count": 12, "id": "e35079a8", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
'Hello, World!'TYPEOF('Hello, World!')
Hello, World!text
\n", "1 row in set (0.00 sec)" ], "text/plain": [ "+-----------------+-------------------------+\n", "| 'Hello, World!' | TYPEOF('Hello, World!') |\n", "+-----------------+-------------------------+\n", "| Hello, World! | text |\n", "+-----------------+-------------------------+\n", "1 row in set (0.00 sec)" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT 'Hello, World!',\n", " TYPEOF('Hello, World!');" ] }, { "cell_type": "code", "execution_count": 13, "id": "f9eab8d5", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
NULLTYPEOF(NULL)
NULLnull
\n", "1 row in set (0.00 sec)" ], "text/plain": [ "+------+--------------+\n", "| NULL | TYPEOF(NULL) |\n", "+------+--------------+\n", "| NULL | null |\n", "+------+--------------+\n", "1 row in set (0.00 sec)" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT NULL,\n", " TYPEOF(NULL);" ] }, { "cell_type": "markdown", "id": "cfc222c9", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "## 在敘述中添加註解\n", "\n", "寫作 SQL 敘述會有需要在其中添加註解（Comments）的時候，註解是不會被關聯式資料庫管理系統執行的說明文字，通常作為與工作上的同事、或者未來的自己（看不懂自己從前所寫的程式是相當常見的），說明 SQL 敘述中值得注意的事項或者邏輯，常用的註解形式有單行註解、行末註解與多行註解。\n", "\n", "單行註解：用兩個減號 `--` 標註並且單獨存在一行。\n", "\n", "```sql\n", "-- single line comment\n", "SELECT columns\n", " FROM table\n", " LIMIT m;\n", "```\n", "\n", "行末註解：用兩個減號 `--` 標註，但是置放於一行 SQL 敘述的句尾。\n", "\n", "```sql\n", "SELECT columns -- end of line comment\n", " FROM table -- end of line comment\n", " LIMIT m; -- end of line comment\n", "```\n", "\n", "多行註解：用 `/*` 開頭、`*/` 結尾來標註。\n", "\n", "```sql\n", "/*\n", "multiple-line comments...\n", "multiple-line comments...\n", "multiple-line comments...\n", "*/\n", "SELECT columns\n", " FROM table\n", " LIMIT m;\n", "```" ] }, { "cell_type": "markdown", "id": "becfe6d3", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "## 為查詢結果限制顯示列數：`LIMIT`\n", "\n", "```sql\n", "SELECT columns\n", " FROM table\n", " LIMIT m;\n", "```\n", "\n", "因為資料表中的觀測值列數可能都有很多筆，為了在有限的版面顯示，我們可以透過 `LIMIT` 保留字讓查詢結果顯示前 `m` 列即可。" ] }, { "cell_type": "code", "execution_count": 14, "id": "b1880641", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
idtitlerelease_yearratingdirectorruntime
1The Shawshank Redemption19949.3Frank Darabont142
\n", "1 row in set (0.00 sec)" ], "text/plain": [ "+----+--------------------------+--------------+--------+----------------+---------+\n", "| id | title | release_year | rating | director | runtime |\n", "+----+--------------------------+--------------+--------+----------------+---------+\n", "| 1 | The Shawshank Redemption | 1994 | 9.3 | Frank Darabont | 142 |\n", "+----+--------------------------+--------------+--------+----------------+---------+\n", "1 row in set (0.00 sec)" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT *\n", " FROM movies\n", " LIMIT 1;" ] }, { "cell_type": "code", "execution_count": 15, "id": "98e05077", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
idtitlerelease_yearratingdirectorruntime
1The Shawshank Redemption19949.3Frank Darabont142
2The Godfather19729.2Francis Ford Coppola175
3The Dark Knight20089Christopher Nolan152
\n", "3 rows in set (0.00 sec)" ], "text/plain": [ "+----+--------------------------+--------------+--------+----------------------+---------+\n", "| id | title | release_year | rating | director | runtime |\n", "+----+--------------------------+--------------+--------+----------------------+---------+\n", "| 1 | The Shawshank Redemption | 1994 | 9.3 | Frank Darabont | 142 |\n", "+----+--------------------------+--------------+--------+----------------------+---------+\n", "| 2 | The Godfather | 1972 | 9.2 | Francis Ford Coppola | 175 |\n", "+----+--------------------------+--------------+--------+----------------------+---------+\n", "| 3 | The Dark Knight | 2008 | 9 | Christopher Nolan | 152 |\n", "+----+--------------------------+--------------+--------+----------------------+---------+\n", "3 rows in set (0.00 sec)" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT *\n", " FROM movies\n", " LIMIT 3;" ] }, { "cell_type": "markdown", "id": "69e9a84b", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "## 選擇資料表欄位：`SELECT columns FROM table;`\n", "\n", "從資料表選擇欄位的時候使用 `SELECT` 與 `FROM` 保留字分別指定欄位名稱與資料表名稱，若希望從資料表選擇「所有」欄位，可以使用星號（`*`）達成。" ] }, { "cell_type": "code", "execution_count": 16, "id": "3bc5dd88", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
idtitlerelease_yearratingdirectorruntime
1The Shawshank Redemption19949.3Frank Darabont142
2The Godfather19729.2Francis Ford Coppola175
3The Dark Knight20089Christopher Nolan152
4The Godfather Part II19749Francis Ford Coppola202
512 Angry Men19579Sidney Lumet96
\n", "5 rows in set (0.00 sec)" ], "text/plain": [ "+----+--------------------------+--------------+--------+----------------------+---------+\n", "| id | title | release_year | rating | director | runtime |\n", "+----+--------------------------+--------------+--------+----------------------+---------+\n", "| 1 | The Shawshank Redemption | 1994 | 9.3 | Frank Darabont | 142 |\n", "+----+--------------------------+--------------+--------+----------------------+---------+\n", "| 2 | The Godfather | 1972 | 9.2 | Francis Ford Coppola | 175 |\n", "+----+--------------------------+--------------+--------+----------------------+---------+\n", "| 3 | The Dark Knight | 2008 | 9 | Christopher Nolan | 152 |\n", "+----+--------------------------+--------------+--------+----------------------+---------+\n", "| 4 | The Godfather Part II | 1974 | 9 | Francis Ford Coppola | 202 |\n", "+----+--------------------------+--------------+--------+----------------------+---------+\n", "| 5 | 12 Angry Men | 1957 | 9 | Sidney Lumet | 96 |\n", "+----+--------------------------+--------------+--------+----------------------+---------+\n", "5 rows in set (0.00 sec)" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT *\n", " FROM movies\n", " LIMIT 5;" ] }, { "cell_type": "markdown", "id": "405df71b", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "在 `SELECT` 後加入欄的名稱讓查詢結果只顯示資料表中指定的欄。" ] }, { "cell_type": "code", "execution_count": 17, "id": "ed166165", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
title
The Shawshank Redemption
The Godfather
The Dark Knight
The Godfather Part II
12 Angry Men
\n", "5 rows in set (0.00 sec)" ], "text/plain": [ "+--------------------------+\n", "| title |\n", "+--------------------------+\n", "| The Shawshank Redemption |\n", "+--------------------------+\n", "| The Godfather |\n", "+--------------------------+\n", "| The Dark Knight |\n", "+--------------------------+\n", "| The Godfather Part II |\n", "+--------------------------+\n", "| 12 Angry Men |\n", "+--------------------------+\n", "5 rows in set (0.00 sec)" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT title\n", " FROM movies\n", " LIMIT 5;" ] }, { "cell_type": "markdown", "id": "ae4e9319", "metadata": {}, "source": [ "在 `SELECT` 後加入欄的名稱讓查詢結果只顯示資料表中指定的多個欄，在不同欄名稱之間用逗號分隔。" ] }, { "cell_type": "code", "execution_count": 18, "id": "ff38507d", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
titlerelease_yeardirector
The Shawshank Redemption1994Frank Darabont
The Godfather1972Francis Ford Coppola
The Dark Knight2008Christopher Nolan
The Godfather Part II1974Francis Ford Coppola
12 Angry Men1957Sidney Lumet
\n", "5 rows in set (0.00 sec)" ], "text/plain": [ "+--------------------------+--------------+----------------------+\n", "| title | release_year | director |\n", "+--------------------------+--------------+----------------------+\n", "| The Shawshank Redemption | 1994 | Frank Darabont |\n", "+--------------------------+--------------+----------------------+\n", "| The Godfather | 1972 | Francis Ford Coppola |\n", "+--------------------------+--------------+----------------------+\n", "| The Dark Knight | 2008 | Christopher Nolan |\n", "+--------------------------+--------------+----------------------+\n", "| The Godfather Part II | 1974 | Francis Ford Coppola |\n", "+--------------------------+--------------+----------------------+\n", "| 12 Angry Men | 1957 | Sidney Lumet |\n", "+--------------------------+--------------+----------------------+\n", "5 rows in set (0.00 sec)" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT title,\n", " release_year,\n", " director\n", " FROM movies\n", " LIMIT 5;" ] }, { "cell_type": "markdown", "id": "4f96f013", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "## 為查詢結果取別名：`AS alias`\n", "\n", "```sql\n", "SELECT constants AS alias;\n", "\n", "SELECT columns AS alias\n", " FROM table;\n", "```\n", "\n", "我們可以透過 `AS` 保留字來為查詢的結果取別名，不論是常數或者是資料表的欄，都能在查詢結果中以指定的名稱顯示。" ] }, { "cell_type": "code", "execution_count": 19, "id": "48f8c259", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
hello_worldtypeof_hello_world
Hello, World!text
\n", "1 row in set (0.00 sec)" ], "text/plain": [ "+---------------+--------------------+\n", "| hello_world | typeof_hello_world |\n", "+---------------+--------------------+\n", "| Hello, World! | text |\n", "+---------------+--------------------+\n", "1 row in set (0.00 sec)" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT 'Hello, World!' AS hello_world,\n", " TYPEOF('Hello, World!') AS typeof_hello_world;" ] }, { "cell_type": "code", "execution_count": 20, "id": "c7d73226", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
moviereleased_indirected_by
The Shawshank Redemption1994Frank Darabont
The Godfather1972Francis Ford Coppola
The Dark Knight2008Christopher Nolan
The Godfather Part II1974Francis Ford Coppola
12 Angry Men1957Sidney Lumet
\n", "5 rows in set (0.00 sec)" ], "text/plain": [ "+--------------------------+-------------+----------------------+\n", "| movie | released_in | directed_by |\n", "+--------------------------+-------------+----------------------+\n", "| The Shawshank Redemption | 1994 | Frank Darabont |\n", "+--------------------------+-------------+----------------------+\n", "| The Godfather | 1972 | Francis Ford Coppola |\n", "+--------------------------+-------------+----------------------+\n", "| The Dark Knight | 2008 | Christopher Nolan |\n", "+--------------------------+-------------+----------------------+\n", "| The Godfather Part II | 1974 | Francis Ford Coppola |\n", "+--------------------------+-------------+----------------------+\n", "| 12 Angry Men | 1957 | Sidney Lumet |\n", "+--------------------------+-------------+----------------------+\n", "5 rows in set (0.00 sec)" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT title AS movie,\n", " release_year AS released_in,\n", " director AS directed_by\n", " FROM movies\n", " LIMIT 5;" ] }, { "cell_type": "markdown", "id": "591a29a7", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "## 為查詢結果剔除重複值：`DISTINCT`\n", "\n", "```sql\n", "SELECT DISTINCT columns\n", " FROM table;\n", "```\n", "\n", "我們可以透過 `DISTINCT` 保留字來為查詢的結果剔除重複值，舉例來說，在 `imdb` 資料庫的 `movies` 資料表中 `director` 欄的前 10 列可以看到重複出現的導演如 Francis Ford Coppola（執導教父三部曲）與 Peter Jackson（執導魔戒三部曲）。" ] }, { "cell_type": "code", "execution_count": 21, "id": "a487df35", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
director
Frank Darabont
Francis Ford Coppola
Christopher Nolan
Francis Ford Coppola
Sidney Lumet
Steven Spielberg
Peter Jackson
Quentin Tarantino
Peter Jackson
Sergio Leone
\n", "10 rows in set (0.00 sec)" ], "text/plain": [ "+----------------------+\n", "| director |\n", "+----------------------+\n", "| Frank Darabont |\n", "+----------------------+\n", "| Francis Ford Coppola |\n", "+----------------------+\n", "| Christopher Nolan |\n", "+----------------------+\n", "| Francis Ford Coppola |\n", "+----------------------+\n", "| Sidney Lumet |\n", "+----------------------+\n", "| Steven Spielberg |\n", "+----------------------+\n", "| Peter Jackson |\n", "+----------------------+\n", "| Quentin Tarantino |\n", "+----------------------+\n", "| Peter Jackson |\n", "+----------------------+\n", "| Sergio Leone |\n", "+----------------------+\n", "10 rows in set (0.00 sec)" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT director\n", " FROM movies\n", " LIMIT 10;" ] }, { "cell_type": "markdown", "id": "a643187c", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "在加入 `DISTINCT` 保留字之後同樣顯示前 10 列，可以清楚發現已經沒有重複出現的導演。" ] }, { "cell_type": "code", "execution_count": 22, "id": "3b88defd", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
director
Frank Darabont
Francis Ford Coppola
Christopher Nolan
Sidney Lumet
Steven Spielberg
Peter Jackson
Quentin Tarantino
Sergio Leone
Robert Zemeckis
David Fincher
\n", "10 rows in set (0.00 sec)" ], "text/plain": [ "+----------------------+\n", "| director |\n", "+----------------------+\n", "| Frank Darabont |\n", "+----------------------+\n", "| Francis Ford Coppola |\n", "+----------------------+\n", "| Christopher Nolan |\n", "+----------------------+\n", "| Sidney Lumet |\n", "+----------------------+\n", "| Steven Spielberg |\n", "+----------------------+\n", "| Peter Jackson |\n", "+----------------------+\n", "| Quentin Tarantino |\n", "+----------------------+\n", "| Sergio Leone |\n", "+----------------------+\n", "| Robert Zemeckis |\n", "+----------------------+\n", "| David Fincher |\n", "+----------------------+\n", "10 rows in set (0.00 sec)" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT DISTINCT director\n", " FROM movies\n", " LIMIT 10;" ] }, { "cell_type": "markdown", "id": "016eeb54", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "## SQL 風格指南\n", "\n", "閱讀到這裡，讀者對於寫作過的 SQL 敘述應該有一些疑惑，例如保留字大寫、換行或者句首的空白（縮排，Indentation），如果沒有遵照這樣的方式寫作會不會影響查詢結果呢？答案是「不會」，SQL 具有幾個語言特性：\n", "\n", "1. 一段 SQL 敘述最後要以分號 `;` 做為結束的標註。\n", "2. 保留字的大小寫並不會影響執行結果，也就是俗稱的大小寫不敏感（Case insensitive）。\n", "3. 常數有固定的書寫方式，像是文字要用一組單引號 `'some texts'` 標註，整數、浮點數與空值可以直接寫作。\n", "4. 保留字之間要以空格或者換行來區隔。\n", "\n", "在 SQL 的敘述中保留字大小寫、是否換行或者是否有縮排，都不會對查詢結果有任何的影響。例如下列的這段 SQL 敘述，保留字大小寫、換行與縮排都相當隨興，但卻沒有影響到查詢的結果。" ] }, { "cell_type": "code", "execution_count": 23, "id": "92343168", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
titlerelease_yearrating
The Shawshank Redemption19949.3
The Godfather19729.2
The Dark Knight20089
The Godfather Part II19749
12 Angry Men19579
\n", "5 rows in set (0.00 sec)" ], "text/plain": [ "+--------------------------+--------------+--------+\n", "| title | release_year | rating |\n", "+--------------------------+--------------+--------+\n", "| The Shawshank Redemption | 1994 | 9.3 |\n", "+--------------------------+--------------+--------+\n", "| The Godfather | 1972 | 9.2 |\n", "+--------------------------+--------------+--------+\n", "| The Dark Knight | 2008 | 9 |\n", "+--------------------------+--------------+--------+\n", "| The Godfather Part II | 1974 | 9 |\n", "+--------------------------+--------------+--------+\n", "| 12 Angry Men | 1957 | 9 |\n", "+--------------------------+--------------+--------+\n", "5 rows in set (0.00 sec)" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "Select title, release_year,\n", " rating\n", "from movies liMiT 5;" ] }, { "cell_type": "markdown", "id": "covered-object", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "唯一不能夠隨興寫作的是 SQL 敘述中保留字彼此之間的「相對順序」，例如目前所學的幾個保留字 `SELECT`、`FROM`、`LIMIT` 等。\n", "\n", "```sql\n", "SELECT DISTINCT columns AS alias\n", " FROM table\n", " LIMIT m;\n", "```\n", "\n", "如果調動保留字的相對順序，就會得到語法錯誤的訊息（Syntax error），例如對調 `LIMIT` 與 `FROM` 的順序。\n", "\n", "```sql\n", "SELECT DISTINCT director AS distinct_director\n", " LIMIT 10\n", " FROM movies;\n", "```\n", "\n", "```\n", "near \"FROM\": syntax error while preparing \"SELECT DISTINCT director AS distinct_director\n", " LIMIT 10\n", " FROM movies;\".\n", "```\n", "\n", "又或者對調 `SELECT` 與 `FROM` 的順序。\n", "\n", "```sql\n", " FROM movies\n", "SELECT DISTINCT director AS distinct_director\n", " LIMIT 10;\n", "```\n", "\n", "```\n", "near \"FROM\": syntax error while preparing \" FROM movies\n", "SELECT DISTINCT director AS distinct_director\n", " LIMIT 10;\".\n", "```" ] }, { "cell_type": "markdown", "id": "bf5e3885", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "只需要遵守保留字的書寫順序讓 SQL 寫作時有很大的彈性，但是這不代表只要查詢結果是正確的，就可以隨心所欲地寫作。有時候我們必須顧慮到可讀性（Readibility），特別是在工作時，可讀性更為重要，因為工作上可能會遭遇到協作、程式碼審查（Code review）、維運、代理或者交接等情境，在這些時候查詢結果正確僅是最低門檻，可讀性必須要能達到前述情境中所要求的程度。推薦的作法是參考一份工作團隊、協作夥伴或者自己喜歡並且願意去遵從的風格指南（Style guide），風格指南指的是一套規範程式語言在寫作時必須遵從的編排、格式和設計的準則，風格指南能夠確保每一段程式碼都和其他不同人寫作的程式碼有高度一致性，被眾多使用者採用的 SQL 風格指南有：\n", "\n", "- Simon Holywell 的風格指南 \n", "- GitLab 的風格指南 \n", "- Mozilla 的風格指南 \n", "\n", "本書採用 Simon Holywell 的風格指南來寫作 SQL，其中值得注意的幾個重點有：\n", "\n", "- 敘述換行與縮排是採用靠右對齊的編排格式。\n", "- 保留字與函數採用全大寫。\n", "- 取別名的時候採用蛇形命名法（Snake case），例如 `alias_for_some_variables`。" ] }, { "cell_type": "code", "execution_count": 24, "id": "78e90458", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
distinct_director
Frank Darabont
Francis Ford Coppola
Christopher Nolan
Sidney Lumet
Steven Spielberg
Peter Jackson
Quentin Tarantino
Sergio Leone
Robert Zemeckis
David Fincher
\n", "10 rows in set (0.00 sec)" ], "text/plain": [ "+----------------------+\n", "| distinct_director |\n", "+----------------------+\n", "| Frank Darabont |\n", "+----------------------+\n", "| Francis Ford Coppola |\n", "+----------------------+\n", "| Christopher Nolan |\n", "+----------------------+\n", "| Sidney Lumet |\n", "+----------------------+\n", "| Steven Spielberg |\n", "+----------------------+\n", "| Peter Jackson |\n", "+----------------------+\n", "| Quentin Tarantino |\n", "+----------------------+\n", "| Sergio Leone |\n", "+----------------------+\n", "| Robert Zemeckis |\n", "+----------------------+\n", "| David Fincher |\n", "+----------------------+\n", "10 rows in set (0.00 sec)" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "SELECT DISTINCT director AS distinct_director\n", " FROM movies\n", " LIMIT 10;" ] }, { "cell_type": "markdown", "id": "5340fa9d", "metadata": {}, "source": [ "除了參閱、遵從 Simon Holywell 的風格指南，我們也可以善用 SQLiteStudio 的 Format SQL 功能，在編輯器範圍按右鍵，讓寫作的 SQL 敘述之編排、格式和設計具備更高的可讀性。\n", "\n", "![](../images/format-01.png)\n", "\n", "![](../images/format-02.png)\n", "\n", "## 重點統整\n", "\n", "- 我們可以將 SQL 敘述歸納為以下幾個部分的組成：\n", " - 保留字：具有特定功能的指令。\n", " - 符號：具有特定功能的符號。\n", " - 常數：由使用者給予的資料。\n", " - 函數：具有特定邏輯的輸入與輸出對應。\n", "- 本書採用 Simon Holywell 的風格指南來寫作 SQL，其中值得注意的幾個重點有：\n", " - 敘述換行與縮排是採用靠右對齊的編排格式。\n", " - 保留字與函數採用全大寫。\n", " - 取別名的時候採用蛇形命名法（Snake case）。\n", "- 這個章節學起來的 SQL 保留字：\n", " - `SELECT`\n", " - `FROM`\n", " - `LIMIT`\n", " - `AS`\n", " - `DISTINCT`\n", "- 將截至目前所學的 SQL 保留字集中在一個敘述中，寫作順序必須遵從標準 SQL 的規定。\n", "\n", "```sql\n", "SELECT DISTINCT columns AS alias\n", " FROM table\n", " LIMIT m;\n", "```" ] }, { "cell_type": "markdown", "id": "23a7faa1", "metadata": {}, "source": [ "## 練習題 01-05\n", "\n", "練習題會涵蓋四個學習資料庫，記得要依據題目的需求，調整編輯器選單的學習資料庫，在自己電腦的 SQLiteStudio 寫出跟預期輸出相同的 SQL 敘述，寫作過程如果卡關了，可以參考附錄二「練習題參考解答」。\n", "\n", "### 01. 從 `twElection2020` 資料庫的 `admin_regions` 資料表選擇所有變數，並且使用 `LIMIT 5` 顯示前五列資料，參考下列的預期查詢結果。\n", "\n", "預期輸出：(5, 4) 的查詢結果。" ] }, { "cell_type": "code", "execution_count": 25, "id": "b2b6e619", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
idcountytownvillage
1南投縣中寮鄉中寮村
2南投縣中寮鄉內城村
3南投縣中寮鄉八仙村
4南投縣中寮鄉和興村
5南投縣中寮鄉崁頂村
\n", "5 rows in set (0.00 sec)" ], "text/plain": [ "+----+-----------+-----------+-----------+\n", "| id | county | town | village |\n", "+----+-----------+-----------+-----------+\n", "| 1 | 南投縣 | 中寮鄉 | 中寮村 |\n", "+----+-----------+-----------+-----------+\n", "| 2 | 南投縣 | 中寮鄉 | 內城村 |\n", "+----+-----------+-----------+-----------+\n", "| 3 | 南投縣 | 中寮鄉 | 八仙村 |\n", "+----+-----------+-----------+-----------+\n", "| 4 | 南投縣 | 中寮鄉 | 和興村 |\n", "+----+-----------+-----------+-----------+\n", "| 5 | 南投縣 | 中寮鄉 | 崁頂村 |\n", "+----+-----------+-----------+-----------+\n", "5 rows in set (0.00 sec)" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [] }, { "cell_type": "markdown", "id": "a41cabe7", "metadata": {}, "source": [ "### 02. 從 `nba` 資料庫的球隊資料表 `teams` 中選擇 `confName`、`divName`、`fullName` 三個變數，並且使用 `LIMIT 10` 顯示前十列資料，參考下列預期的查詢結果。\n", "\n", "預期輸出：(10, 3) 的查詢結果。" ] }, { "cell_type": "code", "execution_count": 26, "id": "43817cd4", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
confNamedivNamefullName
EastSoutheastAtlanta Hawks
EastAtlanticBoston Celtics
EastCentralCleveland Cavaliers
WestSouthwestNew Orleans Pelicans
EastCentralChicago Bulls
WestSouthwestDallas Mavericks
WestNorthwestDenver Nuggets
WestPacificGolden State Warriors
WestSouthwestHouston Rockets
WestPacificLA Clippers
\n", "10 rows in set (0.00 sec)" ], "text/plain": [ "+----------+-----------+-----------------------+\n", "| confName | divName | fullName |\n", "+----------+-----------+-----------------------+\n", "| East | Southeast | Atlanta Hawks |\n", "+----------+-----------+-----------------------+\n", "| East | Atlantic | Boston Celtics |\n", "+----------+-----------+-----------------------+\n", "| East | Central | Cleveland Cavaliers |\n", "+----------+-----------+-----------------------+\n", "| West | Southwest | New Orleans Pelicans |\n", "+----------+-----------+-----------------------+\n", "| East | Central | Chicago Bulls |\n", "+----------+-----------+-----------------------+\n", "| West | Southwest | Dallas Mavericks |\n", "+----------+-----------+-----------------------+\n", "| West | Northwest | Denver Nuggets |\n", "+----------+-----------+-----------------------+\n", "| West | Pacific | Golden State Warriors |\n", "+----------+-----------+-----------------------+\n", "| West | Southwest | Houston Rockets |\n", "+----------+-----------+-----------------------+\n", "| West | Pacific | LA Clippers |\n", "+----------+-----------+-----------------------+\n", "10 rows in set (0.00 sec)" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [] }, { "cell_type": "markdown", "id": "4198008a", "metadata": {}, "source": [ "### 03. 從 `nba` 資料庫的球員資料表 `players` 中選擇 `firstName`、`lastName` 兩個變數，並依序取別名為 `first_name`、`last_name`，使用 `LIMIT 5` 顯示前五列資料，參考下列預期的查詢結果。\n", "\n", "預期輸出：(5, 2) 的查詢結果。" ] }, { "cell_type": "code", "execution_count": 27, "id": "429c2aaf", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
first_namelast_name
LeBronJames
CarmeloAnthony
UdonisHaslem
DwightHoward
AndreIguodala
\n", "5 rows in set (0.00 sec)" ], "text/plain": [ "+------------+-----------+\n", "| first_name | last_name |\n", "+------------+-----------+\n", "| LeBron | James |\n", "+------------+-----------+\n", "| Carmelo | Anthony |\n", "+------------+-----------+\n", "| Udonis | Haslem |\n", "+------------+-----------+\n", "| Dwight | Howard |\n", "+------------+-----------+\n", "| Andre | Iguodala |\n", "+------------+-----------+\n", "5 rows in set (0.00 sec)" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [] }, { "cell_type": "markdown", "id": "7f49a8cc", "metadata": {}, "source": [ "### 04. 從 `twElection2020` 資料庫的 `admin_regions` 資料表選擇「不重複」的縣市（`county`），參考下列的預期查詢結果。\n", "\n", "預期輸出：(22, 1) 的查詢結果。" ] }, { "cell_type": "code", "execution_count": 28, "id": "4e21785e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
distinct_counties
南投縣
嘉義市
嘉義縣
基隆市
宜蘭縣
屏東縣
彰化縣
新北市
新竹市
新竹縣
桃園市
澎湖縣
臺中市
臺北市
臺南市
臺東縣
花蓮縣
苗栗縣
連江縣
金門縣
雲林縣
高雄市
\n", "22 rows in set (0.01 sec)" ], "text/plain": [ "+-------------------+\n", "| distinct_counties |\n", "+-------------------+\n", "| 南投縣 |\n", "+-------------------+\n", "| 嘉義市 |\n", "+-------------------+\n", "| 嘉義縣 |\n", "+-------------------+\n", "| 基隆市 |\n", "+-------------------+\n", "| 宜蘭縣 |\n", "+-------------------+\n", "| 屏東縣 |\n", "+-------------------+\n", "| 彰化縣 |\n", "+-------------------+\n", "| 新北市 |\n", "+-------------------+\n", "| 新竹市 |\n", "+-------------------+\n", "| 新竹縣 |\n", "+-------------------+\n", "| 桃園市 |\n", "+-------------------+\n", "| 澎湖縣 |\n", "+-------------------+\n", "| 臺中市 |\n", "+-------------------+\n", "| 臺北市 |\n", "+-------------------+\n", "| 臺南市 |\n", "+-------------------+\n", "| 臺東縣 |\n", "+-------------------+\n", "| 花蓮縣 |\n", "+-------------------+\n", "| 苗栗縣 |\n", "+-------------------+\n", "| 連江縣 |\n", "+-------------------+\n", "| 金門縣 |\n", "+-------------------+\n", "| 雲林縣 |\n", "+-------------------+\n", "| 高雄市 |\n", "+-------------------+\n", "22 rows in set (0.01 sec)" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [] }, { "cell_type": "markdown", "id": "b30ff0d2", "metadata": {}, "source": [ "### 05. 從 `nba` 資料庫的 `teams` 資料表選擇「不重複」的分組（`divName`），參考下列的預期查詢結果。\n", "\n", "預期輸出：(6, 1) 的查詢結果。" ] }, { "cell_type": "code", "execution_count": 29, "id": "ca60ee31", "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
distinct_divisions
Southeast
Atlantic
Central
Southwest
Northwest
Pacific
\n", "6 rows in set (0.00 sec)" ], "text/plain": [ "+--------------------+\n", "| distinct_divisions |\n", "+--------------------+\n", "| Southeast |\n", "+--------------------+\n", "| Atlantic |\n", "+--------------------+\n", "| Central |\n", "+--------------------+\n", "| Southwest |\n", "+--------------------+\n", "| Northwest |\n", "+--------------------+\n", "| Pacific |\n", "+--------------------+\n", "6 rows in set (0.00 sec)" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [] } ], "metadata": { "kernelspec": { "display_name": "xsql", "language": "sqlite", "name": "xsql" }, "language_info": { "codemirror_mode": "sql", "file_extension": "", "mimetype": "", "name": "mysql", "version": "0.1.5" } }, "nbformat": 4, "nbformat_minor": 5 }