bancodedados.sql

create table if not exists corretora (
    `codigo` integer auto_increment,
    `nome` varchar(50) not null,
    `usuario` varchar(50) not null unique key,
    `senha` varchar(50) not null,
    constraint `pk_corretora` primary key(`codigo`)
);

create table if not exists `cliente` (
    `codigo` integer auto_increment not null,
    `nome` varchar(20) not null,
    `sobrenome` varchar(50) not null,
    constraint `pk_cliente` primary key(`codigo`)
);

create table if not exists `acao` (
    `codigo` integer auto_increment,
    `cliente` integer not null,
    `corretora` integer not null,
    `nome` varchar(50) not null,
    `valor` numeric(10,2) default 0,
    `tipo_acao` char(1),
    `data` timestamp not null default current_timestamp on update current_timestamp,
    constraint identificador_acao primary key (`codigo`),
    constraint tipo_acao check (`tipo` in ('c', 'v')),
    constraint fk_cli_acao foreign key (`cliente`) references `cliente`(`codigo`),
    constraint fk_cor_acao foreign key (`corretora`) references `corretora`(`codigo`)
);

-- vale apenas no dia atual
create table if not exists `ordem_compra` (
    `codigo` integer auto_increment,
    `comprador` integer not null,
    `acao` integer not null,
    `valor` numeric(10,2) default 1,    
    `data` timestamp not null default current_timestamp on update current_timestamp,
    constraint identificador_acao_comprada primary key(`codigo`),
    constraint fk_cli_acao_comprada foreign key (`comprador`) references `cliente`(`codigo`),
    constraint fk_aca_acao_comprada foreign key (`acao`) references `acao`(`codigo`)
);


--------------------------------------------------------------------------------
                -- TABELAS VIRTUAIS --

-- retorna todas as ações de venda de hoje
create view acao_hoje as
    select
        acao.codigo,
        acao.data,
        acao.nome as `acao`,
        acao.valor,
        acao.tipo_acao as `tipo`,
        cliente.nome as `cliente`,
        corretora.nome as `corretora`
    from
        `acao`
    inner join
	cliente on acao.cliente = cliente.codigo
    inner join
	corretora on acao.corretora = corretora.codigo
    where
       acao.data > (DATE_SUB(CURDATE(), INTERVAL 1 DAY))
    group by
        acao.codigo;

-- retorna todas as compras feitas hoje
create view acao_compra_hoje as 
    select
        codigo, `data`, acao, valor, cliente, corretora
    from
        acao_hoje
    where
        `tipo` = 'c';


-- retorna todas as vendas feitas hoje
create view acao_venda_hoje as 
    select
        codigo, `data`, acao, valor, cliente, corretora
    from
        acao_hoje
    where
        `tipo` = 'v';

create view ordem_compra_hoje as
    select
        ordem_compra.codigo as `codigo`,
        cliente.codigo as `cliente_codigo`,
        cliente.nome as `cliente_nome`,
        acao.codigo as `acao_codigo`,
        acao.nome as `acao_nome`,
        ordem_compra.valor as `valor_ordem`
    from
        ordem_compra
    inner join
		cliente on ordem_compra.comprador = cliente.codigo
    inner join
		acao on ordem_compra.acao = acao.codigo
    where
        `ordem_compra`.`data` > (DATE_SUB(CURDATE(), INTERVAL 1 DAY))