dbd_ex2

  DROP TABLE "ASOCIERE" cascade constraints;
  DROP TABLE "CONT" cascade constraints;
  DROP TABLE "UTILIZATOR" cascade constraints;
--------------------------------------------------------
--  DDL for Table ASOCIERE
--------------------------------------------------------

  CREATE TABLE "ASOCIERE"
   (	"ID_UTIL" VARCHAR2(20 BYTE),
	"ID_CONT" VARCHAR2(20 BYTE)
   ) ;
--------------------------------------------------------
--  DDL for Table CONT
--------------------------------------------------------

  CREATE TABLE "CONT"
   (	"ID_CONT" VARCHAR2(20 BYTE),
	"DURATA" VARCHAR2(20 BYTE),
	"CANTATITATE_DATE" NUMBER
   ) ;
--------------------------------------------------------
--  DDL for Table UTILIZATOR
--------------------------------------------------------

  CREATE TABLE "UTILIZATOR"
   (	"ID_UTIL" VARCHAR2(20 BYTE),
	"NUME" VARCHAR2(30 BYTE)
   ) ;
REM INSERTING into ASOCIERE
Insert into ASOCIERE (ID_UTIL,ID_CONT) values ('U01','C01');
Insert into ASOCIERE (ID_UTIL,ID_CONT) values ('U01','C02');
Insert into ASOCIERE (ID_UTIL,ID_CONT) values ('U01','C03');
Insert into ASOCIERE (ID_UTIL,ID_CONT) values ('U02','C04');
Insert into ASOCIERE (ID_UTIL,ID_CONT) values ('U03','C05');
Insert into ASOCIERE (ID_UTIL,ID_CONT) values ('U04','C06');
Insert into ASOCIERE (ID_UTIL,ID_CONT) values ('U05','C07');
Insert into ASOCIERE (ID_UTIL,ID_CONT) values ('U05','C08');
REM INSERTING into CONT
Insert into CONT (ID_CONT,DURATA,CANTATITATE_DATE) values ('C01','3m',100);
Insert into CONT (ID_CONT,DURATA,CANTATITATE_DATE) values ('C02','3m',75);
Insert into CONT (ID_CONT,DURATA,CANTATITATE_DATE) values ('C03','20m',500);
Insert into CONT (ID_CONT,DURATA,CANTATITATE_DATE) values ('C04','1h',1240);
Insert into CONT (ID_CONT,DURATA,CANTATITATE_DATE) values ('C05','40m',840);
Insert into CONT (ID_CONT,DURATA,CANTATITATE_DATE) values ('C06','1m',10);
Insert into CONT (ID_CONT,DURATA,CANTATITATE_DATE) values ('C07','15m',300);
Insert into CONT (ID_CONT,DURATA,CANTATITATE_DATE) values ('C08','25m',400);
REM INSERTING into UTILIZATOR
Insert into UTILIZATOR (ID_UTIL,NUME) values ('U01','Popescu Gheorghe');
Insert into UTILIZATOR (ID_UTIL,NUME) values ('U02','Paran Bianca');
Insert into UTILIZATOR (ID_UTIL,NUME) values ('U03','Adriana Petrisor');
Insert into UTILIZATOR (ID_UTIL,NUME) values ('U04','Sturza Claudiu');
Insert into UTILIZATOR (ID_UTIL,NUME) values ('U05','Petrisor Ioan');
--------------------------------------------------------
--  DDL for Index CONT_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "CONT_PK" ON "CONT" ("ID_CONT")
  ;
--------------------------------------------------------
--  DDL for Index UTILIZATOR_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "UTILIZATOR_PK" ON "UTILIZATOR" ("ID_UTIL")
  ;
--------------------------------------------------------
--  Constraints for Table CONT
--------------------------------------------------------

  ALTER TABLE "CONT" ADD CONSTRAINT "CONT_PK" PRIMARY KEY ("ID_CONT") ENABLE;

  ALTER TABLE "CONT" MODIFY ("ID_CONT" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table UTILIZATOR
--------------------------------------------------------

  ALTER TABLE "UTILIZATOR" MODIFY ("ID_UTIL" NOT NULL ENABLE);

  ALTER TABLE "UTILIZATOR" ADD CONSTRAINT "UTILIZATOR_PK" PRIMARY KEY ("ID_UTIL") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table ASOCIERE
--------------------------------------------------------

  ALTER TABLE "ASOCIERE" ADD CONSTRAINT "CONT_FK" FOREIGN KEY ("ID_CONT")
	  REFERENCES "CONT" ("ID_CONT") ENABLE;

  ALTER TABLE "ASOCIERE" ADD CONSTRAINT "UTIL_FK" FOREIGN KEY ("ID_UTIL")
	  REFERENCES "UTILIZATOR" ("ID_UTIL") ENABLE;


    /*b. Sa se afiseze un top al utilizatorilor in functie de numarul "cantitatea"
          totala de date utilizata (pe toate conturile). Se vor afisa
          doar utilizatorii care au depasit 100MB (sau alta valoare arbitrara)
          */


select * from (select u.id_util, sum(c.cantatitate_date) as cantitatetotala from utilizator u, cont c, asociere a
where u.id_util=a.id_util and c.id_cont=a.id_cont
group by u.id_util
order by cantitatetotala DESC) where cantitatetotala>500;

/*c. Sa se afiseze toti utilizatorii al caror trafic total nu depaseste
          media traficului inregistrat in baza de date (trafic total raportat
          la numarul de utilizatori)
*/

/* media traficului integistrat in baza de date este 433.125
U01 - are trafic total 675 Mb
U02 - 1240 Mb
U03 - 840
U04 - 10
U05 - 700
*/

select * from (select u.id_util,  sum(c.cantatitate_date) as Trafictotal from  utilizator u, cont c, asociere a
where u.id_util=a.id_util and c.id_cont=a.id_cont
group by u.id_util
order by  trafictotal desc) where trafictotal < (select avg(cantatitate_date) from cont c);

/* d Sa se elimine din baza de date ultimii trei utilizatori in ordinea
          traficului total generat. In acest pas se vor elimina atat conturile
          cat si sesiunile acestora
*/