練習用テーブルを作成【CREATE TABLE】
--部門
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 -実践編- へ移動