drop table if exists student;
drop table if exists akademik;

create table `akademik` (
  `id_akademik` int(11) not null,
  `nazwa` varchar(40) default null,
  primary key (`id_akademik`)
) ENGINE=InnoDB default CHARSET=latin1;

create table `student` (
  `id_student` int(10) unsigned not null AUTO_INCREMENT,
  `imie_student` varchar(40) collate utf8_bin default null,
  `nazwisko_student` varchar(40) collate utf8_bin default null,
  `id_akademik` int(11) default null,
  primary key (`id_student`),
  foreign key (id_akademik) references akademik (id_akademik) on delete set null on update cascade

) ENGINE=InnoDB default CHARSET=utf8 collate=utf8_bin;
  -- jezeli on delete cascade
  -- jezeli on delete set null
  -- jezeli on delete restrict
  -- sprawdzic opcje!

insert into akademik values(1,"Bartek");
insert into akademik values(2,"Zosia");

insert into student values(1,"jan","kowalski",1);
insert into student values(2,"piotrek","labedz",1);
insert into student values(3,"tomasz","kowal",2);
insert into student values(4,"dawid","kluska",2);

select id_student,imie_student,nazwisko_student,concat(nazwa," id ",akademik.id_akademik) as 'akademik' from student left join akademik on student.id_akademik=akademik.id_akademik;

delete from akademik where id_akademik = 1;
select id_student,imie_student,nazwisko_student,concat(nazwa," id ",akademik.id_akademik) as 'akademik' from student left join akademik on student.id_akademik=akademik.id_akademik;
delete from student where id_student = 2;
select id_student,imie_student,nazwisko_student,concat(nazwa," id ",akademik.id_akademik) as 'akademik' from student left join akademik on student.id_akademik=akademik.id_akademik;

update akademik set id_akademik = 4 where id_akademik = 2;
select id_student,imie_student,nazwisko_student,concat(nazwa," id ",akademik.id_akademik) as 'akademik' from student left join akademik on student.id_akademik=akademik.id_akademik;