 -- Compile with  @"C:\Documents and Settings\Student\Desktop\lab4.sql"

-- Student, cu coloanele: Nr matricol, nume, grupa, media de admitere, bursa, 
-- camp numeric care repr ID-ul facultatii

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

-- Adaugare 15 studenti
insert into studenti values (1, 'Derek', '123Med', 10, 0, 1);
insert into studenti values (2, 'Meredith Grey', '123Med', 10, 0, 1);
insert into studenti values (3, 'Christina Yang', '321Med', 9.5, 0, 2);
insert into studenti values (4, 'Miranda Bailey', '321Med', 10, 0, 2);
insert into studenti values (5, 'Owen Hunt', '321Med', 9.7, 0, 1);

insert into studenti values (6, 'Iulian Stana', 'fara_fac', 10, 0, 5000);

insert into studenti values (7, 'Vlad TP', '331CA', 8.9, 0, 4);
insert into studenti values (8, 'Laurrrrrr', '321CA', 5.9, 0, 4);
insert into studenti values (9, 'Mitia Karamazov', '331CA', 7.9, 0, 4);
insert into studenti values (10, 'Ivan Karamazov', '331CA', 6.9, 0, 4);

insert into studenti values (11, 'Nick Stokes', '123Crim', 8.9, 0, 5);
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);

-- Facultate, 3 coloane: ID facultate, nume facultate, ID universitate

create table facultati (
	id_facultate number,
	nume varchar(10),
	id_univ number
);

insert into facultati values (1, 'Medicina', 1);
insert into facultati values (2, 'Medicina', 2);
-- Facultatea vietii nu se invata la nici o universitate
insert into facultati values (3, 'Facultatea Vietii', 1000);
insert into facultati values (4, 'Acs', 3);
insert into facultati values (5, 'CrimeLab', 1);
insert into facultati values (100, 'Fizica', 999);

-- Universitate, 2 coloane: ID universitate, nume universitate

create table universitati (
	id_univ number,
	nume varchar(10)
);

insert into universitati values (1, 'UMF Davila');
insert into universitati values (2, 'UMF Iasi');
insert into universitati values (3, 'Politehnica');

--Universitatea fara facultati dni Bragadiru
insert into universitati values (312, 'Bragadiru');

select * from studenti;
select * from facultati;
select * from universitati;

-- Selectati toti studentii de la UMF Carol Davila, 
-- afisand numele, grupa, facultatea si universitatea
select a.nume, a.grupa, b.nume, c.nume
from studenti a, facultati b, universitati c
where a.id_facultate = b.id_facultate 
		and b.id_univ = c.id_univ 
		and c.nume='UMF Davila';
		
update studenti 
	set bursa = medie_admitere * 100 
	where medie_admitere >= 7 
	and 
	id_facultate <> 4;
	
select * from studenti;

(select a.nume, b.nume
from studenti a, facultati b
where (a.id_facultate = b.id_facultate) and b.id_univ <> 3)
UNION
(select a.nume, b.nume from studenti a, facultati b
where (a.id_facultate >= 5000) and (a.id_facultate = b.id_facultate));




drop table studenti;
drop table facultati;
drop table universitati;