drop table testik cascade constraints;
drop table small cascade constraints;
drop synonym zen;

commit

create table small
(
	id	number		constraint pk_small primary key,
	kwak	varchar2(10)
);

create table testik
(
	id	number 		constraint pk_testik primary key,
	name	varchar2(20)	constraint nn_name not null
				constraint upper_name check (name = upper(name)),
	val 	number		constraint ch_val check (val>500),
	x	number		constraint nn_x not null
				constraint fk_small references small(id)
);

alter table testik add ook number;

commit

create index index1 on testik (name, val);

create synonym zen for testik;

insert into small values (1, 'kwak');
insert into small values (2, 'ook');
insert into small values (3, 'eek');
insert into small values (4, 'Brum');

insert into testik values (1, 'FRANTA', 650, 1, 0);
insert into testik values (2, 'ARNOST', 750, 2, 0);
insert into testik values (3, 'ARNOST', 950, 2, 0);
insert into testik values (4, 'ARNOST', 1000, 3, 1);
insert into testik values (5, 'ZAPALKA', 650, 4, 2);
insert into testik values (6, 'ZAPALKA', 650, 4, 3);

select avg(val), max(x) from zen;

select count(*), name from zen group by name;
select count(*), name from zen group by name having count(*)>2;

select testik.id, testik.name, small.kwak from testik, small where testik.x = small.id;

commit

savepoint

alter table testik add eek number;

rollback to savepoint

select * from testik;


declare cursor cur1 is select id, name from zen;

begin
open cur1;
close cur1;
end;
/