■Sample601
CREATE DATABASE dummy;
USE dummy;

■Sample602
CREATE TABLE sample1(
    col1 INT PRIMARY KEY,
    col2 VARCHAR(20),
    col3 TIMESTAMP,
    col4 INT
);

■Sample603
INSERT INTO sample1 VALUES (1, 'ABC', '2021/1/1', 100);
INSERT INTO sample1 VALUES (2, 'DEF', '2021/1/2', 100);
INSERT INTO sample1 VALUES (3, 'ABC', '2021/1/1', 200);

■Sample604
SELECT * FROM sample1;

■Sample605
INSERT INTO sample1 VALUES (NULL, 'ABC', '2021/1/1', 100);

■Sample606
INSERT INTO sample1 VALUES(1, 'GHI', '2021/1/3', 300);

■Sample607
CREATE TABLE sample2(
    col1 INT,
    col2 VARCHAR(30),
    col3 TIMESTAMP,
    col4 INT,
    PRIMARY KEY(col1)
);

■Sample608
INSERT INTO sample2 VALUES (1, 'ABC', '2021/1/1', 100);
INSERT INTO sample2 VALUES (2, 'DEF', '2021/1/2', 100);
INSERT INTO sample2 VALUES (3, 'ABC', '2021/1/1', 200);

■Sample609
SELECT * FROM sample2;

■Sample610
INSERT INTO sample2 VALUES (NULL, 'ABC', '2021/1/1', 100);

■Sample611
INSERT INTO sample2 VALUES (1, 'GHI', '2021/1/3' ,300);

■Sample612
CREATE TABLE sample3(
    col1 INT,
    col2 VARCHAR(30),
    col3 TIMESTAMP,
    col4 INT,
    PRIMARY KEY(col1, col2)
);

■Sample613
INSERT INTO sample3 VALUES (1, 'ABC', '2021/1/1', 100);
INSERT INTO sample3 VALUES (1, 'DEF', '2021/1/2', 100);
INSERT INTO sample3 VALUES (2, 'ABC', '2021/1/3', 200);

■Sample614
SELECT * FROM sample3;

■Sample615
INSERT INTO sample3 VALUES (1, 'ABC', '2021/1/4', 300);

■Sample616
CREATE TABLE sample4(
    col1 INT NOT NULL,
    col2 VARCHAR(30),
    col3 TIMESTAMP,
    col4 INT
)

■Sample617
INSERT INTO sample4 VALUES (1, NULL, '2021/1/1', 100);
INSERT INTO sample4 VALUES (1, 'DEF', NULL, 100);
INSERT INTO sample4 VALUES (2, 'ABC', '2021/1/3', NULL);

■Sample618
SELECT * FROM sample4;

■Sample619
INSERT INTO sample4 VALUES (NULL, 'ABC', '2021/1/4', 200);

■Sample620
CREATE TABLE sample5(
    col1 INT UNIQUE,
    col2 VARCHAR(30),
    col3 TIMESTAMP,
    col4 INT
);

■Sample621
INSERT INTO sample5 VALUES (1, 'ABC', '2021/1/1', 100);
INSERT INTO sample5 VALUES (2, 'DEF', '2021/1/1', 200);
INSERT INTO sample5 VALUES (3, 'ABC', '2021/1/2', 200);

■Sample622
SELECT * FROM sample5;

■Sample623
INSERT INTO sample5 VALUES (1, 'DEF', '2021/1/3', 300);

■Sample624
IINSERT INTO sample5 VALUES (NULL, 'DEF', '2021/1/3', 300);

■Sample625
SELECT * FROM sample5;

■Sample626
CREATE TABLE sample5(
    col1 INT,
    col2 VARCHAR(30),
    col3 TIMESTAMP,
    col4 INT,
    UNIQUE(col1)
);

■Sample627
# 部署テーブル
CREATE TABLE dept(
    id INT PRIMARY KEY,
    name VARCHAR(10)
);

INSERT INTO dept VALUES (1, '営業部');
INSERT INTO dept VALUES (2, '総務部');

■Sample628
SELECT * FROM dept;

■Sample629
# 社員テーブル
CREATE TABLE staff(
  id INT,
  name VARCHAR(30),
  dept_id INT,
  FOREIGN KEY(dept_id) REFERENCES dept(id)
);

■Sample630
INSERT INTO staff VALUES (1, '前田恵子', 1);
INSERT INTO staff VALUES (2, '高橋伸', 2);
INSERT INTO staff VALUES (3, '遠山満子', 2);

■Sample631
SELECT
  staff.id AS '社員番号',
  staff.name AS '名前',
  dept.name AS '所属'
FROM staff
INNER JOIN dept
ON staff.dept_id = dept.id;

■Sample632
INSERT INTO staff VALUES(4,'後藤昭',3);

■Sample633
INSERT INTO dept VALUES (3, '経理部');

■Sample634
SELECT * FROM dept;

■Sample635
CREATE TABLE sample6(
    col1 INT DEFAULT 10,
    col2 VARCHAR(30) DEFAULT 'ななしさん',
    col3 TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
    col4 INT
);

■Sample636
INSERT INTO sample6 VALUES ();

■Sample637
CREATE TABLE sample7(
    col1 INT AUTO_INCREMENT,
    col2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
    PRIMARY KEY(col1)
);

■Sample638
INSERT INTO sample7 VALUES ();

■Sample639
SELECT * FROM sample7;

■Sample640
# companyデータベースの作成
CREATE DATABASE company;

# デフォルトデータベースをcompanyデータベースに切り替える
USE company;

■Sample641
CREATE TABLE sex(
    sex_id INT PRIMARY KEY,
    sex      CHAR(2) NOT NULL
);

■Sample642
INSERT INTO sex VALUES (1, '男性');
INSERT INTO sex VALUES (2, '女性');

■Sample643
SELECT * FROM sex;

■Sample644
CREATE TABLE employee(
    id         INT PRIMARY KEY,
    name   VARCHAR(30) NOT NULL,
    age      INT NOT NULL,
    sex_id INT NOT NULL,
    salary  INT DEFAULT 324000 NOT NULL,
    FOREIGN KEY(sex_id) REFERENCES sex(sex_id)
);

■Sample645
INSERT INTO employee VALUES (2030, '山田太郎', 30, 1, 324000);
INSERT INTO employee VALUES (2031, '佐藤幸一', 35, 1, 412000);
INSERT INTO employee VALUES (2032, '大峰聡子', 28, 2, 290000);
INSERT INTO employee VALUES (2033, '桜井直子', 43, 2, 452000);

■Sample646
SELECT * FROM employee;

■Sample647
CREATE TABLE qual(
    qual_id INT AUTO_INCREMENT PRIMARY KEY,
    qual      VARCHAR(20) NOT NULL
);

■Sample648
INSERT INTO qual (qual) VALUES ('普通自動車第1種免許');
INSERT INTO qual (qual) VALUES ('英語検定2級');
INSERT INTO qual (qual) VALUES ('英語検定1級');
INSERT INTO qual (qual) VALUES ('日商簿記2級');
INSERT INTO qual (qual) VALUES ('日商簿記1級');
INSERT INTO qual (qual) VALUES ('TOEIC(750以上)');
INSERT INTO qual (qual) VALUES ('中小企業診断士');

■Sample649
SELECT * FROM qual;

■Sample650
CREATE TABLE emp_qual(
    id          INT NOT NULL,
    qual_id INT NOT NULL,
    FOREIGN KEY(id) REFERENCES employee(id),
    FOREIGN KEY(qual_id) REFERENCES qual(qual_id)
);

■Sample647
INSERT INTO emp_qual VALUES (2030, 1);
INSERT INTO emp_qual VALUES (2030, 5);
INSERT INTO emp_qual VALUES (2030, 7);
INSERT INTO emp_qual VALUES (2031, 5);
INSERT INTO emp_qual VALUES (2031, 6);
INSERT INTO emp_qual VALUES (2033, 7);
INSERT INTO emp_qual VALUES (2033, 1);

■Sample652
SELECT * FROM emp_qual;

■Sample653
SELECT id, name, age, sex, salary FROM employee
INNER JOIN sex USING (sex_id);

■Sample654
SELECT
  id,
  name
FROM employee
INNER JOIN emp_qual USING (id)
INNER JOIN qual USING (qual_id)
WHERE qual = '中小企業診断士';
