-- {1}
USE Basel;

DROP TABLE Profesor;

CREATE TABLE Profesor(
CODF int NOT NULL PRIMARY KEY,
NUME_PROF  varchar(20) NOT NULL,
NUME_STUD  varchar(50),
FUNCTIE varchar(50),
DISCIPLINA varchar(30)
);

DROP TABLE Stud;

CREATE TABLE Stud(
NUME_STUD  varchar(50) NOT NULL PRIMARY KEY,
AN int NOT NULL,
INCADR varchar(50),
SIT_SC varchar(30),
SEX varchar(10)
);

DROP TABLE Note;

CREATE TABLE Note(
NR int NOT NULL PRIMARY KEY,
NUME_PROF varchar(50) NOT NULL,
NUME_STUD  varchar(50) NOT NULL,
NOTA int
);

insert into Profesor values(101,'Ionescu','Rami','universitar','Istorie');
insert into Profesor values(102,'Ionescu','Hala','universitar','Istorie');
insert into Profesor values(103,'Ionescu','Rama','universitar','Istorie');
insert into Profesor values(104,'Ionescu','Marua','universitar','Istorie');
insert into Profesor values(105,'Ionescu','Waled','universitar','Istorie');
insert into Profesor values(106,'Ionescu','Majed','universitar','Istorie');
insert into Profesor values(107,'Ionescu','Rana','universitar','Istorie');

insert into Profesor values(111,'Popescu','Lama','universitar','Geografie');
insert into Profesor values(112,'Popescu','Nadeen','universitar','Geografie');
insert into Profesor values(113,'Popescu','Tamer','universitar','Geografie');
insert into Profesor values(114,'Popescu','Haytham','universitar','Geografie');
insert into Profesor values(115,'Popescu','Marwan','universitar','Geografie');
insert into Profesor values(116,'Popescu','Haya','universitar','Geografie');
insert into Profesor values(117,'Popescu','Heba','universitar','Geografie');

insert into Profesor values(1001,'Nico','Basel','universitar','Matematica');
insert into Profesor values(1002,'Nico','Foaad','universitar','Matematica');
insert into Profesor values(1003,'Nico','Kresty','universitar','Matematica');
insert into Profesor values(1004,'Nico','Karam','universitar','Matematica');
insert into Profesor values(1005,'Nico','Ahmed','universitar','Matematica');
insert into Profesor values(1006,'Nico','nasel','universitar','Matematica');
insert into Profesor values(1007,'Nico','feras','universitar','Matematica');




insert into Stud values('Rami',2,'Arheologie','Rami@yahoo.com','M');
insert into Stud values('Hala',3,'Arheologie','hala56@gmail.com','F');
insert into Stud values('Rama',1,'Arheologie','Rama34@hotmail.com','F');
insert into Stud values('Marua',1,'Arheologie','marwa@hotmail.com','F');
insert into Stud values('Waled',1,'Arheologie','waled@hotmail.com','M');
insert into Stud values('Majed',1,'Arheologie','mj989@hotmail.com','M');
insert into Stud values('Rana',2,'Arheologie','rana@hotmail.com','F');

insert into Stud values('Lama',1,'Stiinta_solului','lama@hotmail.com','F');
insert into Stud values('Nadeen',3,'Stiinta_solului','nAd@hotmail.com','F');
insert into Stud values('Tamer',2,'Stiinta_solului','tam@hotmail.com','M');
insert into Stud values('Haytham',1,'Stiinta_solului','hay@hotmail.com','M');
insert into Stud values('Marwan',1,'Stiinta_solului','mar@hotmail.com','M');
insert into Stud values('Haya',2,'Stiinta_solului','heya@hotmail.com','F');
insert into Stud values('Heba',1,'Stiinta_solului','haba@hotmail.com','F');

insert into Stud values('Basel',1,'Informatica','basel@hotmail.com','M');
insert into Stud values('Foaad',1,'Matematica','Foaad22@hotmail.com','M');
insert into Stud values('Kresty',1,'Matematica','Kres@hotmail.com','M');
insert into Stud values('Karam',2,'Informatica','Karam54@hotmail.com','M');
insert into Stud values('Ahmed',1,'Informatica','Ahm3444@hotmail.com','M');
insert into Stud values('Adel',3,'Matematica','Aad34@hotmail.com','M');
insert into Stud values('Najem',1,'Informatica','NJ787@hotmail.com','M');




insert into Note values(1,'Ionescu','Rami',7);
insert into Note values(2,'Ionescu','Hala',9);
insert into Note values(3,'Ionescu','Rama',8);
insert into Note values(4,'Ionescu','Marua',9);
insert into Note values(5,'Ionescu','Waled',10);
insert into Note values(6,'Ionescu','Majed',8);
insert into Note values(7,'Ionescu','Rana',6);

insert into Note values(8,'Popescu','Lama',6);
insert into Note values(9,'Popescu','Nadeen',8);
insert into Note values(10,'Popescu','Tamer',9);
insert into Note values(11,'Popescu','Haytham',6);
insert into Note values(12,'Popescu','Marwan',10);
insert into Note values(13,'Popescu','Haya',7);
insert into Note values(14,'Popescu','Heba',7);

insert into Note values(15,'Nico','Basel',10);
insert into Note values(16,'Nico','Foaad',2);
insert into Note values(17,'Nico','Kresty',7);
insert into Note values(18,'Nico','Karam',8);
insert into Note values(19,'Nico','Ahmed',10);
insert into Note values(20,'Nico','Adel',9);
insert into Note values(21,'Nico','Najem',5);




alter table profesor add Constraint f1 foreign key(NUME_STUD) REFERENCES Stud(NUME_STUD);



select * from Profesor;
select * from Stud;
select * from Note;

-- {2}
select NUME_STUD from Note where NOTA>2;


-- {3}
select avg(NOTA) from Note;
select avg(NOTA) NOTA_MEDIU_Nico from Note  where NUME_PROF='Nico';
select avg(NOTA) NOTA_MEDIU_Popescu from Note  where NUME_PROF='Popescu';
select avg(NOTA) NOTA_MEDIU_Ionescu from Note  where NUME_PROF='Ionescu';


ALTER TABLE Note ADD COLUMN Average FLOAT NOT NULL ;

UPDATE Note
SET Average = 8.1429
WHERE NR < 8 ;

UPDATE Note
SET Average = 7.5714
WHERE NR >= 8 AND NR < 15;

UPDATE Note
SET Average = 7.2857
WHERE NR >= 15 AND NR <=21 ;

select NUME_PROF
from Note
where Average > 8 AND NR = 1;

-- {4}
select NUME_PROF , NUME_STUD  from Note where NOTA>=9;

-- {5}
select min(NOTA) from Note where NOTA > 8;