dbd_accounts

-- Insert 2 users
insert into users values (1,'User1');
insert into users values (2,'User2');

-- Creates 3 accounts for the first user
insert into accounts values (1,1,2000);
insert into accounts values (2,1,2000);
insert into accounts values (3,1,2000);

-- Creates 2 acoounts for the second user
insert into accounts values (4,2,3000);
insert into accounts values (5,2,3000);

-- Some operations
insert into operations values (1,1,1,1,500,null);
insert into operations values (2,1,1,1,300,null);
insert into operations values (3,1,1,3,300,4);

-- b
set serveroutput on
begin
  -- Deposit to account 1
  operation(1,1,1,500,null);
  -- Withdraw from account 1
  operation(1,1,2,500,null);
  -- Trying to deposit to a nonexistent account.
  operation(1,9,1,500,null);
  -- Transfer money
  operation(1,1,3,200,4);
  operation(1,2,1,200,null);
end;

-- Selects the number of operations for each account
select a.accountid, count(op.opid)
from accounts a, operations op
where a.accountid=op.accountid or a.accountid=op.toaid
group by a.accountid;

-- Selects the users and the operations for each accounts, if there has been
create view UsersOp (userid,accountid, noop) as
select a.userid, a.accountid, count(op.opid) as noop
from accounts a, operations op
where a.accountid=op.accountid or a.accountid=op.toaid
group by a.userid, a.accountid
order by a.userid, noop desc;

create view AccOp (accountid, noop) as
select a.accountid, count(op.opid) as noop
from accounts a, operations op
where a.accountid=op.accountid or a.accountid=op.toaid
group by a.accountid
order by noop desc;

select uo.userid, max(uo.noop)
from usersop uo
group by uo.userid;

select * from usersop
where (userid,noop) in (select uo.userid, max(uo.noop)
group by uo.userid;

-- c
set serveroutput on
begin
  listclients();
end;

-- Selects the entire amount for all users
create view usersamount (userid, total) as
select u.userid, sum(a.sum) as total
from users u, accounts a
where u.userid=a.userid
group by u.userid
order by total desc;

-- d
set serveroutput on
begin
  -- Printing all users with a total greater than 200
  richest(200);
  richest(1000000);
end;

-- b
set serveroutput on
begin
  operation(1,1,2,500000,null);
end;