Prévia do material em texto
Aula 08 Banco de Dados I - UNIGRAN LINGUAGEM SQL Prezado(a) aluno(a), Continuaremos nosso estudo compreendendo na Aula 8 alguns pressupostos básicos da SQL, veremos sua classificação, estrutura, tipos e linguagem. a) para retirar o máximo de proveito de sua aprendizagem, sugerimos que procure: b) envolver-se ativamente nos estudos; c) atualizar seus interesses ou competências pessoais por intermédio do estudo dos conteúdos; d) consultar diversas fontes de informação, além das disponibilizadas no material didático, contudo tomando o cuidado de não perder o foco do conteúdo da disciplina. Bom trabalho! Objetivos de aprendizagem Ao término desta aula, você será capaz de: • identificar e interpretar, em linhas gerais, o contexto da linguagem SQL; • definir a estrutura e características da SQL; • reconhecer alguns conceitos básicos dos tipos de dados; • apontar as etapas e refletir sobre linguagem SQL e suas instruções. 111 Banco de Dados I - UNIGRAN Seções de estudo • Seção 1 - Conceito. • Seção 2 - Características da SQL. • Seção 3 - Estrutura da SQL. • Seção 4 - Tipos de dados. • Seção 5 - Linguagem SQL e suas instruções. Seção 1 – Conceito A expressão SQL significa Linguagem Estruturada de Pesquisa, essa linguagem seus fundamentos nas especificações de Edgar F. Cood que criou o modelo relacional no qual determinava que os usuários teriam acesso ao banco de dados através de uma linguagem de consulta e que ela seria a única forma de acesso ao banco de dados (ELMASRI; NAVATHE, 2005). Ainda segundo os autores, o Modelo Relacional é constituído de tabelas, cada qual contendo linha (registros, tuplas) e colunas (campos), os registros na tabela não são ordenados e a sua localização se faz através de campos chaves (PK), O campo PK faz o papel de ordenar os registros dentro da tabela através de índices criados. Note que é por meio dessa chave que se identifica uma, e somente uma, ocorrência do valor contido no campo: De acordo com Machado (2004), a linguagem SQL foi escolhida pela ANSI (American National Standars Institute) como a linguagem de consulta padrão para bancos de dados relacionais. Para iniciar nossas refl exões, nesta primeira seção da Aula 8, vamos aprofundar nossos conhecimentos sobre o contexto da linguagem SQL, sua estrutura e caracterísƟ cas, bem como conhecer como aplicar dados e quais seus Ɵ pos. Bons estudos! Importante: a linguagem SQL teve seus fundamentos no Modelo Relacional, sua primeira versão recebeu o nome de SEQUEL, ela foi defi nida, em 1974, nos laboratórios da IBM. Mais tarde foi implementado um novo protóƟ po de aplicação desta nova linguagem e o modelo de Sequel foi melhorado e teve seu nome alterado por questões jurídicas para SQL (MACHADO, 2004). 112 Banco de Dados I - UNIGRAN Com a facilidade de consulta e manipulação de dados no ambiente de BD, a utilização da SQL foi se tornando cada vez maior e um grande número de SGBD foi tendo como linguagem básica o SQL, dentre eles o DB2, ORACLE, SYBASE INC, SQL Server. Seção 2 – Características da SQL De acordo com Machado e Abreu (2009), a grande vantagem da utilização do SQL é a sua facilidade de manipulação e entendimento, pois o SQL é simples e fácil, seu comandos são escritos em linguagem natural o inglês, permitindo escrever suas consultas relativamente rápidas. Para utilizar SQL não tem a necessidade de se conhecer lógica da programação, as suas instruções são executadas separadamente, dessa forma para utilizar a linguagem SQL temos que aprender sua sintaxe, no qual ela executa cada construção. A SQL foi projetada originalmente para ser uma sublinguagem, elas não contem controle de fluxo de dados, laços ou redirecionamento de entrada e saída, seu objetivo principal seria depender de uma linguagem hospedeira que oferecesse tais recursos, como o C++, Delphi. Como todas as linguagens a SQL também possui palavras reservadas. A SQL pode ser utilizada desde um computador pessoal, uma estação de trabalho ou até mesmos em um Mainframe. A linguagem também provê um acesso rápido dos dados ao usuário, fornecendo respostas a questões complexas em questão de segundos (ELMASRI; NAVATHE, 2005). ? VOCÊ SABIA “A linguagem é um grande padrão de banco de dados. Isto decorre da sua simplicidade e facilidade de uso. Ela se diferencia de outras linguagens de consulta a banco de dados no senƟ do em que uma consulta SQL especifi ca a forma do resultado e não o caminho proposição a outras linguagens procedurais. Isto reduz o ciclo de aprendizado daqueles que se iniciam na linguagem” (CARVALHO, 2013). 113 Banco de Dados I - UNIGRAN Seção 3 – Estrutura da SQL A linguagem SQL não é case-sensitive, ou seja, não faz diferença entre letras maiúsculas e minúsculas. Dentre as principais particularidades na nomeclatura da linguagem iremos destacar a seguinte forma de padronizar as instruções: a) Escreveremos as palavras reservadas em maiúscula; b) Parâmetros fornecidos pelo usuário, utilizaremos aspas (‘ ‘); c) Para finalizar cada instrução utilizaremos ponto-e-vírgula (“ ; “); d) Os nomes de campos, tabelas, visões e demais objetos devem conter apenas letras, números e alguns caracteres especiais, em particular o traço (“ - “) e sublinhado (“ _ “). Espaços em branco não serão permitidos; e) Para informar que a instrução não é obrigatória colocaremos esta informação em colchetes ( [ ] ); f) As instruções obrigatórios nós iremos colocar entre parênteses ( ); g) Quando houver mais de um parâmetro aplicável a uma determinada situação as opções estarão separadas por ( | ). Seção 4 – Tipos de Dados De acordo com Machado e Abreu (2009), todos os bancos de dados relacionais armazenam dados em tabelas que compreendem linhas (registros) e colunas (campos). Cada coluna representa informação de um único tipo, cada linha representa uma instância desta informação. Uma coluna pode armazenar texto, número, data e outros tipos de dados. Quando você define uma coluna como parte de uma tabela você dever especificar o tipo de dado que ela armazenará para especificar o tamanho máximo da coluna. Os dados podem ser dos seguintes tipos: dados texto, dados numéricos, dados DATE e dados BLOB. 4.1 Tipos de Dados texto São tipos de dados texto o CHAR e o VARCHAR (ELMASRI; NAVATHE, 2005; MACHADO; ABREU, 2009): a) CHAR: é um literal ou uma cadeia de caracteres (string). Dados do tipo CHAR são utilizados tipicamente para armazenar colunas que já tenham um tamanho pré-definido. O CEP é um bom exemplo, já que quase sempre varia de 07 a 10 caracteres em seu tamanho. Sintaxe: CHAR(n). 114 Banco de Dados I - UNIGRAN b) VARCHAR: também é um literal ou uma cadeia de caracteres (string). Dados do tipo VARCHAR são usados na maioria das vezes para armazenar dados textuais. Por exemplo, Endereços, Nomes, Bairro, devem ser armazenados com este tipo. A principal diferença é que se eu definir como: Nome – Char (50), mesmo não tendo o nome 50 caracteres todas as posições serão gravadas no campo, ficando o nome mais espaços em branco. Caso definíssemos como: Nome – Varchar (50), quando for gravar os dados se o nome tiver 35 caracteres somente eles serão gravados economizando assim espaço na coluna do banco de dados, ou seja, irá ocupar somente o espaço que tiver preenchido. Sintaxe: VARCHAR(n). 4.2 Tipos de Dados numéricos Os dados numéricos podem ser dos seguintes tipos (MACHADO, 2004): a) SMALLINT – armazena números inteiros, não decimais na faixa de -32.768 à + 32.767. Este tipo de dado armazena números inteiros de até 5 dígitos. b) INTEGER – este tipo também armazena números inteiros, não decimais, mas na faixa de -2.147.483.648 à +2.147.483.647. Este tipo de dado armazena números inteiros de até 20 dígitos. c) FLOAT – utilizado para armazenarnúmeros “single-precision” com até 7 casas decimais significantes. O ponto decimal aparece dentro da coluna. Se você tentar armazenar valores com mais de 7 casas decimais, ela será truncada. d) DOUBLE PRECISION – uma coluna Double Precision é comumente conhecida como DOUBLE. Armazena números com até 15 decimais significantes. O ponto decimal também aparece dentro da coluna. e) NUMERIC e DECIMAL – ambos os tipos especificam uma coluna numérica na qual o valor do ponto decimal é fixado, com moeda. Precision (precisão) representa o número de dígitos do lado direito do ponto flutuante. Para ambos os tipos a quantidade varia de 1 a 15, porém a Scale (escala) deve ser sempre menor ou igual à precisão. Sintaxe: NUMERIC (Precision, Scale); DECIMAL (Precision, Scale). 4.3 Tipos de Dados DATE Os dados DATE podem ser (ELMASRI; NAVATHE, 2005; MACHADO; ABREU, 2009): a) DATE – armazena a porção data, possui dez posições. Pode ser representado por DD/MM/AAAA. b) TIME – armazena a porção hora, possui oitos posições. Pode ser representado por HH: MM:SS. c) TIMESTAMP – este tipo de dados armazena tanto a porção data como hora. 115 Banco de Dados I - UNIGRAN Vale salientar que tanto o Interbase como o Firebird reconhecem os seguintes formatos para datas: 4.4 Tipos de Dados BLOB Elmasri e Navathe (2005) ensinam que o dado BLOB é um tipo de dado que pode armazenar desde objetos largos até muitas outras “coisas” (elementos), como imagens gráficas, vídeo clips, arquivos de som, programas, textos grandes etc. Seção 5 – Linguagem SQL e suas Instruções A linguagem SQL representa um grupo de comandos responsáveis pela classificação das tabelas, comandos e atualização dos dados em um SGBD. Os comandos existentes nesta linguagem são subdivididos em três grupos (MACHADO; ABREU, 2009): a) Definição de dados (DDL) – Conjunto de comandos responsáveis pela criação do próprio banco de dados, suas tabelas, índices, visões. b) Controle de dados (DCL) – Conjunto de comandos responsáveis pela segurança do banco de dados, como atribuição de privilégios de acessos aos usuários no banco de dados. c) Manipulação de dados (DML) – Conjunto de comandos responsáveis pela consulta e atualização dos dados armazenados em um banco de dados. FORMATO DESCRIÇÃO 1-15-08 25/12/08 15/10/2008 Outubro Yesterday Today Now Tomorrow mm/dd/aa dd/mm/aa dd/mm/aaaa 15, 2008 O dia anterior O dia corrente O dia e hora corrente O próximo dia 116 Banco de Dados I - UNIGRAN 5.1 Instruções Definição de Dados (DDL) Estas instruções são responsáveis pela criação, alteração e exclusão dos objetos do banco de dados. São, em geral, executadas pelo Administrador do Banco de Dados (DBA) e por um grupo restrito de usuários. 5.1.1 Criando um Banco de Dados O analista de sistemas deve ter conhecimento da localização física em que o banco de dados ficará do espaço de armazenamento, bem como em qual sistema operacional seu banco de dados irá rodaR (MACHADO, 2004). Você também deve ter certeza que o Administrador de Banco de Dados (DBA) entendeu claramente qual será o tamanho do seu banco e qual a perspectiva de crescimento do mesmo, e quais os requerimentos para manutenção. Naturalmente se você é o DBA, deve então se preocupar com todas estas questões. Para Machado e Abreu (2009), o analista de sistemas deve ter a definição clara de seu modelo de dados antes de criar o banco de dados. Isto inclui as tabelas, chaves primárias, relacionamentos de chaves estrangeiras e restrições da integridade referencial. Estaremos demonstrando como criar uma Banco de Dados embora na prática estejamos utilizando o IBexpert para gerenciar nosso banco de dados, no qual a tarefa de criar um banco de dados e tabelas se torna muito mais simples. Mas sempre é bom saber como criar manualmente. Sintaxe: CREATE DATABASE ‘database_filename’ [USER ‘username’ [PASSWORD ‘password’]] [PAGE_SIZE [=] page_size] [LENGTH [=] length [PAGE[S]]] [DEFAUTL CHARACTER SET charset] FONTE: MACHADO, Felipe Nery Rodrigues; ABREU, Mauricio Pereira. Projeto de banco de dados: uma visão prática. 16 ed. São Paulo: Érica, 2009. Figura 8.1 Linguagem SQL. 117 Banco de Dados I - UNIGRAN O nome do banco de dados deve seguir a mesma instrução de nomeação vista anteriormente para nomear campo e tabelas. O usuário deve ter no mínimo 31 caracteres de tamanho e não é “case- sensitive”, a senha deve ter no máximo 8 caracteres. O USER (usuário) tem todos os privilégios de acesso ao banco de dados, bem como, remover e alterar. Por definição o usuário geralmente é SYSDBA e a senha de acesso “masterkey”. O SGBD permite que você tenha controle do tamanho de cada página física ou bloco contido no banco de dados, recomendamos a utilização do Page_size 4096. Ela facilita o acesso em servidores e quando estiver utilizando campos com tamanho grandes ou Blobs você alcança um desempenho melhor na busca de registros entre múltiplas páginas caso seja necessário (ELMASRI; NAVATHE, 2005). Há vários tipos de Character Set utilizados me todo o mundo. Quando você define uma coluna em uma tabela do banco, você deve especificar individualmente o Character Set utilizado por cada coluna. Se você não fizer isto o SGBD adota o padrão utilizado pelo banco de dados. O Character Set determina quais caracteres serão aceitos em campos CHAR, VARCHAR e alguns BLOB. O Characte Set padrão no Interbase e Firebird é o NONE, este tipo de caractere faz com o banco de dados armazene um dado exatamente como você esta digitando. 5.1.2 Eliminando um Banco de Dados Para você apagar um BD, conforme orientações de Machado (2004), basta dar um comando DROP, esse comando remove completamente o banco de dados inclusive todas as tabelas que estiverem em seu conteúdo. Uma vez executado este comando não tem mas como ser recuperado. Sintaxe: DROP DATABASE 5.1.3 Criando Tabelas Dentro de um banco de dados, a tabela é um objeto fundamental para o armazenamento de dados. A tabela é criada seguindo os princípios do modelo relacional. Tabelas são compostas por colunas que representam o tipo de informação que será armazenada, e por linhas que representa uma instância desta informação. Quando você insere dados em uma tabela, você adiciona então uma ou mais linhas (registros) completas (MACHADO; ABREU, 2009). Sintaxe: CREATE TABLE ‘table_name’ (Column_name1 Column_definition, Column_name1 Column_definition, Column_nameN 118 Banco de Dados I - UNIGRAN Column_definitionN); Vamos criar uma tabela CLIENTE: CREATE TABLE CLIENTE ( CODCLI INTEGER NOT NULL, NOME VARCHAR(60), ENDERECO VARCHAR(60), BAIRRO VARCHAR(20), CODCID SMALLINT, CEP CHAR(10), UF CHAR(2), TELEFONE CHAR(9), CGC CHAR(20), IE CHAR(20) ); 5.1.4 Eliminando Tabelas Esta instrução remove a tabela de seu banco de dados, inclusive as definições dos campos, índices e os dados existentes. Você não pode eliminar tabelas que estão relacionadas com outra tabela através de um objeto do BD. Sintaxe: DROP TABLE ‘table_name’ Vamos eliminar a tabela CLIENTE: DROP TABLE CLIENTE. 5.2 Instruções Manipulação de dados (DML) As instruções DML são responsáveis pela manipulação dos dados e um banco de dados. Permitem a inclusão de novas linhas, alteração e exclusão das existentes. As instruções são executadas com frequência e por certo número de usuários. 5.2.1 A instrução Insert A instrução Insert é a forma pelo qual podemos inserir uma ou mais linhas de dados em tabelas existentes no banco de dados. As linhas deverão atender às restrições de integridade e segurança definidas para o banco de dados, caso contrário a instrução falhará. Sintaxe: INSERT INTO ‘table_name’(col1,col2, col3, ...... colN) VALUES (value1, value2, value3, …. valueN); Value pode assumir um dos seguintes valores: b) Um literal - incluindo um número, caractere ou data; Variável – para inserir uma variável; 119 Banco de Dados I - UNIGRAN NULL – um valor nulo, somente se o campo não restringir explicitamente nulos; Uma das seguintes funções Interbase ou Firebird CAST(): Para converter um valor de um tipo de dado para outro; GEN_ID(): Para gerar um valor ID único; UPPER(): Para converter uma cadeia de caractere para letras maiúsculas. Se há uma integridade referencial entre duas tabelas, você deve inserir uma linha na tabela Principal (PK) antes de inserir dados na tabela dependente (FK). Por exemplo, eu tenho uma integridade referencial entre uma tabela CIDADE e uma tabela CLIENTE, no qual o relacionamento entre duas tabelas é o campo CODCID (PK) da tabela CIDADE e um campo CODCID (FK) na tabela CLIENTES. Para eu colocar uma cidade nova para um cliente, primeiramente, ela deve estar cadastrada na tabela cidade. Vamos inserir um registro na tabela CLIENTE: INSERT INTO CLIENTE (CODCLI, NOME, ENDERECO, BAIRRO, CODCID, CEP, UF, TELEFONE, CGC, IE) VALUES(1,’Adriano Câmara’, ‘Rua General, 130’, ‘Centro’, 2, ‘70825- 500’, ‘MS’, ‘4222-4533’, ‘222.222.222/0001-74’,’5555.4444.5555-32’); 5.2.2 A instrução Update Altera os valores das colunas especificadas para todas as linhas. Caso seja especificada uma condição na cláusula WHERE, somente as linhas que satisfaçam a essa condição serão afetadas. Você deve especificar na instrução todas as colunas que serão alteradas. Caso as alterações solicitadas não atendam às restrições de integridade e segurança a instrução falhará. Sintaxe: UPDATE ‘table_name’ SET Column1 = value, Column2 = value, …….. ColumnN = value [WHERE Conditions]; Como na instrução Insert, a cláusula value no Update pode também assumir qualquer um daqueles valores vistos. Vejamos como limitar as linhas a serem atualizadas: Na utilização da cláusula Where a conditions (condição) limita a atualização somente para linhas selecionadas na tabela, se você não especificar uma condição todas as linhas da tabela serão atualizadas, as condições são especificadas seguindo os formatos: 120 Banco de Dados I - UNIGRAN São exemplos da cláusula where: WHERE UF = ‘MS’ WHERE CODCID = 2 WHERE CODCLI BETWEEN 10 AND 20 WHERE CEP IS NOT NULL WHERE UF NOT IN (MS, MT, GO, DF) WHERE ((CODCLI >= 10) AND (CODCLI <= 50)) Vamos atualizar a tabela cliente trocando todos os estados para MT. UPDATE CLIENTE SET UF = ‘MT’ Vamos restringir a atualização, somente iremos trocar o estado caso ele seja SP. UPDATE CLIENTE SET UF=’MT’ WHERE UF=’SP’; Temos uma tabela produtos, no qual todos os seus itens que custam R$ 500,00 reais necessitam de uma atualização de 10%. UPDATE PRODUTOS Expressão Descrição = value Igual a < value Menor que > value Maior que <= value Menor ou igual a >= value Maior ou igual a <> value Diferente de !< value Não menor que !> value Não maior que != value Não igual a BETWEEN value AND value Entre a faixa de valores especifi cada LIKE value Busca de caracteres ‘%’ para 0 ou mais ‘_’ para 1] IN (value, value,....value) Um dos elementos da lista IS NULL Verifi ca se o valor em questão é nulo IS NOT NULL Verifi ca se o valor em questão não é nulo CONTAINIG value Procura uma cadeia de caracteres ‘insensiƟ ve’ STARTING WITH value Procura uma cadeia de caractere ‘case-sensiƟ ve’ 121 Banco de Dados I - UNIGRAN SET VALOR = VALOR * 1.1 WHERE VALOR = 500; 5.2.3 Instrução Delete Exclui todas as linhas existentes em uma tabela especificada. Caso seja especificada uma condição na cláusula WHERE, somente as linhas que satisfaçam a essa condição serão excluídas (MACHADO, 2004). Sintaxe: DELETE FROM ‘table_name’ [WHERE conditions]; Apagar todas as linhas (registros) da tabela CLIENTE. DELETE FROM CLIENTE Apagar somente os CEP NULOS (com valores em branco, ou vazios). DELETE FROM CLIENTE WHERE CEP IS NULL 5.2.4 Instrução Select A instrução Select é um dos mais importantes comandos do SQL. È essa operação que te permite extrair informação real das informações cadastradas em sua tabela. De acordo com as ideias de Machado (2004), quando suas tabelas atingem centenas, milhares de registros são impossíveis discernir linhas, encontrar informações importantes, ou agregar dados em um grupo usando o browsing pela tabelas. È também praticamente impossível unir duas ou mais tabelas manualmente, se você precisar relacionar dados armazenados através destas tabelas. Você pode usar o Select para fazer tarefas como: a) Criar uma lista de valores únicos em uma coluna; b) Criar uma lista de combinações de valores únicos em duas ou mais colunas; c) Limitar as linhas retornadas por uma operação; d) Encontrar linhas retornadas por uma operação; e) Encontrar linhas que satisfaçam uma seleção com múltiplos critérios; f) Agrupar os resultados de seu pedido em diferentes maneiras; g) Combinar informações de duas ou mais colunas em uma única coluna; h) Agregação de valores, e seleção entre agregações; i) Junções entre tabelas para extrair informações de cada tabela; j) Junções entre tabelas para agregar informações entre tabelas. Sintaxe: SELECT [ALL | DISTINCT] ‘selectOption ColumnList’ 122 Banco de Dados I - UNIGRAN FROM ‘table_list’ [WHERE searchConditions] [GROUP BY columnlist] [HAVING groupSearchConditions] [ORDER BY columnlist]; Onde: a) ALL: retorna todas as linhas no resultado da instrução, inclusive as linhas duplicadas; b) DISTINCT: elimina linhas duplicadas do resultado da instrução; c) FROM: especifica uma ou mais tabelas que originam as colunas selecionadas; d) WHERE: determina, por meio de uma expressão lógica, quais linhas da tabela de origem dever ser exibidas no resultado da instrução; e) GROUP BY: agrupa, em um única linhas, as linhas da tabela resultante que apresentarem o mesmo valor na coluna especificada. f) HAVING: da mesma forma que a cláusula Where permite a filtragem das linhas a serem exibidas, a cláusula Having filtra os grupos definidos em Group By, somente estes deverão ser exibidos. g) ORDER BY: permite ordenar os dados apresentados na tabela resultante do Select. Essa ordenação pode ser ascendente (ASC – padrão) ou descendente (DESC). Para exemplificarmos as instruções SQL, estaremos definindo um projeto de controle de pedidos de uma empresa, estaremos demonstrando o DER o projeto físico do BD e suas respectivas tabelas: FONTE: MACHADO, Felipe Nery Rodrigues; ABREU, Mauricio Pereira. Projeto de banco de dados: uma visão prática. 16 ed. São Paulo: Érica, 2009. Figura 8.2 DER: projeto físico 1 do BD. 123 Banco de Dados I - UNIGRAN Iremos agora colocas as informações nas tabelas para que possamos fazer as consultas, agregações e junções entre estas tabelas demonstrando o comando Select. Inserimos dados na tabela clientes, notem que temos alguns campos que estão em branco e que também apresentam valores null, ou seja, nulo sem valor. Neste caso, estes camposnão têm restrição quanto a campos nulos: FONTE: Acervo pessoal. FONTE: Acervo pessoal. Figura 8.3 DER: projeto físico 3 do BD. Figura 8.4 Tabela cliente. 124 Banco de Dados I - UNIGRAN Tabela cidade, ela está relacionada a tabela cidade, através de chave estrangeira, CODCID, que liga a tabela cidade a tabela cliente. Temos os dados na tabela vendedor que está relacionada ao setor no qual trabalha cada vendedor, esse relacionamento acontece com o campo CODSETOR: Temos aqui os dados das três últimas tabelas do nosso Banco de Dados, que se chama Vendas.fdb (“.FDB” é a terminação que indica um Banco de Dados do Firebird). Note que neste relacionamento entre estas três tabelas, neste caso a entidade fraca neste relacionamento é a entidade ItemPedido. Temos na tabela ItemPedido uma chave primária composta (NUMPED E CODPROD), e estes dois campos também são chaves estrangeira, no qual permitem o relacionamento das três tabelas, NUMPED com a tabela Pedido e CODPROD com a tabela Produto: FONTE: Acervo pessoal. FONTE: Acervo pessoal. Figura 8.5 Tabela cidade. Figura 8.6 Tabela vendedor. 125 Banco de Dados I - UNIGRAN Iremos agora utilizar a instrução Select, vamos primeiro começar a especificar as colunas e tabelas que consultadas na tabela. Vamos utilizar primeiramente a tabela cliente: a) Select * from cliente – Neste caso o comando retornará todas as colunas da tabela, neste caso o SQL entende o ‘*” como atalho. Neste exemplo o resultado da consulta SQL seria a tabela Clientes com todos os dados, como na figura Tabela Cliente. b) Select codcli, nome, uf from cliente – Estamos neste caso limitando a exibição dos dados da tabela, somente serão mostrados os campos especificados. Veja: FONTE: Acervo pessoal. FONTE: Acervo pessoal. Figura 8.7 Tabela ItemPedido. Figura 8.8 Select codcli, nome, uf from cliente. 126 Banco de Dados I - UNIGRAN c) Select distinct (bairro) from cliente – Temos no SQL a cláusula DISTINCT que na hora do retorno do resultado ele exclui os valores duplicados. d) Select codcli, nome, uf, bairro, telefone from cliente. Order by nome: Neste caso notem que o resultado foi ordenado pelo campo (coluna), NOME, neste caso o Firebird executará o ASC (ascendente), pois é o padrão SQL. Para colocarmos em ordem decrescente ficara assim: Order by nome Desc. Select codcli, nome, uf, bairro, telefone from cliente Where telefone is not null Estamos agora utilizando o “WHERE” para limitar as linhas que serão retornadas, neste caso todos os telefones com valores nulos não serão exibidos. Para ordenar pelo nome basta novamente no final colocar: Order by Nome. Select * from cliente Where UF IN(‘SP’,’DF’,’MT’) Order by nome, endereco Vejamos: Neste exemplo, ele irá listas todos os campos (colunas) da tabela cliente, e mostrar somente os presente na lista (IN), neste caso, SP, DF,MT. Note que também ele ira ordenar o resultado primeiramente por nome, se existir nomes iguais ele irá ordenar por endereço. Para fazer o contrário, ou seja, listar os nomes não presentes nestes estados basta colocar: WHERE UF NOT IN(‘SP’,’DF’,’MT’). FONTE: Acervo pessoal. FONTE: Acervo pessoal. Figura 8.9 Order by nome. Figura 8.10 Order by nome, endereco. 127 Banco de Dados I - UNIGRAN Select codcli, nome, endereço, bairro, codcid, cep, uf, telefone, cgc, ie from cliente Where (UF IN(‘SP’,’DF’,’MT’)) and (CGC is not null) Order by nome,endereço Observe: Refinamos ainda mais nossa consulta, agora somente foi retornado os campos que estavam dentro da lista (IN) e (and) os campos que possuem o CGC com valor diferente de nulo (vazio, em branco). Notem que tivemos que colocar a primeira verificação e a segunda entre parênteses “()”. Select codcli, nome, cgc, ie from Cliente Where codcli BETWEEN 20 and 25 Order by codcli, nome Retornamos os campos entre a faixa de valores especificados em Between. Select codcli, nome, endereco,bairro,codcid,cep,uf,telefone, cgc, ie from cliente Where nome LIKE ‘%Adriano Câmara%’ FONTE: Acervo pessoal. FONTE: Acervo pessoal. FONTE: Acervo pessoal. Figura 8.11 Order by nome,endereço. Figura 8.12 Order by codcli, nome. Figura 8.13 Select codcli, nome, endereco,bairro,codcid,cep,uf,telefone, cgc, ie from cliente. 128 Banco de Dados I - UNIGRAN Nesse caso ele irá retornar somente os dados do nome especificado. Podemos também pedir para concatenar esta busca, e também entrar com uma variável, no qual nós iremos pedir o nome que o SQL irá pesquisar na tabela. Neste caso estaremos digitando o nome e ele irá pesquisar qualquer pedaço do nome digitado, seria esta a instrução: where NOME LIKE ‘%’||:NOME||’%’ Select codcli, nome, endereço from cliente Where nome Starting With ‘Gil’ Neste caso ele procurou uma cadeia de caracteres na coluna nome e retornaram quais foram satisfeitos no With. Selec codcli, nome, codcid from cliente Where Codcid = 1 Order by nome Foram retornados somente os códigos das cidades que possuem os valores iguais a 1 e ordenados por nome. 5.2.5 Unindo Tabelas Seguindo as ideias de Machado (2004), a informação em um BD normalizado é geralmente distribuída em mais de uma tabela. Muitas das perguntas que você precisa fazer sobre os seus dados requerem que duas ou mais tabelas sejam unidas para listar colunas ou alocar critérios de seleção em cada tabela. Quando normalizamos um BD, é comum acabar com colunas que são compartilhadas entre tabelas. Estas são as colunas que te permitem estabelecer conexões lógicas entre estas tabelas. Notem que a coluna CODCID serve para identificar cada linha única da tabela Cidade. Esta coluna aparece também na FONTE: Acervo pessoal. FONTE: Acervo pessoal. Figura 8.14 Select codcli, nome, endereço from cliente. Figura 8.15 Order by nome. 129 Banco de Dados I - UNIGRAN tabela cliente. Portanto, a tabela CIDADE pode ser unida à tabela CLIENTES, para isto nós utilizamos os relacionamentos entre PF X FK. Neste caso quando você estabelece uma junção o Firebird procura por valores combinantes nas colunas designadas de cada tabela. Sintaxe: SELECT ‘Nome_da_tabela.nome_da_coluna’, [Nome_da_tabela.nome_da_coluna. .....] FROM ‘tabela1’ Alias1 JOIN ‘tabela2’ Alias2 ON Alias1.Column = Alias2.Column Where ‘conditions’ Notem que quando listamos os dados da tabela cliente, sempre é retornado o CODCID com um número, este número se refere ao CODCID que foi cadastrado na tabela cidade. Mas, nós queremos que o retorno desta informação, seja o nome da cidade. Pois com o código não dá para identificarmos qual cidade pertence os clientes. SELECT C.CODCLI, C.NOME, C.ENDERECO, C.BAIRRO, C.UF, C.TELEFONE, D.NOMECID AS CIDADE FROM CLIENTE C JOIN CIDADE D ON C.CODCID = D.CODCID No SELECT estamos dizendo quais são as colunas que serão exibidas no retorno da consulta SQL, neste caso ‘C’ é a tabela CLIENTE. Poderíamos colocar – CLIENTE.CODCLI, CLIENTE.NOME, CLIENTE.ENDERECO. Temos também “D.NOMECID AS CIDADE”, neste caso “D” representa a tabela CIDADE e nomecid é a coluna que armazena o nome da cidade, utilizamos aqui um recurso do Firebird para colocar um nome na coluna resultante, neste caso o “AS” no retorno da informação a coluna resultante terá o nome de CIDADE. Em seguida colocamos quais tabelas serão unidas e seus respectivos ALIAS (apelidos). Na última instrução colocamos a chaves que fazem as ligações entre as tabelas. FONTE: Acervo pessoal. Figura 8.16 C.Codcli, C.Nome, C.Endereco, C.Bairro, C.Uf, C.Telefone, D.Nomecid As Cidade. 130 Banco de Dados I - UNIGRAN SELECT C.CODCLI, C.NOME, C.ENDERECO, C.BAIRRO, C.UF, C.TELEFONE, D.NOMECID AS CIDADE FROM CLIENTE C JOIN CIDADE D ON C.CODCID = D.CODCID WHERE C.CODCID = 3 ORDER BY C.NOME Agora estamos somente retornando nesta junçãoas cidades que tem o código 3, e ordenando por nome do cliente. Poderíamos selecionar as cidades com código 3 e também com telefone não nulos. SELECT C.CODCLI, C.NOME, C.ENDERECO, C.BAIRRO, C.UF, C.TELEFONE, D.NOMECID AS CIDADE FROM CLIENTE C JOIN CIDADE D ON C.CODCID = D.CODCID WHERE (C.CODCID = 3) AND (C.TELEFONE is not null) ORDER BY C.NOME Vamos utilizar o mesmo recurso para saber quais os setores que alguns vendedores trabalham, neste caso, têm que somente trocar os nomes das tabelas e suas respectivas chaves que efetuam o relacionamento. SELECT C.CODVEND, C.NOMEVEND, D.NOMESETRO AS SET OR, C.SALARIO FROM VENDEDOR C JOIN SETOR D ON C.CODSETOR = D.CODSETOR ORDER BY C.NOMEVEND FONTE: Acervo pessoal. FONTE: Acervo pessoal. Figura 8.17 Select C.Codcli, C.Nome, C.Endereco, C.Bairro, C.Uf, C.Telefone, D.Nomecid As Cidade. Figura 8.18 C.Codcli, C.Nome, C.Endereco, C.Bairro, C.Uf, C.Telefone, D.Nomecid As Cidade. 131 Banco de Dados I - UNIGRAN Vamos também filtrar a consulta, agora somente dos vendedores de ferramentas e que ganham acima de 600 reais. SELECT C.CODVEND, C.NOMEVEND, D.NOMESETRO AS SET OR, C.SALARIO FROM VENDEDOR C JOIN SETOR D ON C.CODSETOR = D.CODSETOR WHERE (C.CODSETOR = 4) AND (C.SALARIO > 600) ORDER BY C.NOMEVEND Iremos agora fazer uma junção de três tabelas, CLIENTES X CIDADE para retornar o nome da cidade, E CLIENTE X PEDIDO, para retornar quais os pedidos feitos por todos os clientes cadastrados. SELECT C.CODCLI, C.NOME, C.TELEFONE, D.NOMECID AS CIDADE, E.NUMPED AS PEDIDO FROM CLIENTE C JOIN CIDADE D ON C.CODCID = D.CODCID JOIN PEDIDO E ON C.CODCLI = E.CODCLI ORDER BY NOME FONTE: Acervo pessoal. FONTE: Acervo pessoal. Figura 8.19 Select C.Codvend, C.Nomevend, D.Nomesetro As Set Or, C.Salario. Figura 8.20 Select C.Codvend, C.Nomevend, D.Nomesetro As Set Or, C.Salario. 132 Banco de Dados I - UNIGRAN Podemos agora juntar também mais uma tabela, neste caso, saber qual o vendedor que fez este pedido e retornar o nome dele da tabela VENDEDOR. SELECT C.CODCLI, C.NOME, C.TELEFONE, D.NOMECID AS CIDADE, E.NUMPED AS PEDIDO, F.NOMEVEND AS VENDEDOR FROM CLIENTE C JOIN CIDADE D ON C.CODCID = D.CODCID JOIN PEDIDO E ON C.CODCLI = E.CODCLI JOIN VENDEDOR F ON E.CODVEND = F.CODVEND ORDER BY NOME Vamos agora utilizar o exemplo final de junção de tabelas, bem podemos também saber quão o setor que este vendedor trabalha, ou seja, faremos à junção com mais uma tabela. FONTE: Acervo pessoal. FONTE: Acervo pessoal. Figura 8.21 Select C.Codcli, C.Nome, C.Telefone, D.Nomecid As Cidade, E.Numped As Pedido. Figura 8.22 Select C.Codcli, C.Nome, C.Telefone, D.Nomecid As Cidade, E.Numped As Pedido. 133 Banco de Dados I - UNIGRAN SELECT C.CODCLI, C.NOME, C.TELEFONE, D.NOMECID AS CIDADE, E.NUMPED AS PEDIDO, F.NOMEVEND AS VENDEDOR, G.NOMESETOR AS SETOR FROM CLIENTE C JOIN CIDADE D ON C.CODCID = D.CODCID JOIN PEDIDO E ON C.CODCLI = E.CODCLI JOIN VENDEDOR F ON E.CODVEND = F.CODVEND JOIN SETOR G ON F.CODSETOR = G.CODSETOR ORDER BY NOME Notem que não utilizei nenhum exemplo com a cláusula WHERE, eu ainda poderia refinar mais a pesquisa. Poderia pedir para retornar somente os pedidos feitos pelo no setor de VENENOS, ficaria assim o final da consulta. WHERE F.CODSETOR = 2 ORDER BY NOME Ou somente os pedidos feitos na cidade de Dourados, pelo Vendedor Jair Costa, basca acrescentar a cláusula WHERE a seguir. WHERE (C.CODCID = 1) AND ( E.CODVEND=5) 5.2.6 Agrupando informações selecionadas com GROUP BY e HAVING Antes que vermos as instruções, vamos aprender duas funções: SUM – Esta função totaliza os valores de uma coluna. Vamos pedir para consultar a quantidade de pedidos para o Produto Glifossato. SELECT SUM(QTIDADE) as Quantidade FROM ITEMPEDIDO WHERE CODPROD=2 COUNT – Esta função conta o total dos registros (campos) satisfazem à seleção. SELECT COUNT(SALARIO) as QtSal FROM VENDEDOR FONTE: Acervo pessoal. Figura 8.23 Select C.Codcli, C.Nome, C.Telefone, D.Nomecid As Cidade, E.Numped As Pedido, F.Nomevend As Vendedor, G.Nomesetor As Setor. 134 Banco de Dados I - UNIGRAN WHERE SALARIO > 800 A função de agregação por si mesmo produz um número simples para uma tabela. A cláusula organiza uma lista de dados em grupos, produzindo informação organizada por este grupo, que será a tabela resultante da consulta. Vamos listar o número de produtos de cada pedido contém: SELECT NUMPED, COUNT(CODPROD) as TOT_PRODUTOS FROM ITEMPEDIDO GROUP BY NUMPED A cláusula HAVING realiza as restrições das linhas resultantes do GROUP BY da mesma forma que a cláusula WHERE o faz no SELECT. Agora iremos listar os pedidos que tem mais do que três produtos cadastrados. SELECT NUMPED, COUNT(CODPROD) as TOT_PRODUTOS FROM ITEMPEDIDO GROUP BY NUMPED HAVING COUNT(CODPROD) > 3 FONTE: Acervo pessoal. FONTE: Acervo pessoal. Espero que seus estudos referentes a esta oitava aula tenham sido proveitosos! Agora, é importante que você acesse o ambiente virtual e realize as aƟ vidades propostas como avaliação conƟ nuada desta Aula... Ah! Caso ainda tenha dúvidas, sugerimos que acesse o ambiente virtual e uƟ lize as ferramentas apropriadas Figura 8.24 Select Numped, Count(Codprod) As Tot_Produtos From Itempedido. Figura 8.25 Select Numped, Count(Codprod) As Tot_Produtos From Itempedido. 135 Banco de Dados I - UNIGRAN Retomando a conversa inicial • Seção 1 – Conceito Nesta primeira seção, tivemos a oportunidade de refletir sobre a expressão SQL que significa Linguagem Estruturada de Pesquisa, vimos que essa linguagem e seus fundamentos nas especificações de Edgar F. Cood que criou o modelo relacional no qual determinava que os usuários deveriam ter acesso ao banco de dados por meio de uma linguagem de consulta e que ela seria a única forma de acesso ao banco de dados. • Seção 2 – Características da SQL Na segunda seção, construímos conhecimentos sobre a grande vantagem da utilização do SQL é a sua facilidade de manipulação e entendimento, pois o SQL é simples e fácil, seu comandos são escritos em linguagem natural o inglês, permitindo escrever suas consultas relativamente rápidas. • Seção 3 – Estrutura da SQL Na terceira seção, estudamos que a linguagem SQL não é case-sensitive, ou seja, não faz diferença entre letras maiúsculas e minúsculas. Dentre as principais particularidades na nomenclatura da linguagem destacamos algumas formas de padronizar as instruções. para se comunicar com seus colegas de curso e com seu professor. Além disso, é importante que refl ita sobre os conteúdos e as estratégias didáƟ cas empregadas para a aprendizagem dos conteúdos propostos nesta atual: o que foi bom? O que pode melhorar? Lembre-se de que estaremos esperando suas sugestões para melhorar nossos recursos e técnicas didáƟ cas uƟ lizados no curso. Afi nal, na EAD a construção de conhecimento é um trabalho de todos. ParƟ cipe, nós também queremos aprender com você! Parece que estamos indo bem! Então, para encerrar esta aula, vamos recordar os temas que foram abordados: 136 Banco de Dados I - UNIGRAN • Seção 4 – Tipos de dados Na quarta e penúltima seção vimos que todos os bancos de dados relacionais armazenam dados em tabelas que compreendem linhas (registros) e colunas (campos). Cada coluna representa informação de um único tipo, cada linha representa uma instância desta informação. Compreendemos que uma coluna pode armazenar texto, número, data e outros tipos de dados. Quando você define uma coluna como parte de uma tabela você dever especificar o tipo de dado que ela armazenará. Alguns tipos de dados nós temos que especificar o tamanho máximo daquela coluna. • Seção 5 – Linguagem SQL e suas instruções Para finalizar, reconhecemos a linguagem SQL na qual representaum grupo de comandos responsáveis pela classificação das tabelas, comandos e atualização dos dados em um SGBD. Sugestões de leituras, sites e vídeos Leituras ALVES, W. P. Fundamentos de Bancos de Dados. Editora: Érica, 2004. DATE, J. C. Introdução a sistemas de banco de dados. Editora: Campus, 2005. ELMASRI, R. Fundamentals of database systems. 3. ed. Amsterdam: Addison- Wesley, 2000. ______; NAVATHE, Shamkant. Sistemas de Banco de Dados. 4 .ed. Editora: Pearson / Prentice Hall (Grupo Pearson), 2005. HEUSER, C. A. Projeto de Banco de Dados. Porto Alegre: Sagra Luzzatto, 2001. LIGHTSTONE, S; TEOREY, T; NADEAU, T. Projeto e Modelagem de Bancos de Dados. 1. ed. Editora: Campus, 2006. MACHADO, F. N. R. Banco de Dados: Projeto e Implementação. Editora: Érica, 2004. MACHADO, Felipe Nery Rodrigues; ABREU, Mauricio Pereira. Projeto de banco de dados: uma visão prática. 16 ed. São Paulo: Érica, 2009. ______. Banco de Dados: Uma visão Prática. 12. ed. Editora: Érica, 2005. Nossa! Fizemos uma caminhada boa nesta aula, não acham? Adquirimos conhecimentos interessantes e essenciais para a nossa formação enquanto profi ssionais. Para fundamentar e melhorar ainda mais a nossa aquisição de saberes, iremos, a seguir, dar sugestões de leituras, sites, fi lmes e vídeos que vocês podem acessar para oƟ mizar as informações dadas nesta aula. 137 Banco de Dados I - UNIGRAN SETZER, V. W; SILVA, F. S. C. Banco de Dados. 1. ed. Editora: Edgard Blucher, 2005. Sites • CARVALHO, P. F. Linguagem SQL. Disponível em: <http://www. pedrofcarvalho.com.br/basico.pdf>. Acesso em: 15 nov. 2013. • COHEN, M. Alguns aspectos do uso da informação na economia da informação. Revista Ciência da Informação, Brasília, v. 31, n. 3, set./dez. 2002. 26-36 p. Disponível em: <http://revista.ibict.br/index.php/ciinf/article/view/144/124>. Acesso em: 26 set. 2013. • KLABIN. Centro de Documentação e Memória da Klabin. Disponível em: <http://www.klabin.com.br/pt-br/klabin/centroMemoria.aspx>. Acesso em: 26 set. 2013. • MARCILON, R. Uma palavra dobre des/normalização. Disponível em: <http://reginaldomarcilon.blogspot.com.br/2012/01/uma-palavra-sobre- desnormalizacao.html>. Acesso em: 14 out. 2013. • NEHMY, R. M. Q; PAIM, I. A desconstrução do conceito de "qualidade da informação". Ciência da Informação. Brasília, vol. 27, no. 1, 1998. Disponível em: <http://www.scielo.br/scielo.php?script=sci_arttext&pid =S0100-19651998000100005>. Acesso em: 26 set. 2013. • PESTANA, M. C et al. Desafi os da sociedade do conhecimento e gestão de pessoas em sistemas de informação. Revista Ciência da Informação, Brasília, v. 32, n. 2, p. 77-84, maio/ago. 2003. Disponível em: <revista.ibict.br/index.php/ ciinf/article/download/121/102>. Acesso em: 26 set. 2013. • SETZER, V. W. Dado, informação, conhecimento e competência. Disponível em: <http://www.dgz.org.br/dez99/Art_01.htm>. Acesso em: 26 set. 2013. Vídeos • YOU TUBE. Banco de Dados Modelagem e Estrutura em SQL. Disponível em: <http://www.youtube.com/watch?v=T0-_pO0QPsQ>. Acesso em: 14 out. 2013. • ______. Curso SQL Aula 2: Estrutura de Dados. Disponível em: <http://www. youtube.com/watch?v=8GNgnRwcowg>. Acesso em: 14 out. 2013. 138