Neste post, falaremos sobre o tópico 1.2 da prova de SQL básico.

A sintaxe básica para executar um SELECT é a seguinte:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U0VMRUNUIGxpc3RhX2NvbHVuYXMgRlJPTSBsaXN0YV90YWJlbGFzCltXSEVSRSBjb25kacOnw7Vlc10KW0FORHxPUiBjb25kacOnw7Vlc10KW09SREVSIEJZIGxpc3RhX2NvbHVuYVtBU0N8REVTQ11d” hover_enabled=”0″]U0VMRUNUIGxpc3RhX2NvbHVuYXMgRlJPTSBsaXN0YV90YWJlbGFzCltXSEVSRSBjb25kacOnw7Vlc10KW0FORHxPUiBjb25kacOnw7Vlc10KW09SREVSIEJZIGxpc3RhX2NvbHVuYVtBU0N8REVTQ11d[/et_pb_dmb_code_snippet]

É importante deixar bem claro que a sintaxe acima aborda o mínimo necessário para recuperar as informações armazenadas no banco de dados. Existe uma quase infinidade de outras cláusulas que podem ser usadas no SQL para refinar a busca, gerando relatórios para os mais diversos objetivos.

[ESTRUTURA DE TESTES]

Vamos usar as seguintes tabelas para exemplificar algumas capacidades de um SELECT:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”Q1JFQVRFIFRBQkxFIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUyAoIAogICAgICAgSURfRU1QICAgICAgICAgICAgICAgICAgICAgICBOVU1CRVIoNikgTk9UIE5VTEwsCiAgICAgICBOT01FX0VNUCAgICAgICAgICAgICAgICBWQVJDSEFSMigzMCkgTk9UIE5VTEwsCiAgICAgICBTQUxBUklPICAgICAgICAgICAgICAgICAgICAgTlVNQkVSKDgsMikgTk9UIE5VTEwsCiAgICAgICBEQVRBX0FETUlTU0FPICAgICAgREFURSwKICAgICAgIElEX0RFUEFSVEFNRU5UTyBOVU1CRVIKICk7CgpDUkVBVEUgVEFCTEUgQ0VSVElGSUNBQ0FPLlRBQl9ERVBBUlRBTUVOVE9TICgKICAgICAgIElEX0RFUEFSVEFNRU5UTyAgICAgICAgTlVNQkVSKDYpIE5PVCBOVUxMLAogICAgICAgTk9NRV9ERVBBUlRBTUVOVE8gVkFSQ0hBUjIoMzApIE5VTEwsCiAgICAgICBJRF9HRVJFTlRFICAgICAgICAgICAgICAgICAgICAgTlVNQkVSKDYpIE5VTEwKICk7″ hover_enabled=”0″]Q1JFQVRFIFRBQkxFIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUyAoIAogICAgICAgSURfRU1QICAgICAgICAgICAgICAgICAgICAgICBOVU1CRVIoNikgTk9UIE5VTEwsCiAgICAgICBOT01FX0VNUCAgICAgICAgICAgICAgICBWQVJDSEFSMigzMCkgTk9UIE5VTEwsCiAgICAgICBTQUxBUklPICAgICAgICAgICAgICAgICAgICAgTlVNQkVSKDgsMikgTk9UIE5VTEwsCiAgICAgICBEQVRBX0FETUlTU0FPICAgICAgREFURSwKICAgICAgIElEX0RFUEFSVEFNRU5UTyBOVU1CRVIKICk7CgpDUkVBVEUgVEFCTEUgQ0VSVElGSUNBQ0FPLlRBQl9ERVBBUlRBTUVOVE9TICgKICAgICAgIElEX0RFUEFSVEFNRU5UTyAgICAgICAgTlVNQkVSKDYpIE5PVCBOVUxMLAogICAgICAgTk9NRV9ERVBBUlRBTUVOVE8gVkFSQ0hBUjIoMzApIE5VTEwsCiAgICAgICBJRF9HRVJFTlRFICAgICAgICAgICAgICAgICAgICAgTlVNQkVSKDYpIE5VTEwKICk7[/et_pb_dmb_code_snippet]

[SELECT BÁSICO]

Para recuperar todas as colunas dessa tabela, sem aplicar restrições ou filtros:

SELECT * FROM CERTIFICACAO.TAB_EMPREGADOS;

OBS: Caso você esteja logado como usuário CERTIFICACAO então o schema antes da tabela não é necessário. Caso contrário, basta ou cria-se um sinônimo público para essa tabela, ou usar o schema antes de seu nome.

Para filtrar o resultado de uma query, basta usar a cláusula WHERE. Ela possui o seguinte formato:

WHERE COLUNA|EXPRESSAO OPERADOR_CONDICIONAL COLUNA|EXPRESSAO;

* COLUNA|EXPRESSAO: Coluna ou expressão a ser comparada

* OPERADOR_CONDICIONAL: “=” (igual), “>” (maior), “<“, (menor) “>=” (maior ou igual), “<=” (menor ou igual), “!=” (diferente), “<>” (diferente)

No exemplo a seguir a query retornará apenas a linha cuja coluna ID_EMP for igual a 100:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”ICAgICAgU0VMRUNUICogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgIFdIRVJFIElEX0VNUCA9IDEwMA==” hover_enabled=”0″]ICAgICAgU0VMRUNUICogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgIFdIRVJFIElEX0VNUCA9IDEwMA==[/et_pb_dmb_code_snippet]

[ALIAS]

É possível atribuir aliases para colunas de tabelas, que servem como apelidos. É possível atribuir aliases usando a palavra AS após a coluna, ou usando o alias entre aspas. Ex:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”ICAgICAgU0VMRUNUIElEX0VNUCBBUyBJRF9FTVBSRUdBRE8sCiAgICAgICAgICAgICAgICAgICAgICAgU0FMQVJJTyAiU2Fsw6FyaW8gKFIkKSIsCiAgICAgICAgICAgICAgICAgICAgICAgREFUQV9BRE1JU1NBTwogICAgICAgICAgIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiAgICAgICAgV0hFUkUgSURfRU1QID4gMTAwMA==” hover_enabled=”0″]ICAgICAgU0VMRUNUIElEX0VNUCBBUyBJRF9FTVBSRUdBRE8sCiAgICAgICAgICAgICAgICAgICAgICAgU0FMQVJJTyAiU2Fsw6FyaW8gKFIkKSIsCiAgICAgICAgICAgICAgICAgICAgICAgREFUQV9BRE1JU1NBTwogICAgICAgICAgIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiAgICAgICAgV0hFUkUgSURfRU1QID4gMTAwMA==[/et_pb_dmb_code_snippet]

[OPERADORES AND E OR]

Os operadores AND e OR podem ser usados quando for necessário usar mais de um filtro no resultado da query, especificando mais as condições de busca. Ao usar o AND, a linha só será retornada caso a primeira e a segunda condição for TRUE. No caso do OR, a linha retornada será a que apresentar pelo menos uma condição TRUE.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS1hIHF1ZXJ5IGFiYWl4byByZXRvcm5hcmEgdW0sIG91IG1haXMgcmVnaXN0cm8gYXBlbmFzIHNlIGV4aXN0aXIgdW0gCi0tZW1wcmVnYWRvIGRlIGlkICAgPSAxMDAwIGUgZGUgc2FsYXJpbyA9IDIwMDAuIFRlw7NyaWNhbWVudGUgc8OzIHJldG9ybmFyaWEgdW1hIAotLWxpbmhhLCBwb2lzIElEX0VNUCBzZXJpYSBhIGNoYXZlIHByaW3DoXJpYSBkYSB0YWJlbGEsIGUgZG9pcyBlbXByZWdhZG9zIG7Do28gCi0tcG9kZXJpYW0gdGVyIG8gbWVzbW8gSUQ6CgogICAgICAgU0VMRUNUICogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgIFdIRVJFIElEX0VNUCA9IDEwMDAgQU5EIFNBTEFSSU8gPSAyMDAwCgotLWEgcXVlcnkgYWJhaXhvIHJldG9ybmFyYSB1bSwgb3UgbWFpcyByZWdpc3RybyBhcGVuYXMgc2UgZXhpc3RpciB1bSBlbXByZWdhZG8gCi0tZGUgaWQgPSAxMDAwIG91IHVtIGVtcHJlZ2FkbyBkZSBzYWxhcmlvID0gMjAwMAoKICAgICAgICBTRUxFQ1QgKiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwogICAgICAgIFdIRVJFIElEX0VNUCA9IDEwMDAgT1IgU0FMQVJJTyA9IDIwMDA=” hover_enabled=”0″]LS1hIHF1ZXJ5IGFiYWl4byByZXRvcm5hcmEgdW0sIG91IG1haXMgcmVnaXN0cm8gYXBlbmFzIHNlIGV4aXN0aXIgdW0gCi0tZW1wcmVnYWRvIGRlIGlkICAgPSAxMDAwIGUgZGUgc2FsYXJpbyA9IDIwMDAuIFRlw7NyaWNhbWVudGUgc8OzIHJldG9ybmFyaWEgdW1hIAotLWxpbmhhLCBwb2lzIElEX0VNUCBzZXJpYSBhIGNoYXZlIHByaW3DoXJpYSBkYSB0YWJlbGEsIGUgZG9pcyBlbXByZWdhZG9zIG7Do28gCi0tcG9kZXJpYW0gdGVyIG8gbWVzbW8gSUQ6CgogICAgICAgU0VMRUNUICogRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgIFdIRVJFIElEX0VNUCA9IDEwMDAgQU5EIFNBTEFSSU8gPSAyMDAwCgotLWEgcXVlcnkgYWJhaXhvIHJldG9ybmFyYSB1bSwgb3UgbWFpcyByZWdpc3RybyBhcGVuYXMgc2UgZXhpc3RpciB1bSBlbXByZWdhZG8gCi0tZGUgaWQgPSAxMDAwIG91IHVtIGVtcHJlZ2FkbyBkZSBzYWxhcmlvID0gMjAwMAoKICAgICAgICBTRUxFQ1QgKiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwogICAgICAgIFdIRVJFIElEX0VNUCA9IDEwMDAgT1IgU0FMQVJJTyA9IDIwMDA=[/et_pb_dmb_code_snippet]

[ORDER BY]

A cláusula ORDER BY serve para ordenar o resultado do SELECT. Ele deve ser a última linha do SELECT. Os valores NULL são considerados maiores que os não-nulos. Sua sintaxe é:

ORDER BY [lista_colunas] [ASC|DESC]

A seguir é possível ver alguns exemplos do uso do ORDER BY:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS1vcmRlbmFuZG8gbyByZXN1bHRhZG8gcGVsbyBpZCBkZSBjYWRhIGVtcHJlZ2Fkbywgb3JkZW5hbmRvIGRlIGZvcm1hIAotLWFzY2VuZGVudGUgKGEgcGFsYXZyYSBBU0MgcG9kZSBzZXIgb21pdGlkYSwgasOhIHF1ZSBvcmRlbmFyIGRlIGZvcm1hIAotLWFzY2VuZGVudGUgw6kgbyBkZWZhdWx0KQoKICAgICAgIFNFTEVDVCAqIAogICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgIE9SREVSIEJZIElEX0VNUCBBU0M7CgotLW9yZGVuYW5kbyBvIHJlc3VsdGFkbyBwZWxvIGlkIGRlIGNhZGEgZW1wcmVnYWRvLCBvcmRlbmFuZG8gZGUgZm9ybWEgZGVzY2VuZGVudGUKCiAgICAgICBTRUxFQ1QgKiAKICAgICAgICAgICBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwogICAgICAgIE9SREVSIEJZIElEX0VNUCBERVNDOwoKLS3DqSBwb3Nzw612ZWwgb3JkZW5hciB1c2FuZG8gbWFpcyBkZSB1bWEgY29sdW5hLiBOZXN0ZSBjYXNvIGV1IGVzdG91IG9yZGVuYW5kbwotLWRlIGZvcm1hIGRlY3Jlc2NlbnRlIHBlbG8gZGVwYXJ0YW1lbnRvIGRvIGVtcHJlZ2FkbyBlIGRlIGZvcm1hIGFzY2VuZGVudGUgCi0tcGVsbyBzYWzDoXJpbyBkZSBjYWRhIGVtcHJlZ2FkbwoKICAgICAgIFNFTEVDVCAqIAogICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgIE9SREVSIEJZIElEX0RFUEFSVEFNRU5UTyBERVNDLCBTQUxBUklPIEFTQzsKCi0tdGFtYsOpbSDDqSBwb3Nzw612ZWwgb3JkZW5hciBwZWxhIHBvc2nDp8OjbyBkYSBjb2x1bmEgbm8gU0VMRUNULiAKLS1BYmFpeG8gZXUgZXN0b3Ugb3JkZW5hbmRvIHBlbGFzIGNvbHVuYXMgSURfRU1QICgxwrogY29sdW5hIGRvIFNFTEVDVCkgZSBTQUxBUklPICgzwrogY29sdW5hIGRvIFNFTEVDVCkKICAgIAogICAgICAgU0VMRUNUIElEX0VNUCwgTk9NRV9FTVAsIFNBTEFSSU8gCiAgICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MgCiAgICAgICAgIE9SREVSIEJZIDEgQVNDLCAzIERFU0MKCi0tTm8gT1JERVIgQlksIHBvZGVtIGFwYXJlY2VyIG5vbWVzIGRlIGNvbHVuYXMsIGV4cHJlc3PDtWVzLCBmdW7Dp8O1ZXMgCi0tZSBhbGlhc2VzIGRlIGNvbHVuYXMuIEV4OgoKICAgICAgIFNFTEVDVCBFLk5PTUVfRU1QLCAKICAgICAgICAgICAgICAgICAgICAgIFRPX0NIQVIoRS5TQUxBUklPLCAnOTk5Rzk5OUc5OTlEOTknKSwgCiAgICAgICAgICAgICAgICAgICAgICBFLklEX0RFUEFSVEFNRU5UTyAiU2V0b3IgRW1wcmVnYWRvIiAKICAgICAgICAgIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TIEUKICAgICAgIE9SREVSIEJZIEUuTk9NRV9FTVAsIFRPX0NIQVIoRS5TQUxBUklPLCAnOTk5Rzk5OUc5OTlEOTknKSwgIlNldG9yICBFbXByZWdhZG8i” hover_enabled=”0″]LS1vcmRlbmFuZG8gbyByZXN1bHRhZG8gcGVsbyBpZCBkZSBjYWRhIGVtcHJlZ2Fkbywgb3JkZW5hbmRvIGRlIGZvcm1hIAotLWFzY2VuZGVudGUgKGEgcGFsYXZyYSBBU0MgcG9kZSBzZXIgb21pdGlkYSwgasOhIHF1ZSBvcmRlbmFyIGRlIGZvcm1hIAotLWFzY2VuZGVudGUgw6kgbyBkZWZhdWx0KQoKICAgICAgIFNFTEVDVCAqIAogICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgIE9SREVSIEJZIElEX0VNUCBBU0M7CgotLW9yZGVuYW5kbyBvIHJlc3VsdGFkbyBwZWxvIGlkIGRlIGNhZGEgZW1wcmVnYWRvLCBvcmRlbmFuZG8gZGUgZm9ybWEgZGVzY2VuZGVudGUKCiAgICAgICBTRUxFQ1QgKiAKICAgICAgICAgICBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwogICAgICAgIE9SREVSIEJZIElEX0VNUCBERVNDOwoKLS3DqSBwb3Nzw612ZWwgb3JkZW5hciB1c2FuZG8gbWFpcyBkZSB1bWEgY29sdW5hLiBOZXN0ZSBjYXNvIGV1IGVzdG91IG9yZGVuYW5kbwotLWRlIGZvcm1hIGRlY3Jlc2NlbnRlIHBlbG8gZGVwYXJ0YW1lbnRvIGRvIGVtcHJlZ2FkbyBlIGRlIGZvcm1hIGFzY2VuZGVudGUgCi0tcGVsbyBzYWzDoXJpbyBkZSBjYWRhIGVtcHJlZ2FkbwoKICAgICAgIFNFTEVDVCAqIAogICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgIE9SREVSIEJZIElEX0RFUEFSVEFNRU5UTyBERVNDLCBTQUxBUklPIEFTQzsKCi0tdGFtYsOpbSDDqSBwb3Nzw612ZWwgb3JkZW5hciBwZWxhIHBvc2nDp8OjbyBkYSBjb2x1bmEgbm8gU0VMRUNULiAKLS1BYmFpeG8gZXUgZXN0b3Ugb3JkZW5hbmRvIHBlbGFzIGNvbHVuYXMgSURfRU1QICgxwrogY29sdW5hIGRvIFNFTEVDVCkgZSBTQUxBUklPICgzwrogY29sdW5hIGRvIFNFTEVDVCkKICAgIAogICAgICAgU0VMRUNUIElEX0VNUCwgTk9NRV9FTVAsIFNBTEFSSU8gCiAgICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MgCiAgICAgICAgIE9SREVSIEJZIDEgQVNDLCAzIERFU0MKCi0tTm8gT1JERVIgQlksIHBvZGVtIGFwYXJlY2VyIG5vbWVzIGRlIGNvbHVuYXMsIGV4cHJlc3PDtWVzLCBmdW7Dp8O1ZXMgCi0tZSBhbGlhc2VzIGRlIGNvbHVuYXMuIEV4OgoKICAgICAgIFNFTEVDVCBFLk5PTUVfRU1QLCAKICAgICAgICAgICAgICAgICAgICAgIFRPX0NIQVIoRS5TQUxBUklPLCAnOTk5Rzk5OUc5OTlEOTknKSwgCiAgICAgICAgICAgICAgICAgICAgICBFLklEX0RFUEFSVEFNRU5UTyAiU2V0b3IgRW1wcmVnYWRvIiAKICAgICAgICAgIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TIEUKICAgICAgIE9SREVSIEJZIEUuTk9NRV9FTVAsIFRPX0NIQVIoRS5TQUxBUklPLCAnOTk5Rzk5OUc5OTlEOTknKSwgIlNldG9yICBFbXByZWdhZG8i[/et_pb_dmb_code_snippet]

[LIKE]

O operador LIKE serve para buscar cadeias de caracteres em uma string maior. Trabalha com os tipos de dados CHAR e VARCHAR2. Com o tipo de dados DATE, o ORACLE executa uma conversão implícita, fazendo com que o LIKE possa ser usado com tipos DATE. Sua sinxate básica é:

WHERE NOME_COLUNA LIKE ‘%STRING%’ [ESCAPE “CARACTERE_ESCAPE”];

A seguir, é possível ver alguns exemplos usando o LIKE:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS1idXNjYSBvcyBlbXByZWdhZG9zIHF1ZSBwb3NzdWFtIGEgbGV0cmEgIkYiIGVtIHNldSBub21lIAotLShjb2x1bmEgTk9NRV9FTVApCgogICAgICAgU0VMRUNUICogCiAgICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgICBXSEVSRSBOT01FX0VNUCBMSUtFICclRiUnOwoKLS1idXNjYSBvcyBlbXByZWdhZG9zIGN1am8gbm9tZSBjb21lY2UgY29tICJBIgoKICAgICAgIFNFTEVDVCAqIAogICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgIFdIRVJFIE5PTUVfRU1QIExJS0UgJ0ElJwogCiAtLW8gY2FyYWN0ZXJlICJ1bmRlcmxpbmUiIChfKSBzZXJ2ZSBwYXJhIGluZGljYXIgcXVhbHF1ZXIgY2FyYWN0ZXJlCiAtLW8gZXhlbXBsbyBhYmFpeG8gcmV0b3JuYSAiQU5BIiwgIkFTQSIsICJBR0EiIGUgZXRjLi4uCgogICAgICAgU0VMRUNUICogCiAgICAgICAgICBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwogICAgICAgV0hFUkUgTk9NRV9FTVAgTElLRSAnQV9BJScKICAgICAgCi0tY2FzbyBzZWphIG5lY2Vzc2FyaW8gdXNhciBjYXJhY3RlcmVzIGRlIGVzY2FwZSwgZGV2ZS1zZSB1c2FyIGEgY2zDoXVzdWxhIEVTQ0FQRS4KLS1ubyBleGVtcGxvIGFiYWl4bywgZXUgZXN0b3UgYnVzY2FuZG8gbyBlbXByZWdhZG8gcXVlIHBvc3N1YSBvIG5vbWUgIlJJQ0tfIgotLXNlIGV1IG7Do28gdXNhc3NlIG8gRVNDQVBFLCBvIE9SQUNMRSBpcmlhIGJ1c2NhciBvcyBlbXByZWdhZG9zIGN1am8gbm9tZQotLXNlZ3Vpc3NlIG8gcGFkcsOjbyAiUklDS1tBIGF0w6kgWl0iLCBwb2lzIG8gdW5kZXJsaW5lIGluZGljYSBxdWFscXVlciBjYXJhY3RlcmUuCgogICAgICAgU0VMRUNUICogCiAgICAgICAgICBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwogICAgICAgV0hFUkUgTk9NRV9FTVAgTElLRSAnUklDS18lJyBFU0NBUEUgJyc=” hover_enabled=”0″]LS1idXNjYSBvcyBlbXByZWdhZG9zIHF1ZSBwb3NzdWFtIGEgbGV0cmEgIkYiIGVtIHNldSBub21lIAotLShjb2x1bmEgTk9NRV9FTVApCgogICAgICAgU0VMRUNUICogCiAgICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgICBXSEVSRSBOT01FX0VNUCBMSUtFICclRiUnOwoKLS1idXNjYSBvcyBlbXByZWdhZG9zIGN1am8gbm9tZSBjb21lY2UgY29tICJBIgoKICAgICAgIFNFTEVDVCAqIAogICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgIFdIRVJFIE5PTUVfRU1QIExJS0UgJ0ElJwogCiAtLW8gY2FyYWN0ZXJlICJ1bmRlcmxpbmUiIChfKSBzZXJ2ZSBwYXJhIGluZGljYXIgcXVhbHF1ZXIgY2FyYWN0ZXJlCiAtLW8gZXhlbXBsbyBhYmFpeG8gcmV0b3JuYSAiQU5BIiwgIkFTQSIsICJBR0EiIGUgZXRjLi4uCgogICAgICAgU0VMRUNUICogCiAgICAgICAgICBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwogICAgICAgV0hFUkUgTk9NRV9FTVAgTElLRSAnQV9BJScKICAgICAgCi0tY2FzbyBzZWphIG5lY2Vzc2FyaW8gdXNhciBjYXJhY3RlcmVzIGRlIGVzY2FwZSwgZGV2ZS1zZSB1c2FyIGEgY2zDoXVzdWxhIEVTQ0FQRS4KLS1ubyBleGVtcGxvIGFiYWl4bywgZXUgZXN0b3UgYnVzY2FuZG8gbyBlbXByZWdhZG8gcXVlIHBvc3N1YSBvIG5vbWUgIlJJQ0tfIgotLXNlIGV1IG7Do28gdXNhc3NlIG8gRVNDQVBFLCBvIE9SQUNMRSBpcmlhIGJ1c2NhciBvcyBlbXByZWdhZG9zIGN1am8gbm9tZQotLXNlZ3Vpc3NlIG8gcGFkcsOjbyAiUklDS1tBIGF0w6kgWl0iLCBwb2lzIG8gdW5kZXJsaW5lIGluZGljYSBxdWFscXVlciBjYXJhY3RlcmUuCgogICAgICAgU0VMRUNUICogCiAgICAgICAgICBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUwogICAgICAgV0hFUkUgTk9NRV9FTVAgTElLRSAnUklDS18lJyBFU0NBUEUgJyc=[/et_pb_dmb_code_snippet]

[IN E NOT IN]

Os operadores IN e NOT IN servem para comparar os valores de uma coluna com uma lista de valores. Pode ser usado com datas, numeros e strings. A lista de expressões deve ser do mesmo tipo de dados, ou similar o suficiente para que o ORACLE possa converter implicitamente os valores. Sua sintaxe básica é:

WHERE NOME_COLUNA IN|NOT IN (LISTA_VALORES)

A seguir é possível ver alguns exemplos de uso do IN e NOT IN:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS1yZXRvcm5hIG9zIGVtcHJlZ2Fkb3MgZGUgaWQgMTAwMDAwIGUgMTEwMDAwCgogICAgICAgU0VMRUNUICogCiAgICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgICBXSEVSRSBJRF9FTVAgSU4gKDEwMDAwMCwgMTEwMDAwKQoKLS1yZXRvcm5hIG9zIGVtcHJlZ2Fkb3MgcXVlIG7Do28gcG9zc3VhbSBpZCBkZSAxMjAwMDAgZSAxMzAwMDAKCiAgICAgICBTRUxFQ1QgKiAKICAgICAgICAgIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiAgICAgICBXSEVSRSBJRF9FTVAgTk9UIElOICgxMjAwMDAsIDEzMDAwMCk=” hover_enabled=”0″]LS1yZXRvcm5hIG9zIGVtcHJlZ2Fkb3MgZGUgaWQgMTAwMDAwIGUgMTEwMDAwCgogICAgICAgU0VMRUNUICogCiAgICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgICBXSEVSRSBJRF9FTVAgSU4gKDEwMDAwMCwgMTEwMDAwKQoKLS1yZXRvcm5hIG9zIGVtcHJlZ2Fkb3MgcXVlIG7Do28gcG9zc3VhbSBpZCBkZSAxMjAwMDAgZSAxMzAwMDAKCiAgICAgICBTRUxFQ1QgKiAKICAgICAgICAgIEZST00gQ0VSVElGSUNBQ0FPLlRBQl9FTVBSRUdBRE9TCiAgICAgICBXSEVSRSBJRF9FTVAgTk9UIElOICgxMjAwMDAsIDEzMDAwMCk=[/et_pb_dmb_code_snippet]

[BETWEEN]

O operador BETWEEN é usado para verificar registros entre um limite inicial e final de valores. Ele é inclusivo, ou seja, ele compara os valores determinados na faixa de valores, inclusive os 2 valores delimitadores da faixa. Sua sintaxe básica é:

WHERE NOME_COLUNA [NOT] BETWEEN LIMITE_INICIAL AND LIMITE_FINAL

A seguir é possível ver alguns exemplos de uso do BETWEEN:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”LS1yZXRvcm5hIG9zIGVtcHJlZ2Fkb3MgcXVlIHBvc3N1YW0gaWQgZW50cmUgMTAwMDAwIGUgMTQwMDAwLCBpbmNsdXNpdmUgb3MgZW1wcmVnYWRvcyBkZSBpZCAxMDAwMDAgZSAxNDAwMDAKCiAgICAgIFNFTEVDVCAqIAogICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgIFdIRVJFIElEX0VNUCBCRVRXRUVOIDEwMDAwMCBBTkQgMTQwMDAwCgotLXJldG9ybmEgb3MgZW1wcmVnYWRvcyBuw6NvIHBvc3N1YW0gaWQgZW50cmUgMTMwMDAwIGUgMTgwMDAwCgogICAgICAgU0VMRUNUICogCiAgICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgICBXSEVSRSBJRF9FTVAgTk9UIEJFVFdFRU4gMTMwMDAwIEFORCAxODAwMDA=” hover_enabled=”0″]LS1yZXRvcm5hIG9zIGVtcHJlZ2Fkb3MgcXVlIHBvc3N1YW0gaWQgZW50cmUgMTAwMDAwIGUgMTQwMDAwLCBpbmNsdXNpdmUgb3MgZW1wcmVnYWRvcyBkZSBpZCAxMDAwMDAgZSAxNDAwMDAKCiAgICAgIFNFTEVDVCAqIAogICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgIFdIRVJFIElEX0VNUCBCRVRXRUVOIDEwMDAwMCBBTkQgMTQwMDAwCgotLXJldG9ybmEgb3MgZW1wcmVnYWRvcyBuw6NvIHBvc3N1YW0gaWQgZW50cmUgMTMwMDAwIGUgMTgwMDAwCgogICAgICAgU0VMRUNUICogCiAgICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgICBXSEVSRSBJRF9FTVAgTk9UIEJFVFdFRU4gMTMwMDAwIEFORCAxODAwMDA=[/et_pb_dmb_code_snippet]
[et_pb_dmb_code_snippet _builder_version=”4.0.6″ hover_enabled=”0″ code=””][/et_pb_dmb_code_snippet]

[DICAS RÁPIDAS PARA O EXAME]

Quando se executa uma query, comparando uma coluna com NULL, esta query nunca retornará nada. Isso ocorre porque se está pedindo para o compilador SQL, por exemplo, comparar o valor da coluna XYZ com um valor “desconhecido”, que é isso o que o NULL representa… um valor desconhecido, e não um valor vazio. Qualquer coisa = NULL retornará FALSE. Por exemplo, caso eu possua uma linha na tabela CERTIFICACAO.TAB_EMPREGADOS com o valor da coluna ID_DEPARTAMENTO como NULL, caso eu execute a seguinte query:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”ICAgICAgU0VMRUNUICogCiAgICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgICBXSEVSRSBJRF9ERVBBUlRBTUVOVE8gPSBOVUxMCuKApiBlbGEgbsOjbyB2YWkgcmV0b3JuYXIgbmFkYS4gUGFyYSB2ZXJpZmljYXIgc2UgdW1hIGNvbHVuYSBwb3NzdWkgdW0gdmFsb3IgbnVsbywgdXNhLXNlIG8gSVMgTlVMTCB8IElTIE5PVCBOVUxMOgoKICAgICAgU0VMRUNUICogCiAgICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgICBXSEVSRSBJRF9ERVBBUlRBTUVOVE8gSVMgTlVMTA==” hover_enabled=”0″]ICAgICAgU0VMRUNUICogCiAgICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgICBXSEVSRSBJRF9ERVBBUlRBTUVOVE8gPSBOVUxMCuKApiBlbGEgbsOjbyB2YWkgcmV0b3JuYXIgbmFkYS4gUGFyYSB2ZXJpZmljYXIgc2UgdW1hIGNvbHVuYSBwb3NzdWkgdW0gdmFsb3IgbnVsbywgdXNhLXNlIG8gSVMgTlVMTCB8IElTIE5PVCBOVUxMOgoKICAgICAgU0VMRUNUICogCiAgICAgICAgICAgRlJPTSBDRVJUSUZJQ0FDQU8uVEFCX0VNUFJFR0FET1MKICAgICAgICBXSEVSRSBJRF9ERVBBUlRBTUVOVE8gSVMgTlVMTA==[/et_pb_dmb_code_snippet]

A regra para validar operadores booleanos em queries é: NOT é validado antes, depois o AND e por fim o OR.

Na comparação de caracteres, as letras maiusculas possuem um peso menor que as letras minusculas, e os números possuem um peso menor que as letras. Ex:

‘A’ é menor que ‘Z’;
‘Z’ é menor que ‘a’
‘2’ é maior que ’10’

…caso eu execute a seguinte query:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”4oCmY2FzbyBldSBleGVjdXRlIGEgc2VndWludGUgcXVlcnk6CgogICAgICBTRUxFQ1QgKiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUyBPUkRFUiBCWSBOT01FX0VNUCBERVNDCuKApm9zIGVtcHJlZ2Fkb3MgY29tIG5vbWVzIGluaWNpYWRvcyBlbSBsZXRyYXMgbWluw7pzY3VsYXMgYXBhcmVjZXLDo28gcHJpbWVpcm8sIHNlZ3VpZG9zIGRvcyBlbXByZWdhZ29zIGNhZGFzdHJhZG9zIGNvbSBub21lcyBpbmljaWFkb3MgZW0gbGV0cmFzIG1hacO6c2N1bGFzLiBQb3Igw7psdGltbywgYXBhcmVjZXLDo28gb3MgZW1wcmVnYWRvcyBjYWRhc3RyYWRvcyBjb20gbm9tZXMgaW5pY2lhZG9zIHBvciBuw7ptZXJvcyAocHJvdmF2ZWxtZW50ZSB1bSBlcnJvIGRlIGRpZ2l0YcOnw6NvKS4=” hover_enabled=”0″]4oCmY2FzbyBldSBleGVjdXRlIGEgc2VndWludGUgcXVlcnk6CgogICAgICBTRUxFQ1QgKiBGUk9NIENFUlRJRklDQUNBTy5UQUJfRU1QUkVHQURPUyBPUkRFUiBCWSBOT01FX0VNUCBERVNDCuKApm9zIGVtcHJlZ2Fkb3MgY29tIG5vbWVzIGluaWNpYWRvcyBlbSBsZXRyYXMgbWluw7pzY3VsYXMgYXBhcmVjZXLDo28gcHJpbWVpcm8sIHNlZ3VpZG9zIGRvcyBlbXByZWdhZ29zIGNhZGFzdHJhZG9zIGNvbSBub21lcyBpbmljaWFkb3MgZW0gbGV0cmFzIG1hacO6c2N1bGFzLiBQb3Igw7psdGltbywgYXBhcmVjZXLDo28gb3MgZW1wcmVnYWRvcyBjYWRhc3RyYWRvcyBjb20gbm9tZXMgaW5pY2lhZG9zIHBvciBuw7ptZXJvcyAocHJvdmF2ZWxtZW50ZSB1bSBlcnJvIGRlIGRpZ2l0YcOnw6NvKS4=[/et_pb_dmb_code_snippet]

É isso ai. No próximo post, falaremos sobre o tópico de limitação e ordenação de dados, sendo que já falei um pouco sobre o ORDER BY.