ashot.sql

-- Employees and departments
CREATE TABLE EMPLOYEES(ID INTEGER, NAME VARCHAR, CHIEF_ID INTEGER, SALARY INTEGER, DEPARTMENT_ID INTEGER);
CREATE TABLE DEPARTMENTS(ID INTEGER, NAME VARCHAR);

INSERT INTO DEPARTMENTS VALUES (1, "DEP 1"), (2, "DEP 2"), (3, "DEP 3");
INSERT INTO EMPLOYEES VALUES
   (1,"Աշոտ",2,3000,1),
   (2,"Ophir",NULL,2500,1),
   (3,"Армен",1,4000,2);


-- Q1
-- IDs of departments that have the maximum combined salary of their employees
SELECT DEPARTMENT_ID
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING SUM(SALARY)=MAX((SELECT SUM(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID));


-- Q2
-- IDs of departments with at most 3 employees
SELECT DEPARTMENTS.ID
FROM DEPARTMENTS -- We cannot select directly on employees, or we wouldn't get the IDs of the deprtments that have no employees
LEFT JOIN EMPLOYEES ON EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.ID
GROUP BY DEPARTMENTS.ID
HAVING COUNT(EMPLOYEES.ID)<=3;

-- Q3
-- employees who earn more then their boss
SELECT EMPLOYEES.NAME
FROM EMPLOYEES
INNER JOIN EMPLOYEES AS BOSS
    ON BOSS.ID = EMPLOYEES.CHIEF_ID
WHERE EMPLOYEES.SALARY>BOSS.SALARY;


-------------------------------------------------------
CREATE TABLE call(suscriber_name VARCHAR, event_date VARCHAR, event_cnt INT);
INSERT INTO call VALUES ("Աշոտ", "2018-01-01", 5);
INSERT INTO call VALUES ("Աշոտ", "2018-01-02", 5);
INSERT INTO call VALUES ("Աշոտ", "2018-01-03", 6);
INSERT INTO call VALUES ("Офирик", "2018-01-03", 6);
INSERT INTO call VALUES ("Офирик", "2018-01-04", 7);

-- All suscribers, with the oldest date when they had their call with the
-- maximum number of actions and the most recent date when they had their call with the
-- minimum number of actions.
SELECT
    root.suscriber_name,
    (SELECT MIN(event_date) FROM call WHERE event_cnt=
        (SELECT MAX(event_cnt) FROM call WHERE suscriber_name=root.suscriber_name)),
    (SELECT MAX(event_date) FROM call WHERE event_cnt=(
        SELECT MIN(event_cnt) FROM call WHERE suscriber_name=root.suscriber_name)),
    SUM(event_cnt)
FROM call root
GROUP BY suscriber_name;