26-11-2014 Banco de dados

DROP DATABASE escola;
CREATE DATABASE escola;

SHOW DATABASES;

USE escola;

CREATE TABLE aluno (
    mat       INT,
    nome      VARCHAR(100),
    data_nasc VARCHAR(10),
    tel       VARCHAR(10),
    PRIMARY KEY(mat)
);

CREATE TABLE professor (
    cod  INT,
    nome VARCHAR(100),
    tel  VARCHAR(10),
    salario FLOAT,
    PRIMARY KEY(cod)
);

CREATE TABLE disciplina (
    cod       INT,
    descricao VARCHAR(50),
    PRIMARY KEY(cod)
);

CREATE TABLE cursa(
    mat_aluno INT,
    cod_disc  INT,
    dia       VARCHAR(20),
    PRIMARY KEY(mat_aluno, cod_disc),
    FOREIGN KEY(mat_aluno) REFERENCES aluno(mat),
    FOREIGN KEY(cod_disc)  REFERENCES disciplina(cod)
);

CREATE TABLE leciona(
    cod_professor INT,
    cod_disc      INT,
    dia           VARCHAR(30),
    PRIMARY KEY(cod_professor, cod_disc),
    FOREIGN KEY(cod_professor) REFERENCES professor(cod),
    FOREIGN KEY(cod_disc) REFERENCES disciplina(cod)
);

INSERT INTO aluno (mat, nome, data_nasc, tel)
    VALUES (1, 'bruno', '10-02-99', '9999-9999');

INSERT INTO aluno (mat, nome, data_nasc, tel)
    VALUES (2, 'maria das gracas', '10-02-99', '0666-6660');

SELECT * FROM aluno;
SELECT nome FROM aluno;
SELECT nome, tel, FROM aluno;

ALTER TABLE aluno
    ADD COLUMN email VARCHAR(30);

SELECT * FROM aluno;

INSERT INTO aluno(mat, nome, data_nasc, tel, email)
    VALUES (3, 'Joao Vitor', '18-08-97', '1234-5678', 'victor.rdg@hotmail.com');

ALTER TABLE aluno
    DROP COLUMN tel;

SELECT * FROM aluno;

ALTER TABLE aluno
    ADD COLUMN cpf VARCHAR(15);

ALTER TABLE aluno
    DROP COLUMN email;

SELECT * FROM aluno;

UPDATE aluno SET cpf=('123.123.123')
    WHERE mat=1;

SELECT * FROM aluno;

INSERT INTO aluno(mat, nome, data_nasc, cpf)
    VALUES (4, 'Stevie Vay Vaughan', '01-01-54', '666-666-666');

ALTER TABLE aluno
    ADD COLUMN email VARCHAR(30);

UPDATE aluno SET email=('email1@bol.com.br')
    WHERE mat=1;
UPDATE aluno SET email=('email2@hotmail.com')
    WHERE mat=2;
UPDATE aluno SET email=('email3@gmail.com')
    WHERE mat=3;
UPDATE aluno SET email=('email4@trolo.lo')
    WHERE mat=4;

INSERT INTO disciplina(cod, descricao) VALUES(01, 'português');
INSERT INTO disciplina(cod, descricao) VALUES(02, 'matemática');

INSERT INTO cursa(mat_aluno, cod_disc, dia)
    VALUES (1, 01, 'terça');

DESC cursa;
SELECT * FROM cursa;

SELECT nome, descricao
    FROM aluno, disciplina, cursa
        WHERE cursa.mat_aluno = aluno.mat
            AND cursa.cod_disc = disciplina.cod;

INSERT INTO cursa(mat_aluno, cod_disc, dia)
    VALUES (4, 01, quarta);

SELECT aluno.nome, disciplina.descricao, cursa.dia
    FROM aluno, disciplina, cursa
        WHERE cursa.mat_aluno = aluno.mat
        AND cursa.cod_disc = disciplina.cod
        AND aluno.nome = "Stevie Ray Vaughan";