 -- Compile with  @"C:\Documents and Settings\Student\Desktop\lab3.sql"
 
create table studenti (
	nr_matricol number,
	nume varchar(20),
	an number,
	grupa varchar(6),
	data_admitere date,
	medie_admitere float,
	bursa float default 0
);
insert into studenti values (8, 'Mara', 2, '311CA', '13-MAR-2009', 9.75, 0);
insert into studenti values (5, 'Alexei Karamazov', 1, '311CA', '13-MAR-2012', 8.00, 0);

insert into studenti values (3, 'Gheorghita Lipan', 2, '321CA', '13-MAR-2011', 6.75, 0);
insert into studenti values (10, 'Otilia', 3, '321CA', '13-MAR-2010', 9.10, 0);

insert into studenti values (1, 'Eu eu', 3, '331CA', '13-MAR-2008', 7.75, 0);
insert into studenti values (2, 'Vlad TP', 3, '331CA', '12-MAR-2010', 9.65, 0);
insert into studenti values (9, 'Felix', 3, '331CA', '12-MAR-2010', 7.09, 0);

insert into studenti values (6, 'Dmitri Karamazov', 4, '332CC', '13-MAR-2008', 5.75, 0);
insert into studenti values (7, 'Ion Pop Glanetasu', 3, '332CC', '13-MAR-2009', 4.75, 0);

insert into studenti values (4, 'Raskolnikov', 4, '341C1', '13-MAR-2009', 10, 0);
insert into studenti values (11, 'Rodion Romanovici', 4, '341C1', '13-MAR-2009', 10, 0);


--b) setati bursa egala cu medie admitere * 100 daca media de admitere >= 8 si studentii nu sunt repetenti (in fct de data admitere si an)
update studenti 
	set bursa = medie_admitere * 100 
	where medie_admitere >= 8 
	and 
	extract(year from sysdate) - extract(year from data_admitere) <= an;
	
select * from studenti;

--c) selectati studentii cu medie >= 7 si sunt din grupa ceruta de la tastatura si nu au bursa sau studentii care sunt admisi in 2010
select * from studenti where
	(medie_admitere >= 7 and 
	grupa = &Introduceti_Grupa and
	bursa = 0) or (extract(year from data_admitere) = 2010);

--d) selectati nume, an, grupa, data admitere, medie, bursa a sefilor de grupa (acestia sunt cei cu cea mai mare medie de admitere)
select nume, grupa, an, data_admitere, medie_admitere, bursa from studenti A
	where medie_admitere = (select max(medie_admitere) from studenti where grupa=A.grupa);

drop table studenti;
