# SQL

---
selecionar a base de dados
**use NORTHWND**

selecionar todos os registos da tabela
**select * from categories(tabela)**

selecionar uma coluna e dar-lhe o nome de x
**select categoryName as 'categoria' from categories**

mostrar os q comecem pela letra c
**where CategoryName like 'C%'**

---
selecionar todos os registos da tabela

**select * from Orders**

selecionar uma coluna e dizer mostrar os maior ou igual a 3 e que tenha na coluna y o nome indicado
**where EmployeeID >=9 AND ShipCity='LYON'**

---
selecionar todos os registos da tabela
**select * from Customers**

ordenar descendente a coluna x e ordenar por ascendente na coluna y
**Order by CompanyName DESC, ContactTitle ASC**

---
selecionar todos os registos da tabela

**select * from Customers**

mostrar os registo que na coluna x contenham a palavra

**where ContactTitle IN ('Owner')**

---

select * From Products

Mostrar o valor mais alto do selecionado

**select max(UnitPrice) from Products**

Mostrar o valor mais baixo do selecionado
**select min(UnitPrice) from Products**

Mostrar a media do selecionado
**select avg(UnitPrice) from Products**

mostrar a soma do selecionado
**select sum(UnitPrice) from Products**

----
selecionar coluna sem mostrar registos repetições de uma tabela.

**select distinct (shipcity) from Orders**

## Operadores relacionais
* igual
* maior
* menor
* maior e igual
* menor e igual

## Operadores Lógicos

* And True and false
* OR true or false
* NOT not true/not false

---
---
Use NORTHWND

-- Exercicio 1
-- List of first 5 employees
select * from Employees
where EmployeeID <= 5

--Exercicio 2
-- Select Just the name of employees
select FirstName,LastName from Employees

--Exercicio 3
--Get Name of employees whose employees id is either 1 or 2 or 4
select FirstName,LastName from Employees
where EmployeeID IN (1,2,4)

--Exercicio 4
--Select list of all orders where Employee id is more than 5 but less than 10
select * from Orders
where EmployeeID between 5 and 10

--Exercicio 5
--Get list of all Territories which are ordered by territoryid in descending order
select * from Territories
Order by TerritoryID DESC

--Exercicio 6
--Get list of all products whose ProductName contains either oo or ee anywhere in ProductName

select * from Products
where ProductName IN ('oo','ee')

--Exercicio 7
--Get list of all products whose ProductName does not contain either oo or ee anywhere in ProductName

select * from Products
where ProductName NOT IN ('oo','ee')

--Exercicio 8
-- Get list ProductName , UnitsinStock on products where unitPrice is 18 but the ProductName column name should be fetched as 'Produtos'
select ProductName as 'Produtos',UnitsInStock from Products
where UnitPrice = 18

--Exercicio 9
--Get name of Country on Costumers but "delete" to duplicates
select distinct Country from Customers

--Exercicio 10
--Select all records of employees where the value of the city starts with the letter "a"
select * from Employees
where City not like '%a'

--Exercicio 11
--Select all records of employees where the first letter of the city starts with anything from an "a" to and "f"
select * from Employees
where City between '%a' and '%f'

--Exercicio 12
--USe the BETWEEN operator to select all the records where the value of the ProductName column is alphabetically between 'Geiost' and 'Pavlova'
select * from Products
where ProductName between 'Geitost' and 'Pavlova'

--Exercicio 13
--List the number of customers in each country, ordered by country with the most customers first
select Count(CustomerID),Country from Customers
Group by Country
order by Country desc