create table sectii ( 
  cod integer primary key, 
  denumire varchar(70)
);

Create table grupe(
  cod Char(10),
  anstudiu integer,
  anuniv Smallint,
  sectia integer references sectii(cod)
);

Create table studenti(
  sectia integer references sectii(cod),
  nrmatricol Char(10),
  nume Char(30),
  prenume Char(30),
  cnp Char(13),
  primary key(sectia,nrmatricol)
);

Create table traiectorie(
  anuniv Integer,
  grupa Char(10),
  nrmatricol Char(10),
  sectia Integer references sectii(cod)
);

Create table discipline(
  cod Char(10) primary key,
  denumire Char(100)
);

Create table rezultate(
  anuniv Integer,
  disciplina Char (10) references discipline(cod),
  nota Integer,
  notac Char(1),
  nrcredite Float,
  nrmatricol Char(10),
  sectia Integer,
  semestrucrt Integer,
  semestruplan Integer,
  tipdisc Integer
);

--Average temperature in Edinburgh 1764-1820 for each month (in degrees Fahrenheit)
--http://www.adp-gmbh.ch/ora/sql/analytical/edinburgh_temp.html
create table scottish_weather (
  month    number(2),
  year     number(4),
  avg_temp number(4,2)
);

create or replace view infstud as
select s.sectia,s.nrmatricol,nume,prenume,cnp,media,nrcredite,nrdisc
from studenti s inner join
  (select sectia,nrmatricol,to_char(sum(notamax*nrcredite)/sum(nrcredite),'99.99') media,sum(nrcredite) nrcredite,count(*) nrdisc 
   from 
    (select sectia,nrmatricol,max(nota) notamax,nrcredite from rezultate where nota>4 group by sectia,nrmatricol,nrcredite
		 having sum(nrcredite)>0
		)
   group by sectia,nrmatricol
  ) r
  on s.sectia=r.sectia and s.nrmatricol=r.nrmatricol;
  
--viewul precedent se poate rescrie astfel:
create or replace view infstud1 as
with 
  v1 as (select sectia,nrmatricol,max(nota) notamax,nrcredite from rezultate where nota>4 group by sectia,nrmatricol,nrcredite having sum(nrcredite)>0),
  v2 as (select sectia,nrmatricol,to_char(sum(notamax*nrcredite)/sum(nrcredite),'99.99') media,sum(nrcredite) nrcredite,count(*) nrdisc 
         from v1
         group by sectia,nrmatricol
        )
  select s.sectia,s.nrmatricol,nume,prenume,cnp,media,nrcredite,nrdisc
  from studenti s inner join v2 on s.sectia=v2.sectia and s.nrmatricol=v2.nrmatricol;