CREATE TABLE universitate(
id number(2) PRIMARY KEY,
nume varchar(15) UNIQUE
);

CREATE TABLE facultate(
id number(2) PRIMARY KEY,
nume varchar(15) UNIQUE,
id_univ number(2) REFERENCES universitate(id) ON DELETE CASCADE
);

CREATE TABLE student(
id number(2) PRIMARY KEY,
nume varchar(15) UNIQUE,
data_admitere DATE CONSTRAINT date_check
	CHECK (to_date('01-01-2009', 'dd-mm-yyyy') < data_admitere AND
	data_admitere < to_date('31-12-2012', 'dd-mm-yyyy')),
nota_admitere number CHECK(nota_admitere >= 5 AND nota_admitere <= 10),
bursa number(3) CHECK(bursa >= 0 AND bursa <= 900),
id_fac number(2) REFERENCES facultate(id) ON DELETE CASCADE
);

INSERT INTO universitate VALUES(1, 'Politehnica');
INSERT INTO universitate VALUES(2, 'ASE');
INSERT INTO universitate VALUES(3, 'Univ. Buc');

INSERT INTO facultate VALUES(1, 'Calculatoare', 1);
INSERT INTO facultate VALUES(2, 'Energetica', 1);
INSERT INTO facultate VALUES(3, 'Marketing', 2);
INSERT INTO facultate VALUES(4, 'Comert', 2);
INSERT INTO facultate VALUES(5, 'Drept', 3);

INSERT INTO student VALUES(1, 'Ioana', to_date('20-06-2010', 'dd-mm-yyyy'), 9.90, 0, 1);
INSERT INTO student VALUES(2, 'Iulia', to_date('21-06-2012', 'dd-mm-yyyy'), 8.30, 0, 5);
INSERT INTO student VALUES(3, 'Radu', to_date('20-06-2010', 'dd-mm-yyyy'), 9.80, 0, 1);
INSERT INTO student VALUES(4, 'Delia', to_date('2-07-2010', 'dd-mm-yyyy'), 8.50, 0, 3);
INSERT INTO student VALUES(5, 'Genny', to_date('20-06-2010', 'dd-mm-yyyy'), 7, 0, 4);
INSERT INTO student VALUES(6, 'Robert', to_date('20-06-2009', 'dd-mm-yyyy'), 9.6, 0, 4);
INSERT INTO student VALUES(7, 'Bogdan', to_date('15-07-2010', 'dd-mm-yyyy'), 7.1, 0, 5);
INSERT INTO student VALUES(8, 'Alexandru', to_date('20-06-2011', 'dd-mm-yyyy'), 9.30, 0, 2);
INSERT INTO student VALUES(9, 'Georgiana', to_date('20-06-2011', 'dd-mm-yyyy'), 9.20, 0, 2);
INSERT INTO student VALUES(10, 'Catalina', to_date('20-06-2010', 'dd-mm-yyyy'), 6, 0, 1);

SELECT * FROM universitate;
SELECT * FROM facultate;
SELECT * FROM student;

UPDATE student s SET s.bursa = nota_admitere * 100 WHERE s.nota_admitere = 
(SELECT MAX(nota_admitere) FROM student a WHERE a.id_fac = s.id_fac) AND
s.nota_admitere <= 9;

UPDATE student s SET s.bursa = 900 WHERE s.nota_admitere = 
(SELECT MAX(nota_admitere) FROM student a WHERE a.id_fac = s.id_fac) AND
s.nota_admitere > 9;

SELECT * FROM student;

CREATE TABLE cei_mai_buni AS
SELECT s.nume, s.nota_admitere, s.bursa, 
(SELECT AVG(nota_admitere) FROM student a WHERE a.id_fac = s.id_fac) Medie_fac,
f.nume Nume_fac, u.nume Nume_univ FROM student s, facultate f, universitate u 
WHERE s.nota_admitere >= (SELECT AVG(nota_admitere) FROM student b WHERE b.id_fac = s.id_fac) AND
s.id_fac = f.id AND f.id_univ = u.id;

SELECT * FROM cei_mai_buni;

SET ECHO OFF;
SET PAGESIZE 30;

TTITLE CENTER UNDERLINE 'Raport';

COL A FOR A15 HEA 'Nume';
COL B FOR 9.99 HEA 'Nota_admitere';
COL C FOR 999 HEA 'Bursa';
COL D FOR 9.99 HEA 'Medie_fac';
COL E FOR A15 HEA 'Nume_fac';
COL F FOR A15 HEA 'Nume_univ';

BREAK ON E NODUP ON F NODUP ON REPORT;
COMPUTE SUM LABEL 'Total FAC:' OF C ON E;
COMPUTE SUM LABEL 'Total UNIV:' OF C ON F;

SELECT a.nume A, a.nota_admitere B, a.bursa C, a.Medie_fac D, a.Nume_fac E, a.Nume_univ F 
FROM cei_mai_buni a ORDER BY F, E;

TTITLE OFF;
CLEAR COLUMNS;
CLEAR COMPUTE;

DELETE FROM universitate u WHERE u.nume = 'Politehnica';

SELECT * FROM universitate;
SELECT * FROM facultate;
SELECT * FROM student;

DROP TABLE cei_mai_buni;
DROP TABLE student;
DROP TABLE facultate;
DROP TABLE universitate;