SQL入門ということで、下記2個を実施してみました。
- MySQLが公式で準備している「Employees」のサンプルデータベースの導入及び環境構築
- サンプルデータのの抽出
環境構築
-
MariaDBの準備
$ sudo apt update $ sudo apt install mariadb-client mariadb-server -y -
サンプルデータの入手
MySQL公式のGitHubからダウンロードします。
https://github.com/datacharmer/test_db/tree/master$ cd /tmp $ git clone https://github.com/datacharmer/test_db.git $ cd test_db $ lsChangelog load_dept_emp.dump objects.sql README.md load_dept_manager.dump sakila employees.sql load_employees.dump show_elapsed.sql employees_partitioned.sql load_salaries1.dump sql_test.sh employees_partitioned_5.1.sql load_salaries2.dump test_employees_md5.sql images load_salaries3.dump test_employees_sha.sql load_departments.dump load_titles.dump test_versions.sh -
データベースの作成
$ mariadb -u root -p MariaDB [(none)]> CREATE DATABASE employees; Query OK, 1 row affected (0.000 sec) MariaDB [(none)]> EXIT; Bye -
データのインポート
mariadb -u root -p employees < employees.sql -
MariaDBをGUIで扱うためのソフトのインストール
HeidiSQLがLinuxにも対応したので、こちらをインストールします。
https://www.heidisql.com/download.php#
サンプルデータの抽出
データベース環境の構築が完了したので、実際に手でデータを抽出してみようと思います。
皆さんも試してみてください。
- 部署ごとの平均給与を求めよ
- 入社年ごとの社員数を求めよ
- 社員テーブルから、各入社年ごとの社員数を求め、古い順に並べよ
- 部署ごとの男女比を求めよ
- 各部署について、男性社員数と女性社員数をそれぞれ集計せよ
- 部署の管理者履歴を求めよ
- 開発部 (d005) において、これまでの管理者（マネージャー）の氏名と在任期間を求めよ
- 各社員の給与が、その社員の部署平均より高いかどうか判定せよ
- 社員ごとの給与と、同じ部署の平均給与を比べて、「部署平均より高い社員」だけ出す
# 部署ごとの平均給与を求めよ
select
d.dept_name as 部署名,
de.dept_no as 部署ID,
round(AVG(sa.salary)) as 平均給与
from departments d
inner join dept_emp de on d.dept_no = de.dept_no
inner join salaries sa on sa.emp_no = de.emp_no
group by de.dept_no, d.dept_name
order by 平均給与 DESC;
#入社年ごとの社員数を求めよ
#社員テーブルから、各入社年ごとの社員数を求め、古い順に並べよ。
select
year(e.hire_date) as `入社年`,
count(*) as `社員数`
from employees e
group by year(e.hire_date)
order by 入社年 asc;
#部署ごとの男女比を求めよ
#各部署について、男性社員数と女性社員数をそれぞれ集計せよ。
select
d.dept_name as `部署名`,
sum(case when e.gender = 'F' then 1 else 0 end) as `女性社員数`,
sum(case when e.gender = 'M' then 1 else 0 end) as `男性社員数`
from employees e
inner join dept_emp de on e.emp_no = de.emp_no
inner join departments d on de.dept_no = d.dept_no
group by d.dept_name
order by d.dept_name;
# 部署の管理者履歴を求めよ
# 開発部 (d005) において、これまでの管理者（マネージャー）の氏名と在任期間を求めよ。
select
concat(e.first_name, last_name) as `管理者名`,
dm.from_date as `任開始日`,
dm.to_date as `在任終了日`
from dept_manager dm
inner join employees e on dm.emp_no = e.emp_no
where dm.dept_no = 'd005'
order by dm.from_date;
#各社員の給与が、その社員の部署平均より高いかどうか判定せよ
#社員ごとの給与と、同じ部署の平均給与を比べて、「部署平均より高い社員」だけ出す。
select
concat(e.first_name, e.last_name) as `社員名`,
s.salary as `給与`
from salaries s
inner join employees e on s.emp_no = e.emp_no
inner join dept_emp de ON e.emp_no = de.emp_no
where
s.to_date = '9999-01-01'
and de.to_date = '9999-01-01'
and s.salary > (
select
round(AVG(s2.salary))
from
salaries s2
inner join dept_emp de2 ON s2.emp_no = de2.emp_no
where
s2.to_date = '9999-01-01'
and de2.to_date = '9999-01-01'
and de2.dept_no = de.dept_no
)