SQLの高度な問合せ

はじめに_

本ページはSQLite3を用いて、SQLの使い方を演習形式で学ぶことを目的としている。

このため、端末（ターミナル）上でSQLite3を実行できるようにしておく必要がある。最低限、以下の内容まで終わらせておくこと。

また、以下の内容は理解していることとする。

Windows Subsystem for LinuxでLinux環境を整えた場合_

VcxSrv（XLaunch）の起動_

gnome-terminalを利用するので、まず、VcxSrv（XLaunch）を起動する。VcxSrvのインストールで設定したVcxSrvのアイコンをクリックし、VcxSrvを起動する。

gnome-terminalの起動_

gnome-terminalの利用で設定したgnome-terminalを起動する。

Ubuntuターミナルを起動し、ターミナル上で以下を起動する。

% gterm &

SQLite3を用いたSQLの高度な問合せ_

今回のチュートリアルで使うデータベース_

今日の作業ディレクトリの作成とデータベースをダウンロードする。

% cd
% mkdir -p AppSQL
% cd AppSQL

% which wget
/usr/bin/wget

% wget http://www.aise.ics.saitama-u.ac.jp/~gotoh/Lectures/TUS_IP/download/edu.db
% wget http://www.aise.ics.saitama-u.ac.jp/~gotoh/Lectures/TUS_IP/download/create_table.sql

% ls
create_table.sql  edu.db

表：students（学生）

sid（学生ID）sname（氏名）highschool（出身高校）
501Keitha TeagueUrawa High School
601Marybeth BorosWarabi High School
602Shasta PepperSaitama Sakae High School
701Peg MassingillShukutoku Yono High School
702Eugenio DenglerJohoku High School

表：credits（修得単位）

sid（学生ID）degree （学年）unit（学年ごとの修得単位数）
501148
501240
501338
601139
701137
702145

表：classes （履修）

sid（学生ID）cname（科目名）unit（単位数）
501Graduation Research8
601Web Design2
601Image Processing4
601Computer Graphics2
603Database6
603Web Design2
701Information Retrieval2
701Programming Lang Java4
702Programming Lang Java4
702Web Design2

テーブルの名前と定義を確認する。

% sqlite3 edu.db
sqlite> .tables
classes   credits   students

sqlite> .schema
CREATE TABLE students(
       sid char(4),
       sname char(30),
       highschool char(30),
       PRIMARY KEY(sid)
       );
CREATE TABLE credits(
       sid char(4),
       degree int,
       unit int,
       PRIMARY KEY(sid,degree)
       );
CREATE TABLE classes(
       sid char(4),
       cname char(30),
       unit int,
       PRIMARY KEY(sid,cname)
       );

テーブル内の値を確認する。

sqlite> SELECT * FROM students;
501|Keitha Teague|Urawa High School
601|Marybeth Boros|Warabi High School
602|Shasta Pepper|Saitama Sakae High School
701|Peg Massingill|Shukutoku Yono High School
702|Eugenio Dengler|Johoku High School

sqlite> SELECT * FROM credits;
501|1|48
501|2|40
501|3|38
601|1|39
701|1|37
702|1|45

sqlite> SELECT * FROM classes;
501|Graduation Research|8
601|Web Design|2
601|Image Processing|4
601|Computer Graphics|2
603|Database|6
603|Web Design|2
701|Information Retrieval|2
701|Programming Lang Java|4
702|Programming Lang Java|4
702|Web Design|2

distict句とorder by句_

SELECT DISTINCT column_list
FROM table_list
  JOIN table ON join_condition
WHERE row_filter
ORDER BY column
LIMIT count OFFSET offset
GROUP BY column
HAVING group_filter;

SELECT文にDISTINCT句をつけると問合せ結果から重複を取り除くことができる。以下の問合せを行ってみる。

sqlite> SELECT cname FROM classes;
Graduation Research
Computer Graphics
Image Processing
Web Design
Database
Web Design
Information Retrieval
Programming Lang Java
Programming Lang Java
Web Design

sqlite> SELECT DISTINCT cname FROM classes;
Graduation Research
Computer Graphics
Image Processing
Web Design
Database
Information Retrieval
Programming Lang Java

ORDER BY句を用いるとSELECTの問合せ結果を指定した列（フィールド）について辞書順にならべることができる。辞書順には昇順（ASC）と降順（DESC）が存在し、標準は昇順になっている。以下の問合せを行ってみる。

sqlite> SELECT cname FROM classes;
Graduation Research
Computer Graphics
Image Processing
Web Design
Database
Web Design
Information Retrieval
Programming Lang Java
Programming Lang Java
Web Design

sqlite> SELECT cname FROM classes ORDER BY cname;
Computer Graphics
Database
Graduation Research
Image Processing
Information Retrieval
Programming Lang Java
Programming Lang Java
Web Design
Web Design
Web Design

sqlite> SELECT cname FROM classes ORDER BY cname DESC;
Web Design
Web Design
Web Design
Programming Lang Java
Programming Lang Java
Information Retrieval
Image Processing
Graduation Research
Database
Computer Graphics

DISTINCT句とORDER BY句を同時につかうこともできる。

sqlite> SELECT DISTINCT cname FROM classes ORDER BY cname;
Computer Graphics
Database
Graduation Research
Image Processing
Information Retrieval
Programming Lang Java
Web Design

sqlite> SELECT DISTINCT cname FROM classes ORDER BY cname DESC;
Web Design
Programming Lang Java
Information Retrieval
Image Processing
Graduation Research
Database
Computer Graphics

和集合演算（union）、共通集合演算（intersect）、差集合演算（except）、直積_

構文は以下のとおり。

query_1
UNION [ALL]
query_2
UNION [ALL]
query_3
...;

UNIONを使うと問合せ（query）結果の和集合演算を行うことができる。

sqlite> SELECT cname FROM classes WHERE sid = '701';
Information Retrieval
Programming Lang Java

sqlite> SELECT cname FROM classes WHERE sid = '702';
Programming Lang Java
Web Design

sqlite> SELECT cname FROM classes WHERE sid = '701'
   ...> UNION
   ...> SELECT cname FROM classes WHERE sid = '702';
Information Retrieval
Programming Lang Java
Web Design

INTERSECT句を使うと問合せ（query）結果の共通集合演算を行うことができる。

sqlite> SELECT cname FROM classes WHERE sid = '701';
～出力省略～

sqlite> SELECT cname FROM classes WHERE sid = '702';
～出力省略～

sqlite> SELECT cname FROM classes WHERE sid = '701'
   ...> INTERSECT
   ...> SELECT cname FROM classes WHERE sid = '702';
Programming Lang Java

EXCEPT句を使うと問合せ（query）結果の差集合演算を行うことができる。

sqlite> SELECT cname FROM classes WHERE sid = '701';
～出力省略～

sqlite> SELECT cname FROM classes WHERE sid = '702';
～出力省略～

sqlite> SELECT cname FROM classes WHERE sid = '701'
   ...> EXCEPT
   ...> SELECT cname FROM classes WHERE sid = '702';
Information Retrieval

sqlite> SELECT cname FROM classes WHERE sid = '702'
   ...> EXCEPT
   ...> SELECT cname FROM classes WHERE sid = '701';
Web Design

直積演算はSELECT文で実行できる。

sqlite> SELECT * FROM students;
501|Keitha Teague|Urawa High School
601|Marybeth Boros|Warabi High School
602|Shasta Pepper|Saitama Sakae High School
701|Peg Massingill|Shukutoku Yono High School
702|Eugenio Dengler|Johoku High School

sqlite> SELECT * FROM classes;
501|Graduation Research|8
601|Web Design|2
601|Image Processing|4
601|Computer Graphics|2
603|Database|6
603|Web Design|2
701|Information Retrieval|2
701|Programming Lang Java|4
702|Programming Lang Java|4
702|Web Design|2

sqlite> SELECT * FROM students, classes;
501|Keitha Teague|Urawa High School|501|Graduation Research|8
501|Keitha Teague|Urawa High School|601|Web Design|2
501|Keitha Teague|Urawa High School|601|Image Processing|4
501|Keitha Teague|Urawa High School|601|Computer Graphics|2
501|Keitha Teague|Urawa High School|603|Database|6
～省略～
702|Eugenio Dengler|Johoku High School|701|Information Retrieval|2
702|Eugenio Dengler|Johoku High School|701|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Web Design|2

CROSS JOIN句を用いても直積演算を実行できる。

以下の4つの文は同じ結果となる。

sqlite> SELECT * FROM students, classes;
～出力省略～

sqlite> SELECT * FROM students JOIN classes;
～出力省略～

sqlite> SELECT * FROM students INNER JOIN classes;
～出力省略～

sqlite> SELECT * FROM students CROSS JOIN classes;
～出力省略～

内部結合（JOIN / INNER JOIN）_

結合対象となるテーブルから結合条件を満たす行だけを取り出す演算を内部結合という。結合条件を満たさない行は欠落する。

SQL-89とSQL-92でそれぞれ規格化された構文があり、ほとんどのDBMSで双方に対応している。

sqlite> SELECT * FROM students AS S, credits AS C WHERE S.sid = C.sid; -- (1)
501|Keitha Teague|Urawa High School|501|1|48
501|Keitha Teague|Urawa High School|501|2|40
501|Keitha Teague|Urawa High School|501|3|38
601|Marybeth Boros|Warabi High School|601|1|39
701|Peg Massingill|Shukutoku Yono High School|701|1|37
702|Eugenio Dengler|Johoku High School|702|1|45

sqlite> SELECT * FROM students AS S JOIN credits AS C ON S.sid = C.sid; -- (2)
501|Keitha Teague|Urawa High School|501|1|48
501|Keitha Teague|Urawa High School|501|2|40
501|Keitha Teague|Urawa High School|501|3|38
601|Marybeth Boros|Warabi High School|601|1|39
701|Peg Massingill|Shukutoku Yono High School|701|1|37
702|Eugenio Dengler|Johoku High School|702|1|45

sqlite> SELECT * FROM students JOIN credits USING (sid); -- (3)
501|Keitha Teague|Urawa High School|1|48
501|Keitha Teague|Urawa High School|2|40
501|Keitha Teague|Urawa High School|3|38
601|Marybeth Boros|Warabi High School|1|39
701|Peg Massingill|Shukutoku Yono High School|1|37
702|Eugenio Dengler|Johoku High School|1|45

(1)はSQL-89で規格化された書き方。FROM句に結合対象のテーブルをすべて列挙し、WHERE句で結合条件を指定する。

(2)to(3)はSQL-92で規格化された書き方。「テーブル名 JOIN テーブル名 ON 結合条件」あるいは「テーブル名 JOIN テーブル名 USING (結合する属性)」という構文で2つのテーブルを結合する。

なお、文中の「テーブル名 AS 略記号」は結合条件を記載する際にテーブル名を略記するためのもの。

(1)と(2)はsidの列が複数個表示される。(3)はsidの列が一つだけ表示される。すなわち、(3)は自然結合演算の結果となっている。

3つ以上のテーブルも結合することができる。

sqlite> SELECT * FROM students AS S, credits AS C, classes AS R
   ...> WHERE S.sid = C.sid AND C.sid = R.sid; -- (4)；
501|Keitha Teague|Urawa High School|501|1|48|501|Graduation Research|8
501|Keitha Teague|Urawa High School|501|2|40|501|Graduation Research|8
501|Keitha Teague|Urawa High School|501|3|38|501|Graduation Research|8
601|Marybeth Boros|Warabi High School|601|1|39|601|Computer Graphics|2
601|Marybeth Boros|Warabi High School|601|1|39|601|Image Processing|4
601|Marybeth Boros|Warabi High School|601|1|39|601|Web Design|2
701|Peg Massingill|Shukutoku Yono High School|701|1|37|701|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|701|1|37|701|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|1|45|702|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|1|45|702|Web Design|2

sqlite> SELECT * FROM students AS S JOIN credits AS C ON S.sid = C.sid
   ...> JOIN classes AS R ON C.sid = R.sid; --(5)
501|Keitha Teague|Urawa High School|501|1|48|501|Graduation Research|8
501|Keitha Teague|Urawa High School|501|2|40|501|Graduation Research|8
501|Keitha Teague|Urawa High School|501|3|38|501|Graduation Research|8
601|Marybeth Boros|Warabi High School|601|1|39|601|Computer Graphics|2
601|Marybeth Boros|Warabi High School|601|1|39|601|Image Processing|4
601|Marybeth Boros|Warabi High School|601|1|39|601|Web Design|2
701|Peg Massingill|Shukutoku Yono High School|701|1|37|701|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|701|1|37|701|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|1|45|702|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|1|45|702|Web Design|2

sqlite> SELECT * FROM students JOIN credits USING (sid)
   ...> JOIN classes USING (sid); -- (6)
501|Keitha Teague|Urawa High School|1|48|Graduation Research|8
501|Keitha Teague|Urawa High School|2|40|Graduation Research|8
501|Keitha Teague|Urawa High School|3|38|Graduation Research|8
601|Marybeth Boros|Warabi High School|1|39|Web Design|2
601|Marybeth Boros|Warabi High School|1|39|Image Processing|4
601|Marybeth Boros|Warabi High School|1|39|Computer Graphics|2
701|Peg Massingill|Shukutoku Yono High School|1|37|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|1|37|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|1|45|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|1|45|Web Design|2

なお、「JOIN」と記載している部分は「INNER JOIN」と記載してもよい。

sqlite> SELECT * FROM students AS S INNER JOIN credits AS C ON S.sid = C.sid; -- (7)
～出力省略～

sqlite> SELECT * FROM students INNER JOIN credits USING (sid); -- (8)
～出力省略～

外部結合（OUTER JOIN）_

内部結合により結合条件を満たす行が取り出されるが、外部結合では結合条件を満たさない行も取り出される。結合条件を満たしていない行については空値としてNULL値が挿入される。このため、外部結合を行う場合には該当する列にNOT NULL条件を付与してはいけない。

外部結合には左外部結合（LEFT OUTER JOIN）、右外部結合（RIGHT OUTER JOIN）、完全外部結合（FULL OUTER JOIN）の3種類がある。

内部結合に対応させる形で外部結合の働きを確かめてみる。

sqlite>  SELECT * FROM students AS S INNER JOIN classes AS R ON S.sid = R.sid;
501|Keitha Teague|Urawa High School|501|Graduation Research|8
601|Marybeth Boros|Warabi High School|601|Web Design|2
601|Marybeth Boros|Warabi High School|601|Image Processing|4
601|Marybeth Boros|Warabi High School|601|Computer Graphics|2
701|Peg Massingill|Shukutoku Yono High School|701|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|701|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Web Design|2

sqlite>  SELECT * FROM students AS S LEFT OUTER JOIN classes AS R ON S.sid = R.sid; -- (9) 左外部結合。classesの行に空値が挿入される。
501|Keitha Teague|Urawa High School|501|Graduation Research|8
601|Marybeth Boros|Warabi High School|601|Computer Graphics|2
601|Marybeth Boros|Warabi High School|601|Image Processing|4
601|Marybeth Boros|Warabi High School|601|Web Design|2
602|Shasta Pepper|Saitama Sakae High School|||
701|Peg Massingill|Shukutoku Yono High School|701|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|701|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Web Design|2

sqlite>  SELECT * FROM students AS S RIGHT OUTER JOIN classes AS R ON S.sid = R.sid; -- (10) 右外部結合。studentsの行に空値が挿入される。
Error: RIGHT and FULL OUTER JOINs are not currently supported

sqlite>  SELECT * FROM students AS S FULL OUTER JOIN classes AS R ON S.sid = R.sid; -- (11) 完全外部結合。studentsの行に空値が挿入される。
Error: RIGHT and FULL OUTER JOINs are not currently supported

(10)と(11)のSQL文はSQLite3では実行できない。

重複したsidを取り除く場合はUSING句を用いる。

sqlite>  SELECT * FROM students INNER JOIN classes USING (sid); -- (12)
501|Keitha Teague|Urawa High School|Graduation Research|8
601|Marybeth Boros|Warabi High School|Web Design|2
601|Marybeth Boros|Warabi High School|Image Processing|4
601|Marybeth Boros|Warabi High School|Computer Graphics|2
701|Peg Massingill|Shukutoku Yono High School|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|Web Design|2

sqlite>  SELECT * FROM students LEFT OUTER JOIN classes USING (sid); -- (13) 左外部結合。classesの行に空値が挿入される。
501|Keitha Teague|Urawa High School|Graduation Research|8
601|Marybeth Boros|Warabi High School|Computer Graphics|2
601|Marybeth Boros|Warabi High School|Image Processing|4
601|Marybeth Boros|Warabi High School|Web Design|2
602|Shasta Pepper|Saitama Sakae High School||
701|Peg Massingill|Shukutoku Yono High School|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|Web Design|2

クイズ：集合演算と結合演算（2021年11月4日の講義はここまで）_

表 studentsと表 classesを左外部結合する文(13)は以下のとおりである。

sqlite>  SELECT * FROM students LEFT OUTER JOIN classes USING (sid); -- (13) 左外部結合。classesの行に空値が挿入される。

SQLite3は右外部結合が実装されていないが、右外部結合の結果および完全外部結合の結果をSQL文を用いて得ることができる。表 studentsと表 classesの右外部結合および完全外部結合と同等の結果をえるSQL文を書け。

ヒント

  • SELECT文では問合せ結果の列（フィールド、属性）の順番を任意に指定することができる。
  • 完全外部結合は左外部結合と右外部結合の和集合演算である。
  • 解答例

2021年11月11日の講義の開始時作業_

先週の作業ディレクトリとデータベースファイルが存在するか確かめる。存在する場合は、それをそのまま使う。

% cd

% ls AppSQL/
create_table.sql  edu.db

% cd AppSQL
% sqlite3 edu.db

先週のディレクトリとデータベースファイルが存在しない場合は作業ディレクトリの作成とデータベースのダウンロードを行う。

% cd
% mkdir -p AppSQL
% cd AppSQL

% which wget
/usr/bin/wget

% wget http://www.aise.ics.saitama-u.ac.jp/~gotoh/Lectures/TUS_IP/download/edu.db
% wget http://www.aise.ics.saitama-u.ac.jp/~gotoh/Lectures/TUS_IP/download/create_table.sql

% ls
create_table.sql  edu.db

% sqlite3 edu.db

副問合せ（SELECT文の入れ子）_

SELECT文中にSELECT文を入れ子にすることができる。このとき、入れ子のSELECT文を副問合せ、外側のSELECT文を主問合せという。

副問合せを用いたSQL文では、まず、副問合せのSELECT文が実行され、その結果を用いて主問合せの文が実行される。主問合せに連携するための演算子として、比較演算子、INおよびNOT IN演算子、EXISTSおよびNOT EXISTS演算子、ANY（SOME）演算子、そして、ALL演算子がある。

比較演算子_

全学生の修得単位数の平均値よりも多く単位を修得している学生IDを副問合せを用いて取得する。以下のSQL文を実行する。

sqlite> SELECT sid, unit FROM credits; -- 学生IDと修得単位数を列挙する。
501|48
501|40
501|38
601|39
701|37
702|45

sqlite> SELECT avg(unit) FROM credits; -- avg()は平均値を求める集約関数。後ほど説明
41.1666666666667

sqlite> SELECT sid, unit FROM credits
   ...> WHERE unit >= (SELECT avg(unit) FROM credits); -- 全学生の修得単位数の平均値よりも多く単位を修得している学生ID
501|48
702|45

比較演算子は、＝、<、>、>=、=<などがある。

INおよびNOT IN演算子_

副問合せの結果に検討対象の属性が含まれているかどうかを判定するためにIN演算子を用いる。含まれていないことを判定するためにはNOT IN演算子を用いる。

以下の例は、Web Designを履修している学生の学生ID（sid）と名前（sname）を表示するSQL文である。

sqlite> SELECT sid FROM classes WHERE cname = 'Web Design'; -- Web Designを履修している学生一覧
601
603
702

sqlite> SELECT sid, sname FROM students; -- 学生IDと名前の一覧
501|Keitha Teague
601|Marybeth Boros
602|Shasta Pepper
701|Peg Massingill
702|Eugenio Dengler

sqlite> SELECT sid, sname FROM students
   ...> WHERE sid IN (SELECT sid FROM classes WHERE cname = 'Web Design');
601|Marybeth Boros
702|Eugenio Dengler

sqlite> SELECT sid, sname FROM students
   ...> WHERE sid NOT IN (SELECT sid FROM classes WHERE cname = 'Web Design');
501|Keitha Teague
602|Shasta Pepper
701|Peg Massingill

INとNOT IN演算子は副問合せをつなぐためでなく、通常のSELECT文でも利用できる。

sqlite> SELECT * FROM students;
501|Keitha Teague|Urawa High School
601|Marybeth Boros|Warabi High School
602|Shasta Pepper|Saitama Sakae High School
701|Peg Massingill|Shukutoku Yono High School
702|Eugenio Dengler|Johoku High School

sqlite> SELECT * FROM students WHERE highschool IN ('Urawa High School', 'Johoku High School');
501|Keitha Teague|Urawa High School
702|Eugenio Dengler|Johoku High School

単位数が４、６、８の科目名を降順で列挙することもできる。

sqlite> SELECT cname, unit FROM classes;
Graduation Research|8
Web Design|2
Image Processing|4
Computer Graphics|2
Database|6
Web Design|2
Information Retrieval|2
Programming Lang Java|4
Programming Lang Java|4
Web Design|2

sqlite> SELECT DISTINCT cname, unit FROM classes
   ...> WHERE unit IN (4, 6, 8) ORDER BY unit DESC;
Graduation Research|8
Database|6
Image Processing|4
Programming Lang Java|4

EXISTSおよびNOT EXISTS演算子_

EXISTS演算子は副問合せの結果が空かどうかを判定する。空でない（1行以上ある）ならば真（true）、そうでなければ偽（false）となる。NOT EXISTSはEXISTSの反対の値をとる。

学生テーブル（students）と履修テーブル（classes）の双方に登場する学生の学生IDと名前を列挙する。

sqlite> SELECT * FROM students AS S, classes AS R WHERE S.sid = R.sid; -- 内部結合
501|Keitha Teague|Urawa High School|501|Graduation Research|8
601|Marybeth Boros|Warabi High School|601|Web Design|2
601|Marybeth Boros|Warabi High School|601|Image Processing|4
601|Marybeth Boros|Warabi High School|601|Computer Graphics|2
701|Peg Massingill|Shukutoku Yono High School|701|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|701|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Web Design|2

sqlite> SELECT sid, sname FROM students AS S
   ...> WHERE EXISTS (SELECT * FROM classes AS R WHERE S.sid = R.sid);
501|Keitha Teague
601|Marybeth Boros
701|Peg Massingill
702|Eugenio Dengler

sqlite> SELECT sid, sname FROM students AS S
   ...> WHERE NOT EXISTS (SELECT * FROM classes AS R WHERE S.sid = R.sid);
602|Shasta Pepper

ANY（SOME）演算子_

副問合せの結果を用いた条件式のうち、１つでも条件に当てはまるものがあれば真（true）を返す演算子、SOME演算子はANY演算子と同じ働きの演算子である。

以下の例は履修（classes）に存在する学生ID（sid）について学生IDと名前(sname）を列挙するSQL文である。

sqlite> SELECT sid, sname FROM students
   ...> WHERE sid = ANY (SELECT sid FROM classes);
Error: near "SELECT": syntax error

sqlite> SELECT sid, sname FROM students
   ...> WHERE sid = SOME (SELECT sid FROM classes); -- 上と同義
Error: near "SELECT": syntax error

SQLite3においてはANY演算子およびSOME演算子は使用できない。今回の例はIN演算子を用いることで実現できる。

sqlite> SELECT sid, sname FROM students
   ...> WHERE sid IN (SELECT sid FROM classes);
501|Keitha Teague
601|Marybeth Boros
701|Peg Massingill
702|Eugenio Dengler

ALL演算子_

ALL演算子はANY演算子とは異なり、副問合せの結果すべてが条件式と一致するときに真（true）となる。

以下の例は履修（classes）に含まれない学生ID（sid）について学生IDと名前(sname）を列挙するSQL文である。

sqlite> SELECT sid, sname FROM students
   ...> WHERE sid != ALL (SELECT sid FROM classes);
Error: near "ALL": syntax error

SQLite3においてはALL演算子は使用できない。今回の例はNOT IN演算子を用いることで実現できる。

sqlite> SELECT sid, sname FROM students
   ...> WHERE sid NOT IN (SELECT sid FROM classes);
602|Shasta Pepper

FROM句での副問合せ_

FROM句では複数のテーブルを列挙することができる。このテーブル列挙の代わりに副問合せの結果を用いることもできる。

まず、修得単位数（creditsのunitの値）が44単位以上の行を列挙するSELECT文を考える。

sqlite> SELECT * FROM credits WHERE unit >= 44;
501|1|48
702|1|45

このSELECT文を副問合せとしてFROM句に埋め込む。これは修得単位数が44単位以上の学生IDと氏名を列挙するSELECT文である。

sqlite> SELECT DISTINCT S.sid, sname, degree, unit
   ...> FROM students AS S,
   ...> (SELECT sid, degree, unit FROM credits WHERE unit >= 44) AS VTB
   ...> WHERE S.sid = VTB.sid;
501|Keitha Teague|1|48
702|Eugenio Dengler|1|45

また、SELECT文の列の列挙部分（「SELECT 列の列挙 FROM テーブルの列挙」)に副問合せを用いることもできる。

先ほどの例と同様に修得単位数が44単位以上の学生とその名前を列挙する。

sqlite> SELECT sid, degree, unit, (SELECT sname FROM students AS S
   ...> WHERE S.sid = C.sid) FROM credits AS C WHERE unit >= 44; 
501|1|48|Keitha Teague
702|1|45|Eugenio Dengler

副問合せの練習1_

学生ID（sid）が701と702の学生が両方とも履修している科目名（cname）を抽出するSELECT文をANY演算子を用いて作ると以下のようになる。

sqlite> SELECT DISTINCT cname FROM classes  WHERE sid = ANY ('701', '702'); 
Error: no such function: ANY

これをIN演算子を用いて書き直せ。

副問合せの練習2_

学生ID（sid）が701の学生が履修しているが、702の学生は履修していない科目を抽出するSELECT文を考える。exceptを用いたSELECT文は以下のようになる。

sqlite> SELECT sid, cname FROM classes WHERE sid = '701';
701|Information Retrieval
701|Programming Lang Java

sqlite> SELECT sid, cname FROM classes WHERE sid = '702';
702|Programming Lang Java
702|Web Design

sqlite> SELECT cname FROM classes WHERE sid = '701'
   ...> EXCEPT
   ...> SELECT cname FROM classes WHERE sid = '702';
Information Retrieval

ALL演算子を用いると以下のようになる。

sqlite> SELECT cname FROM classes WHERE sid = '701' AND cname != ALL (SELECT cname FROM classes WHERE sid = '702');
Error: near "ALL": syntax error

IN演算子あるいはNOT IN演算子を用いて同等の働きをするSQL文を作れ。

集約関数_

DBMSの製品の多くは組み込み関数を提供している。SELECT文においてGROUP BY句やHAVING句と一緒に組み込み関数を用いる場合が多い。

COUNT関数_

列名を引数としてその列に含まれる行数をカウントする。一般に COUNT(*)はテーブルに含まれる行数をカウントするために使われる。

classesテーブルの行数をカウントする場合は以下のように入力する。

sqlite> SELECT * FROM classes;
501|Graduation Research|8
601|Web Design|2
601|Image Processing|4
601|Computer Graphics|2
603|Database|6
603|Web Design|2
701|Information Retrieval|2
701|Programming Lang Java|4
702|Programming Lang Java|4
702|Web Design|2

sqlite> SELECT COUNT(*) FROM classes;
10

「COUNT(*) AS 略称」という表記で名前をつけることができる。

sqlite> SELECT COUNT(*) AS num_of_reg FROM classes;
10

classesには科目ごとにどの学生が履修しているのかの情報が格納されている。一般に学生は複数の科目を履修する。このため、履修している学生のユニークな数を得たい場合はDISTINCT句を用いてカウントすることができる。

sqlite> SELECT COUNT(DISTINCT sid) AS num_of_reg_students FROM classes;
5

max, min関数_

列に含まれる最大値と最小値を得るための関数がMAX関数とMIN関数である。

creditsテーブルから修得単位数が最大の学生および最小の学生を列挙する。

sqlite> SELECT sid, MAX(unit) FROM credits;
501|48

sqlite> SELECT sid, MIN(unit) FROM credits;
701|37

AVG, STDDEV, VARIANCE関数_

統計処理を行う際に平均値、標準偏差、分散を用いることが多い。平均値はAVG関数で、標準偏差はSTDDEV関数で、分散はVARIANCE関数で得ることができる。

sqlite> SELECT AVG(unit) FROM credits;
41.1666666666667

sqlite> SELECT STDDEV(unit) FROM credits;
Error: no such function: STDDEV

sqlite> SELECT VARIANCE(unit) FROM credits;
Error: no such function: VARIANCE

SQLite3ではSTDDEV関数、VARIANCE関数は提供されていない。

GROUP BY句とHAVING句_

GROUP BY句はテーブルの特定の列で行をグループ化し、個々のグループに対して集約関数を適用するときに使用する。以下の例は科目名ごとに履修学生の数を数えるSQL文である。

sqlite> SELECT * FROM classes;
501|Graduation Research|8
601|Web Design|2
601|Image Processing|4
601|Computer Graphics|2
603|Database|6
603|Web Design|2
701|Information Retrieval|2
701|Programming Lang Java|4
702|Programming Lang Java|4
702|Web Design|2

sqlite> SELECT cname,COUNT(*) FROM classes GROUP BY cname;
Computer Graphics|1
Database|1
Graduation Research|1
Image Processing|1
Information Retrieval|1
Programming Lang Java|2
Web Design|3

HAVING句は集約された値に対して条件を付与するときに用いる。たとえば、履修登録者が2名以上の科目だけを列挙したい場合は以下のように入力する。

sqlite> SELECT cname,COUNT(*) FROM classes GROUP BY cname;
Computer Graphics|1
Database|1
Graduation Research|1
Image Processing|1
Information Retrieval|1
Programming Lang Java|2
Web Design|3

sqlite> SELECT cname,COUNT(*) FROM classes
   ...> GROUP BY cname HAVING count(*) >= 2;
Programming Lang Java|2
Web Design|3

sqlite> SELECT sid, sum(unit) FROM credits
   ...> GROUP BY sid HAVING sum(unit) >= 80;
501|126

creditsテーブルには学年ごとの総修得単位数が記載されているため、creditsテーブルに登録されている学生IDごとの行数をつかうことで現在の学年を得ることができる。

sqlite> SELECT sid, degree FROM credits;
501|1
501|2
501|3
601|1
701|1
702|1

sqlite> SELECT sid, count(sid)+1 AS current_degree FROM credits GROUP BY sid;
501|4
601|2
701|2
702|2

ビュー（再）_

ビューの使い方_

前回紹介したビューは今回紹介した複雑な問合せを繰り返し実行するときに非常に役立つ。

たとえば、年間の修得単位数が40単位以上の学生だけを列挙するビュー students_40を作成してみる。

sqlite> CREATE VIEW students_40
   ...> AS SELECT S.sid, sname, highschool, degree, unit FROM students AS S,
   ...> credits AS C
   ...> WHERE S.sid = C.sid and unit >= 40;

sqlite> .tables
classes      credits      students     students_40

sqlite> SELECT * FROM students_40;
501|Keitha Teague|Urawa High School|1|48
501|Keitha Teague|Urawa High School|2|40
702|Eugenio Dengler|Johoku High School|1|45

作成したビューを用いて結合演算を行うこともできる。年間の修得単位数が40以上の学生が履修している科目名を列挙する。

sqlite> SELECT DISTINCT cname FROM students_40 AS S, classes AS R
   ...> WHERE S.sid = R.sid;
Graduation Research
Programming Lang Java
Web Design

ビューに対して集約関数を適用することもできる。students_40に含まれる学生と全学生の年間平均修得単位数の差を表示する。

sqlite> SELECT AVG(S.unit), AVG(C.unit), AVG(S.unit)-AVG(C.unit)
   ...> FROM students_40 AS S, credits AS C;
44.3333333333333|41.1666666666667|3.16666666666667

集約関数を含むビュー_

ビューの定義に集約関数を含めることもできる。以下の例は学生の学年と現時点での積算修得単位数を列挙するビューである。

sqlite> CREATE VIEW total_credits 
   ...> AS SELECT sid, count(sid)+1 AS current_degree, sum(unit) AS total_unit 
   ...> FROM credits GROUP BY sid;

sqlite> .tables 
classes        credits        students       students_40    total_credits

sqlite> SELECT * FROM total_credits;
501|4|126
601|2|39
701|2|37
702|2|45

作成したビューを用いて現時点で80単位以上修得している学生を列挙する。

sqlite> SELECT * FROM total_credits WHERE total_unit >= 80;
501|4|126

更新可能ビュー_

ビューはテーブルと同じように使うことができるが、格納されている値の変更（INSERT, UPDATE, DELETE）については特定の条件を満たしたビューのみ実行できる。

その条件は以下のとおり

  • ビューの列と実テーブルの列とが1対1対応になっている
  • ビューを構成する実テーブルにおいて、実テーブルには存在するがビューに現れない列に対してNOT NULL制約がかけられていない、あるいはデフォルト値が定められている（ビュー経由の更新ではNULLが挿入されるため）

別の言い方をすると以下の条件のうちどれかを満たしているビューは更新可能ビューではない。

  • 集約関数、GROUP BY句、HAVING句を使用している
  • DISTINCT、UNIONなどの集合演算を使用している
  • 派生列（たとえば COUNT(sid)+1など）を使用している

先の例で作ったstudents_40は更新可能ビューである。

sqlite> .schema students_40
CREATE VIEW students_40
AS SELECT S.sid, sname, highschool, degree, unit FROM students AS S,
credits AS C
WHERE S.sid = C.sid and unit >= 40
/* students_40(sid,sname,highschool,degree,unit) */;

sqlite> SELECT * FROM students_40;
501|Keitha Teague|Urawa High School|1|48
501|Keitha Teague|Urawa High School|2|40
702|Eugenio Dengler|Johoku High School|1|45

更新してみる。students_40に登録されている学生の履修単位数を1増やす。

sqlite> UPDATE students_40 SET unit = unit + 1;
Error: cannot modify students_40 because it is a view

ただし、SQLite3ではビューからの更新に対応していない。

ビューの練習1_

studentsテーブルから学生ID（sid）が7で始まる学生だけを抜き出したビュー students7を作成せよ。なお、ある列においてある文字列を含む行を抜き出す条件式は以下のように記述する。

  • 特定の文字列から始まる場合： 「列名 like "文字列%"」
  • 特定の文字列で終わる場合： 「列名 like "%文字列"」
  • 特定の文字列が含まれる場合：「列名 like "%文字列%"」
  • 解答例

ビューの練習2_

以下の条件を満たすビューを作成せよ

  • ビュー名が current_students
  • 列が学生ID (sid)、学生名 (sname)、現在の学年 (degree)、出身高校 (highschool)

ヒント

  • 現在の学年の求め方は今日のページ内にある
  • 解答例

発展：_

リンク_

戻る_