sap_task2

CREATE TABLE employees (
  id INT,
  name VARCHAR(100),
  department VARCHAR(100),
  salary INT,
  employ_id INT
);
INSERT INTO employees (id, name, department, salary, employ_id)
VALUES
    (1001,  'Ivanov',   'SAPIENS',  100000, 1001),
    (1002,  'Petrov',   'SAPIENS',  100000, 1001),
    (1003,  'Sidorov',  'TECHNOLOGIES', 130000, 1001),
    (1004,  'Korotkov', 'TECHNOLOGIES', 120000, 1001),
    (1005,  'Filev',    'SAPIENS',  90000,  1001),
    (1006,  'Smirnov',  'REVOLT',   125000, 1001),
    (1007,  'Godov',    'REVOLT',   125000, 1001);

CREATE TABLE business_trip (
  id INT,
  employ_id INT,
  dep_date DATE,
  arriv_date DATE,
  trav_expens INT
);
INSERT INTO business_trip (id, employ_id, dep_date, arriv_date, trav_expens)
VALUES
    (2001,  1001,   '2014-01-01',   '2014-01-10',   2000),
    (2002,  1001,   '2015-01-01',   '2015-01-10',   3000),
    (2003,  1001,   '2015-02-01',   '2015-03-10',   1500),
    (2004,  1001,   '2015-06-01',   '2015-07-10',   2900),
    (2005,  1001,   '2015-07-01',   '2015-08-10',   3600),
    (2006,  1001,   '2016-01-01',   '2016-01-10',   5000),
    (2007,  1001,   '2016-04-01',   '2016-06-10',   1000),
    (2008,  1001,   '2016-07-01',   '2016-08-10',   5700),
    (2009,  1001,   '2017-01-01',   '2017-01-10',   2100),
    (2010,  1001,   '2017-03-01',   '2017-04-10',   5000);

SELECT SUM(b.trav_expens) AS SUM_TRAV_EXPENS
FROM employees AS e LEFT JOIN business_trip AS b
ON e.employ_id = b.employ_id
WHERE e.salary > 90000 AND b.dep_date >= '2015-06-01';