解答例（SQLの高度な問合せ, 2020年度）

クイズ：集合演算と結合演算_

以下のように入力すると右外部結合と同等の結果が得られる。 具体的には、左外部結合のSQL文を用いるが、テーブルの順番を入れ替えている。そして、フィールドの表示順番をテーブル入替前と同等にしている。

（左外部結合のSQL文）
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

（右外部結合と同等の結果を得られるSQL文）
sqlite> SELECT sid,sname,highschool,cname,unit FROM classes
   ...> LEFT OUTER JOIN students USING (sid);
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
603|||Database|6
603|||Web Design|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

完全外部結合と同等の結果が得られるSQL文は以下のとおり。 具体的には左外部結合と右外部結合の和集合演算で実現している。

sqlite> SELECT * FROM students LEFT OUTER JOIN classes USING (sid)
   ...> UNION
   ...> SELECT sid,sname,highschool,cname,unit FROM classes 
   ...> LEFT OUTER JOIN students USING (sid);
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||
603|||Database|6
603|||Web Design|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

クイズ：副問合せの練習1_

sqlite> SELECT DISTINCT cname FROM classes  WHERE sid IN ('701', '702'); 
Information Retrieval
Programming Lang Java
Web Design

sqlite> SELECT DISTINCT sid,cname FROM classes  WHERE sid IN ('701', '702'); 
701|Information Retrieval
701|Programming Lang Java
702|Programming Lang Java
702|Web Design

クイズ：副問合せの練習2_

sqlite> SELECT cname FROM classes WHERE sid = '701'
   ...> AND cname NOT IN (SELECT cname FROM classes WHERE sid = '702');
Information Retrieval

ビューの練習1_

学生ID(sid）が7で始まる行を抜き出すSELECT文

sqlite> SELECT * FROM students WHERE sid like '7%';
701|Peg Massingill|Shukutoku Yono High School
702|Eugenio Dengler|Johoku High School

上述のSELECT文を用いたビュー students7

sqlite> CREATE VIEW students7
   ...> AS SELECT * FROM students WHERE sid like '7%';

sqlite> .tables
classes        students       students_40  
credits        students7      total_credits

確認する。

sqlite> SELECT * FROM students7;
701|Peg Massingill|Shukutoku Yono High School
702|Eugenio Dengler|Johoku High School

ビューの練習2_

現在の学年を求めるSELECT文は以下の通り。

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

以下の条件を満たすSELECT文は以下の通り。

  • ビュー名が current_students
  • 列が学生ID (sid)、学生名 (sname)、現在の学年 (degree)、出身高校 (highschool)
sqlite> SELECT s.sid, sname, count(c.sid)+1 AS current_degree, highschool
   ...> FROM students AS S, credits AS C
   ...> WHERE s.sid = c.sid GROUP BY c.sid;
501|Keitha Teague|4|Urawa High School
601|Marybeth Boros|2|Warabi High School
701|Peg Massingill|2|Shukutoku Yono High School
702|Eugenio Dengler|2|Johoku High School

上のSELECT文を使ってビューを作成する。

sqlite> CREATE VIEW current_students (sid, sname, degree, highschool)
   ...> AS SELECT s.sid, sname, count(c.sid)+1 AS current_degree, highschool
   ...> FROM students AS S, credits AS C
   ...> WHERE s.sid = c.sid GROUP BY c.sid;

sqlite> .tables
classes           current_students  students7         total_credits   
credits           students          students_40     

sqlite> SELECT * FROM current_students;
501|Keitha Teague|4|Urawa High School
601|Marybeth Boros|2|Warabi High School
701|Peg Massingill|2|Shukutoku Yono High School
702|Eugenio Dengler|2|Johoku High School