<file_sep>-- inserindo registro na tabela mesas
insert into mesas (mesa_codigo, mesa_situacao, data_criacao, data_atualizacao) values ('00001', 'A', '01/01/2016', '01/01/2016');
insert into mesas (mesa_codigo, mesa_situacao, data_criacao, data_atualizacao) values ('00002', 'A', '01/01/2016', '01/01/2016');
select * from mesas;

-- inserindo registros na tabela funcionarios
insert into funcionarios (
	funcionario_codigo,
	funcionario_nome,
	funcionario_situacao,
	funcionario_comissao,
	funcionario_cargo,
	data_criacao
) values (
	'00001',
	'<NAME>',
	'A',
	5,
	'GERENTE',
	'01/01/2016'
);
insert into funcionarios (
	funcionario_codigo,
	funcionario_nome,
	funcionario_situacao,
	funcionario_comissao,
	funcionario_cargo,
	data_criacao
) values (
	'00002',
	'Souza',
	'A',
	2,
	'GARÇOM',
	'01/01/2016'
);
select * from funcionarios;

-- Inserindo registros na tabela produtos
insert into produtos (
	produto_codigo,
	produto_nome,
	produto_valor,
	produto_situacao,
	data_criacao,
	data_atualizacao
) values (
	'001',
	'REFRIGERANTE',
	10,
	'A',
	'01/01/2016',
	'01/01/2016'
);

insert into produtos (
	produto_codigo,
	produto_nome,
	produto_valor,
	produto_situacao,
	data_criacao,
	data_atualizacao
) values (
	'002',
	'AGUA',
	3,
	'A',
	'01/01/2016',
	'01/01/2016'
);

insert into produtos (
	produto_codigo,
	produto_nome,
	produto_valor,
	produto_situacao,
	data_criacao,
	data_atualizacao
) values (
	'003',
	'PASTEL',
	7,
	'A',
	'01/01/2016',
	'01/01/2016'
);
select * from produtos;

-- inserir registros na tabela de vendas
insert into vendas (
	funcionario_id,
	mesa_id,
	venda_codigo,
	venda_valor,
	venda_total,
	venda_desconto,
	venda_situacao,
	data_criacao,
	data_atualizacao
) values (
	2,
	1,
	'0001',
	'20',
	'20',
	'0',
	'A',
	'01/01/2016',
	'01/01/2016'
);

insert into vendas (
	funcionario_id,
	mesa_id,
	venda_codigo,
	venda_valor,
	venda_total,
	venda_desconto,
	venda_situacao,
	data_criacao,
	data_atualizacao
) values (
	2,
	2,
	'0002',
	'21',
	'21',
	'0',
	'A',
	'01/01/2016',
	'01/01/2016'
);

select * from vendas;

-- inserir registro na tabela itens_vendas
insert into itens_vendas (
	produto_id,
	vendas_id,
	item_valor,
	item_quantidade,
	item_total,
	data_criacao,
	data_atualizacao
) values (
	1,
	1,
	10,
	2,
	20,
	'01/01/2016',
	'01/01/2016'
);

insert into itens_vendas (
	produto_id,
	vendas_id,
	item_valor,
	item_quantidade,
	item_total,
	data_criacao,
	data_atualizacao
) values (
	1,
	2,
	7,
	3,
	21,
	'01/01/2016',
	'01/01/2016'
);

select * from itens_vendas;<file_sep>-- || para concatenar strings
select funcionario_codigo || ' ' || funcionario_nome
	from funcionarios
	where id = 1;
	
select (funcionario_codigo || 8 || funcionario_nome)
	from funcionarios
	where id = 1;

-- Contando os caracteres de uma string
select funcionario_nome nome from funcionarios where id = 2;
select char_length(funcionario_nome) from funcionarios where id = 2;

-- Transformando letras minúsculas em maúsculas com upper(string)
select upper(funcionario_nome) from funcionarios;
select upper('livro postgresql');

-- Transforma apenas as primeiras letras de cada palavra
select initcap('livro postgresql');

-- Transforma maiúsculas em minúsculas com lower(string)
select funcionario_nome from funcionarios;
select lower(funcionario_nome) from funcionarios;

-- Substituindo string com overlay() e extraindo com substring()
select overlay(funcionario_nome placing '******' from 3 for 5) from funcionarios where id = 1;

-- Extração de um techo da string
select substring(funcionario_nome from 3 for 5) from funcionarios where id = 1;

-- Localizando uma string position
select funcionario_nome from funcionarios where id = 1;
select position('cinius' in funcionario_nome) from funcionarios where id = 1;<file_sep>insert into funcionarios (
	funcionario_codigo,
	funcionario_nome,
	funcionario_situacao,
	funcionario_comissao,
	funcionario_cargo,
	data_criacao
) values (
	'0100',
	'<NAME>',
	'A',
	'2',
	'GARÇOM',
	'01/03/2016'
);

insert into funcionarios (
	funcionario_codigo,
	funcionario_nome,
	funcionario_situacao,
	funcionario_comissao,
	funcionario_cargo,
	data_criacao
) values (
	'0101',
	'<NAME>',
	'A',
	2,
	'GARÇOM',
	'01/03/2016'
);

insert into funcionarios(
	funcionario_codigo,
	funcionario_nome,
	funcionario_situacao,
	funcionario_comissao,
	funcionario_cargo,
	data_criacao
) values (
	'0102',
	'<NAME>',
	'A',
	2,
	'GARÇOM',
	'01/03/2016'
);

insert into funcionarios(
	funcionario_codigo,
	funcionario_nome,
	funcionario_situacao,
	funcionario_comissao,
	funcionario_cargo,
	data_criacao
) values (
	'0103',
	'<NAME>',
	'A',
	2,
	'GARÇOM',
	'01/03/2016'
);

insert into funcionarios(
	funcionario_codigo,
	funcionario_nome,
	funcionario_situacao,
	funcionario_comissao,
	funcionario_cargo,
	data_criacao
) values ( 
	'0104',
	'<NAME>',
	'A',
	2,
	'GARÇOM',
	'01/03/2016'
);

insert into funcionarios(
	funcionario_codigo,
	funcionario_nome,
	funcionario_situacao,
	funcionario_comissao,
	funcionario_cargo,
	data_criacao
) values (
	'0105',
	'<NAME>',
	'A',
	2,
	'GARÇOM',
	'01/03/2016'
);

insert into funcionarios(
	funcionario_codigo,
	funcionario_nome,
	funcionario_situacao,
	funcionario_comissao,
	funcionario_cargo,
	data_criacao
) values (
	'0106',
	'<NAME>',
	'A',
	2,
	'GARÇOM',
	'01/03/2016'
);

select * from funcionarios;

select * from funcionarios where funcionario_nome ilike 'VINICIUS%';

select * from funcionarios where funcionario_nome ilike '%souza%';<file_sep>-- consultar os registros mesas
select mesa_codigo, mesa_situacao from mesas;
select * from mesas where mesa_codigo = '00002';

-- ATUALIZANDO REGISTROS
select * from produtos;
update produtos set produto_valor = 4 where id = 2;

select * from vendas;
update vendas set mesa_id = 1 where id = 2;

-- DELETANDO REGISTROS
select * from mesas;
delete from mesas where id = 2;<file_sep>-- criando uma consulta relacionado duas tabelas com join
select distinct funcionario_nome 
	from funcionarios, vendas
	where funcionarios.id = vendas.funcionario_id
	order by funcionario_nome;


select distinct funcionario_nome from funcionarios, vendas order by funcionario_nome;

select distinct funcionario_nome
	from funcionarios
	inner join vendas
	on (funcionarios.id = vendas.funcionario_id)
	order by funcionario_nome;

select distinct funcionario_nome
	from funcionarios f
	join vendas v
	on (f.id = v.funcionario_id)
	order by funcionario_nome;

select funcionario_nome, v.id
	from funcionarios f
	left join vendas v
	 on f.id = v.funcionario_id 
	order by funcionario_nome desc;

select v.id, v.venda_total, funcionario_nome from vendas v
	right join funcionarios f
	on v.funcionario_id = f.id
	order by v.venda_total;



create or replace view vendas_do_dia as 
	select distinct produto_nome,
		sum(v.venda_total)
	from produtos p, itens_vendas iv, vendas v
	where p.id = iv.produto_id and v.id = iv.vendas_id and v.data_criacao = '01/01/2016'
	group by produto_nome;
	
select * from vendas_do_dia;
select * from vendas_do_dia where produto_nome = 'PASTEL';

create or replace view produtos_vendas as
	select p.id PRODUTO_ID,
		p.produto_nome PRODUTO_NOME,
		v.id VENDA_ID,
		iv.id ITEM_ID,
		iv.item_valor ITEM_VALOR,
		v.data_criacao DATA_CRIACAO
	from produtos p, vendas v, itens_vendas iv
	where v.id = iv.vendas_id
	and p.id = iv.produto_id
	order by data_criacao desc;
	
select * from produtos_vendas;

select produto_nome from produtos_vendas where data_criacao = '01/01/2016';<file_sep>
-- relacionando duas tabelas por meio de uma subconsulta, na qual buscaremos os funcionários que possuem vínculo com uma ou mais vendas
select funcionario_nome from funcionarios where id in (select funcionario_id from vendas);

-- realizando uma consulta apenas buscando as vendas realizadas em 2016
-- passando o ano da data atual por parâmetro
select funcionario_nome from funcionarios where id in(select funcionario_id from vendas where date_part('year', data_criacao) = '2016');
select v.id, f.funcionario_nome from vendas v inner join funcionarios f on v.funcionario_id = f.id;


-- realizando uma consulta trazendo apenas os nomes distintos dos funcionarios e ordenando em ordem alfabética
select distinct funcionario_nome
	from funcionarios 
		where id in(select funcionario_id 
			from vendas v
				where date_part('year', data_criacao) = '2016')
		order by funcionario_nome desc;
		
select distinct produto_nome
	from produtos
where id in (select id from itens_vendas v);

select * from itens_vendas;<file_sep>-- criando toas sequencias (ids com autoincremento)
create sequence mesa_id_seq;
create sequence vendas_id_seq;
create sequence itens_vendas_id_seq;
create sequence produtos_id_seq;
create sequence funcionario_id_seq;
create sequence comissoes_id_seq;

-- vincular cada sequence com suas respectivas tabelas
alter table mesas alter column id set default nextval('mesa_id_seq');
alter table vendas alter column id set default nextval('vendas_id_seq');
alter table itens_vendas alter column id set default nextval('itens_vendas_id_seq');
alter table produtos alter column id set default nextval('produtos_id_seq');
alter table funcionarios alter column id set default nextval('funcionario_id_seq');
alter table comissoes alter column id set default nextval('comissoes_id_seq');

-- deletando uma sequence vinculado a uma tabela
drop sequence funcionario_id_seq cascade;

-- deletando uma sequence não vinculada a uma tabela
drop sequence funcionario_id_seq;

-- ALTERANDO TABELA

-- inserir um novo campo
alter table comissoes add column data_pagamento int;

-- alterando o tipo da coluna
-- por meio da exclusao da coluna e criando uma nova
alter table comissoes drop column data_pagamento;
alter table comissoes add column data_pagamento timestamp;

-- por meio do alter
alter table comissoes alter column data_pagamento type timestamp using data_pagamento_timestamp;

-- alterando o nome de uma coluna
ALTER TABLE vendas RENAME COLUMN vanda_valor TO venda_valor;
<file_sep>-- excluir uma table
drop table comissoes;

-- tabela para gravar registro do caculo das comissoes sem uma chave primaria e sem chave estrangeira
create table comissoe(
	id int not null,
	funcionario_id int,
	comissao_valor real,
	comissao_situacao varchar(1) default 'A',
	data_criacao timestamp,
	data_atualizacao timestamp
);

-- renomear uma table
ALTER TABLE public.comissoe RENAME TO comissoes;

-- criando uma constraints PK (chave primaria)
alter table comissoes add constraint comissoes_pkey primary key(id);

-- criando uma constraints FK (chave estrangeiras)
alter table comissoes add foreign key (funcionario_id) references funcionarios(id);

-- deletando uma constraint
-- removendo FK da tabela de comissoes (funcionario_id) que se relacionova com a tabela de funcionarios(id)
alter table comissoes drop constraint comissoes_funcionario_id_fkey;

-- criar uma constraint que dira para o banco testa o campo venda_total é maior que zero (positivo) a cada novo registro
alter table vendas add check ( venda_total > 0 );

-- criar uma cosntraint para não permitir inserir nulo no campo
alter table funcionarios add check ( funcionario_nome <> null );<file_sep>-- tabela para gravar registro das mesas
create table mesas (
	id int not null primary key,
	mesa_codigo varchar(20),
	mesa_situacao varchar(1) default 'A',
	data_criacao timestamp,
	data_atualizacao timestamp
);


-- tabela para gravar registro dos funcionarios
create table funcionarios(
	id  int not null primary key,
	funcionario_codigo varchar(20),
	funcionario_nome varchar(100),
	funcionario_situacao varchar(1) default 'A',
	funcionario_comissao real,
	funcionario_cargo varchar(30),
	data_criacao timestamp,
	data_atualizacao timestamp
);


-- tabela para gravar registro das vendas
create table vendas(
	id int not null primary key,
	funcionario_id int references funcionarios (id),
	mesa_id int references mesas(id),
	venda_codigo varchar(20),
	vanda_valor real,
	venda_total real,
	venda_desconto real,
	venda_situacao varchar(1) default 'A',
	data_criacao timestamp,
	data_atualizacao timestamp
);

-- tabela para gravar registro dos produtos
create table produtos(
	id int not null primary key,
	produto_codigo varchar(20),
	produto_nome varchar(60),
	produto_valor real,
	produto_situacao varchar(1) default 'A',
	data_criacao timestamp,
	data_atualizacao timestamp
);

-- tabela para gravar registro dos itens das vendas
create table itens_vendas(
	id int not null primary key,
	produto_id int not null references produtos(id),
	vendas_id int not null references vendas(id),
	item_valor real,
	item_quantidade int,
	item_total real,
	data_criacao timestamp,
	data_atualizacao timestamp
);

-- tabela para gravar registro do c?culo das comissoes
create table comissoes(
	id int not null primary key,
	funcionario_id int references funcionarios(id),
	comissao_valor real,
	comissao_situacao varchar(1) default 'A',
	data_criacao timestamp,
	data_atualizacao timestamp
);