eR8MPwna

1create schema if not exists Faculdade;
2
3use faculdade;
4
5create table if not exists Alunos
6(
7    RA integer UNSIGNED not null,
8    senha varchar(15) not null,
9    nome varchar(100) not null,
10    faltas integer,
11    PRIMARY KEY (RA)
12)ENGINE = InnoDB;
13
14create table if not exists Materias
15(
16    id SMALLINT UNSIGNED NOT NULL,
17    nome varchar(50) not null,
18    nota1 float,
19    nota2 float,
20    medias float, 
21    substitutiva float,
22    RA integer UNSIGNED not null,
23    primary key(id,RA)
24)ENGINE = InnoDB;
25
26
27ALTER TABLE `Materias` ADD CONSTRAINT `RA` FOREIGN KEY ( `RA` ) REFERENCES `Alunos` ( `RA` ) ;
28
29drop database faculdade;
30
31describe Alunos;
32
33describe Materias;
34
35insert into Alunos values(201500983,'12345','Marcos Paes Leme',0);
36insert into Alunos values(201500984,'54321','Dionizio',0);
37insert into Alunos values(201500985,'1357','Paulo José',0);
38insert into Alunos values(201500986,'2468','Robervau',0);
39
40select * from Alunos;
41
42insert 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));
43insert 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));
44select * from Materias;