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. Even wondering 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?

-- А) Начнем с простого запросаSELECT3/2;
-- Б)SELECTmin('Какой-то текст'::TEXT), avg('Какой-то текст'::TEXT);
-- В)* Почему данный запрос может вернуть FALSE, возможно ли такое поведение СУБД?SELECT7.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 ISNOTDISTINCTFROM parent_id;

2.2. What is the result of the query?

-- А)SELECT * FROM (
    SELECT1UNION ALL
    SELECT1
    ) x(y)
UNION
(
    SELECT2UNION ALL
    SELECT2
);

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 can I pull out the CAPITAL (Russian or English) letters in a line 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, then replace at least one)

Example string:

X = 'Cтрoитeльствo или рeкoнcтрукция oбъeкта'


4.5 Write a query that converts the string:
'IVANOV IVAN IVANOVICH' to the form 'Ivanov Ivan Ivanovich'

5. A bit of transaction

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

me try to simulate an example:

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

User User_1 opens a transaction, 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 LIMIT1;

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!