カテゴリ:5.データベース > 5.2 データ操作

SQL(Structured Query Language)とは、データベースを操作する言語です。SQL文を使うことで、表からデータを選択したり、追加、削除、変更などが行えます。

■データベースの操作
基本情報のシラバスには「(1)データベースの操作関係データベースのデータの操作として,データの挿入,更新,集合演算(和,差,積,直積),関係演算(選択,射影,結合,商)などの代表的なデータの操作を理解する。」とあります。
ここで、選択、射影、結合について説明します。
①選択
SELECTを使って、行を取り出します。
SELECT * FROM 表 WHERE 行を抽出する条件
②射影
SELECTを使って列を取り出します。
SELECT フィールドA、フィールドB FROM 表
※フィールドA,Bが、取り出す列
③結合
SELECTを使って、複数の表を結合します。
SELECT * FROM 表1、表2 WHERE 表1.フィールドA=表2.
フィールドB
※表1と表2で一致する属性(フィールド)をもとに、
表を結合します。

※実際にやってみたい場合は、以下をhttp://sqlfiddle.com/などで試しましょう。
http://sm.seeeko.com/archives/15876965.html

過去問(H28秋AP午前問28)を見てみましょう。
問28 関係データベースにおける実表と導出表に関する記述のうち,適切なものはどれか。

ア 実表に対する射影,結合などによって導出表が得られる。
イ 導出表は,データを参照する場合だけに用いる。
ウ 導出表は,複数の実表から得られる表である。
エ 導出表は,元の実表の列だけから成る。



正解は、アです。

■データベース言語
SQLの言語はいくつかに分かれます。応用情報試験のシラバスには「データベース言語は,DDL(Data Definition Language:データ定義言語)とDML(Data Manipulation Language:データ操作言語)などに大別される」とあります。もう一つ、DCL(Data Control Language:データ制御言語)があります。

①データ操作言語
 データ操作言語の代表はSELECT(選択)です。それ以外に、INSERT(行の挿入),UPDATE(値の更新) ,DELETE(行の削除)などがあります。

②データ定義言語
データ定義言語には、CREATE文があります。
たとえば、表を作る場合は CREATE TABLE
VIEWを作る場合は CREATE VIEW

③データ制御言語
アクセス権を付与するGRANTや、削除するREVOKEがあります。

■文法
SQLの文法も理解しておきましょう。
基本となるSQL文はSELECT文です。なので、SELECT文でSQLの基本を理解しましょう。
SELECT 表の列名 FROM 表 WHERE 条件

■句
・WHERE 条件を指定する
・GROUP BY グループ化する
・ORDER BY 並び替える
・HAVING →GROUP BYでグループ化している場合の条件を指定します。たとえば、グループ化して平均値(AVG)を求め、そのAVGの値が70以上など(HAVING AVG(得点)>= 70)


過去問(H25春FE午前問28)をみてみましょう
問28“中間テスト表からクラスごと,教科ごとの平均点を求め,クラス名,教科名の昇順に表示するSQL文中のaに入れるべき字句はどれか。
中間テスト(クラス名,教科名,学生番号,名前,点数)
〔SQL文〕
 SELECT クラス名,教科名, AVG(点数) AS 平均点 FROM 中間テスト [ a ]
 
ア GROUP BYクラス名,教科名 ORDER BY クラス名, AVG(点数)
イ GROUP BY クラス名,教科名 ORDER BY クラス名,教科名
ウ GROUP BY クラス名,教科名,学生番号 ORDER BY クラス名,教科名,平均点
工 GROUP BYクラス名,平均点 ORDER BY クラス名,教科名




実際に実行してみた様子は以下です。
http://sm.seeeko.com/archives/15876965.html

正解はイ

H28秋AP午前
問29 “サッカーチーム”表と“審判”表から,条件を満たす対戦を導出するSQL文のaに入れる字句はどれか。

 〔条件〕
  ・出場チーム1のチーム名は出場チーム2のチーム名よりもアルファベット順で先にくる。
  ・審判は,所属チームの対戦を担当することはできない。


サッカーチーム
チーム名
X
Y
Z
審判
氏名
所属チーム名
佐藤健太
X
鈴木翔太
Y
高橋拓也
Z


対戦
出場チーム1
出場チーム2
審判氏名
X
Y
高橋拓也
X
Z
鈴木翔太
Y
Z
佐藤健太


〔SQL文〕
 SELECT A.チーム名 AS 出場チーム1,B.チーム名 AS 出場チーム2,C.氏名 AS 審判氏名
    FROM サッカーチーム AS A,サッカーチーム AS B,審判 AS C
    WHERE A.チーム名 < B.チーム名 AND [  a  ]


ア (A.チーム名 <> C.所属チーム名 OR B.チーム名 <> C.所属チーム名)
イ C.所属チーム名 NOT IN (A.チーム名,B.チーム名)
ウ EXISTS
    (SELECT * FROM 審判 AS D WHERE A.チーム名 <> D.所属チーム名 AND B.チーム名 <> D.所属チーム名)
エ NOT EXISTS
    (SELECT * FROM 審判 AS D WHERE A.チーム名=D.所属チーム名 OR B.チーム名 = D.所属チーム名)





【正解】エ

H27秋AP午前
問29    "倉庫別商品在庫集計”表から在庫数の合計を求めたい。倉庫番号'C003'の倉庫で在庫数が100 以上の商品に対して,全ての倉庫における在庫数の合計を求めるSQL文のaに入る適切な字句はどれか。ここで,該当する商品は複数存在するとともに在庫数が100未満の商品も存在するものとする。また,実線の下線は主キーを表す。

倉庫別商品在庫集計(倉庫番号,商品コード,在庫数)

〔SQL文〕
SELECT 商品コード, SUM(在庫数) AS 在庫合計 FROM 倉庫別商品在庫集計
      WHERE [  a  ]
      GROUP BY 商品コード

ア 商品コード = (SELECT 商品コード FROM 倉庫別商品在庫集計
               WHERE倉庫番号 = 'C003' AND 在庫数 >= 100)
イ 商品コード = ALL(SELECT 商品コード FROM 倉庫別商品在庫集計
               WHERE倉庫番号 = 'C003' AND 在庫数 >= 100)
ウ 商品コード IN(SELECT 商品コード FROM 倉庫別商品在庫集計
               WHERE 倉庫番号 = 'C003' AND 在庫数 >= 100)
エ EXISTS (SELECT * FROM 倉庫別商品在庫集計
               WHERE 倉庫番号 = 'C003' AND 在庫数 >= 100)





【正解】ウ

H27春AP午前
間26 "電話番号"列にNULLを含む"取引先"表に対して,SQL文を実行した結果の行数は幾つか。
20170906_3-1
〔SQL文〕
SELECT * FROM 取引先 WHERE 電話番号 NOT LIKE '010%'

ア 1   イ 2     ウ 3   エ 4





NOT LIKE '010%'はあいまい検索で「010」で始まるもの以外ということを表します。
NULL(ヌルと呼ぶ人が多い)は「値が無い」という意味です。LIKEを使った場合、NULLを抽出することはできません。よって、2行目、4行目、5行目の3つが正解です。
【正解】ウです。

(H25春AP午前問29と同じ)
問27 埋込みSQLにおいて,問合せによって得られた導出表を1行ずつ親プログラムに引き渡す操作がある。この操作と関係の深い字句はどれか。

ア CURSOR
イ ORDER BY   
ウ UNION
エ UNIQUE





【正解】ア

H26秋AP午前
問25 表に対するSQLのGRANT文の説明として,適切なものはどれか。

ア パスワードを設定してデータベースへの接続を制限する。
イ ビューを作成して,データベースへのアクセス処理を隠蔽し,表を直接アクセスできないようにする。
ウ 表のデータを暗号化して,第三者がアクセスしてもデータの内容が分からないようにする。
エ 表の利用者に対し,表への問合せ,更新,追加,削除などの操作を許可する。





【正解】エ

H26春AP午前
問28 "東京在庫" 表と "大阪在庫" 表に対して,SQL文を実行して得られる結果はどれか。ここで,実線の下線は主キーを表す。
20170906_3-2





【正解】エ

H25秋AP午前
(H21春AP午前問31と同じ)
問27 クライアントサーバシステムにおけるストアドプロシージャに関する記述のうち,誤っているものはどれか。
ア 機密性の高いデータに対する処理を特定のプロシージャ呼出しに限定することによって,セキュリティを向上させることができる。
イ システム全体に共通な処理をプロシージャとして格納しておくことによって,処理の標準化を行うことができる。
ウ データベースへのアクセスを細かい単位でプロシージャ化することによって,処理性能(スループット)を向上させることができる。
エ 複数のSQL文から成る手続を1回の呼出しで実行できるので,クライアントとサーバの間の通信回数を減らすことができる。





【正解】ウ

問31 地域別に分かれている同じ構造の三つの商品表,"東京商品","名古屋商品","大阪商品"がある。次のSQL文と同等の結果が得られる関係代数式はどれか。ここで,三つの商品表の主キーは"商品番号"である。

SELECT * FROM 大阪商品
   WHERE 商品番号 NOT IN (SELECT 商品番号 FROM 東京商品)
UNION
SELECT * FROM 名古屋商品
   WHERE 商品番号 NOT IN (SELECT 商品番号 FROM 東京商品)

ア (大阪商品 ∩ 名古屋商品)-東京商品
イ (大阪商品 ∪ 名古屋商品)-東京商品
ウ 東京商品-(大阪商品 ∩ 名古屋商品)
エ 東京商品-(大阪商品 ∪ 名古屋商品)





【正解】イ

H25春AP午前
問27 クライアントサーバシステムにおいて,クライアント側からストアドプロシージャを利用したときの利点として,適切なものはどれか。

ア クライアントとサーバの間の通信量を削減できる。
イ サーバ内でのデータベースファイルへのアクセス量を削減できる。
ウ サーバのメモリ使用量を削減できる。
エ データの格納領域を削減できる。





【正解】ア

問30 A表は外部キーによってB表から参照されている。ここで,A表の行を削除するとき,それを参照しているB表の行を同時に全て削除することを指定するSQLの参照動作はどれか。

ア CASCADE
イ CONSTRAINT
ウ NO ACTION
エ REFERENCES





【正解】ア

H24春AP午前
問26 販売価格が決められていない“商品"表に,次のSQL文を実行して販売価格を設定する。このとき,販売ランクがbの商品の販売価格の平均値は幾らか。

 UPDATE 商品 SET 販売価格 =
        CASE
            WHEN 販売ランク = 'a' THEN 単価 * 0.9
            WHEN 販売ランク = 'b' THEN 単価 - 500
            WHEN 販売ランク = 'c' THEN 単価 * 0.7
            ELSE  単価
 END

H24春AP午前問26

 ア 1,675     イ 2,100     ウ 2,250     エ 2,500






【正解】エ

H23秋AP
問30 更新可能なビューを作成するSQL文はどれか。ここで, SQL文中に現れる表は全て更新可能とする。
ア CREATE VIEW 高額商品(商品番号,商品名,商品単価)
   AS SELECT 商品番号,商品名,商品単価 FROM 商品 WHERE 商品単価 > 1000
イ CREATE VIEW 受注商品(商品番号)
   AS SELECT DISTINCT 商品番号 FROM 受注
ウ CREATE VIEW 商品受注(商品番号,受注数量)
   AS SELECT 商品番号,SUM(受注数量) FROM 受注 GROUP BY 商品番号
エ CREATE VIEW 商品平均受注数量(平均受注数量)
   AS SELECT AVG(受注数量) FROM 受注





【正解】ア

H23特別AP午前
問30   "社員"表ど人事異動"表から社員ごとの勤務成績の平均を求める適切なSQL文はどれか。ここで,求める項目は,社員コード,社員名,勤務成績(平均)の3項目とする。
H23特別AP午前問30表

ア SELECT 社員.社員コード,社員名,AVG(勤務成績) AS "勤務成績(平均)"
     FROM 社員,人事異動
     WHERE 社員.社員コード = 人事異動.社員コード
     GROUP BY 勤務成績
イ SELECT 社員.社員コード,社員名,AVG(勤務成績) AS  "勤務成績(平均)" 
     FROM 社員,人事異動
     WHERE 社員.社員コード = 人事異動.社員コード
     GROUP BY 社員.社員コード,社員.社員名
ウ SELECT 社員.社員コード,社員名,AVG(勤務成績)/COUNT(勤務成績) 
                            AS  "勤務成績(平均)"
     FROM 社員,人事異動
     WHERE 社員.社員コード = 人事異動.社員コード
     GROUP BY 社員.社員コード,社員.社員名
エ SELECT 社員.社員コード,社員名,MAX(勤務成績)/COUNT(*)
                            AS "勤務成績(平均)"
     FROM 社員,人事異動
     WHERE 社員.社員コード = 人事異動.社員コード
     GROUP BY 社員.社員コード,社員.社員名





【正解】イ




■H22春AP午前
問33 表Aから実行結果Bを得るためのSQL文はどれか。
H22春AP問33
ア SELECT 部署コード,社員コード,名前 FROM A
       GROUP BY 社員コード
イ SELECT 部署コード,社員コード,名前 FROM A
       GROUP BY 部署コード
ウ SELECT 部署コード,社員コード,名前 FROM A
       ORDER BY 社員コード,部署コード
エ SELECT 部署コード,社員コード,名前 FROM A
       ORDER BY 部署コード,社員コード





【正解】エ

ef6167f8 

SQLって簡単に試せないですよね?
データベースを作るのも大変そうです。



Webで簡単に試せるツールがあります。
SQLを実際にやってみましょう。
問題は、H25春FE午前問28です。
http://sm.seeeko.com/archives/15877091.html

■プラットフォーム
Webで簡単にSQLのテストができる以下のサイトを活用しました。
http://sqlfiddle.com/
・ブラウザはChrome
・MySQL5.6を選択

※または、いろいろなプログラムが書けるWebサイトであるpaiza.ioも使いやすいです。
https://paiza.io/ja

■テーブルの作成と値のセット
①TABLEの作成と値のセットを同時にやります。
CREATE TABLE TEST
    (`クラス名` varchar(3), `教科名` varchar(3), `学生番号` int, `名前` varchar(5), `点数` int)
;
   
INSERT INTO TEST
    (`クラス名`, `教科名`, `学生番号`, `名前`, `点数`)
VALUES
    ('2A', '英語', 11, '山田太郎',70),
    ('2A', '英語', 12, '鈴木太郎',80),
    ('2A', '英語', 13, '山田次郎',75),
    ('2A', '数学', 11, '山田太郎',60),
    ('2A', '数学', 12, '鈴木太郎',85),
    ('2A', '数学', 13, '山田次郎',95),
    ('2B', '英語', 11, '伊藤太郎',95),
    ('2B', '英語', 12, '大木太郎',85),
    ('2B', '英語', 13, '斉藤次郎',75),
    ('2B', '数学', 11, '伊藤太郎',55),
    ('2B', '数学', 12, '大木太郎',85),
    ('2B', '数学', 13, '斉藤次郎',85)
;
■SQLの実行
・SELECT文の実行
・選択肢ア
select `クラス名`,`教科名`,AVG(`点数`) AS AVG from TEST GROUP BY `クラス名`,`教科名` ORDER BY AVG;
・選択肢イ
select `クラス名`,`教科名`,AVG(`点数`) AS AVG from TEST GROUP BY `クラス名`,`教科名` ORDER BY `クラス名`,`教科名`;

コピペで実行できないかもしれないので、テキストファイルを貼っておきます。
http://sm.seeeko.com/SQL.txt

■その他
テーブルを作るときに、主キー制約を付ける場合はPRYMARY KEYを付けます。こうすることで、重複する行を作成できなくなります。
CREATE TABLE TEST
    (`クラス名` varchar(3), `教科名` varchar(3) , `学生番号` int , `名前` varchar(5), `点数` int, PRIMARY KEY(`クラス名` , `教科名`, `学生番号`))
;

■H28春FE午前問27
この問題をもとに、テーブルを作成して設問にある操作を実行してみました。
また、基本操作として、選択、射影、結合の結果も表示します。

/* テーブルXの作成 */
CREATE TABLE X
    (`学生番号` varchar(3), `氏名` varchar(8), `学部コード`  varchar(5))
;
   
INSERT INTO X
    (`学生番号`, `氏名`, `学部コード`)
VALUES
    ('1', '山田太郎', 'A'),
    ('2', '情報一郎', 'B'),
    ('3', '鈴木花子', 'A'),
    ('4', '技術五郎', 'B'),
    ('5', '小林次郎', 'A'),
    ('6', '試験桃子', 'A')
;
CREATE TABLE Y
    (`学部コード` varchar(3), `学部名` varchar(8))
;
   
INSERT INTO Y
    (`学部コード`, `学部名`)
VALUES
    ('A', '工学部'),
    ('B', '情報学部'),
    ('C', '文学部');
   
/* SQL文の実行 */
select 学部名,学生番号,氏名 from X,Y
  where X.学部コード=Y.学部コード AND X.学部コード='B';
   
   
/* テーブルXの表示 */
select * from X;
/* 選択 */
select * from X where 学部コード='A';
/* 射影 */
select 学生番号,氏名 from X;
/* 結合 */
select * from X,Y
  where X.学部コード=Y.学部コード AND X.学部コード='B';

副問い合わせとは、SQL文の中でSQL文を実行する処理です。
メインの処理を主問い合わせ、サブとなるのを副問い合わせと言います。
副問い合わせには、INとEXISTSがあります。

(1)IN
たとえば、過去問(H22春FE午前問31)を参考にすると、以下です。
select 商品番号 FROM 商品
where 商品番号 IN (SELECT 商品番号 FROM 在庫);

これは、「主問い合わせ(副問い合わせ)」の流れになっています。この例では、副問い合わせの結果を踏まえて主問い合わせが行われます。
具体的には、副問い合わせで、在庫がある商品番号が出されます。そして、そこにある商品番号を表示しています。
e1f51e18 

ということは、在庫がある商品番号を表示しているのですね。
はい、そうです。

(2)EXISTS
さきのINで表現したSQL文をEXSITSを使うと以下のようになります。比較のため、INの場合と両方を記載します。
①INの場合
select 商品番号 FROM 在庫
where 商品番号 IN (SELECT 商品番号 FROM 商品);

②EXISTSの場合
select 商品番号 FROM 在庫
where EXISTS (SELECT 商品番号 FROM 在庫 WHERE 商品.商品番号=在庫.商品番号);

EXISTSの場合は、INと違い、True かFalseしか返しません。
また、INの場合、副問い合わせの検索結果をもとに主問い合わせを実行します。EXISTSの場合は、副問い合わせをしながら(1行ずつ)、主問い合わせを1行ずつ実行します(おそらく)。このあたりが、INとEXSITSの違いです。

http://sqlfiddle.com/などを使って試してみたい方は、以下を活用してください。
商品テーブルをX、在庫テーブルをYとしています。

----ここから
/* テーブルXの作成 */
CREATE TABLE X
    (`商品番号` int, `商品名` varchar(8), `単価`  int)
;
    
INSERT INTO X
    (`商品番号`, `商品名`, `単価`)
VALUES
    ('1', 'エアコン', '50000'),
    ('2', 'ミシン', '30000'),
    ('3', 'テレビ', '100000'),
    ('4', '冷蔵庫', '140000'),
    ('5', 'ドライヤー', '12000'),
    ('6', '掃除機', '24000')
;
CREATE TABLE Y
    (`倉庫番号` varchar(3), `商品番号` int, `在庫数` int)
;
    
INSERT INTO Y
    (`倉庫番号`, `商品番号`, `在庫数`)
VALUES
    ('A', '1', '100'),
    ('B', '2', '200'),
    ('C', '3', '100');

ーーーここまで

では実際の問題をみてみましょう。

-----------
問31 "商品"表,“在庫"表に対する次のSQL文と,同じ結果が得られるSQL文はどれか。ここで,下線部は主キーを表す。

SELECT 商品番号 FROM 商品
 WHERE 商品番号 NOT IN (SELECT 商品番号 FROM 在庫)

商品(商品番号,商品名,単価)
在庫(在庫番号,商品番号,在庫数)

ア SELECT 商品番号 FROM 在庫
 WHERE EXISTS (SELECT 商品番号 FROM 商品)
イ SELECT 商品番号 FROM 在庫
 WHERE NOT EXISTS (SELECT 商品番号 FROM 商品)
ウ SELECT 商品番号 FROM 商品
 WHERE EXISTS (SELECT 商品番号 FROM 在庫
  WHERE商品.商品番号=在庫.商品番号)
エ SELECT 商品番号 FROM 商品
 WHERE NOT EXISTS (SELECT 商品番号 FROM 在庫
  WHERE商品.商品番号=在庫.商品番号)
-----------

正解はエです。

以下、少し難しい問題です。

H20秋SW午前問66
問66  "社員"表から,男女それぞれの最年長社員を除くすべての社員を取り出すSQL文とするためにaに入る副問合せはどれか。ここで,“社員"表は次の構造とし,下線部は主キーを表す。

社員(社員番号,社員名,性別,生年月日) ※主キーは社員番号

SELECT 社員番号,社員名 FROM 社員 AS S1 WHERE 生年月日 > ( a )
ア SELECT MIN(生年月日) FROM 社員 AS S2 GROUP BY S2.性別
イ SELECT MIN(生年月日) FROM 社員 AS S2 WHERE S1.生年月日 > S2.生年月日 OR S1.性別=S2.性別
ウ SELECT MIN(生年月日) FROM 社員 AS S2 WHERE S1.性別 = S2.性別
工 SELECT MIN(生年月日) FROM 社員 GROUP BY S2.性別

→正解はウ

・stored(サーバに蓄えられた) procedure(手続きorプログラム)
・クライアントからSQL文などで要求を毎回送るのではなく、あらかじめサーバ側に一連のProcedure(プログラム)を作成しておく。クライアントからは値だけを送る。

・過去問(H25秋FE午前問28)ではストアドプロシージャに関して、「クライアントサーバシステムにおいて,利用頻度の高い命令群をあらかじめサーバ上のDBMSに用意しておくことによって,データベースアクセスのネットワーク負荷を軽減する仕組み」と述べられています。

効果は「通信回数と通信量の削減」

例えば、退職する社員の日割りの給料計算のために、以下の一連の処理をする場合を考えます。
・人事データベースから、その人の基本給および能力給を抽出
・1か月間の残業日数を集計
・売り上げ額の集計から、成果加算額を積算
・集計した給料をもとに、退職日に応じて日割りの金額を給料データベースに反映
・・・

1.通常のプログラムの場合
サーバクライアント

①SELECT kihon,nouryoku FROM jinji_db ****
②SELECT sum(zangyo) FROM kintai_db ****
②SELECT sum(uriage) FROM uriage_db ****
④UPDATE salary SET ****   
・・・・・             各処理

このように、各処理の回数だけ処理が必要


2.ストアドプロシージャを使った場合
サーバクライアント


ProcedureKyuryo、1023,1218,・・・




プロシージャ名と引数(この場合は、退職する社員の社員番号と退職日)を渡すだけで計算が終わります。クライアントとサーバでやり取りする命令が1回で済みますし、データ量も削減できます。

【関連問題】
SM平成18年問5
SM平成17年問5

H27年秋AP午前
問26 クライアントサーバシステムにおけるストアドプロシージヤに関する記述のうち,適切でないものはどれか。
ア 機密性が高いデータに対する処理を特定のプロシージヤ呼出しに限定することによって,セキュリティを向上させることができる。
イ システム全体に共通な処理をプロシージヤとして格納しておくことによって,処理の標準化を行うことができる。
ウ データベースへのアクセスを細かい単位でプロシージヤ化することによって,処理性能(スループット)を向上させることができる。
エ 複数のSQL文から成る手続を1回の呼出しで実行することによって,クライアントとサーバの間の通信回数を減らすことができる。
【正解】ウ

↑このページのトップヘ