create database Radnici

go

use Radnici

create table Radnici
(
	id int not null,
	ime nvarchar(20) not null,
	prezime nvarchar(20) not null,
	jmbg char(13) null
)

insert into Radnici (ime,id, prezime, jmbg) values 
					('ime2', 2, 'prezime3', '6165465465484')

insert into Radnici values (3, 'ime3', 'prezime3', null)

select *
from radnici

alter table Radnici drop column id

alter table Radnici add id int identity(2, 3)

insert into Radnici values ('ime3', 'prezime3', null)

insert into Radnici values ('ime3', 'prezime3', null)

alter table Radnici add constraint pk_radnici primary key (id)


create table Angazovanje
(
	id int primary key,
	id_radnika int not null,
	id_sektora int not null
)

select * from radnici
insert into Angazovanje values (1, 2, 1)
insert into Angazovanje values (2, 5, 2)

select *
from Radnici r join Angazovanje a on r.id = a.id_radnika

insert into Angazovanje values (3, 8, 2)
select * from Angazovanje

	alter table Angazovanje 
	add constraint fk_radnici 
	foreign key (id_radnika)
	references Radnici(id)

delete from Angazovanje where 1 = 1

select * from Radnici

insert into Radnici
select Imen, 'prezime', CONCAT('1243452-', Snast) 
from studije.dbo.Nastavnici


update radnici
set prezime = 'Nepoznato'
where id > 14

select * from Radnici

select getdate() datum

go
create function prosek(@brojIndeksa int, @godinaUpisa int)
returns float
as
begin
		declare @p float
		
		select @p = avg(ocena * 1.0)
		from prijave
		where indeks = @brojIndeksa and upisan = @godinaUpisa

		return @p

end
go

select dbo.prosek(3, 2001)

select imes, dbo.prosek(indeks, upisan)
from studenti s
where dbo.prosek(indeks, upisan) > 9

create function predmeti_na_smeru(@smer int)
returns table
as 
	return (
				select spred, semestar
				from Planst
				where ssmer = @smer
			)

select getdate()

select spred 
from dbo.predmeti_na_smeru(1)


create procedure unos_prijave(@brojInd int, @godinaUpisa int, @spred int, @snast int)
as
begin

	insert into Prijave(Indeks, Upisan, Spred, Snast, Datump)
	values (@brojInd, @godinaUpisa, @spred, @snast, getdate())

end

exec unos_prijave 2, 2002, 1, 3

select * from prijave



create procedure povratne_vrednosti(@prva int output, @druga int output, @treca int)
as
begin
	set @prva = @treca - 10
	set @druga = @treca + 10
end

declare @izlaz_prva int
declare @druga int

exec povratne_vrednosti @prva = @izlaz_prva output, @druga = @druga output, @treca = 50

select  @izlaz_prva, @druga
print concat('@izlaz_prva = ',  @izlaz_prva)


--1. Kreirati proceduru koja vraca prosek i broj nepolozenih ispita za odredjenog studenta

alter procedure prosek_nepolozeni_ispiti(
					@brojInd int, @godUpisa int, @prosek decimal(6,2) output, @brojNepolozenih int output)
as
begin
		select @prosek = avg(ocena * 1.0)
		from prijave
		where indeks = @brojInd and upisan = @godUpisa

		select @brojNepolozenih = count(*)
		from studenti s join planst ps on s.ssmer = ps.ssmer 
		left join prijave p  
		on s.indeks = p.indeks and s.upisan = p.upisan and p.ocena > 5 and ps.spred = p.spred
		where p.indeks is null and s.indeks = @brojInd and s.upisan = @godUpisa


end

declare @prosek decimal(6,2)
declare @br_nepolozenih int
exec prosek_nepolozeni_ispiti @brojInd = 2, @godUpisa = 2002, @prosek = @prosek output, @brojNepolozenih = @br_nepolozenih output

select 2, 2002, @prosek as prosek, @br_nepolozenih as 'Broj nepolozenih'


--1. Kreirati proceduru koja vraca prosek i broj nepolozenih ispita za odredjenog studenta

		

		select *
		from prijave 
		where indeks = 2 and upisan = 2002

		delete from prijave where spred = 1 and indeks = 2 and upisan = 2002

		
		select * from prijave

--2. Napisati proceduru za upis ocene
create procedure polozen_ispit(@brInd int, @upisan int, @spred int, @snast int, @ocena int)
as
begin

	

	if exists 
	(
		select *
		from prijave
		where  ocena is null and indeks = @brInd and upisan = @upisan and spred = @spred and snast = @snast
	)
	begin
		update prijave set ocena = @ocena, Datump = getdate()
		where ocena is null and indeks = @brInd and upisan = @upisan and spred = @spred and snast = @snast
	end
	else
	begin
		insert into prijave values (@spred, @brInd, @upisan, @snast, GETDATE(), @ocena)
	end

end

select count(*)
	from prijave
	where  ocena is null and indeks = 1 and upisan = 2003 and spred = 1 and snast = 1

insert into prijave values (1, 8, 2003, 1, GETDATE(), null)
select * from prijave

--(@brInd int, @upisan int, @spred int, @snast int, @ocena int)
exec polozen_ispit 8, 2003, 1, 1, 10
select * from prijave