オーロラさんの勉強帳

SESのロースキル客先常駐勤務。データベース、Excel、Excel VBA、ネットワーク、LinuxなどIT関連のことを主に書いていきます。

SQL基礎 INSERT、UPDATE、DELETE文の練習問題1 oracleデータベース

SQLでINSERT(挿入)、UPDATE(更新)、DELETE(削除)を練習する記事です。testidkanriというテーブルでIDの管理をする設定で、INSERT文、UPDATE文、DELETE文の練習問題を解いていきたいと思います。

実際にSQLを打って、手を動かして勉強してもらえれば嬉しいです!!

 

 

INSERT文の構文

INSERT文はテーブルに行を追加できます。

 

<構文1.列名を指定する方法>

INSERT INTO 表名 (列名1,列名2,列名3,・・・・)

VALUES (値1,値2,値3,・・・・);

 

<構文2.列名を指定しない方法>

INSERT INTO 表名

VALUES(値1,値2,値3,・・・・);

※全列に値を指定する必要があります。NULLの列にはNULLを明記します。

 

UPDATE文の構文

UPDATE文はテーブルの値を更新できます。

 

<構文>

UPDATE 表名

SET 列名1 = 値1,列名2 = 値2,・・・・

[WHERE 条件式];

 

注意:WHERE句がない場合、テーブルの指定した列の全ての値が変更されてしまいます。業務などで数十万件のデータが全て更新されたら、リカバリーも大変なのでWHERE句の指定漏れには注意が必要です。

 

DELETE文の構文

DELETE文は行を削除することができます。

 

<構文>

DELETE FROM 表名 [WHERE 条件式];

 

注意:UPDATE文と同様にWHERE句の指定漏れに注意が必要です。WHERE句がない場合はテーブルの全ての行を削除してしまいます。

 

COMMIT文

INSERT、UPDATE、DELETEはDML(データ操作文)なので、実行後にそのトランザクション内の変更を確定するためのCOMMIT文を実行する必要があります。

SQL実行ツールの設定によってはCOMMIT文が不要な場合もあります。

 

<構文>

COMMIT;

 

ROLLBACK文

INSERT、UPDATE、DELETEを実行後に間違いに気が付いた場合、ROLLBACK文を実行することで、そのトランザクションの変更を全て取り消すことが可能です(COMMITする前なら可能。COMMIT後は不可)。

SQL実行ツールの設定でCOMMIT文を使わず、処理を確定させている場合はROLLBACKできないので設定には注意が必要です。

 

<構文>

ROLLBACK;

 

準備1.練習用テーブル(testidkanri)の作成

今回、INSERT、UPDATE、DELETEを練習するための練習用テーブルtestidkanriを、以下のSQLを実行して作成します。

 

create table testidkanri (
 id NUMBER(6) CONSTRAINT pk_idkanriID PRIMARY KEY,
 name VARCHAR2(15) NOT NULL,
 department_id NUMBER(3),
 country_id NUMBER(3),
 hiredate DATE
);

 

準備2.練習用データの挿入

以下の4つのINSERT文で4レコード挿入します。

INSERT INTO testidkanri
VALUES(1,'sato',1,1,'1990/1/1');

INSERT INTO testidkanri
VALUES(2,'suzuki',1,1,'1970/4/1');

INSERT INTO testidkanri
VALUES(3,'tanaka',3,1,'1980/1/10');

INSERT INTO testidkanri
VALUES(4,'okada',2,1,'1999/2/1');

 

f:id:auroralights:20200329225601p:plain

testidkanriテーブル

 

練習問題1

以下の2名のユーザーが増えたので、testidkanriテーブルに追加してください。

 

<追加1>

ID:5

NAME:asai

DEPARTMENT_ID:NULL

COUNTRY_ID:2

HIREDATE;2020/4/1

 

<追加2>

ID:6

NAME:chiba

DEPARTMENT_ID:NULL

COUNTRY_ID:2

HIREDATE:2020/4/1

 

 

練習問題1の回答

2名のユーザーを追加するので「INSERT」文でユーザーの情報をテーブルに挿入する必要があります。

 

 <列を指定した場合>

INSERT
INTO testidkanri(id, name, country_id, hiredate)
VALUES (5, 'asai', 2, '2020/4/1');
INSERT
INTO testidkanri(id, name, country_id, hiredate)
VALUES (6, 'chiba', 2, '1990/4/1');

 

<列を指定しなかった場合>

INSERT
INTO testidkanri
VALUES (5, 'asai', NULL, 2, '2020/4/1');
INSERT
INTO testidkanri
VALUES (6, 'chiba', NULL, 2, '1990/4/1');
 
 
INSERTの結果は以下のSELECT文で取得します。
select * from testidkanri;

f:id:auroralights:20200329230856p:plain

INSERT実行後のtestidkanriテーブル

 

INSERT文を確定するために以下のSQL(COMMIT)を実行します。

COMMIT;

 

 

練習問題2

練習問題1で追加した2名のDEPARTMENT_IDが決まったので、DEPARTMENT_IDを以下の通り更新してください。

 

ID:5

DEPARTMENT_ID:3

 

ID:6

DEPARTMENT_ID:4 

 

 

 

 

練習問題2の回答

更新なのでUPDATE文でテーブルを更新します。

 

UPDATE testidkanri
SET
  department_id = 3
WHERE
  id = 5;
UPDATE testidkanri
SET
  department_id = 4
WHERE
  id = 6;
 
 
以下のSELECT文でUPDATEの結果を確認します。
select  * from  testidkanri
where  id IN ('5', '6');

f:id:auroralights:20200329231718p:plain

UPDATE後のtestidkanriテーブル
UPDATE文を確定するために以下のSQL(COMMIT)を実行します。
 
 

練習問題3

IDが「1」のユーザーが退職のため、テーブルから情報を削除する必要があります。

 

 

 

練習問題3の回答

削除のため、DELETE文を使います。

 

DELETE FROM testidkanri WHERE id = 1;

 

以下のSELECT文でDELETE後のテーブルの情報を確認します。

select * from  testidkanri;

f:id:auroralights:20200329232049p:plain

DELETE後のtestidkanriテーブル

DELETE文を確定するために以下のSQL(COMMIT)を実行します。

 

 

練習問題4

hiredateが1995年4月1日以前のユーザーが異動のため、対象ユーザーをDEPARTMENT_IDを6に変更してください。

 

 

 

練習問題4の回答

hiredateが1995年4月1日以前という条件を指定して、UPDATEする必要があります。

 

UPDATEする前に対象のユーザーを確認します。

select * from testidkanri where hiredate < '1995/4/1';

 

UPDATEをします。この時の条件(WHERE文以降)は、上記でSELECTしたものをそのままコピー&ペーストして使います。

update testidkanri
set
  department_id = 6
where
  hiredate < '1995/4/1';

 

COMMITで処理を確定します。

COMMIT;

 

f:id:auroralights:20200330010015p:plain

UPDATE後のtestidkanriテーブル

 

練習問題5

以下の5つのユーザーを登録してください。IDとNAME以外はNULLです。

 

ID:1 NAME:araki

ID:7 NAME:aragaki

ID:8 NAME:yamazaki

ID:9 NAME:aoi

ID:10 NAME:nakahara

 

 

 

練習問題5の回答

INSERT文でレコードを追加します。ID、NAME以外はNULLなので、値が入るIDとNAMEを指定したINSERT文で対応します。

※全列指定などその他の方法でも良いです。

 

INSERT
INTO testidkanri(id,name)
VALUES (1,'araki');

INSERT
INTO testidkanri(id,name)
VALUES (7,'aragaki');

INSERT
INTO testidkanri(id,name)
VALUES (8,'yamazaki');

INSERT
INTO testidkanri(id,name)
VALUES (9,'aoi');

INSERT
INTO testidkanri(id,name)
VALUES (10,'nakamura');

 

COMMIT;

 

f:id:auroralights:20200330220534p:plain

INSERT後のtestidkanriテーブル

 

練習問題6

ID1、7、8はDEPARTMENT_IDが10、COUNTRY_IDが5、HIREDATEが2020/6/1

ID9、10はDEPARTMENT_IDが11、COUNTRY_IDが3、HIREDATEが2020/6/15でテーブルを更新してください。

 

 

練習問題6の回答

2つのUPDATE文で対応します。

 

<ID1、7、8用のUPDATE文>

UPDATE testidkanri
SET department_id =10, country_id = 5, hiredate = '2020/6/1'
WHERE id
IN ('1','7','8');

 

COMMIT;

 

<ID9、10用のUPDATE文>

UPDATE testidkanri
SET department_id =11, country_id = 3, hiredate = '2020/6/15'
WHERE id IN ('9','10');

 

COMMIT;

 

f:id:auroralights:20200330221411p:plain

UPDATE後のtestidkanriテーブル