Os melhores exemplos de SQL

Artigo original: The Best SQL Examples

SQL é a abreviação de Structured Query Language, ou Linguagem de Consulta Estruturada. Ela é usada para todos os tipos de bancos de dados relacionais.

Exemplo de sintaxe básica de SQL

Este guia fornece uma descrição básica e geral da sintaxe das instruções em SQL.

SQL é um padrão internacional (ISO), mas você encontrará muitas diferenças entre as implementações. Este guia usa o MySQL como exemplo. Se você usar um dos vários outros Sistemas de Gerenciamento de Bancos de Dados (conhecidos pela sigla DBMS, em inglês), precisará conferir o manual do DBMS em questão, se necessário.

Do que trataremos

• Uso (define que banco de dados a instrução utilizará)
• Instruções com SELECT e FROM
• Instrução WHERE (AND/OR, IN, BETWEEN e LIKE)
• ORDER BY (ASC, DESC)
• GROUP BY e HAVING

Como usar

O comando abaixo é usado para selecionar o banco de dados contendo as tabelas para suas instruções em SQL:

use fcc_sql_guides_database; -- seleciona o banco de dados da amostra, chamado fcc_sql_guides

Instruções SELECT e FROM

A parte SELECT normalmente é utilizada para determinar quais colunas dos dados você quer exibir nos resultados. Também existem opções que você pode usar para mostrar dados que não são de uma coluna da tabela.

Este exemplo mostra duas colunas selecionadas da tabela "student" e duas colunas calculadas. A primeira das colunas calculadas é um número sem significado. A outra é a data do sistema.

	select studentID, FullName, 3+2 as five, now() as currentDate
    from student;

Instrução WHERE (AND/OR, IN, BETWEEN e LIKE)

A instrução WHERE é usada para limitar o número de linhas retornado.

    select studentID, FullName, sat_score, recordUpdated
    from student
    where (
		studentID between 1 and 5
		or studentID = 8
        or FullName like '%Maximo%'
		);

Neste caso, usar todas as propriedades torna a instrução WHERE bastante ridícula.

Compare o resultado com a instrução SQL acima para seguir a lógica.

Serão apresentadas linhas que:

• Têm studentIDs entre 1 e 5 (incluindo os extremos)
• ou studentID = 8
• ou ainda que tenha "Maximo" no nome

O exemplo a seguir é semelhante, mas especifica ainda mais excluindo os alunos com pontuações de SAT que estiverem entre 1000 e 1400. Esses não serão apresentados:

    select studentID, FullName, sat_score, recordUpdated
    from student
    where (
		studentID between 1 and 5
		or studentID = 8
        or FullName like '%Maximo%'
		)
		and sat_score NOT in (1000, 1400);

ORDER BY (ASC, DESC)

ORDER BY nos oferece uma maneira de ordenar o conjunto de resultados por um ou mais itens da seção SELECT. Abaixo veremos a mesma lista que vemos acima, mas ordenada pelo Full Name (nome completo) dos alunos. A ordem padrão é a ascendente (ASC), mas para ordenar em ordem inversa (descendente), é possível usar DESC.

SELECT studentID, FullName, sat_score
FROM student
ORDER BY FullName DESC;

+-----------+------------------------+-----------+
| studentID | FullName               | sat_score |
+-----------+------------------------+-----------+
|         2 | Teri Gutierrez         |       800 |
|         3 | Spencer Pautier        |      1000 |
|         6 | Sophie Freeman         |      1200 |
|         9 | Raymond F. Boyce       |      2400 |
|         1 | Monique Davis          |       400 |
|         4 | Louis Ramsey           |      1200 |
|         7 | Edgar Frank "Ted" Codd |      2400 |
|         8 | Donald D. Chamberlin   |      2400 |
|         5 | Alvin Greene           |      1200 |
+-----------+------------------------+-----------+
9 rows in set (0.00 sec)

Abaixo vemos a lista NÃO ORDENADA, atual e completa dos alunos, para comparar à lista acima.

SELECT studentID, FullName, sat_score, rcd_updated FROM student;

+-----------+------------------------+-----------+---------------------+
| studentID | FullName               | sat_score | rcd_updated         |
+-----------+------------------------+-----------+---------------------+
|         1 | Monique Davis          |       400 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez         |       800 | 2017-08-16 15:34:50 |
|         3 | Spencer Pautier        |      1000 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey           |      1200 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene           |      1200 | 2017-08-16 15:34:50 |
|         6 | Sophie Freeman         |      1200 | 2017-08-16 15:34:50 |
|         7 | Edgar Frank "Ted" Codd |      2400 | 2017-08-16 15:35:33 |
|         8 | Donald D. Chamberlin   |      2400 | 2017-08-16 15:35:33 |
|         9 | Raymond F. Boyce       |      2400 | 2017-08-16 15:35:33 |
+-----------+------------------------+-----------+---------------------+
9 rows in set (0.00 sec)

Podemos limitar ainda mais. Nesse caso, com studentIDs entre 1 e 5 OU studentID de 8 OU nome completo que contenha "Maximo" E pontuação do SAT que não seja de 1000 ou 1400

    select studentID, FullName, sat_score
    from student
    where (studentID between 1 and 5 -- incluindo os extremos
		or studentID = 8
        or FullName like '%Maximo%')
		and sat_score NOT in (1000, 1400)
	order by FullName DESC;

GROUP BY e HAVING

GROUP BY nos dá uma maneira de combinar linhas e dados agregados. A instrução HAVING é como a instrução WHERE acima, com a exceção de que ela atua nos dados agrupados.

Os dados abaixo pertencem aos dados de contribuições de campanha que usamos em alguns desses guias.

A instrução em SQL que segue responde a pergunta: "quais candidatos receberam o menor número de contribuições (não em termos de dinheiro, mas de contagem (*)) em 2016, mas somente aqueles que tiveram mais de 80 contribuições?"

Ordenar esse conjunto de dados em uma ordem descendente (DESC) coloca os candidatos com o maior número de contribuições na parte de cima da lista.

    select Candidate, Election_year, sum(Total_$), count(*)
    from combined_party_data
    where Election_year = 2016
    group by Candidate, Election_year
    having count(*) > 80
    order by count(*) DESC;

O operador BETWEEN em SQL

O operador BETWEEN é útil, por causa do otimizador de consultas do SQL (ou SQL Query Optimizer). Embora BETWEEN seja, funcionalmente, o mesmo que: x <= elemento <= y, o otimizador de consultas do SQL reconhecerá esse comando mais rápido e terá um código otimizado para executá-lo.

Esse operador é usado em uma instrução WHERE ou em uma instrução GROUP BY/HAVING.

As linhas selecionadas são as que têm um valor maior que o mínimo e menor que o máximo.

É importante lembrar de que os valores inseridos no comando são excluídos do resultado. Obtemos apenas os valores entre eles.

Essa é a sintaxe para usar a função em uma instrução WHERE:

select field1, testField
from table1
where testField between min and max

Aqui temos um exemplo usando a tabela student e a instrução WHERE:

-- sem a instrução WHERE
select studentID, FullName, studentID
from student;
    
-- com a instrução WHERE e com BETWEEN
select studentID, FullName, studentID
from student
where studentID between 2 and 7;

Aqui temos um exemplo de uso na tabela de fundos de campanha com a instrução HAVING. Ele retornará linhas onde a soma das doações para um candidato estavam ente US$3 milhões e US$18 milhões com base na instrução HAVING e na parte GROUP BY da instrução. Veja mais sobre agregação no guia específico.

select Candidate, Office_Sought, Election_Year, format(sum(Total_$),2)
from combined_party_data
where Election_Year = 2016
group by Candidate, Office_Sought, Election_Year
having sum(Total_$) between 3000000 and 18000000
order by sum(Total_$) desc; 

Uma tabela é um grupo de dados armazenado em um banco de dados.

Para criar uma tabela em um banco de dados, use a instrução CREATE TABLE. Você dá um nome à tabela e uma lista de colunas com seus tipos de dados.

CREATE TABLE TABLENAME(Attribute1 Datatype, Attribute2 Datatype,........);

Aqui temos um exemplo de criação da tabela Person:

CREATE TABLE Person(
  Id int not null,
  Name varchar not null,
  DateOfBirth date not null,
  Gender bit not null,
  PRIMARY KEY( Id )
);

No exemplo acima, cada Person (pessoa) tem um Name (nome), uma Date of Birth (data de nascimento) e um Gender (gênero). A coluna Id é a chave que identifica uma pessoa na tabela. Você usa a palavra-chave PRIMARY KEY para configurar uma ou mais colunas como uma chave primária.

Uma coluna pode ser not null ou null, indicando se ela é ou não obrigatória.

Consultas do tipo INSERT são uma maneira de inserir dados em uma tabela. Digamos que tenhamos criado uma tabela usando o seguinte:

CREATE TABLE example_table ( name varchar(255), age int)

example_table

Name|Age
--- | --- 

Agora, para adicionar dados a essa tabela, usaremos INSERT, dessa forma:

INSERT INTO example_table (column1,column2) VALUES ("Andrew",23)

example_table

Name|Age
--- | --- 
Andrew|23

Até mesmo a instrução abaixo funcionará. Sempre é bom, no entanto, especificar quais dados estão sendo inseridos em quais colunas.

INSERT INTO table_name VALUES ("John", 28)

example_table

Name|Age
--- | --- 
Andrew|23
John|28

AND é usado em uma instrução WHERE ou em uma instrução GROUP BY/HAVING para limitar as linhas retornadas pela instrução executada. Use AND quando é necessário atender a mais de uma condição.

Usamos a tabela student para apresentar exemplos.

Aqui temos a tabela student sem usar uma instrução WHERE:

select * from student;

Agora, a instrução WHERE é adicionada para que sejam exibidos apenas os alunos de programação:

select * from student 
where programOfStudy = 'Programming';

Em seguida, a instrução WHERE é atualizada com AND para mostrar resultados dos alunos de programação que também tenham uma pontuação no SAT maior que 800:

select * from student 
where programOfStudy = 'Programming' 
and sat_score > 800;

Abaixo temos um exemplo mais complexo da tabela de contribuições de campanha. O exemplo tem uma instrução GROUP BY com uma instrução HAVING usando um AND para restringir o número de registros retornados a candidatos de 2016 com contribuições entre US$3 milhões e US$18 milhões no total.

select Candidate, Office_Sought, Election_Year, FORMAT(sum(Total_$),2) from combined_party_data
where Office_Sought = 'PRESIDENT / VICE PRESIDENT'
group by Candidate, Office_Sought, Election_Year
 having Election_Year = 2016 and sum(Total_$) between 3000000 and 18000000
order by sum(Total_$) desc;

Perguntas de entrevistas comuns sobre SQL

O que é um inner join em SQL?

Esse é o tipo padrão de join se nenhum join for especificado. Ele retorna todas as linhas nas quais haja ao menos uma correspondência nas duas tabelas.

SELECT * FROM A x JOIN B y ON y.aId = x.Id

O que é um left join em SQL?

Um left join retorna todas as linhas da tabela da esquerda e as linhas correspondentes da tabela da direita. As linhas da tabela da esquerda serão retornadas mesmo sem ter uma correspondência na tabela da direita. As linhas da tabela da esquerda sem correspondência na tabela da direita terão null nos valores da tabela da direita.

SELECT * FROM A x LEFT JOIN B y ON y.aId = x.Id

O que é um right join em SQL?

Um right join retorna todas as linhas da tabela da direita e as linhas correspondentes da tabela da esquerda. Ao contrário do left join, ele retornará todas as linhas da tabela da direita mesmo que não haja correspondência na tabela da esquerda. As linhas da tabela da direita que não têm correspondência na tabela da esquerda terão valores null para as colunas da tabela da esquerda.

SELECT * FROM A x RIGHT JOIN B y ON y.aId = x.Id

O que é um full join em SQL?

Um full join retorna todas as linhas para as quais haja uma correspondência em qualquer uma das tabelas. Assim, se houver linhas na tabela da esquerda sem correspondência na tabela da direita, elas serão incluídas. Se houver linhas na tabela da direita que não têm correspondências na tabela da esquerda, elas também serão incluídas.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName

Qual é o resultado do comando seguinte?

  DROP VIEW view_name

Aqui, teremos um erro, pois não podemos realizar uma operação DML em uma view.

Podemos fazer um rollback após usar um comando ALTER?

Não, pois ALTER é um comando DDL e servidores da Oracle realizam um COMMIT automático quando as instruções DDL são executadas.

Qual é a única restrição que impõe as regras em nível de coluna?

NOT NULL é a única restrição que funciona em nível de coluna.

O que são as pseudocolunas em SQL? Pode fornecer alguns exemplos?

Uma pseudocoluna é uma função que retorna um valor gerado pelo sistema. O motivo pelo qual isso é conhecido assim é porque uma pseudocoluna é um valor atribuído pela Oracle usado no mesmo contexto que uma coluna de banco de dados da Oracle, mas não armazenado em disco.

    ROWNUM, ROWID, USER, CURRVAL, NEXTVAL etc.

Crie a função role_tables_and_views

    CREATE ROLE role_tables_and_views

Dê à função da pergunta anterior os privilégios para se conectar ao banco de dados e os privilégios de criar tabelas e views.

O privilégio para se conectar com o banco de dados é CREATE SESSION. O privilégio para criar tabelas é CREATE TABLE. O privilégio para criar views é CREATE VIEW.

    GRANT Create session, create table, create view TO role_tables_and_views

Crie um usuário my723acct com a senha kmd26pt. Use os dados de usuário e as tablespaces de dados temporários fornecidas pelo PO8 e dê a esse usuário 10M de espaço de armazenamento em dados de usuários e 5M de espaço de armazenamento em temporary_data.

    CREATE USER my723acct IDENTIFIED BY kmd26pt
    DEFAULT TABLESPACE user_data
    TEMPORARY TABLESPACE temporary_data
    QUOTA 10M on user_data QUOTA 5M on temporary_data

Dê à função da pergunta anterior aos usuários anny e rita

    GRANT role_tables_and_views TO anny, rita

Escreva um comando que altere a senha do usuário rita de abcd para dfgh

    ALTER USER rita IDENTIFIED BY dfgh

Os usuários rita e anny não têm privilégios de SELECT na tabela INVENTORY, criada por SCOTT. Escreva um comando para permitir que SCOTT dê aos usuários privilégios de SELECT para essas tabelas.

    GRANT select ON inventory TO rita, anny

O usuário rita foi transferido e agora está indo para outra empresa. Como os objetos que ela criou já não estão sendo usados, escreva um comando para remover esse usuário e todos os seus objetos.

Aqui, a opção CASCADE é necessária para remover todos os objetos do usuário no banco de dados.

   DROP USER rita CASCADE

O usuário rita foi transferido e já não precisa do privilégio que foi dado a ela pela função role_tables_and_views. Escreva um comando para removê-la de seus privilégios anteriormente recebidos, exceto pelo fato de ela ainda poder se conectar ao banco de dados.

    REVOKE select ON scott.inventory FROM rita
    REVOKE create table, create view FROM rita

Escreva uma consulta em SQL para encontrar o enésimo maior salário da tabela

   SELECT TOP 1 Salary
   FROM (
      SELECT DISTINCT TOP N Salary
      FROM Employee
      ORDER BY Salary DESC
      )
    ORDER BY Salary ASC

Instrução CREATE VIEW em SQL

O que é uma view?

Uma view é um objeto de banco de dados que apresenta os dados existentes em uma ou mais tabelas. Views são usadas de modo semelhante às tabelas, mas elas não contêm dados. Elas simplesmente "apontam" para os dados que existem em outros lugares (como tabelas ou outras views, por exemplo).

Por que elas são tão legais?

• Views São uma maneira de limitar os dados apresentados. Por exemplo, o departamento de recursos humanos filtrou informações para poder apresentar somente aquelas que não são confidenciais. As informações confidenciais, neste caso, poderiam ser números da previdência, gênero do funcionário, valor do salário, endereço residencial etc.
• Dados complexos em mais de uma tabela podem ser combinados em uma única "view". Isso pode facilitar a vida para analistas de empresas e programadores.

Dicas de segurança importantes

• Views são gerenciadas pelo sistema. Quando os dados nas tabelas relacionadas são alterados, adicionados ou atualizados, a view é atualizada pelo sistema. Queremos usá-las apenas quando necessário para gerenciar o uso dos recursos do sistema.
• Em MySQL, alterações ao design da tabela (ou seja, colunas novas ou excluídas) realizados APÓS a criação de uma view não são atualizados na própria view. A view teria de ser atualizada ou recriada.
• Views são um dos quatro tipos de objeto de bancos de dados padrão. Os outros são tabelas, procedimentos armazenados e funções.
• Views geralmente podem ser tratadas como tabelas, mas as atualizações são limitadas ou não disponíveis quando a view contém mais de uma tabela.
• Há vários outros detalhes sobre as views que estão além do escopo desse guia introdutório. Aproveite seu tempo para ler o manual dos gerenciadores de bancos de dados e divirta-se com esse objeto do SQL poderoso.

Sintaxe da instrução Create View (MySQL)

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
	[WITH [CASCADED | LOCAL] CHECK OPTION]

Este guia tratará da parte da instrução que vemos abaixo…

CREATE
    VIEW view_name [(column_list)]
    AS select_statement

Exemplo de criação de views a partir da tabela student

Observações:

• O nome da view tem um "v" no início. Recomenda-se que o nome da view indique que ela é uma view de alguma maneira para facilitar a vida de programadores e administradores de bancos de dados. Sua TI deve ter as próprias regras para nomear objetos.
• As colunas na view são limitadas pela instrução SELECT e pelas linhas de dados pela instrução WHERE.
• O caractere "`" ao redor dos nomes das views são obrigatórios, por causa do "-" que aparece nos nomes. O MySQL relata um erro se não estiverem lá.

create view `programming-students-v` as
select FullName, programOfStudy 
from student 
where programOfStudy = 'Programming';

select * from `programming-students-v`;

Exemplo de uso de uma view para combinar dados de mais de uma tabela

Uma tabela sobre a demografia dos alunos foi adicionada ao banco de dados para demonstrar esse uso. Essa view combinará as tabelas.

Observações:

• Para "unir" as tabelas por meio de um join, elas precisam ter campos em comum (geralmente, as chaves primárias) que identificam cada linha com exclusividade. Neste caso, falamos da studentID. Se quiser saber mais sobre o assunto, consulte o guia sobre joins em SQL (em inglês).
• Observe o "alias" dado a cada tabela ("s" para student - aluno - e "sc" para student contact - contato do aluno). É uma ferramenta que serve para encurtar os nomes das tabelas e facilitar a identificação de qual tabela está sendo usada. É mais fácil do que digitar longos nomes de tabela repetidamente. Neste exemplo, foi necessário porque o nome de coluna studentID é igual nas duas tabelas. O sistema apresentaria um "erro de nome ambíguo de coluna" se não especificássemos que tabela usar.

Como ocorre com tudo o que está relacionado a SQL, há MUITO MAIS do que é oferecido nesse guia introdutório. Espero que este guia dê a você pelo menos o suficiente para começar. Consulte o manual para seu gerenciador de bancos de dados e divirta-se experimentando com opções diferentes por sua conta.