dbd_ex1

--b
SELECT  u.USERID, u.USERNAME, SUM(o.SUM) AS Total
FROM USERS u, ACCOUNTS a, OPERATIONS o
WHERE u.USERID = a.USERID AND
  a.ACC_ID = o.ACC_ID
GROUP BY  u.USERID, u.USERNAME
HAVING SUM(o.SUM) > 100
ORDER BY SUM(o.SUM) DESC;

--c)
CREATE VIEW accountsView AS
SELECT u.USERID, u.USERNAME, a.ACC_ID,count(o.SUM) as OPS
FROM USERS u, ACCOUNTS a, OPERATIONS o
WHERE u.USERID = a.USERID AND
  a.ACC_ID = o.ACC_ID
GROUP BY u.USERID, u.USERNAME, a.ACC_ID
ORDER BY count(o.SUM) DESC;

CREATE VIEW maxOPSview AS
select userid,  username, max(ops) as maxops
from accountsview
group by userid, username
order by max(ops) desc;

select m.USERID, m.username, m.MAXOPS
from maxopsview m, accounts a
WHERE m.USERID = a.ACC_ID;


--d)
CREATE VIEW noOperationAcc AS
SELECT distinct a.acc_id
  FROM accounts a, operations o
  WHERE a.ACC_ID not in
  (
    SELECT acc_id
    FROM OPERATIONS
  );
DELETE FROM accounts
where ACC_ID in (SELECT acc_id FROM noOperationAcc);


DAR TRB STERS SI DIN USERS:
--d)
CREATE VIEW noOperationAcc AS
SELECT distinct a.acc_id, a.USERID
  FROM accounts a, operations o
  WHERE a.ACC_ID not in
  (
    SELECT acc_id
    FROM OPERATIONS
  );
DELETE FROM accounts
where ACC_ID in (SELECT acc_id FROM noOperationAcc);

DELETE FROM users
WHERE USERID in (SELECT userid FROM noOperationAcc);

SELECT * from NOOPERATIONACC;