Tips

AccessでSQLの練習
2019.08.02

AccessでSQLの練習

AccessのSQLビューの使い方の確認と、SQLの基本的な練習問題になります。
後半に行くにつれて難易度が高めになっています。

使用するファイル ⇒ ダウンロード


■使い方

①クエリ「SQL練習」の「SQLビュー」を開きましょう。

②SQLを打ち込んで「実行」をクリックすると、入力したSQLが実行されます。

■問題1

全員分の社員コードと社員名を検索してください。

解答例
SELECT 社員コード,社員名 FROM 社員一覧;

最も簡単なSELECT文です。
社員コードと社員名の2カラムから全レコードを検索します。


■問題2

男性(女性)の社員コードと社員名を検索してください。
↓男性

↓女性

解答例(男性)
SELECT 社員コード,社員名 FROM 社員一覧 WHERE 性別='男性'

レコードの絞り込みを行うにはWHERE句を使います。

解答例(女性)
SELECT 社員コード,社員名 FROM 社員一覧 WHERE 性別='女性'

WHERE句の「男性」の箇所が「女性」に変わっただけです。


■問題3

年齢の降順で社員コード,社員名,年齢を表示してください。

解答例
SELECT 社員コード,社員名,年齢 FROM 社員一覧 ORDER BY 年齢 DESC;

並べ替えを行うにはORDER BY句を使います。
降順なので DESCを忘れずに付けましょう。


■問題4

年齢の昇順で30歳以上の社員の社員コード,社員名,年齢を表示してください。

解答例
SELECT 社員コード,社員名,年齢 FROM 社員一覧 WHERE 年齢>=30 ORDER BY 年齢;

WHERE句とORDER BYを組み合わせています。
WHERE句を先に書かないと構文エラーになるので注意しましょう。


■問題5

年齢の昇順で30代(30~39歳)の社員の社員コード,社員名,年齢を表示してください。

解答例1
SELECT 社員コード,社員名,年齢 FROM 社員一覧 WHERE 年齢>=30 AND 年齢<40 ORDER BY 年齢;

WHERE句で条件式を複数組み合わせる問題です。
「かつ」のときは「AND」、「または」のときは「OR」を使いましょう。

解答例2
SELECT 社員コード,社員名,年齢 FROM 社員一覧 WHERE 年齢 BETWEEN 30 AND 39 ORDER BY 年齢 ASC;

今回のような1つのカラムの中で範囲を指定する場合、BETWEENを使うこともできます。


■問題6

全員分の社員コード,社員名,部署名を検索してください。

解答例
SELECT 社員コード,社員名,部署名 FROM 社員一覧 LEFT OUTER JOIN 部署一覧 
ON 社員一覧.部署ID = 部署一覧.部署ID ORDER BY 社員コード;

テーブルを結合する問題です。
部署IDが振られていない「村田」さんのレコードを表示するために、(左側)外部結合を使っています。


■問題7

社員数を検索してください。

解答例1
SELECT COUNT(*) AS 社員数 FROM 社員一覧;

社員一覧テーブルの全レコード数を数えればいいので、COUNT関数を使用しています。
細かいですが、ASを使うことで任意のカラム名を設定できます。

解答例2
SELECT COUNT(社員コード) AS 社員数 FROM 社員一覧;

COUNTの引数に特定のカラム(今回は社員コード)を指定した例です。
「部署ID」を指定すると13になってしまうので注意しましょう。


■問題8

男性(女性)社員の人数を検索してください。
↓男性

↓女性

解答例1
SELECT COUNT(*) AS 男性社員数 FROM 社員一覧 WHERE 性別='男性';

問題7のCOUNT関数に、WHERE句を組み合わせています。

解答例2
SELECT COUNT(*) AS 女性社員数 FROM 社員一覧 WHERE 性別='女性';

解答例1の男性が女性に変わっています。


■問題9

住所ごとの人数を表示してください。

解答例
SELECT 住所,COUNT(*) AS 人数 FROM 社員一覧 GROUP BY 住所;

GROUP BY句を使う問題です。
今回のように集計関数と組み合わせて使うことが多いです。


■問題10

性別ごとの平均年齢を表示してください。

解答例
SELECT 性別,AVG(年齢) AS 平均年齢 FROM 社員一覧 GROUP BY 性別;

問題9と同じく、GROUP BY句を使う問題です。
平均を求めるにはAVG関数を使います。


■問題11

部署ごとの人数、平均年齢を表示してください。

解答例
SELECT 部署名,COUNT(社員コード) AS 人数,AVG(年齢) AS 平均年齢 FROM 社員一覧 RIGHT OUTER JOIN 部署一覧 
ON 社員一覧.部署ID=部署一覧.部署ID GROUP BY 部署名;

テーブルの結合とGROUP BY句による集計を組み合わせた問題です。
部署一覧の部署名を全て表示したいので、(右側)外部結合を使っています。


■問題12

人数が最大の部署の人数を表示してください。

解答例
SELECT MAX(人数) FROM (SELECT COUNT(*) AS 人数 FROM 社員一覧 GROUP BY 部署ID);

サブクエリを使った問題です。
サブクエリで部署ごとの人数を検索し、その中(人数カラム)での最大値をMAX関数で集計しています。


■問題13

人数が最大の部署の部署名,人数,平均年齢を表示してください。

解答例
SELECT 部署名,COUNT(社員コード) AS 人数,AVG(年齢) AS 平均年齢 FROM 社員一覧 
RIGHT OUTER JOIN 部署一覧 ON 社員一覧.部署ID=部署一覧.部署ID GROUP BY 部署名 
HAVING COUNT(社員コード)= (SELECT MAX(人数) FROM (SELECT 部署ID,COUNT(*) AS 人数 FROM 社員一覧 GROUP BY 部署ID));

サブクエリと結合、HAVING句を組み合わせた問題です。
問題11の結果に対してHAVING句でフィルターをかけている形ですがその中で問題12のサブクエリを使っています。


後半はやや難し目ですが、ここまで問題無くできていればSQLの基礎知識は問題無いと思います!

excel_VBAを学んで業務効率化!

excel-vba入門 連載

Recent News

Recent Tips

Tag Search