３０代初心者インフラエンジニアが実践すべきmysql　-準備編-

<H1>練習用テーブルを作成【CREATE TABLE】</H1>

```sql
--部門
INSERT INTO DEPARTMENT(DEPTCD,DEPTNAME) VALUES('01','人事部');
INSERT INTO DEPARTMENT(DEPTCD,DEPTNAME) VALUES('02','総務部');
INSERT INTO DEPARTMENT(DEPTCD,DEPTNAME) VALUES('03','企画部');
INSERT INTO DEPARTMENT(DEPTCD,DEPTNAME) VALUES('04','営業部');
INSERT INTO DEPARTMENT(DEPTCD,DEPTNAME) VALUES('05','購買部');
INSERT INTO DEPARTMENT(DEPTCD,DEPTNAME) VALUES('06','研究部');
INSERT INTO DEPARTMENT(DEPTCD,DEPTNAME) VALUES('07','開発部');
INSERT INTO DEPARTMENT(DEPTCD,DEPTNAME) VALUES('08','製造部');

-- 性別マスタ
INSERT INTO GENDER VALUES ( '1', '男性' );
INSERT INTO GENDER VALUES ( '2', '女性' );

-- 語学テーブル
INSERT INTO LANGUAGE VALUES ( '01', '英語' );
INSERT INTO LANGUAGE VALUES ( '02', '中国語' );
INSERT INTO LANGUAGE VALUES ( '03', '韓国語' );
INSERT INTO LANGUAGE VALUES ( '04', 'フランス語' );
INSERT INTO LANGUAGE VALUES ( '05', 'ドイツ語' );
INSERT INTO LANGUAGE VALUES ( '06', 'イタリア語' );
INSERT INTO LANGUAGE VALUES ( '07', 'スペイン語' );
INSERT INTO LANGUAGE VALUES ( '08', 'ポルトガル語' );
INSERT INTO LANGUAGE VALUES ( '09', 'ヒンディー語' );
INSERT INTO LANGUAGE VALUES ( '10', 'ロシア語' );
INSERT INTO LANGUAGE VALUES ( '99', 'その他' );

-- 都道府県マスタ
INSERT INTO PREFECTURE VALUES ( '01', '北海道',   '北海道地方' );
INSERT INTO PREFECTURE VALUES ( '02', '青森県',   '東北地方' );
INSERT INTO PREFECTURE VALUES ( '03', '秋田県',   '東北地方' );
INSERT INTO PREFECTURE VALUES ( '04', '岩手県',   '東北地方' );
INSERT INTO PREFECTURE VALUES ( '05', '宮城県',   '東北地方' );
INSERT INTO PREFECTURE VALUES ( '06', '山形県',   '東北地方' );
INSERT INTO PREFECTURE VALUES ( '07', '福島県',   '東北地方' );
INSERT INTO PREFECTURE VALUES ( '08', '茨城県',   '関東地方' );
INSERT INTO PREFECTURE VALUES ( '09', '群馬県',   '関東地方' );
INSERT INTO PREFECTURE VALUES ( '10', '栃木県',   '関東地方' );
INSERT INTO PREFECTURE VALUES ( '11', '埼玉県',   '関東地方' );
INSERT INTO PREFECTURE VALUES ( '12', '東京都',   '関東地方' );
INSERT INTO PREFECTURE VALUES ( '13', '千葉県',   '関東地方' );
INSERT INTO PREFECTURE VALUES ( '14', '神奈川県', '関東地方' );
INSERT INTO PREFECTURE VALUES ( '15', '山梨県',   '中部地方' );
INSERT INTO PREFECTURE VALUES ( '16', '長野県',   '中部地方' );
INSERT INTO PREFECTURE VALUES ( '17', '新潟県',   '中部地方' );
INSERT INTO PREFECTURE VALUES ( '18', '福井県',   '中部地方' );
INSERT INTO PREFECTURE VALUES ( '19', '富山県',   '中部地方' );
INSERT INTO PREFECTURE VALUES ( '20', '石川県',   '中部地方' );
INSERT INTO PREFECTURE VALUES ( '21', '静岡県',   '中部地方' );
INSERT INTO PREFECTURE VALUES ( '22', '岐阜県',   '中部地方' );
INSERT INTO PREFECTURE VALUES ( '23', '愛知県',   '中部地方' );
INSERT INTO PREFECTURE VALUES ( '24', '三重県',   '近畿地方' );
INSERT INTO PREFECTURE VALUES ( '25', '滋賀県',   '近畿地方' );
INSERT INTO PREFECTURE VALUES ( '26', '京都府',   '近畿地方' );
INSERT INTO PREFECTURE VALUES ( '27', '大阪府',   '近畿地方' );
INSERT INTO PREFECTURE VALUES ( '28', '兵庫県',   '近畿地方' );
INSERT INTO PREFECTURE VALUES ( '29', '奈良県',   '近畿地方' );
INSERT INTO PREFECTURE VALUES ( '30', '和歌山県', '近畿地方' );
INSERT INTO PREFECTURE VALUES ( '31', '鳥取県',   '中国地方' );
INSERT INTO PREFECTURE VALUES ( '32', '島根県',   '中国地方' );
INSERT INTO PREFECTURE VALUES ( '33', '岡山県',   '中国地方' );
INSERT INTO PREFECTURE VALUES ( '34', '広島県',   '中国地方' );
INSERT INTO PREFECTURE VALUES ( '35', '山口県',   '中国地方' );
INSERT INTO PREFECTURE VALUES ( '36', '香川県',   '四国地方' );
INSERT INTO PREFECTURE VALUES ( '37', '愛媛県',   '四国地方' );
INSERT INTO PREFECTURE VALUES ( '38', '徳島県',   '四国地方' );
INSERT INTO PREFECTURE VALUES ( '39', '高知県',   '四国地方' );
INSERT INTO PREFECTURE VALUES ( '40', '福岡県',   '九州地方' );
INSERT INTO PREFECTURE VALUES ( '41', '佐賀県',   '九州地方' );
INSERT INTO PREFECTURE VALUES ( '42', '長崎県',   '九州地方' );
INSERT INTO PREFECTURE VALUES ( '43', '大分県',   '九州地方' );
INSERT INTO PREFECTURE VALUES ( '44', '熊本県',   '九州地方' );
INSERT INTO PREFECTURE VALUES ( '45', '宮崎県',   '九州地方' );
INSERT INTO PREFECTURE VALUES ( '46', '鹿児島県', '九州地方' );
INSERT INTO PREFECTURE VALUES ( '47', '沖縄県',   '九州地方' );

-- 社員テーブル
INSERT INTO EMPLOYEE VALUES ( '0299801', '足立一典',     '1', '1976-05-15', '02' );
INSERT INTO EMPLOYEE VALUES ( '0399902', '荒川静香',     '2', '1977-12-29', '05' );
INSERT INTO EMPLOYEE VALUES ( '0500105', '板橋壮介',     '1', '1978-07-31', '47' );
INSERT INTO EMPLOYEE VALUES ( '0500108', '江戸川智',     '1', '1979-02-23', '01' );
INSERT INTO EMPLOYEE VALUES ( '0700312', '大田由希',     '2', '1981-08-19', '26' );
INSERT INTO EMPLOYEE VALUES ( '0700318', '葛飾北斎',     '1', '1981-11-30', '28' );
INSERT INTO EMPLOYEE VALUES ( '0800405', '江東拓也',     '1', '1982-05-15', '40' );
INSERT INTO EMPLOYEE VALUES ( '0800408', '北さやか',     '2', '1983-01-29', '05' );
INSERT INTO EMPLOYEE VALUES ( '1000612', '品川早紀',     '2', '1984-09-03', '12' );
INSERT INTO EMPLOYEE VALUES ( '1000620', '渋谷健一',     '1', '1985-03-10', '31' );
INSERT INTO EMPLOYEE VALUES ( '1000632', '新宿太郎',     '1', '1984-10-11', '30' );
INSERT INTO EMPLOYEE VALUES ( '1100718', '杉並モハマド', '1', '1985-08-17', NULL );
INSERT INTO EMPLOYEE VALUES ( '1401017', '墨田智樹',     '1', '1988-12-01', '41' );
INSERT INTO EMPLOYEE VALUES ( '1401023', '世田谷豪太郎', '1', '1988-12-10', '27' );
INSERT INTO EMPLOYEE VALUES ( '1501171', '台東郁夫',     '1', '1989-06-01', '41' );
INSERT INTO EMPLOYEE VALUES ( '1501179', '中央義勝',     '1', '1989-11-12', '33' );
INSERT INTO EMPLOYEE VALUES ( '1601203', '千代田勝',     '1', '1990-07-31', '39' );
INSERT INTO EMPLOYEE VALUES ( '1701365', '豊島哲也',     '1', '1991-05-28', '12' );
INSERT INTO EMPLOYEE VALUES ( '1801451', '中野ゆかり',   '2', '1992-08-25', '23' );
INSERT INTO EMPLOYEE VALUES ( '2001630', '練馬陽子',     '2', '1994-09-25', '22' );
INSERT INTO EMPLOYEE VALUES ( '2001660', '文京任三郎',   '1', '1995-03-25', '07' );
INSERT INTO EMPLOYEE VALUES ( '2101799', '港アリス',     '2', '1995-12-29', NULL );
INSERT INTO EMPLOYEE VALUES ( '2201840', '目黒リリー',   '2', '1997-01-18', NULL );

-- 所属テーブル
INSERT INTO ASSIGNMENT VALUES ('0299801', '1998-04-01', '01', 500000 );
INSERT INTO ASSIGNMENT VALUES ('0399902', '1999-06-01', '02', 450000 );
INSERT INTO ASSIGNMENT VALUES ('0500105', '2001-04-01', '02', 450000 );
INSERT INTO ASSIGNMENT VALUES ('0500108', '2001-04-16', '06', 600000 );
INSERT INTO ASSIGNMENT VALUES ('0700312', '2003-10-01', '05', 400000 );
INSERT INTO ASSIGNMENT VALUES ('0700318', '2003-10-01', '06', 400000 );
INSERT INTO ASSIGNMENT VALUES ('0800405', '2004-10-01', '06', 350000 );
INSERT INTO ASSIGNMENT VALUES ('0800408', '2005-01-01', '05', 400000 );
INSERT INTO ASSIGNMENT VALUES ('1000612', '2006-04-01', '05', 780000 );
INSERT INTO ASSIGNMENT VALUES ('1000620', '2006-04-01', '04', 700000 );
INSERT INTO ASSIGNMENT VALUES ('1000632', '2006-08-16', '04', 250000 );
INSERT INTO ASSIGNMENT VALUES ('1100718', '2007-04-01', '07', 300000 );
INSERT INTO ASSIGNMENT VALUES ('1401017', '2010-04-01', '06', 250000 );
INSERT INTO ASSIGNMENT VALUES ('1401023', '2010-10-01', '07', 350000 );
INSERT INTO ASSIGNMENT VALUES ('1501171', '2011-06-01', '08', 380000 );
INSERT INTO ASSIGNMENT VALUES ('1501179', '2012-02-01', '08', 320000 );
INSERT INTO ASSIGNMENT VALUES ('1601203', '2012-04-01', '07', 300000 );
INSERT INTO ASSIGNMENT VALUES ('1701365', '2013-04-01', '04', 280000 );
INSERT INTO ASSIGNMENT VALUES ('1801451', '2014-06-01', '07', 260000 );
INSERT INTO ASSIGNMENT VALUES ('2001630', '2016-04-01', '07', 250000 );
INSERT INTO ASSIGNMENT VALUES ('2001660', '2016-10-01', '08', 250000 );
INSERT INTO ASSIGNMENT VALUES ('2101799', '2017-10-01', '05', 240000 );
INSERT INTO ASSIGNMENT VALUES ('2201840', '2018-06-01', '01', 220000 );


-- 語学力テーブル
INSERT INTO LANGSKILL VALUES ('0299801', '01' );
INSERT INTO LANGSKILL VALUES ('0299801', '02' );
INSERT INTO LANGSKILL VALUES ('0399902', '01' );
INSERT INTO LANGSKILL VALUES ('0700312', '04' );
INSERT INTO LANGSKILL VALUES ('0700312', '05' );
INSERT INTO LANGSKILL VALUES ('0700312', '06' );
INSERT INTO LANGSKILL VALUES ('0800408', '01' );
INSERT INTO LANGSKILL VALUES ('1100718', '02' );
INSERT INTO LANGSKILL VALUES ('1100718', '09' );
INSERT INTO LANGSKILL VALUES ('1401023', '99' );
INSERT INTO LANGSKILL VALUES ('1801451', '01' );
INSERT INTO LANGSKILL VALUES ('1801451', '10' );
INSERT INTO LANGSKILL VALUES ('2101799', '01' );
INSERT INTO LANGSKILL VALUES ('2101799', '04' );
INSERT INTO LANGSKILL VALUES ('2201840', '01' );
INSERT INTO LANGSKILL VALUES ('2201840', '07' );
INSERT INTO LANGSKILL VALUES ('2201840', '08' );

-- サンプル集用社員テーブル
INSERT INTO WORKER VALUES ('1','小泉純一郎',1000,'03');
INSERT INTO WORKER VALUES ('2','安倍晋三',700,'01');
INSERT INTO WORKER VALUES ('3','福田康夫',500,'04');
INSERT INTO WORKER VALUES ('4','麻生太郎',900,'10');
INSERT INTO WORKER VALUES ('5','鳩山由紀夫',1100,'01');
INSERT INTO WORKER VALUES ('6','菅直人',750,'09');
INSERT INTO WORKER VALUES ('7','野田佳彦',800,'08');
INSERT INTO WORKER VALUES ('8','菅義偉',900,'04');
INSERT INTO WORKER VALUES ('9','岸田文雄',650,'01');

```

３０代初心者インフラエンジニアが実践すべきmysql　-実践編-　へ移動