05 PostgreSQL Flashcards
select count (distinct rating) from film
retorna quantos tipos diferentes de rating existe na coluna rating da tabela film
select count (*) from payment
- pode ser amount
quantas linhas tem na tabela payment
SELECT x, AGG(j) FROM y WHERE z GROUP BY columnC HAVING AGG(j) ? 67 ORDER BY columnD LIMIT 666
nessa ordem
? símbolo de >,
.
=, >, >=, significam não igual a
.
AND
OR
NOT
.
select description from film
where title = ‘Outlaw Hanky’
Qual a descrição do filme Outlaw Hanky?
select colunaX
from tableY
order by colunaX ASC/DESC
organiza por ASCendente ou DESCendente
select store_id, first_name from customer
order by store_id desc, first_name asc
retorna o codigo da loja e nome do cliente organizados primeiramente por ordem descendente do id da loja e em ordem ascendente dos nomes
select customer_id from payment
order by payment_date asc
limit 10
Os 10 primeiros clientes que fizeram pagamento
BETWEEN low AND high
(inclusive, not between is exclusive)
equivale a
value >= low AND value <= high
.
select * from payment
where amount between 8 and 9
Inclui 8, 9 e o que está entre eles
select * from payment
where payment_date between ‘2007-02-01’ and ‘2007-02-15’
Tudo entre 0 hora de 01/02/2007 e 23:59:59.999999 de 14/02/2007
select * from table
where color IN (‘red’, ‘blue’)
seleciona toda a linha em que color é vermelha ou azul
select * from table
where color IN (‘red’, ‘blue’)
poderia usar NOT IN pra mostrar o contrário
seleciona toda a linha em que color é vermelha ou azul
%
WILDCARD
% (percent)
_ (underscore)
WILDCARD, matches any sequence of characters
matches any single character
LIKE
ILIKE
case sensitive
case-insensitive
% (percent)
_ (underscore)
WILDCARD, matches any sequence of characters. % pode ser nada.
matches any single character
select * from tableX
WHERE name ILIKE ‘_her%’
seleciona os nomes com uma letra antes do her e depois pode vim qualquer coisa.
cHERyl
tHEResa
sHERri
SELECT * from customer
WHERE first_name ILIKE ‘j%’ AND last_name ILIKE ‘s%’
retorna se primeiro nome começa com J/j e segundo com S/s
The main idea behind aggregate function is to take multiple inputs and return a single output
.
most commom aggregate functions are
they happen only in select or having clause
avg - média count - quantidade max - maximo min - minimo sum - soma
round ( ) para arredondar
select round ( avg (replacement_cost), 2 )
retorna a média do replacement cost, arredondada em dois dígitos decimais (décimo e centésimo), padrão seriam uns 10 dígitos decimais
GROUP BY: CATEGORY column (non continuous variable) and VALUE column
GROUP BY aparece logo após WHERE statement ou FROM statment
.
SELECT category_column, AGG data_column
FROM tableX
GROUP BY category_column
Se tiver WHERE statment, não pode se referir ao AGG
.
select customer_id, sum(amount) from payment
group by customer_id
order by sum(amount)
junta todos os pagamentos de cada cliente, soma eles e retorna na ordem crescente de valores
select customer_id, staff_id, sum(amount) from payment
group by staff_id, customer_id
order by staff_id, sum(amount)
organiza por ordem de soma de compras em cada um dos staffs
SELECT company, SUM(sales) FROM finance_table WHERE company != 'Google' GROUP BY company HAVING SUM(sales) > 1000
Retorna as companias com soma de vendas maior que 1000, exceto o google
SELECT columnX AS new_nameX
FROM tableX
AS é executado só no fim, não pode ser usado no WHERE ou HAVING
cria o alias (label) new_nameX para a columnX
SELECT * FROM tableX
INNER JOIN tableY
ON tableX.col_match = tableY.col_match
SELECT * FROM tableA
FULL OUTER JOIN tableB
ON tableA.col_match = tableB.col_match
SELECT * FROM tableA
FULL OUTER JOIN tableB
ON tableA.col_match = tableB.col_match
WHERE tableA.id IS null OR tableB.id IS null
Retorna a linha que TEM NAS DUAS
Pode omitir o INNER
A + B, completa com null
A + B exceto onde A = B (oposto de INNER JOIN)
select payment_id, payment.customer_id, first_name
from payment
inner join customer
on payment.customer_id = customer.customer_id
retorna dados de duas tabelas distintas, desde que nas duas o customer_id seja o mesmo, fazendo uma só linha
select * from customer full outer join payment ON customer.customer_id = payment.customer_id where customer.customer_id is null or payment.payment_id is null
verifica se há algum cliente que não fez nenhuma compra ou se alguma compra não tem cadastro do cliente
SELECT * FROM tableA
LEFT OUTER JOIN tableB
ON tableA.col_match = tableB.col_match
SELECT * FROM tableA
RIGHT JOIN tableB
ON tableA.col_match = tableB.col_match
SELECT * FROM tableA
LEFT OUTER JOIN tableB
ON tableA.col_match = tableB.col_match
WHERE tableB.id IS null
pode-se abreviar e tirar o OUTER
Tudo da tableA, tudo da tableB que tem na table A
Se tem só na B, não entra
Tudo da tableB, tudo da tableA que tem na table B
Se tem só na A, não entra
Tudo que só tem na A
select film.film_id, title, inventory_id, store_id from film left join inventory on inventory.film_id = film.film_id where inventory.film_id is null
Retorna todos os filmes que não estão no inventório da locadora
The UNION operator is used to combine the result-set of two or more SELECT statements.
It basically serves to directly concatenate two results together, essentially “pasting”them together.
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2
retorna tudo que tem nas duas
select email from address left join customer on customer.address_id = address.address_id where district = 'California'
retorna o email de todas as pessoas que moram na california
select title from film_actor join actor on film_actor.actor_id = actor.actor_id join film on film_actor.film_id = film.film_id where first_name = 'Nick' and last_name = 'Wahlberg'
reparar como funciona INNER JOIN:
1 from pra 2 join pode ser usado
Como saber quais filmes o ator “Nick Wahlberg”está?
TIME
DATE
TIMESTAMP
TIMESTAMPTZ
Tempo
Data
Data e Tempo
Data, Tempo e TimeZone
SHOW TIMEZONE
SELECT NOW ( )
SELECT TIMEOFDAY ( )
SELECT CURRENT_TIME ( )
SELECT CURRENT_DATE ( )
Mostra qual zona estamos
Informa data, hora, timezone
Informa data, hora, timezone, dia da semana
tempo com timezone
data
TO_CHAR (date_column, ‘YYYY-MM-DD’ )
Transforma date to text
select extract (year from payment_date) as pay_month from payment
retorna somente o ano do payment
select to_char (payment_date, ‘Month/YYYY’) from payment
select to_char (payment_date, ‘MONTH - YYYY’) from payment
Retorna o Mês com a primeira letra Maiúscula e o ano completo separados por barra.
Ex: February/2007
Retorna o mês com todas maiúsculas, um espaço antes do - e dois espaços após o -.
Ex: FEBRUARY - 2007
select round (100*rental_rate/replacement_cost, 1) as percent_cost from film
retorna arredondado por décimo, a porcentagem entre rental_rate e replacement_cost
select first_name || last_name
from customer
select first_name || ‘ ‘ || last_name
from customer
ArturFaria
Artur Faria
select first_name || last_name
from customer
select first_name || ‘ ‘ || last_name
from customer
select lower(first_name) || ' ' || lower(last_name) from customer
ArturFaria
Artur Faria
artur faria
SELECT student, grade
FROM test_scores
WHERE grade > (SELECT AVG (grade) FROM teste_scores)
seleciona os estudantes maior que a média
SELECT student, grade
FROM test_scores
WHERE grade > (SELECT AVG (grade) FROM teste_scores)
seleciona os estudantes maior que a média
executa primeiro a SUBQUERY entre parênteses
select title, rental_rate
from film
where rental_rate >
(select avg(rental_rate) from film)
primeiro seleciona a média (AVG), depois faz o resto
select title, rental_rate
from film
where rental_rate >
(select avg(rental_rate) from film)
primeiro seleciona a média (AVG), depois faz o resto
SELECT film_id, title
FROM film
WHERE film_id IN
(SELECT inventory.film_id
FROM rental
INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id
WHERE return_date BETWEEN ‘2005-05-29’ and ‘2005-05-30’)
retorna o film_id e title do film que foi devolvido entre essas datas. de (SELECT até -30’), está tudo entre parênteses e é uma SUBQUERY
SELF-JOIN
Precisa usar Alias (AS)
Nesse caso a tableA e tableB é a mesma tabela chamada “table”
SELECT tableA.col, tableB.col
FROM table AS tableA
JOIN table AS tableB ON
tableA.some_col = tableB.other_col
SELECT f1.title, f2.title, f1.length FROM film AS f1 INNER JOIN film AS f2 ON f1.film_id != f2.film_id AND f1.length = f2.length
retorna os filmes com a mesma duração
Boolean - True, False
Character - char, varchar, text
Numeric - integer, floating-point number
Temporal - date, time, timestamp, interval
UUID - Universally Unique Identifiers
Array - stores an array of strings, numbers, etc
JSON
Hstore key-value pair
Special types such as network adderss and geometric data
Faz mais sentido salvar phone number as text base data
A primary key is a column or a group of columns used to identify a row uniquely in a table
[PK]
pkey
A foreign key is a field or group of fields in a table that uniquely identifies a row in another table
A foregin key is defined in a table that references to the primary key of the other table
fkey
SERIAL type
cria número crescente em ordem, é perfeito para id por ser único
SERIAL type
cria número crescente em ordem, é perfeito para id por ser único
se remover, dá pra saber algo que foi removido porque nao volta a ter aquela id
create table account( user_id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, password varchar(50) NOT NULL, email VARCHAR(250) UNIQUE NOT NULL, created_on TIMESTAMP NOT NULL, last_login TIMESTAMP )
user_id é primary key serial
last login pode ser nulo
muitos não precisam ser únicos
CREATE TABLE account_job( user_id INTEGER REFERENCES account(user_id), job_id INTEGER REFERENCES job(job_id), hire_date TIMESTAMP )
references é que pega key de outra tabela
INSERT INTO tableX (column1, column2, …)
VALUES
(value1, value2, …)
(value1, value2, …)
INSERT INTO tableX (column1, column2,…)
SELECT column1, column2, …
FROM tableY
WHERE condition
General Syntax
INSERT INTO account(username, password, email, created_on)
VALUES
(‘Jose’, ‘password’, ‘jose@mail.com’, current_timestamp)
serial_id é criado automaticamente
adiciona valores em ordem
UPDATE tableX
SET columnA = value1,
columnB = value2, …
WHERE condition
UPDATE tableX
SET columnA = columnB
Muda valores da tabela
Coloca os valores de B na A
UPDATE tableX
SET columnA = value1,
columnB = value2, …
WHERE condition
UPDATE tableX
SET columnA = columnB
UPDATE tableA
SET colunaA = coluna B
FROM tableB
WHERE tableA.id = tableB.id
Muda valores da tabela
Coloca os valores da coluna B na A
Coloca os valores da tabela B na A
DELETE FROM tableA
WHERE columnB = valueC
RETURNING xxxxx
sintaxe básica de delete
Returning mostra o que foi deletado
DELETE FROM tableA
USING tableB
WHERE tableA.id=tableB.id
deleta numa tabela se aquela informação está na outra
DELETE FROM tableX
deleta tableX inteira
ALTER TABLE tableX
general syntax de alter table
SELECT customer_id, CASE WHEN (customer_id <= 100) THEN 'Premium' WHEN (customer_id BETWEEN 100 and 200) THEN 'Plus' ELSE 'Normal' END as FROM customer
retorna o customer por id e se tipodecliente é Premium, Plus ou Normal
SELECT first_name, last_name, CASE customer_id WHEN 2 THEN 'Winner' WHEN 5 THEN 'Runner-Up' ELSE 'Normal' END as results FROM customer
reparar no CASE customer_id que depois não precisa mais ser inserido
SELECT SUM(CASE rental_rate WHEN 0.99 THEN 1 ELSE 0 END) AS bargains, SUM(CASE rental_rate WHEN 2.99 THEN 1 ELSE 0 END) as cheap FROM film
bargains 341
cheap 323
Retorna o número de produtos com preço 0.99 e 2.99
SELECT COALESCE ( )
retorna o primeiro valor not null na lista
SELECT COALESCE ( )
SELECT item, ( price - COALESCE (discount, 0 ) )
AS final FROM tableX
retorna o primeiro valor not null na lista
se o desconto é nulo, retorna o preço original
CAST operator let`s you convert from one data type into another
SELECT CAST(‘5’ AS INTEGER)
or
SELECT ‘5’::INTEGER
transforma numero em inteiro, ou outra mudança (tipo de data etc)
SELECT (
SUM(CASE WHEN department = ‘A’ THEN 1 ELSE 0 END)/
SUM(CASE WHEN department = ‘B’ THEN 1 ELSE 0 END)
) AS ratio
FROM depts
select (
sum(case when department = ‘A’ then 1 else 0 end)/
NULLIF (sum(case when department = ‘B’ then 1 else 0 end), 0)
) as ratio
from depts
proporção de pessoas do department A/B
se B for zero da erro porque nao pode dividir por 0
retorna null se nullif (…) for 0
create view nameview as
…
(qualquer coisa)
select * from nameview
retorna a View inteira
