sdtgjsdfh

create table Org0 (
oid integer primary key,
title varchar(20)
);

create table Bill0 (
bid integer primary key,
bdate timestamp,
oid integer references Org0 (oid)
);

create table BillContent0 (
beid integer primary key,
bid integer references Bill0 (bid),
good varchar(20),
quantity float,
price money
);

create table Payment0 (
pid integer primary key,
bid integer references Bill0 (bid),
pdate timestamp,
psum money
);
insert into OrgX values (1, 'SAMSUNG');
insert into OrgX values (2, 'APPLE');
insert into BillX values (1, '01.01.01',1);
insert into BillX values (2, '02.02.02',2);
insert into BillX values (3, '03.03.03',1);
insert into BillX values (4, '04.04.04',2);
insert into BillX values (5, '05.05.05',1);


insert into mOrg values (1, 'QWERTY');
insert into mOrg values (2, 'ABC');

insert into BillcontentX values (1, 1 , 1, 1000, 5000);
insert into BillcontentX values (2, 2 , 2 , 500, 3500);
insert into BillcontentX values (3, 3 , 3, 15, 1500);
insert into BillcontentX values (4, 4 , 4, 60, 3000);
insert into BillcontentX values (5, 5 , 5, 10, 5000);

insert into PaymentX values (1,1,'01.01.01',5000);
insert into PaymentX values (2,2,'02.02.02',3500);
insert into PaymentX values (3,3,'03.03.03',1500);
insert into PaymentX values (4,4,'04.04.04',0);
insert into PaymentX values (5,5,'05.05.05',700);


select good, quantity, price, quantity*price as total from billcontent9 where bid=3

select bid, title from Bill9 inner join Org9 using (oid)

select bid, title as org, quantity*price as total from Bill9 inner join Org9 using (oid) inner join billcontent9 using(bid)
select good, quantity, price, quantity*price as total from billcontent9 where bid=3

select bid, title from Bill9 inner join Org9 using (oid)

select bid, title as org, quantity*price as total from Bill9 inner join Org9 using (oid) inner join billcontent9 using(bid)


insert into GoodsX values(2,'marker', 7, 'kancelyaria', 'box')

insert into GoodsX values(3,'table', 100, 'mebel', 'scotch')

insert into GoodsX values(4,'chair', 50, 'mebel', 'scotch')

insert into GoodsX values(5,'TV', 500, 'entertainment', 'bigbox')



SELECT avg(CAST(price AS Numeric(10,2))) FROM GoodsX;

select type, count(good) from GoodsX group by type;


	UPDATE BillX
	   SET sid = 1
	   WHERE bid = 1;

	UPDATE BillX
	   SET sid = 2
	   WHERE bid = 2;

	UPDATE BillX
	   SET sid = 3
	   WHERE bid = 3;


	UPDATE BillX
	   SET sid = 1
	   WHERE bid = 4;

	UPDATE BillX
	   SET sid = 3
	   WHERE bid = 5;

	   select GoodsX.type, SailorX.name, sum(BillContentX.price) as SUM from SailorX inner join BillX using (sid) inner join BillContentX using(bid) inner join GoodsX using(gid) group by GoodsX.type, SailorX.name
http://nto.immpu.sgu.ru/z3.pdf