質問編集履歴
1
指摘内容の反映（テーブル情報をSQL文に更新）
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -8,33 +8,73 @@
|
|
|
8
8
|
|
|
9
9
|
|
|
10
10
|
### 使うテーブル
|
|
11
|
-
#### titles
|
|
12
|
-
|emp_no|title|from_date|to_date|
|
|
13
|
-
|:--:|:--:|:--:|:--:|
|
|
14
|
-
|10001|Senior Engineer|1986-06-26|9999-01-01|
|
|
15
|
-
|10002|Staff|1996-08-03|9999-01-01|
|
|
16
|
-
|
|
11
|
+
titlesテーブルとdept_empテーブルはそれぞれ443308レコード、331603レコードあってすべてのレコードをINSERT文の中に記述することはできないため、5レコードのみ記載します。
|
|
17
|
-
|10004|Engineer|1986-12-01|1995-12-01|
|
|
18
|
-
|10004|Staff|1995-12-01|9999-01-01|
|
|
19
12
|
|
|
20
|
-
#### dept_emp
|
|
21
|
-
|emp_no|dept_no|from_date|to_date|
|
|
22
|
-
|:--:|:--:|:--:|:--:|
|
|
23
|
-
|10001|d005|1986-06-26|9999-01-01|
|
|
24
|
-
|10002|d007|1996-08-03|9999-01-01|
|
|
25
|
-
|10003|d004|1995-12-03|9999-01-01|
|
|
26
|
-
|10004|d004|1986-12-01|1995-12-01|
|
|
27
|
-
|10005|d003|1989-09-12|9999-01-01|
|
|
28
13
|
|
|
29
|
-
####
|
|
14
|
+
#####titles
|
|
15
|
+
```SQL
|
|
16
|
+
CREATE TABLE "titles"
|
|
17
|
+
(
|
|
18
|
+
"emp_no" INT(11) PRIMARY KEY,
|
|
19
|
+
"title" VARCHAR(50) PRIMARY KEY,
|
|
20
|
+
"from_date" DATE PRIMARY KEY,
|
|
30
|
-
|
|
21
|
+
"to_date" DATE
|
|
31
|
-
|
|
22
|
+
)
|
|
32
|
-
|d001|Marketing|
|
|
33
|
-
|d002|Finance|
|
|
34
|
-
|d003|Human Resources|
|
|
35
|
-
|d004|Production|
|
|
36
|
-
|d005|Development|
|
|
37
23
|
|
|
24
|
+
INSERT INTO "titles"
|
|
25
|
+
("emp_no", "title", "from_date", "to_date")
|
|
26
|
+
VALUES
|
|
27
|
+
(10001, "Senior Engineer", "1986-06-26", "9999-01-01"),
|
|
28
|
+
(10002, "Staff", "1996-08-03", "9999-01-01"),
|
|
29
|
+
(10003, "Senior Engineer", "1995-12-03", "9999-01-01"),
|
|
30
|
+
(10004, "Engineer", "1986-12-01", "1995-12-01"),
|
|
31
|
+
(10004, "Staff", "1995-12-01", "9999-01-01")
|
|
32
|
+
|
|
33
|
+
```
|
|
34
|
+
|
|
35
|
+
|
|
36
|
+
##### dept_emp
|
|
37
|
+
```SQL
|
|
38
|
+
CREATE TABLE "dept_emp"
|
|
39
|
+
(
|
|
40
|
+
"emp_no" INT(11) PRIMARY KEY,
|
|
41
|
+
"dept_no" CHAR(4) PRIMARY KEY,
|
|
42
|
+
"from_date" DATE,
|
|
43
|
+
"to_date" DATE
|
|
44
|
+
)
|
|
45
|
+
|
|
46
|
+
INSERT INTO "dept_emp"
|
|
47
|
+
("emp_no", "dept_no", "from_date", "to_date")
|
|
48
|
+
VALUES
|
|
49
|
+
(10001, "d005", "1986-06-26", "9999-01-01"),
|
|
50
|
+
(10002, "d007", "1996-08-03", "9999-01-01"),
|
|
51
|
+
(10003, "d004", "1995-12-03", "9999-01-01"),
|
|
52
|
+
(10004, "d004", "1986-12-01", "1995-12-01"),
|
|
53
|
+
(10005, "d003", "1989-09-12", "9999-01-01");
|
|
54
|
+
```
|
|
55
|
+
|
|
56
|
+
##### departments
|
|
57
|
+
```SQL
|
|
58
|
+
CREATE TABLE "departments"
|
|
59
|
+
(
|
|
60
|
+
"dept_no" CHAR(4) PRIMARY KEY,
|
|
61
|
+
"dept_name" VARCHAR(40) UNIQUE
|
|
62
|
+
)
|
|
63
|
+
|
|
64
|
+
INSERT INTO "departments"
|
|
65
|
+
("dept_no","dept_name")
|
|
66
|
+
VALUES
|
|
67
|
+
("d001", "Marketing"),
|
|
68
|
+
("d002", "Finance"),
|
|
69
|
+
("d003", "Human Resources"),
|
|
70
|
+
("d004", "Production"),
|
|
71
|
+
("d005", "Development"),
|
|
72
|
+
("d006", "Quality Management"),
|
|
73
|
+
("d007", "Sales"),
|
|
74
|
+
("d008", "Research"),
|
|
75
|
+
("d009", "Customer Service");
|
|
76
|
+
```
|
|
77
|
+
|
|
38
78
|
### 試したこと
|
|
39
79
|
|
|
40
80
|
内部結合を用いた集計を行うことには（おそらく）成功しました。
|