人財育成プログラム

SQLプログラミング

SQLプログラミングではSQL (読み方 : エスキューエル)というデータベースを操作する言語を使用します。

データベースとは

検索や蓄積が容易にできるよう整理された情報の集まりのこと。
Wikipedia

RDBとは

リレーショナルデータベース(Relational Database)の略で、データに関係性を持たせて設計、開発するデータベースのこと。RDBを管理するデータベース管理システムを関係データベース管理システム(RDBMS)と呼ぶ。 Wikipedia

中でも今回はMySQL(読み方 : マイエスキューエル)という関係データベース管理システム(RDBMS)を使用します。

MySQLとは

世界で最も普及している、データベース管理システムのこと。
特徴としては扱えるデータ型の幅が広い点や、オープンソースのため非商用利用であれば無料で使うことができる点がある。
世界中で使われているため、多くの書籍やサイトが存在しており情報源に困らない点も人気の理由。 Wikipedia

操作に関してはphpMyAdmin(読み方 : ピーエイチピーマイアドミン)というMySQLの操作や管理をWebブラウザを通して
行うことができるWebアプリケーションを使用します。

phpMyAdminとは

phpMyAdmin は PHP で実装された MySQL の管理ツールです。
MySQL のデータベースやテーブルの作成を行ったり、データの追加や参照などをブラウザから行うことができます。
今回はこちらを使ってDBの操作を行っていきます。 Wikipedia


【準備事項・ルール】
  1. 開発ツール : Docker / phpMyAdmin
    2. インストールや設定・使用方法に関してはこちらを参考にしてください。
  2. エディタ : Visual Studio Code(推奨)
    3. 問題毎にファイルを作成し、解答（phpMyAdmin上で実行したSQL文）を保存してください。
  3. 「※phpMyAdminの機能を使用すること。」という指示がある問題以外は、必ずSQLを記述して処理を行うこと
  4. 実行結果と同じ件数・同じカラム名になるようにすること

このカリキュラムは下記の日数で修了すること。


問題設定
ストーリー

ある日の朝、君の電話が鳴り響いた。

くそー親友からの電話だ・・・
休みの日ぐらい、夕方まで寝させろ！
そんな思いも虚しく、電話は更に鳴り続ける。

俺　：「はい、もしも・・・」と言い切る前にマシンガンのように親友は喋りだす。
親友：「困ったことになった！とにかく俺の店まで来てくれ！！」
ガチャ！
訳もわからず、とりあえず親友が店長を務めるブックレンタル店へ向かう俺であった。

店についてみると親友の顔は青ざめていた。
俺　：「どうしたんだ？親友よ！」
親友：「店の売上がココ最近落ちてきて・・・」
俺　：「クビになっちゃう？」
親友：「いや、マグロ漁船に乗せられちゃう・・・」
俺　：「・・・」
親友：「おお！心の友よ！何とかしてくれるのか！」

俺は何も言っていないが親友が俺に期待している！！
そして、何よりも親友がマグロ漁船に連れていかれたら、
先月、貸してやったお金がいつ返ってくるかわからない。
それは大変困る！俺には俺の事情がある。
俺は店の売上を上げるべくこの戦いに挑むのであった！

ということで、あなたは親友を助けなくてはいけません。
そのためには店の状況を把握することから始めましょう。
このブックレンタル店は会員制です。
コンピュータ（データベース）を使用し、貸し出し＆返却を管理しています。
そして、データーベースに登録されている内容は以下のようになっております。

【サンプルデータ】

ダウンロード
問題

【CREATE USER文】

問題 : SP01 [★]
下記の条件でユーザーを作成すること。
名称 値
ユーザー名 pruser
パスワード pruser123
権限 全てのデータベースの全ての操作が可能

【実行結果確認】
※ユーザー名が正しいこと
※付与された権限が正しいこと

【CREATE DATABASE文】

問題 : SP02 [★]
下記の条件でデータベースを作成すること。

名称 値
データベース名 prdb
文字コード UTF-8

【実行結果確認】
※データベース名が正しいこと
※文字コードが正しいこと

【CREATE TABLE文】

問題 : SP03 [★]
サンプルデータに列挙されているテーブルを作成すること。
FOREIGN KEYは、親テーブルのデータを更新・削除した場合、子テーブルのデータも更新・削除されるように設定すること。
※型や条件に注意して作成すること。
※PRIMARY KEY/FOREIGN KEYやAUTO_INCREMENT等で分からない単語がある場合は、調べてからテーブルを作成すること。

※クエリ実行時はprdbにテーブルが作成されるようにすること


【実行結果確認】
※各テーブル名・カラム名(論理名/物理名)、型が正しいこと
※PRIMARY KEYやFOREIGN KEY等が正しく設定されていること

  • 会員マスタ


  • タイトルマスタ


  • 書籍マスタ


  • レンタル料金マスタ


  • 貸出履歴


  • 返却履歴

【INSERT文・外部データの取り込み】

問題 : SP04
INSERT文を使用してサンプルデータからレンタル料金マスタのデータを挿入すること。

【実行結果確認】


問題 : SP05 [★]
レンタル料金マスタ以外のテーブルのデータを挿入すること。
その際、SP04のサンプルデータから各テーブル毎のcsvファイルを作成し、作成したファイルを読み込んでデータを挿入すること。
※phpMyAdminの機能を使用すること。 ※挿入後、文字化けや抜けているデータがないか必ず確認すること。

【実行結果確認】
  • 会員マスタ


  • タイトルマスタ


  • 書籍マスタ


  • 貸出履歴


  • 返却履歴

【SELECT文・FROM句】

問題 : SP06
会員マスタの会員名をすべて出力すること。

【実行結果確認】
問題 : SP07
会員マスタの会員名、性別をすべて出力すること。

【実行結果確認】
問題 : SP08 [★]
会員マスタのテーブルから全カラムのデータをすべて出力すること。

【実行結果確認】

【SELECT文・WHERE句】

問題 : SP09
会員マスタの会員名、性別を出力すること。
ただし、男性のみを出力すること。

【実行結果確認】
問題 : SP10
会員マスタの会員名、ポイントを出力すること。
ただし、ポイントが600ポイント以上の人のみ出力すること。

【実行結果確認】
問題 : SP11
会員マスタの会員名、ポイントを出力すること。
ただし、ポイントが600ポイント未満の人のみ出力すること。

【実行結果確認】
問題 : SP12
会員マスタの会員名、ポイントを出力すること。
ただし、ポイントが600ポイント以上、700ポイント未満の人のみ出力すること。

【実行結果確認】
問題 : SP13
会員マスタの会員名、ポイントを出力すること。
ただし、ポイントが600ポイント以上の女性のみ出力すること。

【実行結果確認】
問題 : SP14
会員マスタの会員名、生年月日、性別、年齢を出力すること。
ただし、現時点で18歳以上の男性のみ出力すること。

【実行結果確認】
問題 : SP15 [★]
会員マスタの会員名、性別、ポイントを出力すること。
ただし、性別が男性または女性でポイントが600ポイント以上の人のみ出力すること。

【実行結果確認】

【SELECT文・WHERE句・IS NULL演算子】

問題 : SP16 [★]
会員マスタの会員名、性別、ポイントを出力すること。
ただし、性別がNULLの人のみ出力すること。

【実行結果確認】

【SELECT文・WHERE句・LIKE演算子】

問題 : SP17
会員マスタの会員名を出力すること。
ただし、会員名が「田」で始まる会員名のみ出力すること。

【実行結果確認】
問題 : SP18
会員マスタの会員名を出力すること。
ただし、会員名が「ー」で終わる会員名のみ出力すること。

【実行結果確認】
問題 : SP19
会員マスタの会員名を出力すること。
ただし、会員名が「田」を含む会員名のみ出力すること。

【実行結果確認】
問題 : SP20 [★]
会員マスタの会員名を出力すること。
ただし、会員名が「田」を含まない会員名のみ出力すること。

【実行結果確認】

【SELECT文・ORDER BY句】

問題 : SP21
会員マスタの会員名、ポイントを出力すること。
ただし、ポイントが少ない順に並べ替えて出力すること。

【実行結果確認】
問題 : SP22
会員マスタの会員名、ポイントを出力すること。
ただし、ポイントが多い順に並べ替えて出力すること。

【実行結果確認】
問題 : SP23 [★]
会員マスタの会員名、ポイントを出力すること。
ただし、ポイントが600以上で多い順に並べ替えて出力すること。

【実行結果確認】

【SELECT文・集計関数】

問題 : SP24
会員マスタ全会員数を出力すること。

【実行結果確認】
問題 : SP25
会員マスタの全会員ポイントの合計を出力すること。

【実行結果確認】
問題 : SP26
会員マスタの最も大きい会員ポイントを出力すること。

【実行結果確認】
問題 : SP27
会員マスタの最も小さい会員ポイントを出力すること。

【実行結果確認】
問題 : SP28 [★]
会員マスタの全会員ポイントの平均を出力すること。

【実行結果確認】

【SELECT文・GROUP BY句・HAVING句】

問題 : SP29
会員マスタの男性、女性ごとの会員数合計を出力すること。

【実行結果確認】
問題 : SP30
会員マスタの男性、女性ごとの最大会員ポイントを出力すること。

【実行結果確認】
問題 : SP31
会員マスタの男性、女性ごとの会員ポイントが600ポイント以上の会員数を出力すること。

【実行結果確認】
問題 : SP32 [★]
会員マスタの会員ポイントが600ポイント以上の会員が5人以上存在する性別を出力すること。

【実行結果確認】

【SELECT文・表の結合】

問題 : SP33
タイトルマスタ・書籍マスタより、書籍の貸出状況に不良が含まれるタイトル名を出力すること。

【実行結果確認】
問題 : SP34
貸出履歴・返却履歴より、返却日が7日前の会員名、会員番号を会員マスタから出力すること。

【実行結果確認 例】
問題 : SP35
貸出履歴・返却履歴より、返却が延滞していた・もしくは延滞中の会員名、会員番号を会員マスタから出力すること。

【実行結果確認 例】
問題 : SP36 [★]
タイトルマスタ、書籍マスタより貸出状況が不良になったタイトルを
会員マスタから、貸し出した会員名、会員番号を
貸出履歴から、貸出日付を
書籍ID昇順、貸出日付降順で出力すること。

【実行結果確認】

【副問合せ（サブクエリー）】

問題 : SP37
タイトルマスタ、書籍マスタより、全タイトル貸し出し回数の平均以下のタイトル名、貸し出し回数を出力すること。

【実行結果確認】
問題 : SP38 [★]
会員マスタの会員名、来店回数、ポイントを出力すること。
ただし、ポイントが600以上で多い順に並べ替えて出力すること。
ポイントが同じ場合は来店回数の多い順に出力すること。
貸出日付で来店回数を判断し、日付が同じ場合は来店回数を1回とカウントすること。

【実行結果確認】

【UPDATE文】

問題 : SP39
会員マスタの会員番号が1のデータの性別を「1」から「2」に変更すること。

【実行結果確認】
問題 : SP40
会員マスタの会員番号が1のデータの生年月日と会員ポイントを、それぞれ1年後と1000に変更すること。

【実行結果確認】
問題 : SP41 [★]
会員マスタの会員番号が10以下のデータの会員ポイントに200加算して更新すること。

【実行結果確認】

【DELETE文】

問題 : SP42
会員マスタの会員番号が2のデータを削除すること。

【実行結果確認】
問題 : SP43 [★]
貸出履歴上にない会員番号のデータを会員マスタより削除すること。

【実行結果確認】

【応用問題】

問題 : SP44
貸出履歴・返却履歴から、返却されていないものを洗い出してください。

【実行結果確認】
問題 : SP45 [★]
書籍マスタ・貸出履歴・返却履歴から、貸出状況の整合性の取れていないものを洗い出してください。
※整合性が取れていないとはどういうことか、先輩たちに聞いてみよう。
☆もう一歩！☆
「mysql union」,「mysql case」で検索してみよう。
こちらのページも12章だけでも軽く読んでみよう。
© 2005 - 株式会社プロルート