Baixe o app para aproveitar ainda mais
Prévia do material em texto
Implementação de banco de dados Aula 1: Álgebra relacional Apresentação Nesta aula, você vai estudar o Modelo Relacional e a Álgebra Relacional. No mundo digital, nossas vidas são regidas por diversos sistemas. Para funcionar, esses sistemas necessitam de grande volume de dados. Os sistemas informatizados evoluíram ao longo do tempo de Sistemas Baseados em Arquivos para Sistemas Baseados em Banco de Dados. Não é possível conceber, atualmente, qualquer sistema que possa prescindir de um Banco de Dados. Os Sistemas de Banco de Dados atuais são, majoritariamente, baseados no Modelo Relacional. Entretanto, de que adiantaria termos uma estrutura de armazenamento se não conseguíssemos manipular os dados ali armazenados? Como o modelo relacional teve sua origem na matemática, essa mesma ciência nos fornece a ferramenta para manipulá-lo, a Álgebra Relacional. Objetivos Conhecer as principais características de um Sistema de Banco de Dados; Identi�car os componentes do modelo relacional; Conceituar Álgebra Relacional, suas operações e expressões. Vamos dar início com um vídeo que orienta como baixar e instalar o PostGreSQL, que é um SGBD utilizado em toda a disciplina, possibilitando o acompanhamento prático e a realização das atividades propostas, fundamentais para que os objetivos da disciplina sejam alcançados. Atenção! Aqui existe uma videoaula, acesso pelo conteúdo online Sistemas de banco de dados Podemos iniciar o estudo com a apresentação da de�nição de FERREIRA et al. (1999, p. 62) para dados: Dado é o “princípio em que se assenta uma discussão” ou o “elemento ou base para a formação de um juízo”. Ainda, assumindo-se um ponto de vista mais �losó�co, dado é “o que se apresenta à consciência como imediato, não construído ou não elaborado”, FERREIRA et al. (1999, p. 602). Essas de�nições são úteis para exempli�car o termo "dado" e situar sua de�nição de um ponto de vista mais humanizado. Computacionalmente falando, pode-se de�nir dado como um valor armazenado e que por si só não quer dizer muita coisa. Uma lista de números - por exemplo, 10, 12, 20, 21, 23, 38 - não nos fornece nenhum signi�cado, mas, por assim dizer, são os dados obtidos e anotados de alguma forma. A necessidade de armazenar os dados precede a criação do conceito de Sistemas de Banco de dados. Atenção Antigamente, os dados eram armazenados em arquivos. Normalmente são associados a um único sistema de aplicação, trazendo, por consequência, redundância de dados quando os mesmos se faziam necessários em outros sistemas. A maioria das organizações desenvolve os seus Sistemas de Informação um por vez, à medida que se tornam necessários, utilizando cada um o seu próprio conjunto de programas, arquivos e usuários. A �gura 1 mostra a estrutura de um sistema baseado em arquivo. Nela podemos notar que: 1 Cada aplicação possui seus próprios arquivos de dados; 2 Se uma aplicação compartilha dados com outras, esses têm que ser duplicados; 3 Qualquer alteração no modelo de dados implica em alteração do código da aplicação. Figura 1: Sistemas baseados em arquivos. O esquema apresentado acaba acarretando em: Redundância de dados Trata-se da duplicação dos mesmos dados em dois ou mais arquivos. O problema com a redundância é que as mudanças, ao serem feitas no arquivo de uma aplicação, não são automaticamente realizadas nos arquivos das outras aplicações, gerando a falta de integridade dos dados. Dependência entre programas e dados Os aplicativos tradicionais de banco de dados de arquivos são caracterizados pela dependência entre programas e dados, isso é, programas e dados desenvolvidos e organizados para uma aplicação são incompatíveis com os programas e dados organizados diferentemente para um outro aplicativo. Custo excessivo em software Resulta da criação, documentação e acompanhamento de muitos arquivos e aplicações diferentes. Fonte: SILBERSCHATZ, A.; KORTH, H. F.; SUDARSHAN, S., 2006. A evolução da tecnologia associada ao grande aumento do volume de dados a ser armazenado e processado levou à criação de uma nova abordagem para o problema, o Sistema de Banco de Dados. Um banco de dados é uma coleção de dados organizados de tal forma que possam ser acessados e utilizados por muitas aplicações diferentes. Ou seja, em vez de armazenar dados em arquivos separados para utilização, os dados são armazenados e organizados em um só local, permitindo compartilhamento e inter-relacionamento por múltiplos usuários. A �m de usar a abordagem de banco de dados para o gerenciamento de dados, foi necessário um software adicional – um sistema de gerenciamento de banco de dados (SGBD). O SGBD consiste em um grupo de programas que podem ser usados como uma interface entre o banco de dados e um usuário ou um banco de dados e um programa aplicativo.(Fig 2) Figura 2: Sistemas de banco de dados. Modelo relacional Nos maiores SGBD comerciais, o modelo de dados utilizado é o Modelo Relacional criado com base na Teoria Matemática dos Conjuntos e na Álgebra Relacional, O Modelo de Dados Relacional tem as seguintes vantagens: É independente das linguagens de programação; É independente dos sistemas de gestão de bases de dados; É independente dos Sistemas Operacionais. “O modelo relacional representa o banco de dados como uma coleção de relações.” ELMASRI, R.; NAVATHE, S., 2015. Da a�rmativa acima. pode-se depreender uma relação como uma tabela de valores onde cada linha representa uma coleção de dados ou valores relacionados. Cada linha da tabela representa uma realidade ligada ao mundo real. O nome da tabela e o nome das colunas são de�nidos de forma que representem essa realidade. Pode-se ter uma tabela chamada ALUNO onde cada linha venha a ser o Nome de um Aluno a ser armazenado. Podemos melhorar ainda mais essa representação, criando uma tabela com as colunas NOME, NÚMERO DE MATRÍCULA e CLASSE. Cada linha da tabela representa várias informações de um ALUNO e cada coluna isoladamente, representa uma informação especí�ca desse aluno. Assim o nome da tabela e os nomes das colunas são capazes de dizer o que cada linha representa e também o que o conjunto representa. No modelo relacional formal (Figura 3), cada linha é chamada de tupla, o nome da coluna é conhecido como atributo ou variável, e a tabela, relação. (FONSECA, 2016) Regras de Integridade relacional As regras de Integridade Relacional visam garantir a �delidade de informações em um banco de dados. Basicamente, são três as formas mais comuns: Clique nos botões para ver as informações. Diz respeito aos dados que são permitidos nas colunas da relação (tabela). Um domínio é um conjunto de valores do mesmo tipo. Os domínios são, portanto, conjuntos/faixas de valores a partir dos quais os valores reais são adicionados às colunas de uma tabel. Integridade de Domínio Diz respeito à unicidade de linhas da relação. Para tal, cada tabela deve ter uma chave primária (Primary Key - PK). Dessa forma., as colunas escolhidas para PK devem ser únicas e de preenchimento obrigatório (não nulas), como, por exemplo, as colunas Código na tabela Emp e Codigodepto na tabela Depto (Fig 3); Integridade de Entidade Diz respeito à consistência entre as tuplas de relações. Para tal, as tabelas devem possuir chaves estrangeiras (Foreign Key – FK). A FK de uma tabela com, por exemplo, CodigoDepto na tabela EMP (Fig3) deve referenciar uma PK de outra tabela, no caso CodigoDepto da tabela Depto.(Fig3). Os valores possíveis em uma FK são limitados aos existentes na PK referenciada. Se em um banco de dados você tentar incluir uma linha com valor de FK não existente na PK da outra tabela, o SGBD gerará um erro e não permitirá a operação, garantindo assim a consistência dos dados. Integridade Referencial Figura 3: Componentes do modelo relacional. Á Álgebra relacional É o conjunto básico de operações para o modelo relacional. Essas operações permitem a recuperação de tuplas mediante instruções de consulta aplicadas ao banco de dados. O resultado dessa recuperaçãotambém será uma relação, que pode ser usada em outras operações de consulta. A importância da álgebra relacional: Provê fundamento formal para operações do modelo relacional; Alguns de seus conceitos são incorporados na linguagem SQL padrão. E o mais importante: é usada como base para implementar e otimizar as consultas em sistemas de banco de dados relacional. (FONSECA, 2016, p. 15) As principais operações da álgebra relacional são: 1 Seleção 2 Projeção 3 Produto Cartesiano 4 Operações de Conjunto (União, Intersecção, Diferença) As operações de SELECT (SELEÇÃO) E PROJECT (PROJEÇÃO) são ditas operações unárias, pois atuam em relações únicas (ELMASRI, R.; NAVATHE, S., 2015). Operação de seleção A operação de Seleção recupera os dados de uma ou mais tabelas, selecionando um subconjunto de tuplas que satisfaça determinada condição de seleção. Essa condição de seleção é usada para dividir horizontalmente uma relação em dois conjuntos de tuplas – as tuplas que satisfazem a condição e são retornadas e as tuplas que não satisfazem a condição e são ignoradas. (FONSECA, 2016, p.16) A forma geral de uma operação de seleção é: (nome da relação) <condição de seleção> σ Onde: • <condição de seleção> é a condição que a linha deve atender para ser selecionada. Constituindo-se em uma expressão lógica, é construída a partir de cláusulas da forma: <nome de atributo> <operador de comparação> <valor constante >, ou <nome de atributo> <operador de comparação> <nome de atributo > Onde: <nome de atributo> é um atributo da relação de�nida em <nome da relação> <operador de comparação> é normalmente um dos operadores relacionais {=, <, ≤, ≥, ≠} e <valor constante> é um literal. As cláusulas podem ser utilizadas em conjunto com os operadores lógicos {AND, OR NOT}, seguindo a Lógica Booleana, para formar uma condição de seleção composta. <nome da relação> é o nome da relação sobre a qual será aplicada a operação de Seleção A relação resultante da operação tem os mesmos atributos da relação especi�cada em <nome da relação>. Exemplo Considere a Tabela Disciplina_Nota (Fig 4) Para selecionar os alunos da disciplina ‘álgebra’ cuja nota foi integral, pode-se especi�car cada uma dessas condições em uma operação de SELEÇÃO: NOME_DISCIPLINA = ‘ALGEBRA’ AND NOTA = 10 (Disciplina_Nota) NOME_DISCIPLINA = ‘ALGEBRA’ AND NOTA = 10 Resultando a nova relação: NOME_ALUNO NOME_DISCIPLINA NOTA 1 José Geraldo Álgebra 8 2 Eduardo Tomaz Álgebra 10 3 Cleber Dutra Álgebra 9 4 Hernesto Paula Álgebra 10 5 Josué José Álgebra 10 Figura 4: Tabela DISCIPLINA_NOTA NOME_ALUNO NOME_DISCIPLINA NOTA 1 Eduardo Tomaz Álgebra 10 2 Josué José Álgebra 10 Figura 5: Resultado de uma operação de Seleção | Fonte: FONSECA, 2016. Operação de Projeção A operação de Projeção recupera os dados de certas colunas de uma tabela e descarta outras. Se existir a necessidade de mostrar apenas alguns atributos de uma tabela em detrimento de outros, usa-se a operação PROJEÇÃO. (FONSECA, 2016, p. 17) A forma geral da operação de projeção é: π <lista de atributos> (<nome da relação>) Onde: <lista de atributos> representa a lista de atributos que o usuário deseja selecionar e <nome da relação> representa a relação sobre a qual a operação projeção será aplicada. Exemplo Para selecionar as disciplinas e as ocorrências de notas nas disciplinas na tabela da �g 4, teremos: π <Nome_Disciplina, Nota> (<Disciplina_Nota>) Resultando na relação: NOME_DISCIPLINA NOTA 1 Álgebra 8 2 Álgebra 9 3 Álgebra 10 4 Álgebra 10 Figura 6: Resultado de uma operação projeção. Sequencialidade de Operações A Projeção e a Seleção podem ser combinadas, de forma que apenas algumas linhas e algumas colunas retornem na operação. A forma geral de uma operação sequencializada é: π <lista de atributos> (σ <condição de seleção> (<nome da relação>) ) Onde: π representa a operação de projeção; <lista de atributos> representa a lista de atributos que o usuário deseja selecionar; σ representa a operação de seleção; <condição de seleção> é a condição que a linha deve atender para ser selecionada; <nome da relação> é o nome da relação sobre a qual será aplicada a operação de Seleção e Projeção Exemplo Para projetar a partir da Tabela Disciplina_Nota, o nome das disciplinas e a nota para alunos com nota diferente de 10 a expressão seria: π Nome_Disciplina, Nota ( σ <> 10 (Disciplina_Nota) ) NOME_DISCIPLINA NOTA 1 Álgebra 8 2 Álgebra 9 Figura 7: Resultado de uma operação projeção após uma seleção. Operação Produto Cartesiano O produto cartesiano é uma operação binária que combina todas as tuplas de duas tabelas. O resultado de um produto cartesiano é uma nova tabela formada pela combinação das tuplas das tabelas sobre as quais aplicou-se a operação. O formato geral do produto cartesiano entre duas tabelas A e B é: Operação Junção A operação Join (Junção) é usada para combinar as tuplas relacionadas em duas relações dentro de uma única tupla. Essa operação é uma das mais importantes em um banco de dados relacional, pois ela nos permite retornar os relacionamentos entre as relações (tabelas). A forma geral da operação junção entre duas tabelas A e B é a seguinte: A X B <condição de junção> Onde: <condição de junção> é uma expressão relacional, normalmente utilizando o operador =, que determina qual coluna da tabela A deve ser comparada com qual coluna da tabela B. Observação: normalmente as colunas envolvidas na condição de junção são a Chave Primária de uma tabela e a Chave Estrangeira na outra. Exemplo Considere o banco de dados composto pela tabela Disciplina_Nota (Fig 4). Imagine que nosso modelo de banco de dados de�nido anteriormente com a tabela DISCIPLINA_NOTA (Fig4) seja rede�nido para um modelo mais trabalhado com as tabelas: DISCIPLINA_NOTA, DISCIPLINA E PESSOA, inserindo uma chave primária (PK) que identi�ca unicamente um nome, a tabela DISCIPLINA_NOTA com a chave estrangeira (FK) FK_NOME_ALUNO relacionando com a tabela PESSOA e a FK_NOME_DISCIPLINA relacionando com a tabela DISCIPLINA. Temos, então, a seguinte melhoria em nosso banco de dados (FIG 8): π NOME_DISCIPLINA, NOME_ALUNO, NOTA(PESSOA X DISCIPLINA_NOTA X Disciplina) PK_NOME_PESSOA = FK_NOME_ALUNO FK_NOME DISCIPLINA = PK_DISCIPLINA NOME_ALUNO NOME_DISCIPLINA NOTA 1 José Geraldo Álgebra 8 2 Eduardo Tomaz Álgebra 10 3 Cleber Dutra Álgebra 9 4 Hernesto Paula Álgebra 10 5 Josué José Álgebra 10 Figura 9: Operação JOIN. Operação de Conjuntos São operações derivadas das operações matemáticas padrão de�nidas a partir da teoria dos conjuntos. (FONSECA, 2016, p. 18) São elas: UNION (UNIÃO), INTERSECTION (INTERSEÇÃO) e MINUS (SUBTRAÇÃO)] Clique no botão acima. UNION UNION é a operação de UNIÃO da teoria de conjuntos. Se temos as relações R(A , A , ..., A ) e S(B , B , ..., B ) para haver a operação União, os atributos de cada relação devem ser compatíveis entre si, ou seja, devem ter o mesmo grau (n) e os domínios de cada atributo devem ser iguais ao domínio do outro atributo, ou seja, dom(A ) = dom(B ). Signi�cando que cada relação possui o mesmo grau e que cada par de atributos possuem o mesmo domínio. Sendo assim, a operação UNION pode ser aplicada. (FONSECA, 2016, p. 18) A forma geral da operação junção entre duas tabelas A e B é a seguinte: A U B A Operação de União é comutativa, ou seja, A U B produz o mesmo resultado de B U A. Exemplo: Temos a tabela DISCIPLINA_NOTA (Fig 4) e a nova tabela PROFESSOR (Fig10). Podemos fazer a união dos domínios ‘NOME_PESSOA’ com os nomes dos alunos e os nomes dos professores. π NOME_ALUNO (DISCIPLINA_NOTA) U PROFESSOR 1 2 n 1 2 n 1 1 NOME_PROFESSOR 1 Fagundes Teles 2 Ferreira 3 Elmasri 4 Navathe 5 Cleber Dutra Figura 10: TABELA PROFESSOR. NOME_PESSOA 1 Cleber Dutra 2 Eduardo Tomaz 3 Elmasri 4 Fagundes Teles 5 Ferreira 6 Hernesto Paula 7 José Geraldo 8 Josué José 9 Navathe Figura 11: OPERAÇÃO UNION. Atenção: Repare que a operação UNION não trouxe o nome repetido do Professor ealuno Cleber Dutra, essa operação garante tuplas únicas. INTERSECTION Da mesma forma como foi apresentada a operação UNION, pode-se usar a de�nição matemática de interseção para de�nirmos a operação de interseção entre as relações. As observações feitas para a operação UNION, no que diz respeito ao domínio dos atributos e ao grau da relação, também devem ser seguidas para a operação de INTERSECTION. (FONSECA, 2016, p. 20) A ∩ B Essa operação é comutativa, ou seja, A ∩ B produz o mesmo resultado de B ∩ A. Exemplo: De acordo com Fonseca (2016), para a interseção entre os nomes de professores e alunos, temos: π NOME_ALUNO (DISCIPLINA_NOTA) U PROFESSOR MINUS Por �m, apresentamos a operação MINUS (SUBTRAÇÃO), que representa a diferença de conjunto. O resultado dessa operação, tomando-se nossas relações S e R apresentadas anteriormente, é uma relação que inclui todas as tuplas que estão em R, mas não estão em S. As observações feitas para a operação UNION e INTERSECTION, no que diz respeito ao domínio dos atributos e ao grau da relação, também devem ser seguidas para a operação MINUS. (FONSECA, 2016, p. 20) A forma geral da operação Diferença entre duas tabelas A e B é a seguinte: A - B Essa operação não é comutativa, ou seja, A - B produz um resultado diferente de B – A. Para obtermos o nome dos Professores que não são homônimos de alunos (FONSECA, 2016, p. 20), a expressão seria: PROFESSOR - π NOME_ALUNO (DISCIPLINA_NOTA) Resultando: NOME_PESSOA 1 Cleber Dutra Figura 12: OPERAÇÃO INTERSECTION. NOME_PESSOA 1 Elmasri 2 Cleber Dutra 3 Fagundes Teles 4 Navathe Atenção! Aqui existe uma videoaula, acesso pelo conteúdo online Atividade 1. De�na chave primária, chave estrangeira e qual a importância desses atributos em um modelo relacional. 2. Na tabela a seguir, qual seria uma possível chave primária? Diga os motivos que levaram à sua escolha. Nome da Coluna Tipo de Dados Permitir Nul... NOME NCHAR(50) SOBRENOME NCHAR(50) TELEFONE NCHAR(8) ENDEREÇO NCHAR(100) CPFPAI NCHAR(11) CPFMAE NCHAR(11) RG NCHAR(15) CPF NCHAR(10) 3. De�na: Tupla, Relação, Entidade, Atributo e Domínio. 4. Por que tuplas repetidas não são permitidas em uma relação? a) π Nome, Salário (σ salario >= 2000,00 (Empregado)) b) π Nome, Salário (σ salario = 2000,00 (Empregado)) c) π Nome, Salário (σ salario > 2000,00 (Empregado)) d) π RG ,Nome, Salário (σ salario > 2000,00 (Empregado) e) π RG ,Nome, Salário (σ salario <> 2000,00 (Empregado) f) π salario > 2000,00(σ Nome, Salário (Empregado)) 5. Considerando a tabela a seguir, marque todas as opções que atendam à consulta solicitada como correta ou errada e justi�que. Listar o nome e o salário dos empregados que ganham mais de R$ 2.000,00 . Tabela Empregado ID Nome Cargo Salário Id_Depto 101 Carlos Antunes Gerente de Vendas 5000,00 10 102 Pedro Leitão Vendedor 1100,00 10 103 Antônio Ventura Almoxarife 1200,00 20 104 Marco Aurélio Vendedor 1500,00 10 105 Carla da Silva Secretária 1000,00 30 106 Ivo Pereira Contador 2000,00 40 6. Considerando a tabela a seguir, marque todas as opções que atendam à consulta solicitada como correta ou errada e justi�que. Listar o Nome do Departamento e o Nome da Região onde ele está localizado. Tabela Departamento ID NOME ID_REGIÃO 10 VENDAS 1 20 OPERAÇÕES 1 30 ADMINISTRATIVO 2 40 FINANCEIRO 3 Tabela Departamento ID NOME 1 NORTE 2 CENTRO 3 SUL a) π Nome, Nome ( DEPARTAMENTO Id_Regiao = Id REGIAO) b) π Nome, Nome ( DEPARTAMENTO Id_Regiao = Id REGIAO) c) π Empregado.Nome, Departamento.Nome (DEPARTAMENTO REGIAO) d) π Empregado.Nome, Departamento.Nome (DEPARTAMENTO X REGIAO) e) π Empregado.Nome, Departamento.Nome(σ ID_REGIAO= REGIAO.ID (DEPARTAMENTO X REGIAO)) f) π Empregado.Nome, Departamento.Nome ( DEPARTAMENTO ID = ID_REGIAO REGIAO) Notas Referências DATE, C. J. Introdução a sistemas de banco de dados. 7. ed. Rio de Janeiro: Campus, 2000. ELMASRI, R.; NAVATHE, S. B. Sistemas de banco de dados. 7. ed. São Paulo: Pearson Addison Wesley, 2015. SILBERSCHATZ, A.; KORTH, H. F.; SUDARSHAN, S. Sistemas de banco de dados. 5. ed. Rio de Janeiro: Campus, 2006. Próxima aula Linguagem SQL. Explore mais Leia os textos: Modelagem Relacional. O Modelo Relacional de Dados - Parte I. O que é um modelo de banco de dados?. javascript:void(0); javascript:void(0); javascript:void(0); Implementação de banco de dados Aula 2: Linguagem SQL – DML e DDL Apresentação Anteriormente, estudamos o Modelo Relacional e a Álgebra Relacional, suas características e visualizamos suas principais operações. Além disso, realizamos vários exercícios de �xação. Nesta aula, seremos apresentados à linguagem SQL e vamos conhecer as partes que a compõem, estudaremos os comandos de criação de tabelas (DDL) e os comandos que permitem colocar, alterar ou eliminar linhas das tabelas (DML). Objetivos Criar e eliminar tabelas; Inserir, alterar e eliminar dados das tabelas. Fonte: everything possible / Shutterstock. Linguagem SQL O padrão mundial de acesso a banco de dados é a Linguagem Estruturada de Consulta (Structured Query Language) ou simplesmente SQL, na sigla em inglês. A linguagem SQL divide-se em partes, cada uma atendendo a uma necessidade especí�ca. Temos, então, a seguinte divisão: Linguagem de Descrição de Dados (Data De�nition Language – DDL) – tem como principais comandos Create, Alter e Drop, destinados, respectivamente, a criar, alterar e eliminar objetos de banco de dados, como usuários, tabelas, índices etc. Linguagem de Manipulação de Dados (Data Manipulation Language - DML) – tem como principais comandos Insert, Update, Delete e Select, destinados, respectivamente, a inserir linhas nas tabelas, alterar linhas, eliminar linhas e consultar os dados da tabela. Linguagem de Controle de Dados (Data Control Language – DCL) – tem como principais comandos Grant e Revoke, destinados a conceder e revogar privilégios de acesso respectivamente. Nosso foco de estudo será nos comandos de criação, alteração e eliminação de tabelas e nos comandos de DML (Insert, Update, Delete e Select). Tabelas Tabelas são os objetos básicos de armazenamento de dados no modelo relacional. Para criarmos uma tabela, devemos de�nir o seu nome, suas colunas, os tipos de dados das colunas e suas restrições. Nome, Coluna, Tipos de dados e Restrições Clique no botão acima. Nome O nome da tabela é normalmente de�nido durante a modelagem lógica, constituindo às vezes, alguma variação em relação ao nome da entidade. Por exemplo, entidade Aluno vira tabela Aluno ou Alunos. É importante conhecer as limitações do SGBD na hora de criar a tabela. A maior parte dos SGBD não dão suporte a caracteres em português no nome da tabela. Dessa forma, se temos uma entidade Aprovação, teremos que criar a tabela Aprovacao; se a entidade tem o nome Prova Aluno, teremos que substituir o espaço em banco por: _ Prova_Aluno. Colunas As colunas das tabelas se originaram dos atributos das entidades conforme vimos na modelagem lógica. Da mesma forma que o nome da tabela, temos que respeitar as limitações do SGBD: não usar espaço em branco, caracteres em português e nos preocupar também com a quantidade máxima de caracteres que o nome da coluna ou tabela pode ter. Muitas vezes, por causa dessa limitação, teremos que abreviar o nome. Por exemplo: o atributo Matrícula do Aluno poderá virar a coluna Mat_Aluno na tabela. Tipos de dados As colunas possuem um tipo de dado que podem armazenar de forma similar ao conceito de tipo utilizado nas variáveis criadas em programas. Os SGBD possuem uma variedade muito grande de tipos. Cada SGBD tem o seu conjunto especí�co, que é, muitas vezes, incompatível com outros SGBD. Nós faremos uso do PostGreSql como SGBD para a realização de exercícios. Nos comandos de criação de tabelas, os tipos básicos são: Atenção! Aqui existe uma videoaula, acesso pelo conteúdo online Tipos de Dados Descrição VARCHAR(tam) Caracter de tamanho variável, podendo atingir de 1 até 8.000 bytes, sendo limitadoa tam. CHAR(tam) Caracter de tamanho fixo, podendo tam ser especificado no máximo de 8.000 caracteres. TEXT Caracter de tamanho variável sem limite. INTEGER Número inteiro de 4 bytes podendo ir de -2147483648 a +2147483647. Esses tipos mostrados na tabela acima são uma pequena parte dos tipos existentes no PostgreSql, mas serão su�cientes para nossos exercícios. NOTA: Abordaremos também as particularidades do SQL do Oracle e do SQL Server no que diferem do PostgreSql. Restrições As restrições, normalmente chamadas constraint visam estabelecer regras para orientar o SGBD na forma de manter a integridade do banco. Aqui vamos tratar das seguintes constraint: Comando de criação de tabela O comando de Criação de Tabela é o Create Table. Vejamos sua sintaxe: CREATE TABLE nome_da_tabela( (nome_col1 tipo_col1 [restri_col1] [, nome_col2 tipo_col2 [restri_col2] [, nome_coln tipo_coln [restri_coln] [, restri_tab1 [restri_tab2]); Onde: NUMERIC(I,d) Numérico com precisão. Em i, estabelecemos quantos dígitos tem o número e em d quantos há na parte decimal. Por exemplo, para especificar um número com formato 99999.99. Tipo seria NUMERIC(7,2) DATE Data entre 01 de janeiro de 4713 AC até 31 de dezembro de 32767 DC. TIMESTAMP Armazenamento da data e da hora juntos BIT Tipo booleano armazena 0 (falso) ou 1 (verdadeiro) Restrição (Constraint) Descrição NOT NULL (Obrigatório) Especifica se a coluna é obrigatória para todas as linhas da tabela, não podendo conter nenhum valor NULO. UNIQUE (Chave alternada) A coluna ou combinação de diversas colunas tem que ser única para todas as linhas da tabela, não permitindo repetições. PRIMARY KEY (Chave primária) É a chave primária de identificação unívoca da tabela. Pode ser uma ou uma combinação de colunas. FOREIGN KEY (Chave estrangeira) É uma coluna que garante a integridade de uma relação entre duas tabelas, sendo referenciada por uma chave primária da outra tabela. Vamos ver um exemplo de criação. Considere a tabela Departamento: O comando que a criaria no PostGreSql seria: CREATE TABLE DEPARTAMENTO (ID NUMERIC(7) PRIMARY KEY, NOME VARCHAR(40) NOT NULL) Observe no comando a constraint PRIMARY KEY, de�nindo a coluna ID como chave primária e a constraint NOT NULL, estabelecendo que NOME é de preenchimento obrigatório. Sugiro que você crie a tabela no PostGreSql conforme as orientações do vídeo da aula. Para vermos a tabela criada, a forma mais fácil é consultarmos o conteúdo de toda a tabela. Para isso, podemos dar o comando: SELECT * FROM DEPARTAMENTO Esse comando é básico e lista todo o conteúdo da tabela. Note que a tabela está vazia, mas foi criada no banco com duas colunas. No SQL Server, o comando é o mesmo. Palavra Chave Descrição nome_da_tabela Nome que será atribuído à tabela. Nome_coln Nome atribuído à coluna n. Tipo_coln Tipo de dado atribuído à coluna n. restri_coln Define uma restrição de integridade automática à qual os campos da coluna devem obedecer. restri_tabn Define uma restrição de integridade automática à qual toda a tabela deve obedecer. Tabela Coluna Tipo Tamanho Observação Departamento ID numérico 7 Chave Primária NOME caracter 40 obrigatório O comando funciona também no Oracle: Mais Constraints Até agora utilizamos apenas as constraints NOT NULL e PRIMARY KEY. Vejamos agora as constraints UNIQUE e FOREIGN KEY. Campos únicos UNIQUE Ao estabelecermos a constraint UNIQUE para uma coluna, determinamos que ela não pode ter valor repetido. Entretanto, ela não obriga a coluna a ter valor ou não a torna de preenchimento obrigatório e, como NULO não é valor, uma coluna UNIQUE pode possuir várias linhas nulas (sem valor). Exemplo CPF char(11) UNIQUE, Reforçando a Integridade Referencial com Chave Estrangeira (Foreign Key) Foreign Key Os relacionamentos entre tabelas são criados através da geração de chaves estrangeiras (foreign key – FK) nas tabelas FILHO que referenciam colunas chaves nas tabelas PAI. Para estabelecer essa restrição, acrescentamos REFERENCES na de�nição da coluna, como exemplo: ID_DEPTO numeric (7) References Departamento(ID), Onde: Id_depto é o nome da coluna; Numeric r(7) o tipo da coluna; References identi�ca a restrição de chave estrangeira; Departamento é o nome da tabela para onde aponta a chave estrangeira; (ID) é coluna da tabela departamento apontada pela chave estrangeira. Vejamos um exemplo de criação de tabela com essas restrições. CREATE TABLE EMPREGADO ( ID NUMERIC(7) PRIMARY KEY, ULT_NOME VARCHAR(20) NOT NULL, PRIM_NOME VARCHAR(20) NOT NULL, CARGO VARCHAR(30), SALARIO NUMERIC(7,2), DT_ADMISSAO DATE, CPF CHAR(11) UNIQUE, ID_DEPTO NUMERIC(7) REFERENCES DEPARTAMENTO(ID)) Observe o comando. Através de sua análise, podemos observar que: - a coluna ID é sua chave primária; - As colunas ULT_NOME e PRIM_NOME são de preenchimento obrigatório; - A coluna CPF é única; - A coluna ID_DEPTO é uma chave estrangeira para a tabela departamento. Vamos criá-la no PostGgreSql. Observe a mensagem de sucesso. Atenção Importante Se uma tabela possui uma chave estrangeira para outra, ela tem que ser criada depois da tabela referenciada, senão ocorrerá um erro. Vamos agora criar uma terceira tabela: CREATE TABLE CLIENTE ( ID NUMERIC(7) PRIMARY KEY, NOME VARCHAR(40) NOT NULL, VENDEDOR NUMERIC(7)) Acrescentando colunas em tabelas Podemos acrescentar colunas em tabelas já criadas com o comando Alter Table. Sua sintaxe é: alter table <nome_tabela> add <nome_coluna> <tipo da coluna> <constraint > Em que: <nome_tabela> é o nome da tabela a qual será acrescida à coluna. <nome coluna> é o nome da coluna que será acrescida. <tipo da coluna> é o tipo de dado da coluna a ser acrescida. <constraint> é a restrição, se for o caso, da coluna a ser acrescida. Exemplo Na criação da tabela Departamento. Vimos que duas restrições (constraints) são estabelecidas como: uma de obrigatoriedade (NOT NULL) e uma Chave Primária. Vamos assumir, entretanto que nossa tabela, com esse último comando de criação, não foi completamente estabelecida. Está faltando a coluna descrição. Para inseri-la, podemos dar o comando: ALTER TABLE DEPARTAMENTO ADD descricao VARCHAR(30) NOT NULL; Vamos executar o comando. Para isso, digite-o no PostgreSql. Eliminando colunas de tabelas É possível eliminar colunas de tabelas, inclusive aquelas referenciadas por constraints e índices, e até mesmo chaves primárias, únicas e estrangeiras. É verdade que cuidados quanto à aplicação devem ser tomados por parte dos desenvolvedores e DBA, porém, o SGBD implementa essa funcionalidade. Ao eliminarmos uma coluna, suas restrições, caso existam, também são removidas do dicionário de dados. Sintaxe: alter table <nome_tabela> drop column <nome_coluna>; Em que: <nome_tabela> é o nome da tabela da qual será eliminada a coluna. <nome coluna> é o nome da coluna que será eliminada. Por exemplo: se desejarmos eliminar a coluna descrição da tabela Departamento, daremos o seguinte comando: ALTER TABLE DEPARTAMENTO DROP COLUMN DESCRICAO Vamos executar o comando. Para isso, digite-o no PostgreSql. Incluindo uma Foreign Key numa tabela existente Também podemos incluir a constraint de Foreign Key após a criação da tabela. Para tal, basta especi�car a adição da constraint no comando ALTER TABLE. A tabela Cliente foi criada, mas a coluna Vendedor deveria ser uma chave estrangeira para a tabela Empregado na coluna ID. Podemos dar o seguinte comando de ALTER TABLE: ALTER TABLE CLIENTE ADD FOREIGN KEY (VENDEDOR) REFERENCES EMPREGADO(ID) Em que: CLIENTE – é a tabela a ser alterada ADD FOREIGN KEY – é a restrição a ser acrescida. (VENDEDOR) é a coluna que receberá a constraint REFERENCES EMPREGADO(ID) indica a tabela e a coluna referenciadas pela chave estrangeira. Veja o comando no PostGreSql. Constraint de colunas e tabelas As constraints podem ser de�nidas junto com a coluna ou separadamente, no �nal do comando create table ou com o comando alter table. As constraints not null só podemser de�nidas junto com a de�nição da coluna. As constraints de tabela são utilizadas principalmente para criar constraints compostas, onde duas ou mais colunas fazem parte da constraint. Como, por exemplo, chaves primárias compostas. Exemplo: A tabela Turmas possui uma chave primary composta pelas colunas CODIGO_TURMA e CODIGO_CURSO. O comando para sua criação é: CREATE TABLE TURMAS (CODIGO_TURMA NUMBER(6), CODIGO_CURSO NUMBER(3), CODIGO_FUNCIONARIO NUMBER(6), DATA_INICIO DATE, DATA_FIM DATE , SALA NUMBER(2), PRIMARY KEY (CODIGO_TURMA, CODIGO_CURSO) ); E se desejarmos apagar uma tabela? Para isso temos o Comando Drop, cuja sintaxe é: DROP TABLE <NOME DA TABELA> Para eliminarmos a tabela TURMAS, daremos o comando: DROP TABLE TURMAS; Execute o comando no PostGreSql. Observe a mensagem de sucesso. Agora, se dermos o comando de Select na tabela, teremos uma mensagem de erro. Veja o que acontece ao eliminar a tabela Departamento. Não será possível porque a tabela Empregado possui uma Chave Estrangeira para Departamento, e você não poderá eliminar Departamento enquanto essa FK existir. Atenção Os comandos de CREATE TABLE, ALTER TABLE e DROP TABLE vistos aqui funcionam de forma exatamente igual no PostGreSql, no Oracle e no Sql Server. Atenção! Aqui existe uma videoaula, acesso pelo conteúdo online Manipulando dados Agora que já você já aprendeu a criar, alterar e eliminar tabelas, vai estudar os comandos de manipulação de dados que permitirão que você faça a inclusão, alteração ou eliminação das linhas das tabelas. Esses comandos são: Insert – para incluir linhas; Delete – para eliminar linhas; Update – para alterar linhas. Inserindo Linhas Clique no botão acima. Vamos inserir uma linha na tabela de departamento. INSERT INTO DEPARTAMENTO (ID, NOME) VALUES (100, 'Financeiro'); A relação entre a lista de colunas (ID, NOME) e a lista de valores (100, 'VENDAS) é posicional, portanto: A coluna ID receberá o valor 100; Nome receberá Vendas. Execute o comando no PostgreSql e observe a mensagem de linha inserida. Uma outra forma de dar o comando INSERT é sem referenciar as colunas. Nesse caso, a lista de valores deve estar na ordem das colunas da tabela. INSERT INTO DEPARTAMENTO VALUES (200, 'Compras') Desta vez, não foram especi�cadas as colunas que receberão os valores. Portanto, o comando utilizará todas as colunas da tabela na ordem em que foram criadas. A nossa tabela possui duas linhas. lnserindo com valores nulos Caso alguma coluna deva �car com NULO em uma inserção, basta omitir o nome da mesma na lista de colunas. Vejamos um exemplo em que isso ocorre: Inserindo Linhas O comando INSERT insere linhas em uma tabela. Sua sintaxe básica é: insert into .<nome_tabela> (coluna1, coluna2, ..., colunan) values (valor1, valor2, ..., valorn); Em que: Cláusula Descrição nome_tabela O nome da tabela a ser atualizada Coluna n A coluna que queremos inserir Valor n É o novo valor associado à coluna a ser inserida No exemplo acima, foram omitidas as colunas CARGO, SALARIO, DT_ADMISSAO e CPF, que �caram nulas. No nosso exemplo, se não fosse especi�cado um valor para a coluna id, a inserção resultaria em erro. O mesmo erro ocorreria se não tivéssemos valores para as colunas PRIM_NOME e ULT_NOME, já que são de preenchimento obrigatório (constraint not null). Aproveitando ainda esse exemplo de inserção, note que inserimos o PRIM_NOME e depois o ULT_NOME, apesar de na tabela ULT_NOME vir antes de PRIM_NOME. Isso foi possível devido à inserção ser realizada na ordem da lista de colunas. Uma última observação se refere à Chave Estrangeira ID_DEPTO, cujo valor inserido OBRIGATORIAMENTE tem que existir na tabela DEPARTAMENTO. Outra forma de inserirmos com valores nulos em uma coluna é utilizando a palavra reservada null. Vejamos um exemplo: Como omitimos a lista de colunas, temos que ter um valor para cada coluna. Então estamos inserindo 20 no ID, Fonseca no Prim_Nome, Antonio no Ult_Nome no NOME , nulo nas colunas CARGO, SALARIO, DT_ADMISSAO e CPF e 200 no Id_depto. Execute o comando. Observe a mensagem de linha inserida. Como omitimos a lista de colunas, temos que ter um valor para cada coluna, na ordem das colunas da tabela. Você também pode executar vários comandos de inserção juntos; Basta separá-los por: INSERT INTO EMPREGADO VALUES (2, 'Neves', 'Lauro', 'Diretor de Compras',19500, '07/03/2009','23456789012',200); INSERT INTO EMPREGADO VALUES (3, 'Nogueira', 'Mário','Diretor de Vendas', 18000, '07/04/2010','34567890123',100); INSERT INTO EMPREGADO VALUES (4, 'Queiroz', 'Mark','Gerente de Compras',8000, '07/11/2010','12345432123',200); INSERT INTO EMPREGADO VALUES( 5, 'Rodrigues', 'Alberto', 'Vendedor',4000, '10/1/2008', '87965432123', 100); INSERT INTO EMPREGADO VALUES( 6, 'Ugarte', 'Marlene', 'Vendedor', 3500,'23/11/2009', '87654345678',100); Atenção Observe que NULO não é equivalente a 0 (zero), espaço ou qualquer outro valor. NULO é, justamente, a ausência de qualquer valor na coluna. Um cuidado que devemos ter é que só é possível fazer isso em colunas que não possuam a constraint NOT NULL. Outro detalhe é que nenhuma coluna de�nida como chave primária poderá conter NULO. Como resultado, temos sete linhas na tabela. Observe que apesar de termos inserido a data no formato DD/MM/AAAA, o PostgreSql sempre exibe por padrão no formato AAAA/MM/DD. Você pode dar o comando de inserção de uma forma ou de outra, mas, para evitar problemas, use sempre AAAA/MM/DD. É mais seguro. Atualizando linhas O comando UPDATE permite que atualizemos dados já existentes nas tabelas. Sua sintaxe é: UPDATE nome_tabela SET coluna = expressão WHERE condição Onde: Vejamos um exemplo: Vamos aumentar o salário de todos empregados em R$ 1000. UPDATE EMPREGADO SET SALARIO = SALARIO + 1000; Como resultado teríamos: Repare que: 1. Como o comando foi dado sem cláusula where, ele afetou todas as linhas da tabela; Cláusula Descrição Nome_tabela O nome da tabela a ser atualizada. coluna A coluna que queremos alterar. expressão O novo valor associado à coluna a ser alterada. condição A condição que deverá satisfazer as colunas que serão alteradas. 2. Os empregados de id 10 e 20 continuaram com salário nulo. Isso ocorre porque Nulo não é valor, e qualquer valor operado com Nulo tem como resultado Nulo. Mas vamos agora atribuir um salário a esses dois empregados. Podemos então dar um comando de UPDATE com WHERE para alterar apenas essas duas linhas. UPDATE EMPREGADO SET SALARIO = 3000 WHERE ID = 10 OR ID = 20; Vejamos como �cou: Repare que os empregados 10 e 20 possuem salário. Eliminando linhas O comando DELETE é utilizado para excluir linhas em uma tabela e tem como sintaxe: DELETE FROM nome_tabela WHERE condição Em que: Vamos apagar da Tabela Empregado o funcionário de ID 10. DELETE FROM EMPREGADO WHERE ID = 10; Dessa forma, o resultado seria não ter mais a linha do empregado 10 na tabela. Cláusula Descrição Nome_tabela O nome da tabela a ser deletada condição A condição que deverá satisfazer as colunas que serão deletadas Atenção 1. Se você desse o comando de delete na Tabela Empregado sem a Cláusula Where, TODAS AS LINHAS da tabela seriam apagadas. Vamos, agora, reconstituir nosso ambiente, eliminando as tabelas Departamento e Empregado na ordem correta: DROP TABLE CLIENTE; DROP TABLE EMPREGADO; DROP TABLE DEPARTAMENTO. Você deve dropar as tabelas nessa ordem devido às FK. 2. Você não consegue apagar linhas na tabela Departamento enquanto existirem linhas em Empregado que as referenciem na Chave Estrangeira. Scripts Um SCRIPT nada mais é que um conjunto de comandos SQL salvos em um arquivo com a extensão .sql, que é carregado no SGBD. Em seguida, é lido e tem seus comandos executados como um todo. No PGADMIN, podemos carregar um Script clicando em Abrir Arquivo. Navegando até o local do arquivo e o selecionando. Feito isso, o conteúdo do Script é carregado e basta mandar executá-lo. Atividade 1. A partirdo Modelo Lógico a seguir, crie as tabelas no PostGreSql. As tabelas são criadas e os dados inseridos. Atenção! Aqui existe uma videoaula, acesso pelo conteúdo online 2. Insira os dados nas tabelas criadas na atividade 1 de forma que as tabelas �quem conforme as �guras a seguir: 3. Baixe o Script Escola disponível aqui <./galeria/aula2/apoio/Aula_2_ESCOLA.sql> e o execute no PostGreSql. Notas Título modal 1 Lorem Ipsum é simplesmente uma simulação de texto da indústria tipográ�ca e de impressos. Lorem Ipsum é simplesmente uma simulação de texto da indústria tipográ�ca e de impressos. Lorem Ipsum é simplesmente uma simulação de texto da indústria tipográ�ca e de impressos. Título modal 1 Lorem Ipsum é simplesmente uma simulação de texto da indústria tipográ�ca e de impressos. Lorem Ipsum é simplesmente uma simulação de texto da indústria tipográ�ca e de impressos. Lorem Ipsum é simplesmente uma simulação de texto da indústria tipográ�ca e de impressos. Referências DATE, C. J. Introdução a sistemas de banco de dados. 7. ed. Rio de Janeiro: Campus, 2000. ELMASRI, R.; NAVATHE, S. B. Sistemas de banco de dados. 7. ed. São Paulo: Pearson Addison Wesley, 2015. SILBERSCHATZ, A.; KORTH, H. F.; SUDARSHAN, S. Sistemas de banco de dados. 5. ed. Rio de Janeiro: Campus, 2006. Próxima aula Estudo do comando Select; Cláusulas iniciais (Select e From); Cláusula Distinct. http://estacio.webaula.com.br/cursos/GRA302/galeria/aula2/apoio/Aula_2_ESCOLA.sql Explore mais PostgreSQL javascript:void(0); Implementação de Banco de Dados Aula 3: Linguagem SQL – Select – Parte 1 Apresentação Anteriormente, você aprendeu a criar tabelas e a inserir, alterar e deletar linhas. Chegou, inclusive, a dar o comando Select visando recuperar dados sem, entretanto, entender as suas nuances. Nesta aula, você começará o estudo desse importante comando, sem dúvida o mais utilizado da linguagem SQL. Objetivo Analisar o comando de Select; Consultar dados em tabelas. Banco de Dados de exemplo Um banco de dados denominado ”Empresa” será utilizado para os exemplos desta aula e das próximas. O Banco da empresa possui o seguinte Modelo Lógico: As tabelas possuem os seguintes dados: REGIÃO DEPARTAMENTO EMPREGADO CLIENTE Agora vamos ver como criá-lo. Criação de tabelas Clique no botão acima. Comandos para criação de tabelas: CREATE TABLE REGIAO (ID_REGIAO NUMERIC(7) PRIMARY KEY, NOME VARCHAR(40)); CREATE TABLE DEPARTAMENTO ( ID NUMERIC(7) PRIMARY KEY, NOME VARCHAR(40) NOT NULL, ID_REGIAO NUMERIC(7 )REFERENCES REGIAO(ID_REGIAO)); CREATE TABLE EMPREGADO ( ID NUMERIC(7) PRIMARY KEY, ULT_NOME VARCHAR(20) NOT NULL, PRIM_NOME VARCHAR(20) NOT NULL, CARGO VARCHAR(30), SALARIO NUMERIC(7,2), DT_ADMISSAO DATE, CPF CHAR(11) UNIQUE, ID_DEPTO NUMERIC(7) REFERENCES DEPARTAMENTO(ID), ID_GERENTE NUMERIC(7) REFERENCES EMPREGADO(ID)); CREATE TABLE CLIENTE ( ID NUMERIC(7) PRIMARY KEY NOME VARCHAR(40) NOT NULL, VENDEDOR NUMERIC(7) REFERENCES EMPREGADO(ID)); INSERINDO LINHAS NA TABELAS INSERT INTO REGIAO VALUES (1, 'Norte'); INSERT INTO REGIAO VALUES (2, 'Sul'); INSERT INTO DEPARTAMENTO VALUES (10, 'Administrativo',1); INSERT INTO DEPARTAMENTO VALUES (20, 'Vendas',1); INSERT INTO DEPARTAMENTO VALUES (30, 'Compras',2); INSERT INTO EMPREGADO VALUES (1, 'Velasques', 'Carmen', 'Presidente',29500, '05/05/2009','34567890125',10, null); INSERT INTO EMPREGADO VALUES (2, 'Neves', 'Lauro', 'Diretor de Compras',19500, '03/03/2009','23456789012',30,1); INSERT INTO EMPREGADO VALUES (3, 'Nogueira', 'Ernane','Diretor de Vendas', 18000, '07/04/2010','34567890123',20,1); INSERT INTO EMPREGADO VALUES (4, 'Queiroz', 'Mark','Gerente de Compras',8000, '11/11/2010','12345432123',30,2); INSERT INTO EMPREGADO VALUES (5, 'Rodrigues', 'Alberto', 'Vendedor',4000, '10/10/2008', '87965432123', 20,3); INSERT INTO EMPREGADO VALUES (6, 'Ugarte', 'Marlene', 'Vendedor', 3500,'03/03/2009', '87654345678',20,3); INSERT INTO CLIENTE VALUES (110, 'Ponto Quente',5); INSERT INTO CLIENTE VALUES (120, 'Casa Supimpa',6); INSERT INTO CLIENTE VALUES (130, 'Coisas e Tralhas',5); INSERT INTO CLIENTE VALUES (140, 'Casa Desconto',null); Se preferir, pode obter os comandos aqui. Comentário Os comandos listados acima mostram como o script disponível funciona normalmente no postgreSql e no SqlServer. No ORACLE, deve ser comandado COMMIT após o último insert. Tendo esse Banco em mente, é altamente recomendável que você execute os comandos de exemplo no PostGreSql. Foi escolhido como base o PostgreSql, por ser um SGBD mais leve e fácil de instalar, porém, se você puder usar o SqlServer ou o Oracle quando houver diferença entre os SGBD’s, será avisado. javascript:void(0); Consultando dados de uma tabela O comando SQL que permite recuperar dados de uma ou mais tabelas é o SELECT. Esse comando nos permite escolher as colunas que retornarão, bem como �ltrá-las da tabela. O comando de Select é uma implementação prática da teoria dos conjuntos, mais especi�camente da Álgebra Relacional. Dessa forma, um único Select pode retornar zero ou várias linhas, de acordo com as restrições colocadas no comando. Os componentes básicos do comando são: SELECT e FROM Clique no botão acima. Cláusula SELECT Lista as colunas que serão recuperadas; Se utilizarmos o artifício do * (asterisco) na cláusula SELECT, estaremos de�nindo que todas as colunas serão recuperadas. Cláusula FROM De�ne a tabela que será recuperada. Veja a sintaxe abaixo: SELECT nome-col1, nome_col2, nome coln FROM nome_da_ tabela; OU SELECT * FROM nome_da_tabela ; Em que: Palavra-Chave Descrição nome_da_tabela Nome da tabela que contém os dados a serem recuperados. nome_coln Nome de uma coluna a ser recuperada. * (asterisco) Recupera todas as colunas da tabela. Retornando uma tabela inteira Acesse o SGBD e digite o seguinte comando: SELECT * FROM EMPREGADO. No comando acima, selecionamos todas as colunas e todas as linhas da tabela EMPREGADO. Teremos uma resposta semelhante à �gura 1. Figura 1 – Retorno do Comando Observe que não há nenhuma ordem ou seleção de linhas ou colunas. Retornando colunas especí�cas Na Álgebra Relacional, vimos que existe a operação de projeção que permite retornar apenas algumas colunas da tabela, mas todas as linhas. O mesmo pode ser obtido em SQL. Para isso basta que se liste as colunas desejadas na cláusula SELECT, separando-as por virgulas. Veja um exemplo: Acesse o SGBD e digite o seguinte comando: SELECT ID, PRIM_NOME, ULT_NOME FROM EMPREGADO. No comando acima, são selecionadas apenas três colunas e todas as linhas da tabela EMPREGADO, e o seu retorno pode ser observado na Fig2. Figura 2 – Retorno do Comando Comentário Nesse segundo caso, não são exibidas as colunas CARGO, SALARIO, DT_ADMISSAO, CPF,ID_DEPTO e ID_GERENTE. No primeiro comando analisado, um asterisco substitui a lista de colunas desejadas, indicando que todas as colunas devem ser informadas. Exemplos: Como seria o comando para exibir todo o conteúdo da tabela departamento, cujo retorno é exibido na �g3? Figura 3 – Retorno do Comando Dica: tente dar o comando no SGBD antes de ver a SOLUÇÃO. CONTEÚDO SOLUÇÃO Como seria o comando para exibir todo o NOME e o ID de todos os clientes, cujo retorno é exibido na �g4? Figura 4 – Retorno do Comando Dica: tente dar o comando no SGBD antes de ver a SOLUÇÃO. CONTEÚDO SOLUÇÃO Dica Note que: Quando você utiliza *, as colunas retornam na ordem em que foram criadas na tabela; Quando você lista as colunas no SELECT, elas retornam na ordem em que as listou; As colunas no SELECT devem estar separadas por vírgula; Não deve existir vírgula antes da cláusula FROM. SELECT Clique no botão acima. Incrementando a consulta Para efetuar consultas mais complexas e derivar dados a partir das informações contidas nas tabelas, você pode construir expressões na cláusula SELECT. As expressões podem ser aritméticas ou alfanuméricas, fazendo concatenações por exemplo. Uma expressãoaritmética pode conter os seguintes operadores: * multiplicação; / divisão; + adição; - subtração. Para concatenarmos duas colunas, utilizamos o operador || OU + dependendo do SGBD. Escrevendo expressões aritméticas em comando Select Em uma expressão, podemos especi�car não apenas uma coluna, mas um dado derivado de uma ou mais colunas. O comando pode ser visto na �gura 6: Esse comando funciona da mesma forma no PostGreSql, SqlServer e Oracle. Exemplo A �gura 5 mostra a tabela Empregado. Como �caria então o comando que listaria o ID, o Ult_NOME, o Salário e o salário anual (consideramos que o salário anual é o salário mensal multiplicado por doze) de todos os empregados? Figura 5 – TABELA EMPREGADO Figura 6 – COMANDO E RETORNO Escrevendo expressões de concatenação No PostGreSql, o operador de concatenação é o ||. Se desejássemos retornar o PRIM_NOME do empregado com o ULT_NOME, o comando seria: Figura 11 – NOME DAS COLUNAS NAS EXPRESSÕES SELECT PRIM_NOME || ULT_NOME FROM EMPREGADO O retorno seria o exibido na Fig7. Figura 7 – RETORNO DO COMANDO Analise a �gura 7. Notou que os nomes estão colados? Isso decorre do fato de que, depois do PRIM_NOME ou antes do ULT_NOME, não existe espaço em branco armazenado na coluna. Como resolver isso? Basta concatenar as colunas com um espaço em branco entre elas, conforme mostra a Figura 8. Figura 8 – COMANDO E RETORNO Mas você pode se perguntar: como �ca isso no Oracle e no Sql Server? No Oracle, o operador é o mesmo (Figura 9). Figura 9 – COMANDO E RETORNO Já no SqlServer, o operador de concatenação é o + (Figura 10). Figura 10 – COMANDO E RETORNO Criando Alias Quando são utilizadas expressões, o cabeçalho da coluna, normalmente, �ca sem signi�cado. Dependendo do SGBD, ele pode ser a própria expressão, como acontece no ORACLE, pode ser (No column name), como acontece no SQLSERVER, ou pode ser? column? Como acontece no PostgreSQL (Figura 11). Seja como for, seria mais interessante se fosse possível nomear as colunas de forma a manterem o seu signi�cado. Para isso, existem os alias de coluna. PostGreSql Para você criar um alias após a coluna, você deve colocar ‘AS’ e em seguida a palavra - sem espaços em branco e sem Figura 12 - Retorno dos SGBD caracteres em português - que usará para ser o cabeçalho da coluna. Para chamar a concatenação do PRIM_NOME com o ULT_NOME como NOME_COMPLETO, você deve comandar: SELECT PRIM_NOME ||' '|| ULT_NOME AS NOME_completo FROM EMPREGADO PostGreSql Atenção 1. Repare que no comando o alias está com NOME em maiúsculo e completo em minúsculo. Analise agora o retorno e note que no PostGreSql o alias �ca todo em minúsculo, no Oracle todo em maiúsculo e no SqlServer da forma que você digitou o alias. 2. O AS nos 3 SGBD é opcional. Se você escrever o comando sem o AS, ele funciona. Teste para ver. Colocando espaço em branco no Alias Clique no botão acima. Se você desejar utilizar espaço em branco no Alias, no Oracle e no PostgreSql, então deverá colocar o alias entre aspas duplas (“ “). O comando seria: SELECT PRIM_NOME ||' '|| ULT_NOME "NOME completo" FROM EMPREGADO Figura 12 - Retorno dos SGBD Note que, agora, para os dois o nome da coluna, além de ter o espaço em branco está escrito da forma exata que digitamos o alias. E no SqlServer? Neste SGBD, além do alias também ser opcional, ele pode estar entre aspas duplas(“ “), apóstrofes (‘ ‘) ou colchetes ([ ]). Veja a �gura 13. PostGreSql Figura 13 - ALIAS SQLSERVER Sugestão: tente dar o comando de ALIAS no PòstGreSql ou no ORACLE utilizando apóstrofes ou colchetes e veja o que acontece. Exemplo 1 Dica Tente dar o comando no SGBD antes de ver a SOLUÇÃO. CONTEÚDO SOLUÇÃO Como seria o comando para mostrar os últimos nomes dos empregados com o cabeçalho Sobrenome, cujo retorno é exibido na �gura 14? Figura 14 – Retorno do Comando. Exemplo 2 Dica Como seria o comando para mostrar a concatenação do ID do cliente com o seu Nome com o cabeçalho ‘Dados dos Clientes’? O Id deverá vir separado do nome por um hífen ( - ),cujo retorno é exibido na �g.15? Tente dar o comando no SGBD antes de ver a SOLUÇÃO. CONTEÚDO SOLUÇÃO Figura 15 – Retorno do Comando Utilizando SELECT sem FROM A cláusula SELECT, além de permitir a realização da projeção das colunas da tabela, pode ser utilizada para exibir resultados de operações aritméticas, retorno de funções ou textos. Vejamos um exemplo no PostGreSql: Atenção Note que: 1. O comando primeiro exibe um texto (ALO), em seguida o resultado de uma operação aritmética (9+5) e �nalmente retorna o valor da data da data/hora do sistema (FUNÇÃO NOW()); 2. Não existe cláusula FROM, pois desejamos retornar apenas uma linha com os valores. O que aconteceria se fosse acrescentada uma cláusula FROM com um nome de tabela? Note na �gura acima que retornaram 4 linhas todas iguais. Isso acontece porque tanto as expressões como a função NOW() são de linha, ou seja, retornam uma linha para cada linha da tabela da cláusula FROM. Como a tabela CLIENTE possui 4 linhas, o retorno tem essa quantidade de linhas. Por isso é omitida a cláusula FROM, já que desejamos apenas uma linha de retorno. E o SqlServer, como �ca? Muito Similar. A única diferença é que a função que retorna a data/hora se chama GETDATE(). Como no PostgreSql, se tiver FROM, retornará uma linha para cada linha da tabela. Faltou o Oracle. A função de data do Oracle é SYSDATE. Veja na �gura abaixo o retorno do comando Note que deu o erro, pois a cláusula From não foi encontrada. Por que isso acontece? Ao contrário do PostGreSql e do SqlServer, no Oracle a cláusula FROM é obrigatória. Como fazemos então para conseguir listar os valores? Colocamos a Cláusula FROM com a tabela DUAL, conforme a �gura abaixo. Atenção Observações: 1. Sysdate também pode retornar a hora mas temos que fazer algumas con�gurações que ultrapassam o nosso escopo aqui. 2. Como vimos antes no SqlServer e no PostgreSql também podemos colocar a cláusula FROM com um nome de tabela, só que retornaram uma linha para cada linha da tabela, mas como a cláusula FROM não é obrigatória não é um problema. 3. No Oracle se colocarmos uma tabela com 4 linhas na FROM retornaram 4 linhas, como a cláusula FROM é obrigatório isto poderia gerar um problema. Para eliminar esta di�culdade existe uma tabela de sistema chamada DUAl que possui uma única linha e que deverá ser colocada na cláusula FROM sempre que se desejar retornar uma única linha com expressões ou funções de linha. Se por curiosidade você quiser saber o conteúdo de DUAL, basta comandar: Você verá que a tabela possui uma única coluna chamada DUMMY e uma Linha com o valor X. Atenção Importante: DUAL é uma tabela de sistema em que nenhum usuário pode DROPAR ou INSERIR linhas, tampouco ALTERAR ou ELIMINAR sua única linha. Atividade 1. A partir do Modelo Lógico abaixo, crie as tabelas no PostGreSql em um DataBase chamado “Seguradora”. 2. Insira os dados nas tabelas criadas na atividade 1 de forma que as tabelas �quem conforme as �guras abaixo. 3. Utilizando o banco de dados da Seguradora, emita os comandos abaixo escolhendo as colunas e escrevendo as expressões. a. SELECIONE TODOS OS DADOS DE VEÍCULOS; b. SELECIONE A PLACA, A COR, O ANO DE FABRICAÇÃO E O ANO DO MODELO DE TODOS OS VEÍCULOS; c. EMITA UM COMANDO QUE CONCATENE O NOME DO PROPRIETÁRIO E O SEU CÓDIGO, SEPRANDO-OS COM UM ' - ' E CHAME DA COLUNA DE NOME_COD; d. EMITA UM COMANDO QUE RETORNE A PLACA DO VEÍCULO E O SEU VALOR SEGURADO MAJORADO EM 10% E CHAME A COLUNA DE SEGURO MAJORADO; e. EMITA UM COMANDO QUE RETORNE A PLACA DO VEÍCULO, O CÓDIGO DO PROPRIETÁRIO, O CÓDIGO DO MODELO E A SOMA DOS DOIS CÓDIGOS. Notas Ethernet 1 Ethernet é uma arquitetura de rede local que entre suas especi�cações possui o cabeamento UTP. Backoff 2 Backoff refere-se ao tempo em que um dispositivo de transmissão de dados aguarda para realizar uma nova transmissão após a ocorrência de um problema na primeiratentativa. Feeds de notícias 3 O que é um feed de notícias? É um tipo de ferramenta da internet que faz com que não seja preciso correr atrás de determinado site para descobrir quando ele vai atualizar um conteúdo especí�co. Eles vão até os endereços que você mais gosta e trazem até a sua área de trabalho aquele texto, vídeo ou música tão esperados. Servem para manter você conectado com pessoas, locais e assuntos importantes. As publicações que aparecem primeiro são in�uenciadas por suas conexões e atividades no Facebook. São atualizados a partir daquilo que a internet entende ser de seu maior interesse. Fonte: https://www.tecmundo.com.br/rss/252-o-que-sao-feeds-.htm Referências DATE, C. J. Introdução a sistemas de banco de dados. 7. ed. Rio de Janeiro: Campus, 2000. ELMASRI, R.; NAVATHE, S. B. Sistemas de banco de dados. 7. ed. São Paulo: Pearson Addison Wesley, 2015. SILBERSCHATZ, A.; KORTH, H. F.; SUDARSHAN, S. Sistemas de banco de dados. 5. ed. Rio de Janeiro: Campus, 2006. Próxima aula Comando Select para �ltrar linhas. Explore mais PostgreSQL Prático; Postgresql.org; Tipos de dados no Postgresql e Sql Server . javascript:void(0); javascript:void(0); javascript:void(0); javascript:void(0); Implementação de Banco de Dados Aula 4: LINGUAGEM SQL – SELECT – PARTE 2 Apresentação Na última aula, você começou o seu estudo do comando Select vendo a cláusula Select e From. Nesta aula, aprenderá a �ltrar as linhas das tabelas utilizando a cláusula Where. Objetivos Analisar a Cláusula Where; Formular Consultas. Nesta aula, continuaremos utilizando o Banco de Dados da Empresa para os exemplos de Modelo Lógico. As tabelas possuem os seguintes dados: Região Departamento Empregado Cliente Comentário Foi escolhido como base o PostgreSql, por ser um SGBD mais leve e fácil de instalar, porém, se for possível usar o SqlServer ou o Oracle quando houver diferença entre os SGBD’s, você será informado. Gerando restrições às consultas Até agora, todos os comandos que demos retornaram todas as linhas da tabela. Mas e se você desejar �ltrar as tabelas e retornar apenas as linhas que atendam a uma condição? Nesse caso, você deve acrescentar a cláusula Where ao comando de Select. Saiba mais A cláusula Where estabelece uma condição que a linha deverá obedecer para que faça parte do conjunto resposta da consulta. No caso, apenas retornam as linhas cujo teste da condição dê como resposta verdadeiro. Ao selecionar os dados para visualização ou outra necessidade, podemos, além de ordená-los, restringir o espectro de visualização utilizando a cláusula Where. Repare que, das seis linhas que a tabela possui “empregado com ID de 1 até 6”, somente retornam as três que possuem ID maior que 3 (4,5 e 6). Na construção das condições, você pode utilizar os seguintes operadores relacionais: = igual <> diferente < menor que > maior que >= maior ou igual a <= menor ou igual a Um cuidado que você deve tomar é com o tipo de dado que está utilizando para �ltrar. No caso anterior, era um dado numérico (ID) e bastava escrevê-lo. Mas e se fosse um texto? Veja os dados da tabela CLIENTE. Cliente Você deseja o ID do cliente Ponto Quente, cujo valor é 110. O Comando seria então: SELECT ID FROM CLIENTE WHERE NOME = 'PONTO QUENTE' Veja o retorno. Retorno do Comando. Por que não voltou o ID do cliente, já que existe esse nome na tabela? Veja o mesmo comando no Oracle e no SqlServer. Retorno do Comando Repare que, no SqlServer, retornou o valor 110 e no Oracle, não. O que está acontecendo? Alguns SGBD são case sensitive para os dados, ou seja, fazem diferenciação entre letra maiúscula e letra minúscula. . Dessa forma, temos que respeitar isso, ou a consulta poderá não retornar as linhas. No caso do nosso banco de dados, o nome do Cliente está com a primeira letra de cada palavra em maiúsculo, portanto, temos que escrever dessa forma no comando. SELECT ID FROM CLIENTE WHERE NOME = 'Ponto Quente' Retorno do Comando. Você pode estar pensando: Signi�ca, então, que eu tenho que saber como está escrito no Banco de Dados? E se eu não souber? E se em uma linha estiver tudo maiúsculo e na outra, tudo minúsculo? E se o banco não tiver um padrão? Neste caso, você deve padronizar a consulta utilizando uma função que leve o valor existente no banco de dados para maiúsculo ou para minúsculo antes de fazer a comparação. http://estacio.webaula.com.br/cursos/GRA302/aula4.html Veja os exemplos a seguir. Retorno do Comando Repare que: Nos comandos PONTO QUENTE, está em maiúsculo e foi utilizada a função UPPER para levar o conteúdo da coluna NOME para maiúsculo antes da comparação; UPPER atua apenas na comparação, não altera o valor do existente no banco de dados. Veja o exemplo abaixo, onde, ao pedirmos para retornar também o nome, ele vem como está no banco de dados. Dois cuidados que você deve tomar ao trabalhar com string: A string deve vir entre apóstrofes ‘PONTO QUENTE‘; Se o SGBD for case sensitive, você deve escrever o comando como os dados que estão no banco ou utilizar uma função para padronizar a forma de comparação. Retorno do Comando Trabalhando com Datas Ao trabalhar com datas, devemos colocá-las entre aspas simples, no formato dd/mm/aaaa, onde “dd” é o dia em dois dígitos, “mm” o mês em dois dígitos e “aaaa” é o ano em quatro digitos. Exemplo Mostrar sobrenome e senha dos empregados admitidos em 3/3/2009. SELECT ULT_NOME, DT_ADMISSAO FROM EMPREGADO WHERE DT_ADMISSAO = '3/3/2009'; Retorno do Comando Teste também os seguintes comandos: SELECT ULT_NOME, DT_ADMISSAO FROM EMPREGADO WHERE DT_ADMISSAO = '3/MAR/2009' ou SELECT ULT_NOME, DT_ADMISSAO FROM EMPREGADO WHERE DT_ADMISSAO = '3/MARÇO/2009' SELECT ULT_NOME, DT_ADMISSAO FROM EMPREGADO WHERE DT_ADMISSAO = '3/MAR/09' Saiba mais Estes formatos também são aceitos, mas, no PostgreSql, deve-se ter um cuidado: o nome/abreviatura dos meses devem ser em inglês: Março - March Abr - Apr Já o Oracle e o SqlServer dão suporte aos nomes em português. Atenção! Aqui existe uma videoaula, acesso pelo conteúdo online Consultando dados com várias condições Você pode especi�car critérios complexos combinando várias condições de pesquisa. A utilização dos operadores lógicos AND e OR permite montar expressões lógicas para �ltrar as linhas. Como toda expressão lógica, o operador AND somente retorna Verdadeiro (TRUE) se ambas as condições forem verdadeiras, enquanto o operador OR somente retorna FALSO (FALSE) se as duas condições forem falsas. Veja a tabela EMPREGADO. Vamos supor que você deseja mostrar os empregados que tenham sido contratados após primeiro de janeiro de 2010 e que tenham salário maior que R$10.000. O comando e o resultado estão na �gura a seguir. Comando e Retorno Repare que a utilização do AND obriga que as duas condições sejam verdadeiras para que a linha retorne. Se tivéssemos utilizado OR, bastaria uma ser verdadeira para que a linha retornasse. Comando e Retorno Atividade 1. Utilizando o nosso banco de dados de exemplo para fazer alguns exercícios. Mostrar primeiro nome e sobrenome dos empregados lotados no departamento, cuja identi�cação é 20. O retorno esperado é exibido na �gura. Retorno do comando 2. Utilizando o nosso banco de dados de exemplo para fazer alguns exercícios. Mostrar sobrenome e cargo dos empregados admitidos após 3/3/2009. O retorno esperado é exibido na �gura. Retorno do comando 3. Utilizando o nosso banco de dados de exemplo para fazer alguns exercícios. Mostrar sobrenome, cargo e salário dos empregados que não sejam Vendedores. O retorno esperado é exibido na �gura. Retorno do comando 4. Utilizando o nosso banco de dados de exemplo para fazer alguns exercícios. Mostrar sobrenome, cargo e salário dos empregados que ganham menos que R$18.000 e que não sejam vendedores. O retorno esperado é exibido na �gura. Retorno do comando 5. Utilizando o nosso banco de dados de exemplo para fazer alguns exercícios.Mostrar sobrenome, cargo e salário dos empregados que são vendedores ou que ganham pelo menos R$18.000. O retorno esperado é exibido na �gura. Retorno do comando 6. Utilizando o nosso banco de dados de exemplo para fazer alguns exercícios. Altere o comando: SELECT ULT_NOME, CARGO, SALARIO FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR' OR SALARIO >= 18000 Para mostrar sobrenome, cargo e salário dos empregados que ganham menos que R$18.000 e que não sejam vendedores. Essa alteração deverá ser feita pelo acréscimo do operador lógico NOT e de parênteses onde for o caso. O retorno esperado é exibido na �gura. Retorno do comando 7. Utilizando o nosso banco de dados de exemplo para fazer alguns exercícios. Mostrar todos os dados dos empregados contratados que trabalham no departamento 10 ou no departamento 30 (resolver utilizando operadores lógicos). O retorno esperado é exibido na �gura. Retorno do comando Atenção! Aqui existe uma videoaula, acesso pelo conteúdo online Operadores da Linguagem SQL A linguagem SQL possui um conjunto próprio de operadores para testar condições. Esses operadores são: IN CONTIDO EM (LISTA) BETWEEN ENTRE VALORES LIKE STRING SEMELHANTE IS NULL Testa valores nulos Vamos agora estudá-los mais detalhadamente. Operador IN O operador IN permite comparar o valor da coluna com uma lista de valores e retorna verdadeiro se, em uma determinada linha, o valor da coluna for igual a um dos valores da lista. Exemplo Desejamos listar os empregados que trabalham no departamento 10 ou no departamento 30. Como �caria o comando? SELECT * FROM EMPREGADO WHERE ID_DEPTO IN (10,30) Observe o retorno na �gura. Agora, para ver os empregados que NÃO trabalham nestes departamentos, basta acrescentar NOT ao comando: SELECT * FROM EMPREGADO WHERE ID_DEPTO NOT IN (10,30) Observe o retorno na �gura. Retorno do comando Atenção! Aqui existe uma videoaula, acesso pelo conteúdo online Atividade 8. Utilizando o nosso banco de dados de exemplo para fazer alguns exercícios. Mostrar sobrenome, cargo e código do departamento dos empregados que NÃO são Gerentes de Vendas ou Vendedores (resolver utilizando IN). O retorno esperado é exibido na �gura. Retorno do comando Operador Between O operador Between And permite veri�car se o valor de um campo está contido em uma faixa de valores. Por exemplo, desejamos retornar o id ult_nome e cargo dos empregados com salários entre R$8.000 e R$19.500 inclusive. O comando seria: SELECT * FROM EMPREGADO WHERE SALARIO BETWEEN 8000 AND 19500 Observe o retorno na �gura. Retorno do comando. Repare que retornam os empregados com salários de R$8.000 e de R$19.500. Isso mostra que o Between cria um intervalo fechado, ou seja, os limites fazem parte dos valores aceitáveis. Para fazer a condição inversa, basta utilizarmos Not Between. SELECT * FROM EMPREGADO WHERE SALARIO BETWEEN 8000 AND 19500 Observe o retorno na �gura. Retorno do comando Podemos utilizar Between com Datas. Por exemplo, desejamos os empregados contratados em 2009. O comando seria: SELECT * FROM EMPREGADO WHERE DT_ADMISSAO BETWEEN '1/1/2009' AND '31/12/2009' Observe o retorno na �gura. Retorno do comando Atividade 9. Mostrar Ult_nome, data de admissão e salário dos empregados contratados no ano de 2010 (resolver utilizando Between). O retorno esperado é exibido na �gura. Retorno do comando 10. Mostrar Ult_nome, data de admissão e salário dos empregados que ganham menos que R$8.000 ou mais que R$18.000 (resolver utilizando Between). O retorno esperado é exibido na �gura. Retorno do comando Operador Like O operador Like é utilizado para fazer casamento de padrão, ou seja, procurar um conjunto de caracteres que existe em uma string. Esta operação de comparação, para poder ser e�ciente, necessita do uso de caracteres coringa, que no caso do SQL são dois: % - Curinga para representar uma quantidade arbitrária de caracteres (inclusive nenhum); _ - Curinga para indicar a existência obrigatória de um caracter naquela posição. Exemplo Desejamos saber os dados dos empregados com o ult_nome começado com N. O comando seria: SELECT * FROM EMPREGADO WHERE UPPER(ULT_NOME) LIKE 'N%' Observe o retorno na �gura. Retorno do comando. Observações Repare o uso da função UPPER. Esta função leva a string para maiúsculo antes de fazer a comparação, sendo muito útil para contornar a limitação do Oracle e do Postgresql, por seremCase Sensitive. Uma outra função possível de ser utilizada é lower, que leva o texto todo para minúsculo. No SqlServer, você deve eliminar UPPER, já que ele não é Case Sensitive. Note o % depois do N informando que, após essa letra, pode existir uma quantidade arbitrária de caracteres. Outro exemplo. Exemplo Desejamos saber os dados dos empregados com o ult_nome terminado com S. O comando seria: SELECT * FROM EMPREGADO WHERE UPPER(ULT_NOME) LIKE '%S' Observe o retorno na �gura. Retorno do comando E se você quiser retornar os PRIMEIROS NOMES dos empregados que possuem E em qualquer lugar do nome? Como seria o comando? O retorno esperado é exibido na �gura. Repare no retorno. Temos Carmen com E no meio, mas temos também Ernane que possui E no início e no �nal, pois o % autoriza a ter uma quantidade arbitrária de caracteres, inclusive nenhum. Retorno do comando. Solução. Se você desejasse retornar os sobrenomes que possuem E como segunda letra, não adiantaria utilizar %. Para isso temos que informar que OBRIGATORIAMENTE deve existir uma letra antes do E, utilizando nosso outro curinga, o ‘_’. O comando seria então: SELECT ULT_NOME FROM EMPREGADO WHERE UPPER(ULT_NOME) LIKE '_E%' Observe o retorno na �gura. Retorno do comando. Note que temos que ter um _ no antes do E para informar que o E é a segunda letra e que, após ela, podemos ter uma quantidade qualquer de caracteres. Alguns cuidados: O ‘_’ deve estar colado no ‘E’. Não pode haver espaço entre eles; Você deve colocar um ‘_’ para cada caractere. Por exemplo, para L na terceira, o comando seria o da �gura abaixo. Comando e retorno. Agora, quando você deseja quem não tem R no nome, deve acrescentar o NOT antes do LIKE. Veja a �gura. Comando e retorno. ILIKE Devido ao fato do PostgreSql ser Case Sensitive, nos comandos utilizamos o UPPER, porém este SGBD possui um operador de LIKE proprietário que permite que façamos o teste sem o uso da função UPPER. É o ILIKE, ou seja, Insensitive-LIKE. Quando o utilizamos, não precisamos nos preocupar com maiúsculas ou minúsculas. Veja a �gura. Comando e retorno. Atividade 11. Mostrar primeiro nome e sobrenome dos empregados cujo nome comece pela letra L. O retorno esperado é exibido na �gura. Retorno do comando 12.Mostrar todos os dados dos clientes cujo nome possua a palavra Casa. O retorno esperado é exibido na �gura. Retorno do comando 13.Mostrar todos os dados dos clientes que possuem pelo menos 13 letras no nome. O retorno esperado é exibido na �gura. Retorno do comando 14.Mostrar todos os dados dos clientes que possuem 12 letras ou menos no nome. O retorno esperado é exibido na �gura. Retorno do comando Operador Is Null O operador Is Null visa determinar se, no campo, existe valor ou não (o campo é nulo). Um valor nulo é um valor que está indisponível, não foi atribuído, é desconhecido ou inaplicável, tornando inviável usar ‘=’ no teste. Como nulo não é valor, mas sim ausência de valor, ele não pode ser igual ou diferente de qualquer outro valor. Considere a tabela CLIENTE. Se você desejasse retornar todos os dados dos CLIENTES que não são atendidos por um vendedor, o comando seria: SELECT * FROM CLIENTE WHERE VENDEDOR IS NULL Observe o retorno na �gura. Retorno do comando. Caso contrário, se você quisesse os que são atendidos por vendedor, utilizaria IS NOT NULL. Veja o comando e o retorno na �gura. Comando e retorno. Atividade 15.Utilizando o banco de dados da seguradora, emita os comandos abaixo utilizandooperadores relacionais e lógicos. a. Selecione todos os dados dos proprietários que têm código maior que 10816. b. Selecione todos os dados dos modelos cujos nomes vêm alfabeticamente depois de ‘ka’. c. Selecione todos os dados dos veículos cujo ano de fabricação é diferente do ano do modelo. d. Selecione todos os dados dos veículos cujo valor segurado seja menor de R$30.000. e. Selecione todos os dados dos veículos do modelo 104. f. Selecione a placa dos veículos do proprietário 10.823. g. Selecione a placa, o proprietário e a cor dos veículos pretos ou do proprietário 10.812. h. Selecione a placa, a cor e o modelo dos veículos pretos do modelo 105. 16. Utilizando o banco de dados da seguradora, emita os comandos abaixo utilizando operadores do SQL. a. Selecione o código do proprietário que não possui CPF cadastrado. b. Selecione todos os dados dos modelos cujo nome inicie por ‘P’. c. Selecione todos os dados dos veículos cujo ano de fabricação esteja entre 2010 e 2013. d. Selecione todos os dados dos veículos que não são dos modelos 102 ou 105. e. Selecione todos os dados dos modelos cujo nome não esteja entre Gol e Palio inclusive. f. Selecione a placa e o código dos proprietários dos veículos pertencentes aos proprietários 10.823 ou 10.812. g. Selecione todos os dados dos proprietários cujo CPF não tenha a sequência ‘33’. h. Selecione todos os dados dos modelos cuja segunda letra seja ‘A’. Notas Case sensitive Você deve considerar que o SqlServer, a princípio, não faz distinção entre maiúsculas e minúsculas, enquanto o Oracle e o PostGreSql fazem. Na realidade, o SqlServer pode ou não fazer. É uma con�guração que o DBA faz no servidor, mas o padrão é não fazer tal distinção. Referências DATE, C. J. Introdução a sistemas de banco de dados. 7. ed. Rio de Janeiro: Campus, 2000. ELMASRI, R.; NAVATHE, S. B. Sistemas de banco de dados. 7. ed. São Paulo: Pearson Addison Wesley, 2015. SILBERSCHATZ, A.; KORTH, H. F.; SUDARSHAN, S. Sistemas de banco de dados. 5. ed. Rio de Janeiro: Campus, 2006. Próxima aula Ordenamento do resultado de consulta; Funções de grupo. Explore mais Tipos de dados no Postgresql e Sql Server javascript:void(0); Implementação de Banco de Dados Aula 5: LINGUAGEM SQL – Funções de Grupo Apresentação Na aula passada, você aprendeu a consultar os dados de uma tabela, a de�nir as colunas que retornam e a �ltrar as linhas. Nesta aula, você aprenderá a gerar dados agregados utilizando funções de grupo e a ordenar a saída da consulta. Objetivos Reproduzir funções de grupo; Ordenar o resultado das consultas. Banco de dados de exemplo Continuaremos utilizando o Banco de Dados da Empresa para os exemplos. Modelo Lógico As tabelas possuem os seguintes dados: Região Departamento Empregado Cliente Tendo este Banco em mente, é altamente recomendável que você execute os comandos de exemplo no PostGreSql.: Comentário Foi escolhido como base o PostgreSql, por ser um SGBD mais leve e fácil de instalar, porém, se for possível usar o SqlServer ou o Oracle quando houver diferença entre os SGBD’s, você será informado. Eliminando valores duplicados (DISTINCT) Analise o conteúdo da tabela Empregados Observe que na tabela Empregados, RODRIGUES e UGARTE possuem o mesmo cargo. E se desejássemos ver os diferentes cargos? Se comandássemos SELECT CARGO FROM EMPREGADO, teríamos o resultado desejado? Em termos de dados, até poderíamos dizer que sim, que todos os cargos aparecem no resultado. Mas, em termos de facilidade para o usuário, isso é su�ciente? Imagine que a tabela tivesse milhares de linhas com dezenas de cargos diferentes. O usuário �caria confuso, pois teria muita di�culdade de isolar todos os cargos existentes ali. Para resolver esse tipo de caso, podemos, no comando, eliminar os valores duplicados, retornando apenas uma vez cada cargo. Para tal, devemos acrescentar a cláusula distinct ao comando de Select. Dessa forma o comando seria: SELECT DISTINCT CARGO FROM EMPREGADO Agora conseguimos o resultado que queríamos. Observe agora o seguinte comando: SELECT DISTINCT CARGO, ULT_NOME FROM EMPREGADO Note que voltaram os dois vendedores. Por que isso ocorreu se o DISTINCT continua antes do cargo? O Distinct, na realidade, �ltra as linhas diferentes, não os valores da coluna. Como os dois vendedores possuem nomes diferentes, as linhas são distintas, portanto as duas retornam. Atividade Utilizando o nosso banco de dados de exemplo para fazer alguns exercícios. 1. Mostrar uma única vez os cargos dos empregados como o cabeçalho Cargos Diferentes. O retorno esperado é exibido na �gura. Retorno do comando Agregando dados Até agora todos os comandos que demos retornavam uma linha para cada linha da tabela ou uma linha para cada linha da tabela que atendesse a condição da cláusula Where. Veja os exemplos abaixo. No primeiro comando, retornaram três linhas, o total de linhas da tabela. No segundo comando, duas linhas, o total de linhas que atendem a condição da cláusula Where. Observe agora os próximos dois comandos. Note que, com os dois comandos, retornou apenas uma linha. No primeiro, com valor três (total de linhas da tabela) e no segundo, com valor dois (quantidade de linhas que atendem a cláusula Where). O que mudou? Foi acrescentado COUNT na claúsula Select que, como você já deve ter percebido, contou as linhas que a consulta retornaria e exibiu esse valor. O que �zemos foi agregar dados, ou seja, derivamos um dado sumarizado a partir dos dados da tabela. Saiba mais Para fazer isso, você deverá utilizar as FUNÇÕES DE GRUPO, das quais COUNT é um exemplo. Funções de grupos Uma função de grupo atua em uma instância da tabela, ou seja, no conjunto de suas linhas. As funções agregam os dados a partir de todas a linhas da tabela ou de grupos em que as linhas possam ser enquadradas. A princípio, a tabela forma um único grupo, e a consulta com função de grupo retornará uma única linha. Ao utilizarmos a cláusula GROUP BY, podemos dividir a tabela em grupo, sendo que a consulta, então, retornará uma linha para cada grupo. Na linguagem SQL, possuímos as seguintes funções de grupo: Clique nos botões para ver as informações. Retorna a MÉDIA aritmética dos valores da coluna solicitada; Exemplo: AVG (valor_segurado); Se na coluna existirem nulos, ela os desconsidera no cálculo, Os valores na coluna devem ser numéricos. AVG (coluna) Retorna o MAIOR valor existente na coluna solicitada; Exemplo: MAX (CPF); Se na coluna existirem nulos, ela os desconsidera. Os valores na coluna podem ser numéricos, alfanuméricos ou datas MAX (coluna) Retorna o MENOR valor existente na coluna solicitada; Exemplo: MIN (CPF); Se na coluna existirem nulos, ela os desconsidera; Os valores na coluna podem ser numéricos, alfanuméricos ou datas MIN (coluna) Retorna a soma aritmética dos valores da coluna solicitada; Exemplo: SUM (valor_segurado); Se na coluna existirem nulos, ela os desconsidera no cálculo; Os valores na coluna devem ser numéricos. SUM (coluna) Retorna o número de valores não nulos da coluna solicitada; Exemplo: COUNT (CPF); Os valores na coluna podem ser numéricos, alfanuméricos ou datas COUNT (coluna) Retorna o número de linhas que a consulta retornaria; Exemplo: COUNT (*); Considera os valores nulos. COUNT (* ) Vejamos alguns exemplos. Acesse o PostgreSQL e digite o seguinte comando: SELECT * FROM EMPREGADO. Esse comando seleciona dados de todos os empregados Vamos supor que você deseja recuperar apenas o valor médio dos salários e a soma dos salários. Para essa consulta, você comandaria: SELECT AVG(SALARIO), SUM(SALARIO) FROM EMPREGADO Atenção Apesar de já ter sido dito, lembre-se sempre de que as funções AVG e SUM são numéricas, ou seja, exigem que os seus argumentos (colunas que passamos a função; no exemplo, a coluna SALÁRIO) sejam números. Vejamos mais exemplos. Agora você deseja listar o maior e o menor valor de salário de um empregado, os sobrenomes que aparecem
Compartilhar