/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     10/12/2015 2:11:05 PM                        */
/*==============================================================*/
/*
create database NarudzbaRobe;
go
use NarudzbaRobe;
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('NARUDZBA') and o.name = 'FK_NARUDZBA_NARUCUJE_KUPAC')
alter table NARUDZBA
   drop constraint FK_NARUDZBA_NARUCUJE_KUPAC
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_NARUDZBE') and o.name = 'FK_STAVKA_N_NARUDZBI__NARUDZBA')
alter table STAVKA_NARUDZBE
   drop constraint FK_STAVKA_N_NARUDZBI__NARUDZBA
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_NARUDZBE') and o.name = 'FK_STAVKA_N_ROBA_UCES_ROBA_CEN')
alter table STAVKA_NARUDZBE
   drop constraint FK_STAVKA_N_ROBA_UCES_ROBA_CEN
go

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

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

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

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

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

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

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

/*==============================================================*/
/* Table: KUPAC                                                 */
/*==============================================================*/
create table KUPAC (
   SIF_KUPCA            smallint             not null,
   NAZ_KUPCA            varchar(30)          null,
   POST_BROJ            numeric(5)           null,
   ADRESA               varchar(20)          null,
   constraint PK_KUPAC primary key nonclustered (SIF_KUPCA)
)
go

/*==============================================================*/
/* Table: NARUDZBA                                              */
/*==============================================================*/
create table NARUDZBA (
   BR_NARUDZBE          smallint             not null,
   SIF_KUPCA            smallint             not null,
   DATUM_NARUDZBE       datetime             null,
   constraint PK_NARUDZBA primary key nonclustered (BR_NARUDZBE)
)
go

/*==============================================================*/
/* Index: NARUCUJE_FK                                           */
/*==============================================================*/
create index NARUCUJE_FK on NARUDZBA (
SIF_KUPCA ASC
)
go

/*==============================================================*/
/* Table: ROBA_CENOVNIK                                         */
/*==============================================================*/
create table ROBA_CENOVNIK (
   SIF_ROBE             smallint             not null,
   JED_MERE             char(10)             not null,
   NAZIV_ROBE           varchar(20)          null,
   JED_CENA             numeric(15,2)        null,
   constraint PK_ROBA_CENOVNIK primary key nonclustered (SIF_ROBE, JED_MERE)
)
go

/*==============================================================*/
/* Table: STAVKA_NARUDZBE                                       */
/*==============================================================*/
create table STAVKA_NARUDZBE (
   SIF_ROBE             smallint             not null,
   JED_MERE             char(10)             not null,
   BR_NARUDZBE          smallint             not null,
   NARUCENA_KOL         numeric(12,2)        null,
   constraint PK_STAVKA_NARUDZBE primary key nonclustered (SIF_ROBE, JED_MERE, BR_NARUDZBE)
)
go

/*==============================================================*/
/* Index: NARUDZBI_PRIPADA_STAVKA_FK                            */
/*==============================================================*/
create index NARUDZBI_PRIPADA_STAVKA_FK on STAVKA_NARUDZBE (
BR_NARUDZBE ASC
)
go

/*==============================================================*/
/* Index: ROBA_UCESTVUJE_U_STAVKI_NAR_FK                        */
/*==============================================================*/
create index ROBA_UCESTVUJE_U_STAVKI_NAR_FK on STAVKA_NARUDZBE (
SIF_ROBE ASC,
JED_MERE ASC
)
go

alter table NARUDZBA
   add constraint FK_NARUDZBA_NARUCUJE_KUPAC foreign key (SIF_KUPCA)
      references KUPAC (SIF_KUPCA)
go

alter table STAVKA_NARUDZBE
   add constraint FK_STAVKA_N_NARUDZBI__NARUDZBA foreign key (BR_NARUDZBE)
      references NARUDZBA (BR_NARUDZBE)
go

alter table STAVKA_NARUDZBE
   add constraint FK_STAVKA_N_ROBA_UCES_ROBA_CEN foreign key (SIF_ROBE, JED_MERE)
      references ROBA_CENOVNIK (SIF_ROBE, JED_MERE)
go

/*
Drugi kolokvijum - MUSTRA - RESENJE
*/
/*
Drugi kolokvijum - MUSTRA - RESENJE
*/

use NarudzbaRobe;
go
/*

1. Listati iznose koje su pojedini kupci
  potrosili narucivanjem u mesecu
	decembar 2015 godine. 
	Tabelu rezultata urediti po velicini
	 iznosa u opadajucem redosledu. */

-- GR1	 
select naz_kupca, SUM(narucena_kol*jed_cena) as 'Ukupna nadrudzba'
	from dbo.Kupac k
	join dbo.Narudzba n on n.sif_kupca=k.sif_kupca
	join dbo.Stavka_narudzbe sn on sn.br_narudzbe=n.br_narudzbe
	join dbo.Roba_cenovnik rc on rc.sif_robe=sn.sif_robe
	where YEAR(	datum_narudzbe)=2015 and MONTH(datum_narudzbe)=12
	group by naz_kupca
	order by SUM(narucena_kol*jed_cena) desc;
go

	 
--GR3 
	 
select naz_kupca, SUM(narucena_kol*jed_cena) as 'Vrednost ukupne narudzbe'
	from dbo.Kupac k
	join dbo.Narudzba n on n.SIF_KUPCA=k.SIF_KUPCA
	join dbo.Stavka_narudzbe sn on sn.BR_NARUDZBE=n.BR_NARUDZBE
	join dbo.Roba_cenovnik rc on (rc.SIF_ROBE+rc.JED_MERE)=(sn.SIF_ROBE+sn.JED_MERE)
	where MONTH(datum_narudzbe)=12 and year(datum_narudzbe)=2015
	group by NAZ_KUPCA
	order by SUM(narucena_kol*jed_cena) desc;
go

/*

2. Listati one kupce (po nazivu) iz Beograda,
	a za koje nemamo evidentirane adrese.
	GR1
*/

select naz_kupca, post_broj, adresa
	from dbo.Kupac
	where post_broj=11000 and adresa is null;
go


/*
2. Listati one kupce (po nazivu) iz Beograda koji su narucivali
	u 2015-oj godini, a za koje nemamo evidentirane adrese.
	GR3
*/

select distinct(naz_kupca)
	from dbo.Kupac k
	join dbo.Narudzba n on n.sif_kupca=k.sif_kupca
	where post_broj=11000 and adresa is null and year(datum_narudzbe)=2015;
go

select naz_kupca
	from dbo.Kupac
	where post_broj=11000 and adresa is null 
		and sif_kupca in (select sif_kupca
							from dbo.Narudzba
							where year(datum_narudzbe)=2015);
go 






/*

3. Koji su ti kupci koji jos nisu ni jednom narucivali robu - ZA MENADŽERE
*/

--GR3
select naz_kupca
	from dbo.Kupac k
	left outer join dbo.Narudzba n on n.sif_kupca=k.sif_kupca
	where datum_narudzbe is null;
go

select naz_kupca
	from dbo.Narudzba n
	right outer join dbo.Kupac k on n.sif_kupca=k.sif_kupca
	where datum_narudzbe is null;
go

select naz_kupca
	from dbo.Kupac
	where sif_kupca not in (select sif_kupca
							from dbo.Narudzba);
go


--GR1

select naz_kupca
	from dbo.Kupac k
	left join dbo.Narudzba n on n.sif_kupca=k.sif_kupca
	where datum_narudzbe is null;
go

select naz_kupca
	from dbo.Kupac k
	where sif_kupca not in (select sif_kupca
								from dbo.Narudzba);
go


/*

4. Koliko ima kupaca po postanskim brojevima, i to onih kupaca koji do sada 
	nisu narucivali robu. Zadržati samo one redove u tabeli rezultata (listati
	samo one postanske brojeve) za koje je taj broj (kupaca) veći od 1.
	Urediti listu po broju kupaca u opadajućem redosledu! – ZA INFORMATIČARE */

--GR3 - resenje I

select post_broj, COUNT(sif_kupca)
	from dbo.Kupac
	where sif_kupca not in (select sif_kupca
							from dbo.Narudzba)
	group by post_broj
	having COUNT(sif_kupca)>1
	order by COUNT(sif_kupca) desc;
go

--GR3 - resenje II 

select post_broj, COUNT(k.sif_kupca)
	from dbo.Kupac k
	left outer join dbo.Narudzba n on n.sif_kupca=k.sif_kupca
	where datum_narudzbe is null
	group by post_broj
	having COUNT(k.sif_kupca)>1
	order by COUNT(k.sif_kupca) desc;
go

select post_broj, COUNT(k.sif_kupca)
	from dbo.Narudzba n
	right outer join dbo.Kupac k on n.sif_kupca=k.sif_kupca
	where datum_narudzbe is null
	group by post_broj
	having COUNT(k.sif_kupca)>1
	order by COUNT(k.sif_kupca) desc;
go


/*

5. Koje robe po nazivu (Naziv_robe) je narucivao kupac Pionir? 
Listu urediti po abecednom redosledu po Nazivu_robe! */

--GR3

select distinct(Naziv_robe)
	from dbo.Kupac k
	join dbo.Narudzba n on n.sif_kupca=k.sif_kupca
	join dbo.Stavka_narudzbe sn on sn.br_narudzbe=n.br_narudzbe
	join dbo.Roba_cenovnik rc on rc.sif_robe=sn.sif_robe
	where naz_kupca='Pionir';
go

select Naziv_robe
from dbo.Roba_cenovnik
where sif_robe in (select sif_robe
					from dbo.Stavka_narudzbe
					where br_narudzbe in (select br_narudzbe
											from dbo.Narudzba
											where sif_kupca=(select sif_kupca
															from dbo.Kupac
															where naz_kupca='Pionir')));
go



/*

6. Listati podatke o tome koji kupac (naz_kupca) na kojim narudzbama
(br_narudzbe) i na kojim stavkama (sif_robe, jed_mere) koje robe (naziv_robe) je
narucivao. Urediti listu po naz_kupca, br_narudzbe) i sif_robe, jed_mere! */


--GR3

select naz_kupca,n.br_narudzbe, sn.sif_robe, sn.jed_mere, Naziv_robe
from dbo.Kupac k
	join dbo.Narudzba n on n.sif_kupca=k.sif_kupca
	join dbo.Stavka_narudzbe sn on sn.br_narudzbe=n.br_narudzbe
	join dbo.Roba_cenovnik rc on rc.sif_robe=sn.sif_robe
order by naz_kupca, n.br_narudzbe, sn.sif_robe, sn.jed_mere;
go