Tasks and solutions for the PostgreSQL fighter

Greetings to all SQL lovers!

On the Internet, I rarely met articles that cover different working points and subtleties associated with data processing in SQL .
I like it when you can learn a lot from one article at once, even if in general terms.
Therefore, I decided to write my article containing various tasks and answers with explanations to them.
Suitable for those who have mastered all the basic skills and wants to develop further.

The answers provided are suitable for PostgreSQL ( most of the tasks are suitable for other DBMSs , but the results and solutions may be different. It is even interesting where the differences will arise)
')
Try to answer yourself before opening the spoiler.

Go!


I will try to mark with an asterisk, something especially for PostgreSQL * (there are not many such moments)

1. A bit about numeric operations

1.1 Will these queries be executed? What results will they return?

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

1.2 Given the table " table_2 " (with a single column " value " (INTEGER)) consisting of the following 5 lines:

value
five
five
Null
five
five

What result will return the query:

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

2. General issues

2.1 When can a query return not the entire contents of a table? ( parent_id INTEGER, the table is filled with various data)

  SELECT * FROM any_table WHERE parent_id = parent_id; 

And how will the query behave below? What data will it display? * PostgreSQL

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

2.2. What is the result of the query?

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

2.3 Write a request that will show tomorrow's date.


2.4 The UPDATE , DELETE , INSERT and MERGE statements are designed to manipulate data in tables. Is the execution of SELECT .. "safe"? Can any query affect the data in the table?

3. Only PostgreSQL

3.1 Describe what will happen when executing this query in the SQL dialog:

 SELECT * INTO wtf FROM pg_stat_activity; 

3.2 what this “simple” query will show

 SELECT wtf_ FROM pg_stat_activity AS wtf_ ; 

4. Work with the text. Regular expressions

I think you need to be able not only to build queries, but also to present the results in the right form.
Regular expressions are a separate huge topic, with many quality articles. Therefore, I will only show examples, without detailed explanations.

4.1. Suppose there is a table " table_5 " with a text column " X " and a variety of different rows. What query can I get any last 10 characters of each line?



4.2 There is a table “table_6” with a text column “X”. The table contains one row (all text is in English and Russian only):

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

A) Write a query that returns the characters from the 42nd to the 68th of this line.
B) How to pull out only CAPITAL (Russian or English) letters in a row using SQL?
C) How to calculate the sum of numbers ( not numbers ) in a row using SQL




4.3 How to replace in the text (cell of the table) all double (triple or more) spaces with a single space? (by tradition: table " table_7 " with column " X ") (PS it is enough to write SELECT that returns the desired result, and not UPDATE table_7 ... )


4.4 There is a string " X " in which typos are made. Instead of Russian letters (e, o, c, C), outwardly similar to them characters of the English alphabet were used. Replace these characters with SQL.

PS The line should contain only Russian characters, and you should not worry about a possible change of English words.

(If it is difficult to replace all characters, replace at least one)

Example line:

X = 'Coeo eoc oe'


4.5 Write a query that converts a string:
Ivan IVAN Ivanov Ivanov to the Ivan Ivan Ivanovich

5. A bit of transaction

Transactions are very important thing in a DBMS, it is quite important to understand the main points.

I will try to simulate an example:

Suppose there is a table “goods” with which two users are going to work.
It has an integer discount column of 10 for all rows.
Database settings are standard (READ COMMITTED - reading of captured data).

User_1 opens a transaction, executes the following query:

 BEGIN; UPDATE goods SET discount = discount + 5; 

A second later, another user ( User_2 )
Executes almost the same query without opening a transaction:

 UPDATE goods SET discount = discount + 10; 

What do you think will happen in the following scenario:

A) What result will User_2 get if User_1 leaves the transaction open (i.e., does not confirm the transaction / does not roll back the changes)?
What User_1 will see when prompted:

 SELECT discount FROM goods LIMIT 1; 

B) What happens if User_1 does a ROLLBACK? What results will User_2 get?

Q) What happens if User_1 makes a COMMIT? What results will User_2 get?

Conclusion

I think I touched on some interesting points.

I hope the tasks will help to motivate beginners, because it’s boring to learn anything without specific goals / objectives / directions.

I can be glad for those who were easy to answer all the questions. And those who had difficulties, I hope, got a kick in the direction of development. Those who have understood little, but want to master SQL, I invite you to my last article, The Course of a Young PostgreSQL Fighter .

I am waiting for any additions, solutions of particularly interesting problems (you can own) and other comments!

Thanks for attention! I wish you success in learning SQL!