SQL入門の解答例

練習：INSERTとSELECTの復習_

以下のように入力すると指定されたテーブルを作成することができる。

> INSERT INTO goods VALUES ('A02', 'Office Paper A3', 4000),('B01', 'Toner Cartridge Black', 25000),('C01', 'White Board', 14000);
> SELECT * FROM goods;

> INSERT INTO customers VALUES (0201, 'C corp.', 'Hirosaki city, Aomori'), (1301, 'A corp.', 'Chiyoda-ku, Tokyo'), (1302, 'B corp.', 'Mitaka city, Tokyo'), (2301, 'D corp.', 'Chigusa-ku, Nagoya city, Aichi'), (4001,'E corp.', 'Iizuka city, Fukuoka');
> SELECT * FROM customers;

> INSERT INTO orders values (1, 4001, "B01", 1);
> SELECT * FROM orders;

練習：UPDATE 復習_

以下のように入力すると指定されたテーブルを作成することができる。

sqlite> update goods set price=2000 where gid = 'A01';
sqlite> update goods set price=4000 where gid = 'A02';
sqlite> update goods set price=1500 where gid = 'A03';
sqlite> update goods set price=25000 where gid = 'B01';
sqlite> update goods set price=14000 where gid = 'C01';
sqlite> select * from goods;

練習：DELETEの復習_

sqlite> SELECT * FROM customers WHERE address like "%-ku%";
sqlite> DELETE FROM customers WHERE address like "%-ku%";
sqlite> SELECT * FROM customers WHERE address like "%-ku%";

練習：CREATE VIEWの復習_

sqlite> create view tokyo_corp as select * from customers where address like "%Tokyo%";

sqlite> .tables
customers   goods       low_prices  orders      tokyo_corp

sqlite> select * from tokyo_corp;
1301|A corp.|Chiyoda-ku, Tokyo
1302|B corp.|Mitaka city, Tokyo

練習：集合演算と結合演算_

以下のように入力すると右外部結合と同等の結果が得られる。 具体的には、左外部結合の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