Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

PUC MG > 05 PostgreSQL > Flashcards

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

select count (*) from payment

  • pode ser amount
A

quantas linhas tem na tabela payment

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

=, >, >=, significam não igual a

A

.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

AND
OR
NOT

A

.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

select description from film

where title = ‘Outlaw Hanky’

A

Qual a descrição do filme Outlaw Hanky?

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

select colunaX
from tableY
order by colunaX ASC/DESC

A

organiza por ASCendente ou DESCendente

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

select customer_id from payment
order by payment_date asc
limit 10

A

Os 10 primeiros clientes que fizeram pagamento

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

BETWEEN low AND high
(inclusive, not between is exclusive)

equivale a

value >= low AND value <= high

A

.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

select * from payment

where amount between 8 and 9

A

Inclui 8, 9 e o que está entre eles

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

select * from table

where color IN (‘red’, ‘blue’)

A

seleciona toda a linha em que color é vermelha ou azul

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

%

A

WILDCARD

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

% (percent)

_ (underscore)

A

WILDCARD, matches any sequence of characters

matches any single character

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

LIKE

ILIKE

A

case sensitive

case-insensitive

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

% (percent)

_ (underscore)

A

WILDCARD, matches any sequence of characters. % pode ser nada.

matches any single character

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

The main idea behind aggregate function is to take multiple inputs and return a single output

A

.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q 
GROUP BY:
CATEGORY column (non continuous variable) and VALUE column

GROUP BY aparece logo após WHERE statement ou FROM statment

A

.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

SELECT category_column, AGG data_column
FROM tableX
GROUP BY category_column

Se tiver WHERE statment, não pode se referir ao AGG

A

.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

select customer_id, sum(amount) from payment
group by customer_id
order by sum(amount)

A

junta todos os pagamentos de cada cliente, soma eles e retorna na ordem crescente de valores

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

select customer_id, staff_id, sum(amount) from payment
group by staff_id, customer_id
order by staff_id, sum(amount)

A

organiza por ordem de soma de compras em cada um dos staffs

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q 
SELECT company, SUM(sales)
FROM finance_table
WHERE company != 'Google'
GROUP BY company
HAVING SUM(sales) > 1000
A

Retorna as companias com soma de vendas maior que 1000, exceto o google

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

SELECT columnX AS new_nameX
FROM tableX

AS é executado só no fim, não pode ser usado no WHERE ou HAVING

A

cria o alias (label) new_nameX para a columnX

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

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

A

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
Q

select payment_id, payment.customer_id, first_name
from payment
inner join customer
on payment.customer_id = customer.customer_id

A

retorna dados de duas tabelas distintas, desde que nas duas o customer_id seja o mesmo, fazendo uma só linha

32
Q 
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
A

verifica se há algum cliente que não fez nenhuma compra ou se alguma compra não tem cadastro do cliente

33
Q

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

A

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
Q 
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
A

Retorna todos os filmes que não estão no inventório da locadora

35
Q

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
A

retorna tudo que tem nas duas

36
Q 
select email 
from address
left join customer on
customer.address_id = address.address_id
where district = 'California'
A

retorna o email de todas as pessoas que moram na california

37
Q 
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'
A

reparar como funciona INNER JOIN:
1 from pra 2 join pode ser usado

Como saber quais filmes o ator “Nick Wahlberg”está?

38
Q

TIME
DATE
TIMESTAMP
TIMESTAMPTZ

A

Tempo
Data
Data e Tempo
Data, Tempo e TimeZone

39
Q

SHOW TIMEZONE

SELECT NOW ( )

SELECT TIMEOFDAY ( )

SELECT CURRENT_TIME ( )

SELECT CURRENT_DATE ( )

A

Mostra qual zona estamos

Informa data, hora, timezone

Informa data, hora, timezone, dia da semana

tempo com timezone

data

40
Q

TO_CHAR (date_column, ‘YYYY-MM-DD’ )

A

Transforma date to text

41
Q 
select extract (year from payment_date) as pay_month
from payment
A

retorna somente o ano do payment

42
Q

select to_char (payment_date, ‘Month/YYYY’) from payment

select to_char (payment_date, ‘MONTH - YYYY’) from payment

A

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
Q 
select round (100*rental_rate/replacement_cost, 1) as percent_cost
from film
A

retorna arredondado por décimo, a porcentagem entre rental_rate e replacement_cost

44
Q

select first_name || last_name
from customer

select first_name || ‘ ‘ || last_name
from customer

A

ArturFaria

Artur Faria

45
Q

select first_name || last_name
from customer

select first_name || ‘ ‘ || last_name
from customer

select lower(first_name) || ' ' || lower(last_name)
from customer
A

ArturFaria

Artur Faria

artur faria

46
Q

SELECT student, grade
FROM test_scores
WHERE grade > (SELECT AVG (grade) FROM teste_scores)

A

seleciona os estudantes maior que a média

47
Q

SELECT student, grade
FROM test_scores
WHERE grade > (SELECT AVG (grade) FROM teste_scores)

A

seleciona os estudantes maior que a média

executa primeiro a SUBQUERY entre parênteses

48
Q

select title, rental_rate
from film
where rental_rate >
(select avg(rental_rate) from film)

A

primeiro seleciona a média (AVG), depois faz o resto

49
Q

select title, rental_rate
from film
where rental_rate >
(select avg(rental_rate) from film)

A

primeiro seleciona a média (AVG), depois faz o resto

50
Q

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’)

A

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
Q

SELF-JOIN
Precisa usar Alias (AS)
Nesse caso a tableA e tableB é a mesma tabela chamada “table”

A

SELECT tableA.col, tableB.col
FROM table AS tableA
JOIN table AS tableB ON
tableA.some_col = tableB.other_col

52
Q 
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
A

retorna os filmes com a mesma duração

53
Q

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

A

Faz mais sentido salvar phone number as text base data

54
Q

A primary key is a column or a group of columns used to identify a row uniquely in a table

[PK]

pkey

A

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
Q

SERIAL type

A

cria número crescente em ordem, é perfeito para id por ser único

56
Q

SERIAL type

A

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
Q 
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
)
A

user_id é primary key serial

last login pode ser nulo

muitos não precisam ser únicos

58
Q 
CREATE TABLE account_job(
user_id INTEGER REFERENCES account(user_id),
job_id INTEGER REFERENCES job(job_id),
hire_date TIMESTAMP
)
A

references é que pega key de outra tabela

59
Q

INSERT INTO tableX (column1, column2, …)
VALUES
(value1, value2, …)
(value1, value2, …)

INSERT INTO tableX (column1, column2,…)
SELECT column1, column2, …
FROM tableY
WHERE condition

A

General Syntax

60
Q

INSERT INTO account(username, password, email, created_on)
VALUES
(‘Jose’, ‘password’, ‘jose@mail.com’, current_timestamp)

A

serial_id é criado automaticamente

adiciona valores em ordem

61
Q

UPDATE tableX
SET columnA = value1,
columnB = value2, …
WHERE condition

UPDATE tableX
SET columnA = columnB

A

Muda valores da tabela

Coloca os valores de B na A

62
Q

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

A

Muda valores da tabela

Coloca os valores da coluna B na A

Coloca os valores da tabela B na A

63
Q

DELETE FROM tableA
WHERE columnB = valueC
RETURNING xxxxx

A

sintaxe básica de delete

Returning mostra o que foi deletado

64
Q

DELETE FROM tableA
USING tableB
WHERE tableA.id=tableB.id

A

deleta numa tabela se aquela informação está na outra

65
Q

DELETE FROM tableX

A

deleta tableX inteira

66
Q

ALTER TABLE tableX

A

general syntax de alter table

67
Q 
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
A

retorna o customer por id e se tipodecliente é Premium, Plus ou Normal

68
Q 
SELECT first_name, last_name,
CASE customer_id
WHEN 2 THEN 'Winner'
WHEN 5 THEN 'Runner-Up'
ELSE 'Normal'
END as results
FROM customer
A

reparar no CASE customer_id que depois não precisa mais ser inserido

69
Q 
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
A

bargains 341
cheap 323

Retorna o número de produtos com preço 0.99 e 2.99

70
Q

SELECT COALESCE ( )

A

retorna o primeiro valor not null na lista

71
Q

SELECT COALESCE ( )

SELECT item, ( price - COALESCE (discount, 0 ) )
AS final FROM tableX

A

retorna o primeiro valor not null na lista

se o desconto é nulo, retorna o preço original

72
Q

CAST operator let`s you convert from one data type into another

SELECT CAST(‘5’ AS INTEGER)

or

SELECT ‘5’::INTEGER

A

transforma numero em inteiro, ou outra mudança (tipo de data etc)

73
Q

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

A

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
Q

create view nameview as
…
(qualquer coisa)

A

select * from nameview

retorna a View inteira

PUC MG (15 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions