Arquivos de Categoria: Provas

Using T-SQL in a relational way

(P. 5) Como mencionado, o T-SQL é baseado em SQL, que por sua vez é baseado no modelo relacional. Contudo,há várias maneiras pelas quais o SQL e, portanto, o T-SQL, se desviam do modelo relacional. Mas o T-SQL oferece ferramentas suficientes para que, se você entenda o modelo relacional, e você pode usar a linguagem de forma relacional e, assim, escrever um código mais correto.

Lembre-se de que uma relação tem um título e um corpo. O título é um conjunto de atributos e o corpo é um conjunto de tuplas. Lembre-se de que um conjunto deve ser considerado como um todo.

(P. 6) O que isso se traduz em T-SQL é que você deveria escrever consultas que interagem com as tabelas como um todo. Você deve tentar evitar o uso de construções iterativas como cursores e loops que iteram pelas linhas uma de cada vez. Você também deve tentar evitar pensar em termos iterativos porque esse tipo de pensamento é o que leva a soluções iterativas.Para pessoas com um pouco de programação processual, a maneira natural de interagir com dados (em um arquivo, conjunto de registros ou leitor de dados) é com iterações. Então, usar cursores e outras construções iterativas no T-SQL é, de certo modo, uma extensão para o que eles já conhecem. No entanto, a maneira correta da perspectiva do modelo relacional não é interagir com as linhas uma de cada vez, em vez disso, usar operações relacionais e retornar um resultado relacional. Isso, no T-SQL, se traduz em questões da escrita.

Anúncios

Understanding the foundations of T-SQL

(P. 2)  Apesar do T-SQL ser uma linguagem para o SQL Server, o mesmo possui fortes fundamentos matemáticos. É importante entendermos essas bases fundamentais para melhor entender a linguagem que estamos lidando.

Evolução do T-SQL

O T-SQL é a principal linguagem para manipular dados no SQL Server e no Azure SQL. A figura a seguir dá uma idéia de como foi a evolução do T-SQL:

ScreenHunter_450 Jan. 25 12.44

T-SQL é o dialeto padrão do SQL Server. É padronizado pela ISO (Internation Organization os Standards) e pela ANSI (American National Standards Institute. A lista dos padrões revisados são:

■ SQL-86
■ SQL-89
■ SQL-92
■ SQL:1999
■ SQL:2003
■ SQL:2006
■ SQL:2008
■ SQL:2011

Todos os principais fornecedores de banco de dados, incluindo a Microsoft, implementam um dialeto de SQL como o idioma principal para gerenciar e manipular dados em suas plataformas de banco de dados. Portanto, os principais elementos principais da linguagem são os mesmos. No entanto, cada fornecedor decide quais recursos implementar e quais recursos não devem ser implementados. Além disso, o padrão às vezes deixa alguns aspectos como uma escolha da implementação. Cada fornecedor também geralmente implementa extensões para o padrão nos casos em que o fornecedor sente que uma característica importante não é coberta pelo padrão.

Escrever códigos no padrão é considerada uma boa prática, por ser mais portável. Relação no modelo Relacional é o que o SQL representa em forma de tabela.

Mas é importante notar que, conforme você compreende os princípios do modelo,
você pode usar o SQL – ou mais precisamente, o dialecto que você está usando – de forma relacional.

Voltando a uma relação, o que o SQL tenta representar com uma tabela: uma relação tem um título e um corpo. O título é um conjunto de atributos (o que o SQL tenta representar com as colunas), cada um de um determinado tipo. Um atributo é identificado pelo nome e pelo nome do tipo. O corpo é um conjunto de tuplas (o que o SQL tenta representar com as linhas). Cada título da tupla é o título da relação. Cada valor do atributo de cada tupla é do seu tipo respectivo.Alguns dos aspectos mais importantes para entender sobre T-SQL derivam do relacionalfundamentos principais do modelo: teoria de conjuntos e lógica de predicado.Lembre-se de que o título de uma relação é um conjunto de atributos, e o corpo é um conjunto detuplas. Então, o que é um conjunto? De acordo com o criador da teoria dos conjuntos matemáticos, Georg Cantor, um conjunto é descrito da seguinte forma:Por um “conjunto”, queremos dizer qualquer coleção M em um conjunto de objetos definidos e distintosm (que são chamados de “elementos” de M) de nossa percepção ou de nosso pensamento.

—George Cantor, in
“Georg Cantor” by Joseph
W. Dauben (Princeton
University Press, 1990)

(P. 5) Há uma série de elementos muito importantes nesta definição que, se entendido,
deve ter implicações diretas em suas práticas de codificação T-SQL. Um elemento que requer atenção é o termo inteiro. Um conjunto deve ser considerado como um todo. Isso significa que você não interage com os elementos individuais do conjunto, em vez do conjunto como um todo.
Observe o termo distinto; um conjunto não tem duplicatas. Codd observou uma vez o aspecto não duplicado: “Se algo for verdadeiro, então, dito duas vezes não o tornará mais verdadeiro”. Por exemplo, o conjunto {a, b, c} é considerado igual ao conjunto {a, a , b, c, c, c}.
Outro aspecto crítico de um conjunto não aparece explicitamente na definição acima mencionada por Cantor, mas está implícito; não há relevância para a ordem dos elementos em um conjunto. Em contraste, uma seqüência (que é um conjunto ordenado), tem uma ordem para seus elementos. Combinando as duplicações e nenhuma relevância para os aspectos de ordem significa que a coleção {a, b, c} é um conjunto, mas a coleção {b, a, c, c, a, c} não é.

O outro ramo da matemática com o qual o modelo relacional é chamado de lógica de predicado. Um predicado é uma expressão que, quando atribuída a algum objeto, faz uma proposição verdadeira ou falsa. Por exemplo, “salário superior a US $ 50.000” é um predicado. Você pode avaliar este predicado para um empregado específico, caso em que você tem uma proposição. Por exemplo, suponha que, para um empregado em particular, o salário seja de US $ 60.000. Quando você avalia a proposição para esse funcionário, você obtém uma verdadeira proposição. Em outras palavras, um predicado é uma proposição parametrizada.
O modelo relacional usa predicados como um dos seus elementos principais. Você pode impor integridade de dados usando predicados. Você pode filtrar dados usando predicados. Você pode até usar predicados para definir o próprio modelo de dados. Você primeiro identifica proposições que precisam ser armazenadas no banco de dados. Aqui está uma proposição de exemplo: um pedido com a ID de pedido 10248 foi colocado em 12 de fevereiro de 2017 pelo cliente com ID 7 e manipulado pelo empregado com ID 3. Você então cria predicados das proposições removendo os dados e mantendo o título.
Lembre-se, o título é um conjunto de atributos, cada um identificado pelo nome e nome do tipo. Neste exemplo, você ordenou INT, ordenou DATE, custid INT e INT empid.

Banco de Dados I – Aula 16A

Exercícios SQL

A empresa ABC VENDASCAR LTDA é uma emprea que atua em vendas de carros seminovos e usados multimarcas, principalmente em São Paulo (Capital) e no grande ABC.

Atualmente a empresa implantou o seguinte sistema para melhor controle seus orcamentos e suas vendas. Como a empresa NÃO faz vendas exclusiva de um fabricante, ela optou por trabalhar com veículos dos fabricantes Volkswagem, Chevrolet (GM), Ford e Fiat, para que não fique preso à uma única marca.

O cliente pode ser tanto pessoa física ou juridica, a qual na compra de mais de um carro em uma única negociação, é possível ter descontos mais vantajosos.

Primeiro é necessário que o cliente tenha um pré-cadastro no sistema para que os vendedores apresentem um orcamento prévio. Caso seja aprovado o orcamento, aí sim o orcamento se transforma em uma venda efetiva.

A empresa tem o seguinte esquema de tabelas em seu banco de dados:

FABRICANTE (COD_FAB, DESC_FAB)

MODELO (COD_MOD, DESC_MOD, COD_FAB)

COR (COD_COR, DESC_COR)

VEICULO (COD_VEI, COD_MOD, ANO_FABRICACAO, ANO_MODELO, VALOR_CUSTO, VALOR_VENDA, COD_COR)

CIDADE (COD_CID, DESC_CID, UF)

PLACA_VEICULO (COD_VEI, PLACA, COD_CID)

CLIENTE (COD_CLI, NOME_CLI, TIPO_PESSOA, COD_CID)

ORCAMENTO (COD_ORC, DATA_ORC, VALIDADE_ORC, COD_CLI)

ITEM_ORCAMENTO (COD_ORC, ITEM_ORC, COD_VEI, QUANTIDADE, VALOR_ORC, VALOR_DESCONTO)

VENDA (COD_VENDA, COD_ORC, DATA_VENDA)

ITEM_VENDA (COD_VENDA, ITEM_VENDA, COD_VEI, QUANTIDADE, VALOR_VENDA)

 

Baseado no cenário descrito, faça os exercícios em SQL e também crie o schema VENDASCAR, com as tabelas descritas acima. Por enquanto, as tabelas irão estar sem os relacionamentos necessários, porém tenha ciência de que os dados precisam ter relação entre si. Aconselhável fazer em dupla, onde 1 aluno pode fazer a criação do schema e tabelas e outro pode fazer as consultas SQL.

  1. Selecione o nome do veículo que tenha menor custo do estoque; (função de grupo)
  2. Liste todos os nomes de fabricantes de veículos que tiveram orcamentos feitos no período de 01/06/2017 a 30/06/2017;
  3. Selecione o cliente que mais orçou veículos no período de 01/01/2017 a 30/06/2017;
  4. Selecione as cores dos 5 veículos mais orçados período de 01/01/2017 a 30/06/2017; (pesquisar sobre limitação de registros)
  5. Liste as cidades dos carros vendidos dos fabricantes Volkswagem e Chevrolet (GM), no período de venda de 01/01/2017 a 31/12/2017;
  6. Liste os veículos (placa, cidade, fabricante, modelo) da cidade de Jundiaí, Campinas e São Paulo que foram vendidos entre 01/07/2017 a 31/12/2017;
  7. Selectione os orcamentos feitos (Código do orcamento, data, fabricante, modelo, quantidade, valor, desconto, subtotal (quantidade * valor – desconto) feitos no período do ano de 2016;
  8. Selecione os 5 veículos mais vendidos no período do ano de 2016; (use funções de Grupo)
  9. Selecione os orcamentos feitos no período de 01/01/2017 a 31/03/2017, porém que não foram fechados;
  10. Selecione a média de vendas de junho, julho e agosto de 2017; (função de grupo)

 

Vídeo 01

 

Vídeo 02

 

Banco de Dados I – Aula 15A

FUNÇÕES SQL

As funções são um recurso avançado do SQL e podem ser usadas para realizar:

– Executar cálculos usando dados;

– Modificar itens de dados individuais;

– Manipular saída para grupos de linhas;

– Formatar datas e números para exibição;

– Converter tipos de dados de coluna;

As funções SQL podem aceitar argumentos e sempre retornar um valor;

Há dois tipos de funções:

– Funções de uma única linha;

– Funções de várias linhas;

FUNÇÕES DE UMA ÚNICA LINHA

Essas funções operam somente linhas únicas e retornam um resultado por linha. Há dois tipos diferentes de funções de uma única linha.

– Caractere;

– Número;

– Data;

– Conversão;

Essas funções manipulam grupos de linha a fim de obter um resultado por grupo de linhas.

– Manipulam itens de dados;

– Aceitam argumentos e retornam um valor;

– Agem em cada linha retornada;

– Retornam um resultado por linha;

– Podem modificar o tipo de dados;

– Podem ser aninhadas;

Um argumento pode ser:

– Constante fornecida pelo usuário;

– Valor variável;

– Nome da coluna;

– Expressão;

Os recursos de funções de uma única linha:

– Atuam em cada linha retornada na consulta;

– Retornam um resultado por linha;

– Podem retornar um valor de dados de um tipo diferente do mencionado;

– Podem esperar um ou mais argumentos;

– Podem ser usados em cláusulas SELECT, WHERE e ORDER BY; podem ser aninhados;

– Funções de caractere: aceitam entrada de caractere e podem retornar valores de número e caractere;

– Funções numéricas: aceitam entrada numérica e reotrnam valores numéricos;

– Funções de data: operam sobre valores do tipo de dados data (todas as funções de data retornam data, exceto a função MONTHS_BETWEEN, que retorna um número)

– Funções de conversão: Convertem um valor de tipo de dados para outro;

– Funções gerais:

  • Função NVL;
  • Função DECODE;

FUNÇÕES DE CARACTERE

Podem ser divididas em duas características:

–Funções de conversão de maiúscula e minúscula;

  • LOWER, UPPER e INITCAP

–Funções de manipulação de caractere;

  • CONCAT, SUBSTR, LENGTH, INSTR, LPAD, TRIM

FUNÇÕES DE CONVERSÃO DE MAIÚSCULAS / MINÚSCULAS

– LOWER: Converte string de caracteres com letras maiúsculas e minúsculas ou só maiúsculas para letras minúsculas;

– UPPER: Converte string de caracteres com letras maiúculas e minúsculas ou só minúsculas para letras maiúsculas;

– INITCAP: Converte a primeira letra de cada palavra para maiúscula e mantém as outras letras em minúsculas;

SQL> SELECT UPPER(NMDEPTO)
2  FROM DEPTO;

SQL> SELECT LOWER(NMDEPTO)
2  FROM DEPTO;

SQL> SELECT INITCAP(NMDEPTO)
2  FROM DEPTO;

SQL> SELECT UPPER(NMDEPTO)
2  FROM DEPTO
3  WHERE UPPER(NMDEPTO) LIKE '%A%';

FUNÇÕES DE MANIPULAÇÃO DE CARACTERE

– CONCAT, SUBSTR, LENGTH, INSTR, LPAD e TRIM são as seis funções de manipulação de caractere;

– CONCAT: Une valores de junção (Voce está limitado a usar dois parâmetros com CONCAT);

– SUBSTR: Extrai uma string de determinado tamanho;

– LENGTH: Exibe o tamanho de uma string como um valor numérico;

– INSTR: Localiza a posição numérica do caractere nomeado;

– LPAD: Preenche o valor do caractere justificado à direita;

– RPAD: Preenche o valor de caractere justificado à esquerda;

– TRIM: Organiza cabeçalho ou caracteres de fim de linha (ou os dois) a partir de uma string de caractere;

SQL> SELECT CONCAT(NRMATRIC, NMFUNC),
2          LENGTH(NMFUNC),
3          INSTR(NMFUNC,’A’)
4  FROM FUNCIONARIO
5  WHERE SUBSTR(NMDEPTO,1,2) = 'JO';

FUNÇÕES NUMÉRICAS

ROUND: Arredonda valor para determinado decimal;

  •   ROUND(45.926,2) –> 45.93

TRUNC: Trunca valor para determinado decimal;

  •   ROUND(45.926,2) –> 45.92

MOD: Retorna o restante da divisão;

  •   MOD(1600,300) –> 100

USANDO A FUNÇÃO ROUND

SQL> SELECT ROUND(45.923,2),
2       ROUND(45.923,0),
3       ROUND(45.923,-1)
4  FROM DUAL;

USANDO A FUNÇÃO TRUNC

SQL> SELECT TRUNC(45.923,2),
2       TRUNC(45.923),
3       TRUNC(45.923,-1)
4  FROM DUAL;

USANDO A FUNÇÃO MOD

SQL> SELECT MOD(VRSALARIO,2)
2  FROM CARGO;

TRABALHANDO COM DATAS

O Oracle armazena datas em um formato numérico interno: século, ano, mês, dia, horas, minutos, Segundo;

O formato de data default é DD-MON-YY;

SYSDATE é uma função de retorno de data e hora;

DUAL é uma tabela fictícia usada para visualizar SYSDATE;

EXIBINDO A DATA ATUAL

SQL> SELECT SYSDATE
2  FROM DUAL;

ARITMÉTICA COM DATAS

Adicionar ou subtrair um número de, ou para, uma data para um valor de data resultante;

Subtrair duas datas a fim de localizer o número de dias entre estras datas;

Adicionar horas por uma data dividindo o número de horas por 24;

ARITMÉTICA COM DATAS

ScreenHunter_298 Nov. 11 19.31

USANDO OPERADORES ARITMÉTICOS COM DATAS

SQL> SELECT NMFUNC,
2    (SYSDATE – DTADM) / 7 SEMANA
3  FROM FUNCIONARIO;

FUNÇÕES COM DATAS

ScreenHunter_299 Nov. 11 19.32

FUNÇÕES COM DATAS

MONTHS_BETWEEN('01-SEP-95','11-JAN-94')
--> 19.6774194

ADD_MONTHS('11-JAN-96',6)
--> ’11-JUL-94’

NEXT_DAY('01-SEP-95','FRIDAY')
--> '08-SEP-95'

LAST_DAY('01-SEP-95')
--> '30-SEP-95'

ROUND('25-JUL-95','MONTH')
--> 01-AUG-95

ROUND('25-JUL-95','YEAR')
--> 01-JAN-96

TRUNC('25-JUL-95','MONTH')
--> 01-JUL-95

TRUNC('25-JUL-95','YEAR')
--> 01-JAN-95

 

Vídeo 01

Banco de Dados I – Aula 14A

EXIBINDO DADOS DE VÁRIAS TABELAS

Há situações em que precisamos buscar dados de várias tabelas, onde um select simples não consegue obter esses dados;

Nesses casos, é necessário utilizar dados a partir de uma ou mais tabelas;

Para isso, precisamos fazer junção de tabelas;

O QUE É JUNÇÃO?

Use a junção para consultar dados a partir de uma ou mais tabelas;

Criar uma condição de junção na cláusula WHERE;

Prefixar o nome da coluna com o nome da tabela quando o mesmo nome da coluna aparecer em mais de uma tabela

Exemplo de junção

SQL> SELECT tabela1.coluna, tabela2.coluna
2  FROM tabela1, tabela2
3  WHERE tabela1.coluna = tabela2.coluna;

 

USANDO O SCHEMA HR DO ORACLE

hr

PRODUTO CARTESIANO

Um produto cartesiano é formado quando:

– Uma condição de junção estiver omitida;

– Uma condição de junção estiver inválida;

– Todas as linhas na primeira tabela estão unidas a todas as linhas da segunda tabelas;

Para evitar um produto Cartesiano, sempre inclua uma condição de junção válida em uma cláusula WHERE;

GERANDO UM PRODUTO CARTESIANO

Gera-se um produto cartesiano caso uma condição de junção seja omitida.

SQL> SELECT FIRST_NAME,
  2           DEPARTMENT_NAME
  3    FROM   EMPLOYEES,
  4           DEPARTMENTS;

 

TIPOS DE JUNÇÃO

Há dois tipos principais de condições de junção:

– Junção idênticas;

– Junção não-idêntcias;

Métodos de junção adicional incluem:

– Junções externas;

– Autojunções;

– Operadores de conjunto;

 

JUNÇÃO IDÊNTICA

ScreenHunter_393 Nov. 06 12.59

RECUPERANDO REGISTROS COM JUNÇÕES IDÊNTICAS

SQL>SELECT  FIRST_NAME,
            EMPLOYEES.DEPARTMENT_ID,
            DEPARTMENTS.DEPARTMENT_ID,
            DEPARTMENT_NAME
    FROM    EMPLOYEES, DEPARTMENTS
    WHERE   EMPLOYEES.DEPARTMENT_ID =
                      DEPARTMENTS.DEPARTMENT_ID;

QUALIFICANDO NOMES DE COLUNA AMBÍGUAS

Use os prefixos de tabela para qualificar nomes de colunas que estão em várias tabelas;

Diferencie colunas que possuem nomes idênticos, mas que residam em tabelas diferentes usando apelidos de coluna;

CONDIÇÕES DE PESQUISA ADICIONAIS USANDO O OPERADOR AND

Além da junção, é possível ter critérios para a cláusula WHERE;

SELECT   EMPLOYEE_ID, FIRST_NAME,
         DEPARTMENT_NAME
FROM     EMPLOYEES, DEPARTMENTS
WHERE    EMPLOYEES.DEPARTMENT_ID =
                       DEPARTMENTS.DEPARTMENT_ID
         AND FIRST_NAME = 'Diana';

USANDO APELIDOS DE TABELA

Apelidos de tabelas podem ter um tamanho de até 30 caracteres;

Se um apelido de tabela for usado para um determinado nome de tabela na cláusula FROM, deve-se utilize-lo para toda a instrução SELECT;

Apelidos devem ser significativos;

O apelido de tabela é válido somente para a instrução SELECT atual;

 

USANDO APELIDOS DE TABELA

SELECT   E.FIRST_NAME,
         J.JOB_TITLE,
         D.DEPARTMENT_NAME
FROM     EMPLOYEES E,
         JOBS J,
         DEPARTMENTS D
WHERE    E.JOB_ID = J.JOB_ID
         AND E.DEPARTMENT_ID =   D.DEPARTMENT_ID;

 

JUNÇÕES NÃO-IDÊNTICAS

O relacionamento de junção não-idênticas  significa que não há nenhum coluna correspondente diretamente entre as duas tabelas;

SELECT    E.FIRST_NAME, E.LAST_NAME,
          E.SALARY, J.MIN_SALARY,   J.MAX_SALARY,
          J.JOB_TITLE
FROM      EMPLOYEES E, JOBS J
WHERE     E.SALARY
          BETWEEN J.MIN_SALARY AND   J.MAX_SALARY;

 
Se uma linha não satisfazer uma condição de junção, a linha não aparecerá no resultado da consulta.

ScreenHunter_394 Nov. 06 13.03

Use uma junção externa para consultar também todas as linhas que em geral não atendem à condição de junção.

O operador de junção externo é um sinal de adição (+);

EXEMPLO DE JUNÇÕES EXTERNAS

SELECT   E.FIRST_NAME,
         D.DEPARTMENT_NAME
FROM     EMPLOYEES E,
         DEPARTMENTS D
WHERE    E.DEPARTMENT_ID(+) =
                          D.DEPARTMENT_ID

 
O operador da junção externa pode aparecer somente de um lado da expressão – o lado que possui informações ausentes. Ele retorna estas linhas de uma tabela que não possui correspondênciadireta em outra tabela;

Uma condição envolvendo uma junção externa não pode usar o operador IN ou vincular a outra condição pelo operador OR;

 

AUTOJUNÇÕES

Algumas vezes será necessário unir uma tabela a ela mesma.

ScreenHunter_395 Nov. 06 13.05

EXEMPLO DE AUTOJUNÇÕES

SELECT   WORKER.FIRST_NAME
         ||' TRABALHA PARA ' ||
         MANAGER.FIRST_NAME
FROM     EMPLOYEES WORKER,
         EMPLOYEES MANAGER
WHERE    WORKER.MANAGER_ID =
                       MANAGER.EMPLOYEE_ID;

 

Vídeo 01

learningdatabase.com.br

Tecnologias em Banco de Dados Relacionais, Modelagem de dados dimencionais, tecnologias SQL Servere e Oracle

Aprendendo Programação

Algorítmos, Linguagem C, C++,Pascal, Python

WikiDBA

by Virendra Yaduvanshi - Microsoft SQL Server Database Architect | Consultant | Blogger | Specialist | DBA | Speaker

Blog - Fabiano Neves Amorim

SELECT * FROM [Coisas Da Minha Cabeça] WHERE dbo.fn_TempoParaPost() < dbo.fn_TempoLivre()

ROMANO DBA

Administração de Bancos de Dados

Tércio Costa, Oracle Developer, OCE SQL

Guia de estudos para certificação ORACLE SQL Expert(1Z0-047) e PL/SQL(1Z0-144)

Strate SQL

Data Adventures with an Architect