/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     11/19/2014 9:47:13 PM                        */
/*==============================================================*/

/* Brisanje baze ako postoji */

use master;
go

drop database PPortal;
go

/* Brisanje baze ako postoji   -  KRAJ  */


/* Generisanje baze: PPortal*/

create database PPortal;
go

use PPortal;
go

/* Generisanje baze: PPortal  -  KRAJ */


/*==============================================================*/
/* Table: ZEMLJA                                                */
/*==============================================================*/
create table ZEMLJA (
   SIF_ZEMLJE           int,
   DRZAVA               varchar(30)          not null,
   OZNAKA_DRZAVE        char(5)              null,
   constraint PK_ZEMLJA primary key nonclustered (SIF_ZEMLJE)
)
go

/*==============================================================*/
/* Table: MESTO                                                 */
/*==============================================================*/
create table MESTO (
   SIF_MESTA            int,
   SIF_ZEMLJE           int              not null foreign key references ZEMLJA (SIF_ZEMLJE),
   NAZIV_MESTA          varchar(30)          not null,
   POST_BROJ            numeric(5)           not null,
   constraint PK_MESTO primary key nonclustered (SIF_MESTA)
)
go

/*==============================================================*/
/* Table: ZANR                                                  */
/*==============================================================*/
create table ZANR (
   SIF_ZANRA            int,
   ZANR                 varchar(50)          not null,
   constraint PK_ZANR primary key nonclustered (SIF_ZANRA)
)
go

/*==============================================================*/
/* Table: ULOGA                                                 */
/*==============================================================*/
create table ULOGA (
   SIF_ULOGE            int,
   ULOGA                varchar(30)          null,
   constraint PK_ULOGA primary key nonclustered (SIF_ULOGE)
)
go

/*==============================================================*/
/* Table: POZORISTE                                             */
/*==============================================================*/
create table POZORISTE (
   SIF_POZORISTA        int,
   SIF_MESTA            int              not null foreign key references MESTO (SIF_MESTA),
   NAZIV_POZORISTA      varchar(100)         not null,
   P_POSTANSKI_BROJ     numeric(5)           not null,
   P_MESTO              varchar(30)          not null,
   P_ULICA_BROJ         varchar(30)          null,
   P_TELEFON            varchar(20)          null,
   P_WWW_ADRESA         varchar(100)         null,
   P_EMAIL              varchar(50)          null,
   P_IZ_VOJVODINE       bit                  not null,
   P_AMATERSKO          bit                  not null,
   constraint PK_POZORISTE primary key nonclustered (SIF_POZORISTA)
)
go

/*==============================================================*/
/* Table: LICE                                                  */
/*==============================================================*/
create table LICE (
   SIF_LICA             int,
   SIF_MESTA            int              not null foreign key references MESTO (SIF_MESTA),
   L_PREZIME            varchar(20)          not null,
   L_IME                varchar(20)          not null,
   L_BIOGRAFIJA         varchar(5000)        null,
   L_IZ_VOJVODINE       bit                  not null,
   L_AMATER             bit                  not null,
   L_AKTIVNO            bit                  not null,
   constraint PK_LICE primary key nonclustered (SIF_LICA)
)
go

/*==============================================================*/
/* Table: PROSTORIJA                                            */
/*==============================================================*/
create table PROSTORIJA (
   SIF_POZORISTA        int              not null foreign key references POZORISTE (SIF_POZORISTA),
   SIF_PROSTORIJE       int,
   NAZIV_PROSTORIJE     varchar(50)          not null,
   PR_BROJ_SEDISTA      numeric(3)           not null,
   constraint PK_PROSTORIJA primary key nonclustered (SIF_POZORISTA, SIF_PROSTORIJE)
)
go

/*==============================================================*/
/* Table: POZORISNO_DELO                                        */
/*==============================================================*/
create table POZORISNO_DELO (
   SIF_POZ_DELA         int,
   SIF_ZANRA            int              not null foreign key references ZANR (SIF_ZANRA),
   SIF_LICA_PREVOD      int               foreign key references LICE (SIF_LICA),
   NASLOV_POZ_DELA      varchar(50)          not null,
   POZ_DELO_BR_CINOVA   numeric(1)           not null,
   constraint PK_POZORISNO_DELO primary key nonclustered (SIF_POZ_DELA)
)
go

/*==============================================================*/
/* Table: AUTOR                                                 */
/*==============================================================*/
create table AUTOR (
   SIF_POZ_DELA         int              not null foreign key references POZORISNO_DELO (SIF_POZ_DELA),
   SIF_LICA             int              not null foreign key references LICE (SIF_LICA),
   constraint PK_AUTOR primary key nonclustered (SIF_POZ_DELA, SIF_LICA)
)
go

/*==============================================================*/
/* Table: PREDSTAVA_PROJEKT                                     */
/*==============================================================*/
create table PREDSTAVA_PROJEKT (
   SIF_POZ_DELA         int              not null foreign key references POZORISNO_DELO (SIF_POZ_DELA),
   NASLOV_PREDSTAVE     varchar(50)          not null,
   PREDST_BR_CINOVA     numeric(1)           not null,
   PREDST_SADRZAJ       varchar(5000)        null,
   PREDST_DAT_I_VREME_PREMIJERE datetime             not null,
   PREDST_DUZINA_U_MIN  numeric(3)           not null,
   NA_REPERTOARU        bit                  not null,
   constraint PK_PREDSTAVA_PROJEKT primary key nonclustered (SIF_POZ_DELA, NASLOV_PREDSTAVE)
)
go

/*==============================================================*/
/* Table: POZ_PREDST_PROJ                                       */
/*==============================================================*/
create table POZ_PREDST_PROJ (
   SIF_POZ_DELA         int              not null,
   NASLOV_PREDSTAVE     varchar(50)          not null,
   SIF_POZORISTA        int              not null foreign key references POZORISTE (SIF_POZORISTA),
   constraint PK_POZ_PREDST_PROJ primary key nonclustered (SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_POZORISTA),
   constraint FK_PREDSTAVA_PROJEKT foreign key (SIF_POZ_DELA, NASLOV_PREDSTAVE)
      references PREDSTAVA_PROJEKT (SIF_POZ_DELA, NASLOV_PREDSTAVE)
)
go

/*==============================================================*/
/* Table: PREDSTAVA_NA_REPERTOARU                               */
/*==============================================================*/
create table PREDSTAVA_NA_REPERTOARU (
   SIF_POZ_DELA         int              not null,
   NASLOV_PREDSTAVE     varchar(50)          not null,
   SIF_POZORISTA        int              not null,
   SIF_PROSTORIJE       int              not null,
   POCETAK_PREDSTAVE    datetime             not null,
   constraint PK_PREDSTAVA_NA_REPERTOARU primary key nonclustered (SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_POZORISTA, SIF_PROSTORIJE, POCETAK_PREDSTAVE),
   constraint FK_PREDSTAVA_PROJEKT1 foreign key (SIF_POZ_DELA, NASLOV_PREDSTAVE)
      references PREDSTAVA_PROJEKT (SIF_POZ_DELA, NASLOV_PREDSTAVE),
   constraint FK_PROSTORIJA foreign key (SIF_POZORISTA, SIF_PROSTORIJE)
      references PROSTORIJA (SIF_POZORISTA, SIF_PROSTORIJE)
)
go

/*==============================================================*/
/* Table: PREDST_PROJ_ULOGA                                     */
/*==============================================================*/
create table PREDST_PROJ_ULOGA (
   SIF_POZ_DELA         int              not null,
   NASLOV_PREDSTAVE     varchar(50)          not null,
   SIF_ULOGE            int              not null foreign key references ULOGA (SIF_ULOGE),
   constraint PK_PREDST_PROJ_ULOGA primary key nonclustered (SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_ULOGE),
   constraint FK_PREDSTAVA_PROJEKT2 foreign key (SIF_POZ_DELA, NASLOV_PREDSTAVE)
      references PREDSTAVA_PROJEKT (SIF_POZ_DELA, NASLOV_PREDSTAVE)
)
go

/*==============================================================*/
/* Table: LICE_U_ULOZI_PREDST_PROJ                              */
/*==============================================================*/
create table LICE_U_ULOZI_PREDST_PROJ (
   SIF_LICA             int              not null foreign key references LICE (SIF_LICA),
   SIF_POZ_DELA         int              not null,
   NASLOV_PREDSTAVE     varchar(50)          not null,
   SIF_ULOGE            int              not null,
   constraint PK_LICE_U_ULOZI_PREDST_PROJ primary key nonclustered (SIF_LICA, SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_ULOGE),
   constraint FK_PREDST_PROJ_ULOGA_ foreign key (SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_ULOGE)
      references PREDST_PROJ_ULOGA (SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_ULOGE)
)
go

/*==============================================================*/
/* Table: UCESTVUJE_U_PREDSTAVI                                 */
/*==============================================================*/
create table UCESTVUJE_U_PREDSTAVI (
   SIF_POZ_DELA         int              not null,
   NASLOV_PREDSTAVE     varchar(50)          not null,
   SIF_POZORISTA        int              not null,
   SIF_PROSTORIJE       int              not null,
   POCETAK_PREDSTAVE    datetime             not null,
   SIF_LICA             int              not null,
   SIF_ULOGE            int              not null,
   constraint PK_UCESTVUJE_U_PREDSTAVI primary key nonclustered (SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_POZORISTA, SIF_PROSTORIJE, POCETAK_PREDSTAVE, SIF_LICA, SIF_ULOGE),
   constraint FK_LICE_U_ULOZI_PREDST_PROJ foreign key (SIF_LICA, SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_ULOGE)
      references LICE_U_ULOZI_PREDST_PROJ (SIF_LICA, SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_ULOGE),
   constraint FK_PREDSTAVA_NA_REPERTOARU foreign key (SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_POZORISTA, SIF_PROSTORIJE, POCETAK_PREDSTAVE)
      references PREDSTAVA_NA_REPERTOARU (SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_POZORISTA, SIF_PROSTORIJE, POCETAK_PREDSTAVE)
)
go

    
 use PPortal;
 go

/* PUNJENJE PODATAKA U TABELE */
/* PUNJENJE PODATAKA U TABELE */


/* 1 - Punjenje tabele ZEMLJA  */
/*
create table ZEMLJA (
   SIF_ZEMLJE           int,
   DRZAVA               varchar(30)          not null,
   OZNAKA_DRZAVE        char(5)              null,
   constraint PK_ZEMLJA primary key nonclustered (SIF_ZEMLJE)
)
go
*/

insert ZEMLJA values (1, 'Republika Srbija', 'SRB');
insert ZEMLJA values (2, 'Crna Gora', 'MNE');
insert ZEMLJA values (3, 'Madjarska', 'H');
insert ZEMLJA values (4, 'Sjedinjene Americke Drzave', 'USA');




/* 2 - Punjenje tabele MESTO  */

/*
create table MESTO (
   SIF_MESTA            int,
   SIF_ZEMLJE           int              not null foreign key references ZEMLJA (SIF_ZEMLJE),
   NAZIV_MESTA          varchar(30)          not null,
   POST_BROJ            numeric(5)           not null,
   constraint PK_MESTO primary key nonclustered (SIF_MESTA)
)
go
*/
		
insert MESTO values (1, 1, 'Novi Sad', 21000);
insert MESTO values(2, 1, 'Beograd',11000);
insert MESTO values(3, 2, 'Budva',2350);
insert MESTO values(4, 3, 'Budimpesta',1111);
insert MESTO values(5, 4, 'New York',13572);
insert MESTO values(6, 4, 'Chicago',23657);
go



/* 3 - Punjenje tabele ZANR  */

/*
create table ZANR (
   SIF_ZANRA            int,
   ZANR                 varchar(50)          not null,
   constraint PK_ZANR primary key nonclustered (SIF_ZANRA)
)
go
*/

insert ZANR values (1, 'Mjuzikl');
insert ZANR values(2, 'Drama');
go


/* 4 - Punjenje tabele ULOGA  */	

/*
create table ULOGA (
   SIF_ULOGE            int,
   ULOGA                varchar(30)          null,
   constraint PK_ULOGA primary key nonclustered (SIF_ULOGE)
)
go
*/

insert ULOGA values (1, 'Reditelj');
insert ULOGA values(2, 'Domar');
insert ULOGA values(3, 'Bela');
insert ULOGA values(4, 'Ana');
insert ULOGA values(5, 'Tevje, mlekadzija');
insert ULOGA values(6, 'Golde, njegova zena');
insert ULOGA values(7, 'Cajtel, najstarija kci');


go



/* 5 - Punjenje tabele POZORISTE  */
/*
create table POZORISTE (
   SIF_POZORISTA        int,
   SIF_MESTA            int              not null foreign key references MESTO (SIF_MESTA),
   NAZIV_POZORISTA      varchar(100)         not null,
   P_POSTANSKI_BROJ     numeric(5)           not null,
   P_MESTO              varchar(30)          not null,
   P_ULICA_BROJ         varchar(30)          null,
   P_TELEFON            varchar(20)          null,
   P_WWW_ADRESA         varchar(100)         null,
   P_EMAIL              varchar(50)          null,
   P_IZ_VOJVODINE       bit                  not null,
   P_AMATERSKO          bit                  not null,
   constraint PK_POZORISTE primary key nonclustered (SIF_POZORISTA)
)
go
*/

insert POZORISTE
	values(1, 3, 'Budva grad teatar', 2355, 'Budva', 'Put pozorista 1',
	 '+38611578-9814', 'www.budvagradteatar.mne',
	 'info@budvagradteatar.mne', 'false', 'false');
insert POZORISTE
values(2, 2,'Smart studio',11150,'Beograd',
	'Kalemegdanska 2','+38111/8123-344','http://www.smartstudio.rs',
	'info@smartstudio.rs','false','false');
insert POZORISTE
values(3, 2,'Dom omladine Beograda',11110,'Beograd',
	'Omladinska 5-7','+38111/8698-344','http://www.domomladinebeograda.rs',
	'info@domomladinebeograda.rs','false','false');
insert POZORISTE
values(4, 1,'Srpsko narodno pozoriste',21000,'Novi Sad',
	'Pozorisni trg 1','+38121/8288-389','http://www.srpskonarodnopozoriste.rs',
	'info@srpskonarodnopozoriste.rs','true','false');
insert POZORISTE
values(5, 1,'Novosadsko pozoriste',21000,'Novi Sad',
	'Jovan Subotic 3-5','+38121/2689-389','http://www.novosadskopozoriste.rs',
	'info@novosadskopozoriste.rs','true','false');
go
 

/* 6 - Punjenje tabele LICE  */
/*
create table LICE (
   SIF_LICA             int,
   SIF_MESTA            int              not null foreign key references MESTO (SIF_MESTA),
   L_PREZIME            varchar(20)          not null,
   L_IME                varchar(20)          not null,
   L_BIOGRAFIJA         varchar(5000)        null,
   L_IZ_VOJVODINE       bit                  not null,
   L_AMATER             bit                  not null,
   L_AKTIVNO            bit                  not null,
   constraint PK_LICE primary key nonclustered (SIF_LICA)
)
go
*/

insert LICE
	values(1, 2, 'Maksimovic', 'Danica', 'Biografijaaaaa Danice Maksimovic...',
	 'false', 'false', 'true');
insert LICE
	values(2, 2,'Lecic','Branislav','Biografija Branislava Lecica...',
	'false','false','true');
insert LICE
	values(3, 2, 'Vickovic', 'Marija', 'Biografijaaaaa Marije Vickovic...',
	 'false', 'false', 'true');
insert LICE
	values(4, 2,'Premovic','Dusko','Biografija Duska Premovica...',
	'false','false','true');
insert LICE
	values(5, 1,'Beres','Atila','Biografija Atile Beresa...',
	'false','false','true');
insert LICE
	values(6, 2,'Jurisa','Ivo','Biografija Ive Jurise...',
	'false','false','true');
insert LICE
	values(7, 1,'Balaz','Aron','Biografija Arona Balaza...',
	'false','false','true');
insert LICE
	values(8, 1,'Josic Gajin','Gordana','Biografija Gordane Josic Gajin...',
	'false','false','true');
insert LICE
	values(9, 1,'Elor','Emina','Biografija Emine Elor...',
	'false','false','true');
insert LICE
	values(10, 5,'Stein','Joseph','Biografija Josepha Steina...',
	'false','false','true');
insert LICE
	values(11, 6,'Bock','Jerry','Biografija Jerryja Bocka...',
	'false','false','true');
go


/* 7 - Punjenje tabele PROSTORIJA  */
/*
create table PROSTORIJA (
   SIF_POZORISTA        int              not null foreign key references POZORISTE (SIF_POZORISTA),
   SIF_PROSTORIJE       int,
   NAZIV_PROSTORIJE     varchar(50)          not null,
   PR_BROJ_SEDISTA      numeric(3)           not null,
   constraint PK_PROSTORIJA primary key nonclustered (SIF_POZORISTA, SIF_PROSTORIJE)
)
go
*/

insert PROSTORIJA
	values(1, 1, 'Pera Dobrinovic', 156);
insert PROSTORIJA
	values(3, 1,'Bina 1',150);
go




/* 8 - Punjenje tabele POZORISNO_DELO  */
/*
create table POZORISNO_DELO (
   SIF_POZ_DELA         int,
   SIF_ZANRA            int              not null foreign key references ZANR (SIF_ZANRA),
   SIF_LICA_PREVOD      int               foreign key references LICE (SIF_LICA),
   NASLOV_POZ_DELA      varchar(50)          not null,
   POZ_DELO_BR_CINOVA   numeric(1)           not null,
   constraint PK_POZORISNO_DELO primary key nonclustered (SIF_POZ_DELA)
)
go
*/

insert POZORISNO_DELO
	values(1, 1,  6, 'Violinista na krovu', 2);
insert POZORISNO_DELO
	values(2, 2, null, 'Indigo',4);
go



/* 9 - Punjenje tabele AUTOR  */
/*
create table AUTOR (
   SIF_POZ_DELA         int              not null foreign key references POZORISNO_DELO (SIF_POZ_DELA),
   SIF_LICA             int              not null foreign key references LICE (SIF_LICA),
   constraint PK_AUTOR primary key nonclustered (SIF_POZ_DELA, SIF_LICA)
)
go
*/

insert AUTOR values(2, 4);
insert AUTOR values(1, 10);
insert AUTOR values(1, 11);
	go


/* 10 - Punjenje tabele PREDSTAVA_PROJEKT  */
/*
create table PREDSTAVA_PROJEKT (
   SIF_POZ_DELA         int              not null foreign key references POZORISNO_DELO (SIF_POZ_DELA),
   NASLOV_PREDSTAVE     varchar(50)          not null,
   PREDST_BR_CINOVA     numeric(1)           not null,
   PREDST_SADRZAJ       varchar(5000)        null,
   PREDST_DAT_I_VREME_PREMIJERE datetime             not null,
   PREDST_DUZINA_U_MIN  numeric(3)           not null,
   NA_REPERTOARU        bit                  not null,
   constraint PK_PREDSTAVA_PROJEKT primary key nonclustered (SIF_POZ_DELA, NASLOV_PREDSTAVE)
)
go
*/

insert PREDSTAVA_PROJEKT
	values( 1, 'Violinista na krovu', 2, 'Sadrzaj predstaveeeee Violinista.......',
	 '2015.05.23 19:30', 150, 'true');
insert PREDSTAVA_PROJEKT
	values(2,'Indigo',4,'Sadrzaj predstave Indigo...',
	'9/23/2012 20:30',90,'true');
	go


/* 11 - Punjenje tabele POZ_PREDST_PROJ  */
/*

create table POZ_PREDST_PROJ (
   SIF_POZ_DELA         int              not null,
   NASLOV_PREDSTAVE     varchar(50)          not null,
   SIF_POZORISTA        int              not null foreign key references POZORISTE (SIF_POZORISTA),
   constraint PK_POZ_PREDST_PROJ primary key nonclustered (SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_POZORISTA),
   constraint FK_PREDSTAVA_PROJEKT foreign key (SIF_POZ_DELA, NASLOV_PREDSTAVE)
      references PREDSTAVA_PROJEKT (SIF_POZ_DELA, NASLOV_PREDSTAVE)
)
go
*/

insert POZ_PREDST_PROJ values( 1, 'Violinista na krovu', 4);
insert POZ_PREDST_PROJ values( 1, 'Violinista na krovu', 5);
insert POZ_PREDST_PROJ values(2,'Indigo', 1);
insert POZ_PREDST_PROJ values(2,'Indigo', 2);
insert POZ_PREDST_PROJ values(2,'Indigo', 3);

go



/* 12 - Punjenje tabele PREDSTAVA_NA_REPERTOARU  */
/*
create table PREDSTAVA_NA_REPERTOARU (
   SIF_POZ_DELA         int              not null,
   NASLOV_PREDSTAVE     varchar(50)          not null,
   SIF_POZORISTA        int              not null,
   SIF_PROSTORIJE       int              not null,
   POCETAK_PREDSTAVE    datetime             not null,
   constraint PK_PREDSTAVA_NA_REPERTOARU primary key nonclustered (SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_POZORISTA, SIF_PROSTORIJE, POCETAK_PREDSTAVE),
   constraint FK_PREDSTAVA_PROJEKT1 foreign key (SIF_POZ_DELA, NASLOV_PREDSTAVE)
      references PREDSTAVA_PROJEKT (SIF_POZ_DELA, NASLOV_PREDSTAVE),
   constraint FK_PROSTORIJA foreign key (SIF_POZORISTA, SIF_PROSTORIJE)
      references PROSTORIJA (SIF_POZORISTA, SIF_PROSTORIJE)
)
go
*/

insert PREDSTAVA_NA_REPERTOARU
	values( 1, 'Violinista na krovu', 1, 1, '2015.06.13 19:30');
insert PREDSTAVA_NA_REPERTOARU
	values( 1, 'Violinista na krovu', 1, 1, '2015.11.26 19:30');
insert PREDSTAVA_NA_REPERTOARU
	values(2,'Indigo',3,1,'9/23/2012 20:30');
insert PREDSTAVA_NA_REPERTOARU
	values(2,'Indigo',3,1,'9/25/2012 20:00');
go



/* 13 - Punjenje tabele PREDST_PROJ_ULOGA  */
/*
create table PREDST_PROJ_ULOGA (
   SIF_POZ_DELA         int              not null,
   NASLOV_PREDSTAVE     varchar(50)          not null,
   SIF_ULOGE            int              not null foreign key references ULOGA (SIF_ULOGE),
   constraint PK_PREDST_PROJ_ULOGA primary key nonclustered (SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_ULOGE),
   constraint FK_PREDSTAVA_PROJEKT2 foreign key (SIF_POZ_DELA, NASLOV_PREDSTAVE)
      references PREDSTAVA_PROJEKT (SIF_POZ_DELA, NASLOV_PREDSTAVE)
)
go
*/

insert PREDST_PROJ_ULOGA values( 1, 'Violinista na krovu', 1);
insert PREDST_PROJ_ULOGA values( 1, 'Violinista na krovu', 5);
insert PREDST_PROJ_ULOGA values( 1, 'Violinista na krovu', 6);
insert PREDST_PROJ_ULOGA values( 1, 'Violinista na krovu', 7);
insert PREDST_PROJ_ULOGA values(2,'Indigo',1);
insert PREDST_PROJ_ULOGA values(2,'Indigo',2);
insert PREDST_PROJ_ULOGA values(2,'Indigo',3);
insert PREDST_PROJ_ULOGA values(2,'Indigo',4);

go




/* 14 - Punjenje tabele LICE_U_ULOZI_PREDST_PROJ  */
/*
create table LICE_U_ULOZI_PREDST_PROJ (
   SIF_LICA             int              not null foreign key references LICE (SIF_LICA),
   SIF_POZ_DELA         int              not null,
   NASLOV_PREDSTAVE     varchar(50)      not null,
   SIF_ULOGE            int              not null,
   constraint PK_LICE_U_ULOZI_PREDST_PROJ primary key nonclustered (SIF_LICA, SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_ULOGE),
   constraint FK_PREDST_PROJ_ULOGA_ foreign key (SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_ULOGE)
      references PREDST_PROJ_ULOGA (SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_ULOGE)
)
go
*/

insert LICE_U_ULOZI_PREDST_PROJ values( 5, 1, 'Violinista na krovu', 1);
insert LICE_U_ULOZI_PREDST_PROJ values( 7, 1, 'Violinista na krovu', 5);
insert LICE_U_ULOZI_PREDST_PROJ values( 8, 1, 'Violinista na krovu', 6);
insert LICE_U_ULOZI_PREDST_PROJ values( 9, 1, 'Violinista na krovu', 7);
insert LICE_U_ULOZI_PREDST_PROJ values(2,2,'Indigo',1);
insert LICE_U_ULOZI_PREDST_PROJ values(2,2,'Indigo',2);
insert LICE_U_ULOZI_PREDST_PROJ values(1,2,'Indigo',3);
insert LICE_U_ULOZI_PREDST_PROJ values(3,2,'Indigo',4);


go


/* 15 - Punjenje tabele UCESTVUJE_U_PREDSTAVI  */
/*
create table UCESTVUJE_U_PREDSTAVI (
   SIF_POZ_DELA         int              not null,
   NASLOV_PREDSTAVE     varchar(50)          not null,
   SIF_POZORISTA        int              not null,
   SIF_PROSTORIJE       int              not null,
   POCETAK_PREDSTAVE    datetime             not null,
   SIF_LICA             int              not null,
   SIF_ULOGE            int              not null,
   constraint PK_UCESTVUJE_U_PREDSTAVI primary key nonclustered (SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_POZORISTA, SIF_PROSTORIJE, POCETAK_PREDSTAVE, SIF_LICA, SIF_ULOGE),
   constraint FK_LICE_U_ULOZI_PREDST_PROJ foreign key (SIF_LICA, SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_ULOGE)
      references LICE_U_ULOZI_PREDST_PROJ (SIF_LICA, SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_ULOGE),
   constraint FK_PREDSTAVA_NA_REPERTOARU foreign key (SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_POZORISTA, SIF_PROSTORIJE, POCETAK_PREDSTAVE)
      references PREDSTAVA_NA_REPERTOARU (SIF_POZ_DELA, NASLOV_PREDSTAVE, SIF_POZORISTA, SIF_PROSTORIJE, POCETAK_PREDSTAVE)
)
go
*/

insert UCESTVUJE_U_PREDSTAVI
	values( 1, 'Violinista na krovu', 1, 1, '2015.06.13 19:30', 7, 5);
insert UCESTVUJE_U_PREDSTAVI
	values( 1, 'Violinista na krovu', 1, 1, '2015.06.13 19:30', 8, 6);
insert UCESTVUJE_U_PREDSTAVI
	values( 1, 'Violinista na krovu', 1, 1, '2015.06.13 19:30', 9, 7);
insert UCESTVUJE_U_PREDSTAVI
	values( 1, 'Violinista na krovu', 1, 1, '2015.11.26 19:30', 7, 5);
insert UCESTVUJE_U_PREDSTAVI
	values( 1, 'Violinista na krovu', 1, 1, '2015.11.26 19:30', 8, 6);
insert UCESTVUJE_U_PREDSTAVI
	values( 1, 'Violinista na krovu', 1, 1, '2015.11.26 19:30', 9, 7);
insert UCESTVUJE_U_PREDSTAVI
	values(2,'Indigo',3,1,'9/23/2012 20:30',2,2);
insert UCESTVUJE_U_PREDSTAVI
	values(2,'Indigo',3,1,'9/23/2012 20:30',1,3);
insert UCESTVUJE_U_PREDSTAVI
	values(2,'Indigo',3,1,'9/23/2012 20:30',3,4);
insert UCESTVUJE_U_PREDSTAVI
	values(2,'Indigo',3,1,'9/25/2012 20:00',2,2);
insert UCESTVUJE_U_PREDSTAVI
	values(2,'Indigo',3,1,'9/25/2012 20:00',1,3);
insert UCESTVUJE_U_PREDSTAVI
	values(2,'Indigo',3,1,'9/25/2012 20:00',3,4);	
go






/* 09.12.2015. 14:00 */

use PPortal;
go



/* Koliko ima evidentiranih mesta(gradova) u bazi? */

select count(sif_mesta)
	from MESTO;
go

/* Koliko ima evidentiranih mesta(gradova) po zemljama (drzavama) u bazi? */

select count(sif_mesta)
	from MESTO
	group by SIF_ZEMLJE;
go

select SIF_ZEMLJE, count(sif_mesta)
	from MESTO
	group by SIF_ZEMLJE;
go


/* Koliko ima evidentiranih mesta(gradova) po nazivima zemaljama (drzava)
	 u bazi? */

select Drzava, count(sif_mesta)
	from MESTO m, ZEMLJA z
	group by Drzava;
go

select *
	from MESTO m, ZEMLJA;
go

select Drzava, count(sif_mesta)
	from MESTO m, ZEMLJA z
	where m.SIF_ZEMLJE=z.SIF_ZEMLJE
	group by Drzava;
go

select Drzava, count(sif_mesta)
	from MESTO m
	join ZEMLJA z on m.SIF_ZEMLJE=z.SIF_ZEMLJE
	group by Drzava;
go

update MESTO
	set SIF_ZEMLJE=4 where NAZIV_MESTA='Chichago';
	go

select *
	from MESTO m, ZEMLJA z
	where m.SIF_ZEMLJE=z.SIF_ZEMLJE;
go

/* Koliko ima pozorista po mestima (sif_mesta)? */

select sif_mesta, COUNT(SIF_POZORISTA)
	from POZORISTE
	group by sif_mesta;
	go

/* Koliko ima pozorista po nazivima mesta? */

select NAZIV_MESTA, COUNT(SIF_POZORISTA)
	from POZORISTE p, MESTO m
	where p.SIF_MESTA=m.SIF_MESTA
	group by NAZIV_MESTA;
	go

/* Koliko ima pozorista po nazivima mesta? 
	Zadrzati samo one redove u kojima je taj broj veci od 1 */

select NAZIV_MESTA, COUNT(SIF_POZORISTA)as 'Broj pozorista u mestu'
	from POZORISTE p, MESTO m
	where p.SIF_MESTA=m.SIF_MESTA
	group by NAZIV_MESTA
	having COUNT(SIF_POZORISTA)>1;
	go

/* Koliko ima evidentiranih pozorista u Beogradu? */


select NAZIV_MESTA, COUNT(SIF_POZORISTA)as 'Broj pozorista u mestu'
	from POZORISTE p, MESTO m
	where p.SIF_MESTA=m.SIF_MESTA and NAZIV_MESTA='Beograd'
	group by NAZIV_MESTA;
	go
	

select NAZIV_MESTA, COUNT(SIF_POZORISTA)as 'Broj pozorista u mestu'
	from POZORISTE p, MESTO m
	where p.SIF_MESTA=m.SIF_MESTA
	group by NAZIV_MESTA
	having COUNT(SIF_POZORISTA)>1;
	go

/* Koja su pozorista iz Vojvodine? */

select NAZIV_POZORISTA, P_MESTO, P_IZ_VOJVODINE
	from dbo.POZORISTE
	where  P_IZ_VOJVODINE=1;
	go

/* Koja su amaterska pozorista iz Vojvodine? */

select NAZIV_POZORISTA, P_MESTO, P_IZ_VOJVODINE
	from dbo.POZORISTE
	where  P_IZ_VOJVODINE=1 and P_AMATERSKO=1;
	go

/* Koliko ima amaterskih a koliko profesionalnih pozorista iz Vojvodine? */

select P_AMATERSKO, COUNT(SIF_POZORISTA)
	from dbo.POZORISTE
	where  P_IZ_VOJVODINE=1
	group by P_AMATERSKO;
	go
	
	
	
/* 16-12-2015   */

use PPortal
go

-- Listati lica koja pripadaju Subotickom okrugu, a nisu vise aktivna

select L_IME, L_PREZIME
	from dbo.LICE l, dbo.MESTO m
	where l.SIF_MESTA=m.SIF_MESTA and (POST_BROJ>=24000 and POST_BROJ<25000)
			and L_AKTIVNO='false';
go
select * from LICE;
go

-- Listati lica iz Novog Sada koja su aktivna, a bave se profesionalno
-- pozoristem


select L_IME, L_PREZIME
	from dbo.LICE l, dbo.MESTO m
	where l.SIF_MESTA=m.SIF_MESTA and NAZIV_MESTA='Novi Sad'
			and L_AKTIVNO=1 and L_AMATER=0;
go

-- Listati lica iz Novog Sada cije ime pocinje slovom 'A', koja su aktivna,
-- a bave se profesionalno pozoristem

select L_IME, L_PREZIME
	from dbo.LICE l, dbo.MESTO m
	where l.SIF_MESTA=m.SIF_MESTA and NAZIV_MESTA='Novi Sad'
			and L_AKTIVNO=1 and L_AMATER=0 and SUBSTRING(L_IME,1,1)='A';
go

select L_IME, L_PREZIME
	from dbo.LICE l, dbo.MESTO m
	where l.SIF_MESTA=m.SIF_MESTA and NAZIV_MESTA='Novi Sad'
			and L_AKTIVNO=1 and L_AMATER=0 and L_IME like 'A%';
go

select L_IME, L_PREZIME
	from dbo.LICE l, dbo.MESTO m
	where l.SIF_MESTA=m.SIF_MESTA and NAZIV_MESTA='Novi Sad'
			and L_AKTIVNO=1 and L_AMATER=0 and L_IME='A';
go

-- Listati lica iz Novog Sada koja su aktivna, a bave se profesionalno
-- pozoristem


select L_IME, L_PREZIME
	from dbo.LICE l
	join dbo.MESTO m on l.SIF_MESTA=m.SIF_MESTA
	where NAZIV_MESTA='Novi Sad'
			and L_AKTIVNO=1 and L_AMATER=0;
go

select * from LICE l, MESTO m
	where l.SIF_MESTA=m.SIF_MESTA;
go

-- Listati lica iz Novog Sada i Beograda koja su aktivna, 
-- bave se profesionalno pozoristem a duzina biografije prevazilazi
-- 500 znakova

select L_IME, L_PREZIME
	from dbo.LICE l
	join dbo.MESTO m on l.SIF_MESTA=m.SIF_MESTA
	where (NAZIV_MESTA='Novi Sad' or NAZIV_MESTA='Beograd')
			and L_AKTIVNO=1 and L_AMATER=0 and LEN(L_BIOGRAFIJA)>500;
go

select L_IME, L_PREZIME
	from dbo.LICE l
	join dbo.MESTO m on l.SIF_MESTA=m.SIF_MESTA
	where NAZIV_MESTA in ('Novi Sad', 'Beograd')
			and L_AKTIVNO=1 and L_AMATER=0 and LEN(L_BIOGRAFIJA)>5;
go

-- Listati imena i prezimena lica iz Srbije

select L_IME, L_PREZIME
from dbo.LICE l, ZEMLJA z, MESTO m
where l.SIF_MESTA=m.SIF_MESTA and m.SIF_ZEMLJE=z.SIF_ZEMLJE 
		and DRZAVA='Republika Srbija';
go

select L_IME, L_PREZIME
from dbo.LICE l
join MESTO m on l.SIF_MESTA=m.SIF_MESTA
join ZEMLJA z on m.SIF_ZEMLJE=z.SIF_ZEMLJE 
where DRZAVA='Republika Srbija';
go

-- Resenje pomocu ugradjenog select-a

select L_IME, L_PREZIME
from dbo.LICE
where SIF_MESTA in (select SIF_MESTA
						from dbo.MESTO
						where SIF_ZEMLJE=(select SIF_ZEMLJE
											from dbo.ZEMLJA
											where DRZAVA='Republika Srbija'));
go

-- Listati imena i prezimena lica kao i naziv pripadajuceg grada iz Srbije

select L_IME, L_PREZIME, NAZIV_MESTA
from dbo.LICE l
join dbo.MESTO m on m.SIF_MESTA=l.SIF_MESTA
	where SIF_ZEMLJE=(select SIF_ZEMLJE
						from dbo.ZEMLJA
						where DRZAVA='Republika Srbija');
go

-- Listati imena i prezimena lica kao i naziv pripadajuceg grada
-- takodje i ime/naziv zemlje iz Srbije

select L_IME, L_PREZIME, NAZIV_MESTA, DRZAVA
from dbo.LICE l
join dbo.MESTO m on m.SIF_MESTA=l.SIF_MESTA
join dbo.ZEMLJA z on z.SIF_ZEMLJE=m.SIF_ZEMLJE
	where DRZAVA='Republika Srbija';
go



/* 23.12.2015  */

Prikazat
use Pportal
go
/*Prikazati trenitni datum i trenutno vreme  */

SELECT
substring(cast(CURRENT_TIMESTAMP as character), 1, 11) AS "Date",
substring(cast(CURRENT_TIMESTAMP as character), 12,8) AS "Time",
CURRENT_TIMESTAMP AS "Timestamp"
go


SELECT
substring(cast(GETDATE() as character), 1, 11) AS "Date",
substring(cast(GETDATE() as character), 12,8) AS "Time",
GETDATE() AS "GetDate";
go


SELECT
substring(cast(Sysdatetime() as character), 1, 11) AS "Date",
substring(cast(Sysdatetime() as character), 12,8) AS "Time",
Sysdatetime() AS "Sysdatetime";
go

/*
-- DATEADD()
If you want to add or subtract an amount of time to a column or a variable, then display a new value
in a rowset or set a variable with that new value, DATEADD() will do this. The syntax for DATEADD() is
DATEADD(datepart, number, date)
The datepart option applies to all of the date functions and details what you want to add from
milliseconds to years. These are defined as reserved words and therefore are not surrounded by
quotation marks. There are a number of possible values, as detailed in Table 11-1.
Taking the second option of the datepart function, to add the value, make the number positive,
and to subtract a number, make it negative. Moving to the final option of the datepart function, this
can be either a value, a variable, or a column date type holding the date and time you wish to change.
Table 11-1. Potential Values for datepart
datepart Definition Meaning
isowk, isoww ISOWeek is a numbering system used to give every week in the calendar
a unique, ascending number. An ISO week starts on a Monday, and
Week 1 is the week containing the first Thursday of that year. For example,
in 2008, the first Thursday occurred on January 3, so Week 1 ran from
December 31, 2007, through January 6, 2008.
tz Timezone offset
ns Nanosecond
mcs Microsecond
ms Millisecond
ss, s Second
mi, n Minute
hh Hour
dw, w Weekday
wk, ww Week
dd, d Day
dy, y Day of year
mm, n Month
qq, q Quarter
yy, yyyy Year
*/

-- Timestamp za 50 casova
select GETDATE(), DATEADD(hh, 50, getdate()) as 'Timestamp za 50 casova';
go

-- Timestamp za trenutak od pre 50 casova
select GETDATE() as 'Trenutni Timestamp',
		 DATEADD(hh, -50, getdate()) as 'Timestamp pre 50 casova';
go




/*

-- DATEDIFF()
To find the difference between two dates, you would use the function DATEDIFF(). The syntax for this
function is
DATEDIFF(datepart, startdate, enddate)
The first option contains the same options as for DATEADD(), and startdate and enddate are the
two days you wish to compare. A negative number shows that the enddate is before the startdate.
*/
-- Timestamp za 50 dana, a razlika u nedeljama

select GETDATE() as 'Trenutni Timestamp',
	   DATEADD(DD, 55, getdate()) as 'Timestamp za 50 dana',
	   DATEDIFF(WW, getdate(), DATEADD(DD, 55, getdate())) as 'Razlika u nedeljama';
go

/*
-- DATEPART()
If you wish to achieve returning part of a date from a date variable, column, or value, you can use
DATEPART() within a SELECT statement.
As you may be expecting by now, the syntax has datepart as the first option, and then the
datetoinspect as the second option, which returns the numerical day of the week from the date
inspected.
DATEPART(datepart, datetoinspect)
1. We need to set only one local variable to a date and time. After that, we find the day of the month.
DECLARE @WhatsTheDay datetime
SET @WhatsTheDay = '24 March 2008 3:00 PM'
SELECT DATEPART(dd, @WhatsTheDay)
Figure 11-24 shows the results after executing this code.
Figure 11-24. Finding part of a date
2. To produce a more pleasing date and time for a statement, we can combine DATEPART() and DATENAME() to
have a meaningful output. The function CAST(), which we will look at in detail shortly, is needed here, as it is a
data type conversion function.
DECLARE @WhatsTheDay datetime
SET @WhatsTheDay = '24 March 2008 3:00 PM'
SELECT DATENAME(dw, @WhatsTheDay) + ', ' +
CAST(DATEPART(dd,@WhatsTheDay) AS varchar(2)) + ' ' +
DATENAME(mm,@WhatsTheDay) + ' ' +
CAST(DATEPART(yyyy,@WhatsTheDay) AS char(4))
3. When this is executed, it will produce the more meaningful date shown in Figure 11-25.
Figure 11-25. Finding and concatenating to provide a useful date
*/
-- Ispisati timestamp, koji je dan u nedelji u trenutnom datumu
-- naziv dana nedelje u trenutnom datumu
-- Timestamp za 50 dana, redni broj dana u nedelji u tom datumu i naziv dana

select GETDATE() as 'Trenutni Timestamp',
	   datepart(DW, GETDATE())-1 as 'Redni broj dana u nedelji',
	   DATENAME(dw, GETDATE()) as 'Naziv dana',
		DATEADD(DD, 50, getdate()) as 'Timestamp za 50 dana', 
		datepart(DW, DATEADD(DD, 50, getdate())) as 'Redni br. dana iz Timestampa+50',
		DATENAME(dw, DATEADD(DD, 50, getdate())) as 'Naziv dana za Timestampa+50',
		DATEDIFF(WW, getdate(), DATEADD(DD, 50, getdate())) as 'Razlika u nedeljama';
go

-- ISDATE()
Although ISDATE() is a function that works with dates and times, this system function takes a value
in a column or a variable and confirms whether it contains a valid date or time. The value returned
is 0, or false, for an invalid date, or 1 for true if the date is okay. The formatting of the date for testing within
the ISDATE() function has to be in the same regional format as you have set with SET DATEFORMAT or SET
LANGUAGE. If you are testing in a European format but have your database set to US format, then you
will get a false value returned.

-- 1. The first example demonstrates where a date is invalid. There are only 30 days in September.
DECLARE @IsDate char(15)
SET @IsDate = '31 Sep 2008'
SELECT ISDATE(@IsDate)    - FALSE
*/

select ISDATE('2001-02-28')
go

select GETDATE(),ISDATE(GETDATE())
go

/* Kolika je duzina vracene vrednosti funkcije GETDATE()? */

select GETDATE(), len(GETDATE())
go

Listati malim slovima nazive drzava bez ponavljanja od 9 karaktera!   */	

select distinct LOWER(DRZAVA)
	from dbo.ZEMLJA
	where LEN(DRZAVA)=9;
go

select * from ZEMLJA;
go

Listati malim slovima nazive drzava bez ponavljanja od 9 karaktera,
a naziv drzave ne pocinje slovom M!   */

select distinct LOWER(DRZAVA)
	from dbo.ZEMLJA
	where LEN(DRZAVA)=9 and SUBSTRING(drzava, 1, 1)<>'M';
go

/* Da li postoji lice koje se pojavljuje dva ili vise puta
 kao autor pozorisnog dela? */
 
 select SIF_LICA, COUNT(SIF_LICA)
	from dbo.AUTOR
	group by SIF_LICA;
go

/* Koja lica se (po imenu i prezimenu) pojavljuju po koliko 
puta kao autori pozorisnog dela? */

 select L_IME, L_PREZIME, COUNT(a.SIF_LICA)
	from dbo.AUTOR a,dbo.LICE l
	where a.SIF_LICA=l.SIF_LICA 
	group by L_IME, L_PREZIME;
go

 select L_IME, L_PREZIME, COUNT(a.SIF_LICA)
	from dbo.AUTOR a
	join dbo.LICE l on a.SIF_LICA=l.SIF_LICA
	group by L_IME, L_PREZIME;
go

/* Koja lica se (po imenu i prezimenu) pojavljuju po koliko 
puta kao autori pozorisnog dela? Tabelu rezultata urediti po imenu u 
ABC-dnom redosledu! */

 select L_IME, L_PREZIME, COUNT(a.SIF_LICA)
	from dbo.AUTOR a
	join dbo.LICE l on a.SIF_LICA=l.SIF_LICA
	group by L_IME, L_PREZIME
	order by L_IME;
go

/* Koliko ima razlicitih pozorisnih dela po nazivima zanrs (ZANR)?*/

select ZANR, COUNT(SIF_POZ_DELA)
	from dbo.ZANR z
	join dbo.POZORISNO_DELO pd on pd.SIF_ZANRA=z.SIF_ZANRA
	group by ZANR;
go

select * from POZORISNO_DELO;
go