close
プログラミング クラウド Microsoft Azure 情報処理資格 基本情報技術者 IT基礎 応用情報技術者 開発・設計方法 オブジェクト指向 内定者・新人研修 プログラミング基礎 アルゴリズム コンピュータ数学 内定者研修 新人研修 ヒューマンスキル プロジェクトマネジメント プレゼンテーション リーダーシップ 組織マネジメント ネゴシエーション ロジカルシンキング Java UI/UX HTTP JavaScript基礎 情報処理資格 ネットワークスペシャリスト ネットワーク インターネットルーティング応用 IPアドレス データベース応用 SQL応用 パフォーマンスチューニング データベース設計 ER図 概念設計(データベース) 論理設計(データベース) IT資格 Linux基礎 OS・システム基盤 セキュリティ TCP/IP OSI参照モデル データベースセキュリティ ファイアウォール 標的型攻撃 SQLインジェクション ネットワーク基本設計 CCNA Cisco プロジェクトマネジメント資格 情報処理資格プロジェクトマネージャ 情報処理安全確保支援士 人事給与 財務会計 管理会計 簿記 生産管理 在庫管理 ERP バランススコアカード 情報処理資格 ITアーキテクト 情報処理資格 ITストラテジスト 情報処理資格 ITサービスマネジメント 情報処理資格 システム監査 PMBOK® PMP® プロジェクト計画 WBS リスクコントロール ITIL ITサービスマネジメント 要求定義 要件定義 見積手法 ビジネスインダストリ 業種・業界知識 業務知識 提案力 ソフトウェアテスト基礎 情報処理資格 データベーススペシャリスト ハードウェア基礎 外部設計(基本設計) 内部設計(詳細設計) データベース基礎 SQL基礎 RDBMS 物理設計(データベース) C++ Ruby MVC基礎 Webアプリケーション開発 JavaEE Javaプログラミング応用 フレームワーク MVC応用 Spring フレームワーク ソフトウェアテスト応用 テスト手法 JUnit スマートフォンアプリ開発 Androidアプリ開発 C# 基礎 C# 応用 負荷テスト Javaプログラミング基礎 ソフトウェアテスト コーチング メンタリング HTML/CSS サーバー構築 仮想化技術 KVS (NoSQL) アジャイル スクラム ファシリテーション C言語 ITパスポート JSTQB データサイエンス 単体テスト ユニットテスト キャリアアップ インターネットルーティング基礎 パケット解析 LAN構築 データベース データサイエンティスト トレンド 障害対応 インフラ監視 HTTP/2.0 コンピュータサイエンス VPN ネットワーク物理設計 データベース障害 JavaScript モダンJS (Modern JavaScript) 応用 MVS応用 バックアップ/リカバリ 分散処理 Hadoop Hive Python AI 深層学習(DeepLearning) CentOS Linux応用 Zabbix シェルスクリプト Infrastructure as Code Windowsサーバー基礎 内部設計 Docker DevOps Windowsサーバー応用 NginX chef Ainsible ロジカルライティング R テスト自動化 Jenkins Git 継続的インテグレーション (CI) バージョン管理 Vagrant 要求分析 Redmine 継続的インテグレーション(CI) 継続的デリバリー (CD) ヒューマンリソース管理 Web API マイクロサービス コミュニケーション 業務知識/業界知識 マーケティング 語学 AWS 法務 IoT ビジネスマナー OJT 業務効率化 表計算ソフト オフィスソフト コンプライアンス フロントエンド Subversion PHP 関数型プログラミング Laravel モダンJS (Modern JavaScript) 基礎 Android Studio 機械学習 iOSアプリ開発 ぷプログラミング React 次世代高度IT人材 共創 IPA Raspberry Pi Xamarin スクリプト言語 GoF CUI VBA 資格 ビジネス文書 jQuery 研修参加レポート マネジメント OSPF テーブル設計 アンガーマネジメント クリティカル・シンキング PDU 経営改善 Pマーク 問題解決技法 サイバー攻撃 エンジニア 参加してみた エンゲージメントマネジメント 労働関連法 新人育成 ネットワーク構築 情報セキュリティマネジメント デザインパターン リファクタリング マルチスレッドプログラミング ベンダーコントロール Modern JavaScript 冗長化 VLAN インフラエンジニア チームビルディング テストケース リーダブルコード セキュリティ入門 ネットワーク入門 Node.js npm gulp ビルドツール Python入門 冗長化入門 インフラ実機演習 プロジェクト管理 Active Directory ネットワーク管理 コンテナ 正規化理論 Haskell 品質管理 OpenStack シンギュラリティ DBA中級 プロトコル UX 基本設計 FinTech トラブルシューティング 並列処理 見える化 PMO ロジカルコミュニケーション Deep Learning インデックス設計 超上流工程 BGP Excel C-CENT Selenide プライベートクラウド アセンブラ コンピュータ基礎 工数見積 CCENT 法律知識 失敗から学ぶ プロジェクト失敗事例 PDCA プログラミング入門 非エンジニア向け 4Biz DNS セルフマネジメント 片付け術 サーバーダウン サーバー タイムマネジメント GO言語 プロダクトマネジメント プロダクトマネージャ LVS ロードバランサー 負荷分散 仮想通過 犯罪心理学 情報漏えい SEカレッジ導入事例 IT研修制度を聞いてみた CentOS7 開発環境構築 数字力 財務 IT人材 UI Machine Learning Go言語 (golang) データマイニング 統計学 新人教育 やり直し数学 RDB つながる工場 モチベーション WebSocket WebWorker HTML5 CSS3 Bootstrap 微分・積分 システム設計 決断力 LAMP環境 教育研修担当者向け ルーティング Linux入門 図解術 目標設定 試験対策 インタビュー技法 Vue.js ブロックチェーン DHCP 仕掛け学 BSC 財務諸表 自己分析 RIP スタティックルート バッファオーバーフロー DoS攻撃 システム開発 Wireshark パケットキャプチャ 管理職研修 部下育成 文章力 情報システム部門向け プロジェクトリーダー プロジェクトマネージャ 塗り絵 リスク管理 法改定 会社の仕組み Chainer AI人材 会話術 テスト技法 会社規模199名まで 会社規模49名まで 会社規模99名まで アプリ開発 サーバサイドJava 営業知識 Cloud 栄養学 基本コマンド ウォーターフォールモデル ヘルスケア 論理設計 ニューラルネットワーク ハンズオン UML 顧客ヒアリング マウスで学ぶ Apache EC2 Lightsail M5Stack DevSecOps プロジェクト成果 画像認識 チャットポット コマンド レビュー 基本用語 自動構築 LPIC-1 サーバーサイドJavascript キャリア形成 ワークライフバランス インバスケット テック用語 GitHub Windows エディタ 教養 令和時代 RESTful API 物理設計 会社規模300名以上 データモデリング サーバーサイドJava Webサーバー基礎 Webサーバー応用 Watson IBMWatson Learning Topics OS モバイル コンテスト トレーニング手法 アーキテクチャ 人材モデル インフラ CI/CD Infrastructure as a Code チーム開発 制度づくり Special_Intro AI市場分析 研修ロードマップ 仕事術 デジタルトランスフォーメーション 財務分析手法 情報整理 PowerPoint 新しい研修 オンライン研修 見どころ紹介 統計分析 ディープラーニング G検定 情報処理技術者試験 販売管理 C# テスト計画 Linuxサーバー WEBサーバ構築 http/2 Postfix イーサリアム プロジェクト・メンバ 正規化 パケット実験 作業分解 トラブル調査 ネットワーク設計 Windows server 2016 ネットワーク機器 DX 管理職 最新動向 ポストコロナ時代 IoTデバイス マイコンボード センサ サーバー仮想化 仮想ルータ WAN インターネットVPN 若手エンジニア ITプロジェクト 人事面談 DX人材育成 Java基礎 ZAP 脆弱性診断 NWサービス構築 イノベーション・マネジメント ネットワークセキュリティ ストレッチ Google Cloud Platform 不動産業界 テレワーク(WFH) ドリル GCP ( Google Cloud Platform ) システム業界 PMS テレワーク ビッグデータ NoSQL OWASP CentOS8 ネットワーク技術 データ分析 デザインシンキング 保険業界 会議リーダー システムエンジニア 段取り術 プロジェクト原論 文章書き換え術 ノーコード No Code MongoDB Redis Cassandra 運用管理 Windows10 仮想マシン リモートワーク 働き方 生産性 IPSec Office セキュリティマナー ソフトウェア・レビュー ライフハック 新しい働き方 エクササイズ ビジネスモデルキャンバス 状況認識 ストレス 必須コマンド Web 今日わかる きほん 状況把握 意思決定 心の健康 IT書籍 書籍紹介 営業マン 類推法 クラス プロセス指向 PdM 共用 ウェビナーレポート 地方創生 GraphQL CSS OWASP ZAP セキュリティマネジメント 問題解決 ソフトウェア 新技術 雑談力 テスト見積もり Scala Go Rust Relay Cloud AI Kaggle ITエンジニア フレッシャーズ 経営戦略 事業戦略 マインドフルネス 基本情報技術者試験 ニューノーマル プロジェクト会議 メソドロジ 講師インタビュー システム障害 販売管理システム VMware セキュリティ事例 ケーススタディ インターネット通信 ビジネスマン 品質向上 提案 ロジック図解術 バーチャルマシン 対策事例 アスリート 国の動向 アンチパターン リモートアクセス 脳ヨガ 自律神経 整え方 組み立て方 コミュニケーション術 リーダー 新人 知っておきたいこと 対人能力 洞察力 一文作成 サッカー業界 グループワーク マネジメント手法 IT業界 Octave セキュリティ管理 IT ネットワーク機器の特徴 ネットワーク機器の仕組み 基本のキ プレゼンテーションの組み立て方 伝え力 試験合格後 時短術 作成のコツ 導入事例 メンタルマネジメント メンタルヘルスケア DXプロジェクト プログラミング教育 プログラミング的思考 子供向けプログラミング データ定義言語 DDL モダンWebアプリケーション ドキュメント作成 Docker Compose Docker Hub AR VBAエキスパート試験 Azure メディア掲載 サーバーアーキテクチャ データ操作言語 DML NewSQL ソフトウェアセキュリティ 数学 VR アパレル業界 Kubernetes Power BI Android プロダクトオーナーシップ プロダクトオーナー 内製化 情報システム部門 Z世代 クラウドネイティブ 技術教育 Windows server 2019 XSS CSRF クリックジャッキング ビジネスパーソン VPC IAM AWS Fargete ECS 問題発見力 問題分析力編 Access 流通業界 金融業界 ネットワーク設定 トラブル対応 評価 ソフトウェア品質 クレーム対応 呼吸法 戦国武将 エンジニアリング 組織論 SpreadSheet GAS ゼロトラスト Express 3D

SQL 練習 ドリル 2 ~高度で複雑な SQL に挑戦|研修コースに参加してみた


2021-09-01 更新

今回参加したコースは SQL 練習 ドリル 2 ~高度で複雑な SQL に挑戦 です。

以前にもレポートした SQL 練習ドリルの続編です!

SQL の書き方をある程度、わかった / 出来るようになったとして、現場で開発・運用されているエンタープライズなアプリケーションになった途端、複雑な条件での JOIN (LEFT / RGHT の違いや INNER JOIN ) や副問合せ、集合関数などに加え、 UNION なども時々必要とされます。

この練習ドリル 2 では、そういった現場で出てきそうな、またできそうで出来ない問題にレベルアップして、 3 時間集中してクエリを書き続けます。読者のあなたもぜひ腕試ししてみませんか?

 

では、どのような内容だったのか、レポートします!

コース情報

想定している受講者 データベース、 SQL をひと通り学んでおり、特に結合、副問合せ、グループ化、集計について理解していること
受講目標
  • 結合、副問合せ、集計、グループ化などを組み合わせた、高度で複雑な SQL が書けるようになる
  • 必要なデータを得ることができる SQL を、「なんとなく」「まぐれ」ではなく書けるようになる

講師紹介

この「参加してみた」レポートでは初登場となる 宮崎 克也 さんが登壇されました!

講師紹介
宮崎 克也
携帯電話や政府系金融機関の関連企業でシステムエンジニア・システムコンサルタントを経験。独立後、 IT コンサルティングの傍ら、セミナー講師としても活躍。
豊富な講師経験をもとに、 IT 知識ゼロの新入社員からベテランまで、研修中の疑問を探り出し、解決に導く懇切丁寧なレクチャーで人気。

今後 SQL に関連するコースでの登壇が増えるそうなので、お楽しみに!

演習環境

今回の演習環境は MySQL 8.0 で、 SQL クライアントは GUI で操作できる A5:SQL Mk-2 をお好みで使うという環境でした。「エー・ゴ」でおなじみのツールですね。

A5:SQL Mk-2 – フリーのSQLクライアント/ER図作成ソフト (松原正和)

なお、上記の環境はクラウド上に用意され、受講者はリモートから接続するだけで演習できるようになっています。

A5:SQL Mk-2 では、画面左にデータベースとテーブルがツリー状に表示されます。そこからテーブルを選んで、内容を表示することができます。そして、画面右に SQL のクエリを書いて実行すると、結果が表示されます。

A5:SQL Mk-2 でクエリを実行

使用するテーブル

用意された環境では、すでにテーブルが CREATE されていました。カラムまでは載せていませんので、あまり参考にならないかも知れませんが、念のため。



レベル 1 基本の確認

では演習に突入しましょう。

このレポートでは、合計 22 問ある演習問題の中からピックアップして紹介します。全問挑戦したい方は、ぜひコースにご参加ください!

なお、出題時の都合で問題番号は前後します。 ご了承ください。

問題 1-9

書籍情報テーブル (books) を出版社ごとにグルーピングし、各社ごとの価格の平均値を求める。 なお、取り出す列は publish, price の平均値とする。
ヒント

特になし

解答と解説
SELECT publish, AVG(price)
FROM books 
GROUP BY publish;
    解説

  • GRUOP BY の基本どおり
  • グループ化する列( publish )と集計列( price )をはっきりさせる

問題 2-2

注文明細テーブル ( order_desc ) と商品テーブル ( product )から、商品ごとの累計購入数と累計購入額を、購入額が多い順に取り出す。 なお、まったく売れていない商品についても取り出すものとする。
ヒント

「まったく売れていない商品」についても取り出すには、外部結合を利用する

解答と解説
SELECT p.p_name, SUM(o.quantity), SUM(p.price * o.quantity)
FROM order_desc AS o RIGHT JOIN product AS p
ON p.p_id = o.p_id
GROUP BY p.p_id, p.p_name ORDER BY SUM(p.price * o.quantity) DESC;
    解説

  • 「まったく売れていない商品」についても取り出すには、外部結合を利用する
    • ここでは RIGHT JOIN (右外部結合)を使っている
    • 全件表示したいテーブルがどちらなのか というと product テーブル
    • ちなみに、左外部結合でも書ける
  • 累計購入額も求める
    • GROUP BY して集計関数
    • SUM(p.price * o.quantity)
  • GROUP BY では別名を使っているが、 ORDER BY には別名を使っていないので、 SELECT の列名に同じものを書いている

レベル 2 関数、結合など

問題 1-11

書籍情報テーブル ( books ) から ISBN コード、書名、刊行日を取り出す。 その際、 ISBN コードの頭には一律「 ISBN 」という固定文字列を付加し、刊行日は「 YYYY 年 MM 月 DD 日」の形式で整形する。
ヒント

文字列連結のための関数、および日時の書式整形のための関数を利用する

解答と解説
SELECT CONCAT('ISBN', isbn) AS ISBNコード, title AS 書名,
  DATE_FORMAT(publish_date, '%Y年%m月%d日') AS 刊行日
FROM books;
    解説

  • 日時の整形
  • 取り出した文字列で足りなくて足すことはよくある
  • 文字列を足すのは CONCAT
  • 日付の書式整形は DATE_FORMAT 関数
    • %%Y%m%dで年月日の埋め込みを表す
  • データベース製品ごとに、関数や使用方法が異なる場合があるので注意

問題 1-13

アクセス記録テーブル ( access_log ) から、リンク元ごとのアクセス数を求め、 10 件未満を「 C 」、 10 件以上 50 件未満を「 B 」、 50 件以上を「 A 」とランク付けする。
ヒント

列の値によって条件分岐を行う演算子を用いる

解答と解説
SELECT referer, count(*) AS カウント数,
CASE
  WHEN count(*) >= 50 THEN 'A'
  WHEN count(*) >= 10 THEN 'B'
  ELSE 'C'
END AS ランク
FROM access_log
GROUP BY referer;
    解説

  • CASE 演算子で条件分岐する
    • WHEN で各条件と対応する値を記述し、その他の条件の値は ELSE に記述する
  • CASE 全体に AS で名前をつけることもできる

問題 2-5-prac1

アンケート回答テーブルから ( quest ) から、回答者の平均年齢を超えている人の回答 ( answer1, answer2 列)を、評価の低い順に取り出す。
ヒント

副問合せを用いる

解答と解説
SELECT answer1, answer2
FROM quest
WHERE age >
  (SELECT AVG(age) FROM quest)
ORDER BY answer1 ASC;
    解説

  • 「回答者の平均年齢を超えている人の回答」を副問合せにする
  • age と回答者の平均年齢(副問合せ)を比較
  • >で比較するので、副問合せは単一の値だけが返る SQL 文を記述する
  • 副問合せを思いつくのは難しいかもしれない
    • どれだけ引き出しを持っているかが、すばやく SQL を書けることにつながる

レベル3 総合・副問合せなど

問題 2-3

書籍目次テーブル ( contents ) から現在のコンテンツ名と、次のコンテンツ名を、 コンテンツコードの昇順で取り出す。
ヒント

自己結合を利用する

解答と解説
SELECT cc.c_title, cn.c_title
FROM contents AS cc
  INNER JOIN contents AS cn
  ON cc.next_id = cn.c_id
ORDER BY cc.c_id ASC;
    解説

  • 自己結合
    • ふだんあまり使わないテクニックだが、たまにある
    • 自分の分身と結合するようなもの
    • ただし書き方は通常の結合と同じ
    • どちらも同じテーブルなので、別名をつけないといけない

問題 2-4

書籍情報テーブル ( books )、著者-書籍情報テーブル ( author_books )、 著者情報テーブル ( author) を結合して、 出版社が「日経 BP 」である書籍情報を、刊行日の新しい順に取り出す。 取得列は、書名、著者名、刊行日とする。
ヒント

結合は 3 つ以上のテーブルでも可能

解答と解説
SELECT b.title, a.name, b.publish_date
FROM
  ( books AS b
  INNER JOIN author_books AS ab
  ON b.isbn = ab.isbn )
  INNER JOIN author AS a
  ON ab.author_id = a.author_id
WHERE b.publish = '日経BP'
ORDER BY b.publish_date DESC;
    解説

  • 3 つ以上のテーブルの結合
    • 3 つ以上もできるということは知っていても、それをスムーズに書けるかどうかはまた別
  • JOIN の入れ子
    • 丸カッコで 1 つめの結合
    • その結合した結果に対してさらに INNER JOIN
  • なお、この例では両方 INNER JOIN だが、 INNER JOIN と LEFT JOIN でもよい

問題 2-6

ユーザテーブル ( usr ) と、社員テーブル ( employee ) に含まれるユーザの氏 / 名を すべて取り出して、氏(カナ)、名(カナ)について昇順に並べる
ヒント

等号 (和集合)を利用する

解答と解説
SELECT u.l_name_kana, u.f_name_kana
FROM usr AS u
UNION
  SELECT e.l_name_kana, e.f_name_kana
  FROM employee AS e
  ORDER BY 1, 2;
    解説

  • UNION や UNION ALL による和集合は、ふだん多用はしないが、たまに出てくる

まとめ

SQL 練習ドリル 2 ということで、高度で複雑な問題に取り組みましたが、手応えはいかがでしたか?

私は「まずはどのテーブルを使うのか、不足しているテーブルは何か、次に条件は … 」と考えているうちに、頭が混乱してきました。プログラミングのアルゴリズムのように処理の順番を考える訓練に、とってもふさわしい内容でした!

宮崎さんがおっしゃっていたように、アルゴリズムのように、手順に慣れて、引き出しを増やせられれば、いろいろなパターンに対応できそうですね。

また、実務で遭遇する、昔からあるデータベースは、そんなに綺麗なものではないとのこと。大量のデータが多数のテーブルに入って、リレーションも複雑で、不整形なデータもあったりするようです。

そんなデータベースに出会っても、たじろぐことないように、今回のコースよりもさらに高度で複雑な SQL のコースも企画中とのことでした!

それまでに私も練習ドリル 2 を復習しておきます!

 


SEカレッジについて

label SEカレッジを詳しく知りたいという方はこちらから !!

SEcollege logo
SEカレッジ
IT専門の定額制研修 月額28,000円 ~/ 1社 で IT研修 制度を導入できます。
年間 670 コースをほぼ毎日開催中!!

午前免除 FAQ

タグ一覧