
drop table studenti;
drop table facultati;

create table studenti (
	nr_matricol number,
	nume varchar(20),
	grupa varchar(10),
	medie_admitere float,
	bursa float,
	id_facultate number
);

create table facultati (
	id_facultate number,
	nume varchar(15)
);

insert into studenti values (1, 'Derek Shepherd', '123Med', 10, 0, 1);
insert into studenti values (2, 'Meredith Grey', '123Med', 10, 0, 1);

insert into studenti values (3, 'Christina Yang', '321Surg', 5.5, 0, 2);
insert into studenti values (4, 'Miranda Bailey', '321Surg', 9.9, 0, 2);
insert into studenti values (5, 'Owen Hunt', '321Surg', 9.7, 0, 2);

insert into studenti values (11, 'Nick Stokes', '331CA', 8.9, 0, 4);

insert into studenti values (12, 'Warrick Brown', '123Crim', 5.9, 0, 5);
insert into studenti values (13, 'Sara Sidle', '123Crim', 9.5, 0, 5);
insert into studenti values (14, 'Catherine Willows', '123Crim', 6, 0, 5);
insert into studenti values (15, 'David Hodges', '123Crim', 8.7, 0, 5);

select * from studenti;

insert into facultati values (1, 'SeatleGrace');
insert into facultati values (2, 'GreySloanMem');
insert into facultati values (3, 'FacultVietii');
insert into facultati values (4, 'Acs');
insert into facultati values (5, 'CrimeLab');

select * from facultati;

select a.nume, a.medie_admitere, a.bursa, b.nume Facultate
from studenti a, facultati b
where (a.id_facultate = b.id_facultate 
		and 
		a.medie_admitere = (select max(medie_admitere) from studenti where id_facultate = a.id_facultate)
		);
		
update studenti a
set a.bursa = a.medie_admitere * 100
where (
		a.medie_admitere = (select max(medie_admitere) from studenti where id_facultate = a.id_facultate)
		);

--select * from studenti;
select a.nume, a.medie_admitere, a.bursa, b.nume Facultate
from studenti a, facultati b
where (a.id_facultate = b.id_facultate);

update studenti a
set a.bursa = 0.5 * (select max(bursa) from studenti where id_facultate = a.id_facultate)
where (
		a.medie_admitere >= (select avg(medie_admitere) from studenti where id_facultate = a.id_facultate)
		and a.bursa = 0
		);

select a.nume, a.medie_admitere, a.bursa, b.nume Facultate, 
(select nume from studenti where id_facultate = a.id_facultate 
and
medie_admitere = (select max(medie_admitere) from studenti where id_facultate = a.id_facultate)
and nume !='Meredith Grey' ) SefuLaToti,
(select max(bursa) from studenti where id_facultate = a.id_facultate) BursaSef 
from studenti a, facultati b
where a.id_facultate = b.id_facultate;

--c) Clasament al facultatilor care au cel putin 2 studenti, selectand intr-un tabel de forma:
--facultate, nr_studenti, nr_studenti_peste_medie, nota_maxima, medie, suma burselor, punctaj
--Punctaj = nr_studenti(facultate) + nr_studenti(nota > peste medie) + nota maxima

select 
	(select f.nume from facultati f where f.id_facultate=a.id_facultate) NumeFacultate, 
	count(a.nume) NrStuds
from studenti a
group by a.id_facultate
having count(a.nume) >= 2;


-- @"C:\Documents and Settings\Student\Desktop\lab6.sql"
