lista 3 de bd | 2015-04-10

-- carga do banco da lista 3
CREATE TABLE pessoas(
    cpf INTEGER(11) PRIMARY KEY,
    nome VARCHAR(255),
    nascimento DATE
);

CREATE TABLE motoristas(
    habilitacao INTEGER(11) PRIMARY KEY,
    cpf INTEGER(11) NOT NULL,
    data_emissao DATE,
    categoria  VARCHAR(3) DEFAULT 'B',
    CHECK(categoria IN ('A','AB','ACC','B','C','D','E')),
    FOREIGN KEY (cpf) REFERENCES pessoas(cpf)
);

CREATE TABLE infracoes(
    codinfracao INTEGER(11) PRIMARY KEY,
    descricao VARCHAR(255),
    valor DECIMAL(10,2),
    pontos INTEGER(2),
    gravidade VARCHAR(10) DEFAULT 'LEVE',
    CHECK (gravidade IN ('LEVE','MÉDIA','GRAVE','GRAVÍSSIMA'))
);

CREATE TABLE multas(
    placa VARCHAR(7),
    habilitacao INTEGER(11),
    data_multa datetime,
    LOCAL VARCHAR(255),
    codinfracao INTEGER(11),
    PRIMARY KEY (placa,data_multa,codinfracao),
    FOREIGN KEY (habilitacao) REFERENCES motoristas(habilitacao)
);

ALTER TABLE pessoas ADD COLUMN endereco VARCHAR(255);
CREATE UNIQUE INDEX motoristas_cpf ON motoristas(cpf);
ALTER TABLE pessoas DROP COLUMN nascimento;
ALTER TABLE motoristas CHANGE data_emissao emissao DATE;

INSERT INTO infracoes(codinfracao,pontos,gravidade) VALUES (1,3,'LEVE');
INSERT INTO infracoes(codinfracao,pontos,gravidade) VALUES (2,7,'MÉDIA');
INSERT INTO infracoes(codinfracao,pontos,gravidade) VALUES (3,11,'GRAVE');
INSERT INTO infracoes(codinfracao,pontos,gravidade) VALUES (4,20,'GRAVÍSSIMA');

INSERT INTO pessoas(cpf,nome) VALUES (1,'Rui');
INSERT INTO pessoas(cpf,nome) VALUES (2,'Rô');
INSERT INTO pessoas(cpf,nome) VALUES (3,'Seu Silva');
INSERT INTO pessoas(cpf,nome) VALUES (4,'Lia');
INSERT INTO pessoas(cpf,nome) VALUES (4,'João');
INSERT INTO pessoas(cpf,nome) VALUES (5,'Maria');
INSERT INTO pessoas(cpf,nome) VALUES (6,'Leo');
INSERT INTO pessoas(cpf,nome) VALUES (7,'Brena');
INSERT INTO pessoas(cpf,nome) VALUES (8,'Xico');
INSERT INTO pessoas(cpf,nome) VALUES (9,'Carlos');
INSERT INTO pessoas(cpf,nome) VALUES (10,'Carol');
INSERT INTO pessoas(cpf,nome) VALUES (11,'Juca');

INSERT INTO motoristas (habilitacao,cpf,categoria) VALUES (1,1,'B');
INSERT INTO motoristas (habilitacao,cpf,categoria) VALUES (2,2,'C');
INSERT INTO motoristas (habilitacao,cpf,categoria) VALUES (3,3,'D');
INSERT INTO motoristas (habilitacao,cpf,categoria) VALUES (4,4,'E');
INSERT INTO motoristas (habilitacao,cpf,categoria) VALUES (5,5,'AB');
INSERT INTO motoristas (habilitacao,cpf,categoria) VALUES (6,6,'A');
INSERT INTO motoristas (habilitacao,cpf,categoria) VALUES (7,7,'ACC');
INSERT INTO motoristas (habilitacao,cpf,categoria) VALUES (9,9,'B');
INSERT INTO motoristas (habilitacao,cpf,categoria) VALUES (10,10,'C');
INSERT INTO motoristas (habilitacao,cpf,categoria) VALUES (11,11,'B');

INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (2,1,'AAA0001','2013-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (3,1,'AAA0001','2014-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (4,1,'AAA0001','2015-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,2,'AAA0002','2012-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (3,2,'AAA0002','2014-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (4,2,'AAA0002','2015-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,3,'AAA0003','2012-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (2,3,'AAA0003','2013-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (3,3,'AAA0003','2014-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (4,3,'AAA0003','2015-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,4,'AAA0004','2012-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (2,4,'AAA0004','2013-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (3,4,'AAA0004','2014-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (4,4,'AAA0004','2015-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,5,'AAA0005','2012-02-02 12:12:12');

INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (2,5,'AAA0005','2012-02-02 12:12:12');

INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (3,5,'AAA0005','2014-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (4,5,'AAA0005','2015-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,6,'AAA0006','2012-02-02 12:12:12');

INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (2,6,'AAA0006','2015-02-02 12:12:12');

INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (3,6,'AAA0006','2014-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (4,6,'AAA0006','2015-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,7,'AAA0007','2012-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (2,7,'AAA0007','2013-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (3,7,'AAA0007','2014-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (4,7,'AAA0007','2015-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,9,'AAA0009','2012-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (2,9,'AAA0009','2013-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (3,9,'AAA0009','2014-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (4,9,'AAA0009','2015-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,10,'AAA0010','2012-02-02 12:12:12');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (2,10,'AAA0010','2013-02-02 12:12:12');

INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (4,10,'AAA0010','2015-02-02 12:12:12');

INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,1,'AAA0001','2015-03-03 13:13:13');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,2,'AAA0002','2015-03-03 13:13:13');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,3,'AAA0003','2015-03-03 13:13:13');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,4,'AAA0004','2015-03-03 13:13:13');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,5,'AAA0005','2015-03-03 13:13:13');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,6,'AAA0006','2015-03-03 13:13:13');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,7,'AAA0007','2015-03-03 13:13:13');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,9,'AAA0009','2015-03-03 13:13:13');
INSERT INTO multas(codinfracao,habilitacao,placa,data_multa) VALUES (1,10,'AAA0010','2015-03-03 13:13:13');


-- 3)
-- insert into pessoas (cpf,nome) values(1,'Rui');
-- delete from pessoas where nome like '%R?';
-- update infracoes set pontos = 1 where gravidade = 'LEVE';

-- 4.a)
SELECT
    nome,endereco
FROM
    pessoas
WHERE
    nome LIKE '%Silva%';

-- 4.b)
SELECT
    nome, cpf
FROM
    pessoas
WHERE
    cpf IN (SELECT
        cpf
    FROM
        motoristas
    WHERE
        categoria = 'B')
ORDER BY nome;

-- 4.c)
SELECT
    nome,habilitacao
FROM
    pessoas
NATURAL JOIN
    motoristas
WHERE
    habilitacao NOT IN (SELECT
        habilitacao
    FROM
        multas
    WHERE
        data_multa BETWEEN '2013-01-01 00:00:00' AND '2013-12-31 23:59:59')

-- 4.d)
SELECT DISTINCT
    nome,cpf
FROM
    pessoas
WHERE
    cpf IN(SELECT
        cpf
    FROM
        motoristas
    WHERE
        habilitacao IN (SELECT
            habilitacao
        FROM
            multas
        WHERE
            codinfracao = 3))

-- 4.e)
SELECT
    p.nome, p.cpf, SUM(i.pontos)
FROM
    pessoas p
NATURAL JOIN
    motoristas
NATURAL JOIN
    multas
NATURAL JOIN
    infracoes i
WHERE
    data_multa BETWEEN '2013-01-01 00:00:00' AND '2013-12-31 23:59:59'
GROUP BY
    p.nome,p.cpf

-- 4.f)
SELECT
    pessoas.nome, pessoas.cpf
FROM
    pessoas
NATURAL JOIN
    motoristas
WHERE
    motoristas.habilitacao IN (SELECT
        habilitacao
    FROM
        (SELECT
            habilitacao, COUNT(codinfracao)
        FROM
            (SELECT DISTINCT
                habilitacao,codinfracao
            FROM
                multas)
        GROUP BY
            habilitacao
        HAVING
            COUNT(codinfracao) = (SELECT
                COUNT(codinfracao)
            FROM
                infracoes)))
ORDER BY
    pessoas.cpf