15  Semana 5.1 (26-Oct-2026)

15.1 SQL Statements (UNIONS)

  • Usando a base de dados BikeStores

15.1.1 UNION

SELECT City FROM sales.customers
UNION 
SELECT City FROM sales.stores
ORDER BY City;
Displaying records 1 - 10
City
NA
Albany
Amarillo
Amityville
Amsterdam
Anaheim
Apple Valley
Astoria
Atwater
Auburn

15.1.2 UNION ALL

SELECT City FROM sales.customers
UNION ALL 
SELECT City FROM sales.stores
ORDER BY City;
Displaying records 1 - 10
City
NA
Albany
Albany
Albany
Amarillo
Amarillo
Amarillo
Amarillo
Amarillo
Amityville

15.1.3 UNION | ALL with WHERE CLAUSES

SELECT City FROM sales.customers
WHERE state='TX'
UNION  
SELECT City FROM sales.stores
WHERE state='TX'
ORDER BY City;
Displaying records 1 - 10
City
Amarillo
Copperas Cove
Corpus Christi
Desoto
El Paso
Euless
Forney
Fort Worth
Garland
Harlingen
SELECT City FROM sales.customers
WHERE state='TX'
UNION ALL
SELECT City FROM sales.stores
WHERE state='TX'
ORDER BY City;
Displaying records 1 - 10
City
Amarillo
Amarillo
Amarillo
Amarillo
Amarillo
Copperas Cove
Copperas Cove
Copperas Cove
Corpus Christi
Corpus Christi

15.1.4 Exercícios/Soluções

  • Usando a base de dados BikeStores

15.1.4.1 Exercício 1: Todos os Nomes de Produtos

  • Combinar nomes de produtos a partir de duas fontes:
    • Produtos que já foram encomendados.
    • Produtos atualmente em stock.

15.1.4.2 Exercício 2: Combinar produtos

  • Combinar produtos:
    • Produtos com stock abaixo de um certo limite (< 10).
    • Produtos que não estão presentes em nenhuma encomenda.

15.1.4.3 Exercício 3: Obter todos os emails

Para efectuar uma campanha de sensibilização sobre segurança informática, é necessário enviar emails a todos os envolvidos na área comercial.

  • Combinar emails de clientes, lojas e staff.
SELECT p.product_name
FROM production.products p 
INNER JOIN sales.order_items oi ON p.product_id = oi.product_id
UNION
SELECT p.product_name
FROM production.stocks s
LEFT JOIN production.products p ON s.product_id = p.product_id
WHERE s.quantity > 0
Displaying records 1 - 10
product_name
Trek X-Caliber 8 - 2017
Electra Townie Original 3i EQ - 2017/2018
Trek Domane S 5 Disc - 2017
Trek Emonda ALR 6 - 2018
Trek Silque SLR 7 Women’s - 2017
Electra Townie Commute 8D - 2018
Trek Domane SLR 8 Disc - 2018
Electra Cruiser 7D (24-Inch) Ladies’ - 2016/2018
Trek Domane SL 5 - 2018
Haro Shredder 20 - 2017
SELECT  p.product_name
FROM production.products p
INNER JOIN production.stocks s ON s.product_id = p.product_id
WHERE s.quantity < 10
UNION
SELECT  p.product_name
FROM production.products p 
LEFT JOIN sales.order_items oi ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL
Displaying records 1 - 10
product_name
Bicicleta Novinha
Electra Amsterdam Fashion 3i Ladies’ - 2017/2018
Electra Amsterdam Original 3i - 2015/2017
Electra Amsterdam Original 3i Ladies’ - 2017
Electra Amsterdam Royal 8i Ladies - 2018
Electra Cruiser 1 (24-Inch) - 2016
Electra Cruiser 1 Ladies’ - 2018
Electra Cruiser 7D (24-Inch) Ladies’ - 2016/2018
Electra Cruiser 7D Ladies’ - 2016/2018
Electra Cruiser 7D Tall - 2016/2018
SELECT email FROM sales.customers
UNION
SELECT email FROM sales.stores
UNION
SELECT email FROM sales.staffs
Displaying records 1 - 10
email
aaron.knapp@yahoo.com
abbey.pugh@gmail.com
abby.gamble@aol.com
abram.copeland@gmail.com
adam.henderson@hotmail.com
adam.thornton@hotmail.com
addie.hahn@hotmail.com
adelaida.hancock@aol.com
adelle.larsen@gmail.com
adena.blake@hotmail.com