SQL (01) - DI @ UMinho

SQL (01)
Sistemas de Bases de Dados Relacionais
Introdução ao SQL
Interrogações diversas sobre a Base de Dados Northwind
/*
Introdução ao SQL - Parte I
===========================
Folha de Exercícios
Resolução dos exercícios em SQL Server 2000
*/
-- Indicação da base de dados de trabalho
use northwind
-- Utilização do comando SELECT - Exemplos de aplicação.
-- Query 01: Quais são os produtos registados na base de dados?
-- Objectivo: Demonstração de queries simples.
-- Selecção de todos os registos da tabela "products" com visualização de todos
-- os valores correspondentes a todos (*) os atributos da tabela.
select *
from products
-- Query 02: Quais são os códigos dos fornecedores dos produtos que temos registados
-na base de dados? Apresentar além dessa informaçãooscódigos e os nomes
-dos produtos.
-- Objectivo: Demonstração de queries simples com selecção de atributos.
-- Selecção de todos os registos da tabela "products", com visualização apenas
-- dos valores correspondentes aos atributos "ProductID", "ProductName" e "SupplierID".
select ProductID, ProductName, SupplierID
from products
-- Query 03: Quais são os produtos catalogados que sãofornecidospelo fornecedor com o
-código '1'?
-- Objectivo: Demonstração de queries simples com critérios de filtragem.
-- Selecção de todos os registos da tabela "products" cujos valores do
-- atributo "SupplierID" sejam iguais a 1. Apenas são visualizados os valores
-- correspondentes aos atributos "ProductID", "ProductName" e "SupplierID" cujos
-- registos obedeçam ao critério de filtragem.
select ProductID, ProductName, supplierid
from products
where supplierid = 1
-- Query 04: Quais são os códigos dos fornecedores que fornecem od produtos registados
-na base de dados?
-- Objectivo: Demonstração de queries simples com a eliminação de registos repetidos.
-- Selecção dos registos da tabela "products" apenas segundo o atributo "SupplierID".
-- Apenas são visualizados os valores correspondentes a esse atributo. Os valores
-- repetidos são eliminados (DISTINCT)
select distinct SupplierID
from products
-- Query 05: Quais são os nomes dos fornecedores que fornecem os produtos registados
-na base de dados?
-- Objectivo: Demonstração de queries simples com a utilização de operações de junção -
O.Belo, DI, UM, 2003
-- produto cartesiano com critério de filtragem.
-- Apenas são visualizados os nomes dos fornecedores. Eventuais valores repetidos são
-- eliminados.
select distinct suppliers.CompanyName
from products, suppliers
where products.supplierid = suppliers.supplierid
-- Query 06: Quais foram os códigos dos produtos encomendados pelos clientes? Apresentar
-além dos códigos dos produtos apresentar também os números das encomendas e os
-códigos dos clientes.
-- Objectivo: Demonstração de queries simples com a utilização de operações de
-- (equi-)junção.
select orders.orderid, orders.customerid, [order details].productid
from orders inner join [order details]
on orders.orderid = [order details].orderid
-- Query 07: Quais foram os produtos encomendados pelos clientes? Apresentar
-além dos códigos dos produtos os seus nomes.
-- Objectivo: Demonstração de queries simples com a utilização de operações de
-- (equi-)junção.
select distinct [order details].productid, [products].productname
from [order details] inner join products
on [order details].productid=products.productid
-- Query 08: Quais foram os produtos encomendados pelos clientes? Apresentar
-além dos códigos dos produtos os seus nomes e os códigos e nomes dos clientes
-que encomendaram esses produtos.
-- Objectivo: Demonstração de queries utilizando várias operações de (equi-)junção
-- encadeadas.
select orders.customerid, customers.companyname, [order details].productid,
[products].productname
from ((orders inner join [order details]
on orders.orderid= [order details].orderid)
inner join products on [order details].productid=products.productid)
inner join customers on orders.customerid=customers.customerid
-- Query 09: Quais são foram os produtos encomendados pelos clientes? Apresentar
-- juntamente com a lista de produtos os códigos e os nomes dos clientes; a lista
-- deverá estar ordenada por código de cliente.
-- Objectivo: Demonstração da utilização de "alias" - sinónimos - em queries
-- envolvendo várias operações de junção.
select cl.customerid, cl.companyname, pr.productid, pr.productname
from ((orders as en inner join [order details] as od on en.orderid = od.orderid)
inner join products as pr on od.productid = pr.productid)
inner join customers as cl on en.customerid = cl.customerid
order by cl.customerid
-- Query 10: Quais são os clientes que têm umnúmero de fax?
-- Objectivo: Demonstração da manipulaçãode valores nulos em queries.
select companyname, fax
from customers
where fax is not null
-- Query 11: Quantos são os clientes registados na base de dados que não têm
-um número de fax?
-- Objectivo: Demonstração da utilização de funções de agregação e manipulação de
-- valores nulos em queries simples.
select count(*) as "Número de Clientes sem FAX"
from customers
where fax is null
O.Belo, DI, UM, 2003
-- Query 12: Quantos produtos foram encomendados até hoje, qual é a média dos
-preços dos produtos encomendados, qual foi o maior e o menor preço de produto
-encomendado, e qual foi o valor total dos produtos encomendados?
-- Objectivo: Demonstração da utilização de funções de agregação em queries.
select count(*) as "Número de Produtos Encomendados",
avg(unitprice) as "Média dos Produtos Encomendados",
max(unitprice) as "Maior Preço dos Produtos Encomendados",
min(unitprice) as "Menor Preço dos Produtos Encomendados",
sum(unitprice*quantity) as "Valor dos Produtos Encomendados"
from [order details]
-- Query 13: Apresentar uma lista com os códigos e os nomes dos clientes, ordenada
-alfabeticamente.
-- Objectivo: Demonstração da utilização de funções de ordenação.
select CustomerID, CompanyName
from customers
order by Companyname ASC
-- Query 14: Apresentar uma lista com o nome dos clientes, ordenada decrescentemente pelo
-valor total das encomendas efectuadas pelos clientes da região de Lisboa.
-- Objectivo: Demonstração da utilização de funções de agregação em queries envolvendo
-- várias operações de junção, ordenação de resultados e utilização de instruções
-- de agrupamento.
select customers.companyname,
sum([order details].quantity * [order details].unitprice) as "Valor Total p/
Cliente"
from ((orders inner join [order details]
on orders.orderid = [order details].orderid)
inner join products on [order details].productid=products.productid)
inner join customers on orders.customerid=customers.customerid
where customers.city = 'Lisboa'
group by customers.companyname
order by sum([order details].quantity * [order details].unitprice) desc
-- Query 15: Apresentar a data actual.
-- Objectivo: Demonstração da utilização de algumas funções de manipulação de datas,
-- específicas do SQL2K.
select convert(varchar(11),getdate())
-- Query 16: Apresentar o mês actual.
-- Objectivo: Demonstração da utilização de algumas funções de manipulação de datas,
-- específicas do SQL2K.
select datepart(mm,getdate())
-- Query 17: Quais os códigos dos clientes que fizeram encomendas durante o mês 3
-(Março)?
-- Objectivo: Demonstração da utilização de algumas funções de manipulação de datas,
-- específicas do SQL2K.
select distinct customerid
from orders
where datepart(mm,orderdate) = 3
-- Query 18: Qual foi o valor das encomendas anotadas em Julho de 1996?
-- Objectivo: Demonstração da utilização de algumas funções de manipulação de datas,
-- específicas do SQL2K, em queries envolvendo funções de agregação e de manipulação de
-- datas.
select sum([order details].quantity * [order details].unitprice) as "Valor Encomendas
Julho/1996"
from orders inner join [order details] on orders.orderid= [order details].orderid
where datepart(mm,orders.orderdate) = 7 and datepart(yyyy,orders.orderdate)= 1996
O.Belo, DI, UM, 2003
-- Query 19: Valor total das encomendas efectuadas em cada mês? Apresentar a lista
-ordenada decrescentemente por valor total.
-- Objectivo: Demonstração da utilização de algumas funções de manipulação de datas,
-- específicas do SQL2K, em queries envolvendo funções de agregação e de manipulação de
-- datas, e instruções de agrupamento e de ordenação.
select datepart(mm,orders.orderdate) as "Mês", sum([order details].quantity * [order
details].unitprice) AS "Valor"
from orders inner join [order details] on orders.orderid= [order details].orderid
where datepart(yyyy,orders.orderdate)= 1996
group by datepart(mm,orders.orderdate)
order by sum([order details].quantity * [order details].unitprice) DESC
-- Utilização do comando INSERT - Exemplos de aplicação.
-- Query 20: Inserir na tabela "Region" um novo registo em que os valores de RegionID e
-RegionDescription sejam, respectivamente '88' e 'Braga'.
-- Objectivo: Demonstração da operação de inserção de registos. A ordem de apresentação
-dos valores deve corresponder a ordem pela qual estão definidosna tabela.
insert into Region
values (88, 'Braga')
-- Query 21: Inserir na tabela "Region" um novo registo em que os valores de RegionID e
-RegionDescription sejam, respectivamente '99' e 'Aveiro'.
-- Objectivo: Demonstração da operação de inserção de registos. Neste caso apresenta-se
-a lista dos atributos para os quais se quer inserirvalores. Neste caso a ordem
-de apresentação e o número de valores deve corresponder a ordem pela qual foram
-apresentados os atributos e ao seu número.
insert into Region
(RegionDescription,RegionID)
values ('Aveiro', 99)
-- Query 22: Inserir na tabela "Region" todos os registos armazenados na tabela "Region2"
-- Objectivo: Demonstração da operação de inserção de registos com queries embebidos.
-Os atributos seleccionados (e a sua ordem) sobre a tabela "Region2" devem ser
-equivalentes aos da tabela "Region".
insert into Region
select *
from Region2
-- Utilização do comando UPDATE - Exemplos de aplicação.
-- Query 23: Substituir todos os valores do atributo "RegionDescription", em todos os
-registos nos quais este atributo tenha o valor de 'Aveiro' por 'Beira Litoral'.
-- Objectivo: Demonstração da operação de actualização de registos com a aplicação de
-- critérios de filtragem.
update Region
set RegionDescription = 'Beira Litoral'
where RegionDescription = 'Aveiro'
-- Utilização do comando DELETE - Exemplos de aplicação.
-- Query 24: Remover da tabela "Region" todos os registos cujos valores do atributo
-"RegionDescription" sejam iguais a 'Aveiro' ou a 'Braga'
-- Objectivo: Demonstração da operação de remoção de registos com a aplicação de
-- critérios de filtragem.
delete Region
where RegionDescription = 'Aveiro' OR RegionDescription = 'Braga'
O.Belo, DI, UM, 2003
-- Query 25: Remover todos os registos da tabela "Region".
-- Objectivo: Demonstração da operação de remoção de todos os registos de uma
-tabela. Recomenda-se muito cuidado na utilização deste comando.
delete Region
-- Fim da folha de exercícios de demonstração.
-- O.Belo, DI, UM, 2003
O.Belo, DI, UM, 2003