helpers

CREATE TABLE Faculty (
    Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50),
    Address VARCHAR(60),
    YearFounded YEAR,
    Dean VARCHAR(50)
);

CREATE TABLE Specialty(
  Id int not null primary key auto_increment,
  IdFaculty int(11) not null,
  Name varchar(50),
  FOREIGN KEY (IdFaculty)
    REFERENCES Faculty(Id)
);

create table Course(
  Id int not null primary key auto_increment,
  IdSpecialty int not null,
  Name varchar(50),
  TeachingYear int,
  Semester int,
  Professor varchar(50),
  FOREIGN KEY (IdSpecialty)
    REFERENCES Specialty(Id)
);

create table Student(
  Id int not null primary key auto_increment,
  IdFaculty int not null,
  IdSpecialty int not null,
  CurrentYear int,
  StudyGroup varchar(10),
  Semester int,
  Scholarship varchar(2),
  
  FOREIGN KEY (IdFaculty)
    REFERENCES Faculty(Id),
  
  FOREIGN KEY (IdSpecialty)
    REFERENCES Specialty(Id)
);

create table StudentData(
  Id int not null primary key,
  CNP char(13),
  FirstName varchar(30),
  LastName varchar(30),
  Father varchar(50),
  Mother varchar(50),
  Address varchar(200),
  Nationality varchar(50)
);

create table Grade(
  Id int not null primary key auto_increment,
  IdStudent int not null,
  IdCourse int not null,
  Grade int,
  ExamDate date,
  
  FOREIGN KEY (IdStudent)
    REFERENCES Student(Id),
  
  FOREIGN KEY (IdCourse)
    REFERENCES Course(Id)
);

insert into Faculty (Id, Name, Address, YearFounded, Dean)
values
('Faculty of Engineering', 'Sibiu', 1990, 'Liviu Roșca'),
('Faculty of Musical Performance', 'Cluj-Napoca', 1919, 'Cocea Amza'),
('Bucharest Academy of Economic Studies', 'Bucharest', 1913, 'Manciulea Corneliu'),
('Faculty of Computer Science', 'Bucharest', 1818, 'Mureșanu Doru'),
('Nicolae Bălcescu Land Forces Academy', 'Sibiu', 1920, 'Turcescu Silviu')

insert into Specialty (IdFac, Name)
values
(1, 'Computer science'),
(1, 'Information Technology')

insert into Course(IdSpec, Name, TeachingYear, Semester, Credits)
values
(1, 'Computer Programming', 1, 1, 5),
(1, 'Mathematical Analysis', 1, 1, 5),
(1, 'Operating Systems Utilization', 1, 1, 4),
(1, 'Algebra and Discrete Mathematics', 1, 1, 4),
(1, 'Physics', 1, 1, 5),
(1, 'Logical Basis of Computers', 1, 1, 5),
(1, 'Data Structures', 1, 2, 5),
(1, 'Special Mathematics', 1, 2, 5),
(1, 'Electrotechnics', 1, 2, 5),
(1, 'Numerical Methods', 1, 2, 5),
(1, 'Logic Design', 1, 2, 5),
(1, 'Professional Communication Techniques', 1, 2, 3),
(1, 'Algorithms Analysis and Design', 2, 1, 5),
(1, 'Object Oriented Programming', 2, 1, 5),
(1, 'Analog Electronics', 2, 1, 4),
(1, 'Assembly Language', 2, 1, 5),
(1, 'Systems Theory', 2, 1, 5),
(1, 'Statistical Techniques for Data Analysis', 2, 1, 4),
(1, 'Programming Techniques', 2, 2, 4),
(1, 'Computer Peripherals and Protocols', 2, 2, 4),
(1, 'Graphical Applications Programming', 2, 2, 4),
(1, 'Web Design', 2, 2, 4),
(1, 'Digital Electronics', 2, 2, 4),
(1, 'Measurements and Data Acquisition', 2, 2 	, 4),
(1, 'Digital Computers', 3, 1, 5),
(1, 'Computer Networks', 3, 1, 5),
(1, 'Formal Languages and Compilers', 3, 1, 5),
(1, 'Databases', 3, 1, 5),
(1, 'Software Engineering', 3, 1, 5),
(1, 'Hardware Description Languages', 3, 1, 5),
(1, 'Computer Architecture', 3, 2, 5),
(1, 'Microprocessor Based Systems', 3, 2, 5),
(1, 'Operating Systems', 3, 2, 4),
(1, 'Parallel and Distributed Algorithms', 3, 2, 4),
(1, 'Databases Design', 3, 2, 4),
(1, 'Bioinformatics', 3, 2, 2),
(1, 'Company Economics', 3, 2, 2),
(1, 'Industrial Marketing', 3, 2, 2)


('Andreescu ', 'Bogdan '),
('Barbu ', 'Paul '),
('Catrina ', 'Marius '),
('Călin ', 'Florina '),
('Filigean ', 'Casian '),
('Cojocaru ', 'Roxana '),
('Fleacă ', 'Valentin '),
('Ghip ', 'Sabrina '),
('Floare ', 'Andreea '),
('Nuţu ', 'Andrei '),
('Matei ', 'Alexandru '),
('Olescu ', 'Marco '),
('Morcoaşe ', 'Ion '),
('Tiucă ', 'Dumitru '),
('Alexandrescu ', 'Diana '),
('Bebeşelea ', 'Radu '),
('Bugner ', 'Ioana '),
('Cherciu ', 'Alexandra '),
('Chirilă ', 'Vlad '),
('Cojocariu ', 'Roxana '),
('Drăgoi ', 'Alexandra '),
('Gînţă ', 'Anda '),
('Halaţi ', 'Daniel '),
('Mihai ', 'Lenuţa '),
('Oros ', 'Ana '),
('Rusu ', 'Andrei '),
('Talpoş ', 'Mădălina '),
('Lazăr ', 'Liviu '),
('Presecan ', 'Răzvan '),
('Achim ', 'Darius '),
('Bebeşelea ', 'Nicoleta '),
('Cinezan ', 'Alexandru '),
('Cozma ', 'Alexandru '),
('Creţ ', 'Cătălin '),
('Mosor ', 'Dănuţ '),
('Nistor ', 'Raul '),
('Pallo ', 'Samuel '),
('Pastor ', 'Andreea '),
('Prundurel ', 'Flavius '),
('Răulea ', 'Mihaela '),
('Toderuţiu ', 'Radu '),
('Tomegea ', 'Răzvan '),
('Stoica ', 'Mădălina '),
('Zăvoianu ', 'Bianca-Elena '),
('Burghelea ', 'Eli '),
('Macarie ', 'Cristian '),
('Bugner ', 'Adrian '),
('Damian ', 'Mihail '),
('Fleşeriu ', 'Gheorghe '),
('Modrângă ', 'Cristina-Maria '),
('Orăşan ', 'Vlăduţ '),
('Panainte ', 'Nicolae '),
('Pîrcălăboiu ', 'Andrei '),
('Popescu ', 'Ovidiu '),
('Scorobeţ ', 'Ionel '),
('Stoia ', 'Paul '),
('Spătăcian ', 'Adrian '),
('Ţerbea ', 'Ionuţ '),
('Vararu ', 'Andrei '),
('Antonescu ', 'Cătălin '),
('Căpăţînă ', 'Constantin-Sorin '),
('Cojanu ', 'Andrei '),
('Dobrilă-Petric ', 'Victor '),
('Nicolaescu ', 'Vlad '),
('Ionescu ', 'Raluca '),
('Moraru ', 'Diana '),
('Oniţă ', 'Sebastian '),
('Pătruţiu ', 'Dan '),
('Pocean ', 'Susana-Dana '),
('Schuller ', 'Alin-Mihai '),
('Szakacs ', 'Marieta '),
('Benchea ', 'Ioan '),
('Bularca ', 'Bianca '),
('Chiriţă ', 'Andrei '),
('Cristea ', 'Adrian-Nicolae '),
('Dumitru ', 'Vlad-Iulian '),
('Făgeţan ', 'Sebastian '),
('Iliuţ ', 'Andrei-Coman '),
('Iftode ', 'Mihai-Flavius '),
('Nicoară ', 'Andreea '),
('Păunescu ', 'Flavius '),
('Stan ', 'Evald-Bogdan '),
('Tătulea ', 'Paula '),
('Ţocu ', 'Nicolae-Adrian '),
('Şuţoiu ', 'Casandra '),
('Popa ', 'Daniel '),
('Stancu ', 'Andrei '),
('Vinersar ', 'Denisa '),
('Lăcătuş ', 'Melissa '),
('Fleaca ', 'Dan '),
('Tudorică ', 'Paul '),
('Pătruţa ', 'Alex '),
('Cora ', 'Adrian '),
('Stănilă ', 'Ilie '),
('Crapciu ', 'Dan '),
('Popa ', 'Cosmin '),
('Cotoară ', 'Silvia '),
('Fogoroş ', 'Andrei '),
('Puşcă ', 'Radu '),
('Stan ', 'Sorin '),
('Dancăşiu ', 'Şerban '),
('Banu ', 'Mihai '),
('Banciu ', 'Diana '),
('Anisiea ', 'Geanina '),
('Popa ', 'Laurenţiu ')

// -------------- Create procedure template ----------------- 

DELIMITER $$

DROP PROCEDURE IF EXISTS insert_grades$$
CREATE PROCEDURE insert_grades()
BEGIN
  // ------------ Write your loops here -------------
END$$

DELIMITER ;