Baixe o app para aproveitar ainda mais
Prévia do material em texto
Explorando a Linguagem de Consulta a Banco de Dados SQL – Structured Query Language Formação de Banco de Dados Relacional [1] Juliana Mascarenhas Tech Education Specialist DIO / Owner @Simplificandoredes e @SimplificandoProgramação Mestre em modelagem computacional | Cientista de dados @in/juliana-mascarenhas-ds/ Objetivo Geral [2] Criação do projeto Lógico a partir do esquema conceitual, para posteriormente utilizar a linguagem SQL para criação do Banco de Dados relacionado. Dessa forma, serão apresentados o mapeamento ER/Relacional, assim como a estrutura e comandos da linguagem de consulta a banco de dados SQL. Pensamento Computacional Modelagem de dados Modelo Entidade Relacionamento Pré-requisitos [3] Percurso Etapa 1 Modelo Relacional e Mapeamento ER/Relacional Etapa 2 Primeitos Passos com SQL Etapa 3 Explorando Queries com SQL [4] Etapa 4 Agrupando Regsitros e Tabelas com SQL // Explorando a Linguagem de Consulta a Banco de Dados SQL Etapa 1 Modelo Relacional e Mapemamento ER/Relacinal [5] Modelo Relacional [6] Explorando modelo de dados relacional Mapeamento ER/Relacinal Hands on: Estudo de Caso DESAFIO Mapemaneto ER/Relacional [7] Algoritmo de mapeamento Mapeando Construtores do modelo EER Especialização e Generalização Multipla Herança Categorias Entidades fortes e fracas Relacionamento binário Atributos multivalorados Relacionamentos N-ários Processo [8] O que vai ter? Como estruturar? Como implementar? Projeto Conceitual Projeto Lógico Projeto Físico Processo [9] O que vai ter? Como estruturar? Como implementar? Projeto Conceitual Projeto Lógico Projeto Físico Conceitos formais do Modelo Relacional [10] [11] Conceitos Modelo Relacional Tabelas X Arquivos Entidade Coleção de Relação [12] Conceitos Modelo Relacional Tabelas X Arquivos Entidade Coleção de Relação Flat Files Classe Objeto [13] Conceitos Tupla Atributo Relação Domínio [14] Conceitos Valores atômicos Grupos: especificando nomes Domínio Contato de Usuários ID Social Local Brasil Nomes Nota de Alunos [15] Conceitos Esquema Relacional Ai: atributo D: domínio R(A1,A2,A3, ..., An) Descrição de uma relação Degree [16] Conceitos Esquema Relacional Ai: atributo D: domínio Descrição de uma relação Definição - R r: [17] Conceitos Esquema Relacional Ai: atributo D: domínio Descrição de uma relação r = {t1,t2,t3,..,tm} t = <v1,v2,v3,..,vn> 1<= i <= n [18] Conceitos [19] Conceitos Cardinalidade Total de instâncias Conforme o estado do mundo real é modificado, o estado da relação também se modifica. [20] Conceitos Domínio Papel (Role) Coleção de Relação DOMÍNIO A1 A2 A3 [21] Características da Relação Relação != Arquivo != Tabela Característcas [22] Características da Relação Ordenação de Tuplas na Relação Tabelas Arquivos Relação [23] Características da Relação Ordenação de Tuplas na Relação Relação [24] Características da Relação Ordenação de valores dentro da Tupla Lista de tuplas Conjunto de tuplas Ordenação Nível Abstrato [25] Características da Relação Ordenação de valores dentro da Tupla Lista de tuplas Conjunto de tuplas [26] Características da Relação Ordenação de valores dentro da Tupla Lista de tuplas Conjunto de tuplas Qual que usa? [27] Características da Relação Valores Nulos Escritório Casa Valores nulos nas tuplas Valor desconhecido Valor existente mas indisponível Valor indefinido Atributo não se aplica [28] Características da Relação Valores Nulos Escritório Casa Valores nulos nas tuplas Valor desconhecido Valor existente mas indisponível Valor indefinido Atributo não se aplica Alunos externos [29] Características da Relação Valores nulos nas tuplas Valor desconhecido Valor existente mas indisponível Valor indefinido Atributo não se aplica Vou usar isso? [30] Significado de Relação Afirmação [31] Significado de Relação Predicado O predicado, formado por um ou mais verbos, é aquilo que se declara sobre a ação do sujeito, concordando em número e pessoa com ele. Lúcia correu semana passada Preposições [32] Significado de Relação Lógica de Predicados Afirmações declarativas Predicado O predicado, formado por um ou mais verbos, é aquilo que se declara sobre a ação do sujeito, concordando em número e pessoa com ele. Validade de Argumentos Qual a diferença entre lógica proposicional e lógica de predicados? A lógica proposicional é a lógica que lida com uma coleção de declarações declarativas que possuem um valor de verdade, verdadeiro ou falso. A lógica de predicado é uma expressão que consiste em variáveis com um domínio especificado. Consiste em objetos, relações e funções entre os objetos. [33] Características da Relação Uppercase: Relações - Q, R, S Lowercase: Estado de Relação - q, r, s Letras: Tuplas – t, u, v Nome atributo = papel Nome (ESTUDANTE) Constraints do Modelo Relacional [34] [35] Constraints Composição do mini-mundo Constraints Relações Dados do Contexto de Banco de Dados [36] Constraints Categorizadas Schema-base Constraints Inheret Model-based Constraints Dados do Contexto de Banco de Dados Application-base Constraints Características das relações DDL Dependência de dados [37] Constraints Processo de Normalização Dependências funcionais Dependências Multivaloradas Constraints baseadas no Esquema do Banco de Dados [38] A = <v1> — dom(A) Constraints de Domínio [39] Integer Float A = <v1> — dom(A) Constraints de Domínio [40] Integer Float Strings Date Time Timestamp Relação -> conjunto de tuplas Constraint de Chave [41] Repetição SK = Subconjunto de atributos Uniqueness Constraint [42] Não pode haver duas tuplas distintas dentro de um estado r de uma relação R com os mesmos valores. Pode haver redundância dentro da superkey, contudo, a chave é única. In the relational data model a superkey is a set of attributes that uniquely identifies each tuple of a relation. Because superkey values are unique, tuples with the same superkey value must also have the same non-key attribute values Chave & Superchave Uniqueness Constraint [43] Falar sobre o motivo de escolha de um atributo para chave em detrimento de outro. Constraint de Chave [44] + de uma possível chave? Chave candidata A escolha de uma em detrimento de outra é arbitrária. Pois cada uma pode se tornar um chave primária. Elas são identificadas pelo sublinhado. Para facilitar podes escolher a cahve primária com apenas um atributo, ou pequeno número de atributos. Uniquinness constraints Chave primária e candidata Constraint de Chaves [45] Valores nulos em atributos? Estado civil, endereço ... SGBDs e esquemas relacionais [46] SGBD É um subconjunto de relações Estado de um SGBD relacional Esquema relacional [47] Esquema relacional [48] Esquema relacional [49] Mesmo nome para atributos diferentes? Esquema relacional [50] Domínio e Conceito SGBD Esquema relacional [51] Restrições de integridade Status do SGBD Definição [52] DDL – Data Definition Language Create, Drop, Insert, Rename... Definição [53] DDL – Data Definition Language Create, Drop, Insert, Rename... DML – Data Manipulation Language Insert, Update, Delete, Merge DCL – Data Control Language Grant, Revoke DQL – Data Query Language Select Estado SGBD - Company [54] Estado SGBD - Company [55] SGBD Integridade [56] Como manter um estado válido? Constraints de Integridade Contraints de domínio, chave, Not Null Integridade [57] Como manter um estado válido? Integridade de entidade Integridade Referencial Integridade Referencial e de Entidade e Chaves Estrangeiras [58] Chave Primária != NULL Consitência entre entidades Integridade [59] Integridade de Entidade Integridade Referencial A restrição de chave e entidade estão relacionadas a uma entidade isoladamente. Contudo, a entregridade referencial busca manter a consistência das informações, tuplas,entre as entidades. Chave Primária != NULL Consitência entre entidades Integridade [60] Integridade de Entidade Integridade Referencial Chave estrangeira A restrição de chave e entidade estão relacionadas a uma entidade isoladamente. Contudo, a entregridade referencial busca manter a consistência das informações, tuplas, entre as entidades. Regras: Domínio dos atributos de FK = Domínio dos atributos da PK t1[FK] = t2[PK] Chave estrangeira [61] As condições para uma chave estrangeira, dadas abaixo, especificam uma restrição de integridade referencial entre os dois esquemas de relação R1 e R2. Regras: Domínio dos atributos de FK = Domínio dos atributos da PK t1[FK] = t2[PK] Chave estrangeira [62] r1(R1) & r2(R2) Rela. De referência Rela. referenciada As condições para uma chave estrangeira, dadas abaixo, especificam uma restrição de integridade referencial entre os dois esquemas de relação R1 e R2. Integridade Referencial [63] As condições para uma chave estrangeira, dadas abaixo, especificam uma restrição de integridade referencial entre os dois esquemas de relação R1 e R2. Chave estrangeira [64] DDL As condições para uma chave estrangeira, dadas abaixo, especificam uma restrição de integridade referencial entre os dois esquemas de relação R1 e R2. Contraints - semântica Salário máximo do supervisor Carga horária máxima/semana Outras Contraints [65] Depende do contexto do banco de dados Triggers & Assertions Geralemente são implementadas a n´vel de aplicação, pois implementar por especificações de constraints por linguagem (SQL) é mais difícil. Outras Contraints [66] Restrições de Estado Restrições de Transição O salário do empregado só pode aumentar ... Triggers & Assertions Aplicação Mapeando o modelo ER/Relacional [67] Mapeando [68] Logical database desing Design Lógico do Projeto Mapeamento do Modelo de dados Relacional ER ou EER Projeto Lógico [69] Mapeamento ER para Modelo Relacional Fonte: livro de referência - Navathe Algortimo de Mapeamento [70] Logical database desing Referência Atributos de valor único (single-values) PK – Primary Key & Unique Key Constraint de Entidade e Referencial Algoritmo de Mapeamento [71] Relação -> E (A1,A2,A3,…,A4) PK Unique Constraint FK – Foreign Key Simples ou Composta? Entidades Fortes (Regulares) O conhecimento sobre as chaves será interessante quando estivermos falando de indexação ou outras análises. Algoritmo de Mapeamento [72] Relação -> E (A1,A2,A3,…,A4) PK Unique Constraint FK – Foreign Key Simples ou Composta? Entidades Fortes (Regulares) [73] Algoritmo de Mapeamento [74] Strong Entity Mapping Relação de Entidade – Entity Relation Algortimo de Mapeamento [75] Relação -> E (A1,A2,A3,…,A4) PK Unique Constraint FK – Foreign Key Simples ou Composta? Entidades Fracas (Dependentes) O conhecimento sobre as chaves será interessante quando estivermos falando de indexação ou outras análises. Algortimo de Mapeamento [76] Entidades Fracas (Dependentes) Como representar a dependência? O conhecimento sobre as chaves será interessante quando estivermos falando de indexação ou outras análises. Algortimo de Mapeamento [77] Entidades Fracas (Dependentes) Como representar a dependência? Employee Dependent O conhecimento sobre as chaves será interessante quando estivermos falando de indexação ou outras análises. Algortimo de Mapeamento [78] Entidades Fracas (Dependentes) Como representar a dependência? Employee Dependent PK e FK O conhecimento sobre as chaves será interessante quando estivermos falando de indexação ou outras análises. [79] Mapeamento - Entidade Fraca Entidade Fraca identificada pela Chave (PK e FK) Employee Dependent Primary key Algoritmo de Mapeamento [80] Foreign Key Merge dos relacionamentos Cross-reference Abordagem Relacionamento Binário 1:1 O conhecimento sobre as chaves será interessante quando estivermos falando de indexação ou outras análises. Algoritmo de Mapeamento [81] PK de S -> FK em T Abordagem FK Relacionamento Binário 1:1 Participação Total O conhecimento sobre as chaves será interessante quando estivermos falando de indexação ou outras análises. Algoritmo de Mapeamento [82] PK de S -> FK em T Abordagem FK Relacionamento Binário 1:1 Participação Total O conhecimento sobre as chaves será interessante quando estivermos falando de indexação ou outras análises. Algoritmo de Mapeamento [83] PK de S -> FK em T Abordagem FK Relacionamento Binário 1:1 Mgr_ssn Start Date FK Note que é possível adicionar a FK na relação S de T. Contudo, neste caso haveriam várias tuplas com valores nulos. Visto que 2% dos empregados irão gerenciar um departamento. Outra possibilidade está na inserção de FK em ambas relações. Este fato acarreta em redudância e consequentemente em penalidade quando se trata da consistência de manutenção. Algoritmo de Mapeamento [84] 2 entidades em 1 entidade Merge Relacionamento Binário 1:1 MgrDep Relacionameno Total O conhecimento sobre as chaves será interessante quando estivermos falando de indexação ou outras análises. Algoritmo de Mapeamento [85] Cross-reference M:N Relacionamento Binário 1:1 Entidade Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Algoritmo de Mapeamento [86] Abordagens: Foreign Key Relationship relation 1:N Relacionamento Binário 1:N Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Algoritmo de Mapeamento [87] Relacionamento Binário 1:N FK Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Algoritmo de Mapeamento [88] FK Relacionamento Binário 1:N Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Algoritmo de Mapeamento [89] Relationship relation Relacionamento Binário 1:N Nova relação Preocupação: valores nulos redundância e fks das entidades para evitar valores nulos Algoritmo de Mapeamento [90] Abordagem: Relationship relation M:N Relacionamento Binário N:M Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Algoritmo de Mapeamento [91] Relacionamento Binário N:M Nova relação Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Algoritmo de Mapeamento [92] Relacionamento Binário N:M Projeto Empregado Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Algoritmo de Mapeamento [93] Abordagem: Componentes simples Atributos Compostos Fname | Minit | Lname Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Algoritmo de Mapeamento [94] Abordagem: Relationship Relation Atributos Multivalorados Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. [95] Algoritmo de Mapeamento [96] Algoritmo de Mapeamento [97] Abordagem: Relationship relation Relacionamento N-ário Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Algoritmo de Mapeamento [98] Relacionamento N-ário Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Algoritmo de Mapeamento [99] Resumo Modelo ER Entidade Relação 1:1 ou 1:N Relação M:N Relacionamento N-ário Modelo Relacional Relação (enidade) FK ou relação Relação ou 2 FKs Relação ou N FKs Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Algoritmo de Mapeamento [100] Resumo Modelo ER Atributo Simples Atributos Composto Atributo Multivalorado Conjunto de valores Atributo chave Modelo Relacional Atributo Conjunto de atributos Relação ou FK Domínio PK (ou secundária) Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Mapeando os components doEER [101] Algoritmo de Mapeamento [102] Especialização SubClasse Única tabela Múltiplas tabela Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Algoritmo de Mapeamento [103] Especialização SubClasse Única tabela Múltiplas tabela Superclasse e Subclasse A escolha depende das constraints da especialização/generalização. Algoritmo de Mapeamento [104] Opções SubClasse Múltiplas relações - Sub/superclasse Múltiplas relações - Subclasse Relação única - 1 tipo de atributo Relação única - Múltiplos tipos de atributo A escolha depende das constraints da especialização/generalização. Algoritmo de Mapeamento [105] A escolha depende das constraints da especialização/generalização. Algoritmo de Mapeamento [106] Relação L Attrs(L) = {k, a1, … , an} Relação Li Attrs(Li) = {k} Múltiplas relações - Sub/superclasse 1 ≤ i ≤ m Si Attrs(Li) = {k} ∪ {attributes of Si} PK(Li) = k A escolha depende das constraints da especialização/generalização. Algoritmo de Mapeamento [107] A escolha depende das constraints da especialização/generalização. Algoritmo de Mapeamento [108] Múltiplas relações - Sub/superclasse A escolha depende das constraints da especialização/generalização. Algoritmo de Mapeamento [109] Relação L Attrs(L) = {k, a1, … , an} Relação Li Attrs(Li) = {k} Múltiplas relações - Subclasse apenas 1 ≤ i ≤ m Si Attrs(Li) = {k} ∪ {attributes of Si} PK(Li) = k A escolha depende das constraints da especialização/generalização. Algoritmo de Mapeamento [110] Relação Li Attrs(Li) = {k} Múltiplas relações - Subclasse apenas 1 ≤ i ≤ m Si Attrs(Li) = {attributes of Si} ∪ {k, a1, … , an} PK(Li) = k Usado em situações de pertecimento total. Todas as instâncias da superclasse devem pertencer a pelo uma das subclasses. Se encaixa vem com relação de especialização disjunta Algoritmo de Mapeamento [111] A escolha depende das constraints da especialização/generalização. Algoritmo de Mapeamento [112] Múltiplas relações - Subclasse apenas A escolha depende das constraints da especialização/generalização. Algortimo de Mapeamento [113] Relação L Attrs(L) = {k} Relação única - 1 tipo de atributo Attrs(L) = {k, a1, …, an} ∪ {attributes of S1} ∪ … ∪ {attributes of Sm} ∪ {t} PK(L) = k Type attribute d Usado em situações de pertecimento total. Todas as instâncias da superclasse devem pertencer a pelo uma das subclasses. Se encaixa vem com relação de especialização disjunta Algoritmo de Mapeamento [114] Relação única - 1 tipo de atributo ID | Nome | Endereço | Data de Nascimento | T(tipo) subclasse Usado em situações de pertecimento total. Todas as instâncias da superclasse devem pertencer a pelo uma das subclasses. Se encaixa vem com relação de especialização disjunta Algoritmo de Mapeamento [115] Relação única - 1 tipo de atributo Atributos de tipo A escolha depende das constraints da especialização/generalização. Algoritmo de Mapeamento [116] Relação L Attrs(L) = {k} Relação única - Múltiplos atributos Type Attrs(L) = {k, a1, …, an} ∪ {attributes of S1} ∪ … ∪ {attributes of Sm} ∪ {t1, t2, …, tm} PK(L) = k True OR False Usado em situações de pertecimento total. Todas as instâncias da superclasse devem pertencer a pelo uma das subclasses. Se encaixa vem com relação de especialização disjunta Algoritmo de Mapeamento [117] Relação única - Múltiplos atributos Type ID | Nome | Endereço | Data de Nascimento | Auxiliar | Técnico | Engenheiro Subclasse Funciona melhor para relações sobrepostas ou overlaping. Contudo, também vale para disjuntas. Algoritmo de Mapeamento [118] Múltiplos Atributos de tipo Relação única - Múltiplos atributos Type A escolha depende das constraints da especialização/generalização. Algoritmo de Mapeamento [119] Herança Múltipla Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Algoritmo de Mapeamento [120] Herança Múltipla Mesmo atributo chave Mapeamento: Opções anteriores Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. [121] Herança Múltipla Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Algoritmo de Mapeamento [122] Herança Múltipla Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Algoritmo de Mapeamento [123] Superclasses Tipos de entidades diferentes UNION TYPE Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. [124] UNION TYPE Algoritmo de Mapeamento Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Algoritmo de Mapeamento [125] UNION TYPE Chave substituta Chave Primária Chave Estrangeira Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Handos on: Estudo de caso [126] Hands on [127] Herança Múltipla Objetivo: Mapear os esquemas conceituais de OS, Universidade e E-commerce Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Hands on: OS [128] Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Hands on: Universidade [129] Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Etapa 2 Explorando a Linguagem SQL [130] // Explorando a Linguagem de Consulta a Banco de Dados SQL Explorando a SQL [131] Explorando SGBDs: MySQL e PostgreSQL SQL Básico, DDL e DML DESAFIO Introdução ao SQL [132] Linguagem para: Manipulação de dados Execução de operações Introdução ao SQL 1970 DBAs BI Devs DS Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Introdução ao SQL [133] Objetivo Modificação de dados e estrutura Adicionar, remover ou atualizar linhas Recuperação de um subconjunto de info do BD Introdução ao SQL 1970 OLTP OTAP Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Introdução ao SQL [134] Classificações DDL – Data Definition Language DML – Data Manipulation Language DCL – Data Control Language DQL – Data Query Language Introdução ao SQL 1970 Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Introdução ao SQL [135] Classificações DDL – Data Definition Language Introdução ao SQL CREATE | DROP | ALTER RENAME | TRUNCATE | MERGE INSERT | UPDATE | DELETE Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Introdução ao SQL [136] Classificações DDL – Data Definition Language Introdução ao SQL USUÁRIO ESQUEMA STATEMENTS INDEXING Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Introdução ao SQL [137] Classificações DDL – Data Definition Language Introdução ao SQL Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Introdução ao SQL [138] Classificações DDL – Data Definition Language Introdução ao SQL Primeiro Exemplo Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Introdução ao SQL [139] Classificações DDL – Data Definition Language Primeiro Exemplo Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. “Falar é fácil. Mostre-me o código!” Linus Torvalds [140] Hands On! Criando objeto pessoa Introdução ao SQL [141] Classificações DML – Data Manipulation Language Introdução ao SQL INSERT | UPDATE DELETE | MERGE Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Introdução ao SQL [142] Classificações DCL – Data Control Language DQL – Data Query Language Introdução ao SQL GRANT | REVOKE SELECT Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Introdução ao SQL [143]Statement Comando/instrução Reconhecido pelo BD Retorno: registro de dados Introdução ao SQL Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Introdução ao SQL [144] Cláusulas SQL Função -> instrução Algumas são obrigatórias Introdução ao SQL Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Introdução ao SQL [145] SELECT now(); Datatime da máquina SELECT now() FROM dual; Introdução ao SQL Cadê o FROM ? Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Introdução ao SQL [146] Cláusulas SQL Introdução ao SQL SELECT FROM WHERE ORDER BY GROUP BY HAVING Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Introdução ao SQL [147] Termos – SQL Introdução ao SQL Identificador Operador Constante Expressão Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. [148] MODELO RELACIONAL [149] MODELO ER “Falar é fácil. Mostre-me o código!” Linus Torvalds [150] Hands On! Manipulando o esquema do BD Tipos de dados no Mysql [151] Tipos de dados [152] Caracteres Fixo - 255 bytes Variável - 65.535 bytes Introdução ao SQL Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Tipos de dados [153] Texts types mediumtext - 16,777,215 bytes longtext - 4,294,967,295 characters ... Introdução ao SQL Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Tipos de dados [154] Conjunto de caracteres Alfabeto, Português, Inglês... SHOW CHARACTER SET; ... Fonte: livro 2 de referência Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Tipos de dados [155] Conjunto de caracteres Alfabeto, Português, Inglês... SHOW CHARACTER SET; ... Maxlen > 1 = multibyte char set Fonte: livro 2 de referência Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Tipos de dados [156] Texts data < 64KB Introdução ao SQL Fonte: livro 2 de referência Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Tipos de dados [157] Dados numéricos Introdução ao SQL Fonte: livro 2 de referência Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Tipos de dados [158] Dados temporais Introdução ao SQL Fonte: livro 2 de referência Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Constraints em SQL [159] Constraint [160] NOT NULL PK & SK Introdução ao SQL Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Constraints [161] NOT NULL PK & SK Introdução ao SQL DEFAULT CHECK DOMAIN VALOR Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Constraints [162] NOT NULL PK & SK Introdução ao SQL DEFAULT CHECK DOMAIN VALOR Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Constraints [163] NOT NULL PK & SK Introdução ao SQL DEFAULT CHECK DOMAIN VALOR DELETE COMPORTAMENTO Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Constraints [164] Primary Key UNIQUE Introdução ao SQL CREATE TABLE Atualizações que violem as constraints são rejeitadas pelo banco de dados. De qualquer forma triggers podem ser adicionados para serem executados a partir de uuma ação. Violação de Constraints [165] Primary Key UNIQUE Introdução ao SQL Referential Triggered Action Atualizações que violem as constraints são rejeitadas pelo banco de dados. De qualquer forma triggers podem ser adicionados para serem executados a partir de uuma ação. Violação de Constraints [166] Nomear CHECK Introdução ao SQL Row-based Row-based constraint QUERIES / INSERTION COM SQL [167] SQL Queries [168] Comportamento Multiset – not set Duplicações (Redundâncias) Row-based constraint SQL Queries [169] Comportamento Multiset – not set Duplicações (Redundâncias) Custoso – melhor prevenir Pode ser desejada pelo usuário Row-based constraint SQL Queries [170] Comportamento Multiset – not set Duplicações (Redundâncias) DISTINCT Row-based constraint SQL Queries [171] Mapping SELECT <lista de atributos> FROM <tabela> WHERE <condição> Row-based constraint SQL Queries [172] Mapping SELECT <lista de atributos> FROM <tabela> WHERE <condição> Projeção de atributos Row-based constraint SQL Queries [173] Operadores =, <, <=, >, >=, e <> - SQL +, -, *, / - operadores básicos AND, OR, XOR, NOT – op. boleanos TRUE, FALSE – operadores lógicos Row-based constraint SQL Queries [174] DML Subconjunto do SQL Mais utilizado Comandos: INSERT, DELETE, UPDATE Row-based constraint SQL Queries [175] DML INSERT INTO <table> (<list-attributes>) VALUES (<list-values>); NULL & Not NULL Row-based constraint SQL Queries [176] DML INSERT INTO <table> (<list-attributes>) VALUES (<list-values>); NULL & Not NULL Valores das PK? Row-based constraint SQL Queries [177] DML INSERT INTO <table> (<list-attributes>) VALUES (<list-values>); NULL & Not NULL Valores das PK? SMALLINT UNSIGNED INT, BIGINT 0 to 65535 Row-based constraint SQL Queries [178] DML INSERT INTO <table> (<list-attributes>) VALUES (<list-values>); Valores das PK? SMALLINT UNSIGNED INT, BIGINT 0 to 65535 Row-based constraint [179] Inserindo as Constraints “Falar é fácil. Mostre-me o código!” Linus Torvalds [180] Hands On! Constraints com SQL Nomes, Aliasing e Variação de tuplas [181] Nomes de atributos [182] Suponha... Row-based constraint Identificando a Tabela [183] Suponha... Row-based constraint Nomes de atributos [184] Quando usar? Ambiguidade Esclarecimento Row-based constraint Nomes de atributos [185] Quando usar? Diferenciar atributos Row-based constraint Renomeando [186] Renomear atributos? Nomenclatura diferente Nova nomenclatura Row-based constraint Renomeando [187] Renomear atributos? Nomenclatura diferente Referências múltiplas Row-based constraint “Falar é fácil. Mostre-me o código!” Linus Torvalds [188] Hands On! Alias (AS Statement) em Queries SQL When Good Statements Go Bad [189] Problemas [190] PK & FK inexistente Valores violados Conversão inválida de datas Introdução ao SQL Row-based constraint Problemas [191] PK inexistente Introdução ao SQL Row-based constraint Problemas [192] FK inexistente Introdução ao SQL Row-based constraint Problemas [193] Violação de valores Introdução ao SQL Row-based constraint Problemas [194] Conversão inválida Introdução ao SQL Row-based constraint MySQL & PostgreSQL [195] Postegresql [196] Otimizado para aplicações complexas Grande volume de dados \ informações críticas Ex: E-commerce grande/médio porte Row-based constraint MySQL [197] Possui agilidade e versatilidade Operações mas simples Processamento e tempo curto de resposta Ex: Site | Fórum | Portal Row-based constraint Ponderações [198] Precisa de Rollback? MySQL não possui Precisa de agilidade? Fácil utilização Operação mais simplificada MySQL Enterprise Row-based constraint Gerando relatórios com SQL – Exportando XML [199] Desafio 1 : Design do Esquema de BD Relacinal para Aplicação Oficina [200] Design de Esquema Relacional - Oficina [201] Declare suas relações usando o SQL DDL. Especifique um número de consultas em SQL que são necessárias para seu banco de dados Oficina. Com base no uso esperado do banco de dados, determine as cronstraints que fazem sentido para seu contexto Implemente seu banco de dados com MySQL Exporte o script SQL, adicione no Github para validação Row-based constraint Desafio 2 : Gerando Relatórios baseadosno BD - Oficina [202] Design de Esquema Relacional - Oficina [203] 1. Especificar as perguntas relevantes para sua aplicação 2. Descrever como representa-las em SQL 3. Gerar o relatório com base nas perguntas Row-based constraint Etapa 3 Explorando Queries com SQL [204] // Explorando a Linguagem de Consulta a Banco de Dados SQL Percurso [205] Row-based constraint Explorando os Comandos de DDL – Data Definition Language Criando Expressões com SQL Concatenando Texto em uma Query SQL Comandos baseados em operações matemáticas: UNION, INTERSECTION & EXCEPT Percurso [206] Row-based constraint Cláusulas de ordenação: ORDER BY Statement Funções de agregação de dados com SQL Cláusulas de agrupamento: GROUP BY Statement HAVING Statement Hands on: Aplicando cláusulas de agrupamento e ordenação ao BD Universidade Hands on: Aplicando cláusulas de agrupamento e ordenação ao BD E-commerce Explorando comandos DDL – Data Definition Language [207] Introdução ao SQL [208] Classificações DDL – Data Definition Language Introdução ao SQL CREATE | DROP | ALTER RENAME | TRUNCATE | MERGE INSERT | UPDATE | DELETE Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. [209] UPDATE DROP DELETE ALTER Introdução ao SQL Row-based constraint UPDATE Modificação a partir de uma condição DROP DELETE ALTER [210] Introdução ao SQL UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; Row-based constraint UPDATE DROP Remoção de estruturas DELETE ALTER [211] Introdução ao SQL DROP DATABASE databasename; Row-based constraint [212] UPDATE DROP DELETE Remoção de registros ALTER Introdução ao SQL DELETE FROM table_name WHERE condition; Row-based constraint [213] UPDATE DROP DELETE ALTER Modificação da estrutura do BD Introdução ao SQL ALTER TABLE Customers ADD Email varchar(255); Row-based constraint “Falar é fácil. Mostre-me o código!” Linus Torvalds [214] Hands On! Constraints com SQL Expressões em Statements SQL [215] [216] Atributos aritméticos Operações matemáticas Tipos de dados: string e numérico Expressões SELECT Fname, Lname, Salary, Salary * 0.11 AS INSS FROM EMPLOYEE; Row-based constraint [217] Armazenamento: dados simples Resultado de expressões: dados complexos Plus: utilizar alias Expressões SELECT Fname, Lname, Salary, Salary * 0.11 AS INSS FROM EMPLOYEE; Row-based constraint Concatenando Strings [218] Nome Fname Minit Lname Como representar isso em um consulta? Row-based constraint [219] Atributos distintintos -> único sentido Nome: Fname, Mint, Lname Expressões - Strings Como representar isso em um consulta? Row-based constraint [220] Atributos distintintos -> único sentido Nome: Fname, Mint, Lname Expressões - Strings SELECT concat(Fname, ' ', Minit, ' ', Lname) AS Complete_name FROM EMPLOYEE; Como representar isso em um consulta? Row-based constraint “Falar é fácil. Mostre-me o código!” Linus Torvalds [221] Hands On! Utlizando Expressões em SQL Introdução a operação de conjuntos com SQL [222] Like|Between [223] Comparação String Matching Caracteres especiais: % e _ Row-based constraint Strings [224] ‘AB_CD%EF’ '' -> "" ‘AB\_CD\%EF’ ESCAPE ‘\’ Row-based constraint Like|Between [225] Intervalo Numérico Ex: salário, idade ... Row-based constraint “Falar é fácil. Mostre-me o código!” Linus Torvalds [226] Hands On! Utilizando like e between com SQL Comandos baseados em Operações Matemáticas: UNION, INTERSECTION & EXCEPT [227] Union|Intersection|Except [228] Fonte: c-sharpcorner Row-based constraint Union|Intersection|Except [229] UNION UNION ALL INTERSECTION EXCEPT Mesmo Tipo de Relação Row-based constraint Union|Intersection|Except [230] UNION UNION ALL INTERSECTION EXCEPT Mesmo Tipo de Relação A ---- a1 a2 A ---- a3 A ---- a4 a5 R -> S R -> S S -> R Row-based constraint Union|Intersection|Except [231] UNION UNION ALL INTERSECTION EXCEPT Mesmo Tipo de Relação Row-based constraint “Falar é fácil. Mostre-me o código!” Linus Torvalds [232] Hands On! Utilizando operadores com SQL Nested Queries com SQL [233] Nestes Queries [234] Comparação por atributos buscados Nested Query – Consulta aninhada Consulta externa e interna O que é isso? Row-based constraint Nestes Queries [235] Comparação por atributos buscados Nested Query – Consulta aninhada Consulta externa e interna O que é isso? SELECT FROM WHERE Row-based constraint Nestes Queries [236] Exemplo Row-based constraint Nestes Queries [237] IN – comparação com set = – comparação com unidades Retorno: tabela SELECT FROM WHERE Row-based constraint Nestes Queries [238] Keywords: ANY, SOME, ALL Operadores: >, >=, <, <=, and <> SELECT FROM WHERE Row-based constraint [239] SELECT FROM WHERE Nestes Queries Correlacionadas Row-based constraint [240] EXISTS: TRUE se o resultado da consulta aninhada contiver pelo menos uma tupla NOT EXISTS: TRUE se o resultado da consulta aninhada não contiver tuplas UNIQUE: retorna TRUE se único Cláusulas EXISTS & UNIQUE Row-based constraint [241] Cláusulas EXISTS & UNIQUE Row-based constraint [242] Conjuntos Explícitos Row-based constraint Etapa 4 Criando Queries com Funções/Cláusulas de Agrupamentos [243] // Explorando a Linguagem de Consulta a Banco de Dados SQL Cláusulas de Ordenação com SQL [244] Ordenação em Queries SQL [245] Recuperação dos dados é ordenada? Row-based constraint Ordenação em Queries SQL [246] Recuperação dos dados é ordenada? Como realizar a ordenação? ORDER BY Row-based constraint Ordenação em Queries SQL [247] Cláusula SQL Ordenação por coluna Expressões baseadas em dados + de um valor ORDER BY Row-based constraint Ordenação em Queries SQL [248] Cláusula SQL Ordenação por coluna Expressões baseadas em dados + de um valor ORDER BY Select Fname, Lname, Dno from employee order by (Dno); Select Fname, Lname, Dno from employee; Row-based constraint Ordenação em Queries SQL [249] Sentido da ordenação Ascendente Descendente Row-based constraint Ordenação em Queries SQL [250] SELECT <attribute list> FROM <table list> [ WHERE <condition> ] [ ORDER BY <attribute list> ]; Estrutura das Clásulas SQL Row-based constraint WHERE STATEMENT? [251] Critério a ser considerado na query ao recuperar a informação. A tupla (linha ou instância) deve conter a condição definida em where Definição formal Row-based constraint Ordenação em Queries SQL [252] SELECT <attribute list> FROM <table list> [ ORDER BY <attribute list> DESC]; Estrutura das Clásulas SQL Ordenação Row-based constraint Ordenação em Queries SQL [253] SELECT idAccount FROM accounts ORDER BY amount LIMIT 5; Estrutura das Clásulas SQL Exemplo Expressão Row-based constraint Ordenação em Queries SQL [254] SELECT <attribute list> FROM <table list> [ WHERE <condition> ] [ ORDER BY <attribute list> ]; Estrutura das Clásulas SQL Order + Expressões Row-based constraint Ordenação em Queries SQL [255] SELECT idAccount FROM accounts WHERE type = ‘PJ’ ORDER BY amount LIMIT 5; Estrutura das Clásulas SQL Exemplo Expressão Row-based constraint “Falar é fácil. Mostre-me o código!” Linus Torvalds [256] Hands On! Ordenando dados recuperados com SQL Ordenação com SQL Utilizando Expressões [257] Classificação de dados [258] Cláusula SQL Ordenação por coluna Expressões baseadas em dados + de um valor ORDER BY E com dados derivados? Row-based constraint Classificação de dados [259] E com dados derivados? Ordenação dos dados para trazer consistência e sequência Alias para fornecer nomenclatura adequada à dados derivados ou armazenados Row-based constraint [260] SELECT <attribute list> FROM <table list> [ WHERE <condition> ] [ ORDER BY <attribute list> ]; Order + Expressões Adicionar expressão Tratar dados não armazenados Classificação de dados Row-based constraint Classificação de dados [261] Exemplo práticoXXX.XXX.XX7-61 Expressão para identificar o user Row-based constraint Classificação de dados [262] Exemplo prático XXX.XXX.XX7-61 Expressão para identificar o user Row-based constraint Classificação de dados [263] Operadores numéricos Nomes dos atributos + de um atributo DESC ou ASC Como ordernar os registros? Row-based constraint [264] SELECT <attribute list> FROM <table list> [ WHERE <condition> ] [ ORDER BY <atribute a, atribute b> ] [DESC | ASC]; Classificação de dados Row-based constraint [265] SELECT <attribute list> FROM <table list> [ WHERE <condition> ] [ ORDER BY <EXPRESSION> ] [DESC | ASC]; Classificação de dados Row-based constraint “Falar é fácil. Mostre-me o código!” Linus Torvalds [266] Hands On! Ordenando dados recuperados com SQL Funções de Agregação com SQL [267] [268] Funções de Agrupamento Agregar Aglutinar Agrupar Row-based constraint [269] COUNT SUM MIN MAX AVERAGE Agrupamento de Registros Row-based constraint [270] COUNT: registros SUM: somatório MIN: valor mínimo - atributo MAX: valor máximo - atributo AVERAGE: média de valor - atributo Agrupamento de Registros Row-based constraint [271] COUNT: registros SUM: somatório MIN: valor mínimo - atributo MAX: valor máximo - atributo AVERAGE: média de valor - atributo Agrupamento de Registros SELECT HAVING Row-based constraint [272] Agrupamento de Registros Station number | report code | year | month | day | temp | pressure |visibility | wind speed | temperature | precipitation | fog | rain | hail | thunder | tornado Row-based constraint [273] Fonte: Getting Start with SQL Row-based constraint [274] Agrupamento de Registros SELECT COUNT(*) AS record_count FROM station_data; SELECT year, COUNT(*) AS record_count FROM station_data WHERE tornado = 1 GROUP BY year; SELECT year, month, COUNT(*) AS record_count FROM station_data WHERE tornado = 1 GROUP BY year, month Row-based constraint [275] Agrupamento de Registros SELECT month, round(AVG(temp),2) as avg_temp FROM station_data WHERE year >= 2000 GROUP BY month Row-based constraint “Falar é fácil. Mostre-me o código!” Linus Torvalds [276] Hands On! Exemplos com BD Employee GROUP BY: Cláusula de Agrupamento com SQL [277] [278] Funções de Agrupamento Agregar Aglutinar Agrupar Grupos Row-based constraint [279] Atributos de Relacionamentos Grupos de valores Agrupamento de Registros Row-based constraint [280] Atributos de Relacionamentos Grupos de valores Agrupamento de Registros Row-based constraint [281] Agrupamento de Registros Row-based constraint “Falar é fácil. Mostre-me o código!” Linus Torvalds [282] Hands On! Exemplos com BD Employee Entendendo o HAVING Statement [283] [284] Funções de Agrupamento Agregar Aglutinar Agrupar Grupos Condição? Row-based constraint [285] SELECT: seleciona tabela e atributos GROUP BY: grupos de valores HAVING: Filtragem dos grupos HAVING Satement Row-based constraint [286] SELECT: seleciona tabela e atributos GROUP BY: grupos de valores HAVING: Filtragem dos grupos HAVING Satement Condição sobre a informação sobre o grupo Row-based constraint [287] HAVING Satement Row-based constraint [288] HAVING Satement SELECT <attribute and function list> FROM <table list> [ WHERE <condition> ] [ GROUP BY <grouping attribute(s)> ] [ HAVING <group condition> ] [ ORDER BY <attribute list> ]; Estrutura da Query SQL Row-based constraint [289] HAVING Satement SELECT <attribute and function list> FROM <table list> [ WHERE <condition> AND <condition2> ] [ GROUP BY <grouping attribute(s)> ] [ HAVING <group condition> ] [ ORDER BY <attribute list> ]; Query interna Row-based constraint [290] HAVING Satement SELECT <attribute and function list> FROM <table list> [ WHERE <condition> AND <condition2> ] [ GROUP BY <grouping attribute(s)> ] [ HAVING <group condition> ] [ ORDER BY <attribute list> ]; Query interna Preferência de execução das cláusulas Row-based constraint “Falar é fácil. Mostre-me o código!” Linus Torvalds [291] Hands On! Exemplos com BD Employee Ainda está em dúvida sobre ORDER e GROUP? [292] AGRUPAMENTO [293] Qual a diferença entre ORDER BY e GROUP BY? GROUP BY Nome | ----------+ Maria João Cristina Mario .... ORDER BY Row-based constraint AGRUPAMENTO [294] Qual a diferença entre ORDER BY e GROUP BY? ORDER BY Nome | ----------+ Maria João Cristina Mario .... Nome | ----------+ Cristina João Maria Mario .... Row-based constraint AGRUPAMENTO [295] Qual a diferença entre ORDER BY e GROUP BY? ORDER BY Nome | ----------+ Maria João Cristina Mario .... SELECT nome FROM tabela ORDER BY nome Row-based constraint AGRUPAMENTO [296] Qual a diferença entre ORDER BY e GROUP BY? GROUP BY CarModel | ----------+ C350 C350 C350 C23 .... ORDER BY Row-based constraint GROUP BY AGRUPAMENTO [297] Qual a diferença entre ORDER BY e GROUP BY? CarModel | ----------+ C350 C350 C350 C23 .... CarModel | ----------+ C23 C350 .... Row-based constraint GROUP BY AGRUPAMENTO [298] Qual a diferença entre ORDER BY e GROUP BY? CarModel | ----------+ C350 C350 C350 C23 .... SELECT carro FROM tabela GROUP BY carro ORDER BY carro Row-based constraint Agrupamento em Queries SQL [299] Cláusula SQL Ordenação por coluna Expressões baseadas em dados + de um valor Row-based constraint Etapa 5 Agrupando Registros e Tabelas com JOIN [300] // Explorando a Linguagem de Consulta a Banco de Dados SQL Explorando a SQL [301] Case Statement Entendendo Queries para Múltiplas tabelas com Joins Queries com Inner JOIN Orientação no JOIN: LEFT JOIN & RIGHT JOIN OUTER JOIN E NATURAL JOIN CASE Statement [302] CASE Statement [303] Troca de um valor Condição para troca Mapeamento de valores de correspondência CASE END Row-based constraint CASE Statement [304] Troca de um valor Condição para troca Mapeamento de valores de correspondência CASE END WHEN THEN Row-based constraint CASE Statement [305] CASE END WHEN THEN Fonte: livro de referência Row-based constraint CASE Statement [306] Fonte: livro Getting Started with SQL Exemplo de mapeamento do clima Row-based constraint CASE Statement [307] Fonte: livro de referência SELECT report_code, year, month, day, wind_speed, CASE WHEN wind_speed >= 40 THEN 'HIGH' WHEN wind_speed >= 30 AND wind_speed < 40 THEN 'MODERATE' ELSE 'LOW' END as wind_severity FROM station_data Row-based constraint CASE Statement [308] Fonte: livro de referência SELECT report_code, year, month, day, wind_speed, CASE WHEN wind_speed >= 40 THEN 'HIGH' WHEN wind_speed >= 30 THEN 'MODERATE' ELSE 'LOW' END as wind_severity FROM station_data SGBD entende a operação Row-based constraint Agrupando Queries com CASE Statement [309] Agrupando CASE Statement [310] CASE END CASE END Row-based constraint CASE Statement [311] Fonte: livro de referência SELECT year, CASE WHEN wind_speed >= 40 THEN 'HIGH' WHEN wind_speed >= 30 THEN 'MODERATE' ELSE 'LOW' END as wind_severity, COUNT(*) as record_count FROM station_data GROUP BY 1, 2 Row-based constraint O caso zero/null trick [312] CASE Statement [313] Filtros – valores distintos SELECT QUERY WHERE CASE END Row-based constraint CASE Statement [314] Tornado precipitation SELECT year, month, SUM(precipitation) AS tornado_precipitation FROM station_data WHERE tornado = 1 GROUP BY year, month Sem o CASE Fonte: livro Getting Start with SQL Row-based constraint CASE Statement [315] Non-Tornado precipitation SELECT year, month, SUM(precipitation) AS non_tornado_precipitation FROM station_data WHERE tornado = 0 GROUP BY year, month Sem o CASE Fonte: livro Getting Start with SQL Row-based constraint CASE Statement [316] Fonte: livro de referência SELECT year, month, SUM(CASE WHEN tornado = 1 THEN precipitation ELSE 0 END) as tornado_precipitation, SUM(CASE WHEN tornado = 0 THEN precipitation ELSE 0 END) as non_tornado_precipitationFROM station_data GROUP BY year, month Utilizando CASE Row-based constraint CASE Statement [317] Resultado Fonte: livro Getting Start with SQL Row-based constraint CASE Statement [318] Fonte: livro Getting Start with SQL SELECT year, month, SUM(CASE WHEN tornado = 1 THEN precipitation ELSE 0 END) as tornado_precipitation, SUM(CASE WHEN tornado = 0 THEN precipitation ELSE 0 END) as non_tornado_precipitation FROM station_data GROUP BY year, month Utilizando CASE MAX AVG MIN Row-based constraint CASE Statement [319] MAX, AVG, MIN NAD, OR, NOT COUNT CASE END Combinar operadores com CASE Row-based constraint “Falar é fácil. Mostre-me o código!” Linus Torvalds [320] Hands On! Projeto Entendendo Queries de Mútiplas Tabelas com JOINs [321] JOIN Statement [322] SELECT e.fname, e.lname FROM employee e Row-based constraint JOIN Statement [323] SELECT e.fname, e.lname, d.name FROM employee e JOIN department d Row-based constraint JOIN Statement [324] SELECT e.fname, e.lname, d.name FROM employee e JOIN department d ... Row-based constraint JOIN Statement [325] JOIN: repetição de registros Produto Cartesiano CASE END Combinar operadores com CASE Row-based constraint CROSS JOIN Statement [326] SELECT pt.name, p.product_cd, p.name FROM product p CROSS JOIN product_type pt; Leitura para CROSS JOIN: Learning SQL Row-based constraint JOIN Statement [327] SELECT e.fname, e.lname, d.name FROM employee e JOIN department d ... O que faltou? Row-based constraint JOIN Statement [328] SELECT e.fname, e.lname, d.name FROM employee e JOIN department d ON e.dept_id = d.dept_id ; ... Row-based constraint SELECT <atributes list> FROM <table1> ft JOIN <table2> st JOIN Statement [329] O que acontece OUTER JOIN? CROSS SELECT <atributes list> FROM <table1> ft INNER JOIN <table2> st ON ft.commom_attibute = st.commom_attribute JOIN Statement [330] O que acontece OUTER JOIN? Queries com INNER JOIN [331] JOIN Statement [332] SELECT e.fname, e.lname, d.name FROM employee e JOIN department d ON e.dept_id = d.dept_id ; ... Row-based constraint JOIN Statement [333] SELECT e.fname, e.lname, d.name FROM employee e JOIN department d ON e.dept_id = d.dept_id ; ... Valores existentes em uma tabela mas não na outra? Falha: linhas excluídas Row-based constraint Mais comum dos JOINs Linhas não correspondentes excluídas Atributo de junção INNER JOIN [334] 1 + atributos junção Mesclar Tabelas 2 + Tabelas Mais comum dos JOINs Linhas não correspondentes excluídas Atributo de junção INNER JOIN [335] ON tab_a.atributo = tab_b.atributo USING (atributo c/ mesmo nome) 1 + atributos junção Mesclar Tabelas 2 + Tabelas Exemplo: INNER JOIN [336] Fonte: livro Getting Started with SQL INNER JOIN [337] SELECT ORDER_ID, CUSTOMER.CUSTOMER_ID, ORDER_DATE, SHIP_DATE, NAME, STREET_ADDRESS, CITY, STATE, ZIP, PRODUCT_ID, ORDER_QTY FROM CUSTOMER INNER JOIN CUSTOMER_ORDER ON CUSTOMER.CUSTOMER_ID = CUSTOMER_ORDER.CUSTOMER_ID Atributos de junção Row-based constraint “Falar é fácil. Mostre-me o código!” Linus Torvalds [338] Hands On! Projeto Agrupamento com mais de 3 Tabelas com JOIN [339] 3 ou + tabelas com JOIN [340] Fonte: livro Learning SQL JOIN com duas tabelas 3 ou + tabelas com JOIN [341] Fonte: livro Learning SQL JOIN com três tabelas 3 tabelas 2 tipos de JOINs 2 subcláusulas 3 ou + tabelas com JOIN [342] Fonte: livro Learning SQL 3 ou + tabelas com JOIN [343] Fonte: livro Learning SQL 3 ou + tabelas com JOIN [344] Fonte: livro Learning SQL “Falar é fácil. Mostre-me o código!” Linus Torvalds [345] Hands On! Projeto A Ordem Importa em Queries com JOIN? [346] SGBD interpreta e escolhe a sequência de comados SGBD escolhe um ponto de partida Ordem de Queries JOIN [347] Ordem de Queries JOIN [348] Tem como especificar a ordem para o SGBD? STRAIGHT_JOIN FORCE ORDER INNER JOIN [349] SELECT STRAIGHT_JOIN a.account_id, c.fed_id, e.fname, e.lname FROM customer c INNER JOIN account a ON a.cust_id = c.cust_id INNER JOIN employee e ON a.open_emp_id = e.emp_id WHERE c.cust_type_cd = 'B'; Forçando ordem de junção Row-based constraint Outros tópicos com JOIN Statement [350] Nested (Subqueries) com JOIN Statement [351] SELECT a.account_id, a.cust_id, a.open_date, a.product_cd FROM account a INNER JOIN (SELECT emp_id, assigned_branch_id FROM employee WHERE start_date < '2007-01-01' AND (title = 'Teller' OR title = 'Head Teller')) e ON a.open_emp_id = e.emp_id INNER JOIN (SELECT branch_id FROM branch WHERE name = 'Woburn Branch') b ON e.assigned_branch_id = b.branch_id; Subqueries com JOIN [352] Subqueries COM JOIN [353] Subqueries COM JOIN [354] SELECT a.account_id, a.cust_id, a.open_date, a.product_cd FROM account a INNER JOIN (SELECT emp_id, assigned_branch_id FROM employee WHERE start_date < '2007-01-01' AND (title = 'Teller' OR title = 'Head Teller')) e ON a.open_emp_id = e.emp_id INNER JOIN (SELECT branch_id FROM branch WHERE name = 'Woburn Branch') b ON e.assigned_branch_id = b.branch_id; Subqueries com JOIN [355] Mesma Tabela em uma Query JOIN [356] JOIN Statement [357] E quando precisamos utilizar a mesma tabela em um JOIN? Mas preciso disso? Row-based constraint JOIN Satement [358] Chave estrangeira refenciando a mesma tabela Exemplo 1 Row-based constraint JOIN Satement [359] Exemplo 2 Row-based constraint Condição de Junção e Filtros [360] JOIN Satement [361] SELECT cust_id, name FROM business; Row-based constraint JOIN Satement [362] SELECT cust_id, name FROM customer; Row-based constraint JOIN Satement [363] SELECT a.account_id, a.product_cd, c.fed_id, FROM account a INNER customer c ON a.cust_id = c.cust_id WHERE c.cust_type_cd = ‘B’; Row-based constraint JOIN Satement [364] SELECT a.account_id, a.product_cd, c.fed_id, FROM account a INNER customer c ON a.cust_id = c.cust_id AND c.cust_type_cd = ‘B’; O QUE ACONTECE SE ERRARMOS A SINTAXE? Row-based constraint JOIN Statement [365] SELECT e.fname, e.lname, d.name FROM employee e JOIN department d ... Row-based constraint “Falar é fácil. Mostre-me o código!” Linus Torvalds [366] Hands On! Projeto Como utilizar o OUTER JOIN Statement? [367] Linhas sem correspondencias não eram exibidas Valores de ambas tabelas OUTER JOIN [368] So far, so good ... OUTER JOIN [369] OUTER JOIN [370] O que acontece no OUTER JOIN? OUTER JOIN [371] O que acontece no OUTER JOIN? OUTER JOIN [372] O que acontece no OUTER JOIN? Matchs nas tabelas SELECT <atributes list> FROM <table1> ft OUTER JOIN <table2> st ON ft.commom_attibute = st.commom_attribute OUTER JOIN [373] O que acontece OUTER JOIN? SELECT <atributes list> FROM <table1> ft OUTER JOIN <table2> st ON ft.commom_attibute = st.commom_attribute OUTER JOIN [374] O que acontece OUTER JOIN? Tabela de orientação SELECT <atributes list> FROM <table1> ft OUTER JOIN <table2> st ON ft.commom_attibute = st.commom_attribute OUTER JOIN [375] O que acontece OUTER JOIN? Tabela de orientação Condição de junção “Falar é fácil. Mostre-me o código!” Linus Torvalds [376] Hands On! Projeto Orientação em Queries JOIN: LEFT e RIGHT JOIN [377] LEFT e RIGHT JOIN [378] CROSS JOIN Tabela 1: 10 linhas Tabela 2: 20 linhas “Falar é fácil. Mostre-me o código!” Linus Torvalds [379] Hands On! Projeto Entendendo melhor os tipos de JOINs [380] A tabela referenciada modifica o resultado da query INNER ou OUTER JOIN? [381] Fonte: dofactory.com A tabela referenciada modifica o resultado da query INNER ou OUTER JOIN? [382] Fonte: dofactory.com Só usa com OUTER JOIN? JOIN LEFT JOIN RIGTH JOIN Diferenças [383] INNER JOIN FULL OUTER JOIN FULL OUTER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN JOIN LEFT JOIN RIGTH JOIN Diferenças [384]INNER JOIN FULL OUTER JOIN FULL OUTER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN JOIN LEFT JOIN RIGTH JOIN Diferenças [385] INNER JOIN FULL OUTER JOIN FULL OUTER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN Interseção Matchs INNER ou OUTER JOIN? [386] Fonte: https://towardsdatascience.com/ JOIN LEFT JOIN RIGTH JOIN Diferenças [387] INNER JOIN FULL OUTER JOIN FULL OUTER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN CROSS JOIN & Diferenças [388] FULL OUTER JOIN CROSS JOIN & Tabela 1: 10 linhas Tabela 2: 20 linhas Diferenças [389] FULL OUTER JOIN CROSS JOIN & Tabela 1: 10 linhas Tabela 2: 20 linhas CROSS JOIN: 10*20 = 200 linhas Diferenças [390] FULL OUTER JOIN CROSS JOIN & Tabela 1: 10 linhas Tabela 2: 20 linhas CROSS JOIN: 10*20 = 200 linhas 5 linhas como combinação Diferenças [391] FULL OUTER JOIN CROSS JOIN & Tabela 1: 10 linhas Tabela 2: 20 linhas INNER JOIN: interseção = 5 linhas 5 linhas como combinação Diferenças [392] FULL OUTER JOIN CROSS JOIN & Tabela 1: 10 linhas Tabela 2: 20 linhas FULL OUTER JOIN: combinação = 25 linhas 5 linhas como combinação Explorando ainda mais: NATURAL JOIN [393] NATURAL JOIN [394] Preciso determinar a condição de junção? NATURAL JOIN entre R & S Condição de junção implícita Atributos comuns NATURAL JOIN [395] Preciso determinar a condição de junção? NATURAL JOIN entre R & S Condição de junção implícita Atributos comuns NATURAL JOIN [396] EMPLOYEE.Dno = DEPT.Dno Atributo com mesmo nome SELECT a.account_id, a.cust_id, c.cust_type_cd, c.fed_id FROM account a NATURAL JOIN customer c; NATURAL JOIN [397] EMPLOYEE.Dno = DEPT.Dno Atributo com mesmo nome NATURAL JOIN [398] EMPLOYEE.Dno = DEPT.Dno Atributo com mesmo nome Se se não houver atributos com mesmo nome? NATURAL JOIN [399] E o que acontece... “Falar é fácil. Mostre-me o código!” Linus Torvalds [400] Hands On! Projeto DESAFIO DE PROJETO Construindo seu Primeiro Projeto Lógico de BD [401] // Explorando a Linguagem de Consulta a Banco de Dados SQL Mapeamento do esquema ER para Relacional Definição do script SQL para criação do esquema de banco de dados Persistência de dados para testes Recuperação de informações com queries SQL Desafio de Projeto [402] E agora? E-commerce [403] Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. DESAFIO DE PROJETO Entendendo o Desafio [404] // Explorando a Linguagem de Consulta a Banco de Dados SQL Não há um mínimo de queries a serem realizadas Os tópicos supracitados devem estar presentes nas queries Elabore perguntas que podem ser respondidas pelas consultas As cláusulas podem estar presentes em mais de uma query Desafio de Projeto - Diretrizes [405] Desafio de Projeto - Diretrizes [406] Após a criação do esquema lógico, realize a criação do Script SQL para criação do esquema do banco de dados. Posteriormente, realize a persistência de dados para realização de testes. Especifique ainda queries mais complexas do que apresentadas durante a explicação do desafio. Desafio de Projeto - Diretrizes [407] Sendo assim, crie queries SQL com as cláusulas abaixo: Recuperações simples com SELECT Statement Filtros com WHERE Statement Crie expressões para gerar atributos derivados Defina ordenações dos dados com ORDER BY Condições de filtros aos grupos – HAVING Statement Crie junções entre tabelas para fornecer uma perspectiva mais complexa dos dados DESAFIO DE PROJETO Construindo um Projeto Lógico de BD do zero [408] // Explorando a Linguagem de Consulta a Banco de Dados SQL Desafio - Oficina [409] Agora você irá criar um esquema lógico a partir do esquema conceitual que criou para o cenário de uma oficia. Utilize o algoritmo de mapeamento apresentado para determinar qual a melhor opção para mapear seus componentes para modelo relacional. Pense que posteriormente você irá criar o esquema do banco de dados com base neste esquema lógico. Mapeamento ER/Relacional Relationship relation. Essa relação irá herdar as chaves primárias das entidades que a compõem. Mapeamento do esquema ER para Relacional Definição do script SQL para criação do esquema de banco de dados Persistência de dados para testes Recuperação de informações com queries SQL Desafio de Projeto [410] Passos do projeto Não há um mínimo de queries a serem realizadas Os tópicos supracitados devem estar presentes nas queries Elabore perguntas que podem ser respondidas pelas consultas As cláusulas podem estar presentes em mais de uma query Desafio de Projeto - Diretrizes [411] Referências Bibliográficas [412] > Fórum/Artigos > Comunidade Online (Discord) Dúvidas? [413] Livros de Referência ELMASRI, Ramez; B. NAVATHE, Shamkant. Sistema de Banco de Dados. 7. ed. São Paulo: Pearson, 2018. Livros complementares RODZVILLA, John. A Review of “Learning SQL” Beaulieu, Alan. Sebastapol, CA: O’Reilly, 2009, 320 pp., $39.99, ISBN 978-0-596-52083-0. 2010. TEOREY, Toby J. Database modeling and design. Morgan Kaufmann, 1999. NIELD, Thomas. Getting Started with SQL: A Hands-On Approach for Beginners. " O'Reilly Media, Inc.", 2016. Para saber mais [414] Documentação Oficial Documentação: http://dev.mysql.com/doc/ Download: http://dev.mysql.com/downloads/ Site oficial : http://www.mysql.com/ Suporte: http://www.mysql.com/support/ https://dev.mysql.com/doc/refman/8.0/en/preface.html Links Úteis [415] https://support.microsoft.com/pt-br/office/acesso-sql-conceitos-b%C3%A1sicos-vocabul%C3%A1rio-e-sintaxe-444d0303-cde1-424e-9a74-e8dc3e460671#:~:text=SQL%20cl%C3%A1usulas,as%20cl%C3%A1usulas%20SQL%20mais%20comuns. https://www.analyticsvidhya.com/blog/2020/11/guide-data-types-mysql-data-science-beginners/#:~:text=LONGTEXT%20can%20store%20the%20maximum,any%20long%2Dform%20text%20strings. https://dev.mysql.com/doc/refman/8.0/en/create-database.html https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql https://basedosdados.org/dataset/br-me-caged?bdm_table=microdados_antigos Links Úteis [416] https://docs.microsoft.com/pt-br/sql/relational-databases/tables/add-columns-to-a-table-database-engine?view=sql-server-ver16 https://www.mysqltutorial.org/mysql-primary-key/#:~:text=Because%20MySQL%20works%20faster%20with,that%20the%20table%20may%20have. https://www.c-sharpcorner.com/article/the-complete-reference-set-operations-in-ms-sql-union-all-intersect-excep/#:~:text=UNION%20ALL%20combines%20two%20or,in%20the%20second%20result%20set. https://dotnettutorials.net/lesson/differences-between-union-except-and-intersect-operators-in-sql-server/#:~:text=UNION%20ALL%3A%20Combine%20two%20or,no%20matching%20to%20each%20other) Links Úteis [417] > Fórum/Artigos > Comunidade Online (Discord) Dúvidas? [418] .MsftOfcThm_Accent5_lumMod_50_Fill_v2 { fill:#004B53; } .MsftOfcThm_Accent5_lumMod_50_Stroke_v2 { stroke:#004B53; } .MsftOfcResponsive_Stroke_ea4e60 { stroke:#EA4E60; }
Compartilhar