An
SQL “equivalent” of the SBQL query
(Person as p). (p.name,
sum(bag(0, ((Student)p).scholarship, ((Emp)p).sal)))
by
Krzysztof Stencel,
Declarations
of tables
CREATE TABLE Dept (
id INTEGER NOT
NULL PRIMARY KEY,
name VARCHAR(32) NULL
);
CREATE TABLE Person (
id INTEGER NOT
NULL PRIMARY KEY,
name VARCHAR(32) NULL
);
CREATE TABLE Emp (
id INTEGER NOT
NULL PRIMARY KEY,
Dept_id INTEGER NOT NULL REFERENCES Dept,
Person_id INTEGER NOT NULL REFERENCES Person,
sal INTEGER NULL
);
CREATE
id INTEGER NOT
NULL,
name VARCHAR(32) NULL,
PRIMARY KEY(id)
);
CREATE TABLE Student (
id INTEGER NOT
NULL PRIMARY KEY,
University_id
Person_id INTEGER NOT NULL REFERENCES Person,
scholarship INTEGER NULL
);
Filling
in the database
INSERT INTO Dept VALUES (1, 'Marketing');
INSERT INTO Dept VALUES (2, 'HR');
INSERT INTO Dept VALUES (3, 'Zaopatrzenie');
INSERT INTO University VALUES (1, 'UW');
INSERT INTO Person VALUES (1, 'Jan Kowalski');
INSERT INTO Emp VALUES (1, 1, 1, 1000);
INSERT INTO Emp VALUES (2, 2, 1, 500);
INSERT INTO Person VALUES (2, 'Zbyszek Nowak');
INSERT INTO Emp VALUES (3, 2, 2, 500);
INSERT INTO Emp VALUES (4, 3, 2, 800);
INSERT INTO Student VALUES (1, 1, 2, 300);
INSERT INTO Person VALUES (3, 'Romek Abacki');
INSERT INTO Emp VALUES (5, 3, 3, 600);
INSERT INTO Student VALUES (2, 1, 3, 200);
INSERT INTO Student VALUES (3, 1, 3, 100);
INSERT INTO Person VALUES (4, 'Anna Cabacka');
INSERT INTO Emp VALUES (6, 2, 4, 100);
INSERT INTO Emp VALUES (7, 3, 4, 100);
INSERT INTO Student VALUES (4, 1, 4, 100);
INSERT INTO Student VALUES (5, 1, 4, 100);
INSERT INTO Person VALUES (5, 'Szczepan Babacki');
INSERT INTO Student VALUES (6, 1, 5, 50);
INSERT INTO Person VALUES (6, 'Justyna Dabrowka');
INSERT INTO Person VALUES (7, 'Krzysztof Trzmiel');
INSERT INTO Student VALUES (7, 1, 7, NULL);
INSERT INTO Student VALUES (8, 1, 7, NULL);
INSERT INTO Student VALUES (9, 1, 7, 10);
INSERT INTO Person VALUES (8, 'Jacek Szeliga');
INSERT INTO Student VALUES (10, 1, 8, NULL);
INSERT INTO Emp VALUES (8, 3, 8, NULL);
INSERT INTO Emp VALUES (9, 2, 8, NULL);
INSERT INTO Person VALUES (9, 'Karolina Dubrovnik');
INSERT INTO Student VALUES (11, 1, 9, NULL);
INSERT INTO Emp VALUES (10, 3, 9, NULL);
INSERT INTO Emp VALUES (11, 2, 9, NULL);
INSERT INTO Student VALUES (12, 1, 9, 100);
INSERT INTO Emp VALUES (12, 3, 9, 100);
COMMIT;
SQL
query
SELECT name, SUM(income)
FROM Person pe,
(SELECT Person_id pid, scholarship AS income FROM
Student
UNION ALL
SELECT Person_id pid, sal AS income FROM Emp
UNION ALL
SELECT id pid, 0 AS income FROM Person) v
WHERE v.pid = pe.id
GROUP BY v.pid, pe.name;
Query
result
NAME
SUM(INCOME)
-------------------------------- -----------
Karolina
Dubrovnik
200
Jan
Kowalski
1500
Justyna
Dabrowka
0
Romek
Abacki
900
Zbyszek
Nowak
1600
Szczepan
Babacki
50
Anna
Cabacka
400
Jacek Szeliga
0
Krzysztof
Trzmiel
10
9 rows selected.
Last update: January 07, 2010.