05 PostgreSQL Flashcards
1
Q
select count (distinct rating) from film
A
retorna quantos tipos diferentes de rating existe na coluna rating da tabela film
2
Q
select count (*) from payment
- pode ser amount
A
quantas linhas tem na tabela payment
3
Q
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 >,
A
.
4
Q
=, >, >=, significam não igual a
A
.
5
Q
AND
OR
NOT
A
.
6
Q
select description from film
where title = ‘Outlaw Hanky’
A
Qual a descrição do filme Outlaw Hanky?
7
Q
select colunaX
from tableY
order by colunaX ASC/DESC
A
organiza por ASCendente ou DESCendente
8
Q
select store_id, first_name from customer
order by store_id desc, first_name asc
A
retorna o codigo da loja e nome do cliente organizados primeiramente por ordem descendente do id da loja e em ordem ascendente dos nomes
9
Q
select customer_id from payment
order by payment_date asc
limit 10
A
Os 10 primeiros clientes que fizeram pagamento
10
Q
BETWEEN low AND high
(inclusive, not between is exclusive)
equivale a
value >= low AND value <= high
A
.
11
Q
select * from payment
where amount between 8 and 9
A
Inclui 8, 9 e o que está entre eles
12
Q
select * from payment
where payment_date between ‘2007-02-01’ and ‘2007-02-15’
A
Tudo entre 0 hora de 01/02/2007 e 23:59:59.999999 de 14/02/2007
13
Q
select * from table
where color IN (‘red’, ‘blue’)
A
seleciona toda a linha em que color é vermelha ou azul
14
Q
select * from table
where color IN (‘red’, ‘blue’)
poderia usar NOT IN pra mostrar o contrário
A
seleciona toda a linha em que color é vermelha ou azul
15
Q
%
A
WILDCARD
16
Q
% (percent)
_ (underscore)
A
WILDCARD, matches any sequence of characters
matches any single character
17
Q
LIKE
ILIKE
A
case sensitive
case-insensitive
18
Q
% (percent)
_ (underscore)
A
WILDCARD, matches any sequence of characters. % pode ser nada.
matches any single character
19
Q
select * from tableX
WHERE name ILIKE ‘_her%’
A
seleciona os nomes com uma letra antes do her e depois pode vim qualquer coisa.
cHERyl
tHEResa
sHERri
20
Q
SELECT * from customer
WHERE first_name ILIKE ‘j%’ AND last_name ILIKE ‘s%’
A
retorna se primeiro nome começa com J/j e segundo com S/s
21
Q
The main idea behind aggregate function is to take multiple inputs and return a single output
A
.
22
Q
most commom aggregate functions are
they happen only in select or having clause
A
avg - média count - quantidade max - maximo min - minimo sum - soma
round ( ) para arredondar
23
Q
select round ( avg (replacement_cost), 2 )
A
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
24
Q
GROUP BY: CATEGORY column (non continuous variable) and VALUE column
GROUP BY aparece logo após WHERE statement ou FROM statment
A
.
25
SELECT category_column, AGG data_column
FROM tableX
GROUP BY category_column
Se tiver WHERE statment, não pode se referir ao AGG
.
26
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
27
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
28
```
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
29
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
30
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)
31
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
32
```
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
33
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
34
```
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
35
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
36
```
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
37
```
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á?
38
TIME
DATE
TIMESTAMP
TIMESTAMPTZ
Tempo
Data
Data e Tempo
Data, Tempo e TimeZone
39
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
40
TO_CHAR (date_column, 'YYYY-MM-DD' )
Transforma date to text
41
```
select extract (year from payment_date) as pay_month
from payment
```
retorna somente o ano do payment
42
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
43
```
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
44
select first_name || last_name
from customer
select first_name || ' ' || last_name
from customer
ArturFaria
Artur Faria
45
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
46
SELECT student, grade
FROM test_scores
WHERE grade > (SELECT AVG (grade) FROM teste_scores)
seleciona os estudantes maior que a média
47
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
48
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
49
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
50
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
51
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
52
```
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
53
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
54
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
55
SERIAL type
cria número crescente em ordem, é perfeito para id por ser único
56
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
57
```
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
58
```
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
59
INSERT INTO tableX (column1, column2, ...)
VALUES
(value1, value2, ...)
(value1, value2, ...)
INSERT INTO tableX (column1, column2,...)
SELECT column1, column2, ...
FROM tableY
WHERE condition
General Syntax
60
INSERT INTO account(username, password, email, created_on)
VALUES
('Jose', 'password', 'jose@mail.com', current_timestamp)
serial_id é criado automaticamente
| adiciona valores em ordem
61
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
62
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
63
DELETE FROM tableA
WHERE columnB = valueC
RETURNING xxxxx
sintaxe básica de delete
| Returning mostra o que foi deletado
64
DELETE FROM tableA
USING tableB
WHERE tableA.id=tableB.id
deleta numa tabela se aquela informação está na outra
65
DELETE FROM tableX
deleta tableX inteira
66
ALTER TABLE tableX
general syntax de alter table
67
```
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
68
```
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
69
```
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
70
SELECT COALESCE ( )
retorna o primeiro valor not null na lista
71
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
72
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)
73
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
74
create view nameview as
...
(qualquer coisa)
select * from nameview
retorna a View inteira
