🏹 🕊️ 👩🏾‍🤝‍👩🏼 Tugas dan solusi untuk pejuang PostgreSQL 🚘 👨🏼‍🎨 👤

Salam untuk semua pecinta SQL!

Di Internet, saya jarang melihat artikel yang mencakup berbagai titik kerja dan seluk beluk yang terkait dengan pemrosesan data dalam SQL .
Saya suka ketika Anda bisa belajar banyak hal dari satu artikel sekaligus, bahkan secara umum.
Karena itu, saya memutuskan untuk menulis artikel saya yang berisi berbagai tugas dan jawaban dengan penjelasan untuknya.
Cocok untuk mereka yang telah menguasai semua keterampilan dasar dengan baik dan ingin mengembangkan lebih lanjut.

Jawaban yang diberikan cocok untuk PostgreSQL ( sebagian besar tugas akan cocok untuk DBMS lainnya , tetapi hasil dan solusinya mungkin berbeda. Bahkan menarik ketika ada perbedaan)

Coba jawab sendiri sebelum membuka spoiler.

Ayo pergi!

Saya akan mencoba menandai dengan tanda bintang sesuatu murni untuk PostgreSQL * (tidak ada banyak momen seperti itu)

1. Sedikit tentang operasi numerik

1.1 Apakah permintaan ini dapat dipenuhi? Hasil apa yang akan mereka kembalikan?

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

Jawaban untuk 1.1

A) Jawab: 1
Hanya seluruh bagian yang akan ditampilkan, karena operasi menggunakan bilangan bulat. Ini sering ditemukan dalam bahasa lain.

B) Jawab: permintaan tidak akan dieksekusi .

avg akan memberikan kesalahan sejak itu hanya menerima angka dan interval waktu *

Namun, fungsi min / maks dapat dieksekusi pada data teks (sesuai dengan pengurutan alfabetis dalam basis data).
Terkadang ini berguna ketika Anda setidaknya harus melihat kolom yang tidak tercantum dalam GROUP BY
Atau ketika Anda perlu menerapkan penyortiran alfabet ke angka, di mana '10' <'2'

B) Jawab: SALAH

Mungkin terlihat aneh, tetapi ini dapat diterima , karena ini adalah fitur komputer yang mewakili beberapa angka floating-point, suatu angka dapat berbentuk 7.1 (9)
Saya ingat bagaimana saya pernah menangani permintaan untuk waktu yang lama tanpa menyadarinya.

D) Jawab: 0 . tangkapannya adalah ekspresi dalam tanda kurung akan = 0

SELECT (20 / 25.0) * 25 akan bekerja lebih benar

1.2 Diberikan tabel " table_2 " (dengan satu kolom " nilai " (INTEGER)) yang terdiri dari 5 baris berikut:

nilai
5
5
Tidak
5
5

Hasil apa yang akan mengembalikan kueri:

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

Opsi jawaban

-4
0
Tidak
5
Ini akan menyebabkan kesalahan karena tidak ditentukan GROUP BY
Tidak ada yang terdaftar

Jawab 1.2

jawaban: 5

Fungsi agregat yang diterapkan pada kolom tertentu mengabaikan NULL , namun hitungan (*) akan menghitung semua baris
5 * 5 - 20

2. Masalah umum

2.1 Dalam kasus apa permintaan dapat mengembalikan tidak semua konten tabel? ( parent_id INTEGER, tabel diisi dengan berbagai data)

  SELECT * FROM any_table WHERE parent_id = parent_id;

Bagaimana perilaku permintaan di bawah ini? Data apa yang akan dikeluarkannya? * PostgreSQL

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

Jawaban untuk 2.1

Kueri pertama akan menampilkan semua entri kecuali yang parent_id adalah NULL

Kueri kedua akan menampilkan semua entri tabel. IS DISTINCT FROM secara logika mirip dengan operator ! = Di mana NULL identik dengan NULL
BUKAN MENDAPATKAN DARI logis mengubah ketidaksetaraan menjadi kesetaraan

2.2. Apa yang akan menjadi hasil dari permintaan?

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

Balas ke 2.2

Hasilnya akan menjadi 2 baris dengan nilai 1 dan 2 , UNION akan menghapus semua duplikat dalam pilihan yang dihasilkan , dan tidak hanya antara dua tabel yang digabungkan. Saya perhatikan bahwa ini tidak jelas bagi semua orang.

2.3 Tulis kueri yang menunjukkan tanggal besok.

Jawaban untuk 2.3

 SELECT CAST((now()+ INTERVAL '1 DAY') AS DATE)

Tidak semua orang sering bekerja dengan tanggal, tetapi ada baiknya menguasai beberapa minimum
* Solusi Postgres, tapi saya rasa DBMS lain tidak jauh berbeda

Jika bekerja dengan tanggal adalah hal baru bagi Anda, saya sarankan Anda untuk bereksperimen dengan permintaan tersebut
Sebagai contoh:
- ganti HARI dengan (minggu, bulan, tahun, dll.)
- ganti +1 dengan -9000
- ganti DATE dengan TIME
- hapus CAST
- hanya menyisakan SEKARANG ()
dll.

Dan, terinspirasi oleh beberapa hasil, bacalah MANUAL , semua topik di sana dijelaskan secara rinci

2.4 Pernyataan UPDATE , DELETE , INSERT, dan MERGE dirancang untuk memanipulasi data dalam tabel. Apakah SELECT .. eksekusi "aman"? Bisakah kueri memengaruhi data dalam tabel?

Jawaban untuk 2.4

Pertanyaannya mungkin tampak primitif, namun ...

Pada awal belajar SQL, saya berpendapat bahwa pernyataan ini hanya dapat menampilkan data, tetapi:

Selain fakta bahwa SELECT dapat mengunci tabel untuk perubahan (MULAI; PILIH ... UNTUK PEMBARUAN) *
SELECT dapat memanggil fungsi yang dapat melakukan hampir semua manipulasi.

Pemula perlu segera memahami hal ini, dan tidak setelah menyelesaikan permintaan "informasi kecil" di server ~~Produksi~~

3. Hanya PostgreSQL

3.1 Jelaskan apa yang terjadi ketika kueri ini dieksekusi dalam dialog SQL:

 SELECT * INTO wtf FROM pg_stat_activity;

Balas ke 3.1

Biasanya, SELECT INTO digunakan dalam fungsi plpgsql untuk menulis nilai ke variabel.

Di luar plpgsql, efek dari perintah akan mirip dengan permintaan di bawah ini:

 CREATE TABLE wtf AS SELECT * FROM pg_stat_activity;

3.2 apa yang akan ditampilkan permintaan "sederhana" ini

 SELECT wtf_ FROM pg_stat_activity AS wtf_ ;

Balas ke 3.2

pg_stat_activity system view (VIEW) dari proses aktif dalam database.

Keunikan kueri adalah bahwa satu kolom dengan baris (ROW) yang memiliki TYPE pg_stat_activity (atau tabel lain) akan ditampilkan. Anda harus mengetahui hal ini lebih cepat bagi mereka yang menulis fungsi. Anda dapat membaca lebih lanjut di manual.
Pertanyaan itu ditambahkan karena seorang pemula dapat dengan mudah mendapatkan hasil seperti itu secara tidak sengaja, dan tidak mengerti apa masalahnya

4. Bekerja dengan teks. Ekspresi reguler

Saya pikir Anda harus dapat tidak hanya membangun kueri, tetapi juga menyajikan hasilnya dengan cara yang benar.
Ekspresi reguler adalah topik besar yang terpisah, dengan banyak artikel berkualitas. Karena itu, saya hanya akan menunjukkan contoh, tanpa penjelasan rinci.

4.1. Misalkan ada tabel " table_5 " dengan kolom teks " X " dan banyak baris berbeda. Permintaan apa yang bisa mendapatkan 10 karakter terakhir dari setiap baris?

Balas ke 4.1

SQL memungkinkan Anda menemukan banyak solusi untuk masalah yang sama, misalnya:
hal paling sederhana yang terlintas dalam pikiran adalah benar (X, 10)
regex dapat digunakan: substring (X, '. {0,10} $')
Anda bahkan dapat ~~nakostylyat~~ "menghindar" (dalam semua pengertian) seperti ini: mundur (substring (mundur (X) selama 10))

4.2 Ada tabel “table_6” dengan kolom teks “X”. Tabel berisi satu baris (semua teks dalam bahasa Inggris dan Rusia saja):

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

A) Tulis kueri yang akan mengembalikan karakter ke-42 ke 68 dari string ini
B) Bagaimana cara mengekstrak hanya huruf kapital (Rusia atau Inggris) dalam sebuah string menggunakan SQL?
C) Cara menghitung jumlah angka ( bukan angka ) dalam suatu string menggunakan SQL

Sketsa SQL

 WITH table_6(X) AS( SELECT 'Lorem 3 Ipsum 23 standard 7 dummy 17 text Ultimate Answer of Life ?? 777'::TEXT ) SELECT X FROM table_6

Jawaban untuk 4.2

  --    WITH  "SQL " -- ) SELECT SUBSTRING(LEFT(X,68) FROM 42 ) FROM table_6 -- 1  SELECT SUBSTRING(X, 42, (68-42)+1) FROM table_6 -- 2  -- 3    -- )  ,        SELECT regexp_replace(X,'[^A-Z-]', '','g') FROM table_6 --  ''      - --   'g'    1  -- )        --   regexp_matches   **     ,      SELECT sum(x[1]::INT) FROM ( SELECT regexp_matches(X,'[0-9]+','g') FROM table_6 ) AS y(x) -- *        -- **   +,     (   1 ,     )

4.3 Bagaimana cara mengganti semua spasi ganda (tiga kali lipat atau lebih) dengan satu spasi dalam teks (sel tabel)? (berdasarkan tradisi: tabel " table_7 " dengan kolom " X ") (PS itu akan cukup untuk menulis SELECT mengembalikan hasil yang diinginkan, dan bukan UPDATE table_7 ... )

Balas ke 4.3

 WITH table_7(X) AS (SELECT 'Lorem 3 Ipsum 23 standard 7 dummy 11 text'::TEXT) -- 1 .    (2   ) SELECT regexp_replace(X, '( ){2,}', ' ', 'g') FROM table_7 -- 2 .     (,  ,    ..)   ,      SELECT regexp_replace(X, '\s+', ' ', 'g') FROM table_7 --  !  ,  -    ,    "" .    .. --   ,    , ,        --    ,      ,    SELECT replace(replace(replace(X, ' ', '<>'), '><', ''), '<>', ' ') FROM table_7

4.4 Ada string " X " di mana kesalahan ketik diizinkan. Alih-alih huruf Rusia (e, o, s, C), karakter luar yang mirip dari alfabet Inggris digunakan. Ganti karakter ini dengan SQL.

PS Baris harus hanya berisi karakter Rusia, dan Anda tidak perlu khawatir tentang kemungkinan perubahan kata-kata bahasa Inggris.

(Jika Anda mengalami kesulitan mengganti semua karakter, ganti setidaknya satu)

Baris contoh:

X = 'Coeo eoc oe'

Balas ke 4.4

 -- , Replace(Replace(Replace(..  ,  --        (1   1 ) SELECT TRANSLATE('Coeo  eoc oe', 'Cceo', '')

4.5 Tulis kueri yang mengonversi string:
' Ivan Ivanov dan Ivanovich' untuk spesies 'Ivan Ivanov

Balas ke 4.5

 --  ,     SELECT initcap('  ') *

Pencarian bonus untuk mereka yang mengatasinya

~~Hebat jika ada fungsi yang sudah jadi~~
Bisakah Anda mengonversi sebaliknya? (lebih disukai tanpa kehilangan bantalan).
Mungkin tugasnya tidak khas, tetapi akan berguna untuk pengembangan.

'IVANOV IVAN IVANOVICH' dikonversi menjadi 'IVANOV IVAN IVANOVICH'
~~dan membalikkan kasus?~~

Respon Tantangan Bonus

 SELECT string_agg(LOWER(LEFT(x,1)) || UPPER(SUBSTRING(x from 2)), '' ORDER BY rn) FROM (SELECT * FROM regexp_split_to_table('    4 TesT', '\y') WITH ORDINALITY y(x, rn) ) AS z -- *  PostgreSQL,      --    ,     --      ,      --      . -- WITH ORDINALITY      (   9.4) --      --          -- ..

5. Sedikit tentang transaksi

Transaksi adalah hal yang sangat penting dalam DBMS, penting untuk memahami poin utama.

Saya akan mencoba mensimulasikan contoh:

Misalkan ada tabel "barang" dengan dua pengguna yang akan bekerja.
Ini memiliki kolom diskon integer sama dengan 10 untuk semua baris.
Pengaturan basis data adalah standar (READ COMMITTED - read data berkomitmen).

Pengguna User_1 membuka transaksi, menjalankan permintaan berikut:

 BEGIN; UPDATE goods SET discount = discount + 5;

Sedetik kemudian, pengguna lain ( User_2 )
Ia melakukan permintaan yang hampir sama tanpa membuka transaksi:

 UPDATE goods SET discount = discount + 10;

Menurut Anda apa yang akan terjadi dalam situasi berikut:

A) Apa hasil yang akan diperoleh User_2 jika User_1 membiarkan transaksi terbuka (mis. Tidak mengkonfirmasi transaksi / tidak mengembalikan perubahan)?
Apa yang User_1 akan lihat berdasarkan permintaan:

 SELECT discount FROM goods LIMIT 1;

B) Apa yang terjadi jika User_1 melakukan ROLLBACK? Apa hasil yang akan diperoleh User_2?

T) Apa yang terjadi jika User_1 melakukan COMMIT? Apa hasil yang akan diperoleh User_2?

Jawabannya

Sejauh yang saya tahu, READ UN COMMITTED tidak didukung di PostgreSQL, dan data kotor (tidak dikonfirmasi) tidak dapat dibaca

Jawabannya adalah sebagai berikut:

A) Permintaan User_2 akan menunggu COMMIT atau ROLLBACK dari User_1. (permintaan tampaknya membeku)
User_1 dalam transaksinya akan melihat snapshot database versi miliknya, di mana diskon sudah sama dengan 15

B) Jika User_1 melakukan ROLLBACK, maka nilai diskon akan tetap sama, dan kemudian User_2 akan dieksekusi, yang akan menambahkan 10 ke diskon dan diskon akan menjadi 20

C) Jika User_1 melakukan KOMIT, maka nilai diskon akan meningkat sebesar 5, dan kemudian User_2 akan dieksekusi, yang akan menambahkan 10 ke diskon dan diskon akan menjadi 25

Versi lain dari tugas ini

Versi tugas 13 yang sedikit berbeda dari pengguna kirill_petrov pada READ COMMITTED

 --      CREATE TABLE goods (discount) AS (SELECT 10::INT UNION ALL SELECT 15); -- 1. User_1   (  ): BEGIN; UPDATE goods SET discount = discount + 5; --2. User_2  : UPDATE goods SET discount = discount + 100 WHERE discount = 15 --3. User_1  COMMIT;

Data apa yang akan ada dalam tabel?

Kesimpulan

Saya pikir itu menyentuh poin yang cukup menarik.

Saya berharap tugas-tugas ini akan membantu memotivasi pemula, karena membosankan mempelajari sesuatu tanpa tujuan / sasaran / arah tertentu.

Saya bisa senang untuk mereka yang mudah menjawab semua pertanyaan. Dan mereka yang memiliki kesulitan, saya harap, mendapat ~~tendangan ke~~ arah pembangunan. Mereka yang tidak mengerti banyak, tetapi ingin belajar SQL, saya mengundang kursus pejuang muda PostgreSQL ke artikel terakhir saya.

Saya menantikan penambahan, solusi untuk masalah yang sangat menarik (Anda dapat menambang) dan komentar lainnya!

Terima kasih atas perhatian anda! Semoga sukses dalam belajar SQL!