Tarefas e soluções para o lutador PostgreSQL

Saudações a todos os amantes de SQL!

Na Internet, raramente vi artigos que cobrissem diferentes pontos de trabalho e sutilezas associados ao processamento de dados no SQL .
Gosto quando você pode aprender muitas coisas de um artigo de uma só vez, mesmo em termos gerais.
Por isso, decidi escrever meu artigo contendo várias tarefas e respostas com explicações para eles.
Adequado para quem domina bem todas as habilidades básicas e deseja se desenvolver ainda mais.

As respostas fornecidas são adequadas para o PostgreSQL (a maioria das tarefas será adequada para outros DBMSs , mas os resultados e soluções podem ser diferentes. É até interessante onde as diferenças surgem)

Tente responder a si mesmo antes de abrir o spoiler.

Vamos lá!


Vou tentar marcar com um asterisco algo puramente para o PostgreSQL * (não existem muitos momentos assim)

1. Um pouco sobre operações numéricas

1.1 Essas solicitações serão atendidas? Quais resultados eles retornarão?

-- )     SELECT 3/2; -- ) SELECT min('- '::TEXT), avg('- '::TEXT); -- )*      FALSE,     ? SELECT 7.2 = (3.8::FLOAT + 3.4) -- ) SELECT (20/25)*25.0; 

1.2 Dada a tabela " table_2 " (com uma única coluna " value " (INTEGER)) composta pelas 5 linhas a seguir:

valor
5
5
Nulo
5
5

Qual resultado retornará a consulta:

 SELECT (avg(value)*count(*)) - sum(value) FROM table_2; 

2. Questões gerais

2.1 Em que casos uma consulta pode retornar nem todo o conteúdo de uma tabela? ( parent_id INTEGER, a tabela é preenchida com vários dados)

  SELECT * FROM any_table WHERE parent_id = parent_id; 

Como o pedido abaixo se comportará? Quais dados serão gerados? * PostgreSQL

  SELECT * FROM any_table WHERE parent_id IS NOT DISTINCT FROM parent_id; 

2.2 Qual será o resultado da solicitação?

 -- ) SELECT * FROM ( SELECT 1 UNION ALL SELECT 1 ) x(y) UNION ( SELECT 2 UNION ALL SELECT 2 ); 

2.3 Escreva uma consulta que mostre a data de amanhã.


2.4 As instruções UPDATE , DELETE , INSERT e MERGE são projetadas para manipular dados em tabelas. A execução do SELECT .. é "segura"? Qualquer consulta pode afetar os dados na tabela?

3. Somente PostgreSQL

3.1 Descreva o que acontece quando esta consulta é executada na caixa de diálogo SQL:

 SELECT * INTO wtf FROM pg_stat_activity; 

3.2 o que esse pedido "simples" mostrará

 SELECT wtf_ FROM pg_stat_activity AS wtf_ ; 

4. Trabalhe com texto. Expressões regulares

Eu acho que você precisa não apenas criar consultas, mas também apresentar os resultados da maneira correta.
Expressões regulares são um tópico enorme separado, com muitos artigos de qualidade. Portanto, mostrarei apenas exemplos, sem explicações detalhadas.

4.1 Suponha que exista uma tabela " table_5 " com uma coluna de texto " X " e muitas linhas diferentes. Qual consulta pode obter os últimos 10 caracteres de cada linha?



4.2 Existe uma tabela "table_6" com uma coluna de texto "X". A tabela contém uma linha (todo o texto apenas em inglês e russo):

 'Lorem 3 Ipsum 23 standard 7 dummy 17 text Ultimate Answer of Life ?? 777' 

A) Escreva uma consulta que retorne os caracteres 42 a 68 dessa string
B) Como extrair apenas letras maiúsculas (russo ou inglês) em uma string usando SQL?
C) Como calcular a soma dos números ( não dígitos ) em uma string usando SQL




4.3 Como substituir todos os espaços duplos (triplos ou mais) por um único espaço no texto (célula da tabela)? (por tradição: a tabela " table_7 " com a coluna " X ") (PS, basta escrever SELECT retornando o resultado desejado, e não UPDATE table_7 ... )


4.4 Existe uma string " X " na qual erros de digitação são permitidos. Em vez de letras russas (e, o, s, C), eram usados ​​caracteres externamente semelhantes do alfabeto inglês. Substitua esses caracteres pelo SQL.

PS A linha deve conter apenas caracteres russos e você não deve se preocupar com uma possível alteração nas palavras em inglês.

(Se estiver com dificuldades para substituir todos os caracteres, substitua pelo menos um)

Linha de exemplo:

X = 'Coeo eoc oe'


4.5 Escreva uma consulta que converta uma string:
« Ivan Ivanov e Ivanovich» da espécie Ivan Ivanov

5. Um pouco sobre transações

As transações são uma coisa muito importante em um SGBD; é importante entender os pontos principais.

Vou tentar simular um exemplo:

Suponha que exista uma tabela de "mercadorias" com a qual dois usuários irão trabalhar.
Possui uma coluna de desconto inteiro igual a 10 para todas as linhas.
As configurações do banco de dados são padrão (READ COMMITTED - lendo dados confirmados).

Usuário User_1 abre uma transação, executa a seguinte solicitação:

 BEGIN; UPDATE goods SET discount = discount + 5; 

Um segundo depois, outro usuário ( Usuário_2 )
Ele executa quase a mesma solicitação sem abrir uma transação:

 UPDATE goods SET discount = discount + 10; 

O que você acha que acontecerá nas seguintes situações:

A) Qual resultado o Usuário_2 obterá se o Usuário_1 deixar a transação em aberto (ou seja, não confirmar a transação / não reverter as alterações)?
O que o usuário_1 verá a pedido:

 SELECT discount FROM goods LIMIT 1; 

B) O que acontece se o Usuário_1 fizer ROLLBACK? Quais resultados o User_2 obterá?

Q) O que acontece se o Usuário_1 se comprometer? Quais resultados o User_2 obterá?

Conclusão

Eu acho que isso tocou em pontos bastante interessantes.

Espero que as tarefas ajudem a motivar os iniciantes, porque é chato aprender algo sem metas / objetivos / orientações específicas.

Fico feliz por aqueles que foram fáceis de responder a todas as perguntas. E aqueles que tiveram dificuldades, espero, deram um chute na direção do desenvolvimento. Para quem não entende muito, mas quer aprender SQL, convido o curso de lutador jovem do PostgreSQL para o meu último artigo.

Estou ansioso para quaisquer adições, soluções para problemas especialmente interessantes (você pode os meus) e outros comentários!

Obrigado pela atenção! Desejo-lhe sucesso em aprender SQL!