Guia de Comandos SQL: ANSI 85, ANSI 92, CAST, CONVERT e Views

SQL ANSI 85

select *
from funcionarios, setores -- produto cartesiano
where funcionarios.setor_id = setores.setor_id -- remove o lixo
order by func_id

SQL ANSI 92

select *
from funcionarios
inner join setores on (funcionarios.setor_id = setores.setor_id)
order by func_id

CAST e CONVERT

select CAST(Codgenero as varchar(5)) + ' - ' + descricao as Titulo from Genero
select CONVERT(varchar(5), Codgenero) + ' - ' + descricao from Genero

SUBSTRING

select SUBSTRING('Termomecanica', 1, 4)

TAMANHO

select Codgenero, descricao, LEN(descricao) as qtdLetras from Genero

Buscar texto da direita e da esquerda

select top 2 RIGHT(descricao, 4) from genero
select top 2 left(descricao, 4) from genero

REPLICATE (repetição de caracter)

select REPLICATE('0', 6 - len(g.codgenero)) + cast(codgenero as varchar), descricao from Genero g

Funções de data

select DAY(getdate()) as dia, MONTH(getdate()) as mes, YEAR(getdate()) as ano

VIEWS

create view vw_Pedido as
select numPed, nome, endereco
from pedido p
inner join Cliente c on (p.codCliente = c.codCliente)

Usando a view junto ao JOIN

select *
from vw_pedido
inner join Itens it on (it.numPed = vw_pedido.numPed)
where qtdProd > 25

Exibir o total mês a mês de locações feitas

select SUM(tot01) as tot01, SUM(tot02) as tot02
from (
select total as tot01, 0 as tot02 from vw_TotalLocado_01
UNION ALL
select 0 as tot01, total as tot02 from vw_TotalLocado_02
) as novaTab