解答例(SQLite3を用いたSQL入門, 2020年度)
練習: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 orers 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