Baixe o app para aproveitar ainda mais
Prévia do material em texto
Álgebra Relacional e S Q L – Structured Query Language prof.joao.hypolito@gmail.com Histórico Características Conceitos Iniciais A evolução dos SGBDs Padronização A Álgebra Relacional (Operações) Comandos do SQL Operações da AR X Comandos SQL 18 de fevereiro de 2020 3 Programação Modelos De Dados ao longo do tempo 1900 10 20 30 40 50 60 70 80 90 2K 10 Modelos SGBDs que Usam os modelos P D P D Hierárquico Rede RedeHierárquico Relacional Relacional MER MER O. O. ≈1905 – Hierarárquico Dificuldade de representar no Conceitual e Lógico, para certos contextos é implementado com redundância de dados. ≈ 1930 – Redes – Resolveu o problema da redundância mas ... complicou a linguagem de programação. P D SI P D Físico P D Lógico P D P D Conceitual O que se tenta resolver? Perceber Representar Construir Histórico dos Modelos de Dados e produtos respectivos Empresa (organização com OBJETIVO) ≈1970 – Relacional O banco de dados é representado por tabelas. Tabela é um conjunto LINHAS organizadas em COLUNAS com valores bem definidos para cada linha e coluna. A coleção de valores escritos na linha (em cada coluna) é um elemento de um conjunto. ≈1978/79 – MER Entidade é um conjunto, as associações são os relacionamentos entre ocorrências das entidades. As entidades são caracterizadas por atributos. A intensidade dos relacionamentos é a cardinalidade. Este ciclo de produção é uma fonte de riqueza. Empresas se interessam em desenvolver SGBDs. Em cerca de 15 anos temos a lista dos produtores de SGBDs. Modelos: Relacional, MER e Hierárquico 4th Dimension Firebird Mimer SQL SQLite Adabas D FrontBase MonetDB Sybase Adaptive Server Enterprise Alpha Five Gladius DB mSQL Sybase Adaptive Server IQ Apache Derby Greenplum MySQL (1994) Sybase SQL Anywhere BlackRay H2 Netezza tdbengine CA-Datacom Helix database NonStop SQL Teradata CSQL HSQLDB Openbase The SAS system CUBRID IBM DB2 (1982) Informix(Relacional) OpenLink Virtuoso (OS) TimesTen Daffodil database IBM DB2 Express-C OpenLink Virtuoso Universal Server txtSQL DataEase Informix (ER) Oracle (1978) UniData DataFlex (1981) (ER-1988) Ingres (1973) Oracle Rdb for OpenVMS UniVerse Dataphor InterBase Paradox (ER) Valentina (Database) dbase (1980) InterSystems Caché Pervasive Vertica DB-Fast IMS (IBM) (1967) PostgreSQL(1985-1995) VistaDB Derby aka Java DB Kognitio Progress 4GL VMDS EffiProz Linter RDM Embedded Vulcan ElevateDB MaxDB RDM Server WCE SQL Plus Empress Embedded Database Mckoi SQL Database Sav Zigzag XSPRADA EnterpriseDB Microsoft Access ScimoreDB ZIM (1983)(ER) eXtremeDB Microsoft Jet Database Engine (Access) SmallSQL Clipper(1984) fastDB Microsoft SQL Server (Express) (1993) solidDB FileMaker Pro Microsoft Visual FoxPro SQLBase Distribuição dos SGBDs e Modelos de Dados Tópico Definição 18 de fevereiro de 2020 6 Conceitos Fundamentais do Modelo Relacional Banco de Dados Conjunto de arquivos que armazenam os dados de uma organização Base de Dados Conjunto de arquivos de dados e também todos os outros componentes que dão suporte ao funcionamento de um sistema de informação Tabela Representação de um dos conjuntos de dados necessários para definir o banco de dados de uma organização. A tabela deve ser correspondente a um conjunto no sentido estrito da matemática. Ou seja, deve conter dados de um só conjunto importante para a realização dos procedimentos e controle das regras de uma organização Tópico Definição 18 de fevereiro de 2020 7 Conceitos Fundamentais do Modelo Relacional Tupla Também denominada LINHA da tabela, é uma coleção de valores que representam uma ocorrência da realidade que está retratada na tabela Campo Uma das colunas de uma tabela. De outra forma, é a propriedade associada a um grupo de valores contidos em uma tabela, assim sendo, podemos dizer que, em uma tabela de PRODUTOS, um campo seria a descrição do produto ou outro campo seria a quantidade em estoque Domínio de valor Regra que define um intervalo de valores válidos para um campo da tabela. C9 0E 2D D3 45 87 12 A3 0A Um exemplo de uma árvore binária (índice) #R Código Nome cidade 1F 1 Ana A 0A 2 Angela C A3 3 Raissa A 12 4 Anita B 45 5 Rosa A 87 6 Luiza C D3 7 Carolina A C9 8 Vera E 0E 9 Natália D 2D 10 Beatriz B 18 de fevereiro de 2020 8 1F Uma Base de Dados pode conter os arquivos que implementam os índices de acesso às tabelas do banco de dados. Mas... O que é o índice? Código Uma Base de Dados pode conter os arquivos que implementam os índices de acesso às tabelas do banco de dados. Mas... O que é o índice? Um exemplo de uma árvore binária (índice) #R Código Nome cidade 1F 1 Ana A 0A 2 Angela C A3 3 Raissa A 12 4 Anita B 45 5 Rosa A 87 6 Luiza C D3 7 Carolina A C9 8 Vera E 0E 9 Natália D 2D 10 Beatriz B 18 de fevereiro de 2020 9 cidade 1F:A3:45:D3 12:2D 0A:87 0E C9 O Modelo Relacional tem dois Princípios herdados da Teoria dos Conjuntos: UNICIDADE DA TUPLA: não existem tuplas repetidas em uma tabela (qualquer que seja a tabela) Este princípio tem uma consequência: SEMPRE existe uma combinação de campos (em qualquer tabela) tal que ao se fornecer os seus valores encontra-se no máximo UMA tupla na tabela. Isso é a definição da CHAVE PRIMÁRIA Também é possível definir a chave estrangeira: Um campo de uma tabela que tem um correspondente campo em outra e nesta outra é chave primária UNICIDADE DE VALOR DE CAMPO DE TUPLA: não existe, em um campo qualquer de uma tupla qualquer de uma tabela qualquer, dois valores distintos no mesmo instante. O Modelo Relacional tem Duas REGRAS de INTEGRIDADE: Integridade da Tabela: A CHAVE PRIMÁRIA da tabela NÃO pode ser NULA. Integridade Referencial: Uma Chave Estrangeira pode ter dois valores: SER NULO (sem valor) TER um VALOR dentro da série de valores da Chave Primária correspondente. 18 de fevereiro de 2020 10 Conceitos Fundamentais do Modelo Relacional Álgebra Relacional – A Necessidade de Processamento Em uma mesma Base de Dados podemos ter Tabelas com dados específicos de cada área funcional do Modelo estudado Isto é mesmo o pretendido em um bom projeto, dados unicamente representantes de elementos de conjuntos necessários ao contexto real. Os Dados das tabelas destes Banco de Dados diferentes podem criar novas situações de interpretação onde teremos que "COMBINAR" dados de diferentes Tabelas de uma mesmo Banco de Dados ou até de diferentes Bancos de Dados; Temos que trabalhar com dados de diferentes Bancos de Dados ou mesmo de diferentes tabelas de um Banco de Dados. Esta necessidade pode (e deve) ser traduzida para uma linguagem computacional e permitir a construção de aplicativos que serão usados para extrair dos Bancos de Dados parâmetros para tomada de decisão (Informação). A Especificação destes novos programas aplicativos deve ser Universal, ou seja, entendida por todos os programadores e analistas que participam da construção da aplicação. É necessário criar uma série de símbolos que interpretem as necessidades de processamento. Este conjunto é a Álgebra Relacional. A Linguagem que implementa a Álgebra Relacional é a SQL (Structured Query Language). Dadas as tabelas abaixo responda à pergunta: Quais as marcas dos carros cujos os donos tenham nome “Jose” e que não fizeram serviço de "pintura" no mês de “Maio de 2010”? Clientes CdCli Nome A José B João C Ana D José E Fábio … … Carros CdCli CdCarro Marca A F1 Gol B F2 Celta A F3 Corsa C F4 Gol D F5 Fox A F6 Corsa … … … Aplicação CdCarro CdSer DtServiço F1 1 09/10/2010 F1 5 10/10/2010 F2 2 09/10/2010 F2 3 10/10/2010 F2 4 11/10/2010 F3 1 09/10/2010 F3 4 10/10/2010 F5 3 11/10/2010 F5 4 09/10/2010 F4 2 10/10/2010 F4 3 10/10/2010 F3 5 11/10/2010 F4 1 11/10/2010 … … Álgebra Relacional – A Necessidade de Processamento Serviços CdSer Nome 1 Pintura2 Suspensão 3 Motor 4 Elétrica 5 Painel e revestimento … … Criar uma forma de representar as operações sobres as tuplas de diferentes tabelas Conceitos: Entender as Tabelas como CONJUNTOS de Tuplas; As Tuplas passam a ser ELEMENTOS de CONJUNTOS; Para realizar a UNIÃO, INTERSECÇÃO e SUBTRAÇÃO entre elementos de conjuntos distintos, é necessário que os elementos tenham características idênticas. Quando duas tabelas têm o mesmo esquema elas são ditas UNIÃO COMPATÍVEIS. Portanto podemos combinar suas tuplas em operações de UNIÃO, INTERSECÇÃO e SUBTRAÇÃO. As operações de Álgebra Relacional manipulam no máximo duas tabelas. As tabelas criadas pelo procedimento podem “quebrar” algumas das características do Modelo Relacional. Nesta condição as tabelas são ditas “LIVRES” – podem ter tuplas repetidas ou campos repetidos na mesma tabela (sem contrariar a multivaloração) A Álgebra Relacional permitiu o desenvolvimento de uma linguagem para manipular os dados. Álgebra Relacional – Solução – Conceitos Fundamentais A implementação SQL - Características Amigável para o usuário, sem ser inconveniente para programadores e analistas de sistemas; Aprendizado fácil e podendo ser usada em linguagens procedurais tais como C, COBOL ou PL/I; Pode ser usada como linguagem de comunicação entre o usuário e o programador facilitando a comunicação; Instrumento amigável para programadores e analistas sem perder o rigor matemático da Álgebra Relacional. 18 de fevereiro de 2020 14 1970: E.F. Cood – "A Relational Model of Data Large Shared Banks" Modelo Relacional Álgebra Relacional indica uma Linguagem de Pesquisa; Apresenta a Álgebra Relacional um conjunto de símbolos que permite uniformizar o entendimento sobre as operações que podemos realizar sobre as tuplas das tabelas Consequência da Álgebra Relacional: Criação da Linguagem de Manipulação para pesquisa dos dados nos Banco de Dados 1974: Donald D. Chamberlin e Raymond Boyce (pesquisadores do "IBM San Jose Research Center"): artigo sugerindo a forma da linguagem de consulta estruturada. A linguagem foi chamada de SEQUEL; 1975: Chamberlin, Boyce, King e Hammer apresentam a SQUARE (com símbolos matemáticos), semelhante a SEQUEL (mas esta tinha termos em inglês); 1976: Chamberlin apresenta a SEQUEL2 que passa a ser usada como linguagem de consulta para o banco de dados que se pesquisava na IBM, o sistema R: criam-se grupos de usuários, a linguagem começa a evoluir rapidamente ; 18 de fevereiro de 2020 15 SQL - Histórico 1977: O SystemR-IBM (com SEQUEL/2) torna-se operacional + Oracle (Relacional) 1980: Chamberlin sumariza as experiências dos usuários com a linguagem (seu nome a esta altura já era SQL – Structured Query Language). 1983: IBM lança o DB2. Outros produtos no mercado: Sybase, Ingres, Progress), SQL é padrão de fato; 1986: SQL é homologa como linguagem padrão ANSI para tratamento de dados em BD Relacionais 1987: Padrão ANSI é aceito pelo ISO (ANSI:86) 1989: SQL incorpora características de tratamento de integridade (ANSI:89) 1992: Os comitês ISO e ANSI apresentam SQL2 (ANSI:92) 1999 SQL3 18 de fevereiro de 2020 16 SQL - Histórico Os SGBDs são o resultado do amadurecimento das necessidades empresariais para suporte no desenvolvimento de Sistemas de Informação Sistemas de Informação são um conjunto de computadores e outros dispositivos que tem a finalidade de fornecer meios de entrada, armazenamento, processamento e apresentação de saídas de dados que permitam estabelecer parâmetros que melhorem a tomada de decisão pelas organizações (empresas, escolar, etc). OS SGBD devem dar suporte ao projeto do SI, portanto devem permitir: Criar os arquivos que armazenam os dados e administrar estes arquivos Implementar os programas aplicativos (que contém as regras de negócio das organizações). 18 de fevereiro de 2020 17 A Evolução dos SGBDs Grupo Descrição 18 de fevereiro de 2020 18 Os Grupos de Linguagens dos SGBDs DDL Data Defination Language Permite que sejam caracterizados a estrutura dos arquivos onde se gravam os dados da organização. É possível definir o tamanho dos campos, do arquivo como um todo e seus índices associados DCL Data Control Language Linguagem de Definição de Usuários e Grupos de usuários e suas atribuições de acesso aos Dados. DML Data Manipulation Language Linguagem de Manipulação de Dados. É o grupo de comandos que permite incluir uma nova linha em uma tabela, pesquisar uma linha, alterar alguns dos valores de seus campos ou mesmo remover a linha da tabela No grupo de linguagens, os SGBDs devem ter seus comandos agrupados em três categorias: A SQL foi estudada por vários grupos, logo se estabeleceram padrões: ANSI em 1986 e ISO em 1987 ANSI em 1992 (denominada ANSI:1992) ANSI 1999 e 2003 (ampliação da ANSI:1992) Na ANSI:1999 já define o padrão para triggers e stored- procedures (embora muitos SGBDs já tenham seus próprios comandos para tratar este recurso). Na ANSI:2003 introduz-se os tipos de dados não- escalonados e algumas características de programação orientada a objetos. 18 de fevereiro de 2020 19 Padronização SQL Aderência ao Padrão. Os SGBD podem ou não adotar o Padrão. Hoje os SGBD (quase todos) usam o ANSI:1992. Alguns têm o ANSI:1999, mas têm sintaxe própria para definir Triggers e Stored Procedures. Vamos exercitar o ANSI:1992. 18 de fevereiro de 2020 20 Padronização SQL Padronização SQL Aderência ao Padrão Vemos que os fabricantes de SGBDs podem implementar mais funções do que as padronizadas (até mesmo colocando mais comandos com performance melhorada). Chamamos de Linguagem NATIVA (ou proprietária); Mas não é interessante fugir do Padrão. Então muitos SGBDs “FALAM” SQL como uma alternativa de linguagem. Chamamos de SQL-PADRÃO. 18 de fevereiro de 2020 21 Ling. Nativa Padronização SQL Aderência ao Padrão Outros ainda percebem que podem combinar SQL com algumas funções do seu produto e geram comandos sintaticamente semelhantes com SQL mas com mais potencial de funções. Chamamos de SQL-NATIVO; Esquematicamente podemos representar as linguagens assim: Ling. SQL Nativa Linguagem SQL 18 de fevereiro de 2020 22 Padronização SQL – Tendências A SQL foi um marco na história do desenvolvimento de linguagens de computação; Sabemos como deve ser a linguagem e o que ela deveria fazer; Baseados nos fundamentos teóricos lançados na apresentação do Modelo Relacional; Conseqüência: Linguagem é elegante e econômica, consistindo de relativamente poucos comandos; A simplicidade da linguagem torna-se conveniente tanto para o usuário eventual como para um projetista sofisticado; Ainda se desenvolve padronização do SQL para alguns segmentos de linguagens, tais como: integridade referencial; gerência de transações melhorada; especificação de regras definida pelo usuário; facilidades de manuseio de caracteres; suporte a alfabetos nacionais. Também ocorrem alterações na SQL por sugestões de usuários e como resultados de testes no desenvolvimento da linguagem. 18 de fevereiro de 2020 23 Os grupos de comandos da SQL são: 18 de fevereiro de 2020 24 Padronização SQL Exercitando... Para uma prática de SQL criaremos as tabelas apresentadas no item: Exerc./ProjetosPrática AR-SQL(MariaDB) Script: Cria Base de Dados MariaDB-Prática de SQL no site da disciplina. Dinâmica: em sala de aula, executam-se os comandos iniciais do SQL… SELEÇÃO, PROJEÇÃO, PRODUTO CARTESIANO, JUNÇÃO… em uma base de dados de aula. Note, anote e repita os comandos em outras tabelas da base de dados para prática SQL. 18 de fevereiro de 2020 25 Símbolo Significado Significa a criação de uma Tabela; [[ c1, c2,... ]] Nomes de campos. [[condição]] Condição de comparação entre campos de tabelas ou de campos com valores. ][condição]] Condição de comparação entre campos detabelas com lista completa pela ESQUERDA. [[condição][ Condição de comparação entre campos de tabelas com lista completa pela DIREITA. ][condição][ Condição de comparação entre campos de tabelas com lista completa pelos dois lados – Junção completa. Muda o nome de um campo ou uma tabela dinamicamente. Pode também direcionar o resultado e um função para um campo ou variável. () Contém a especificação de uma função ou pode conter comandos encadeados. {} Contém a especificação de agrupamento ou ordenação. Álgebra Relacional – Simbologia A C1 C2 1 A 2 B 3 C B C1 C2 1 A 1 B 2 C 5 A C C2 C5 C6 A 1A Qwe B 2A Tgb D 1B Rty E 1C Ert Seleção RB[[C2="A"]] C1 C2 1 A 5 A AR – Quadro Geral das Operações Cria uma tabela a partir de UMA outra levando as tuplas que estão na primeira Tabela e que ATENDAM À UMA CONDIÇÃO; Esquema da tabela resultado é igual ao esquema da tabela de origem da pesquisa. Representação: R A[[Condição]] Por Exemplo: R A[[C2 contenha letra "w"]] A C1 C2 Asd Qwe Rfv Tgb Zxc Rty Fgh Erw Wsx Edc R C1 C2 Asd Qwe Fgh Erw AR – Seleção C2 tem W C2 NÃO tem W Observações: O Resultado da operação pode ser uma tabela SEM TUPLAS ( o que seria o conceito de conjunto vazio). A condição aceita qualquer tipo de operação, dependendo do SGBD poder implementar ou não tais operações; A condição pode processar todos os operadores de comparação matematicamente aceitos, tais como: = ≥ ou >= ≤ ou <= > < <> ou != ou ≠ E, OU, OU exclusivo e NÃO (operadores lógicos) AR – Seleção Comando SQL (SELECT) Implementa as sete operações da Álgebra Relacional Tem uma estrutura rígida mas com partes opcionais SELECT [ALL | DISTINCT [ON (expressao[,...])]] * | Campo1[AS nome_saída1][,...] FROM TAB1[, TAB2, TAB3, ... , TABn] [WHERE condição] [GROUP BY chave1[,chave2,...]] [HAVING condição[,...]] [{} select] [ORDER BY campo1 [ASC|DESC|USING operador][, campo2 [ASC|DESC|USING operador][, ...campon]; Tudo o que está entre [] é opcional O comando SELECT implementa: Seleção, Projeção, Junção (fechada) e Produto Cartesiano. Junções Abertas, União, Subtração e Intersecção são implementas usando operadores ou subcomandos. 18 de fevereiro de 2020 30 AR - SQL - SELECT Os grupos podem ser definidos assim: Parte Ação 18 de fevereiro de 2020 31 AR - SQL - SELECT SELECT Indica ao núcleo que deverá ser iniciado o processamento de leitura de dados das tabelas *| campo [as c1] Indica quais campos devem ser exibidos no resultado do comando. Se usarmos * vemos todos os campos processados, senão devemos indicar quais queremos ver. O uso do “as” permite o renomear os campos dinamicamente. FROM Especifica a(s) tabela(s) a processar. No caso de mais de uma tabela, os nomes devem ser separados por ',' WHERE Especifica uma condição do processamento das linhas da tabela. Pode-se combinar condições com operadores lógicos. Usa-se operadores de comparação específicos de cada SGBD. Os operadores Padrão ANSI/99 são: =, <>, >, <, >=, <=, IN, BETWEEN e os operadores lógicos são: AND, OR, NOT, XOR. Os grupos podem ser definidos assim: Parte Ação 18 de fevereiro de 2020 32 AR - SQL - SELECT GROUP BY Permite estabelecer agrupamentos de valores que podem apresentar a execução de funções escalares ou aritméticas HAVING Executam funções sobre valores de campos, podem indicar resultados que podem ser usados como parâmetro de agrupamento. ORDER BY Classificam os dados das tuplas segundo campos da tabela permitindo estabelecer organizações para execução de funções. As chaves de ordenação podem apresentar sub- chaves de ordenação e, para cada uma delas, ter ordens diferentes indicadas pelo uso de ASC ou DESC seguindo cada chave declarada. Sobre a Criação de Tabelas Podemos, também, montar o esquema de uma tabela indicando as propriedades de cada um de seus campos. Sintaxe: CREATE TABLE NomeTab (CAMPO1 TIPO(TAM) CaractComplem[, CAMPO2 TIPO(TAM) CaractComplem, … CAMPOn TIPO(TAM) CaractComplem[, PRIMARY KEY (CAMPO1)]] ) [ENGINE=InnoDB] [DEFAULT CHARSET=utf8] [COLLATE=utf8_bin]; 18 de fevereiro de 2020 33 SQL - Criando tabela Um exemplo... Para criar as tabelas usamos o CREATE TABLE: CREATE TABLE regioes (IDRegiao int(3) NOT NULL, NMRegiao varchar(15) NOT NULL, QTAreaestimada double(12,3) NOT NULL, PRIMARY KEY (IDRegiao)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; 18 de fevereiro de 2020 34 SQL - Criando tabela (MySQL) Este é um comando DDL da SQL já escrito para o SGBD MySQL. Note a especificação do ENGINE indicando o tipo InnoDB. A característica de adotar a tabela de símbolos UTF8- BIN somente informa como o SGBD deve tratar os símbolos da tabela de caracteres da língua nacional e de como isso é gravado nas tabelas e lidos a partir delas. Calma o script que cria a base está pronto e vamos distribuir. 18 de fevereiro de 2020 35 SQL - Criando tabela (MySQL) Podemos combinar os comandos de SELEÇÃO e de criação de uma tabela com aninhamento deste modo: CREATE TABLE final AS (resultado de um comando em SQL); Este comando cria um arquivo na estrutura de arquivos do SO, depois abre este arquivo e 'copia' as tuplas que 'recupera' da tabela que atendem o que está especificado depois da palavra WHERE. 18 de fevereiro de 2020 36 SQL - Criando tabela com aninhamento Criando tabelas temporárias Já pensou se o comando anterior fosse executado dentro de um programa disponibilizado na Internet e que tivesse um acesso momentâneo de uns.... 10000 usuários? Provavelmente o servidor pararia de trabalhar, e isso pode ser uma devido à criação de muitos arquivos em um só diretório, além disso o HD seria muito sobrecarregado. Para resolver isso o SQL dispõe de um recurso que é criar o arquivo em memória RAM, para isso basta escrever temporary após a palavra create. Desta forma... 18 de fevereiro de 2020 37 SQL - Criando tabela com aninhamento Podemos escrever: CREATE TEMPORARY TABLE depto01 AS (SELECT * FROM departamentos WHERE id_depto_superior IN ('A01','B01')); Neste comando a tabela será criada em memória (no pedaço de memória que o usuário pode acessar no servidor). Sendo um usuário acessando e executando o comando por uma aplicação WEB o mesmo acontece pois o servidor de aplicativo executado cria uma 'imagem' de memória para controlar o que os usuários visitantes executam. 18 de fevereiro de 2020 38 SQL - Criando tabela com aninhamento Em SQL podemos combinar o resultado de um comando com outros comandos. Isso se chama SUB-Queries (aninhamento). Cada comando faz exatamente uma operação. Desta forma a expressão em álgebra relacional: R Clientes[[Cidade=”Bauru”]] Deve ser vista como dois comandos em ANSI: O primeiro CRIA a tabela R O segundo FAZ a seleção e copia as tuplas para R Escrevendo em SQL… CREATE TABLE R AS ( SELECT * FROM Clientes WHERE Cidade='Bauru'); 18 de fevereiro de 2020 39 SQL - SubQueries Executando uma Seleção nos dados da tabela funcionários Pode-se escrever, na base de dados, o comando: SELECT * FROM funcionarios WHERE id_depto='A01'; Este comando 'recupera' as linhas da tabela que atendem o que está especificado depois da palavra WHERE. 18 de fevereiro de 2020 40 SQL - Criando tabela com aninhamento A C1 C2 1 A 2 B 3 C B C1 C2 1 A 1 B 2 C 5 A C C2 C5 C6 A 1A Qwe B 2A Tgb D 1B Rty E 1C Ert Projeção RC[[C2,C6]] Seleção RB[[C2="A"]] C1 C2 1 A 5 A C2 C6 A Qwe B Tgb D Rty E Ert AR – Quadro Geral das Operações Cria uma tabela a partir de UMA outra levando os valores de um ou mais CAMPOS da tabela; TODOS os Valores dos campos escolhidos são copiados; Esquema da tabela resultado é igual à lista de campos da tabela de origem da operação. Representação: R A[[Lista de Campos]] Por Exemplo: R A[[C2,C4]] Se for preciso, pode-se escrever: R A[[C2,C4]](distintos) A C1C2 C3 C4 Asd Qwe Fr4 Dfg Rfv Tgb Gt5 Edc Zxc Rty Hy6 Rfv Fgh Ert Ju7 Tgb Wsx Rty Ki8 Rfv R C2 C4 Qwe Dfg Tgb Edc Rty Rfv Ert Tgb Rty Rfv AR - SQL - Projeção Observações: A quantidade de tuplas da tabela resultado será o mesmo da tabela de origem, embora a quantidade de campos possa ser menor; O Resultado da operação pode ser uma tabela SEM TUPLAS ( o que seria o conceito de conjunto vazio). Se a tabela destino apresentar tuplas repetidas podemos complementar o comando com (distintos) R A[[C1,C4]](distintos) Na projeção é possível trocar os nomes dos campos, para isso usa-se o simbolo para indicar a troca, desse modo: R A[[C1casa, C2tel]] produz uma tabela R com campos (casa,tel). AR - SQL - Projeção Sintaxe: Select NomeDoCampo FROM TABELA; Executando na Base: SELECT id_funcionario, tx_primeiro_nome, nu_ramal FROM funcionarios; Combinado com o Create: CREATE TABLE ResProdutos AS (SELECT id_produto, tx_nome, vl_preco FROM produtos); Usando o AS para trocar nomes de campos e tabelas: CREATE TABLE ResProdutos AS (SELECT id_produto AS Prod, tx_nome AS Nome, vl_preco AS Preco FROM produtos); 18 de fevereiro de 2020 44 AR - SQL - Projeção Quadro Geral A C1 C2 1 A 2 B 3 C B C1 C2 1 A 1 B 2 C 5 A C C2 C5 C6 A 1A Qwe B 2A Tgb D 1B Rty E 1C Ert Projeção RC[[C2,C6]] Junção (natural ou fechada) R A [[ A.C2 = C.C2 ]] C Seleção RB[[C2="A"]] C1 C2 1 A 5 A C2 C6 A Qwe B Tgb D Rty E Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb AR – Quadro Geral das Operações Cria uma tabela a partir de duas outras emparelhando as tuplas e levando para o destino apenas as tuplas que obedecem a uma determinada condição; Esquema da tabela resultado é igual aos esquemas das tabelas colocados lado a lado, obedecendo, inclusive a ordem de campos. Representação: R B [[Condição]] A, por exemplo: R B [[C3=C1]] A ou R B [[B.C3=A.C1]] A A tabela resultado pode ser um conjunto vazio R C3 C4 C1 C2 Asd Edc Asd Qwe Rfv Jmh Rfv Tgb Rfv Vah Rfv Tgb A C1 C2 Asd Qwe Rfv Tgb B C3 C4 Fr4 Dfg Asd Edc Rfv Jmh Rfv Vah AR – SQL - Junção Natural (ou fechada) ‡ = AR - SQL – Junção (Fechada ou Natural) Sintaxe: SELECT * FROM TABELA1, TABELA2 WHERE TABELA1.Campo1=TABELA2.Campo1; Ou também: SELECT * FROM Tab1 INNER JOIN Tab2 ON TABELA1.Campo1=TABELA2.Campo1; Ou também: SELECT * FROM Tab1 INNER JOIN Tab2 USING (campo); Exemplos: Select * FROM departamentos, funcionarios WHERE departamentos.id_depto=funcionarios.id_depto; Select * FROM departamentos INNER JOIN funcionarios WHERE departamentos.id_depto=funcionarios.id_depto; Select * FROM departamentos INNER JOIN funcionarios USING (id_depto); 18 de fevereiro de 2020 47 AR - SQL – Junção (Fechada ou Natural) Testando a Junção... Criamos uma Tabela com dept com o comando: CREATE TABLE dept AS (SELECT * FROM departamantos WHERE iddepto IN ('A01','B01','D11','E01') Depois criamos duas novas tabelas na Base com os comandos: create table func1 as (Select * from funcionarios where iddepto='A01'); create table func2 as (Select * from funcionarios where iddepto='D11'); Note: a tabela func1 tem somente os funcionários do departamento 'A01' e func2 os funcionários do departamento 'D11'. 18 de fevereiro de 2020 48 AR – SQL - Junção (Fechada ou Natural) Podemos também fazer algumas coisas mais complexas: "quais são os nomes de gerentes e seus subalternos para os departamentos com código do gerente entre 230, 260 ou 290? " Podemos escrever: SELECT F1.ID_Funcionario, F1.tx_primeiro_nome, D1.id_depto, D1.tx_nome_depto, D1.ID_Funcionario_Gerente, F2.ID_Funcionario, F2.tx_primeiro_nome FROM funcionarios as F1, departamentos AS D1, funcionarios AS F2 WHERE F1.id_funcionario=D1.id_funcionario_gerente AND D1.id_depto=F2.id_depto AND F1.id_funcionario IN ('230','260','290'); Note que 'funcionario' aparece duas vezes na lista de tabelas mas com dois nomes distintos (f1 e f2). 18 de fevereiro de 2020 49 A C1 C2 1 A 2 B 3 C B C1 C2 1 A 1 B 2 C 5 A C C2 C5 C6 A 1A Qwe B 2A Tgb D 1B Rty E 1C Ert Projeção RC[[C2,C6]] Junção (natural) R A [[ A.C2 = C.C2 ]] C Seleção RB[[C2="A"]] Produto Cartesiano R A X CC1 C2 1 A 5 A C2 C6 A Qwe B Tgb D Rty E Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb C1 C2 C2 C5 C6 1 A A 1A Qwe 1 A B 2A Tgb 1 A D 1B Rty 1 A E 1C Ert 2 B A 1A Qwe 2 B B 2A Tgb 2 B D 1B Rty 2 B E 1C Ert 3 C A 1A Qwe 3 C B 2A Tgb 3 C D 1B Rty 3 C E 1C Ert AR – Quadro Geral das Operações Cria uma tabela a partir de duas outras colocando cada uma de todas as tuplas da Primeira tabela ao lado de todas as tuplas da Segunda. Isto se diz "emparelhamento de tuplas“; Esquema da tabela resultado é igual aos esquemas das tabelas colocados lado a lado, obedecendo, inclusive a ordem de campos. Representação: R A X B Por Exemplo: R A X B R C1 C2 C3 C4 Asd Qwe Fr4 Dfg Asd Qwe Gt5 Edc Rfv Tgb Fr4 Dfg Rfv Tgb Gt5 Edc A C1 C2 Asd Qwe Rfv Tgb B C3 C4 Fr4 Dfg Gt5 Edc AR – SQL – Produto Cartesiano Observações: A quantidade de tuplas da tabela resultado, será a quantidade de tuplas da primeira multiplicada pela quantidade de tuplas da segunda. É comutativo: A X B = B X A R C1 C2 C3 C4 Asd Qwe Fr4 Dfg Asd Qwe Gt5 Edc Rfv Tgb Fr4 Dfg Rfv Tgb Gt5 Edc A C1 C2 Asd Qwe Rfv Tgb B C3 C4 Fr4 Dfg Gt5 Edc R C1 C2 C3 C4 Asd Qwe Fr4 Dfg Rfv Tgb Fr4 Dfg Asd Qwe Gt5 Edc Rfv Tgb Gt5 Edc R C3 C4 C1 C2 Fr4 Dfg Asd Qwe Fr4 Dfg Rfv Tgb Gt5 Edc Asd Qwe Gt5 Edc Rfv Tgb A C1 C2 Asd Qwe Rfv Tgb B C3 C4 Fr4 Dfg Gt5 Edc AR – SQL – Produto Cartesiano Sintaxe: SELECT * FROM TABELA1, TABELA2; Combinado com o Create (exec. na base): CREATE TABLE possiveisturmas AS (SELECT * FROM disciplinas, professores); Experimente combinar dados de duas tabelas quaisquer (sem relacionamento). Experimente fazer um produto cartesiano de três tabelas. Na AR isso não é possível, mas a SQL permite. 18 de fevereiro de 2020 53 AR – SQL – Produto Cartesiano A C1 C2 1 A 2 B 3 C B C1 C2 1 A 1 B 2 C 5 A C C2 C5 C6 A 1A Qwe B 2A Tgb D 1B Rty E 1C Ert Projeção RC[[C2,C6]] Junção (natural) R A [[ A.C2 = C.C2 ]] C Junção Aberta pela Esquerda R A ][ A.C2 = C.C2 ]] C Seleção RB[[C2="A"]] C1 C2 1 A 5 A C2 C6 A Qwe B Tgb D Rty E Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb 3 C Nulo Nulo Nulo AR – Quadro Geral das Operações Cria uma tabela a partir de duas outras emparelhando todas as tuplas da tabela do lado esquerdo com cada tupla da tabela da direita levando para o destino as tuplas que obedecem a uma determinada condição e colocando as tuplas que não atendem ao lado de registros com valores NULOS.; Esquema da tabela resultado é igual aos esquemas das tabelas colocados lado a lado, obedecendo, inclusive a ordem de campos. Representação: R B][Condição]]A Por Exemplo: R B][C3=C1]]A ou B][B.C3=A.C1]]A R C3 C4 C1 C2 Fr4 Dfg NULO NULO Asd Edc Asd Qwe Rfv Jmh Rfv Tgb Rfv Vah Rfv Tgb A C1 C2 Asd Qwe Rfv Tgb Wse Tuj B C3 C4 Fr4 Dfg Asd Edc Rfv Jmh Rfv Vah AR – SQL – Junção Aberta pela Esquerda AR – SQL – Junção Aberta pela Esquerda Sintaxe: Select * FROM TABELA1 LEFT JOIN TABELA2 ON TABELA1.Campo1=TABELA2.Campo1 Testando... Podemos saber quais os func1 que não são gerentes de dept com o seguinte comando: SELECT F1.IDFuncionario, F1.TXPrimNome, F1.TXSobreNome, D1.iddepto, D1.TXNomedepto, D1.IDFuncGerente FROM func1 as F1 LEFT JOIN departamentos AS D1 ON F1.idfuncionario=D1.idfuncgerente WHERE D1.iddepto IS NULL; 18 de fevereiro de 2020 56 A C1 C2 1 A 2 B 3 C B C1 C2 1 A 1 B 2 C 5 A C C2 C5 C6 A 1A Qwe B 2A Tgb D 1B Rty E 1C Ert Projeção RC[[C2,C6]] Junção (natural) R A [[ A.C2 = C.C2 ]] C Junção Aberta pela Direita R A [[ A.C2 = C.C2 ][ C Junção Aberta pela EsquerdaR A ][ A.C2 = C.C2 ]] C Seleção RB[[C2="A"]] C1 C2 1 A 5 A C2 C6 A Qwe B Tgb D Rty E Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb 3 C Nulo Nulo Nulo C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb Nulo Nulo D 1B Rty Nulo Nulo E 1C Ert AR – Quadro Geral das Operações Cria uma tabela a partir de duas outras emparelhando todas as tuplas da tabela do lado direito com cada tupla da tabela da esquerda levando para o destino as tuplas que obedecem a uma determinada condição e colocando as tuplas que não atendem ao lado de registros com valores NULOS.; Esquema da tabela resultado é igual aos esquemas das tabelas colocados lado a lado, obedecendo, inclusive a ordem de campos. Representação: R B[[Condição][A Por Exemplo: R B[[C3=C1][A ou B[[B.C3=A.C1][A R C3 C4 C1 C2 Asd Edc Asd Qwe Rfv Jmh Rfv Tgb Rfv Vah Rfv Tgb NULO NULO Wse Tuj A C1 C2 Asd Qwe Rfv Tgb Wse Tuj B C3 C4 Fr4 Dfg Asd Edc Rfv Jmh Rfv Vah AR – SQL - Junção Aberta pela Direita AR – SQL - Junção Aberta pela Direita Sintaxe: Select * FROM TABELA1 RIGHT JOIN TABELA2 ON TABELA1.Campo1=TABELA2.Campo1 Testando... Podemos saber quais os dept que não tem ligação com func1 através do comando: SELECT IDFuncionario,TXPrimNome,TXIniciaisMedias, TXSobreNome,func1.IDDepto,dept.IDDepto,TXNomedepto FROM func1 RIGHT JOIN dept ON func1.iddepto=dept.iddepto; 18 de fevereiro de 2020 59 A C1 C2 1 A 2 B 3 C B C1 C2 1 A 1 B 2 C 5 A C C2 C5 C6 A 1A Qwe B 2A Tgb D 1B Rty E 1C Ert Projeção RC[[C2,C6]] Junção (natural) R A [[ A.C2 = C.C2 ]] C Junção Aberta pela Direita R A [[ A.C2 = C.C2 ][ C Junção Aberta pela Esquerda R A ][ A.C2 = C.C2 ]] C Seleção RB[[C2="A"]] Junção completa (aberta) RA][A.C2=C.C2][C C1 C2 1 A 5 A C2 C6 A Qwe B Tgb D Rty E Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb 3 C Nulo Nulo Nulo C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb Nulo Nulo D 1B Rty Nulo Nulo E 1C Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb 3 C Nulo Nulo Nulo Nulo Nulo D 1B Rty Nulo Nulo E 1C Ert AR – Quadro Geral das Operações Cria uma tabela a partir de duas outras emparelhando todas as tuplas da tabelas que obedecem a condição. Para as tuplas que não tem correspondentes são emparelhados registros com valores NULOS.; Esquema da tabela resultado é igual aos esquemas das tabelas colocados lado a lado, obedecendo, inclusive a ordem de campos. Representação: R B][Condição][A Por Exemplo: R B][C3=C1][A ou A][A.C3=B.C1][B R C3 C4 C1 C2 Fr4 Dfg NULO NULO Asd Edc Asd Qwe Rfv Jmh Rfv Tgb Rfv Vah Rfv Tgb NULO NULO Wse Tuj B C1 C2 Asd Qwe Rfv Tgb Wse Tuj A C3 C4 Fr4 Dfg Asd Edc Rfv Jmh Rfv Vah AR – SQL – Junção Completa (ou Aberta) Para executar uma junção completa em SQL podemos escrever: SELECT * FROM cidades FULL OUTER JOIN clientes ON cidades.id_cidade=clientes.id_cidade; Entretanto o MySQL NÃO implementa este comando... Para realizar a junção completa temos qe ver a operação de união. Mas então vou deixar este comando para mais tarde ou talvez propor: teste no PostgreSQL e veja se o FULL OUTER JOIN funciona. 18 de fevereiro de 2020 62 AR – SQL – Junção Completa (ou Aberta) A C1 C2 1 A 2 B 3 C B C1 C2 1 A 1 B 2 C 5 A C C2 C5 C6 A 1A Qwe B 2A Tgb D 1B Rty E 1C Ert Projeção RC[[C2,C6]] Junção (natural) R A [[ A.C2 = C.C2 ]] C Junção Aberta pela Direita R A [[ A.C2 = C.C2 ][ C Junção Aberta pela Esquerda R A ][ A.C2 = C.C2 ]] C Seleção RB[[C2="A"]] Junção completa (aberta) RA][A.C2=C.C2][C Produto Cartesiano R A X C C1 C2 1 A 5 A C2 C6 A Qwe B Tgb D Rty E Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb 3 C Nulo Nulo Nulo C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb Nulo Nulo D 1B Rty Nulo Nulo E 1C Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb 3 C Nulo Nulo Nulo Nulo Nulo D 1B Rty Nulo Nulo E 1C Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 1 A B 2A Tgb 1 A D 1B Rty 1 A E 1C Ert 2 B A 1A Qwe 2 B B 2A Tgb 2 B D 1B Rty 2 B E 1C Ert 3 C A 1A Qwe 3 C B 2A Tgb 3 C D 1B Rty 3 C E 1C Ert AR – Quadro Geral das Operações União R A B C1 C2 1 A 2 B 3 C 1 A 1 B 2 C 5 A Cria uma tabela a partir de duas outras levando as tuplas comuns e não comuns; A Operação só pode ser feita se as tabelas de origem forem União Compatíveis; O Esquema da tabela resultado é igual ao esquema de cada uma das tabelas. Representação: R A B Observações: A União é comutativa (a ordem dos fatores na operação não altera o resultado da operação); R A B = B A As Tabelas geradas por operações de Álgebra Relacional são Livres; Podemos escrever complementos aos comandos da Álgebra Relacional para tratamento de prováveis desvios do Modelo. A Operação acima poderia ser melhor escrita assim: R A B (Distintos) A C1 C2 Asd Qwe Rfv Tgb B C1 C2 Zxc Rty Fgh Ert Rfv Tgb R C1 C2 Zxc Rty Fgh Ert Rfv Tgb Asd Qwe Rfv Tgb AR – SQL – União Esta repetição é 'correta'. Na implementação as linguagens facilitam e colocam operadores que já omitem a repetição. AR – SQL – União Sintaxe: SELECT * FROM tabela1 UNION ALL (SELECT * FROM tabela2); Executando na Base: create table func1 as (Select * from funcionarios where id_depto='A01'); create table func2 as (Select * from funcionarios where id_depto='D11'); SELECT * FROM func1 UNION (SELECT * FROM func2); 18 de fevereiro de 2020 65 AR – SQL – União O comando UNION já trata a NÃO repetição de tuplas na tabela resultado. Se quisermos recuperar as linhas repetidas devemos escrever UNION ALL na operação, desta forma: Select * FROM TABELA1 UNION ALL (Select * FROM TABELA2); Testando... Select * FROM func1 UNION ALL (Select * FROM func2); 18 de fevereiro de 2020 66 Lembra da Junção Completa? Podemos 'ver' a junção aberta como uma união de uma junção aberta pela direita com uma junção aberta pela esquerda. Então em MySQL podemos escrever: SELECT * FROM tab1 LEFT JOIN tab2 ON [condição] UNION (SELECT * FROM tab1 RIGHT JOIN tab2 ON [condição]); Testando... SELECT * FROM departamentos LEFT JOIN f1 ON departamentos.id_depto=f1.id_depto UNION (SELECT * FROM departamentos RIGHT JOIN f1 ON departamentos.id_depto=f1.id_depto); 18 de fevereiro de 2020 67 AR – SQL – Junção Completa (ou Aberta) A C1 C2 1 A 2 B 3 C B C1 C2 1 A 1 B 2 C 5 A C C2 C5 C6 A 1A Qwe B 2A Tgb D 1B Rty E 1C Ert Projeção RC[[C2,C6]] Junção (natural) R A [[ A.C2 = C.C2 ]] C Junção Aberta pela Direita R A [[ A.C2 = C.C2 ][ C Junção Aberta pela Esquerda R A ][ A.C2 = C.C2 ]] C Seleção RB[[C2="A"]] Junção completa (aberta) RA][A.C2=C.C2][C Produto Cartesiano R A X C C1 C2 1 A 5 A C2 C6 A Qwe B Tgb D Rty E Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb 3 C Nulo Nulo Nulo C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb Nulo Nulo D 1B Rty Nulo Nulo E 1C Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb 3 C Nulo Nulo Nulo Nulo Nulo D 1B Rty Nulo Nulo E 1C Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 1 A B 2A Tgb 1 A D 1B Rty 1 A E 1C Ert 2 B A 1A Qwe 2 B B 2A Tgb 2 B D 1B Rty 2 B E 1C Ert 3 C A 1A Qwe 3 C B 2A Tgb 3 C D 1B Rty 3 C E 1C Ert AR – Quadro Geral das Operações Intersecção R A B C1 C2 1 A Cria uma tabela a partir de duas outras levando as tuplas que estão na primeira Tabela E TAMBÉM estão na Segunda; A Operação só pode ser feita se as tabelas de origem forem União Compatíveis; Esquema da tabela resultado é igual ao esquema de cada uma das tabelas. Representação: R A B A C1 C2 Asd Qwe Rfv Tgb B C1 C2 Zxc Rty Fgh Ert Rfv Tgb R C1 C2 Rfv Tgb AR – SQL – Intersecção Observações: A Interseção é comutativa (a ordem dos fatores na operação NÃO ALTERA o resultado da operação); R A B = B A O Resultadoda operação pode ser uma tabela SEM TUPLAS ( o que seria o conceito de conjunto vazio). AR – SQL – Intersecção Sintaxe: SELECT * FROM TAB1 INTERSECT (SELECT * FROM TAB2); ou SELECT * FROM TAB1 WHERE EXISTS (SELECT * FROM TAB2 WHERE TAB1.CAMPO1=TAB2.CAMPO1 AND TAB1.CAMPO2=TAB2.CAMPO2 AND…); Combinado com o Create: CREATE TEMPORARY TABLE resultado AS (SELECT * FROM TAB1 INTERSECT (SELECT * FROM TAB2); Nota: Alguns SGBDs seguem o padrão ANSI:1992 MAS NÃO implementam algum destes operadores. Para cada SGBD temos que estudar e ver como implementa a intersecção. 18 de fevereiro de 2020 71 AR – SQL – Intersecção AR – SQL – Intersecção Note a forma do comando que usa o WHERE EXISTS: Select * FROM TABELA1 WHERE EXISTS (SELECT * FROM TABELA2 WHERE TABELA1.Campo1=TABELA2.Campo1 AND TABELA1.Campo2=TABELA2.Campo2 AND ...... AND TABELA1.Campon=TABELA2.Campon); É mais trabalhosa pois necessita que todos os campos dos esquemas sejam declarados. Além disso são feitas n comparações de igualdade. É possível usar uma função que ‘agilize’ a comparação dos valores dos campos de cada linha… Desse modo… 18 de fevereiro de 2020 72 AR – SQL – Intersecção Desse modo... SELECT * FROM TABELA1 WHERE EXISTS (SELECT * FROM TABELA2 WHERE CONCAT(TABELA1.Campo1, TABELA1.Campo2, ... TABELA1.CampoN) = CONCAT(TABELA2.Campo1, TABELA2.Campo2 AND ... TABELA2.CampoN)); Ainda não vimos o uso de funções... Mas: CONCAT() coloca todos os valores dos campos lado-a-lado... E assim se faz somente uma comparação. 18 de fevereiro de 2020 73 AR – SQL – Intersecção Na base, podemos escrever: CREATE TABLE func1 AS (Select * from funcionarios WHERE id_depto IN ('A01','B01'); CREATE TABLE func2 AS (Select * from funcionarios WHERE id_depto IN ('A01','C01'); E a seguir… 18 de fevereiro de 2020 74 AR – SQL – Intersecção SELECT * FROM func1 WHERE EXISTS (SELECT * FROM func2 WHERE func1.id_funcionario = func2.id_funcionario AND func1.tx_primeiro_nome = func2.tx_primeiro_nome AND func1.tx_iniciais_medias = func2.tx_iniciais_medias AND func1.tx_sobre_nome = func2.tx_sobre_nome AND func1.id_depto = func2.id_depto AND func1.nu_ramal = func2.nu_ramal AND func1.dt_contratacao = func2.dt_contratacao AND func1.id_funcao = func2.id_funcao AND func1.id_nivel_educacao = func2.id_nivel_educacao AND func1.ao_sexo = func2.ao_sexo AND func1.dt_nascimento = func2.dt_nascimento AND func1.tx_resenha = func2.tx_resenha AND func1.vl_salario = func2.vl_salario AND func1.vl_bonus = func2.vl_bonus AND func1.vl_comissao = func2.vl_comissao AND func1.nu_cep = func2.nu_cep AND func1.dt_cadastro = func2.dt_cadastro) 18 de fevereiro de 2020 75 AR – SQL – Intersecção Nota: No MySQL (5.+) podemos escrever assim: SELECT * FROM func1 WHERE id_funcionario IN (SELECT id_funcionario FROM func2); PORÉM este operador (IN) permite a comparação de uma coluna necessitando que as duas tabelas tenham os campos de comparação MAS não necessariamente sejam UNIÃO COMPATÍVEIS. Isso quebra o conceito de operação intersecção da Álgebra Relacional. É possível se implementar a INTERSECÇÃO desde que se use o IN comparando TODOS os campos das tabelas, usando a função CONCAT(). 18 de fevereiro de 2020 76 A C1 C2 1 A 2 B 3 C B C1 C2 1 A 1 B 2 C 5 A C C2 C5 C6 A 1A Qwe B 2A Tgb D 1B Rty E 1C Ert Projeção RC[[C2,C6]] Junção (natural) R A [[ A.C2 = C.C2 ]] C Junção Aberta pela Direita R A [[ A.C2 = C.C2 ][ C Junção Aberta pela Esquerda R A ][ A.C2 = C.C2 ]] C Seleção RB[[C2="A"]] Junção completa (aberta) RA][A.C2=C.C2][C Produto Cartesiano R A X C C1 C2 1 A 5 A C2 C6 A Qwe B Tgb D Rty E Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb 3 C Nulo Nulo Nulo C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb Nulo Nulo D 1B Rty Nulo Nulo E 1C Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb 3 C Nulo Nulo Nulo Nulo Nulo D 1B Rty Nulo Nulo E 1C Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 1 A B 2A Tgb 1 A D 1B Rty 1 A E 1C Ert 2 B A 1A Qwe 2 B B 2A Tgb 2 B D 1B Rty 2 B E 1C Ert 3 C A 1A Qwe 3 C B 2A Tgb 3 C D 1B Rty 3 C E 1C Ert AR – Quadro Geral das Operações Subtração R A – B C1 C2 2 B 3 C Cria uma tabela a partir de duas outras levando as tuplas que estão na Primeira Tabela e que NÃO estão na Segunda; A Operação só pode ser feita se as tabelas de origem forem União Compatíveis; Esquema da tabela resultado é igual ao esquema de cada uma das tabelas. Representação: R A – B R C1 C2 Asd Qwe A C1 C2 Asd Qwe Rfv Tgb B C1 C2 Zxc Rty Fgh Ert Rfv Tgb T B - A T C1 C2 Zxc Rty Fgh Ert .·. Não comutativa AR – SQL – Subtração Asd Qwe Esta Tupla ESTÁ em A e NÃO ESTÁ em B .·. vai para R Observações: As tabelas geradas também são livres. O Resultado da operação pode ser uma tabela SEM TUPLAS ( o que seria o conceito de conjunto vazio); A C1 C2 Asd Qwe Rfv Tgb Zws Ade B C1 C2 Asd Qwe Rfv Tgb T B - A T C1 C2 AR – SQL – Subtração AR – SQL – Subtração Sintaxe: Select * FROM TABELA1 MINUS | EXCEPT (Select FROM TABELA2); Combinado com o Create: CREATE TABLE FUNC3 as (SELECT * FROM funcionarios MINUS (SELECT * FROM func1)); 18 de fevereiro de 2020 80 AR – SQL – Subtração O MySQL não implementa o operador MINUS, podemos fazer a subtração assim: SELECT * FROM TAB1 WHERE CONCAT(todos os campos de TAB1) NOT IN (SELECT CONCAT(todos os campos de TAB) FROM TAB2); Ou podemos usar: SELECT func1.* FROM func1 LEFT JOIN func2 USING (id_funcionario) WHERE func2.id_funcionario IS NULL; Ou ainda usando o operador IN (ou NOT IN) SELECT * FROM funcionarios WHERE idfuncionario NOT IN (select idfuncionario FROM func1); 18 de fevereiro de 2020 81 AR – SQL – Subtração Note bem: parece que com o NOT IN as tabelas TAB1 e TAB2 NÃO precisam ser UNIÃO COMPATÍVEL para fazer o MINUS, basta envolver alguns campos (PK de preferencia) no operador NOT IN. Agora Interprete este comando: Select * from Funcionarios WHERE idfuncionario NOT IN (SELECT T1.idfuncionario FROM (SELECT * from func1 UNION (SELECT * FROM FUNC2)) AS T1); 18 de fevereiro de 2020 82 A C1 C2 1 A 2 B 3 C B C1 C2 1 A 1 B 2 C 5 A C C2 C5 C6 A 1A Qwe B 2A Tgb D 1B Rty E 1C Ert Projeção RC[[C2,C6]] Junção (natural) R A [[ A.C2 = C.C2 ]] C Junção Aberta pela Direita R A [[ A.C2 = C.C2 ][ C Junção Aberta pela Esquerda R A ][ A.C2 = C.C2 ]] C Seleção RB[[C2="A"]] Junção completa (aberta) RA][A.C2=C.C2][C Produto Cartesiano R A X C C1 C2 1 A 5 A C2 C6 A Qwe B Tgb D Rty E Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb 3 C Nulo Nulo Nulo C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb Nulo Nulo D 1B Rty Nulo Nulo E 1C Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 2 B B 2A Tgb 3 C Nulo Nulo Nulo Nulo Nulo D 1B Rty Nulo Nulo E 1C Ert C1 C2 C2 C5 C6 1 A A 1A Qwe 1 A B 2A Tgb 1 A D 1B Rty 1 A E 1C Ert 2 B A 1A Qwe 2 B B 2A Tgb 2 B D 1B Rty 2 B E 1C Ert 3 C A 1A Qwe 3 C B 2A Tgb 3 C D 1B Rty 3 C E 1C Ert AR – Quadro Geral das Operações União R A B Subtração R A – B Intersecção R A B C1 C2 1 A C1 C2 2 B 3 C C1 C2 1 A 2 B 3 C 1 A 1 B 2 C 5 A Cria uma tabela a partir de duas outras (A e B) indicando em uma das tabelas (A) qual tem tuplas que apresentam toda a seqüência de tuplas da outra tabela (B). Representação: Não tem representação em Álgebra Relacional; É representado por uma seqüência de operações de Álgebra Relacional. Observações: A tabela resultado pode ser um conjunto vazio (NÃO conter NENHUMA Tupla). AR – SQL – Procedimento da Divisão Quais são os fornecedores (todos) que fornecem TODAS as peças? Que bom se pudéssemos especificar um comando assim: Divida F, PF, P ... MAS isso é implementado em poucos SGBDs... Por isso fazemos uma seqüência de comandos que executam a divisão. Forn(PF) CP CF A F1 A F2 B F2 BF3 B F1 Peças (P) CP A B Fornecedores (F) CF F1 F2 F3 AR – SQL – Procedimento da Divisão Proposta de operações para divisão: CP CF A F1 B F1 A F2 B F2 A F3 B F3 CP CF A F3 CF F3 CF F1 F2 CP CF A F1 A F2 B F2 B F3 B F1 CP CF A F1 B F1 A F2 B F2 A F3 B F3 S P X F R S – PF T R[[CF]](Dist) W F – T T R[[CP]](Dist) CP A W P – T CP B Esta seqüência responde a qual pergunta? Possível Real NÃO Real AR – SQL – Procedimento da Divisão Dadas as tabelas abaixo: Responda: Quais são os nomes dos professores que ministram todas as disciplinas? Professores CdProf Nome A José .... ..... Disciplinas CdDisc Nome Tipo F01 Banco de Dados I A F11 Português II B ...... ...... ..... Atribuídas CdProf CdDisc A F01 C F08 ..... ..... AR – SQL – Procedimento da Divisão O uso de funções pode ser feito em qualquer operação da Álgebra Relacional. As funções podem ser usadas em condições de comparação. As funções podem ser divididas em dois grupos básicos: Funções de ordenação ou agrupamento Delimitadas pelo símbolo {} e aparecendo no final da expressão da álgebra relacional Funções de processamento de valor de campo. Delimitados por () e podendo surgir em qualquer parte da origem de uma expressão da álgebra relacional AR – Uso de Função em expressões de AR Exemplo: R A[[SOMA(C1)Total,C2]] {agrupando(C2)} {ordem por C2} Note que esta operação é uma projeção Agora tente processar SEM Ordenar MAS com Agrupamento A C1 C2 10 BC 15 DC 20 DC 15 BC 10 AB 25 DC 15 AB 20 BC R Total C2 25 AB 45 BC 60 DC AR – Uso de Função em expressões de AR SQL – Executando Funções Trabalhando com Funções De modo geral as funções são usadas no comando SELECT, realizando o processamento enquanto "lê" as tuplas de uma tabela. Pode ser usada também com comandos de atualização (UPDATE ou INSERT) informando valor para os campos, ou no comando DELETE (usando para determinar quais tuplas serão deletadas) 18 de fevereiro de 2020 90 SQL – Executando Funções Trabalhando com Funções As funções retornam um único valor cada vez que são chamados. A SQL padrão fornece funções divididas em dois grupos: Escalares Funções que manipulam tipos de dados data e hora, strings, e números, bem como recuperar informações do sistema, como o usuário atual ou o nome de login, etc. Aritméticas Funções para processamentos matemáticos. Por exemplo: ABS, PI, SQRT, COS, POWER e TAN, entre outras. Usamos assim: SELECT SQRT(16); 18 de fevereiro de 2020 91 SQL – Executando Funções Trabalhando com Funções Exemplificando... Queremos saber quantos funcionarios de cada departamento ganham mais do que 25000 anualmente: SELECT id_depto, COUNT(id_funcionario) as "QtdFunc" From funcionarios WHERE vl_salario>25000 GROUP BY id_depto ORDER BY id_depto Escrevendo outros exemplos Teste alternativas ao comandos apresentados. 18 de fevereiro de 2020 92 Exemplo: R A[[SOMA(C1)Total,CONTA(C2)Quant,C2]] {agrupando(C2) [[SOMA(C1)>=50]]} {ordem por C2} Note que esta operação é uma projeção. Esperamos isso: Mas... A restrição no agrupamento... Só vai mostrar as tuplas com o RESULTADO da função obedecendo a uma seleção e o resultado... A C1 C2 10 BC 15 DC 20 DC 15 BC 10 AB 25 DC 15 AB 20 BC R Total Quant C2 60 3 DC AR – Uso de Função em expressões de AR R Total Quant C2 25 2 AB 45 3 BC 60 3 DC Exemplo: R A[[SOMA(C1)Total, CONTA(C2)Qtd, MEDIA(C1)Med, C2]] {agrupando(C2)} {ordem por C2} A C1 C2 10 BC 15 DC 20 DC 15 BC 10 AB 25 DC 15 AB 20 BC R Total Quant Med C2 25 2 12.5 AB 45 3 15 BC 60 3 20 DC AR – Uso de Função em expressões de AR SQL – Executando Funções Trabalhando com Funções Exemplificando... Queremos saber o total médio gasto em vendas para cada clientes. Podemos escrever: SELECT id_fornecedor, AVG(vl_total_nota_venda) FROM notas_fiscais_compras GROUP BY id_fornecedor ORDER BY id_fornecedor Se quisermos o valor do AVG arredondado com duas casas decimais usamos a função ROUND (do MySQL e não no ANSI:92) 18 de fevereiro de 2020 95 SQL – Executando Funções Trabalhando com Funções SELECT id_fornecedor, ROUND(AVG(vl_total_nota_venda),2) FROM notas_fiscais_compras GROUP BY id_fornecedor ORDER BY id_fornecedor Escrevendo outros exemplos Teste alternativas ao comandos apresentados. 18 de fevereiro de 2020 96 SQL – Executando Funções Trabalhando com Funções E o que será que este comando faz? SELECT id_fornecedor AS Fornecedor, SUM(vl_total_nota_venda) AS Total, COUNT(id_nota_fiscal_compra) AS QtdDeNotas, AVG(vl_total_nota_venda) AS VlMédio, MAX(vl_total_nota_venda) AS VlMaximo, MIN(vl_total_nota_venda) AS VlMinimo FROM notas_fiscais_compras GROUP BY id_fornecedor ORDER BY id_fornecedor Calcula o total de valor em notas de cada Fornecedor, contas quantas notas foram emitidas e o Valor médio gasto por nota. Que conclusões se pode obter com esta análise? 18 de fevereiro de 2020 97 SQL – Atualização: INSERT Sintaxe: INSERT INTO <Tab> [ (Campo1, Campo2, ...) ] VALUES ('valor docampo1', 'valordocampo 2', ...) [,('valordocampo 1, 'valordocampo 2', ...), ('valordocampo 1', 'valordocampo 2', ...), ('valordocampo 1', 'valordocampo 2', ...)]; Exemplo: INSERT INTO DEPARTAMENTOS VALUES ( 'A11','CONTROLE DE CUSTOS1','40','A01','-'), ( 'A12','CONTROLE DE CUSTOS2','120','A01','-'); 18 de fevereiro de 2020 98 SQL – Atualização: INSERT Sintaxe: INSERT INTO <Tab> SET Campo1='valordocampo1', Campo2=valordocampo2 , … ; Exemplo: INSERT INTO departamentos SET DEPTNO='A02', DEPTNAME='Prova que dá certo', MGRNO='000090', ADMRDEPT='A01', LOCATION='-' ; 18 de fevereiro de 2020 99 SQL – Atualização: INSERT com sub-queries O Comando de INSERÇÃO com sub-query Permite executar um processamento sobre uma tabela e colocar o resultado como tuplas em outra tabela. Sintaxe: INSERT INTO <Tab> [ (Campo1, Campo2, ...) ] (SELECT [* | Campo1, Campo2, …, Campo n] FROM <tab2> [WHERE <condição>]); Exemplo: INSERT INTO dept (SELECT * FROM departamentos WHERE id_depto IN ('A11','A12')); 18 de fevereiro de 2020 100 SLQ – Atualização: DELETE Sintaxe: DELETE FROM <Tab> [WHERE <condição>]; Exemplo: DELETE FROM ClienteBOM WHERE ClienteBOM.Limite < 1500; 18 de fevereiro de 2020 101 SQL – Atualização: DELETE de tabelas complexas Teste: Se quisermos identificar e deletar tuplas resultantes de uma junção? Quero deletar todos as tuplas de uma tabela (d1) que tem relacionamento com outra (d2)... Vamos contruir duas tabelas d1 e d2 derivadas de funcionários Podemos escrever DELETE d1 FROM d1, d2 WHERE d1.id_funcionario = d2.id_funcionario; 18 de fevereiro de 2020 102 SQL – Atualização: UPDATE Sintaxe: UPDATE TABELA SET Atrib1=Valor1, Atrib2 = Valor2, Atrib3= Valor3 [ WHERE condição] ; Exemplo: UPDATE Alunos SET Nome="José Antonio da Silva" WHERE Alunos.Matricula ="9871234"; 18 de fevereiro de 2020 103 SQL – Atualização: UPDATE sobre múltiplas tabelas E se... Temos uma tabela com notas fiscais e outra com seus respectivos itens e PRECISAMOS recalcular o valor total das notas somando os respectivos valores totais de cada item correspondente, como fazer? Em outras palavras o UPDATE devera atualizar uma tabela com o resultado de processamento de função de outras tabelas, como fazer? O princípio do raciocínio é este: CRIE SUB-QUERIES COM O MAIS BAIXO GRAU DE GRANULARIDADE DEFININDO: GRANULARIDADE nível de detalhamento dos dados apresentados em um banco de dados. EM GERAL aparecem em tabelas de desmembramentos N:M, pois quase sempre tem a estrutura de Produtos Cartesianos (que mostram as possibilidades de combinação de Dados). Na situação acima o mais baixo nível de granularidade está em itens das notas fiscais... Portanto, devemos processaras totalizações nos itens de notas e gerar os dados que devem ser 'levados' para as notas respectivas. 18 de fevereiro de 2020 104 SQL – Atualização: UPDATE sobre múltiplas tabelas Então podemos escrever: select id_nota_fiscal_compra, SUM(qt_vendida*vl_unitario_venda) as vtot FROM notas_fiscais_compras_itens GROUP BY id_nota_fiscal_compra ORDER BY id_nota_fiscal_compra para totalizar os itens das notas (note o que a projeção gera) E para atualizar a tabela com as notas fiscais... UPDATE notas_fiscais_compras, ((select id_nota_fiscal_compra, SUM(qt_vendida*vl_unitario_venda) as vtot FROM notas_fiscais_compras_itens GROUP BY id_nota_fiscal_compra ORDER BY id_nota_fiscal_compra ) AS vtotitem) SET vl_total_nota_venda=vtot WHERE notas_fiscais_compras.id_nota_fiscal_compra = vtotitem.id_nota_fiscal_compra; 18 de fevereiro de 2020 105 SQL – Alterando a Estrutura de uma tabela Comando: Alter Table Pode ser usado com 3 propósitos: Alterar a estrutura de uma tabela Definir uma chave primária (Primary Key ou PK) Definir uma chave estrangeira – Foreign Key ou FK; (caracterizando o comportamento da FK em função da PK correspondente de outra tabela). 18 de fevereiro de 2020 106 SQL – Alterando a Estrutura de uma tabela Alter Table Sintaxe: ALTER TABLE <TAB> < ADD | DROP > <TIPO> [TAM(n)] [BEFORE | AFTER <campo>] Exemplo: ALTER TABLE funcoes ADD teste INT(3) NULL AFTER IDFuncao 18 de fevereiro de 2020 107 SQL – Alterando a Estrutura de uma tabela Alter Table Trocando o nome de um campo, a posição na tabela e outras características: ALTER TABLE <tab> CHANGE COLUMN <NomeAntes> <NomeDepois> <Definições do campo – Tipo+Tam...> <Posição: FIRST| AFTER<campo>|before<campo>| LAST>; Exemplo: ALTER TABLE enfermeiros CHANGE COLUMN numcasaenf numcasaenf INT(5) NOT NULL COLLATE 'latin1_swedish_ci' AFTER endenf, CHANGE COLUMN foneenf foneenf CHAR(12) NOT NULL COLLATE 'utf8_bin' AFTER cidadeenf; 18 de fevereiro de 2020 108 SQL – Alterando a Estrutura de uma tabela Alter Table Trocando a posição de um campo de uma tabela ALTER TABLE funcionarios MODIFY COLUMN tx_sobre_nome varchar(15) COLLATE utf8_bin NOT NULL AFTER tx_iniciais_medias; 18 de fevereiro de 2020 109 SQL – Alterando a Estrutura de uma tabela Alter Table Criando (excluindo) uma Chave Primária Sintaxe: ALTER TABLE <TAB> [ADD | DROP] PRIMARY KEY (<nome>) Exemplo: ALTER TABLE project ADD PRIMARY KEY(PROJNO) 18 de fevereiro de 2020 110 SQL – Alterando a Estrutura de uma tabela O ANSI:1992 tem comando que permite criar Chaves Estrangeiras e indicar ao SGBD que faça o controle da Integridade referencial. Lembrando da 2ª Regra de Integridade do Mod. Relacional: A Chave Estrangeira pode assumir dois valores: SER NULO Um dos valores da Série de Valores onde é Chave Primária. Sendo assim considera-se dois momentos críticos a remoção de uma linha de uma tabela que contém uma chave primária e/ou a alteração de um valor de uma chave primária. 18 de fevereiro de 2020 111 SQL – Alterando a Estrutura de uma tabela Alter Table: ALTER TABLE <TAB> ADD FOREIGN KEY ( <CampoDaTab> ) REFERENCES <TAB da CP> (<CAMPO CP>) ON DELETE [cascade | set null | no action | restrict] ON UPDATE [cascade | set null | no action | restrict], [ADD FOREIGN KEY ( <CampoDaTab> ) REFERENCES <TAB da CP> (<CAMPO CP>) …..]; 18 de fevereiro de 2020 112 SQL – Alterando a Estrutura de uma tabela Alterando a estrutura de uma tabela ALTER TABLE <TAB> ADD FOREIGN KEY ( <CampoDaTab> ) REFERENCES <TAB da CP> (<CAMPO CP>) ON DELETE [cascade |set null | no action | restrict] ON UPDATE [cascade |set null | no action | restrict]; cascade – a ação sofrida na CP é repetida para as linhas da CE set null – muda o valor da CE para NULO se a CP for excluída no action – não faz nada com o valor da CE restrict – não permite a alteração na linha onde está a CP 18 de fevereiro de 2020 113 SQL – Alterando a Estrutura de uma tabela Alterando a estrutura de uma tabela Pré-requisito para montar uma CE. Os campos das tabelas envolvidas devem ter índices associados às Chaves Primárias e Estrangeiras. Atividade: Criem as CPs das tabelas da base exemplo_SQL Criem os índices dos campos correspondentes à CE Criem a integridade referencial mantida no BD com o comando alter table (ou usem o PHPMyADMIN). 18 de fevereiro de 2020 114 SQL – Trabalhando com Junções SELECT * FROM funcionarios, departamentos WHERE funcionarios.id_depto=departamentos.id_depto CRIE UMA TABELA dept A PARTIR DE departamentos EXCLUA METADE DE SUAS LINHAS REPITA O PRIMEIRO COMANDO COM dept NO LUGAR DE departamentos. AGORA ESCREVA: SELECT * FROM funcionarios LEFT JOIN dept ON funcionarios.id_depto=dept.id_depto ANALISE O COMPORTAMENTO DO LEFT JOIN 18 de fevereiro de 2020 115 SQL – Trabalhando com Junções Teste: SELECT projetos.*,tarefas.* FROM tarefas, tarefas_projetos, projetos WHERE tarefas.id_tarefa= tarefas_projetos.id_tarefa AND tarefas_projetos.id_projeto=projetos.id_projeto Observe que os campos da tabela tarefas_projetos NÃO são projetados Teste: SELECT projetos.*,tarefas.* FROM tarefas, tarefas_projetos, projetos WHERE tarefas.id_tarefa= tarefas_projetos.id_tarefa AND tarefas_projetos.id_projeto=projetos.id_projeto ORDER BY projetos. id_projeto ASC 18 de fevereiro de 2020 116 SQL – Trabalhando com Junções Teste: SELECT projetos.*,COUNT(tarefas_projetos.id_tarefa) AS Num_Tarefas FROM projetos, tarefas_projetos WHERE tarefas_projetos.id_projeto=projetos.id_projeto GROUP BY projetos.id_projeto ORDER BY projetos.id_projeto asc 18 de fevereiro de 2020 117 SQL – Trabalhando com Junções Teste (um relacionamento reflexivo): SELECT dsup.id_depto as id_depsup, dsup.tx_nome_depto as tx_nomsup, dsup. id_funcionario_gerente as gerente, dsub.id_depto as id_depsub, dsub.tx_nome_depto as tx_nomsub FROM departamentos as dsup, departamentos as dsub WHERE dsup.id_depto = dsub.id_depto_superior Responde: Quais são os departamentos subalternos de cada departamento superior? 18 de fevereiro de 2020 118 SQL – Trabalhando com Junções Teste: SELECT f1.id_funcionario as id_chefe, f1.tx_primeiro_nome as tx_prinome_chefe, f1.nu_ramal as nu_ramal_chefe, d1.id_depto, d1.tx_nome_depto, f2.id_funcionario, f2.tx_primeiro_nome, f2.nu_ramal FROM funcionarios as f1, departamentos as d1, funcionarios as f2 WHERE f1.id_funcionario = d1.id_funcionario_gerente AND d1.id_depto=f2.id_depto Responde: Quais são os funcionários de cada departamento e seu respectivo gerente? 18 de fevereiro de 2020 119 Dadas as tabelas abaixo: Para responder:Quais são as disciplinas do professor “Cesar” escrevemos: Seleção: A professores[[nome=“Cesar”]] Junção: B atribuidas[[atribuidas.cdprof=A.cdprof]]A Junção: C disciplinas[[disciplinas.cddisc=B.cddisc]]B Projeção: C[[disciplinas.nome]] Ou... Professores CdProf Nome A José .... ..... Disciplinas CdDisc Nome Tipo F01 Banco de Dados I A F11 Português II B ...... ...... ..... Atribuídas CdProf CdDisc A F01 C F08 ..... ..... AR – SQL – Encadeamentos de Comandos Dadas as tabelas abaixo: (((Professores[[nome=“Cesar”]]A) [[A.cdprof=atribuidas.cdprof]]AtribuidasB) [[B.cddisc=disciplinas.cddisc]]DisciplinasC) [[disciplinas.nome]] (Disciplinas[[disciplinas.cddisc=B.cddisc]] (Atribuidas[[atribuidas.cdprof=A.cdprof]] (Professores[[nome=“Cesar”]]A)B)C) [[disciplinas.nome]] Para escrever o SQL equivalente devemos começar no nível mais interno dos ninhos... Tente! Professores CdProf Nome A José .... ..... Disciplinas CdDisc Nome Tipo F01 Banco de Dados I A F11 Português II B ...... ...... ..... Atribuídas CdProf CdDisc A F01 C F08 ..... ..... AR – SQL – Encadeamentos de Comandos Exercitando Comandos SQL Um Exemplo de Aplicação NomePrograma CodigoEntity Pfolha01 0110100 ProgramaEntidade (PE) CodEntidade NomedaEntidade 0110100Funcionários Entidades (E) CdPrograma NomedoPrograma pFolha01 Cadastro de Funcionários Programas (P) Para estas tabelas pergunta-se: a) Quais são os nomes dos programas que acessam a entidade 'FUNCIONARIOS' 18 de fevereiro de 2020 122 Char(7) Char(8) Varchar(90) Exercitando Comandos SQL Um Exemplo de Aplicação Para estas tabelas pergunta-se: a) Quais são os nomes dos programas que acessam a entidade 'FUNCIONARIOS' Resolvendo: Seleção T E [NomeDaEntidade='Funcionários'] Junção R T [CodEntidade=CodigoEntity]PE Junção S R [NomePrograma=cdPrograma]P Projeção F S [NomeDoPrograma] 18 de fevereiro de 2020 123 Exercitando Comandos SQL Um Exemplo de Aplicação Em SQL teremos: CREATE TABLE T AS (SELECT * FROM E WHERE NomeDaEntidade='Funcionários') CREATE TABLE R AS (SELECT * FROM T,PE WHERE T.CodEntidade=PE.CodigoEntity) CREATE TABLE S AS (SELECT * FROM R,P WHERE R.NomePrograma=P.cdPrograma) CREATE TABLE F AS (SELECT NomeDoPrograma FROM S) DROP TABLE T, R, S 18 de fevereiro de 2020 124 Dadas as tabelas abaixo responda à pergunta: Quais os dias que os clientes com nome José estacionaram um de seus carros? Clientes CdCli Nome A José Paradas CdCarro DtEstac F1 09/10/2010 F1 10/10/2010 Carros CdCli CdCarro Marca A F1 Gol Junção Junção Seleção Projeção AR – SQL – Resolvendo uma Recuperação de Informação Dadas as tabelas abaixo responda à pergunta: Quais os dias que os clientes com nome José estacionaram um de seus carros? Podemos propor a seguinte seqüência de operações: R Clientes[[Nome="José"]] – recupero 2 tuplas Tenho agora que colocar estas tuplas ao lados dos seus respectivos carros (faço uma JUNÇÃO) CdCli Nome A José D José AR – SQL – Resolvendo uma Recuperação de Informação Dadas as tabelas abaixo responda à pergunta: Quais os dias que os clientes com nome José estacionaram um de seus carros? Podemos propor a seguinte seqüência de operações: R Clientes[[Nome="José"]] – recupero 2 tuplas S R[[cdCli=cdCli]]Carros CdCli Nome A José D José CdCli CdCarro Marca A F1 Gol A F3 Corsa D F5 Fox A F6 Corsa B F2 Celta C F4 Gol CdCli Nome CdCli CdCarro Marca A José A F1 Gol A José A F3 Corsa D José D F5 Fox A José A F6 Corsa AR – SQL – Resolvendo uma Recuperação de Informação Dadas as tabelas abaixo responda à pergunta: Quais os dias que os clientes com nome José estacionaram um de seus carros? Podemos propor a seguinte seqüência de operações: R Clientes[[Nome="José"]] – recupero 2 tuplas S R[[cdCli=cdCli]]Carros T S[[cdCarro=cdCarro]]Estacionadas CdCli Nome CdCli CdCarro Marca A José A F1 Gol A José A F3 Corsa D José D F5 Fox A José A F6 Corsa CdCarro DtEstac F1 09/10/2010 F1 10/10/2010 F2 09/10/2010 F2 10/10/2010 F2 11/10/2010 F3 09/10/2010 F3 10/10/2010 F5 11/10/2010 F5 09/10/2010 F4 10/10/2010 F4 10/10/2010 F3 11/10/2010 F4 11/10/2010 AR – SQL – Resolvendo uma Recuperação de Informação Dadas as tabelas abaixo responda à pergunta: Quais os dias que os clientes com nome José estacionaram um de seus carros? Podemos propor a seguinte seqüência de operações: R Clientes[[Nome="José"]] – recupero 2 tuplas S R[[cdCli=cdCli]]Carros T S[[cdCarro=cdCarro]]Estacionadas CdCli Nome CdCli cdCarro Marca CdCarro DtEstac A José A F1 Gol F1 09/10/2010 A José A F1 Gol F1 10/10/2010 A José A F3 Corsa F3 09/10/2010 A José A F3 Corsa F3 10/10/2010 D José D F5 Fox F5 11/10/2010 D José D F5 Fox F5 09/10/2010 A José A F3 Corsa F3 11/10/2010 AR – SQL – Resolvendo uma Recuperação de Informação Dadas as tabelas abaixo responda à pergunta: Quais os dias que os clientes com nome José estacionaram um de seus carros? Podemos propor a seguinte seqüência de operações: R Clientes [[Nome="José"]] S R [[cdCli=cdCli]]Carros T S [[cdCarro=cdCarro]]Estacionadas F T [[dtEstac]] (distintos) Escreva os comandos em SQL que traduzem esta sequencia de AR e grave em um arquivo com nome ARSQL1-SeuNome.txt. Envie para prof.joao.hypolito@gmail.com. Não se esqueça de escrever corretamente o assunto da mensagem. Use "_" nos espaços do SeuNome. CdCli NomeCdClicdCarroMarcaCdCarro DtEstac A José A F1 Gol F1 09/10/2010 A José A F1 Gol F1 10/10/2010 A José A F3 Corsa F3 09/10/2010 A José A F3 Corsa F3 10/10/2010 D José D F5 Fox F5 11/10/2010 D José D F5 Fox F5 09/10/2010 A José A F3 Corsa F3 11/10/2010 DtEstac 09/10/2010 10/10/2010 11/10/2010 AR – SQL – Resolvendo uma Recuperação de Informação Dadas as tabelas abaixo responda à pergunta: Quais as marcas dos carros cujos os donos tenham nome “Jose” e que não fizeram serviço de "pintura" no mês de “Maio de 2010”? Escreva as expressões de AR e traduza para SQL. Grave em um arquivo com nome ARSQL2-SeuNome.txt e envie para prof.joao.hypolito@gmail.com Clientes CdCli Nome A José B João C Ana D José E Fábio … … Carros CdCli CdCarro Marca A F1 Gol B F2 Celta A F3 Corsa C F4 Gol D F5 Fox A F6 Corsa … … … Aplicação CdCarro CdSer DtServiço F1 1 09/10/2010 F1 5 10/10/2010 F2 2 09/10/2010 F2 3 10/10/2010 F2 4 11/10/2010 F3 1 09/10/2010 F3 4 10/10/2010 F5 3 11/10/2010 F5 4 09/10/2010 F4 2 10/10/2010 F4 3 10/10/2010 F3 5 11/10/2010 F4 1 11/10/2010 … … AR – SQL – A Necessidade de Processamento Serviços CdSer Nome 1 Pintura 2 Suspensão 3 Motor 4 Elétrica 5 Painel e revestimento … … Seleção SeleçãoProjeção Junção Junção Junção Seleção Estacionadas CdCarro DtEstac F1 09/10/2010 F1 10/10/2010 F2 09/10/2010 … ... Dadas as tabelas abaixo Escreva os comandos de AR e SQL que respondam as seguintes perguntas: Quais os nomes dos donos de carros e as marcas dos carros que estacionaram entre 9/10 e 10/10? Quais foram os dias de estacionadas de todos os carros exceto os da marca 'Gol'? Quais são os registros em Estacionadas que indicam carros que foram excluídos da tabela Carros? Escreva as respostas em um Arquivo texto com nome ARSQL3-SeuNome.txt e envie para prof.joao.hypolito@gmail.com Escreva os espaços com '_' no SeuNome. Álgebra Relacional – Resolvendo uma Recuperação de Informação Clientes CdCli Nome A José Paradas CdCarro DtEstac F1 09/10/2010 F1 10/10/2010 Carros CdCli CdCarro Marca A F1 Gol Dadas as tabelas abaixo responda à pergunta apresentadas a seguir: 1. Quais são os nomes dos professores e os nomes das disciplinas atribuídas aos professores que ministram disciplinas do tipo "Tecnologia da Inf."? 2. Qual é a quantidade de disciplina atribuídas a cada professor? 3. Quais são os nomes dos professores que ministram todas disciplinas? 4. Qual é a média da quantidade de disciplinas atribuídas aos professores para cada tipo de disciplina? O que pode representar esta média (interprete o resultado) 5. Quantas disciplinas são atribuídas para cada professor que ministra disciplinas do tipo "Matemáticas"? 6. Quais são, em ordem crescente, as disciplinas com mais professores associados? 7. Sendo que qualquer professor só pode ministrar disciplinas de um tipo, indique quais são as disciplinas (código e nome) que ainda podem ser atribuídas ao professor com nome "ANA"? Escreva as Perguntas e as Respostas em um arquivo com nome ARSQL4-SeuNome .TXT e envie para prof.joao.hypolito@gmail.com Professores CdProf Nome A José .... ..... Disciplinas CdDisc NomeDisc Tipo F01 Banco de Dados I A ...... ...... ..... Atribuídas CdProf CdDisc D F05 ..... ..... AR – SQL – Exercitando a AR-SQL TipoDisciplinas CdTipo NomeTipoDisc Tecnologia da Inf. ...... ...... Dadas as tabelas abaixo responda à pergunta apresentadas a seguir: Passageiros CdPass NomePass A José B João C Ana D José E Fábio .... ..... Viagens CdViagem CdRota DtViagem V01 2 2011-11-19 V02 3 2011-11-19 V03 4 2011-11-19 V04 3 2011-11-19 V05 2 2011-11-20 V06 3 2011-11-20 V07 4 2011-11-20 ...... ..... Passagens CdPass CdViagem Poltrona A V01 15 B V01 16 A V01 17 C V01 20 D V01 22 D V02 1 B V02 3 E V02 5 B V02 6 C V02 7 .......... Cidades CdCidade NomeCidade 1 São Paulo 2 Rio de Janeiro 3 Florianópolis 4 Belo Horizonte 5 Curitiba .... ..... Rotas CdRota CdOrigem CdDestino 1 1 2 2 1 3 3 2 4 4 2 3 5 1 2 .... ... ... AR – SQL – Exercitando a AR e SQL(2) 1. Quais os nomes dos Passageiros e as datas de viagens que passaram pela cidade de "São Paulo" (como origem ou destino)? 2. Qual é a quantidade de assentos de cada viagem feita no mês de "Outubro de 2011" (Outubro tem 31 dias)? 3. Quais são os nomes dos Passageiros e a quantidade de passagens que cada passageiro comprou no mês de "Novembro de 2011"? 4. Qual é a percentagem de aproveitamento dos assentos de cada viagem feita em Outubro de 2011? Considere que a quantidade máxima de assentos por viagem seja de 40 assentos. 5. Mostre a lista de Cidades e a quantidade de viagens realizadas de/para cada cidade durante o ano de 2011. 6. Sendo a cidade de origem "São Paulo", mostre quantidade de passageiros por viagem realizada em ordem crescente de quantidade de passageiros e somente as viagens com mais de 10 passageiros. 7. Mostre a lista de cidades que não tiveram nenhuma viagem partindo ou chegando nela durante o mês de Outubro de 2011. Para CADA questão crie um arquivo TEXTO com as expressões de AR e os respectivos comandos SQL. Grave o arquivo com nome ARSQL5-SeuNome.txt e envie para prof.joao.hypolito@gmail.com AR – SQL – Exercitando a AR e SQL(2) Lembretes Usamos os comandos SQL para: Recuperar dados das linhas de tabelas Recuperar dados de colunas específicas Mas também podemos... Processar dados que estão armazenados em tabelas e depois recuperá-los. Processar dados de uma tabela e gravá-los em outra tabela. 18 de fevereiro de 2020 148 Conclusão A SQL é hoje uma linguagem de comunicação padronizada entre os bancos de dados, dado a sua divulgação e amplitude mercadológica; Muitos gerenciadores de Banco de Dados adotam a SQL como acessório para sua linguagem nativa já prevendo a interligação de vários gerenciadores e Base de Dados diferentes. 18 de fevereiro de 2020 149
Compartilhar