Eng. de Software | Projeto e otimização de banco de dado | Linguagem SQL – Parte 2
Introdução
A linguagem de manipulação de dados é usada para modificar registros em um banco de dados. As seguintes tabelas exemplo serão utilizadas.
Curso
Disciplina
Professor
Grade
A linguagem de manipulação de dados é usada para modificar registros em um banco de dados. As seguintes tabelas exemplo serão utilizadas.
Inclusão de tuplas em uma tabela
Inserir dados em uma tabela significa preencher uma linha de determinada tabela com dados correspondentes aos tipos determinados naquela tabela. Essa inserção de dados deve seguir as regras de integridade da tabela, assim como respeitar as regras de chave primária e chaves estrangeiras estabelecidas na tabela.
Sintaxe
INSERT
INTO nome_tabela (coluna1, coluna2, …. , colunaN)
VALUES (valor1, valor2, … , valorN)
Exemplo: Inserir uma linha na tabela Professor
INSERT
INTO Professor (CodP, NomeP, CidadeP, TituloP)
VALUES (‘P1′, ‘Joaquim’, ‘Rib Preto’, ‘Mestre’)
Os valores valor1 , valor2 etc. seguem a ordem dos campos da tabela, sendo utilizado valor vazio (‘ ‘) ou a sentença NULL para campos que não necessitem de preenchimento. Dados de tipo numérico podem ser escritos sem a necessidade de aspas simples. Dados do tipo caractere (como char e varchar) devem ser escritos entre aspas simples.
Atualização dos dados de uma tabela
Alterar dados em uma tabela significa atualizar um dado de uma determinada tabela por outro dado do mesmo tipo.
O comando Update pode ser realizado sem o WHERE. Neste caso todas as linhas da tabela serão atualizadas com o valor determinado no comando. Para os casos onde se necessite atualizar apenas linhas que cumpram determinada condição, essa condição é estabelecida com a inclusão do comando WHERE.
Sintaxe
UPDATE nome_tabela
SET nome_coluna = valor, …..
[WHERE (condição de localização)
Exemplo: Alterar o valor da mensalidade do curso de Ciência da Computação para 650,00.
UPDATE Curso
SET MensC = 650
WHERE NomeC = ‘Ciência Comp’
Remoção de dados de uma tabela
Apagar dados em uma tabela significa eliminar uma ou mais linhas de uma determinada tabela. Para isso utilizamos a instrução DELETE.
O comando Delete pode ser realizado sem o WHERE. Neste caso todas as linhas da tabela determinada serão excluídas. Utilizamos WHERE quando desejamos
eliminar os registros que obedeçam a certa condição.
Sintaxe
DELETE FROM nome_tabela
WHERE (condição de localização)
Exemplo: Remover da tabela Professor todos os professores que têm título de Doutor.
DELETE FROM Professor
WHERE TituloP = ‘Doutor’
Conceito de transação
Uma TRANSAÇÃO é uma unidade de execução programada que acessa e provavelmente atualiza vários itens de dados. Uma transação deve acessar uma base de dados consistente. Durante a execução da transação é possível que a base de dados passe por um estado de inconsistência. Quando a transação é confirmada (COMMIT), a base de dados deverá estar consistente.
O gerenciamento de transações lida com 2 situações principais:
- Falhas de vários tipos: hardware, energia, travamento de aplicações etc.
- Execução concorrente de múltiplas transações.
Propriedades ACID
Atomicidade
Ou todas ou nenhuma das operações da transação serão devidamente refletidas na base de dados.
Consistência
Após a execução de uma transação, a base de dados deverá retornar a um estado consistente dos dados.
Isolamento
Apesar de múltiplas transações poderem ser executadas concorrentemente, cada uma delas deverá estar isolada dos efeitos da execução das outras. Resultados intermediários de uma transação deverão ficar ocultos para as outras.
Durabilidade
Depois que uma transação termina com sucesso, as alterações que ela realizou sobre o banco de dados persistirão mesmo que haja falhas do sistema.
Controle de transações
Todas as transações realizadas com o banco deverão ser confirmadas pelo usuário para que sejam realmente efetivadas, ou então descartadas, caso o usuário deseje.
- Para confirmar uma transação – ou um bloco de transações – utiliza-se o comando COMMIT.
- Para descartar uma transação – ou um bloco de transações – utiliza-se o comando ROLLBACK.
O bloco de transações é definido a partir da última ocorrência de COMMIT, ou a partir do início da seção.
Comando SELECT
Linguagem de consulta de dados é usada para extrair informações de uma base de dados. Em um banco de dados, selecionar dados significa efetuar uma consulta enviando o comando que retornará com as informações solicitadas (linhas de uma ou mais tabelas). Para efetuar essas consultas utilizamos o comando SELECT.
Sintaxe
SELECT [DISTINCT] nome_coluna, ….
FROM nome_tabela, ….
[WHERE (condições) [ sub-select] ]
[GROUP BY nome_coluna, ….]
[HAVING (condições)]
[ORDER BY nome_coluna {ASC | DESC}, ….]
[{INTERSECT | MINUS | UNION} comando_select]
SELECT – operadores
A tabela a seguir mostra os principais operadores utilizados na sintaxe das cláusulas que envolvem condições.
Observe as várias funções em SQL que permitem operar sobre os dados resultantes da consulta.
SELECT – consulta simples
O comando SELECT deve conter o nome do campo (ou campos) que deve ser retornado e qual tabela (ou tabelas) se referencia.
Exemplo 1: Nomes das disciplinas.
SELECT NOMED
FROM DISCIPLINA
Resultado
NOMED
TLP1
Cálculo 1
Inglês
Ed Física
G analítica
Projeto Final
Exemplo 2: Salas onde as aulas serão ministradas.
SELECT SALA
FROM GRADE
Resultado
SALA
305
305
305
201
204
204
207
DISTINCT
A cláusula DISTINCT é usada para suprimir linhas duplicadas no resultado.
Exemplo 3: Salas onde as aulas serão ministradas, sem repetição.
SELECT DISTINCT SALA
FROM GRADE
Resultado
SALA
305
201
204
207
WHERE
A cláusula WHERE permite aplicar condições para filtrar as linhas que retornam da consulta. A condição deve respeitar o tipo de dado da coluna. Para colunas com conteúdo do tipo caractere devem ser usadas aspas simples (‘ ‘) no objeto
de comparação. Para colunas com tipo numérico basta colocar o valor sem aspas na comparação.
Exemplo 4: Nome e código dos professores de Ribeirão Preto.
SELECT NOMEP, CODP
FROM PROFESSOR
WHERE CIDADEP = ‘Rib Preto’
NOME
Joaquim
André
CODP
P1
P#
Exemplo 5: Código e nome das disciplinas com carga horário maior ou igual a 5.
SELECT CODD, NOMED
FROM DISCIPLINA
WHERE CARGAD >= 5
Resultado
CODD
D5
D6
NOMED
G Analítica
Projeto Fina
Quando desejamos trazer todos os campos utilizamos o asterisco (*).
SELECT *
FROM GRADE
WHERE CODC = ‘C4’
Resultado
CODC
C4
C4
CODD
D1
D3
CODP
P5
P3
SALA
201
204
Operações no comando SELECT
É possível efetuar uma operação aritmética sobre o dado de uma coluna no comando SELECT.
Exemplo 7: Nome e duração em meses de cada curso.
SELECT NOMEC, (DURACAOC * 12) FROM CURSO
Resultado
NOMEC
Análise Sist
Eng Mecatrônica
Ciência Comp
Eng Elétrica
Turismo
DURACAOC * 12
48
60
60
60
36
Exemplo 8: Nomes dos cursos cuja mensalidade é inferior a 500 reais.
SELECT NOMEC
FROM CURSO
WHERE MENSC < 500
Resultado
NOMEC
Análise Sist
Ciência Comp
Turismo
Operadores lógicos
Critérios combinados podem ser especificados utilizando operadores lógicos AND/OR.
Exemplo 9: Código e carga horária das disciplinas da área de Matemática, com carga horária maior ou igual a 5.
SELECT CODD, CARGAD
FROM DISCIPLINA
WHERE AREAD = ‘Matemática’
AND CARGAD >= 5
Resultado
CODD
D5
CARGAD
5
Operador LIKE
Utilizado em consultas em atributos do tipo caractere para filtrar conteúdos onde ocorrem sequências de strings. O caractere porcentagem (%) indica a posição em que o conteúdo será procurado e o caractere sublinhado (_) indica o número de caracteres envolvidos na pesquisa. Veja os exemplos:
Exemplo 10: O código e o nome de todos os cursos de engenharia.
SELECT CODC, NOMEC
FROM CURSO
WHERE NOMEC LIKE ‘Eng%’
Resultado
CODC
C2
C4
NOMEC
Eng Mecatrônica
Eng Elétrica
Exemplo 11: As salas do segundo andar (número fica na casa dos 200) onde serão ministradas aulas.
SELECT DISTINCT SALA
FROM GRADE
WHERE TO_CHAR(SALA) LIKE ‘2__’
Resultado
SALA
201
204
207
Operador BETWEEN
Utilizado quando é necessário recuperar linhas entre valores de um intervalo. Os valores contidos no comando fazem parte do intervalo.
Exemplo 12: Código dos cursos cuja mensalidade está entre 400 e 550 reais.
SELECT CODC
FROM CURSO
WHERE MENSC BETWEEN 400 AND 550
Resultado
CODC
C1
C3
Operador IN
Utilizado quando para recuperar linhas onde os valores a serem comprados estão em uma lista.
Exemplo 13: Nome das disciplinas que são da área de Computação ou de Humanas ou de Saúde.
SELECT NOMED
FROM DISCIPLINA
WHERE AREAD IN (‘Computação’, ‘Humanas’, ‘Saúde’)
Resultado
NOMED
TLP1
Inglês
Ed Física
Operador NULL/NOT NULL
Verifica se o valor de uma coluna é nulo ou não (IS NULL/IS NOT NULL).
Exemplo 14: Nome das disciplinas que não pertencem a nenhuma área específica.
SELECT NOMED
FROM DISCIPLINA
WHERE AREAD IS NULL
Resultado
NOMED
Projeto Final
ALIAS
O alias é usado para substituir nomes na consulta. Existem dois tipos de alias: de coluna e de tabela.
O alias de coluna é aplicado na lista de colunas do comando SELECT através da cláusula AS e usado para alterar o nome da coluna na apresentação do resultado da consulta.
O alias de tabela é usado na cláusula FROM após o nome da tabela e serve para substituir o nome da tabela dentro da consulta. Muito utilizado no comando de junção que será visto mais adiante.
Exemplo 15: Nome e duração em meses de cada curso.
SELECT NOMEC AS CURSO,
(DURACAOC * 12) AS DURACAO_MESES
FROM Curso
Resultado
NOMEC
Análise Sist
Eng Mecatrônica
Ciência Comp
Eng Elétrica
Turismo
DURACAO_MESES
48
60
60
60
36
Concatenação de campos
Utilizamos concatenação de campos quando é necessário combinar vários campos diferentes em uma coluna de saída da consulta.
Exemplo 16: Códigos dos cursos com seu nome e mensalidade concatenados.
SELECT CODC,
NOMEC || ‘ ‘ || MENSC || ‘ reais’ AS INFO_CURSO
FROM CURSO
Resultado
CODC
C1
C2
C3
C4
C5
INFO_CURSO
Análise Sist 400 reais
Eng Mecatrônica 600 reais
Ciência Comp 450 reais
Eng Elétrica 600 reais
Turismo 350 reais
Ordenação do resultado
Para ordenar o resultado da consulta utilizamos a cláusula ORDER BY. É possível ordenar o resultado em ordem crescente ou decrescente utilizando a cláusula ASC ou DESC.
Exemplo 17: Nomes dos cursos ordenados de forma ascendente.
SELECT NOMEC
FROM CURSO
ORDER BY NOMEC ASC
Resultado
NOMEC
Análise Sist
Ciência Comp
Eng Elétrica
Eng Mecatrônica
Turismo
Também é possível utilizar a posição da coluna para indicar a que coluna será aplicada a cláusula ORDER BY.
Exemplo 18: Código e nomes dos cursos ordenados por nome de forma decrescente.
SELECT CODC, NOMEC
FROM CURSO
ORDER BY 2 DESC
Resultado
CODC
C5
C4
C2
C3
C1
NOMEC
Turismo
Eng Elétrica
Eng Mecatrônica
Ciência Comp
Análise Sist
Atividade proposta
Seja a tabela: Func (cod_func, nome, dta_nasc, salario, cod_superv).
Escreva os comandos em SQL para responder às seguintes proposições:
- Recupere o código dos funcionários que são supervisores.
- Recupere o código dos funcionários que não têm supervisores.
Chave de resposta:
- SELECT DISTINCT COD_SUPERV FROM FUNC.
- SELECT COD_FUNC FROM FUNC WHERE COD_SUPERV IS NULL.
Referências
SILBERSCHATZ, Abraham; KORTH, Henry F; SUDARSHAN, S. A. Sistema de banco de dados. 5. ed. Rio de Janeiro: Campus, 2006.
ELMASRI, R.; NAVATHE, S. Sistemas de banco de dados. 4. ed. Pearson Education do Brasil, 2005.
Eng. de Software | Projeto e otimização de banco de dado | Linguagem SQL – Parte 2