course.sql

---https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in
---
SELECT ContactName, Address, City FROM Customers;
----
SELECT * FROM Customers WHERE CustomerID > 50
---
SELECT * FROM Customers WHERE CustomerID >= 50
---
SELECT * FROM Customers WHERE CustomerID <= 50
---
SELECT * FROM Customers WHERE CustomerID = 50
---
SELECT * FROM Customers WHERE CustomerID = 50 AND CustomerID = 44
---
SELECT * FROM Customers WHERE CustomerID = 50 OR CustomerID = 44
---
SELECT * FROM Customers WHERE Country = "Mexico"
---
SELECT * FROM Customers WHERE Country = "Mexico" AND CustomerID > 3
---
SELECT * FROM Customers WHERE NOT Country = "Mexico" AND CustomerID > 3
---
SELECT * FROM Customers WHERE CustomerID > 10 AND CustomerID < 20
---
SELECT * FROM Customers WHERE CustomerID BETWEEN 10 AND 20
---
SELECT * FROM Customers WHERE Country LIKE "s%"
---
SELECT * FROM Customers WHERE Country LIKE "%s%"
---
SELECT * FROM Customers WHERE Country LIKE "%a%"
---
SELECT * FROM Customers WHERE CustomerName LIKE "A%"
---
SELECT * FROM Customers ORDER BY Country
---
SELECT * FROM Customers ORDER BY Country ASC;
---
SELECT * FROM Customers ORDER BY Country DESC;
---
SELECT * FROM Customers ORDER BY Country, Address;
---
SELECT DISTINCT Country FROM Customers ORDER BY Country
---
SELECT DISTINCT * FROM Customers ORDER BY Country
---
SELECT CustomerID AS 'Código', CustomerName AS 'Nome do cliente' FROM Customers
---
---EX
SELECT CustomerName FROM Customers ORDER BY CustomerName DESC
---
SELECT * FROM Customers WHERE Country = "Brazil" AND CustomerID >= 10 AND CustomerID <=34
---
SELECT * FROM Customers WHERE Country = "Brazil" AND CustomerID BETWEEN 10 AND 34
---
SELECT CustomerName AS 'Nome do Cliente' FROM Customers 
WHERE Country = "Mexico" 
AND CustomerName LIKE 'A%' 
ORDER BY CustomerName DESC;
---
INSERT INTO Customers (CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country) 
VALUES (92, "Well", "Welzinho", "Rua Tal", "Recife", "12345", "Brazil")
---
SELECT * FROM Customers
---
UPDATE Customers 
SET
CustomerName = 'João da Silva'
WHERE
CustomerID = 92
---
---
UPDATE Customers 
SET
Address = 'Rua z'
WHERE
Country = 'Brazil'
---
DELETE FROM Customers 
WHERE CustomerID = 4
---
DELETE FROM Customers 
WHERE CustomerID > 4
---
CREATE TABLE clientes (codigo INT, 
                       nome VARCHAR(60), 
                       endereco VARCHAR(100), 
                       bairro VARCHAR(60));
---
INSERT INTO clientes (codigo, nome, endereco, bairro) 
VALUES (1, 'Well', 'Rua Tal', 'Bairro Feliz');
---
DROP TABLE clientes;
---
ALTER TABLE clientes RENAME TO tb_clientes;
---
ALTER TABLE clientes ADD cep VARCHAR(9);
---
CREATE TABLE clientes (codigo INT PRIMARY KEY, 
                       nome VARCHAR(60), 
                       endereco VARCHAR(100), 
                       bairro VARCHAR(60))
---
CREATE TABLE clientes (codigo INTEGER PRIMARY KEY AUTOINCREMENT, 
                       nome VARCHAR(60), 
                       endereco VARCHAR(100),  
                       bairro VARCHAR(60))
---
INSERT INTO clientes (nome, endereco, bairro) 
VALUES ('Well', 'Rua Tal', 'Bairro Feliz');
---
SELECT * FROM albums, artists
WHERE 
albums.ArtistId = artists.ArtistId
---
SELECT albums.Title, artists.Name FROM albums, artists
WHERE 
albums.ArtistId = artists.ArtistId
---
SELECT AB.Title, ART.Name FROM albums AB, artists ART
WHERE 
AB.ArtistId = ART.ArtistId
---
SELECT * FROM albums
JOIN artists on artists.ArtistId = albums.ArtistId
---
SELECT * FROM albums
JOIN artists on artists.ArtistId = albums.ArtistId
---
SELECT albums.Title, artists.Name FROM albums
LEFT outer join artists On artists.ArtistId = albums.ArtistId
---
CREATE TABLE profissioes (
  id INTEGER PRIMARY KEY AUtoincrement,
  nome VArchar(30)
  )
---
create table clientes (
  id INTEGER PRIMARY KEY AUtoincrement,
  nome VArchar(30),
  profissao_id INTEGER,
  foreign key (profissao_id) references profissioes(id)
  )
---
insert into profissioes (nome) Values ('Advogado')
---
insert into clientes (nome, profissao_id) Values ('João', 1)
---
PRIMARY foreign_keys = ON;
---