Tâches et solutions pour PostgreSQL Fighter

Salutations à tous les amateurs de SQL!

Sur Internet, j'ai rarement vu des articles couvrant différents points de travail et subtilités associés au traitement des données en SQL .
J'aime quand vous pouvez apprendre beaucoup de choses d'un article à la fois, même en termes généraux.
Par conséquent, j'ai décidé d'écrire mon article contenant diverses tâches et réponses avec des explications à leur sujet.
Convient à ceux qui maîtrisent bien toutes les compétences de base et souhaitent se développer davantage.

Les réponses fournies conviennent à PostgreSQL (la plupart des tâches conviendront à d'autres SGBD , mais les résultats et les solutions peuvent être différents. C'est même intéressant où les différences surviennent)

Essayez de vous répondre avant d'ouvrir le spoiler.

C'est parti!


Je vais essayer de marquer avec un astérisque quelque chose de purement pour PostgreSQL * (il n'y a pas beaucoup de tels moments)

1. Un peu sur les opérations numériques

1.1 Ces demandes seront-elles satisfaites? Quels résultats reviendront-ils?

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

1.2 Étant donné la table " table_2 " (avec une seule colonne " valeur " (INTEGER)) composée des 5 lignes suivantes:

valeur
5
5
Null
5
5

Quel résultat renverra la requête:

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

2. Problèmes généraux

2.1 Dans quels cas une requête ne peut-elle pas renvoyer tout le contenu d'une table? ( parent_id INTEGER, le tableau est rempli d'une variété de données)

  SELECT * FROM any_table WHERE parent_id = parent_id; 

Comment se comportera la demande ci-dessous? Quelles données sortiront-elles? * PostgreSQL

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

2.2. Quel sera le résultat de la demande?

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

2.3 Écrivez une requête qui affiche la date de demain.


2.4 Les instructions UPDATE , DELETE , INSERT et MERGE sont conçues pour manipuler les données des tableaux. SELECT .. l' exécution est-elle "sûre"? Une requête peut-elle affecter les données de la table?

3. Uniquement PostgreSQL

3.1 Décrivez ce qui se passe lorsque cette requête est exécutée dans la boîte de dialogue SQL:

 SELECT * INTO wtf FROM pg_stat_activity; 

3.2 Que montrera cette "simple" demande

 SELECT wtf_ FROM pg_stat_activity AS wtf_ ; 

4. Travaillez avec du texte. Expressions régulières

Je pense que vous devez être en mesure non seulement de générer des requêtes, mais également de présenter les résultats de la bonne manière.
Les expressions régulières sont un énorme sujet distinct, avec de nombreux articles de qualité. Par conséquent, je ne montrerai que des exemples, sans explications détaillées.

4.1. Supposons qu'il existe une table " table_5 " avec une colonne de texte " X " et de nombreuses lignes différentes. Quelle requête peut obtenir les 10 derniers caractères de chaque ligne?



4.2 Il existe une table «table_6» avec une colonne de texte «X». Le tableau contient une ligne (tout le texte en anglais et en russe uniquement):

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

A) Écrivez une requête qui renverra les 42e à 68e caractères de cette chaîne
B) Comment extraire uniquement des lettres MAJUSCULES (russe ou anglais) dans une chaîne en utilisant SQL?
C) Comment calculer la somme des nombres ( pas des chiffres ) dans une chaîne en utilisant SQL




4.3 Comment remplacer tous les espaces doubles (triples ou plus) par un seul espace dans le texte (cellule du tableau)? (par tradition: la table " table_7 " avec la colonne " X ") (PS il suffira d'écrire SELECT renvoyant le résultat souhaité, et non UPDATE table_7 ... )


4.4 Il existe une chaîne " X " dans laquelle les fautes de frappe sont autorisées. Au lieu des lettres russes (e, o, s, C), des caractères similaires de l'extérieur de l'alphabet anglais ont été utilisés. Remplacez ces caractères par SQL.

PS La ligne ne doit contenir que des caractères russes, et vous ne devez pas vous inquiéter d'un éventuel changement de mots anglais.

(Si vous éprouvez des difficultés à remplacer tous les personnages, remplacez-en au moins un)

Exemple de ligne:

X = 'Coeo eoc oe'


4.5 Écrivez une requête qui convertit une chaîne:
« Ivan Ivanov et Ivanovich» à l'espèce «Ivan Ivanov

5. Un peu sur les transactions

Les transactions sont une chose très importante dans un SGBD, il est important de comprendre les principaux points.

Je vais essayer de simuler un exemple:

Supposons qu'il existe une table «biens» avec laquelle deux utilisateurs vont travailler.
Il a une colonne de remise entière égale à 10 pour toutes les lignes.
Les paramètres de la base de données sont standard (READ COMMITTED - lecture des données validées).

L'utilisateur User_1 ouvre une transaction, exécute la demande suivante:

 BEGIN; UPDATE goods SET discount = discount + 5; 

Une seconde plus tard, un autre utilisateur ( User_2 )
Il effectue presque la même demande sans ouvrir de transaction:

 UPDATE goods SET discount = discount + 10; 

Que pensez-vous qu'il se passera dans les situations suivantes:

A) Quel résultat l'Utilisateur_2 obtiendra-t-il si l'Utilisateur_1 laisse la transaction ouverte (c'est-à-dire qu'il ne confirme pas la transaction / n'annule pas les modifications)?
Ce que User_1 verra sur demande:

 SELECT discount FROM goods LIMIT 1; 

B) Que se passe-t-il si User_1 fait ROLLBACK? Quels résultats obtiendra User_2?

Q) Que se passe-t-il si User_1 COMMIT? Quels résultats obtiendra User_2?

Conclusion

Je pense que cela a touché des points assez intéressants.

J'espère que les tâches aideront à motiver les débutants, car il est ennuyeux d'apprendre quelque chose sans buts / objectifs / directions spécifiques.

Je peux être heureux pour ceux qui ont été faciles à répondre à toutes les questions. Et ceux qui ont eu des difficultés, je l'espère, se sont lancés dans la voie du développement. Ceux qui ne comprennent pas grand-chose mais veulent apprendre le SQL, j'invite le cours PostgreSQL pour les jeunes combattants à mon dernier article.

J'attends avec impatience tous les ajouts, solutions à des problèmes particulièrement intéressants (vous pouvez exploiter) et d'autres commentaires!

Merci de votre attention! Je vous souhaite beaucoup de succès dans l'apprentissage du SQL!