Funções agregadas do sistema de composição de dados. Usando funções agregadas e consultas aninhadas em uma instrução Select SQL Outer Joins

SQL adicionou funções adicionais que permitem calcular valores de grupo generalizados. Para aplicar funções agregadas, é assumida uma operação de agrupamento preliminar. Qual é a essência da operação de agrupamento? Ao agrupar, todo o conjunto de tuplas de uma relação é dividido em grupos nos quais são coletadas tuplas que possuem os mesmos valores de atributos especificados na lista de agrupamento.

Por exemplo, vamos agrupar a relação R1 pelo valor da coluna Disciplina. Obteremos 4 grupos para os quais podemos calcular alguns valores de grupo, como o número de tuplas no grupo, o valor máximo ou mínimo da coluna Pontuação.

Isso é feito usando funções agregadas. As funções agregadas calculam um único valor para um grupo inteiro de tabelas. Uma lista dessas funções é apresentada na Tabela 5.7.

Tabela 5.7.Funções agregadas


R1
	Nome completo	Disciplina	Nota
Grupo 1	Petrov F.I.	Base de dados
	Sidorov K. A.	Base de dados
	Mironov A.V.	Base de dados
	Stepanova K. E.	Base de dados
	Krylov T. S.	Base de dados
	Vladimirov V. A.	Base de dados
Grupo 2	Sidorov K. A.	Teoria da informação
	Stepanova K. E.	Teoria da informação
	Krylov T. S.	Teoria da informação
	Mironov A.V.	Teoria da informação	Nulo
Grupo 3	Trofimov P.A.	Redes e telecomunicações
	Ivanova E. A.	Redes e telecomunicações
	Utkina N.V.	Redes e telecomunicações
Grupo 4	Vladimirov V. A.	língua Inglesa
	Trofimov P.A.	língua Inglesa
	Ivanova E. A.	língua Inglesa
	Petrov F.I.	língua Inglesa	eu

As funções agregadas são usadas de forma semelhante aos nomes de campo em uma instrução SELECT, com uma exceção: elas usam o nome do campo como argumento. Somente campos numéricos podem ser usados com as funções SUM e AVG. Os campos numéricos e de caracteres podem ser usados com as funções COUNT, MAX e MIN. Quando usados com campos de caracteres, MAX e MIN irão traduzi-los para o código ASCII equivalente e processá-los em ordem alfabética. Alguns SGBDs permitem o uso de agregações aninhadas, mas isso é um desvio do padrão ANSI com todas as consequências decorrentes.

Por exemplo, você pode calcular o número de alunos que realizaram provas em cada disciplina. Para isso, é necessário realizar uma consulta agrupada pelo campo “Disciplina” e exibir como resultado o nome da disciplina e a quantidade de linhas do grupo desta disciplina. Usar o caractere * como argumento para a função COUNT significa contar todas as linhas do grupo.

SELECIONE R1.Disciplina. CONTAR(*)

GRUPO POR Disciplina R1

Resultado:

Se quisermos contar o número de pessoas que passaram no exame em qualquer disciplina, precisamos excluir valores incertos da proporção original antes de agrupar. Neste caso, a solicitação ficará assim:

SELECIONE R1.Disciplina. CONTAR(*)

DE R1 ONDE R1.

Avaliação NÃO É NULA

GRUPO POR Rl.Disciplina

Obtemos o resultado:

Neste caso, a linha com o aluno


Mironov A, V.	Teoria da informação	Nulo

não será incluído no conjunto de tuplas antes do agrupamento, portanto o número de tuplas no grupo da disciplina “Teoria da Informação” será 1 a menos.

Você também pode usar funções agregadas sem a operação de pré-agrupamento; nesse caso, toda a relação é tratada como um grupo e para esse grupo você pode calcular um valor por grupo.

Voltando novamente à base de dados “Sessão” (tabelas Rl, R2, R3), encontramos o número de exames aprovados com sucesso:

ONDE Pontuação > 2:

É claro que isso é diferente de selecionar um campo, pois sempre retorna um único valor, não importa quantas linhas existam na tabela. Os argumentos para agregar funções podem ser colunas individuais da tabela. Mas para calcular, por exemplo, o número de valores distintos de uma determinada coluna de um grupo, é necessário usar a palavra-chave DISTINCT junto com o nome da coluna. Vamos calcular o número de notas diferentes recebidas em cada disciplina:

SELECIONE Rl.Disciplina.

CONTAGEM(Pontuação R1 DISTINTA)

ONDE R1.Avaliação NÃO É NULA

GRUPO POR Rl.Disciplina

Resultado:

O resultado pode incluir um valor de campo de agrupamento e diversas funções agregadas, e as condições de agrupamento podem usar diversos campos. Nesse caso, os grupos são formados com base em um conjunto de campos de agrupamento especificados. As operações de função agregada podem ser aplicadas para unir várias tabelas de origem. Por exemplo, vamos colocar a questão: determine para cada turma e cada disciplina o número de alunos aprovados no exame e a nota média da disciplina.

SELECIONE R2.Grupo. R1.Disciplina. CONTAGEM(*), AVP(Avaliação)

ONDE Rl.nome completo = R2.nome completo E

Rl.Avaliação NÃO É NULA E

Rl.Classificação > 2

GRUPO POR R2.Grupo. Rl.Disciplina

Resultado:

Não podemos usar funções agregadas na cláusula WHERE porque os predicados são avaliados em termos de uma única linha e as funções agregadas são avaliadas em termos de grupos de linhas.

A cláusula GROUP BY permite definir um subconjunto de valores em um determinado campo em termos de outro campo e aplicar uma função agregada ao subconjunto. Isso torna possível combinar campos e agregar funções em uma única cláusula SELECT. As funções agregadas podem ser usadas tanto na expressão para saída dos resultados da linha SELECT, quanto na expressão para a condição de processamento dos grupos HAVING formados. Neste caso, cada função agregada é calculada para cada grupo selecionado. Os valores obtidos no cálculo das funções agregadas podem ser utilizados para visualizar os resultados correspondentes ou para condicionar a seleção dos grupos.

Vamos construir uma consulta que exiba grupos em que mais de uma nota ruim foi obtida em uma disciplina nas provas:

SELECIONE R2.Grupo

ONDE Rl.nome completo = R2.nome completo E

Rl.Classificação = 2

GRUPO POR R2.Grupo. R1.Disciplina

TENDO contagem(*)> 1

Futuramente, a título de exemplo, trabalharemos não com o banco de dados “Sessão”, mas sim com o banco de dados “Banco”, composto por uma tabela F, que armazena a relação F contendo informações sobre contas em agências de um determinado banco:

F = ;

Q = (Filial, Cidade);

pois com base nisso é possível ilustrar mais claramente o trabalho com funções agregadas e agrupamento.

Por exemplo, suponha que queiramos encontrar o saldo total das contas das agências. Você pode fazer uma consulta separada para cada um deles selecionando SUM(Balance) na tabela de cada filial. GROUP BY, entretanto, permitirá que você coloque todos eles em um comando:

SELECIONE Filial, SOMA

GRUPO POR Filial:

GROUP BY aplica funções agregadas de forma independente para cada grupo identificado pelo valor do campo Filial. Um grupo é composto por linhas com o mesmo valor do campo Filial, e a função SUM é aplicada separadamente para cada grupo, ou seja, o saldo total da conta é calculado separadamente para cada filial. O valor de um campo ao qual GROUP BY é aplicado possui, por definição, apenas um valor por grupo de saída, assim como o resultado de uma função agregada. Portanto, podemos combinar uma agregação e um campo em uma solicitação. Você também pode usar GROUP BY com vários campos.

Vamos supor que gostaríamos de ver apenas os saldos totais das contas que excedem US$ 5.000. Para ver os saldos totais acima de US$ 5.000, você deve usar a cláusula HAVING. A cláusula HAVING especifica os critérios usados para remover certos grupos da saída, assim como a cláusula WHERE faz para linhas individuais.

O comando correto seria:

SELECIONE Filial, SUM (Remanescente)

Agrupar por filial

TENDO SOMA(Restante) > 5000;

Os argumentos em uma cláusula HAVING seguem as mesmas regras de uma cláusula SELECT que usa GROUP BY. Eles devem ter um valor por grupo de saída.

O seguinte comando será proibido:

SELECIONE Filial.SUM (Remanescente)

DO GRUPO F POR Filial

HAVINGOpenDate = 27/12/1999;

O campo OpenDate não pode ser usado em uma cláusula HAVING porque pode ter mais de um valor por grupo de saída. Para evitar esta situação, a cláusula HAVING deve referenciar apenas os agregados e campos selecionados por GROUP BY. Existe uma maneira correta de fazer a consulta acima:

SELECIONE Filial,SUM(Restante)

WHEREOpenDate = "27/12/1999"

AGRUPAMENTO POR Filial;

O significado desta consulta é o seguinte: encontre a soma dos saldos de cada agência de contas aberta em 27 de dezembro de 1999.

Conforme declarado anteriormente, HAVING só pode aceitar argumentos que tenham um valor por grupo de saída. Na prática, as referências a funções agregadas são as mais comuns, mas os campos selecionados usando GROUP BY também são válidos. Por exemplo, queremos ver os saldos totais nas contas das filiais em São Petersburgo, Pskov e Uryupinsk:

SELECIONE Filial.SUM (Remanescente)

ONDE F.Branch = Q.Branch

Agrupar por filial

TENDO Filial IN ("São Petersburgo". "Pskov". "Uryupinsk");

Portanto, apenas as especificações das colunas especificadas como colunas de agrupamento na cláusula GROUP BY podem ser usadas diretamente em expressões aritméticas de predicado incluídas na cláusula de seleção da cláusula HAVING. As colunas restantes só podem ser especificadas dentro das especificações das funções agregadas COUNT, SUM, AVG, MIN e MAX, que neste caso calculam algum valor agregado para todo o grupo de linhas. A situação é semelhante com as subconsultas incluídas nos predicados da condição de seleção da seção HAVING: se a subconsulta utiliza uma característica do grupo atual, então ela só pode ser especificada referindo-se às colunas de agrupamento.

O resultado da cláusula HAVING é uma tabela agrupada contendo apenas os grupos de linhas para os quais a condição de pesquisa é avaliada como TRUE. Em particular, se uma cláusula HAVING estiver presente em uma expressão de tabela que não contém um GROUP BY, então o resultado de sua execução será uma tabela vazia ou o resultado da execução das seções anteriores da expressão de tabela, tratada como uma única agrupar sem agrupar colunas.

Consultas SQL aninhadas

Agora vamos retornar ao banco de dados “Sessão” e ver seu exemplo de uso de consultas aninhadas.

Com SQL você pode aninhar consultas umas dentro das outras. Normalmente, a consulta interna gera um valor que é testado no predicado da consulta externa (na cláusula WHERE ou HAVING) para determinar se é verdadeiro ou falso. Em conjunto com uma subconsulta, você pode usar o predicado EXISTS, que retorna verdadeiro se a saída da subconsulta não estiver vazia.

Quando combinada com outros recursos do operador select, como agrupamento, uma subconsulta é uma ferramenta poderosa para alcançar o resultado desejado. Na parte FROM da instrução SELECT, é permitido aplicar sinônimos aos nomes das tabelas se, ao formar uma consulta, precisarmos de mais de uma instância de uma determinada relação. Os sinônimos são especificados usando a palavra-chave AS, que pode ser totalmente omitida. Portanto, a parte FROM pode ficar assim:

DE Rl COMO A, Rl COMO B

DE Rl A. Rl B:

ambas as expressões são equivalentes e são consideradas aplicações da instrução SELECT a duas instâncias da tabela R1.

Por exemplo, vamos mostrar como são algumas consultas ao banco de dados “Sessão” em SQL:

Lista daqueles que passaram em todos os exames exigidos.

ONDE Pontuação> 2

TENDO CONTAGEM(*) = (SELECIONE CONTAGEM(*)

ONDE R2.Group=R3.Group E nome completoa.nome completo)

Aqui, a consulta integrada determina o número total de exames que cada aluno da turma do aluno deve realizar e compara esse número com o número de exames que o aluno fez.

Uma lista de quem deveria fazer o exame de banco de dados, mas ainda não o fez.

SELESTFIO

ONDE R2.Fpynna = R3.Group AND Discipline = "DB" E NÃO EXISTE

(SELECIONE nome completo FROM Rl WHERE nome completo = a.nome completo AND Disciplina = "DB")

O predicado EXISTS (SubQuery) é verdadeiro quando a subconsulta SubQuery não está vazia, ou seja, contém pelo menos uma tupla, caso contrário o predicado EXISTS é falso.

O predicado NOT EXISTS é verdadeiro somente quando a Subconsulta está vazia.

Observe como NOT EXISTS com uma consulta aninhada permite evitar a operação de diferença de relacionamento. Por exemplo, formular uma consulta com a palavra “todos” pode ser feito como se fosse uma dupla negativa. Vamos considerar um exemplo de banco de dados que modela o fornecimento de peças individuais por fornecedores individuais. Ele é representado por um relacionamento SP “Fornecedores-peças” com o diagrama;

SP (Número_da_fornecedor. Número_da peça) P (Número_da peça. Nome)

É assim que se formula a resposta ao pedido: “Encontre fornecedores que forneçam todas as peças”.

SELECIONE DISTINCT VENDOR_NUMBER DE SP SP1 ONDE NÃO EXISTE

(SELECIONE número_da_peça

DE P ONDE NÃO EXISTE

(SELECIONE * DE SP SP2

ONDE SP2.supplier_number=SP1.supplier_number E

sp2.part_number = P.part_number)):

Na verdade, reformulamos este pedido da seguinte forma: “Encontre fornecedores de tal forma que não haja nenhuma peça que eles não forneçam”. Deve-se notar que esta consulta também pode ser implementada através de funções agregadas com uma subconsulta:

SELECIONE DISTINCT Número_do_fornecedor

GRUPO POR Número_do_fornecedor

TENDO CounKDISTINCT part_number) =

(SELECIONE Contagem(número_da_peça)

O padrão SQL92 estende operadores de comparação para múltiplas comparações usando as palavras-chave ANY e ALL. Esta extensão é usada ao comparar o valor de uma coluna específica com uma coluna de dados retornada por uma subconsulta.

A palavra-chave ANY, colocada em qualquer predicado de comparação, significa que o predicado será verdadeiro se pelo menos um valor da subconsulta o predicado de comparação for verdadeiro. A palavra-chave ALL exige que o predicado de comparação seja verdadeiro quando comparado com todas as linhas da subconsulta.

Por exemplo, vamos encontrar alunos que passaram em todos os exames com uma nota pelo menos “bom”. Trabalhamos com a mesma base de dados “Sessão”, mas acrescentamos-lhe mais uma relação R4, que caracteriza a entrega de trabalhos laboratoriais durante o semestre:

R 1 = (Nome, Disciplina, Série);

R 2 = (nome completo, grupo);

R 3 = (Grupos, Disciplina)

R 4 = (Nome, Disciplina, Número do trabalho_laboratório, Nota);

Selecione R1.Nome completo de R1 onde 4 > = Todos (selecione Rl.Rating

Onde R1.Nome completo = R11.Nome completo)

Vejamos outro exemplo:

Selecionar os alunos cuja nota do exame não seja inferior a pelo menos uma nota no trabalho laboratorial que obtiveram aprovação nesta disciplina:

Selecione R1.Nome

De R1 Onde R1.Rating>= QUALQUER (Selecione R4.Rating

Onde Rl.Disciplina = R4. Disciplina E R1.Nome completo = R4.Nome completo)

Junções externas SQL

O padrão SQL2 expandiu o conceito de junção condicional. No padrão SQL1, ao unir relações, foram utilizadas apenas as condições especificadas na parte WHERE da instrução SELECT e, neste caso, apenas as tuplas das relações originais concatenadas pelas condições especificadas, para as quais essas condições foram definidas e verdadeiras , foram incluídos na relação resultante. No entanto, na realidade, muitas vezes é necessário unir tabelas de tal forma que o resultado inclua todas as linhas da primeira tabela e, em vez das linhas da segunda tabela para as quais a condição de junção não seja atendida, o resultado acabe com valores indefinidos. Ou vice-versa, todas as linhas da tabela direita (segunda) são incluídas e as partes ausentes das linhas da primeira tabela são complementadas com valores indefinidos. Essas junções foram chamadas de junções externas, em oposição às junções definidas pelo padrão SQL1, que passaram a ser chamadas de junções internas.

Em geral, a sintaxe da parte FROM no padrão SQL2 é a seguinte:

DE<список исходных таблиц> |

< выражение естественного объединения > |

< выражение объединения >

< выражение перекрестного объединения > |

< выражение запроса на объединение >

<список исходных таблиц>::= <имя_таблицы_1>

[nome do sinônimo da tabela_1] [...]

[,<имя_таблицы_п>[ <имя синонима таблицы_n> ] ]

<выражение естественного объединениям:: =

<имя_таблицы_1>NATURAL (INTER | COMPLETO | ESQUERDA | DIREITA) JUNTE-SE<имя_таблицы_2>

<выражение перекрестного объединениям: = <имя_таблицы_1>JUNÇÃO CRUZADA<имя_таблицы_2>

<выражение запроса на объединением:=

<имя_таблицы_1>JUNTE-SE À UNIÃO<имя_таблицы_2>

<выражение объединениям:= <имя_таблицы_1>( INTERNO |

COMPLETO | ESQUERDA | DIREITA) JOIN (ON condição)<имя_таблицы_2>

Nessas definições, INNER significa uma junção interna, LEFT significa uma junção à esquerda, ou seja, o resultado inclui todas as linhas da tabela 1, e partes das tuplas resultantes para as quais não havia valores correspondentes na tabela 2 são complementadas com NULL valores (indefinidos). A palavra-chave RIGHT significa uma junção externa direita e, ao contrário de uma junção esquerda, neste caso todas as linhas da tabela 2 são incluídas na relação resultante e as partes que faltam na tabela 1 são complementadas com valores indefinidos. A palavra-chave FULL define uma junção externa completa. junte-se: esquerda e direita. Com uma junção externa completa, são executadas junções externas direita e esquerda, e a relação resultante inclui todas as linhas da Tabela 1, preenchidas com nulos, e todas as linhas da Tabela 2, também preenchidas com nulos.

A palavra-chave OUTER significa exterior, mas se as palavras-chave FULL, LEFT, RIGHT forem fornecidas, a junção será sempre considerada externa.

Vejamos exemplos de execução de junções externas. Voltemos ao banco de dados "Sessão". Vamos criar uma relação na qual serão válidas todas as notas recebidas por todos os alunos em todas as provas que eles tiveram que fazer. Caso o aluno não tenha passado neste exame, em vez de nota terá valor indefinido. Para fazer isso, vamos realizar uma junção interna natural sequencial das tabelas R2 e R3 usando o atributo Grupo, e conectar a relação resultante com uma junção natural externa esquerda com a tabela R1 usando as colunas Nome Completo e Disciplina. Ao mesmo tempo, o padrão permite o uso de uma estrutura de colchetes, pois o resultado da união pode ser um dos argumentos da parte FROM da instrução SELECT.

SELECIONE Rl.Nome completo, R1.Disciplina. Rl.Classificação

FROM (R2 NATURAL INNER JOIN R3) LEFT JOIN Rl USING (Nome. Disciplina)

Resultado:


Nome completo	Disciplina	Nota
Petrov F.I.	Base de dados
Sidorov K. A.	Base de dados	4
Mironov L.V.	Base de dados
Stepanova K. E.	Base de dados
Krylov T. S.	Base de dados
Vladimirov V. A.	Base de dados
Petrov F.I.	Teoria da informação	Nulo
Sidorov K. A.	Teoria da informação
Mironov A.V.	Teoria da informação	Nulo
Stepanova K. E.	Teoria da informação
Krylov T. S.	Teoria da informação
Vladimirov V. A.	Teoria da informação	Nulo
Petrov F.I.	língua Inglesa
Sidorov K. A.	língua Inglesa	Nulo
Mironov A.V.	língua Inglesa	Nulo
Stepanova K. E.	língua Inglesa	Nulo
Krylov T. S.	língua Inglesa	Nulo
Vladimirov V. A.	língua Inglesa
Trofimov P.A.	Redes e telecomunicações
Ivanova E. A.	Redes e telecomunicações

Vamos considerar outro exemplo, para isso pegamos o banco de dados “Biblioteca”. Consiste em três relações; os nomes dos atributos aqui são digitados em letras latinas, o que é necessário na maioria dos SGBDs comerciais.

LIVROS (ISBN, TÍTULO. AUTOR. COAUTOR. ANOJZD, PÁGINAS)

LEITOR(NUM_READER. NOME_READER, ENDEREÇO. HOOM_PHONE. WORK_PHONE. BIRTH_DAY)

EXEMPLO (INV, ISBN, YES_NO. NUM_READER. DATE_IN. DATE_DUT)

Aqui a tabela BOOKS descreve todos os livros presentes na biblioteca, possui os seguintes atributos:

ISBN - código exclusivo do livro;
TÍTULO – título do livro;
AUTOR - sobrenome do autor;
COAUTOR – sobrenome do coautor;
ANOIZD - ano de publicação;
PÁGINAS - número de páginas.

A tabela READER armazena informações sobre todos os leitores da biblioteca e contém os seguintes atributos:

NUM_READER - número exclusivo do cartão da biblioteca;
NAME_READER - sobrenome e iniciais do leitor;
ENDEREÇO – endereço do leitor;
HOOM_PHONE - número de telefone residencial;
WORK_PHONE - número de telefone comercial;
BIRTH_DAY - data de nascimento do leitor.

A tabela EXEMPLARE contém informações sobre o estado atual de todas as instâncias de todos os livros. Inclui as seguintes colunas:

INV - número único de inventário de um exemplar do livro;
ISBN - cifra do livro, que determina que tipo de livro se trata e se refere às informações da primeira tabela;
YES_NO - sinal da presença ou ausência desta instância na biblioteca no momento atual;
NUM_READER - número do cartão da biblioteca se o livro for entregue ao leitor, e Nulo caso contrário;
DATE_IN - se o leitor possui o livro, então esta é a data em que ele foi emitido ao leitor; a DATE_OUT é a data em que o leitor deve devolver o livro à biblioteca.

Determinemos a lista de livros de cada leitor; se o leitor não tiver livros, o número da cópia do livro será NULO. Para realizar esta pesquisa, precisamos utilizar um left outer join, ou seja, pegamos todas as linhas da tabela READER e as juntamos com as linhas da tabela EXEMPLARE, caso a segunda tabela não possua uma linha com o número do cartão da biblioteca correspondente , então na linha da relação resultante o atributo EXEMPLARE.INV terá um valor NULL indefinido:

SELECIONE READER.NAME_READER, EXEMPLARE.INV

DO LEITOR DIREITO JUNTE-SE AO EXEMPLO EM READER.NUM_READER=EXEMPLARE.NUM_READER

A operação outer join, como já mencionamos, pode ser usada para formar fontes na cláusula FROM, então, por exemplo, o seguinte texto de consulta seria aceitável:

DE (LIVROS À ESQUERDA DO EXEMPLO)

LEFT JOIN (EXEMPLO DE JOIN NATURAL DO LEITOR)

Ao mesmo tempo, para os livros cujo exemplar não esteja nas mãos dos leitores, os valores do número do cartão da biblioteca e as datas de coleta e devolução do livro serão incertos.

Uma junção cruzada, conforme definido no padrão SQL2, corresponde a uma operação de produto cartesiano estendida, ou seja, uma operação de junção entre duas tabelas em que cada linha da primeira tabela é unida a cada linha da segunda tabela.

Operação pedido de fusãoé equivalente à operação de união teórica dos conjuntos em álgebra. Neste caso, mantém-se a exigência de equivalência dos esquemas das relações originais. Uma solicitação de adesão é realizada de acordo com o seguinte esquema:

SELECIONE - solicitação

SELEÇÃO DE UNIÃO - consulta

Todas as consultas participantes da operação de junção não devem conter expressões, ou seja, campos calculados.

Por exemplo, você precisa exibir uma lista de leitores que possuem o livro “Idiota” ou o livro “Crime e Castigo”. Esta será a aparência da solicitação:

SELECIONE LEITOR. NAME_READER

DO LEITOR, EXEMPLAR.LIVROS

BOOKS.TITLE = "Idiota"!}

SELECIONE LEITOR.NAME_READER

DO LEITOR, EXEMPLO, LIVROS

ONDE EXEMPLARE.NUM_READER= READER.NUM_READER E

EXEMPLO.ISBN = LIVROS.ISBN E

BOOKS.TITLE = "Crime e Castigo"!}

Por padrão, ao executar uma consulta de junção, as tuplas duplicadas são sempre excluídas. Portanto, se houver leitores que tenham os dois livros em mãos, eles ainda aparecerão na lista resultante apenas uma vez.

Uma consulta de junção pode juntar qualquer número de consultas originais.

Assim, ao pedido anterior você pode adicionar mais leitores que estão com o livro “Castelo” nas mãos:

SELECIONE LEITOR. NAME_READER

DO LEITOR. EXEMPLO, LIVROS

ONDE EXEMPLARE.NUM_READER= READER.NUM_READER E .

EXEMPLO.ISBN = LIVROS.ISBN E

BOOKS.TITLE = "Castelo"!}

No caso em que você precisa manter todas as linhas das relações de origem, você precisa usar a palavra-chave ALL na operação de junção. Se tuplas duplicadas forem armazenadas, o fluxo de execução da consulta de junção será semelhante a este:

SELECIONE - solicitação

Porém, o mesmo resultado pode ser obtido simplesmente alterando a cláusula WHERE da primeira parte da consulta original, conectando as condições locais com uma operação lógica OR e eliminando tuplas duplicadas.

SELECIONE LEITOR DISTINTO.NAME_READER

DO LEITOR. LIVROS.EXEMPLARES

ONDE EXEMPLARE.NUM_READER= READER.NUM_READER E

EXEMPLO.ISBN = LIVROS.ISBN E

BOOKS.TITLE = "Idiota" OR!}

BOOKS.TITLE = "Crime e Castigo" OR!}

BOOKS.TITLE = "Castelo"!}

Nenhuma das consultas originais em uma operação UNION deve conter uma cláusula ORDER BY, mas o resultado da união pode ser ordenado escrevendo uma cláusula ORDER BY especificando a lista de colunas ordenadas após o texto da última consulta SELECT original.

Importante! Se um parâmetro de função for do tipo String e especificar um nome de campo que contém espaços, o nome do campo deverá ser colocado entre colchetes.
Por exemplo: "[Número de volume de negócios]".

1. Valor (Total)- calcula a soma dos valores das expressões passadas a ele como argumento para todos os registros detalhados. Você pode passar um Array como parâmetro. Neste caso, a função será aplicada ao conteúdo do array.

Exemplo:
Valor(Vendas.ValorTurnover)

2. Contar - calcula o número de valores diferentes de NULL. Você pode passar um Array como parâmetro. Neste caso, a função será aplicada ao conteúdo do array.

Sintaxe:
Quantidade (parâmetro [vários])

Para indicar o recebimento de valores diferentes, você deve especificar Distinto antes do parâmetro Método Quantidade.

Exemplo:
Quantidade(Vendas.Contraparte)
Quantidade (Várias Vendas.Contraparte)

3. Máximo - obtém o valor máximo. Você pode passar um Array como parâmetro. Neste caso, a função será aplicada ao conteúdo do array.

Exemplo:
Máximo (quantidade restante)

4. Mínimo - obtém o valor mínimo. Você pode passar um Array como parâmetro. Neste caso, a função será aplicada ao conteúdo do array.

Exemplo:
Mínimo (Quantidade.Remanescente)

5. Média - Obtém a média para valores não NULOS. Você pode passar um Array como parâmetro. Neste caso, a função será aplicada ao conteúdo do array.

Exemplo:
Média (Quantidade.Remanescente)

6. Matriz - forma um array contendo o valor do parâmetro para cada registro detalhado.

Sintaxe:
Array([Várias] Expressões)

Você pode usar uma tabela de valores como parâmetro. Neste caso, o resultado da função será um array contendo os valores da primeira coluna da tabela de valores, passado como parâmetro. Se uma expressão contiver uma função Array, então a expressão será considerada uma expressão agregada. Se a palavra-chave Vários for especificada, a matriz resultante não conterá valores duplicados.

Exemplo:
Matriz(Contraparte)

7. Tabela de Valores - gera uma tabela de valores contendo tantas colunas quantos forem os parâmetros da função. Os registros detalhados são obtidos de conjuntos de dados necessários para obter todos os campos envolvidos nas expressões de parâmetros de função.

Sintaxe:
ValueTable([Vários] Expressão1 [AS ColumnName1][, Expressão2 [AS ColumnName2],...])

Se os parâmetros da função forem campos residuais, a tabela de valores resultante conterá valores para registros para combinações exclusivas de dimensões de outros períodos. Neste caso, os valores são obtidos apenas para campos de saldo, dimensões, contas, campos de período e seus detalhes. Os valores dos demais campos nos registros de outros períodos são considerados iguais a NULL. Se uma expressão contiver a função ValueTable, essa expressão será considerada uma expressão agregada. Se a palavra-chave Vários for especificada, a tabela de valores resultante não conterá linhas contendo os mesmos dados. Após cada parâmetro pode haver uma palavra-chave opcional AS e um nome que será atribuído à coluna da tabela de valores.

Exemplo:
Tabela de Valores (Várias Nomenclaturas, Características das Nomenclaturas AS Características)

8. Recolher (GroupBy) - projetado para remover duplicatas de um array.

Sintaxe:
Collapse(Expressão, ColumnNumbers)

Opções:

Expressão- uma expressão do tipo Array ou ValueTable, cujos valores dos elementos precisam ser recolhidos;
Números de coluna- (se a expressão for do tipo ValueTable) digite String. Números ou nomes (separados por vírgulas) das colunas da tabela de valores, entre as quais é necessário procurar duplicatas. O padrão são todas as colunas.

Exemplo:
Collapse(ValueTable(Telefone, Endereço),,"Telefone");

9. Obter parte - obtém uma tabela de valores contendo determinadas colunas da tabela de valores original.

Sintaxe:
GetPart(Expressão, NúmerosColuna)

Opções:

Expressão- digite Tabela de Valores. Uma tabela de valores da qual obter colunas;
Números de coluna- digite String. Números ou nomes (separados por vírgulas) das colunas da tabela de valores que precisam ser obtidos.

Valor de retorno: Tabela de valores, que contém apenas as colunas especificadas no parâmetro.

Exemplo:
GetPart(Collapse(ValueTable(PhoneNumber, Address) ,"PhoneNumber"),"PhoneNumber");

10. Pedido - projetado para organizar elementos de array e tabela de valores.

Sintaxe:
Organizar(Expressão, NúmerosColuna)

Opções:

Expressão- Array ou ValueTable do qual você precisa obter colunas;
Números de coluna- (se a expressão for do tipo ValueTable) números ou nomes (separados por vírgulas) das colunas da tabela de valores pelas quais se deseja ordenar. Pode conter a direção da ordenação e a necessidade de ordenação automática: Decrescente/Ascendente + Ordenação automática.

Valor de retorno: Array ou ValueTable, com elementos ordenados.

Exemplo:
Arrange(ValueTable(PhoneNumber, Address, CallDate),"CallDate Decrescente");

11. UnirStrings - projetado para combinar cordas em uma linha.

Sintaxe:
ConnectRows(Valor, ItemSeparator, ColumnSeparator)

Opções:

Significado- expressões que precisam ser combinadas em uma linha. Se for um Array, os elementos do array serão combinados em uma string. Se for uma ValueTable, todas as colunas e linhas da tabela serão combinadas em uma linha;
Separador de Elementos- uma string contendo texto a ser usado como separador entre os elementos da matriz e as linhas da tabela de valores. Padrão – caractere de avanço de linha;
Separadores de Coluna- uma linha contendo texto que deve ser utilizada como separador entre as colunas da tabela de valores. Padrão "; ".

Exemplo:
ConnectRows(ValueTable(Telefone, Endereço));

12. Processamento de Grupo - retorna o objeto GroupProcessingDataCompositionData. Na propriedade Dados do objeto, os valores de agrupamento são colocados na forma de uma tabela de valores para cada expressão especificada no parâmetro da função Expressões. Ao usar o agrupamento hierárquico, cada nível da hierarquia é processado separadamente. Os valores dos registros hierárquicos também são colocados nos dados. A propriedade CurrentItem do objeto contém a linha da tabela de valores para a qual a função está sendo calculada atualmente.

Sintaxe:
GroupProcessing (Expressões, Expressões de Hierarquia, GroupName)

Opções:

Expressões. Expressões a serem avaliadas. Uma linha contendo expressões separadas por vírgula que precisam ser avaliadas. Após cada expressão pode haver uma palavra-chave opcional COMO e o nome da coluna da tabela de valores resultante. Cada expressão forma uma coluna na tabela de valores da propriedade Data do objeto DataCompositionGroupProcessingData.
ExpressõesHierarquias. Expressões a serem avaliadas para registros hierárquicos. Semelhante ao parâmetro Expressões, com a diferença de que o parâmetro Expressões de Hierarquia é utilizado para registros hierárquicos. Se o parâmetro não for especificado, as expressões especificadas no parâmetro Expressão serão usadas para calcular valores para registros hierárquicos.
Nome do grupo. O nome do agrupamento no qual calcular o agrupamento de processamento. Linha. Se não for especificado, o cálculo ocorre no agrupamento atual. Se o cálculo for realizado em uma tabela e o parâmetro contiver uma string vazia ou não for especificado, então o valor é calculado para o agrupamento - a string. O compositor de layout, ao gerar um layout de layout de dados, substitui esse nome pelo nome do agrupamento no layout resultante. Se o agrupamento não estiver disponível, a função será substituída por um valor NULL.

13. Todos - se pelo menos um registro tiver o valor False, então o resultado será False, caso contrário, True.

Sintaxe:
Cada (Expressão)

Parâmetro:

Expressão- Tipo booleano.

Exemplo:
Todo()

14. Qualquer (qualquer)- se pelo menos um registro tiver o valor True, então o resultado será True, caso contrário False

Sintaxe:
Qualquer (expressão)

Parâmetro:

Expressão- Tipo booleano.

Exemplo:
Qualquer()

15. Desvio Padrão da População Geral (Stddev_Pop) - calcula o desvio padrão da população. Calculado pela fórmula: SQRT(Variância da População Geral (X)).

Sintaxe:
Desvio Padrão da População Geral (Expressão)

Parâmetro:

Expressão- Tipo de número.

Exemplo:

X	1	2	3	4	5	6	7	8	9
S	7	1	2	5	7	34	32	43	87

SELECIONE o Desvio Padrão da População Geral (Y) DA Tabela
Resultado: 805.694444

16. Desvio Padrão da Amostra (Stddev_Samp) - calcula o desvio padrão cumulativo da amostra. Calculado usando a fórmula: SQRT(Variância da amostra(X)).

Sintaxe:
StandardDeviationSample(Expressão)

Parâmetro:

Expressão- Tipo de número.

Tipo de retorno Número.

Exemplo:

X	1	2	3	4	5	6	7	8	9
S	7	1	2	5	7	34	32	43	87

SELECIONE StandardDeviationSamples(Y) DA Tabela
Resultado: 28.3847573

17. Amostras de Variância (Var_Samp) - calcula a diferença típica de uma série de números sem levar em conta os valores NULL neste conjunto. Calculado usando a fórmula: (Quantidade(X^2) - Quantidade(X)^2 / Quantidade(X)) / (Quantidade(X) - 1). Se Quantidade(X) = 1, então NULL será retornado.

Sintaxe:
Amostras de Variância (Expressão)

Parâmetro:

Expressão- Tipo de número.

Exemplo:
SELECIONE Variância da População (Y) DA Tabela
Resultado: 716.17284

19. Covariância da População Geral (Covar_Pop) - calcula a covariância de vários pares numéricos. Calculado usando a fórmula: (Soma(Y * X) - Soma(X) * Soma(Y) / n) / n, onde n é o número de pares (Y, X) em que nem Y nem X são NULOS.

Sintaxe:
Covariância da População (Y, X)

Opções:

S- digite Número;
X- Tipo de número.

Exemplo:

X	1	2	3	4	5	6	7	8	9
S	7	1	2	5	7	34	32	43	87

SELECIONE CovariancePopulation(Y, X) DA Tabela
Resultado: 59.4444444

20. Amostras de Covariância (Covar_Samp) - calcula a diferença típica de uma série de números sem levar em conta os valores NULL neste conjunto. Calculado usando a fórmula: (Soma(Y * X) - Soma(Y) * Soma(X) / n) / (n-1), onde n é o número de pares (Y, X) em que nem Y nem X são NULOS.

Sintaxe:
Amostras de covariância (Y, X)

Opções:

S- digite Número;
X- Tipo de número.

Exemplo:

X	1	2	3	4	5	6	7	8	9
S	7	1	2	5	7	34	32	43	87

SELECIONE Amostras de Covariância (Y, X) DA Tabela
Resultado: 66.875

21. Correlação (Corr) - calcula o coeficiente de correlação de vários pares numéricos. É calculado através da fórmula: Covariância da População (Y, X) / (Desvio Padrão da População (Y) * Desvio Padrão da População (X)). Pares em que Y ou X são NULOS não são levados em consideração.

Sintaxe:
Correlação (Y, X)

Opções:

S- digite Número;
X- Tipo de número.

Exemplo:

X	1	2	3	4	5	6	7	8	9
S	7	1	2	5	7	34	32	43	87

SELECIONE Correlação (X, Y) DA Tabela
Resultado: 0,860296149

22. Inclinação de regressão (Regr_Slope) - calcula a inclinação da linha. Calculado através da fórmula: Covariância da População Geral (Y, X) / Variância da População Geral (X). Calculado sem levar em conta pares contendo NULL.

Sintaxe:
RegressãoInclinação(Y, X)

Opções:

S- digite Número;
X- Tipo de número.

Exemplo:

X	1	2	3	4	5	6	7	8	9
S	7	1	2	5	7	34	32	43	87

SELECIONE RegressionSlope(Y, X) DA Tabela
Resultado: 8.91666667

23. RegressãoIntercept (Regr_Intercept) - calcula o ponto Y de intersecção da linha de regressão. Calculado usando a fórmula: Média(Y) - RegressionSlope(Y, X) * Média(X). Calculado sem levar em conta pares contendo NULL.

Sintaxe:
Segmento de regressão(Y, X)

Opções:

S- digite Número;
X- Tipo de número.

Exemplo:
SELECIONE RegressionCount(Y, X) DA Tabela
Resultado: 9

25. RegressãoR2 (Regr_R2) - calcula o coeficiente de determinação. Calculado sem levar em conta pares contendo NULL.

Sintaxe:
RegressãoR2(Y, X)

Opções:

S- digite Número;
X- Tipo de número.

Valor de retorno:

Nulo - se Variância da População Geral (X) = 0;
1 - se Variância da População Geral (Y) = 0 E Variância da População Geral (X)<>0;
POW(Correlação(Y,X),2) - se a Variância da População Geral(Y)>0 E a Variância da População Geral(X)<>0.

Exemplo:

X	1	2	3	4	5	6	7	8	9
S	7	1	2	5	7	34	32	43	87

SELECIONE RegressãoR2(Y, X) DA Tabela
Resultado: 0,740109464

26. RegressãoMédiaX (Regr_AvgX) - calcula a média de X após eliminar os pares X e Y onde X ou Y estão vazios. Average(X) é calculado sem levar em conta pares contendo NULL.

Sintaxe:
RegressãoMédiaX(Y, X)

Opções:

S- digite Número;
X- Tipo de número.

Exemplo:

X	1	2	3	4	5	6	7	8	9
S	7	1	2	5	7	34	32	43	87

SELECIONE RegressionMeanX(Y, X) DA Tabela
Resultado: 5

27. RegressãoMédiaY (Regr_AvgY) - calcula a média de Y após eliminar os pares X e Y onde X ou Y estão vazios. Média(Y) é calculada sem levar em conta pares contendo NULL.

Sintaxe:
Média de regressãoY(Y, X)

Opções:

S- digite Número;
X- Tipo de número.

Exemplo:

X	1	2	3	4	5	6	7	8	9
S	7	1	2	5	7	34	32	43	87

SELECIONE RegressionMeanY(Y, X) DA Tabela
Resultado: 24.2222222

28. RegressãoSXX (Regr_SXX) - calculado pela fórmula: RegressãoQuantidade(Y, X) * Dispersão da População Geral(X). Calculado sem levar em conta pares contendo NULL.

Sintaxe:
RegressãoSXX(Y, X)

Opções:

S- digite Número;
X- Tipo de número.

Retorna a soma dos quadrados das expressões independentes usadas em um modelo de regressão linear. A função pode ser usada para avaliar a validade estatística de um modelo de regressão.

Exemplo:
SELECIONE RegressãoSYY(Y, X) DA Tabela
Resultado: 6445,55556

30. RegressãoSXY (Regr_SXY) - calculado pela fórmula: RegressionQuantity(Y, X) * Covariância da População Geral(Y, X). Calculado sem levar em conta pares contendo NULL.

Sintaxe:
RegressãoSXY(Y,X)

Opções:

S- digite Número;
X- Tipo de número.

Exemplo:

X	1	2	3	4	5	6	7	8	9
S	7	1	2	5	7	34	32	43	87

SELECIONE RegressãoSXY(Y, X) DA Tabela
Resultado: 535

31. Classificação

Sintaxe:
PlaceInOrder(Ordem, HierarquiaOrdem, NomeDoGrupo)

Opções:

Ordem– digite String. Contém expressões na sequência em que você deseja organizar os registros do grupo, separadas por vírgulas. A direção da ordem é controlada usando as palavras Crescente, Descendente. Você também pode seguir o campo com Ordem Automática, o que significa que ao ordenar links, você deve usar os campos de ordenação definidos para o objeto referenciado. Se nenhuma sequência for especificada, o valor será calculado na sequência de agrupamento;
OrdemHierarquia– digite String. Contém expressões de ordenação para registros hierárquicos;
Nome do grupo– digite String. O nome do agrupamento no qual calcular o agrupamento de processamento. Se não for especificado, o cálculo ocorre no agrupamento atual. Se o cálculo for realizado em uma tabela e o parâmetro contiver uma string vazia ou não for especificado, então o valor é calculado para o agrupamento - a string. O compositor de layout, ao gerar um layout de layout de dados, substitui esse nome pelo nome do agrupamento no layout resultante. Se o agrupamento não estiver disponível, a função será substituída por um valor NULL.

Se houver dois ou mais registros em uma sequência com os mesmos valores de campo de ordenação, a função retornará os mesmos valores para todos os registros.

Exemplo:
PlaceInOrder("[Número de faturamento]")

32. ClassificaçãoABC

Sintaxe:
ClassificaçãoABC(Valor, Número de Grupos, PercentageForGroups, GroupName)

Opções:

Significado– digite String. pelo qual a classificação deve ser calculada. Uma linha contendo a expressão;
Número de grupos- Tipo de número. Especifica o número de grupos nos quais serão divididos;
PorcentagemParaGrupos- digite String. Tantos quantos grupos menos 1 precisam ser divididos. Se não for especificado, será automaticamente;
Nome do grupo- digite String. O nome do agrupamento no qual calcular o agrupamento de processamento. Se não for especificado, o cálculo ocorre no agrupamento atual. Se o cálculo for realizado em uma tabela e o parâmetro contiver uma string vazia ou não for especificado, então o valor é calculado para o agrupamento - a string. O compositor de layout, ao gerar um layout de layout de dados, substitui esse nome pelo nome do agrupamento no layout resultante. Se o agrupamento não estiver disponível, a função será substituída por um valor NULL.

O resultado da função será o número da classe, começando em 1, que corresponde à classe A.

Exemplo:
ClassificaçãoABC("Valor(Lucro Bruto)", 3, "60, 90")

SQL adicionou funções adicionais que permitem calcular valores de grupo generalizados. Para aplicar funções agregadas, é assumida uma operação de agrupamento preliminar. Ao agrupar, todo o conjunto de tuplas de uma relação é dividido em grupos nos quais são coletadas tuplas que possuem os mesmos valores de atributos especificados na lista de agrupamento.

Isso é feito usando funções agregadas. As funções agregadas calculam um único valor para um grupo inteiro de tabelas. Uma lista dessas funções é apresentada na tabela. 7.

SELECIONE R1.Disciplina, CONTAGEM (*) DE R1GROUP POR R1.Disciplina

Resultado:

Exemplo. Obtenha uma lista de disciplinas em que pelo menos 5 pessoas passaram no exame:

SELECT R1.DisciplineFROM R1GROUP BY R1.DisciplineHAVING COUNT(*) >= 5 Resultado: Aqui, a instrução HAVING seleciona grupos que satisfazem a condição fornecida.

Consultas aninhadas

Razão D (Detalhes)

Taxa PD (Fornecimento)

1. Obtenha uma lista de fornecedores cujo status é inferior ao status máximo na tabela de fornecedores (comparação com uma subconsulta):

ONDE P.STATYS<

(SELECIONE MAX(P.STATUS)

Comente. Porque campo P.STATUS é comparado com o resultado da subconsulta, então a subconsulta deve ser formulada para retornar uma tabela consistindo exatamente uma linha e uma coluna.

Comente

Executar uma vez subconsulta aninhada e obtenha o valor máximo de status.
Digitalize a tabela de fornecedores P, comparando sempre o valor do status do fornecedor com o resultado da subconsulta, e selecione apenas as linhas em que o status é menor que o máximo.

2. Usando um predicado EM

(SELECIONE PD.PNUM DISTINTO

ONDE PD.DNUM = 2);

Comente. Nesse caso, uma subconsulta aninhada pode retornar uma tabela contendo diversas linhas.

Comente. O resultado da execução da solicitação será equivalente ao resultado da seguinte sequência de ações:

Executar uma vez subconsulta aninhada e obtenha uma lista de números de fornecedores que fornecem o número da peça 2.
Digitalize a tabela de fornecedores P, verificando sempre se o número do fornecedor está contido no resultado da subconsulta.

3. Usando um predicado EXISTE . Obtenha uma lista de fornecedores que fornecem a peça número 2:

PD.PNUM = P.PNUM E

Comente. O resultado da execução da solicitação será equivalente ao resultado da seguinte sequência de ações:

Digitalize a tabela de fornecedores P, toda vez que você executa uma subconsulta com o novo valor do número do fornecedor retirado da tabela P.
Inclua no resultado da consulta apenas as linhas da tabela de fornecedores para as quais a subconsulta aninhada retornou um conjunto de linhas não vazio.

Comente. Ao contrário dos dois exemplos anteriores, a subconsulta aninhada contém um parâmetro (link externo) passado da solicitação principal - o número do provedor P.PNUM. Essas subconsultas são chamadas correlacionado (correlacionado ). Uma referência externa pode assumir um valor diferente para cada linha candidata avaliada pela subconsulta, portanto a subconsulta deve ser executada novamente para cada linha selecionada na consulta principal. Essas subconsultas são típicas do predicado EXISTS, mas podem ser usadas em outras subconsultas.

Comente. Pode parecer que as consultas contendo subconsultas correlacionadas serão mais lentas do que as consultas com subconsultas não correlacionadas. Na verdade, não é assim, porque... a forma como o usuário formulou a solicitação, não define como esta solicitação será executada. A linguagem SQL não é processual, mas sim declarativa. Isso significa que o usuário que formula a solicitação está simplesmente descrevendo, qual deve ser o resultado da consulta, e a forma como esse resultado será obtido é de responsabilidade do próprio SGBD.

4. Usando um predicado NÃO EXISTE . Obtenha uma lista de fornecedores que não fornecem a peça número 2:

ONDE NÃO EXISTE

PD.PNUM = P.PNUM E

Comente. Assim como no exemplo anterior, uma subconsulta é usada aqui. A diferença é que a consulta principal selecionará as linhas da tabela de fornecedores para as quais a subconsulta aninhada não retornará uma única linha.

5. Obtenha os nomes dos fornecedores que fornecem todas as peças:

SELECIONE NOME DISTINTO

ONDE NÃO EXISTE

PD.DNUM = D.DNUM E

PD.PNUM = P.PNUM));

Comente. Esta consulta contém duas subconsultas aninhadas e implementa uma operação relacional divisões de relações.

A subconsulta mais interna é parametrizada por dois parâmetros (D.DNUM, P.PNUM) e tem o seguinte significado: selecionar todas as linhas contendo dados de entregas do fornecedor com número PNUM da peça com número DNUM. A negação de NÃO EXISTE indica que o fornecedor não fornece a peça. A subconsulta externa a ele, que é um parâmetro aninhado e parametrizado P.PNUM, faz sentido: para selecionar uma lista de peças que não são fornecidas pelo fornecedor PNUM. A negação de NOT EXISTS indica que não deve haver peças para um fornecedor com número PNUM que não sejam fornecidas por esse fornecedor. Isso significa exatamente que a consulta externa seleciona apenas os fornecedores que fornecem todas as peças.

Conexões externas

Freqüentemente, é necessário unir tabelas de forma que o resultado inclua todas as linhas da primeira tabela e, em vez das linhas da segunda tabela para as quais a condição de junção não seja atendida, o resultado contenha valores indefinidos. Ou vice-versa, todas as linhas da tabela direita (segunda) são incluídas e as partes ausentes das linhas da primeira tabela são complementadas com valores indefinidos. Tais associações foram chamadas de externas.

Em geral, a sintaxe da parte FROM no padrão SQL2 é a seguinte:

DE<список исходных таблиц>< выражение естественного соединения >< выражение соединения >< выражение перекрестного соединения >< выражение запроса на объединение ><список исходных таблиц>::= <имя_таблицы_1>[nome do sinônimo tabela_1] […] [,<имя_таблицы_n>[ <имя синонима таблицы_n> ] ]<выражение естественного соединения>:: =<имя_таблицы_1>NATURAL (INTER | TOTAL ESQUERDA | DIREITA) JUNTE-SE<имя_таблицы_2><выражение перекрестного соединения>:: = <имя_таблицы_1>JUNÇÃO CRUZADA<имя_таблицы_2><выражение запроса на объединение>::=<имя_таблицы_1>JUNTE-SE À UNIÃO<имя_таблицы_2><выражение соединения>::= <имя_таблицы_1>(INNERFULL | ESQUERDA | DIREITA) JOIN (ON condição |)<имя_таблицы_2>

Nessas definições, INNER significa uma junção interna (natural), LEFT significa uma junção à esquerda, ou seja, o resultado inclui todas as linhas da tabela 1, e partes das tuplas resultantes para as quais não havia valores correspondentes na tabela 2 são complementado com valores NULL (indefinidos). A palavra-chave RIGHT significa uma junção externa direita e, diferentemente de uma junção esquerda, neste caso, todas as linhas da Tabela 2 são incluídas na relação resultante e as partes que faltam na Tabela 1 são completadas com valores nulos. A palavra-chave FULL especifica uma junção externa completa: esquerda e direita. Com uma junção externa completa, são executadas junções externas direita e esquerda, e a relação resultante inclui todas as linhas da Tabela 1, preenchidas com nulos, e todas as linhas da Tabela 2, também preenchidas com nulos.

A palavra-chave OUTER significa exterior, mas se as palavras-chave FULL, LEFT, RIGHT forem especificadas, a conexão será sempre considerada externa.

Vejamos exemplos de execução de junções externas. Voltemos ao banco de dados "Sessão". Vamos criar uma relação na qual serão válidas todas as notas recebidas por todos os alunos em todas as provas que eles tiveram que fazer. Se o aluno não for aprovado neste exame, em vez de nota terá um valor indefinido. Para fazer isso, realizaremos uma junção interna natural sequencial das tabelas R2 e R3 usando o atributo Grupo, e conectaremos a relação resultante com uma junção natural externa esquerda à tabela R1 usando as colunas Nome Completo e Disciplina. Porém, o padrão permite o uso de uma estrutura de colchetes, pois o resultado da conexão pode ser um dos argumentos da parte FROM da instrução SELECT.

SELECIONE R1.Nome, R1.Disciplina, R1.RatingFROM (R2 NATURAL INNER JOIN R3) LEFT JOIN R1 USING (Nome, Disciplina)

Resultado:

Nome completo	Disciplina	Nota
Petrov F.I.	Base de dados
Sidorov K. A.	Base de dados
Mironov A.V.	Base de dados
Stepanova K. E.	Base de dados
Krylov T. S.	Base de dados
Vladimirov V. A.	Base de dados
Petrov F.I.	Teoria da informação	Nulo
Sidorov K. A.	Teoria da informação
Mironov A.V.	Teoria da informação	Nulo
Stepanova K. E.	Teoria da informação
Krylov T. S.	Teoria da informação

Muitas consultas ao banco de dados não exigem o nível de granularidade fornecido pelas consultas SQL discutidas nos exemplos anteriores. Assim, em todas as consultas listadas abaixo, você precisa descobrir apenas um ou vários valores que resumem as informações contidas no banco de dados:

1) qual é o valor da renda de todos os residentes?
2) qual é o maior e o menor rendimento total de um residente individual?
3) qual é a renda média per capita de um residente de Zelenograd?
4) qual a renda média per capita dos moradores de cada apartamento?
5) quantos moradores há em cada apartamento?

No SQL, esses tipos de consultas podem ser criados usando funções agregadas e as cláusulas GROUP BY e HAVING usadas na instrução SELECT.

Usando funções agregadas

Para resumir as informações contidas no banco de dados, o SQL fornece funções agregadas. Uma função agregada usa uma coluna inteira de dados como argumento e retorna um único valor que resume essa coluna de uma maneira específica.

Por exemplo, a função agregada AVG() pega uma coluna de números como argumento e calcula sua média.

Para calcular a renda média per capita de um residente de Zelenograd, você precisa da seguinte consulta:

SELECIONE ‘RENDA MÉDIA PER CAPITA, AVG(SUMD) DA PESSOA

SQL possui seis funções agregadas que permitem obter diferentes tipos de informações resumidas (Figura 3.16):

SUM() calcula a soma de todos os valores contidos em uma coluna;

AVG() calcula a média dos valores contidos em uma coluna;

- MIN() encontra o menor entre todos os valores contidos na coluna;
- MAX() encontra o maior entre todos os valores contidos na coluna;
- COUNT() conta o número de valores contidos em uma coluna;

COUNT(*) conta o número de linhas na tabela de resultados da consulta.

O argumento para uma função agregada pode ser um simples nome de coluna, como no exemplo anterior, ou uma expressão, como na consulta a seguir, que especifica o cálculo do imposto médio per capita:

SELECIONE MÉDIA(SOMA*0,13)

Arroz. 3.16.

Esta consulta cria uma coluna temporária contendo os valores (SUMD*0,13) para cada linha da tabela PERSON e, em seguida, calcula a média da coluna temporária.

O valor da renda de todos os residentes de Zelenograd pode ser calculado usando a função agregada SOMA:

SELECIONE SOMA (SOMA) DA PESSOA

Uma função agregada também pode ser usada para calcular totais de uma tabela de resultados obtida pela união de várias tabelas de origem. Por exemplo, você pode calcular o valor total da renda que os residentes receberam de uma fonte chamada “Bolsa”:

SELECIONE SOMA (DINHEIRO)

DO LUCRO, HAVE_D

ONDE LUCRO.ID = HAVE_D.ID

E LUCRO.FONTE^Bolsa’

As funções agregadas MIN() e MAX() permitem encontrar os menores e maiores valores de uma tabela, respectivamente. A coluna pode conter valores numéricos ou de string, ou valores de data ou hora.

Por exemplo, você pode definir:

a) O rendimento total mais baixo recebido pelos residentes e o imposto a pagar mais elevado:

SELECIONE MIN(SOMA), MÁX(SOMA*0,13)

(b) datas de nascimento do residente mais velho e do residente mais novo:

SELECIONE MIN(RDATA), MAX(RDATA)

SELECIONE MIN(FIO), MAX(FIO)

Ao usar essas funções agregadas, você precisa lembrar que os dados numéricos são comparados usando regras aritméticas, as datas são comparadas sequencialmente (os valores de datas anteriores são considerados menores que os posteriores) e os intervalos de tempo são comparados com base em sua duração.

Ao usar as funções MIN() e MAX() com dados de string, o resultado da comparação de duas strings depende da tabela de codificação de caracteres usada.

A função agregada COUNT() conta o número de valores em uma coluna de qualquer tipo:

(a) quantos apartamentos existem no 1º microdistrito?

SELECIONE CONTAGEM (ADR)

ONDE ADR GOSTA *%, 1_

(b) quantos residentes têm fontes de renda?

SELECIONE CONT.(NOM DISTINTO)

SELECIONE CONTAGEM (ID DISTINTO)

A palavra-chave "DISTINCT" especifica que os valores não duplicados em uma coluna são contados.

A função agregada especial COUNT(*) conta as linhas na tabela de resultados, não os valores dos dados:

(a) quantos apartamentos existem no 2º microdistrito?

ONDE ADR GOSTA "%, 2_-%'

(b) quantas fontes de renda Ivan Ivanovich Ivanov possui?

DE PESSOA, HAVE_D

ONDE FIO = "Ivanov Ivan Ivanovich"

E PESSOA.NOM = HAVE_D.NOM

SELECIONE CONTAGEM(*) DE PESSOA ONDE ADR = "Zelenograd, 1001-45’

Uma maneira de entender como as consultas resumidas com funções agregadas são executadas é pensar na execução da consulta dividida em duas partes. Primeiro, determinamos como a consulta funcionaria sem funções agregadas, retornando diversas linhas de resultados. Funções agregadas são então aplicadas aos resultados da consulta, retornando uma única linha resultante.

Por exemplo, considere a seguinte consulta complexa: encontre o rendimento total médio per capita, a soma do rendimento total dos residentes e o rendimento médio da fonte como percentagem do rendimento total do residente. O operador dá a resposta

SELECIONE AVG(SOMA), SOMA(SOMA), (100*AVG(DINHEIRO/SOMA))

DE PESSOA, LUCRO, HAVE_D ONDE PERSON.NOM = HAVE_D.NOM E HAVE_D.ID = PROFIT.ID

Sem funções agregadas, a consulta ficaria assim:

SELECIONE SUMD, SUMD, M0NEY/SUMD DE PESSOA, LUCRO, HAVE_D ONDE PERSON.NOM = HAVE_D.NOM E HAVE_D.ID = PROFIT.ID

e retornaria uma linha de resultados para cada residente e fonte de renda específica. As funções agregadas usam as colunas da tabela de resultados da consulta para produzir uma tabela de linha única com os resultados resumidos.

Você pode especificar uma função agregada na linha da coluna retornada em vez de qualquer nome de coluna. Por exemplo, pode fazer parte de uma expressão que adiciona ou subtrai os valores de duas funções agregadas:

SELECIONE MAX(SOMA)-MIN(SOMA)

No entanto, uma função agregada não pode ser um argumento para outra função agregada, ou seja, Funções agregadas aninhadas são proibidas.

Além disso, a lista de colunas retornadas não pode usar funções agregadas e nomes de colunas regulares ao mesmo tempo, pois isso não faz sentido, por exemplo:

SELECIONE FIO, SOMA(SOMA)

Aqui, o primeiro elemento da lista instrui o SGBD a criar uma tabela que consistirá em várias linhas e conterá uma linha para cada residente. O segundo elemento da lista solicita ao SGBD que obtenha um único valor de resultado, que é a soma dos valores da coluna SUMD. Estas duas instruções se contradizem, resultando em um erro.

O acima não se aplica aos casos de processamento de subconsultas e consultas com agrupamento.

Exemplo 21. Obtenha o número total de fornecedores (palavra-chave CONTAR ):

SELECIONE CONTAGEM(*) COMO N

O resultado é uma tabela com uma coluna e uma linha contendo o número de linhas da tabela P:

Usando funções agregadas com agrupamentos

Exemplo 23 . Para cada peça, obtenha a quantidade total fornecida (palavra-chave Agrupar por …):

SOMA(PD.VOLUME) COMO SM

GRUPO POR PD.DNUM;

Esta solicitação será executada da seguinte maneira. Primeiro, as linhas da tabela de origem serão agrupadas para que cada grupo contenha linhas com os mesmos valores DNUM. Então, dentro de cada grupo, o campo VOLUME será somado. Uma linha de cada grupo será incluída na tabela resultante:

Comente. Na lista de campos selecionados de uma instrução SELECT contendo uma seção GROUP BY, você pode incluir apenas funções e campos agregados, que estão incluídos na condição de agrupamento. A consulta a seguir gerará um erro de sintaxe:

SOMA(PD.VOLUME) COMO SM

GRUPO POR PD.DNUM;

A razão do erro é que a lista de campos selecionados inclui o campo PNUM, que Excluído para a seção GROUP BY. Na verdade, cada grupo de linhas resultante pode conter várias linhas com vários valores do campo PNUM. Uma linha total será gerada a partir de cada grupo de linhas. No entanto, não há uma resposta clara para a questão de qual valor escolher para o campo PNUM na linha final.

Comente. Alguns dialetos SQL não consideram isso um erro. A consulta será executada, mas é impossível prever quais valores serão inseridos no campo PNUM da tabela resultante.

Exemplo 24 . Obtenha números de peças cuja quantidade total fornecida exceda 400 (palavra-chave TENDO …):

Comente. A condição de que a quantidade total ofertada seja superior a 400 não pode ser formulada na cláusula WHERE, porque Você não pode usar funções agregadas nesta seção. As condições que usam funções agregadas devem ser colocadas em uma seção especial HAVING:

SOMA(PD.VOLUME) COMO SM

GRUPO POR PD.DNUM

TENDO SOMA(PD.VOLUME) > 400;

Como resultado, obtemos a seguinte tabela:

Comente. Uma consulta pode conter condições de seleção de linha na seção WHERE e condições de seleção de grupo na seção HAVING. As condições de seleção de grupo não podem ser movidas da seção HAVING para a seção WHERE. Da mesma forma, as condições de seleção de linha não podem ser movidas da seção WHERE para a seção HAVING, com exceção das condições que incluem campos da lista de agrupamento GROUP BY.

Usando subconsultas

Uma ferramenta muito conveniente que permite formular consultas de uma forma mais compreensível é a capacidade de usar subconsultas aninhadas na consulta principal.

Exemplo 25 . Obtenha uma lista de fornecedores cujo status é inferior ao status máximo na tabela de fornecedores (comparação com uma subconsulta):

ONDE P.STATYS<

(SELECIONE MAX(P.STATUS)

Comente. Porque campo P.STATUS é comparado com o resultado da subconsulta, então a subconsulta deve ser formulada para retornar uma tabela consistindo exatamente uma linha e uma coluna.

Comente

Executar uma vez subconsulta aninhada e obtenha o valor máximo de status.

Digitalize a tabela de fornecedores P, comparando sempre o valor do status do fornecedor com o resultado da subconsulta, e selecione apenas as linhas em que o status é menor que o máximo.

Exemplo 26 . Usando um predicado EM

(SELECIONE PD.PNUM DISTINTO

ONDE PD.DNUM = 2);

Comente. Nesse caso, uma subconsulta aninhada pode retornar uma tabela contendo diversas linhas.

Comente. O resultado da execução da solicitação será equivalente ao resultado da seguinte sequência de ações:

Executar uma vez subconsulta aninhada e obtenha uma lista de números de fornecedores que fornecem o número da peça 2.

Digitalize a tabela de fornecedores P, verificando sempre se o número do fornecedor está contido no resultado da subconsulta.

Exemplo 27 . Usando um predicado EXISTIR . Obtenha uma lista de fornecedores que fornecem a peça número 2:

PD.PNUM = P.PNUM E

Comente. O resultado da execução da solicitação será equivalente ao resultado da seguinte sequência de ações:

Digitalize a tabela de fornecedores P, toda vez que você executa uma subconsulta com o novo valor do número do fornecedor retirado da tabela P.

Inclua no resultado da consulta apenas as linhas da tabela de fornecedores para as quais a subconsulta aninhada retornou um conjunto de linhas não vazio.

Comente. Ao contrário dos dois exemplos anteriores, a subconsulta aninhada contém um parâmetro (link externo) passado da solicitação principal - o número do provedor P.PNUM. Essas subconsultas são chamadas correlacionado (correlacionado ). Uma referência externa pode assumir um valor diferente para cada linha candidata avaliada pela subconsulta, portanto a subconsulta deve ser executada novamente para cada linha selecionada na consulta principal. Essas subconsultas são típicas do predicado EXIST, mas podem ser usadas em outras subconsultas.

Exemplo 28 . Usando um predicado NÃO EXISTE . Obtenha uma lista de fornecedores que não fornecem a peça número 2:

PD.PNUM = P.PNUM E

Comente. Assim como no exemplo anterior, uma subconsulta correlacionada é usada aqui. A diferença é que a consulta principal selecionará as linhas da tabela de fornecedores para as quais a subconsulta aninhada não retornará uma única linha.

Exemplo 29 . Obtenha os nomes dos fornecedores que fornecem todas as peças:

SELECIONE NOME DISTINTO

PD.DNUM = D.DNUM E

PD.PNUM = P.PNUM));

Comente. Esta consulta contém duas subconsultas aninhadas e implementa uma operação relacional divisões de relações.

A subconsulta mais interna é parametrizada por dois parâmetros (D.DNUM, P.PNUM) e tem o seguinte significado: selecionar todas as linhas contendo dados de entregas do fornecedor com número PNUM da peça com número DNUM. A negação NOT EXIST indica que este fornecedor não fornece esta peça. A subconsulta externa a ele, que é um parâmetro aninhado e parametrizado P.PNUM, faz sentido: para selecionar uma lista de peças que não são fornecidas pelo fornecedor PNUM. A negação NOT EXIST indica que não deve haver peças para um fornecedor com número PNUM que não sejam fornecidas por esse fornecedor. Isso significa exatamente que a consulta externa seleciona apenas os fornecedores que fornecem todas as peças.

Artigos relacionados:
Revisão dos esquemas de recuperação de carga da bateria Dispositivo para regeneração de células galvânicas e cargas... Smartphones Android semelhantes ao iPhone Moto X 2014 Se você está procurando um celular exclusivo, a Motorola tem o que... Programas gratuitos para promoção de SEO nas redes sociais A interface do messenger é bonita e discreta, vai te ajudar a entender...