/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     11/4/2014 5:32:53 PM                         */
/*==============================================================*/

create database Trgovački_centar;
go

use Trgovački_centar;
go


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('RACUN') and o.name = 'FK_RACUN_SASTAVLJA_RADNIK')
alter table RACUN
   drop constraint FK_RACUN_SASTAVLJA_RADNIK
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('STAVKA_RACUNA') and o.name = 'FK_STAVKA_R_PRIPADA_ROBA')
alter table STAVKA_RACUNA
   drop constraint FK_STAVKA_R_PRIPADA_ROBA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('STAVKA_RACUNA') and o.name = 'FK_STAVKA_R_SADRZI_RACUN')
alter table STAVKA_RACUNA
   drop constraint FK_STAVKA_R_SADRZI_RACUN
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('RACUN')
            and   name  = 'SASTAVLJA_FK'
            and   indid > 0
            and   indid < 255)
   drop index RACUN.SASTAVLJA_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RACUN')
            and   type = 'U')
   drop table RACUN
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RADNIK')
            and   type = 'U')
   drop table RADNIK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ROBA')
            and   type = 'U')
   drop table ROBA
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('STAVKA_RACUNA')
            and   name  = 'PRIPADA_FK'
            and   indid > 0
            and   indid < 255)
   drop index STAVKA_RACUNA.PRIPADA_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('STAVKA_RACUNA')
            and   name  = 'SADRZI_FK'
            and   indid > 0
            and   indid < 255)
   drop index STAVKA_RACUNA.SADRZI_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('STAVKA_RACUNA')
            and   type = 'U')
   drop table STAVKA_RACUNA
go

/*==============================================================*/
/* Table: RACUN                                                 */
/*==============================================================*/
create table RACUN (
   BROJ_RACUNA          int                  not null,
   ID_RADNIKA           int                  not null,
   DATUM_RACUNA         datetime             not null,
   IZNOS_RACUNA         money                not null,
   ROK_ZA_PLACANJE      datetime             not null,
   LICE_ZA_VEZU         char(30)             null,
   TELEFON              char(20)             null,
   constraint PK_RACUN primary key nonclustered (BROJ_RACUNA)
)
go

/*==============================================================*/
/* Index: SASTAVLJA_FK                                          */
/*==============================================================*/
create index SASTAVLJA_FK on RACUN (
ID_RADNIKA ASC
)
go

/*==============================================================*/
/* Table: RADNIK                                                */
/*==============================================================*/
create table RADNIK (
   ID_RADNIKA           int                  not null,
   JMBG                 numeric(13)          not null,
   IME                  char(15)             not null,
   PREZIME              char(15)             not null,
   DATUM_RO_ENJA        datetime             not null,
   OTKAD_JE_NAS_RADNIK  datetime             not null,
   ADRESA_GRAD          char(15)             null,
   ADRESA_ULICA_BROJ    char(15)             null,
   constraint PK_RADNIK primary key nonclustered (ID_RADNIKA)
)
go

/*==============================================================*/
/* Table: ROBA                                                  */
/*==============================================================*/
create table ROBA (
   SIFRA_ROBE           int                  not null,
   NAZIV_ROBE           char(25)             not null,
   OPIS_ROBE            varchar(500)         null,
   constraint PK_ROBA primary key nonclustered (SIFRA_ROBE)
)
go

/*==============================================================*/
/* Table: STAVKA_RACUNA                                         */
/*==============================================================*/
create table STAVKA_RACUNA (
   BROJ_RACUNA          int                  not null,
   BROJ_STAVKE_RAC      int                  not null,
   SIFRA_ROBE           int                  not null,
   KOLICINA             decimal(12,2)        not null,
   JEDINICA_MERE        char(15)             not null,
   CENA                 decimal(12,2)        not null,
   constraint PK_STAVKA_RACUNA primary key nonclustered (BROJ_RACUNA, BROJ_STAVKE_RAC)
)
go

/*==============================================================*/
/* Index: SADRZI_FK                                             */
/*==============================================================*/
create index SADRZI_FK on STAVKA_RACUNA (
BROJ_RACUNA ASC
)
go

/*==============================================================*/
/* Index: PRIPADA_FK                                            */
/*==============================================================*/
create index PRIPADA_FK on STAVKA_RACUNA (
SIFRA_ROBE ASC
)
go

alter table RACUN
   add constraint FK_RACUN_SASTAVLJA_RADNIK foreign key (ID_RADNIKA)
      references RADNIK (ID_RADNIKA)
go

alter table STAVKA_RACUNA
   add constraint FK_STAVKA_R_PRIPADA_ROBA foreign key (SIFRA_ROBE)
      references ROBA (SIFRA_ROBE)
go

alter table STAVKA_RACUNA
   add constraint FK_STAVKA_R_SADRZI_RACUN foreign key (BROJ_RACUNA)
      references RACUN (BROJ_RACUNA)
go

/*
Brisanje pogrešno unetih tabela Trgovačkog_centra iz baze Master  

use master;
go

alter table racun
	drop constraint FK_RACUN_SASTAVLJA_RADNIK;
go

drop table radnik;
go

alter table stavka_racuna
	drop constraint FK_STAVKA_R_PRIPADA_ROBA;
go

drop table roba;
go

alter table stavka_racuna
	drop constraint FK_STAVKA_R_SADRZI_RACUN;
go

drop table stavka_racuna;
go

drop table racun;
go


Brisanje pogrešno unetih tabela Trgovačkog_centra iz baze Master  */


/*************************************************************************************/
/* Rešenje zadataka / upita drugog kolokvijuma */
/* Rešenje zadataka / upita drugog kolokvijuma */
/* Rešenje zadataka / upita drugog kolokvijuma */
/*************************************************************************************/

-- 1./
/*
1.	Listati uspešnost radnika: U kojem ukupnom iznosu su pojedini radnici (ID_radnika)
	sastavili račune u decembru mesecu, za koje račune je Rok_za plaćanje u istom mesecu?
	Tabelu rezultata urediti po uspešnosti radnika
	(ukupnom iznosu svih sastavljenih računa pojedinih radnika)!   */

select ID_RADNIKA, sum(IZNOS_RACUNA) as 'Ukupan iznos svih računa'
	from dbo.RACUN
	where DATEPART(mm,DATUM_RACUNA)= 12 and DATEPART(mm,DATUM_RACUNA)= DATEPART(mm,ROK_ZA_PLACANJE)
	group by ID_RADNIKA
	order by sum(IZNOS_RACUNA) desc;
go


-- 2./
/*
2.	Listati one račune (Broj_racuna) iz 2014. godine za koje je evidentiran Telefon,
	a Lice_za _vezu je neppoznato.  */

select BROJ_RACUNA, LICE_ZA_VEZU, TELEFON, DATUM_RACUNA
	from dbo.RACUN
	where TELEFON is not null and LICE_ZA_VEZU is null and DATEPART(yy, DATUM_RACUNA)= 2014;
go


-- 3./ ZA MENADŽERE
/*
3.	Ko su ti radnici (Prezime, Ime) koji do sada nisu sastavili nijedan račun? ZA MENADŽERE  */

select PREZIME, IME
	from dbo.RADNIK
	where ID_RADNIKA not in (select ID_RADNIKA
										from dbo.RACUN);
go


-- 3./ ZA INFORMATIČARE
/*
4.	Koliko ima radnika u evidenciji (u bazi podataka) i po kojim gradovima
	koji do sada nisu sastavili ni jedan račun? Zadržati samo one redove u tabeli rezultata
	(listati samo one gradove) u kojima je taj broj (radnika) veći od 1.
	Urediti listu po broju radnika u opadajućem redosledu! – ZA INFORMATIČARE  */
	
select ADRESA_GRAD, COUNT(ID_RADNIKA)
	from dbo.RADNIK
	where ID_RADNIKA not in (select ID_RADNIKA
										from dbo.RACUN)
	group by ADRESA_GRAD
	having COUNT(ID_RADNIKA) > 1
	order by COUNT(ID_RADNIKA) desc;
go

-- 4./
/*
5.	Koje robe po nazivu (Naziv_robe) je fakturisao (obuhvatio na računima/stavkama računa) radnik
	Marko Marković? Listu urediti po abecednom redosledu po Nazivu_robe!  */

select distinct(NAZIV_ROBE)
	from dbo.ROBA r
	join dbo.STAVKA_RACUNA s_r on s_r.SIFRA_ROBE = r.SIFRA_ROBE
	join dbo.RACUN ra on ra.BROJ_RACUNA = s_r.BROJ_RACUNA
	join dbo.RADNIK rad on rad.ID_RADNIKA = ra.ID_RADNIKA
	where rad.PREZIME = 'Marković' and rad.IME = 'Marko'
	order by NAZIV_ROBE;
go



select distinct(NAZIV_ROBE)
	from dbo.ROBA
	where SIFRA_ROBE in (select SIFRA_ROBE
						from dbo.STAVKA_RACUNA
						where BROJ_RACUNA in (select BROJ_RACUNA
												from dbo.RACUN
												where ID_RADNIKA in (select ID_RADNIKA
																				from dbo.RADNIK
																				where PREZIME='Marković' and
																					  IME='Marko')))
	order by NAZIV_ROBE;
go



-- 5./
/*
6.	Listati podatke o tome koji radnik (prezime i ime) na kojim računima (broj_računa)
	i na kojim stavkama (broj_stavke_rac) koje robe (naziv_robe) je fakturisao. 
	Urediti listu po prezimenu, imenu, broju_racuna i broju_stavke_racuna!  */
	
select PREZIME, IME, ra.BROJ_RACUNA, s_r.BROJ_STAVKE_RAC, NAZIV_ROBE
	from dbo.ROBA r
	join dbo.STAVKA_RACUNA s_r on s_r.SIFRA_ROBE = r.SIFRA_ROBE
	join dbo.RACUN ra on ra.BROJ_RACUNA = s_r.BROJ_RACUNA
	join dbo.RADNIK rad on rad.ID_RADNIKA = ra.ID_RADNIKA
	order by PREZIME, IME, ra.BROJ_RACUNA, s_r.BROJ_STAVKE_RAC;
go