CREATE TABLE Student
(NrMtr int,
Nume varchar(20),
an int,
Grupa varchar(5),
Data_Admitere date,
Medie number,
Bursa int);

INSERT INTO Student VALUES (1, 'Popescu Ion', 2, '322CC', '20-JUN-2011', 6.73, 0);
INSERT INTO Student VALUES (2, 'Costache Marius', 3, '332CA', '20-JUN-2009', 7.24, 0);
INSERT INTO Student VALUES (3, 'Tache Ionut', 4, '341CC', '20-JUN-2009', 8.73, 0);
INSERT INTO Student VALUES (4, 'Stoica Dan', 1, '312CB', '20-JUN-2012', 9.44, 0);
INSERT INTO Student VALUES (5, 'Negru Gelu', 1, '312CB', '20-JUN-2011', 9.38, 0);
INSERT INTO Student VALUES (6, 'Florescu Aurel', 3, '332CA', '20-JUN-2010', 8.24, 0);
INSERT INTO Student VALUES (7, 'Milescu Vasile', 2, '322CC', '20-JUN-2010', 7.73, 0);
INSERT INTO Student VALUES (8, 'Tanase Dodel', 4, '341CC', '20-JUN-2008', 6.83, 0);
INSERT INTO Student VALUES (9, 'Ionescu Victor', 3, '331CB', '20-JUN-2010', 9.73, 0);
INSERT INTO Student VALUES (10, 'Avram Calin', 3, '331CB', '20-JUN-2010', 8.24, 0);

SELECT * FROM Student;

UPDATE Student SET Bursa = Medie * 100 WHERE Medie >= 8 AND (An * 365 >= to_date('2013/03/13', 'yyyy/mm/dd') - Data_Admitere); 

SELECT * FROM Student;

prompt 'Introduceti Grupa'
accept Gr char;

select * from Student
	where Medie >= 7 
INTERSECT
select * from Student
	where Grupa = '&Gr' 
INTERSECT
select * from Student
	where Bursa = 0 
UNION
select * from Student
	where (EXTRACT (YEAR FROM Data_Admitere) = 2010); 

select * from Student s1 where Medie = (Select MAX(Medie) From Student s2 where s1.grupa = s2.grupa);

DROP TABLE Student;
