1.


CREATE TABLE smer(
sifra_smera int not null,
oznaka_smera nvarchar(20)not null,
naziv_smera nvarchar(100)not null
)

CREATE TABLE Student(
JMBG nvarchar (13) not null,
Prezime nvarchar (50) not null,
Ime nvarchar (30) not null,
Datum_Rodjenja datetime null,
Godina_Studija smallint null,
Sifra_Smera int not null 
)

ALTER TABLE Smer
ADD CONSTRAINT PK_SM PRIMARY KEY (Sifra_smera)

ALTER TABLE Student
ADD CONSTRAINT PK_ST PRIMARY KEY (JMBG)

ALTER TABLE Student
ADD CONSTRAINT FK_StudentSmer FOREIGN KEY  (Sifra_Smera)
REFERENCES Smer (sifra_smera)
ON UPDATE CASCADE
ON DELETE NO ACTION


2.

INSERT INTO Smer
VALUES(11111, 'Broj','IT') 

INSERT INTO Student
VALUES(0612990855006,'Peric','Petar','4/3/1985','4', '11111')

UPDATE smer  
SET naziv_smera='IZ'  WHERE sifra_smera='11111' 

DELETE FROM Student WHERE sifra_smera=11111


3. 

/* KREIRAMO POGLED */

CREATE VIEW BrojStudenataZaSveSmerove
AS
SELECT smer.oznaka_smera as [Oznaka smera], smer.naziv_smera as [Naziv smera], COUNT(Student.JMBG) as [Broj studenata na smeru]
from Student inner join smer on smer.sifra_smera=Student.Sifra_Smera
GROUP BY smer.oznaka_smera, smer.naziv_smera


/* Pozivamo pogled */

select * from "BrojStudenataZaSveSmerove"


/* KREIRANJE PROCEDURE TIPA SELECT 
CREATE PROCEDURE PrikaziStudenteNaSmeru
@nazivsmera nvarchar
AS
SELECT Student.Prezime, Student.Ime 
FROM Student inner join smer on Student.Sifra_Smera=smer.sifra_smera
WHERE smer.naziv_smera=@nazivsmera  

/* pozivamo proceduru */

EXECUTE PrikaziStudenteNaSmeru 'profesori'


/* kreiranje procedure tipa insert into - NE ZABORAVI ZAREZE KOD NAVODJENJA PROMENLJIVIH

CREATE PROCEDURE DodajSmer
@sif int,
@ozn nvarchar,
@naz nvarchar
AS
INSERT INTO smer VALUES (@sif, @ozn, @naz)

/ * TRIGER ZA ID+1

CREATE TRIGGER OdrediNoviIDSmera ON smer
FOR INSERT
AS
DECLARE @maxIDSmera INT
SET @maxIDSmera =(SELECT MAX(sifra_smera) FROM smer)+1
UPDATE smer
SET sifra_smera=@maxIDSmera
WHERE sifra_smera=0;