Create table clients (id number primary key, name varchar2(255))Table created.
Create table invoices (id number primary key, clientid number, quantity number, unitprice number, FOREIGN KEY (clientid) REFERENCES Clients(id))Table created.
Create table payments (id number primary key, clientid number, payment number, FOREIGN KEY (clientid) REFERENCES Clients(id))Table created.
insert into clients values (1, 'client1')1 row(s) inserted.
insert into clients values (2, 'client2')1 row(s) inserted.
insert into clients values (3, 'client3')1 row(s) inserted.
insert into clients values (4, 'client4')1 row(s) inserted.
insert into invoices values (1,1,10,10)1 row(s) inserted.
insert into invoices values (2,1,15,15)1 row(s) inserted.
insert into invoices values (3,2,20,20)1 row(s) inserted.
insert into invoices values (4,2,30,30)1 row(s) inserted.
insert into invoices values (5,3,3,40)1 row(s) inserted.
insert into invoices values (6,4,1,100)1 row(s) inserted.
insert into payments values (1,1,90)1 row(s) inserted.
insert into payments values (2,1,215)1 row(s) inserted.
insert into payments values (3,2,390)1 row(s) inserted.
insert into payments values (4,2,890)1 row(s) inserted.
insert into payments values (5,3,110)1 row(s) inserted.
select
c.id,
c.name,
sum(i.quantity*i.unitprice) invAmt
from
clients c,
invoices i
where c.id = i.clientid
group by c.id,c.name| ID | NAME | INVAMT | 2 | client2 | 1300 | 4 | client4 | 100 | 1 | client1 | 325 | 3 | client3 | 120 |
|---|
select
c.id,
c.name,
sum(p.payment) paidAmt
from
clients c,
payments p
where
c.id=p.clientid
group by c.id, c.name| ID | NAME | PAIDAMT | 2 | client2 | 1280 | 1 | client1 | 305 | 3 | client3 | 110 |
|---|
select
c.id,
c.name,
i.id,
i.clientid,
(i.quantity*i.unitprice) invAmt
from
clients c left outer join invoices i on c.id=i.clientid| ID | NAME | ID | CLIENTID | INVAMT | 1 | client1 | 1 | 1 | 100 | 1 | client1 | 2 | 1 | 225 | 2 | client2 | 3 | 2 | 400 | 2 | client2 | 4 | 2 | 900 | 3 | client3 | 5 | 3 | 120 | 4 | client4 | 6 | 4 | 100 |
|---|
select
c.id,
c.name,
p.payment
from
clients c left outer join payments p on c.id=p.clientid| ID | NAME | PAYMENT | 1 | client1 | 90 | 1 | client1 | 215 | 2 | client2 | 390 | 2 | client2 | 890 | 3 | client3 | 110 | 4 | client4 | - |
|---|
SELECT
c.id,
c.name,
p.payment,
(i.quantity * i.unitprice) AS invAmt
FROM
clients c
JOIN
invoices i ON c.id = i.clientid
JOIN
payments p ON c.id = p.clientid| ID | NAME | PAYMENT | INVAMT | 1 | client1 | 90 | 100 | 1 | client1 | 215 | 100 | 1 | client1 | 90 | 225 | 1 | client1 | 215 | 225 | 2 | client2 | 390 | 400 | 2 | client2 | 890 | 400 | 2 | client2 | 390 | 900 | 2 | client2 | 890 | 900 | 3 | client3 | 110 | 120 |
|---|
with ci as (
select
c.id clientid,
c.name,
sum(i.quantity*i.unitprice) invoicedAmt
from
clients c,
invoices i
where c.id = i.clientid
group by c.id,c.name
),
cp as (
select
c.id clientid,
c.name,
sum(p.payment) paidAmt
from
clients c left outer join payments p on c.id=p.clientid
group by c.id, c.name
)
select
c.id,
c.name,
ci.invoicedAmt,
cp.paidAmt
from
clients c
LEFT OUTER JOIN ci ON c.id=ci.clientid
LEFT OUTER JOIN cp ON c.id=cp.clientid| ID | NAME | INVOICEDAMT | PAIDAMT | 1 | client1 | 325 | 305 | 2 | client2 | 1300 | 1280 | 4 | client4 | 100 | - | 3 | client3 | 120 | 110 |
|---|