3. SQL (Structured Query Language)
Certamente a SQL tem representado um padrão para linguagens de banco de dados relacionais. Existem diversas versões de SQL. A versão original foi desenvolvida no Laboratório de Pesquisa da IBM. Esta linguagem, originalmente chamada Sequel foi implementada como parte do projeto System R no início dos anos 70. A linguagem evoluiu desde então, e seu nome foi mudado para SQL (Structured Query Language).
Em 1986, o Americam National Standard Institute (ANSI) e a International Standard Organization (ISO) publicaram um padrão SQL. A IBM publicou o seu próprio SQL, standard, o Systems Application Database Interface (SAA-SQL). A versão mais recente do padrão ANSI/ISO SQL é o padrão SQL-99.
A linguagem SQL possui diversas partes: v Linguagem de Definição de Dados (DDL) - Inclui comandos para definição de esquemas de relações, exclusão de relações, criação de índices e modificações do esquema de relações; v Linguagem de manipulação de dados (DML) - Inclui comandos para inserção, exclusão e modificação de tuplas no banco de dados; v Incorporação DML (SQL Embutida) - Uso de SQL em linguagens de programação de uso geral, como Pascal, C,...; v Definição de Visões - A SQL DDL inclui comandos para definição de visões; v Autorização - A SQL DDL inclui comandos para especificação de direitos de acesso à relações e visões; v Integridade - A SQL DDL inclui comandos para especificação de regras de integridade que os dados que serão armazenados no banco de dados devem satisfazer; v Controle de Transações - A SQL DDL inclui comandos para especificação de iniciação e finalização de transações.
3.1. Estrutura Básica
A estrutura básica de uma expressão SQL consiste em três cláusulas: select, from e where.
v A cláusula select corresponde à operação projeção da álgebra relacional. É usada para listar os atributos desejados no resultado de uma consulta. v A cláusula from corresponde à operação produto cartesiano da álgebra relacional. Ela lista as relações a serem examinadas na avaliação da expressão. v A cláusula where corresponde ao predicado de seleção da álgebra relacional. Consiste em um predicado envolvendo atributos de relações que aparecem na cláusula from.
Uma típica consulta SQL tem a forma:
select A1, A2, ..., An ß 3 from r1, r2, ..., rn ß 1 where P ß 2
Cada Ai representa um atributo e cada ri é uma relação. P é um predicado. Esta consulta é equivalente à expressão da álgebra relacional p A1, A2, ..., An (sP (r1 x r2 x ...x rn))
A lista de atributos A1, A2, ..., An pode ser substituída por um (*) para selecionar todos os atributos presentes na cláusula from.
A SQL forma o produto cartesiano das relações chamadas na cláusula from, executa uma seleção da álgebra relacional usando o predicado da cláusula where e, então, projeta o resultado para os atributos da cláusula select. Na prática, a SQL pode converter esta expressão em uma forma equivalente que pode ser processada mais eficientemente.
Considerar o seguinte esquema para exemplos que se seguem.
Agencias = (Cod_Agencia, Nome_Agencia, Cidade_Agencia) Clientes = (Cod_Cliente, Nome, rua, cidade) Depositos = ( Cod_Agencia, Conta_Numero, Cod_Cliente, saldo) Emprestimos = (Cod_Agencia, Cod_Cliente, Numero_Emprestimo, quantia)
O resultado de uma consulta SQL é, obviamente, uma relação. Vamos considerar uma consulta muito simples usando nosso banco como exemplo. “Encontre os nomes de todos os clientes na relação clientes” .
select Nome from Clientes
v Linhas (tuplas) duplicadas
Em algumas situações uma consulta SQL pode retornar uma relação que contenha tuplas duplicadas. Nessa situação, inserimos a palavra-chave distinct depois do select para eliminá-las.
Aproveitando o exemplo anterior, a relação resultante poderá ter clientes que possuam o mesmo nome. Neste caso, podemos eliminar estas duplicações, como se segue:
select distinct Nome from Clientes
v Predicados e ligações
A SQL não tem uma representação da operação ligação natural. No entanto, uma vez que a ligação natural é definida em termos de um produto cartesiano, uma seleção e uma projeção, é relativamente simples escrever uma expressão SQL para uma ligação natural.
Ex.: “Encontre os nomes e cidades de clientes que possuam empréstimos em alguma agência”. Na SQL, isto pode ser escrito como:
Select distinct Nome, cidade From Clientes, Emprestimos Where Clientes.Cod_Cliente=Emprestimos.Cod_Cliente
A SQL inclui os conectores and, or e not ; caracteres especiais: (, ), ., :, _, %,<, >, <= , >= , = , <>, +, - ,* e /; operador para comparação: between, como mostra o exemplo a seguir.
“Selecionar todas as contas que possuam saldo entre 10000 e 20000”.
Select Conta_Numero From Depositos Where saldo between 10000 and 20000
Que equivale a consulta
Select Conta_Numero From Depositos Where saldo >= 10000 and saldo <= 20000
A SQL inclui também um operador para comparações de cadeias de caracteres, o like. Ele é usado em conjunto com dois caracteres especiais: v Por cento (%). Substitui qualquer subcadeia de caracteres; v Sublinhado (_). Substitui qualquer caractere.
Ex.: “Encontre os nomes de todos os clientes cujas ruas incluem a subcadeia “na””.
Select distinct Nome From Clientes Where rua like “ %na%”
Ou também
Ex.: “Encontre os nomes de todos os clientes cujas ruas finalizem com a subcadeia “na”, seguido de um caractere”.
Select distinct Nome From Clientes Where rua like “ %na”
Para que o padrão possa incluir os caracteres especiais ( isto é, % , _ , etc...), a SQL permite a especificação de um caractere de escape. O caractere de escape é usado imediatamente antes de um caractere especial para indicar que o caractere especial deverá ser tratado como um caractere normal. Definimos o caractere de escape para uma comparação like usando a palavra-chave escape. Para ilustrar, considere os padrões seguintes que utilizam uma barra invertida como caractere de escape.
v Like “ ab\%cd%” escape “\” substitui todas as cadeias começando com “ ab%cd”; v Like “ ab\_cd%” escape “\” substitui todas as cadeias começando com “ ab_cd”.
A procura por não-substituições em vez de substituições dá-se através do operador not like.
v Operações de conjunto
A SQL inclui a operações de conjunto union que opera em relações e corresponde à operação È da álgebra relacional.
Uma vez que as relações são conjuntos, na união destas, as linhas duplicadas são eliminadas.
Para que uma operação r È s seja válida, necessitamos que duas condições sejam cumpridas: v As relações r e s precisam ter a mesma paridade. Isto é, elas precisam ter o mesmo número de atributos; v Os domínios do i-ésimo atributo de r e do i-ésimo atributo de s devem ser os mesmos.
Ex. “Se quiséssemos saber todos os clientes que possuam empréstimo na agência de código “ 051””, fazemos:
Select distinct Nome From Clientes, Emprestimos Where Clientes.Cod_Cliente=Emprestimos.Cod_Cliente and Cod_Agencia = “051”
Da mesma forma, se quiséssemos saber “todos os clientes que possuam depósitos na agência de código “051””, fazemos:
Select distinct Nome From Clientes, Depositos Where Clientes.Cod_Cliente= Depositos.Cod_Cliente and Depositos.Cod_Agencia = “051”
"Para achar todos os clientes que possuam um depósito, um empréstimo ou ambos na agência de código “051””, faz-se:
Select distinct Nome From Clientes, Depositos Where Clientes.Cod_Cliente= Depositos.Cod_Cliente and Depositos.Cod_Agencia = “051” Select distinct Nome From Clientes, Emprestimos Where Clientes.Cod_Cliente=Emprestimos.Cod_Cliente and Cod_Agencia = “051”
v Ordenando a exibição de tuplas
A cláusula order by ocasiona o aparecimento de tuplas no resultado de uma consulta em uma ordem determinada. Para listar em ordem alfabética todos os clientes do banco, fazemos:
Select distinct Nome From Clientes Order by Nome
Como padrão, SQL lista tuplas na ordem ascendente. Para especificar a ordem de classificação, podemos especificar asc para ordem ascendente e desc para descendente. Podemos ordenar uma relação por mais de um elemento. Como se segue:
Select * From Emprestimos Order by quantia desc, Cod_Agencia asc
v Membros de conjuntos
O conectivo in testa os membros de conjunto, onde o conjunto é uma coleção de valores produzidos por uma cláusula select. Para ilustrar, considere a consulta “Encontre todos os clientes que possuem uma conta e um empréstimo na agência “Princesa Isabel””.
Select distinct Nome From Clientes Where Clientes.Cod_Cliente in (select Cod_Cliente from depositos, agencias where depositos.Cod_Agencia = agencias.Cod_Agencia and Nome_Agencia = “Princesa Isabel”) and Clientes.Cod_Cliente in (select Cod_Cliente from emprestimos, agencias where emprestimos.Cod_Agencia = agencias.Cod_Agencia and Nome_Agencia = “Princesa Isabel”)
da mesma forma, pode ser usada a expressão not in.
v Variáveis tuplas (renomeação) Ilustração: “encontre o nome e a cidade de todos os clientes com um depósito e qualquer agência”.
Select distinct T.Nome, T.cidade from Clientes as T, Depositos as S where T.Cod_Cliente = S.Cod_Cliente
Redefinindo o esquema do banco para os exemplos que se seguem: Agencias = (Cod_Agencia, Nome_Agencia, Cidade_Agencia, ativos) Clientes = (Cod_Cliente, Nome, rua, cidade) Depositos = ( Cod_Agencia, Conta_Numero, Cod_Cliente, saldo) Emprestimos = (Cod_Agencia, Cod_Cliente, Numero_Emprestimo, quantia)
v Comparação de conjuntos Considere a consulta “encontre todas as agências que possuem ativos maiores que alguma agência de Vitória”. Podemos escrever a expressão SQL:
select distinct t.Nome_Agencia from agencias as t, agencias as s where t.ativos > s.ativos and agencias.cidade = “Vitória”
Uma vez que isto é uma comparação “maior que”, não podemos escrever a expressão usando a construção in.
A SQL oferece o operador any que pode ser usado para construir a consulta anterior. Comparações do tipo >any, <any, >=any, <=any, =any são aceitos pela linguagem. A consulta anterior pode ser escrita:
select Nome_Agencia from agencias where ativos > any (select ativos from agencias where agencias.cidade = “Vitória”)
Modificando a consulta anterior levemente. Vamos encontrar todas as agências que possuem ativos maiores do que todas as agências de Vitória. A construção > all corresponde a frase “maior que todos”. A consulta fica como se segue:
select Nome_Agencia from agencias where ativos > all (select ativos from agencias where agencias.cidade = “Vitória”)
Como o operador some, o operador all pode ser usado como: >all, <all, >=all, <=all, =all e <> all.
v Testando relações vazias
A SQL possui um recurso para testar se uma subconsulta tem alguma tupla em seus resultados. A construção exists retorna true se o argumento subconsulta está não-vazio. Usando a construção exists, podemos escrever a consulta “Encontre todos os clientes que possuam uma conta e um empréstimo na agência “Princesa Isabel” ”.
Select Nome from clientes where exists (select * from depositos, agencias where depositos.Cod_Cliente= clientes.Cod_Cliente and agencias.Cod_Agencia = depositos.Cod_Agencia and Nome_Agencia = “Princesa Isabel”) and exists (select * from emprestimos, agencias where emprestimos.Cod_Cliente= clientes.Cod_Cliente and agencias.Cod_Agencia = emprestimos.Cod_Agencia and Nome_Agencia = “Princesa Isabel”)
Pode-se usar também a expressão not exists. Exercícios de SQL
Considerando o esquema:
Pessoas = (CodPessoa, NomePessoa, Cidade, Chefe) Empresas = (CodEmpresa, NomeEmpresa, Cidade) Trabalha = (CodPessoa, CodEmpresa, Salario)
1. Consulte todas as pessoas que trabalham em Vitória.
2. Consulte todas as pessoas que trabalham na mesma cidade onde moram.
3. Consulte todas as pessoas que moram na mesma cidade do chefe.
4. Consulte todas as empresas que funcionam em cidades que não moram pessoas cujo primeiro nome seja Maria (usar operações de conjunto).
5. Consulte todas as pessoas que não trabalham em Vitória e que ganham acima de 2000 em ordem decrescente.
6. Consulte todas as pessoas que não trabalham na empresa que comece com “inf_” em ordem alfabética.
Considere o esquema seguinte para as questões que se seguem.
Fabricante = (codf, nomef) Automovel = (coda, nomea, preço, codf) Pessoa = (codp, nomep) Venda = (codp, coda, valor, cor, data)
1) Quem comprou “Ford”? (
2) Quem não comprou “Ford”?
3) Quem comprou carro com ágio?
4) Quem comprou “Ford” e não comprou “Volks”?
5) Quem comprou carro entre 01/01/97 e 01/01/98?
v Funções agregadas
A SQL oferece a habilidade para computar funções em grupos de tuplas usando a cláusula group by. O(s) atributo(s) dados na cláusula group by são usados para formar grupos. Tuplas com o mesmo valor em todos os atributos na cláusula group by são colocados em um grupo. A SQL inclui funções para computar: v Média: avg v Mínimo: min v Máximo: max v Soma: sum v Contar: count
Para ilustrar, considere as consultas “ Encontre o saldo médio das contas em cada agência” Select Nome_Agencia, avg(saldo) From Depositos, Agencias where Depositos.Cod_Agencia = Agencias.Cod_Agencia Group by Nome_Agencia
“Encontre o número de depositantes de cada agência” Select Nome_Agencia, count(distinct Nome) From Depositos, Agencias where Depositos.Cod_Agencia = Agencias.Cod_Agencia Group by Nome_Agencia
Note que nesta última consulta é importante a existência da cláusula distinct pois um cliente pode ter mais de uma conta em uma agência, e deverá ser contado uma única vez.
“Encontre o maior saldo de cada agência” Select Nome_Agencia, max(saldo) From Depositos, agencias Where Depositos.Cod_Agencia= agencias.agencias-cod Group by Nome_Agencia
Ás vezes é útil definir uma condição que se aplique a grupos em vez de tuplas. Por exemplo, poderíamos estar interessados apenas em agências nas quais a média dos saldos seja maior que 1200. Esta condição será aplicada a cada grupo e não à tuplas simples e é definida através da cláusula having. Expressamos esta consulta em SQL assim:
Select Nome_Agencia, avg(saldo) From Depositos Group by Nome_Agencia Having avg(saldo)>1200
Às vezes, desejamos tratar a relação inteira como um grupo simples. Nesses casos, não usamos a cláusula group by. Considere a consulta “Encontre a média de saldos de todas as contas”. Escrevemos:
Select avg(saldo) From Depositos Exercícios
Considerando o esquema abaixo,
Agencias = (Cod_Agencia, Nome_Agencia, Cidade_Agencia, ativos) Clientes = (Cod_Cliente, Nome, rua, cidade) Depositos = ( Cod_Agencia, Conta_Numero, Cod_Cliente, saldo) Emprestimos = (Cod_Agencia, Cod_Cliente, Numero_Emprestimo, quantia)
1. Selecione todos os clientes que possuem contas em agencia(s) que possui(m) o maior ativo.
2. Selecione o total de agencias por cidade, classificado por cidade.
3. Selecione, por agências, o(s) cliente(s) com o maior saldo.
4. Selecione o valor médio de empréstimos efetuados por cada agência em ordem crescente das cidades onde estas agências se situam.
5. Selecione a(s) agência(s) que possui(m) a maior média de quantia emprestada.
6. Selecione todas as agências situadas fora de Vitória que possuem a média de depósitos maior do que alguma agência localizada em Vitória.
7. Selecione o menor saldo de clientes, por agências.
8. Selecione o saldo de cada cliente, caso ele possua mais de uma conta no banco.
Exercícios a serem implementados: MS SQL Server Banco de Dados: Northwind
Dicionário: Employees: Funcionários; Shippers: Transportadoras; Orders: Pedidos; Orders Details: Itens de Pedidos; Customers: Clientes; Suppliers: Fornecedores; Categories: Categorias;Products: Produtos.
Dado o modelo abaixo, fazer as consultas SQL (DML) que se seguem:
1. Consultar todos os pedidos de clientes (retornar o nome do cliente e o número do pedido). 2. Consultar todos os números de pedidos (orderId) de clientes cujo nome comece com 'M'. 3. Consultar todos os clientes em ordem crescente da cidade onde moram. 4. Consultar todos os pedidos feitos entre '01/01/1996' e '01/10/1996' (OrderDate). 5. Consultar o preço médio dos produtos para cada categoria. 6. Consultar o produto mais caro, para cada categoria. 7. Consultar o valor total de cada pedido. 8. Selecionar todos os produtos cujos preços unitários (unitprice) estão acima do preço médio de todos os produtos. 9. Consultar todos os números de pedidos que possuam mais de 5 itens (produtos) relacionados. 10. Consultar produtos que não fazem parte de nenhum pedido. 11. Consultar todas as empresas (clientes e/ou fornecedoras). 12. Consultar todos os Funcionários que moram na mesma região dos respectivos chefes (ReportsTo).
Primeiro Trabalho de Banco de Dados
Considerando o modelo em anexo (PUBS), faça consultas SQL e mostre o resultado obtido com tais consultas, para as questões que se seguem.
1ª Questão - Escreva um comando que seja capaz de consultar o nome e o sobrenome de todos os autores que possuem sobrenome terminado com ‘inger’.
2ª Questão - Escreva um comando que seja capaz de consultar o volume acumulado de vendas por editor (publisher). O resultado deve ser apresentado em ordem crescente de volume acumulado de vendas.
Obs.: O campo ytd_sales (titles) contêm o volume acumulado de vendas de um determinado título.
3ª Questão - Escreva um comando que seja capaz de consultar o volume médio de vendas dos títulos, para cada editor que possui mais do que cinco livros editados.
4ª Questão - Escreva um comando que seja capaz de consultar o preço médio de vendas, dos títulos, para cada tipo de livro vendido. O resultado deverá estar em ordem crescente de tipo (type de titles).
5ª Questão - Escreva um comando que seja capaz de determinar o tipo de livro que possui o menor preço médio de venda.
6ª Questão - Escreva um comando que seja capaz de informar o nome dos autores que moram na mesma cidade do editor de pelo menos um de seus livros.
7ª Questão - Escreva um comando que seja capaz de identificar todos os livros que possuem preço de venda superior ao preço de venda do livro de código “BU7832”.
8ª Questão - Escreva um comando que seja capaz de identificar todos os editores que não possuem nenhum livro do tipo “Business” editado (Obs.: usar operações de conjunto).
9ª Questão - Escreva um comando que seja capaz de informar o nome dos autores que publicaram pelo menos um livro do tipo “popular_comp”.
10ª Questão - Escreva um comando que seja capaz de informar o nome dos autores que publicaram todos os seus livros do tipo “popular_comp”.
11ª Questão - Escreva um comando que seja capaz de informar o nome dos autores que moram em estados onde não existem livrarias (stores) cadastradas.
12ª Questão - Escreva um comando que seja capaz de identificar os autores que venderam pelo menos um livro em lojas situadas no estado de código “WA” (Washington). A consulta deverá retornar o nome do autor, o título e a quantidade de vendas efetuadas por título.
13ª Questão - Escreva um comando que seja capaz de informar a quantidade de livros vendidos de cada editor, em cada estado.
14ª Questão - Escreva um comando que seja capaz de produzir uma listagem de todos os livros cadastrados. Esta listagem deverá estar agrupada por editor e autor.
|