Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE employee
(
department VARCHAR(50) NOT NULL,
salary INT 
);
INSERT INTO employee
VALUES ('Sales', 5000);
1 rows affected

INSERT INTO employee
VALUES ('HR', 6000);
1 rows affected

INSERT INTO employee
VALUES ('IT', 7500);
1 rows affected

INSERT INTO employee
VALUES ('Marketing', NULL);
1 rows affected

INSERT INTO employee
VALUES ('Finance', 5500);
1 rows affected

INSERT INTO employee
VALUES ('Sales', 8000);
1 rows affected

SELECT *
FROM employee


DEPARTMENT
SALARY


Sales
5000

HR
6000

IT
7500

Marketing
null

Finance
5500

Sales
8000


CREATE TABLE Location
(
LocationName VARCHAR(50) NOT NULL,
department VARCHAR(50) NOT NULL
);
INSERT INTO Location
VALUES ('America', 'Sales');
1 rows affected

INSERT INTO Location
VALUES ('Japan', 'Finance');
1 rows affected

SELECT *
FROM Location


LOCATIONNAME
DEPARTMENT


America
Sales

Japan
Finance


SELECT *
FROM Location
LEFT JOIN employee ON employee.department=Location.department


LOCATIONNAME
DEPARTMENT
DEPARTMENT
SALARY


America
Sales
Sales
5000

Japan
Finance
Finance
5500

America
Sales
Sales
8000


SELECT COALESCE (LocationName, '總計') AS Department,SUM(salary) AS Salary_Sum
FROM Location
LEFT JOIN employee ON employee.department=Location.department
GROUP BY ROLLUP (LocationName)


DEPARTMENT
SALARY_SUM


America
13000

Japan
5500

總計
18500

LOCATIONNAME	DEPARTMENT	DEPARTMENT	SALARY
America	Sales	Sales	5000
Japan	Finance	Finance	5500
America	Sales	Sales	8000