Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE DEPARTMENTS (
	ID INT NOT NULL, -- 部門ID
	CODE VARCHAR(2) NOT NULL, -- 部門代碼
	NAME VARCHAR(20) NOT NULL, -- 部門名稱
	PRIMARY KEY (ID)
);
CREATE TABLE EMPLOYEES (
	ID INT NOT NULL, -- 員工ID
	NAME VARCHAR(20) NOT NULL,	-- 姓名
	GENDER VARCHAR(1) NULL, -- 性別：M男性、F女性、NULL
	BIRTHDATE DATE NULL, -- 出生日期
	HIREDATE DATE NOT NULL, -- 到職日期
	EXTENSION VARCHAR(5) NULL, -- 分機號碼
	ADDRESS VARCHAR(200) NULL, -- 地址
	MANAGERID INT NULL, -- 主管ID
	DEPARTMENTID INT NOT NULL, -- 部門ID
	PRIMARY KEY (ID),
    CONSTRAINT FK_EMPLOYEEDEPT FOREIGN KEY (DEPARTMENTID) REFERENCES DEPARTMENTS(ID)
);
CREATE TABLE TRAINING (
	EMPLOYEEID INT NOT NULL, -- 員工編號
	TYPE VARCHAR(1) NOT NULL, -- 訓練類型
	GRADE VARCHAR(1) NOT NULL -- 訓練成績：1-5
);
CREATE TABLE MANAGERS (
	EMPLOYEEID INT NOT NULL,
	DEPARTMENTID INT NOT NULL,
	ALTER_TIMESTAMP TIMESTAMP NOT NULL
);
INSERT INTO DEPARTMENTS (ID, CODE, NAME) VALUES (1, 'HR', '人力資源');
1 rows affected

INSERT INTO DEPARTMENTS (ID, CODE, NAME) VALUES (2, 'MA', '行銷企劃');
1 rows affected

INSERT INTO DEPARTMENTS (ID, CODE, NAME) VALUES (3, 'RD', '產品研發');
1 rows affected

INSERT INTO DEPARTMENTS (ID, CODE, NAME) VALUES (4, 'SD', '業務開發');
1 rows affected

INSERT INTO DEPARTMENTS (ID, CODE, NAME) VALUES (5, 'QA', '品質保管');
1 rows affected

COMMIT;
INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (1, '張瑾雯', 'F', TO_DATE('19610409', 'YYYYMMDD'), TO_DATE('19851209', 'YYYYMMDD'), '32389', '台北市中山區中山北路一段7號', NULL, 1);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (2, '陳季暄', 'M', TO_DATE('19501211', 'YYYYMMDD'), TO_DATE('19751111', 'YYYYMMDD'), '32690', '桃園市中壢區八德路111號', NULL, 2);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (3, '趙飛燕', 'F', TO_DATE('19560205', 'YYYYMMDD'), TO_DATE('19760403', 'YYYYMMDD'), '31348', '台北市中正區紹興南路99號', NULL, 2);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (4, '林美麗', 'F', TO_DATE('19880318', 'YYYYMMDD'), TO_DATE('20160707', 'YYYYMMDD'), '31579', '高雄市一心二路675號', NULL, 3);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (5, '劉天王', 'M', TO_DATE('19721009', 'YYYYMMDD'), TO_DATE('20081101', 'YYYYMMDD'), '36594', '', NULL, 3);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (6, '黎國明', 'M', TO_DATE('19590505', 'YYYYMMDD'), TO_DATE('19810301', 'YYYYMMDD'), '21432', '新竹市富明街72巷16號', 1, 1);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (7, '郭國臹', 'M', TO_DATE('19630908', 'YYYYMMDD'), TO_DATE('19850718', 'YYYYMMDD'), '45634', '', 3, 2);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (8, '蘇涵蘊', NULL, TO_DATE('19680304', 'YYYYMMDD'), TO_DATE('19900103', 'YYYYMMDD'), '23454', '台北市木柵三路100號5樓', 3, 2);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (9, '孟庭亭', NULL, TO_DATE('19770401', 'YYYYMMDD'), TO_DATE('20070801', 'YYYYMMDD'), '45673', '', 4, 3);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (10,'賴俊良', NULL, TO_DATE('19791030', 'YYYYMMDD'), TO_DATE('19990910', 'YYYYMMDD'), '38552', '', 1, 1);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (12,'何大樓', NULL, TO_DATE('19831116', 'YYYYMMDD'), TO_DATE('20051210', 'YYYYMMDD'), '35964', '', 2, 2);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (13,'王大德', NULL, TO_DATE('19731204', 'YYYYMMDD'), TO_DATE('19951114', 'YYYYMMDD'), '23794', '高雄市文衡一路123號', 5, 3);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (14,'林正明', NULL, TO_DATE('19840912', 'YYYYMMDD'), TO_DATE('20071203', 'YYYYMMDD'), '89245', '', 4, 3);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (15,'劉文林', NULL, TO_DATE('19850714', 'YYYYMMDD'), TO_DATE('20040401', 'YYYYMMDD'), '45976', '台中市鎮北路96號', 1, 1);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (16,'李友吉', NULL, TO_DATE('19820617', 'YYYYMMDD'), TO_DATE('20000101', 'YYYYMMDD'), '43505', '', 4, 3);
1 rows affected

COMMIT;

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (17,'侯碧元', NULL, TO_DATE('19770509', 'YYYYMMDD'), TO_DATE('19980805', 'YYYYMMDD'), '34209', '', 4, 3);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (18,'文芳芳', 'F', TO_DATE('19760819', 'YYYYMMDD'), TO_DATE('19960309', 'YYYYMMDD'), '95049', '', 3, 2);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (19,'呂天生', 'M', TO_DATE('19790910', 'YYYYMMDD'), TO_DATE('19990512', 'YYYYMMDD'), '95076', '台北市師大路67號', 2, 2);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (20,'陳  旻', 'M', TO_DATE('19810201', 'YYYYMMDD'), TO_DATE('20021003', 'YYYYMMDD'), '49073', '', 4, 3);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (21,'蔡一心', 'F', TO_DATE('19880315', 'YYYYMMDD'), TO_DATE('20091008', 'YYYYMMDD'), '43293', '', 5, 3);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (22,'田登文', 'M', TO_DATE('19881210', 'YYYYMMDD'), TO_DATE('20091201', 'YYYYMMDD'), '90584', '台中市文化路700號', 1, 1);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (23,'嚴  正', 'M', TO_DATE('19891003', 'YYYYMMDD'), TO_DATE('20070502', 'YYYYMMDD'), '39099', '', 2, 2);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (24,'王明田', 'M', TO_DATE('19850801', 'YYYYMMDD'), TO_DATE('20060805', 'YYYYMMDD'), '49087', '', 5, 3);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (25,'林天生', 'F', TO_DATE('19810101', 'YYYYMMDD'), TO_DATE('20040312', 'YYYYMMDD'), '29018', '台北市北平東路24 號3 樓之一', 5, 3);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (26,'湯玉婷', 'F', TO_DATE('19900710', 'YYYYMMDD'), TO_DATE('20110409', 'YYYYMMDD'), '76085', '台北市北平東路24 號3 樓之一', 3, 2);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (27,'林秀吉', 'F', TO_DATE('19900919', 'YYYYMMDD'), TO_DATE('20130802', 'YYYYMMDD'), '45896', '台北市北平東路24 號3 樓之一', 4, 3);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (28,'毛高雯', 'F', TO_DATE('19900514', 'YYYYMMDD'), TO_DATE('20131107', 'YYYYMMDD'), '68598', '高雄縣鳳山市澄清路255巷532號', 4, 3);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (29,'蔡天林', 'M', TO_DATE('19910719', 'YYYYMMDD'), TO_DATE('20141209', 'YYYYMMDD'), '80486', '台中市復興一路333巷555號6樓', 5, 3);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (30,'傅文章', 'M', TO_DATE('19911031', 'YYYYMMDD'), TO_DATE('20140615', 'YYYYMMDD'), '90449', '台北市南京東路三段3號', 1, 1);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (31,'劉富生', 'M', TO_DATE('19950219', 'YYYYMMDD'), TO_DATE('20160819', 'YYYYMMDD'), '08835', '台北市北平東路24號', 5, 3);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (32,'呂應宏', 'M', TO_DATE('19930123', 'YYYYMMDD'), TO_DATE('20161109', 'YYYYMMDD'), '03458', '高雄縣武廟路912號', 1, 1);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (33,'李石柱', 'M', TO_DATE('19920715', 'YYYYMMDD'), TO_DATE('20160208', 'YYYYMMDD'), '06894', '台南市中正二路3421號', 5, 3);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (34,'黃智文', 'M', TO_DATE('19920321', 'YYYYMMDD'), TO_DATE('20150321', 'YYYYMMDD'), '43503', '高雄縣崗山鎮光復路150巷5號3樓', 4, 3);
1 rows affected

INSERT INTO EMPLOYEES (ID, NAME, GENDER, BIRTHDATE, HIREDATE, EXTENSION, ADDRESS, MANAGERID, DEPARTMENTID) VALUES (35,'丁慧語', 'F', TO_DATE('19920221', 'YYYYMMDD'), TO_DATE('20150527', 'YYYYMMDD'), '59908', '', 5, 3);
1 rows affected

COMMIT;

INSERT INTO TRAINING (EMPLOYEEID, TYPE, GRADE) VALUES (24, 'A', 2);
1 rows affected

INSERT INTO TRAINING (EMPLOYEEID, TYPE, GRADE) VALUES (24, 'B', 5);
1 rows affected

INSERT INTO TRAINING (EMPLOYEEID, TYPE, GRADE) VALUES (26, 'B', 3);
1 rows affected

INSERT INTO TRAINING (EMPLOYEEID, TYPE, GRADE) VALUES (27, 'B', 4);
1 rows affected

INSERT INTO TRAINING (EMPLOYEEID, TYPE, GRADE) VALUES (27, 'C', 4);
1 rows affected

INSERT INTO TRAINING (EMPLOYEEID, TYPE, GRADE) VALUES (30, 'C', 5);
1 rows affected

INSERT INTO TRAINING (EMPLOYEEID, TYPE, GRADE) VALUES (31, 'A', 2);
1 rows affected

INSERT INTO TRAINING (EMPLOYEEID, TYPE, GRADE) VALUES (31, 'B', 4);
1 rows affected

INSERT INTO TRAINING (EMPLOYEEID, TYPE, GRADE) VALUES (31, 'C', 3);
1 rows affected

INSERT INTO TRAINING (EMPLOYEEID, TYPE, GRADE) VALUES (32, 'B', 3);
1 rows affected

INSERT INTO TRAINING (EMPLOYEEID, TYPE, GRADE) VALUES (33, 'A', 5);
1 rows affected

INSERT INTO TRAINING (EMPLOYEEID, TYPE, GRADE) VALUES (33, 'C', 4);
1 rows affected

INSERT INTO TRAINING (EMPLOYEEID, TYPE, GRADE) VALUES (34, 'B', 5);
1 rows affected

INSERT INTO TRAINING (EMPLOYEEID, TYPE, GRADE) VALUES (35, 'B', 3);
1 rows affected

COMMIT;
Select EMPLOYEEID, GRADE, COUNT('EMPLOYEEID') CNT, ROUND(SUM(Cast(GRADE As int)) / COUNT('EMPLOYEEID'), 2) CAL, AVG(Cast(GRADE As int)) AVGGRADE
From TRAINING
GROUP BY EMPLOYEEID, GRADE
Order By 'AVGGRADE' DESC;


EMPLOYEEID
GRADE
CNT
CAL
AVGGRADE


31
4
1
4
4

24
5
1
5
5

33
4
1
4
4

26
3
1
3
3

27
4
2
4
4

31
3
1
3
3

32
3
1
3
3

30
5
1
5
5

35
3
1
3
3

34
5
1
5
5

24
2
1
2
2

31
2
1
2
2

33
5
1
5
5
EMPLOYEEID	GRADE	CNT	CAL	AVGGRADE
31	4	1	4	4
24	5	1	5	5
33	4	1	4	4
26	3	1	3	3
27	4	2	4	4
31	3	1	3	3
32	3	1	3	3
30	5	1	5	5
35	3	1	3	3
34	5	1	5	5
24	2	1	2	2
31	2	1	2	2
33	5	1	5	5
EMPLOYEEID	GRADE	CNT	CAL	AVGGRADE
31	4	1	4	4
24	5	1	5	5
33	4	1	4	4
26	3	1	3	3
27	4	2	4	4
31	3	1	3	3
32	3	1	3	3
30	5	1	5	5
35	3	1	3	3
34	5	1	5	5
24	2	1	2	2
31	2	1	2	2
33	5	1	5	5