create schema if not exists Faculdade;

use faculdade;

create table if not exists Alunos
(
    RA integer UNSIGNED not null,
    senha varchar(15) not null,
    nome varchar(100) not null,
    faltas integer,
    PRIMARY KEY (RA)
)ENGINE = InnoDB;

create table if not exists Materias
(
    id SMALLINT UNSIGNED NOT NULL,
    nome varchar(50) not null,
    nota1 float,
    nota2 float,
    medias float,
    substitutiva float,
    RA integer UNSIGNED not null,
    primary key(id,RA)
)ENGINE = InnoDB;


ALTER TABLE `Materias` ADD CONSTRAINT `RA` FOREIGN KEY ( `RA` ) REFERENCES `Alunos` ( `RA` ) ;

drop database faculdade;

describe Alunos;

describe Materias;

insert into Alunos values(201500983,'12345','Marcos Paes Leme',0);
insert into Alunos values(201500984,'54321','Dionizio',0);
insert into Alunos values(201500985,'1357','Paulo José',0);
insert into Alunos values(201500986,'2468','Robervau',0);

select * from Alunos;

insert into Materias values(1,'Alged',6.0,7.0,(select sum(nota1+nota2)/2 from Materias where RA = 201500984),0.0,(select RA from Alunos where RA=201500984));
insert into Materias values(1,'Alged',6.0,7.0,(select sum(nota1+nota2)/2 from Materias where RA = 201500984),0.0,(select RA from Alunos where RA=201500984));
select * from Materias;