Comandos SQL Essenciais: DDL, DML, Consultas, Joins e Subconsultas

DDL (Data Definition Language): comandos para definir ou modificar a composição das tabelas, apagar tabelas, criar índices, definir views, especificar direitos de acesso a tabelas e views;
DML (Interactive Data Manipulation Language): inclui uma linguagem de consulta baseada em álgebra relacional e em cálculo relacional sobre registros; inclui também comandos para inserir, apagar e modificar registros na base de dados;

Tipos de dados

inteiro (bigint, int, smallint) | bit (bit) | real (decimal, float, real) | data e hora (datetime, smalldatetime) | string (char, varchar, text, nchar, nvarchar)

Criação de banco de dados

use master -- vai para o banco de dados principal do SQL Server
IF DB_ID ('DBLocadora') IS NOT NULL -- verifica se o banco de dados já existe
DROP DATABASE DBLocadora -- se já existir, apaga
CREATE DATABASE DBLocadora -- cria o banco de dados
ON (NAME = dbLocadora_dados, FILENAME = 'd:\BD\dbLocadora_dados.mdf')
LOG ON (NAME = dbLocadora_Log, FILENAME = 'd:\BD\dbLocadora_Log.ldf')

Criação de tabela

CREATE TABLE fitas (
codigo int PRIMARY KEY IDENTITY(1,1), descricao varchar(20), data_cadastro datetime, valor decimal(10,2) NOT NULL)

Comandos de manipulação de dados

INSERT INTO tabela (campo1, campo2, campon) VALUES (valor1, valor2, valorn)

DELETE FROM tabela WHERE condição

UPDATE tabela SET campo1 = valor1, campo2 = valor2, campon = valorn WHERE condição (sem WHERE vai alterar todos os registros e no DELETE apagar todos)

SELECT * FROM tabela WHERE condição GROUP BY campo(s) HAVING condição ORDER BY campo(s)

Alias (apelido)

AS "código do funcionário" — literal que identifica um campo, uma função ou uma tabela.

WHERE (restrições/filtro)

Com esta cláusula é possível fazer restrição dos registros a serem manipulados. Também pode ser usada para outros comandos do SQL.

BETWEEN

WHERE x BETWEEN x AND y — o operador BETWEEN determina a faixa de valores aceita em uma pesquisa.

LIKE

WHERE campo LIKE 'a%' (inicia com a), '%a' (termina com a), '%a%' (contém a). Faz a comparação com uma parte de uma literal; os caracteres % e _ representam a parte a ser desconsiderada.

IN

WHERE campo IN (1, 5, 9) — o operador IN determina os valores fixos aceitos em uma pesquisa.

DISTINCT (valores únicos)

SELECT DISTINCT — o operador DISTINCT elimina todas as repetições.

TOP (limitando a quantidade de registros)

SELECT TOP 5 * FROM tabela — retorna apenas 5 registros.

ORDER BY

ORDER BY campo DESC ou ASC — opcional. Se quiser exibir os dados na ordem classificada, o padrão é ascendente (A a Z, 0 a 9).

Funções de agregação

COUNT (contador de registros): SELECT COUNT([DISTINCT] campo ou *) — retorna a quantidade de registros correspondentes.

SUM (somatória de valores): SELECT SUM(campo) — faz a somatória dos valores de um campo.

AVG (média de valores): SELECT AVG(campo) — faz a média dos valores de um campo.

MIN e MAX (menor e maior valor): SELECT MIN(func_salario), MAX(func_salario).

GROUP BY

Agrupa um conjunto de linhas selecionadas em um conjunto de linhas de resumo pelos valores de uma ou mais colunas ou expressões.

HAVING

HAVING define condições na cláusula GROUP BY, de modo semelhante ao modo como o WHERE interage com o SELECT.

Joins

INNER JOIN (junção interna): uma tupla é incluída no resultado somente se existir uma tupla correspondente na outra relação.

OUTER JOIN (junção externa): relacionando duas tabelas r e s, pode-se manter todas as tuplas em r, em s ou em ambas no resultado da junção.

LEFT JOIN (junção externa à esquerda): outer join com r à esquerda. Se nada for encontrado em s, o resultado será preenchido com valores NULL.

RIGHT JOIN (junção externa à direita): outer join com s à direita.

FULL OUTER JOIN (junção externa total): mantém os valores da esquerda e da direita, preenchendo com NULL conforme a necessidade.

SELF JOIN (auto junção): é feito um join de uma tabela com ela mesma.

UNION

Combina os resultados de duas ou mais consultas em um único conjunto de resultados. UNION ALL incorpora todas as linhas no resultado, inclusive as repetidas.

Subconsultas (subqueries ou consultas encadeadas)

Uma subconsulta é uma consulta aninhada em uma instrução SELECT, INSERT, UPDATE ou DELETE.

Ex. 1: utilizando uma subconsulta para retornar um campo adicional.
Ex. 2: utilizando uma subconsulta para restringir os registros retornados.
Ex. 3: aplicação da cláusula TOP e dos operadores IN e NOT IN em subconsultas.

Visões (views)

CREATE VIEW ... AS (comando SQL) — uma view é uma instrução que retorna dados e é salva no banco de dados.

Exemplos práticos

-- Quantos clientes moram em cada rua
SELECT COUNT(*) AS qtd, endereco FROM cliente GROUP BY endereco

-- Exiba a quantidade de filmes que cada gênero possui
SELECT COUNT(*) AS qtd, codgenero FROM midias GROUP BY codgenero

-- Exiba a quantidade de fitas que cada gênero possui
SELECT SUM(quantidade) AS qtd, codgenero FROM midias GROUP BY codgenero

-- Exiba a quantidade de mídias que cada gênero possui, somente para os gêneros que possuem mais que 3 mídias cada
SELECT COUNT(*) AS qtd, codgenero FROM midias GROUP BY codgenero HAVING COUNT(*) > 3

-- Exiba quantas locações cada cliente fez
SELECT codcli, COUNT(*) AS locacoes FROM locacao GROUP BY codcli

-- Exiba o total gasto por cliente em locações
SELECT codcli, SUM(valortotalloc) AS totalgasto FROM locacao GROUP BY codcli ORDER BY totalgasto

-- Exiba somente os códigos dos clientes que gastaram mais que 27 reais
SELECT codcli, SUM(valortotalloc) AS totalgasto FROM locacao GROUP BY codcli HAVING SUM(valortotalloc) > 27 ORDER BY totalgasto

-- Exiba a quantidade de locações que cada mês possui
SELECT COUNT(*) AS qtd, MONTH(dataloc) AS mes FROM locacao GROUP BY MONTH(dataloc)

-- Exiba quantas mídias cada locação possui
SELECT COUNT(*) AS qtd, codlocacao FROM itenslocacao GROUP BY codlocacao ORDER BY codlocacao

-- Exiba somente as locações que possuem mais que 3 filmes
SELECT COUNT(*) AS qtd, codlocacao FROM itenslocacao GROUP BY codlocacao HAVING COUNT(*) > 3 ORDER BY codlocacao

-- Exiba quantas mídias foram devolvidas por data, desconsiderando as mídias que não foram devolvidas
SELECT COUNT(*) AS qtd, datadev FROM itenslocacao WHERE datadev IS NOT NULL GROUP BY datadev

-- Selecione o maior e o menor código de funcionário
SELECT MIN(f.func_id) AS menor, MAX(f.func_id) AS maior FROM funcionarios f

-- Selecione a média salarial
SELECT AVG(f.func_salario) AS media FROM funcionarios f

-- Selecione todos os campos apenas dos 5 primeiros funcionários ordenados por ordem decrescente de salário
SELECT TOP 5 * FROM funcionarios ORDER BY func_salario DESC

-- Selecione todos os setores cujo nome termina com a letra a
SELECT * FROM setores WHERE setor_nome LIKE '%a'

-- Selecione o setor_id da tabela funcionários, de forma distinta
SELECT DISTINCT setor_id FROM funcionarios

-- Selecione a média salarial dos funcionários que são gerentes
SELECT AVG(f.func_salario) AS media FROM funcionarios f WHERE f.gerente_id IS NULL

-- Selecione o código do gerente e a quantidade de funcionários que trabalham para ele
SELECT gerente_id, COUNT(*) AS quantidade FROM funcionarios WHERE gerente_id IS NOT NULL GROUP BY gerente_id

-- Selecione todos os funcionários que nasceram antes de 1974 e que ganham mais de 4000,00, ou aqueles que trabalham no setor 1 e ganham mais de 1000,00
SELECT * FROM funcionarios f WHERE (YEAR(f.func_datanasc) < 1974 AND f.func_salario > 4000) OR (f.setor_id = 1 AND f.func_salario > 1000)

-- Exemplo do uso de JOIN e subconsulta
SELECT f.func_nome AS nome_funcionario, ISNULL(g.func_nome, 'gerente') AS nome_gerente FROM funcionarios f LEFT JOIN funcionarios g ON (f.gerente_id = g.func_id)

SELECT f.func_nome AS nome_funcionario, (SELECT g.func_nome FROM funcionarios g WHERE f.gerente_id = g.func_id) AS nome_gerente FROM funcionarios f

GROUP BY e HAVING com funcionários

SELECT setor_id, COUNT(*) AS "total de funcionários no setor" FROM funcionarios WHERE setor_id IS NOT NULL GROUP BY setor_id

SELECT setor_id, COUNT(*) AS "total de funcionários no setor" FROM funcionarios WHERE setor_id IS NOT NULL GROUP BY setor_id HAVING COUNT(*) > 4

Exemplos de JOINs

SELECT funcionarios.*, setores.setor_nome FROM funcionarios INNER JOIN setores ON funcionarios.setor_id = setores.setor_id

SELECT funcionarios.*, setores.setor_nome FROM funcionarios LEFT OUTER JOIN setores ON funcionarios.setor_id = setores.setor_id

SELECT funcionarios.*, setores.setor_nome FROM funcionarios RIGHT OUTER JOIN setores ON funcionarios.setor_id = setores.setor_id

SELECT funcionarios.*, setores.setor_nome FROM funcionarios FULL OUTER JOIN setores ON funcionarios.setor_id = setores.setor_id

SELECT funcionarios.*, gerentes.func_nome AS "gerente_nome" FROM funcionarios INNER JOIN funcionarios AS "gerentes" ON gerentes.func_id = funcionarios.gerente_id

UNION

SELECT funcionarios.func_id AS "codigo", funcionarios.func_nome AS "nome" FROM funcionarios WHERE funcionarios.func_nome LIKE 'a%' UNION SELECT setores.setor_id, setores.setor_nome FROM setores

Subconsultas

SELECT funcionarios.*, (SELECT gerentes.func_nome FROM funcionarios AS "gerentes" WHERE gerentes.func_id = funcionarios.gerente_id) AS "gerente_nome" FROM funcionarios WHERE gerente_id IS NOT NULL

SELECT setores.*, (SELECT COUNT(*) FROM funcionarios WHERE funcionarios.setor_id = setores.setor_id) AS "total de funcionários" FROM setores

SELECT setores.*, (SELECT COUNT(*) FROM funcionarios WHERE funcionarios.setor_id = setores.setor_id) AS "total de funcionários" FROM setores WHERE (SELECT COUNT(*) FROM funcionarios WHERE funcionarios.setor_id = setores.setor_id) > 0

SELECT * FROM setores WHERE setor_id IN (SELECT TOP 1 funcionarios.setor_id FROM funcionarios WHERE funcionarios.setor_id = setores.setor_id)