// rz1.sql


connect baza1; 


create table zesp2
(Nazwazesp char(15),
Nrz smallint not null,
Nrpkz smallint,
primary key (Nrz));

create unique index zesp2_Nrz on zesp2 (Nrz);

create table prac2
(Nrp smallint not null,
Kobieta char(1),
Data_ur timestamp not null,
Nazwisko char(15) not null,
Nrz smallint not null,
primary key (Nrp) );

create index prac2_Nrz on prac2 (Nrz);

create unique index prac2_Nrp on prac2 (Nrp);

insert into zesp2 select * from zespol; 

insert into prac2 select * from pracowni;

select * from prac2;

select * from zesp2;

alter table zesp2
foreign key fk_prac2 (nrpkz) references prac2 
on delete restrict;

alter table prac2
foreign key fk_zesp2 (nrz) references zesp2 
on delete set null;

select * from prac2;
select * from zesp2;

insert into zesp2 values ('USM', 7, 33);

insert into zesp2 values ('USM2', 8, 1);

delete from prac2 where nrp=33;

grant connect to admin identified by admin;

connect baza1 admin/admin;

select * from zesp2;

connect baza1 sysadm/sysadm;

grant resource to admin;

select * from zesp2;

connect baza1 admin/admin;

select * from zesp2;

connect baza1 sysadm/sysadm;

grant dba to admin;

connect baza1 admin/admin;
select * from sysadm.zesp2;

use 1;
use 2; 
use 3;

connect baza1 sysadm/sysadm;

select * from sysuserauth;

grant connect to prac identified by prac;

connect baza1 admin/admin;
grant connect to prac1 identified by prac1;
use 1;
grant connect to stud identified by stud;

connect baza1 admin/admin;
grant select on zesp2 to prac;

connect baza1 sysadm/sysadm;
grant select on zesp2 to prac;

select * from zesp2;
grant select on sysadm.zesp2 to prac;
disconnect all;
connect baza1 admin/admin;

create synonym zesp2 for sysadm.zesp2;
connect baza1 prac/prac;

select * from sysadm.zesp2;

create table Tem1 (nazwateamt char(30), data_odb timestamp, nrt integer not null,
nrpkt smallint not null);
