-- Lesson 1-3

-- practiceというデータベースを作成せよ
CREATE DATABASE practice;

-- データベースを一覧表示せよ
SHOW DATABASES;

-- practiceというデータベースを使用せよ
USE practice;

-- Lesson 1-4

-- Employee表を作成せよ
CREATE TABLE Employee (
emp_id CHAR(6) NOT NULL,
name VARCHAR(10) NOT NULL,
gender CHAR(1) NOT NULL,
birthday DATE NOT NULL,
salary INT,
dep_id CHAR(4),
PRIMARY KEY(emp_id));

-- Department表を作成せよ
CREATE TABLE Department (
dep_id CHAR(4) NOT NULL,
name VARCHAR(10) NOT NULL,
floor INT,
PRIMARY KEY(dep_id));

-- 表を一覧表示せよ
SHOW TABLES;

-- Employee表にデータを登録せよ
INSERT INTO Employee VALUES ('E00001', '山田太郎', '男', '1995-01-01', NULL, NULL);

INSERT INTO Employee VALUES ('E00002', '佐藤次郎', '男', '1990-05-03', 250000, 'D001');

INSERT INTO Employee VALUES ('E00003', '鈴木花子', '女', '1990-02-11', 250000, 'D002');

INSERT INTO Employee VALUES ('E00004', '田中三郎', '男', '1975-11-03', 400000, 'D003');

INSERT INTO Employee VALUES ('E00005', '高橋良子', '女', '1985-11-23', 300000, 'D003');

INSERT INTO Employee VALUES ('E00006', '鈴木良枝', '女', '1970-05-03', 450000, 'D003');

INSERT INTO Employee VALUES ('E00007', '佐藤健次', '男', '1980-05-05', 350000, 'D001');

-- Department表にデータを登録せよ
INSERT INTO Department VALUES ('D001', '総務部', 8);

INSERT INTO Department VALUES ('D002', '人材開発部', 8);

INSERT INTO Department VALUES ('D003', 'システム開発部', 12);

-- Employee表からすべての列を取得せよ
SELECT * FROM Employee;

--Department表からすべての列を取得せよ
SELECT * FROM Department;

-- Employee表を削除せよ
DROP TABLE Employee;

-- Department表を削除せよ
DROP TABLE Department;

-- practiceデータベースを削除せよ
DROP DATABASE practice;

-- Lesson 2-1

-- サンプル2-1-1 Employee表からname列を取得せよ
SELECT name FROM Employee;

-- 練習問題2-1-1 Department表からname列を取得せよ
SELECT name FROM Department;

-- サンプル2-1-2 Employee表から
-- name列、gender列、birthday列を取得せよ
SELECT name, gender, birthday FROM Employee;

-- 練習問題2-1-2 Department表からname列とfloor列を取得せよ
SELECT name, floor FROM Department;

-- 重複を除かずにgender列を取得した結果の例
SELECT gender FROM Employee;

-- サンプル2-1-3 Employee表から重複を除いてgender列を取得せよ
SELECT DISTINCT gender FROM Employee;

-- 練習問題2-1-3 Department表から重複を除いてfloor列を取得せよ
SELECT DISTINCT floor FROM Department;

-- 複数の列を取得するSQL文でDISINTCを指定しない場合の実行結果の例
SELECT gender, dep_id FROM Employee;

-- 複数の列を取得するSQL文でDISINTCを指定した場合の実行結果の例
SELECT DISTINCT gender, dep_id FROM Employee;

-- Lesson 2-2

-- サンプル2-2-1 Employee表から、
-- gender列が「男」という条件で、
-- name列とgender列を取得せよ
SELECT name, gender FROM Employee WHERE gender = '男';

-- 練習問題2-2-1 Employee表から、
-- gender列が「男」でないという条件で、
-- name列とgender列を取得せよ
SELECT name, gender FROM Employee WHERE gender <> '男';

-- サンプル2-2-2 Employee表から、
-- dep_id列がNULLであるという条件で、
-- name列とdep_id列を取得せよ
SELECT name, dep_id FROM Employee WHERE dep_id IS NULL;

-- 練習問題2-2-2 Employee表から、
-- dep_id列がNULLでないという条件で、
-- name列とdep_id列を取得せよ
SELECT name, dep_id FROM Employee WHERE dep_id IS NOT NULL;

-- サンプル2-2-3 Employee表から、
-- salary列が30万以上という条件で、
-- name列とsakary列を取得せよ
SELECT name, salary FROM Employee WHERE salary >= 300000;

-- 練習問題2-2-3 Employee表から、
-- salary列が30万未満という条件で、
-- name列とsalary列を取得せよ
SELECT name, salary FROM Employee WHERE salary < 300000;

-- サンプル2-2-4 Employee表から、
-- salary列を+5万円すると30万以上になるという条件で、
-- name列、salary列、およびsalary列に50000を足した値を取得せよ
SELECT name, salary, salary + 50000 FROM Employee 
WHERE salary + 50000 >= 300000;

-- 練習問題2-2-4 Employee表から、
-- salary列を70%にする（30%ダウンする）と30万未満になるという条件で、
-- name列、salary列、およびsalary列の70%の値を取得せよ
SELECT name, salary, salary * 0.7 FROM Employee WHERE salary * 0.7 < 300000;

-- サンプル2-2-5 Employee表から、
-- salary列に5万円を足すと30万円以上になるという条件で、
-- name列、salary列、およびsalary列に50000を足した値に
-- raiseという別名を付けて、取得せよ
SELECT name, salary, salary + 50000 AS raise FROM Employee 
WHERE salary + 50000 >= 300000;

-- 練習問題2-2-5 Employee表から、
-- salary列に0.7を掛けた値が30万円未満になるという条件で、
-- name列、salary列、およびsalary列に0.7を掛けた値に
-- cut（減給という意味）という別名を付けて、取得せよ
SELECT name, salary, salary * 0.7 AS cut FROM Employee 
WHERE salary * 0.7 < 300000;

-- サンプル2-2-6 先ほどサンプル2-2-5に示したSQL文の
-- salary + 50000に「raise 50000」という別名を付けよ
SELECT name, salary, salary + 50000 AS 'raise 50000' 
FROM Employee WHERE salary + 50000 >= 300000;

-- 練習問題2-2-6 先ほど練習問題のSQL文の
-- 「salary列に0.7を掛けた値」に「cut 30%」という別名
-- （「cut」と「30%」の間にスペースを入れる）を付けよ
SELECT name, salary, salary * 0.7 AS 'cut 30%' FROM Employee 
WHERE salary * 0.7 < 300000;

-- Lesson 2-3

-- サンプル2-3-1 Employee表から、
-- gender列が「男」、かつ、salary列が30万以上という条件で、
-- name列、gender列、salary列を取得せよ
SELECT name, gender, salary FROM Employee
WHERE gender = '男' AND salary >= 300000;

-- 練習問題2-3-1 Employee表から、
-- salaryが未定（NULL）、または、salaryが30万円未満という条件で、
-- name列とsalary列を取得せよ
SELECT name, salary FROM Employee WHERE salary IS NULL OR salary < 300000;

-- サンプル2-3-2 Department表から、
-- floor列が10階より下でないという条件で、
-- name列とfloor列を取得せよ
SELECT name, floor FROM Department WHERE NOT floor < 10;

-- 練習問題2-3-2 Employee表から、
-- birthday列が1990年代以降ではないという条件で、
-- name列とbirthday列を取得せよ
SELECT name, birthday FROM Employee WHERE NOT birthday >= '1990-01-01';

-- サンプル2-3-3 Employee表から、
-- birthdayが1990年1月1日以降、かつ、
-- 1999年12月31日以前という条件で、
-- name列とbirthday列を取得せよ
SELECT name, birthday FROM Employee 
WHERE birthday >= '1990-01-01' AND birthday <= '1999-12-31';

-- 練習問題2-3-3 Employee表から、
-- salaryが30万円以上、かつ、40万円未満という条件で、
-- name列とsalary列を取得せよ
SELECT name, salary FROM Employee WHERE salary >= 300000 AND salary < 400000;

-- サンプル2-3-4 Employee表から、
-- birthdayが1990年1月1日以降、かつ、
-- 1999年12月31日以前という条件で、
-- name列とbirthday列を取得せよ
SELECT name, birthday FROM Employee 
WHERE birthday BETWEEN '1990-01-01' AND '1999-12-31';

-- 練習問題2-3-4 Employee表から、
-- salaryが30万円と39万9 999円の範囲にあるという条件で、
-- name列とsalary列を取得せよ
SELECT name, salary FROM Employee WHERE salary BETWEEN 300000 AND 399999;

-- サンプル2-3-5 Employee表から、
-- gender列が「男」、かつ、birthday列が1980年代以降、
-- かつ、salary列が30万円以上という条件で、
-- name列、gender列、birthday列、salary列を取得せよ
SELECT name, gender, birthday, salary FROM Employee
WHERE gender = '男' 
AND birthday >= '1980-01-01' 
AND salary >= 300000;

-- 練習問題2-3-5 Employee表から、
-- gender列が「男」、または、salary列が40万円以上、
-- または、dep_idが「総務部（dep_id = 'D001'）」という条件で、
-- name列、gender列、salary列、dep_id列を取得せよ
SELECT name, gender, salary, dep_id FROM Employee
WHERE gender = '男' OR salary >= 400000 OR dep_id = 'D001';

-- サンプル2-3-6 Employee表から、
-- gender列が「男」、かつ、birthday列が1980年代以降
-- またはsalary列が30万円以上、という条件で、
-- name列、gender列、birthday列、salary列を取得せよ
SELECT name, gender, birthday, salary FROM Employee
WHERE gender = '男' 
AND (birthday >= '1980-01-01' OR salary >= 300000);

-- 練習問題2-3-6 Employee表から、
-- gender列が「女」、かつ、-- birthday列が1990年以降
-- またはdep_idが「D003（システム開発部）」という条件で、
-- name列、gender列、birthday列、dep_id列を取得せよ
SELECT name, gender, birthday, dep_id FROM Employee
WHERE gender = '女' AND (birthday >= '1990-01-01' OR dep_id = 'D003');

-- 多くのORを使ったSQL文の実行結果の例
SELECT dep_id, name FROM Department
WHERE dep_id = 'D001'
OR dep_id = 'D002'
OR dep_id = 'D003';

-- サンプル2-3-7 Department表から、
-- dep_id列が「D001、D002、D003」の中にあるという条件で、
-- dep_id列とname列を取得せよ
SELECT dep_id, name FROM Department 
WHERE dep_id IN ('D001', 'D002', 'D003');

-- 練習問題2-3-7 Department表から、
-- floor列が、1、2、11、12の中にないという条件で、
-- name列とfloor列を取得せよ
SELECT name, floor FROM Department WHERE floor NOT IN (1, 2, 11, 12);

-- Lesson 2-4

-- サンプル2-4-1 Employee表から、
-- name列の中に「田」があるという条件で、
-- name列を取得せよ
SELECT name FROM Employee WHERE name LIKE '%田%';

-- 練習問題2-4-1 Employee表から、
-- name列の先頭が「田」であるという条件で、
-- name列を取得せよ
SELECT name FROM Employee WHERE name LIKE '田%'; 

-- サンプル2-4-2 Employee表から、
-- 「4文字で末尾が子」という条件で、
-- name列を取得せよ
SELECT name FROM Employee WHERE name LIKE '___子';

-- 練習問題2-4-2 Department表から、
-- name列が「5文字で〇〇開発部」という条件で、
-- name列を取得せよ。
SELECT name FROM Department WHERE name LIKE '__開発部';

-- サンプル2-4-3 Employee表から、
-- name列の中に「田」がないという条件で、
-- name列を取得せよ
SELECT name FROM Employee WHERE name NOT LIKE '%田%';

-- 練習問題2-4-3 Employee表から、
-- name列の先頭が「田」でないという条件で、
name列を取得せよ
SELECT name FROM Employee WHERE name NOT LIKE '田%'; 

-- Lesson 3-1

-- サンプル3-1-1 Employee表から、
-- name列とsalary列を取得し、
-- salary列の大きい順に整列せよ
SELECT name, salary FROM Employee ORDER BY salary DESC;

-- 練習問題3-1-1 Employee表から、
-- name列とbirthday列を取得し、
-- birthday列の小さい順に整列せよ
SELECT name, birthday FROM Employee ORDER BY birthday ASC;

-- サンプル3-1-2 Employee表から、
-- name列、salary列、birthday列を取得し、
-- salary列の大きい順に整列し、
-- salary列が同じなら、birthday列の小さい順に整列せよ
SELECT name, salary, birthday FROM Employee 
ORDER BY salary DESC, birthday ASC;

-- 練習問題3-1-2 Employee表から、
-- dep_id列、name列、salary列を取得し、
-- dep_id列の小さい順に取得し、
-- dep_id列が同じなら、salary列の大きい順に取得せよ
SELECT dep_id, name, salary FROM Employee ORDER BY dep_id ASC, salary DESC;

-- サンプル3-1-3 Employee表から、
-- name列とsalary列を取得し、
-- salary列の大きい順に整列して、
-- 0行目から3件を取得せよ
SELECT name, salary FROM Employee 
ORDER BY salary DESC LIMIT 0, 3;

-- 練習問題3-1-3 Employee表から、
-- name列とsalary列を取得し、
-- salary列の大きい順に整列して、
-- 3行目から2件を取得せよ
SELECT name, salary FROM Employee ORDER BY salary DESC LIMIT 3, 2;

-- Lesson 3-2

-- サンプル3-2-1 Employee表から、
-- salary列の合計値と平均値を求めよ
SELECT SUM(salary), AVG(salary) FROM Employee;

-- 練習問題3-2-1 Employee表から、
-- salary列の最大値と最小値を求めよ
SELECT MAX(salary), MIN(salary) FROM Employee;

-- サンプル3-2-2 Employee表から、
-- salary列がNULLであるデータを除いたレコード件数を求めよ
SELECT COUNT(salary) FROM Employee;

-- 練習問題3-2-2 Employee表から、
-- 列がNULLであるデータを含めたレコード件数を求めよ
SELECT COUNT(*) FROM Employee;

-- サンプル3-2-3 Employee表から、
-- gender列が「男」という条件で、
-- レコード件数を求めよ
SELECT COUNT(gender) FROM Employee WHERE gender ='男';

-- 練習問題3-2-3 Employee表から、
-- salary列が40万円以上という条件で、
-- レコード件数を求めよ
SELECT COUNT(salary) FROM Employee WHERE salary >= 400000;

-- Employee表からsalary列の平均値を求めよ
SELECT AVG(salary) FROM Employee;

-- サンプル3-2-4 Employee表から、
ｰｰ salary列の平均値を取得し、
-- 小数点以下1桁、小数点以下なし、整数部の100の位で、
-- それぞれ四捨五入せよ
SELECT ROUND(AVG(salary), 1), 
ROUND(AVG(salary), 0), 
ROUND(AVG(salary), -2) FROM Employee;

-- 練習問題3-2-4 Employee表から、
-- salary列の平均値を求め、
-- 万単位で四捨五入せよ
SELECT ROUND(AVG(salary), -4) FROM Employee;

-- サンプル3-2-5 WHERE句で集約関数を使ったSQL文
-- （このSQL文は、エラーになります）
SELECT name, salary FROM Employee WHERE salary > AVG(salary);

-- サンプル3-2-6 サブクエリで集約関数を使ったSQL文
SELECT name, salary FROM Employee 
WHERE salary > (SELECT AVG(salary) FROM Employee);

-- Lesson 3-3

-- サンプル3-3-1 Employee表とDepartment表をdep_id列で結合し、
-- Employee表のname列とDepartment表のname列を取得せよ
SELECT Employee.name, Department.name FROM Employee, Department
WHERE Employee.dep_id = Department.dep_id;

-- 練習問題3-3-1 Employee表とDepartment表をdep_id列で結合し、
-- Employee表のname列とDepartment表のfloor列を、
-- floor列の小さい順に取得せよ
SELECT Employee.name, Department.floor FROM Employee, Department
WHERE Employee.dep_id = Department.dep_id ORDER BY Department.floor ASC;

-- サンプル3-3-2 Employee表とDepartment表をdep_idで内部結合し、
-- Employee表のname列とDepartment表のname列を取得せよ
SELECT Employee.name, Department.name 
FROM Employee INNER JOIN Department 
ON Employee.dep_id = Department.dep_id;

-- 練習問題3-3-2 Employee表とDepartment表をdep_idで内部結合し、
-- Employee表のname列とDepartment表のfloor列を、
-- floor列の小さい順に取得せよ
SELECT Employee.name, Department.floor 
FROM Employee INNER JOIN Department
ON Employee.dep_id = Department.dep_id 
ORDER BY Department.floor ASC;

-- サンプル3-3-3 Employee表とDepartment表を、
-- Employee表を基準としてdep_id列で左外部結合し、
-- Employee表のname列とDepartment表のname列を取得せよ
SELECT Employee.name, Department.name 
FROM Employee LEFT OUTER JOIN Department
ON Employee.dep_id = Department.dep_id;

-- 練習問題3-3-3 サンプル3-3-3のLEFT OUTER JOINを
-- RIGHT OUTER JOINに変更して実行せよ
SELECT Employee.name, Department.name 
FROM Employee RIGHT OUTER JOIN Department
ON Employee.dep_id = Department.dep_id;

-- Lesson 3-4

-- サンプル3-4-1 Employee表をdep_id列でグループ化し、
-- それぞれのグループのdep_id列、
-- およびsalary列の平均値を求めよ
SELECT dep_id, AVG(salary) FROM Employee GROUP BY dep_id;

-- 練習問題3-4-1 Employee表をgender列でグループ化し、
-- それぞれのグループのgender列、
-- およびsalary列の最大値を求めよ
SELECT gender, MAX(salary) FROM Employee GROUP BY gender;

-- サンプル3-4-2 Employee表をdep_id列でグループ化し、
-- salary列の平均値が30万円以上という条件で、
-- dep_id列およびsalary列の平均値を求めよ
SELECT dep_id, AVG(salary) FROM Employee 
GROUP BY dep_id HAVING AVG(salary) >= 300000;

-- 練習問題3-4-2 Employee表をdep_id列でグループ化し、
-- レコード件数が2以上という条件で、
-- それに該当するdep_id列とレコード件数を求めよ
SELECT dep_id, COUNT(*) FROM Employee GROUP BY dep_id HAVING COUNT(*) >= 2;

-- サンプル3-4-3 Employee表から、
-- gender列が「男」の行を抽出して、
-- dep_id列でグループ化して、
-- salary列の平均値が30万円以上のデータを抽出して、
-- dep_id列とsalary列の平均値を取得して、
-- salary列の平均値の大きい順に整列せよ
SELECT dep_id, AVG(salary)
FROM Employee
WHERE gender = '男'
GROUP BY dep_id
HAVING AVG(salary) >= 300000
ORDER BY AVG(salary) DESC;

-- 練習問題3-4-3 Employee表から、
-- dep_id列がNULLでない行を抽出して、
-- dep_id列でグループ化し、
-- birthday列の最大値が「1990年1月1日」以上のデータを抽出して、
-- dep_id列とレコード件数を取得して、
-- レコード件数の大きい順に整列せよ
SELECT dep_id, COUNT(*)
FROM Employee
WHERE dep_id IS NOT NULL
GROUP BY dep_id
HAVING MAX(birthday) >= '1990-01-01'
ORDER BY COUNT(*);

-- サンプル3-4-4 Employee表から、
-- name列とsalary列を取得し、
-- さらにsalary列の値に応じて
-- 「低賃金」「中賃金」「高賃金」「想定外」というデータを
-- 「salary level」という列名で取得せよ
SELECT name, salary,
CASE WHEN salary < 300000 THEN '低賃金'
     WHEN salary < 400000 THEN '中賃金'
     WHEN salary < 500000 THEN '高賃金'
     ELSE '想定外'
END AS 'salary level'
FROM Employee;

-- 練習問題3-4-4 Employee表から、
-- name列とbirthday列を取得し、
-- さらにbirthday列の値に応じて、
-- 1980年代以前なら「熟年社員」、
-- そうでなく1990年代以前なら「中堅社員」、
-- そうでなく2000年代以前なら「中堅社員」、
-- それ以外は「想定外」というデータを
-- 「age level」という列名で取得せよ
SELECT name, birthday,
CASE WHEN birthday < '1980-01-01' THEN '熟年社員'
     WHEN birthday < '1990-01-01' THEN '中堅社員'
     WHEN birthday < '2000-01-01' THEN '若手社員'
     ELSE '想定外'
END AS 'age level'
FROM Employee;

-- Lesson 4-1

-- サンプル4-1-1 Employee表から、
-- gender列が「男」という条件で、
-- すべての列を取得するSQL文を、
-- MaleEmployeeという名前のビューとして作成せよ
CREATE VIEW MaleEmployee 
AS SELECT * FROM Employee WHERE gender = '男';

-- 練習問題4-1-1 Employee表から、
-- gender列が「女」という条件で、
-- すべての列を取得するSQL文を、
-- FemaleEmployeeという名前のビューとして作成せよ
CREATE VIEW FemaleEmployee AS SELECT * FROM Employee WHERE gender = '女';

-- ビューが作成されていることを確認する
SHOW TABLES;

-- サンプル4-1-2 Employee表から、
-- gender列が「男」かつsalary列が30万円以上という条件で、
-- name列とsalary列を取得せよ
SELECT name, salary FROM Employee 
WHERE gender = '男' AND salary >= 300000;

-- サンプル4-1-3 MaleEmployeeビューから、
-- salary列が30万円以上という条件で、
-- name列とsalary列を取得せよ
SELECT name, salary FROM MaleEmployee WHERE salary >= 300000;

-- 練習問題4-1-2 FemaleEmployeeビューから、
-- birthday列が「1980-01--01」より小さいという条件で、
-- name列とbirthday列を取得せよ
SELECT name, birthday FROM FemaleEmployee WHERE birthday < '1980-01-01';

-- Lesson 4-2

-- サブクエリを使ったSQL文の例
SELECT name, salary FROM Employee
WHERE salary > (SELECT AVG(salary) FROM Employee);

-- 練習問題4-2-1 Employee表から、
-- salary列がsalary列の最大値と等しいという条件で、
-- name列とsalary列を取得せよ
SELECT name, salary FROM Employee 
WHERE salary = (SELECT MAX(salary) FROM Employee);

-- サンプル4-2-1 副問合せで、Employee表から、
-- dep_id列がNULLでなく、かつ、30万円以上という条件で
-- dep_id列を取得し、
-- その結果の中にdep_id列の値があるという条件で、
-- Department表から、
-- name列を取得せよ
SELECT name FROM Department WHERE dep_id
IN (SELECT DISTINCT dep_id FROM Employee
WHERE dep_id IS NOT NULL AND salary >= 300000);

-- 練習問題4-2-2 副問合せで、Employee表から、
-- dep_idがNULLでない、かつ、birthday列が「1990年01月01日」以降、
-- という条件でdep_id列を取得し、
-- その結果の中にdep_id列の値がないという条件で、
-- Department表から、
-- name列を取得せよ
SELECT name FROM Department WHERE dep_id
NOT IN (SELECT DISTINCT dep_id FROM Employee 
WHERE dep_id IS NOT NULL AND birthday >= '1990-01-01');

-- サンプル4-2-2 Employee表から、
-- salary列が、サブクエリで求めたグループごとの最大給与と等しい
-- という条件で、dep_id列、name列、salary列と求めよ
-- （このSQL文はエラーになります）
SELECT dep_id, name, salary FROM Employee
WHERE salary = (SELECT MAX(salary) FROM Employee GROUP BY dep_id);

-- サブクエリの部分だけを実行した結果の例
SELECT MAX(salary) FROM Employee GROUP BY dep_id;

-- サンプル4-2-2の「 = 」を「IN」に変えたSQL文の実行結果の例
SELECT dep_id, name, salary FROM Employee
WHERE salary IN (SELECT MAX(salary) FROM Employee GROUP BY dep_id);

-- サンプル4-2-3 相関サブクエリの例
SELECT dep_id, name, salary FROM Employee AS E1
WHERE salary = 
(SELECT MAX(salary) FROM Employee AS E2 
WHERE E1.dep_id = E2.dep_id GROUP BY dep_id);

-- 練習問題4-2-3 性別ごとにグループ分けして、
-- それぞれの性別で
-- 最高齢の社員の性別、名前、生年月日を取得せよ
SELECT gender, name, birthday FROM Employee AS E1
WHERE birthday = 
(SELECT MIN(birthday) FROM Employee AS E2 
WHERE E1.gender = E2.gender GROUP BY gender);

-- サンプル4-2-4 サブクエリでEmployee表から
-- salary列の平均値を求め、
-- メインクエリでEmployee表から
-- name列、salary列、salary列から平均値を引いた値を取得せよ
SELECT name, salary, 
salary - (SELECT AVG(salary) FROM Employee) AS difference
FROM Employee;

-- 練習問題4-2-4 サブクエリでEmployee表から
-- salary列の最大値を求め、
-- メインクエリでEmployee表から
-- name列、salary列、salary列から最大値を引いた値を
-- differenceという別名で取得せよ
SELECT name, salary, 
salary - (SELECT MAX(salary) FROM Employee) AS difference
FROM Employee;

-- Lesson 5-1

-- サンプル5-1-1 Employee表に
-- 「E00008、山本景子、女、1996-12-25、250000、D002」
-- という1件のデータを登録し、
-- 登録されたことを確認せよ
INSERT INTO Employee 
VALUES ('E00008', '山本景子', '女', '1996-12-25', 250000, 'D002');

SELECT * FROM Employee;

-- 練習問題5-1-1 Department表に
-- 「D004、営業部、12」という1件のデータを登録し、
-- 登録されたことを確認せよ
INSERT INTO Department VALUES ('D004', '営業部', 12);

SELECT * FROM Department;

-- サンプル5-1-2 Employee表に
-- 「E00009、渡辺五郎、男、1997-08-11、250000、D004」
-- という1件のデータを（略式表現を使わずに）登録し、
-- 登録されたことを確認せよ
INSERT INTO Employee (emp_id, name, gender, birthday, salary, dep_id)
VALUES ('E00009', '渡辺五郎', '男', '1997-08-11', 250000, 'D004');

SELECT * FROM Employee;

-- 練習問題5-1-2 Department表に
-- 「D005、経理部、8」という1件のデータを
-- （略式表現を使わずに）登録し、
-- 登録されたことを確認せよ
INSERT INTO Department (dep_id, name, floor) VALUES ('D005', '経理部', 8);

SELECT * FROM Department;

-- サンプル5-1-3 Employee表に、
-- name列が空（NULL）のレコードを登録して、
-- エラーになることを確認せよ
-- （このSQL文は、エラーになります）
INSERT INTO Employee 
VALUES ('E00010', NULL, '女', '1998-11-11', 250000, 'D005');

-- 練習問題5-1-3 Department表に、
-- name列が空（NULL）のレコードを登録して、
-- エラーになることを確認せよ
-- （このSQL文は、エラーになります）
INSERT INTO Department VALUES('D006', NULL, 12);

-- サンプル5-1-4 Employee表に
-- 主キーが重複するレコードを登録し、
-- エラーになることを確認せよ
-- （このSQL文は、エラーになります）
INSERT INTO Employee 
VALUES ('E00001', '岡田康夫', '男', '1996-08-11', 250000, 'D001');

-- 練習問題5-1-4 Department表に
-- 主キーが重複する（dep_id = 'D001'）レコードを登録し、
-- エラーになることを確認せよ
-- （このSQL文は、エラーになります）
INSERT INTO Department VALUES ('D001', '購買部', 12);

-- Lesson 5-2

-- サンプル5-2-1 Employee表で、
-- emp_idが「E00001」のレコードのname列を
-- 「鈴木太郎」に設定して更新し、
-- 更新されたことを確認せよ
UPDATE Employee SET name = '鈴木太郎' WHERE emp_id = 'E00001';

SELECT * FROM Employee WHERE emp_id = 'E00001';

-- 練習問題5-2-1 Employee表で、
-- emp_idが「E00001」のレコードのname列を
-- 「山田太郎」に設定して更新し、
-- 更新されたことを確認せよ
UPDATE Employee SET name = '山田太郎' WHERE emp_id = 'E00001';

SELECT * FROM Employee WHERE emp_id = 'E00001';

-- サンプル5-2-2 Employee表で、
-- gender列が「女」であるレコードのsalary列を
-- 30%アップして更新し、
-- 更新されたことを確認せよ
UPDATE Employee SET salary = salary * 1.3 WHERE gender = '女';

SELECT name, gender, salary FROM Employee;

-- 練習問題5-2-2 Employee表で、
-- gender列が「女」であるレコードのsalary列を
-- 元の値（30%アップする前の値）に戻して更新し、
-- 更新されたことを確認せよ
UPDATE Employee SET salary = salary / 1.3 WHERE gender = '女';

SELECT name, gender, salary FROM Employee;

-- サンプル5-2-3 Employee表で、
-- dep_id列が「D004」のレコードのsalary列を＋1万円で更新し、
-- さらにdep_id列を「D001」に更新し、
-- 更新されたことを確認せよ
UPDATE Employee SET salary = salary + 10000, dep_id = 'D001'
WHERE dep_id = 'D004'; 

SELECT name, salary, dep_id FROM Employee;

-- 練習問題5-2-3 Department表で、
-- dep_id列が「D005」のレコードのname列を「購買部」に更新し、
-- さらにfloor列を12に更新し、
-- 更新されたことを確認せよ
UPDATE Department SET name = '購買部', floor = 12 WHERE dep_id = 'D005';

SELECT * FROM Department;

-- サンプル5-2-4 Employee表で、
-- emp_idが「E00002」のレコードのemp_idを「E00001」に更新し、
-- エラーになることを確認せよ
-- （このSQL文は、エラーになります）
UPDATE Employee SET emp_id = 'E00001' WHERE emp_id = 'E00002';

-- 練習問題5-2-4 Department表で、
-- dep_idが「D002」のレコードのdep_idを「D001」に更新し、
-- エラーになることを確認せよ
-- （このSQL文は、エラーになります）
UPDATE Department SET dep_id = 'D001' WHERE dep_id = 'D002';

-- Lesson 5-3

-- サンプル5-3-1 Employee表から、
-- emp_id列が「E00009」のレコードを削除し、
-- 削除されたことを確認せよ
DELETE FROM Employee WHERE emp_id = 'E00009';

SELECT emp_id, name FROM Employee;

-- 練習問題5-3-1 Department表から、
-- dep_id列が「D005」のレコードを削除し、
-- 削除されたことを確認せよ
DELETE FROM Department WHERE dep_id = 'D005';

SELECT * FROM Department;

-- サンプル5-3-2 トランザクションを開始し、
-- Department表のすべてのデータを削除し、
-- 削除されたことを確認せよ
-- さらに、ロールバックを実行し、
-- 削除がキャンセルされたことを確認せよ
START TRANSACTION;

DELETE FROM Department;

SELECT * FROM Department;

ROLLBACK;

SELECT * FROM Department;

-- 練習問題5-3-2 トランザクションを開始し、
-- Department表のfloor列をすべて30階に更新し、
-- 更新されたことを確認せよ
-- さらにロールバックを実行し、
-- 更新がキャンセルされたことを確認せよ
START TRANSACTION;

UPDATE Department SET floor = 30;

SELECT * FROM Department;

ROLLBACK;

SELECT * FROM Department;

-- サンプル5-3-3 トランザクションを開始し、
-- Department表に
-- 「D006、システム管理部、12」というデータを登録せよ
-- さらに、トランザクションをコミットして、
-- 登録が確定していることを確認せよ
START TRANSACTION;

INSERT INTO Department VALUES ('D006', 'システム管理部', 12);

COMMIT;

SELECT * FROM Department;

-- 練習問題5-3-3 トランザクションを開始し、
-- Department表から
-- dep_idが「D006」であるデータを削除せよ
-- さらに、トランザクションをコミットして、
-- 削除が確定していることを確認せよ
START TRANSACTION;

DELETE FROM Department WHERE dep_id = 'D006';

COMMIT;

SELECT * FROM Department;