⚪️ 🚆 🥢 PostgreSQL फाइटर के लिए कार्य और समाधान 🌆 ™️ 📚

सभी एसक्यूएल प्रेमियों को बधाई!

इंटरनेट पर, मैंने उन लेखों को शायद ही देखा हो जो SQL में डेटा प्रोसेसिंग से जुड़े विभिन्न कार्य बिंदुओं और सूक्ष्मताओं को कवर करते हैं।
मुझे यह पसंद है जब आप एक बार में एक लेख से कई चीजें सीख सकते हैं, यहां तक कि सामान्य शब्दों में भी।
इसलिए, मैंने अपने लेख को विभिन्न कार्यों और उनके लिए स्पष्टीकरण के साथ उत्तर लिखने का फैसला किया।
उन लोगों के लिए उपयुक्त है जिन्होंने सभी बुनियादी कौशल में अच्छी तरह से महारत हासिल की है और आगे विकास करना चाहते हैं।

प्रदान किए गए उत्तर PostgreSQL के लिए उपयुक्त हैं ( अधिकांश कार्य अन्य DBMS के लिए उपयुक्त होंगे , लेकिन परिणाम और समाधान भिन्न हो सकते हैं। यह और भी दिलचस्प है जहां मतभेद उत्पन्न होते हैं।

स्पॉइलर खोलने से पहले खुद को जवाब देने की कोशिश करें ।

चलो चलते हैं!

मैं पूरी तरह से PostgreSQL * के लिए एक तारांकन चिह्न के साथ चिह्नित करने की कोशिश करूँगा (ऐसे कई क्षण नहीं हैं)

1. संख्यात्मक कार्यों के बारे में थोड़ा

१.१ क्या ये अनुरोध पूरे होंगे? वे क्या परिणाम देंगे?

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

१.१ का उत्तर

ए) उत्तर: १
केवल पूरे भाग को दिखाया जाएगा, क्योंकि ऑपरेशन पूर्णांक का उपयोग करता है। यह अक्सर अन्य भाषाओं में पाया जाता है।

बी) उत्तर: अनुरोध निष्पादित नहीं किया जाएगा ।

avg तब से एक एरर देगा केवल संख्या और समय अंतराल को स्वीकार करता है *

हालाँकि, न्यूनतम / अधिकतम फ़ंक्शन को पाठ डेटा पर (डेटाबेस में वर्णानुक्रमिक छंटाई के अनुसार) निष्पादित किया जा सकता है।
कभी-कभी यह तब उपयोगी हो सकता है जब आपको कम से कम एक ऐसे कॉलम को देखने की आवश्यकता हो जो ग्रुप बीवाई में सूचीबद्ध नहीं है
या जब आपको संख्याओं के लिए वर्णमाला क्रमबद्ध करने की आवश्यकता होती है, जिसमें '10' <'2'

बी) उत्तर: FALSE

यह अजीब लग सकता है, लेकिन यह स्वीकार्य है , क्योंकि यह कुछ फ्लोटिंग-पॉइंट संख्याओं का प्रतिनिधित्व करने वाले कंप्यूटर की एक विशेषता है; एक नंबर 7.1 (9) फॉर्म ले सकता है
मुझे याद है कि कैसे मैं एक बार एक लंबे समय के लिए अनुरोध के साथ यह जानने के बिना निपटा।

D) उत्तर: 0 पकड़ यह है कि कोष्ठक में अभिव्यक्ति = 0 होगी

चयन (20 / 25.0) * 25 अधिक सही ढंग से काम करेगा

1.2 निम्नलिखित 5 पंक्तियों से मिलकर तालिका " table_2 " (एकल स्तंभ " मान " (INTEGER) के साथ) दी गई है:

मूल्य
5
5
शून्य
5
5

क्या परिणाम क्वेरी लौटाएगा:

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

उत्तर के विकल्प

-4
0
शून्य
5
यह एक त्रुटि का कारण होगा निर्दिष्ट नहीं है BY द्वारा
कोई भी सूचीबद्ध नहीं है

उत्तर 1.2

उत्तर: 5

किसी विशेष कॉलम के लिए लागू किए गए कार्य NULL को अनदेखा करते हैं, हालांकि सभी पंक्तियों को गिनेंगे (*)
५ * ५ - २०

2. सामान्य मुद्दे

2.1 किन मामलों में क्वेरी एक तालिका की सभी सामग्री नहीं लौटा सकती है? ( parent_id INTEGER, तालिका विभिन्न डेटा से भरी हुई है)

  SELECT * FROM any_table WHERE parent_id = parent_id;

नीचे का अनुरोध कैसे व्यवहार करेगा? क्या डेटा यह उत्पादन करेगा? * PostgreSQL

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

२.१ का उत्तर

पहली क्वेरी उन सभी प्रविष्टियों को दिखाएगी, जहां पैरेंट_ड NULL है

दूसरी क्वेरी सभी तालिका प्रविष्टियाँ दिखाएगी। IS DISTINCT FROM तार्किक रूप से ऑपरेटर के समान है ! = जिसमें NULL, NULL के समान है
तार्किक रूप से कोई समानता नहीं है कि तार्किकता असमानता को समानता में बदल देती है

2.2। अनुरोध का परिणाम क्या होगा?

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

उत्तर २.२

परिणाम 1 और 2 मानों के साथ 2 पंक्तियाँ होंगी, परिणामस्वरूप चयन में UNION सभी डुप्लिकेट को हटा देगा, और न केवल दोनों में शामिल तालिकाओं के बीच। मैंने देखा कि यह सभी के लिए स्पष्ट नहीं है।

2.3 एक क्वेरी लिखें जो कल की तारीख को दर्शाता है।

2.3 का उत्तर

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

हर कोई अक्सर तारीखों के साथ काम नहीं करता है, लेकिन यह कुछ न्यूनतम करने में माहिर है
* समाधान को स्थगित करता है, लेकिन मुझे लगता है कि अन्य DBMS ज्यादा अलग नहीं हैं

यदि तिथियों के साथ काम करना आपके लिए नया है, तो मैं आपको अनुरोध के साथ प्रयोग करने की सलाह देता हूं
उदाहरण के लिए:
- दिन (सप्ताह, महीने, वर्ष, आदि के साथ बदलें)
- +1 को -9000 से बदलें
- DATE को TIME से बदलें
- कास्ट निकालें
- अब केवल छोड़ें ()
आदि

और, कुछ परिणामों से प्रेरित होकर, MANUAL पढ़ें, वहां सभी विषयों का विस्तार से वर्णन किया गया है

2.4 तालिका में डेटा में हेरफेर करने के लिए अद्यतन , DELETE , INSERT, और MERGE विवरण तैयार किए गए हैं । क्या चयन .. निष्पादन "सुरक्षित" है? क्या कोई क्वेरी तालिका में डेटा को प्रभावित कर सकती है?

2.4 का उत्तर

प्रश्न आदिम लग सकता है, हालांकि ...

एसक्यूएल सीखने की बहुत शुरुआत में, मेरी राय थी कि यह कथन केवल डेटा दिखा सकता है, लेकिन:

इस तथ्य के अलावा कि चयन परिवर्तन के लिए तालिका को लॉक करने में सक्षम है (BEGIN; SELECT ... FOR UPDATE) *
चयन उन कार्यों को कॉल करने में सक्षम है जो लगभग किसी भी हेरफेर कर सकते हैं।

शुरुआती को इसे समझने की आवश्यकता है, न कि ~~उत्पादन~~ सर्वर पर "छोटी सूचना" अनुरोध को पूरा करने के बाद

3. केवल PostgreSQL

3.1 बताइए कि SQL क्वेरी में इस क्वेरी को निष्पादित करने पर क्या होता है:

 SELECT * INTO wtf FROM pg_stat_activity;

उत्तर 3.1

सामान्यतया, SELECT INTO का उपयोग किसी चर का मान लिखने के लिए plpgsql फ़ंक्शन में किया जाता है।

Plpgsql के बाहर, कमांड का प्रभाव नीचे दिए गए क्वेरी के समान होगा:

 CREATE TABLE wtf AS SELECT * FROM pg_stat_activity;

3.2 यह "सरल" अनुरोध क्या दिखाएगा

 SELECT wtf_ FROM pg_stat_activity AS wtf_ ;

३.२ का जवाब दें

डेटाबेस में सक्रिय प्रक्रियाओं के pg_stat_activity सिस्टम दृश्य (देखें)।

क्वेरी की ख़ासियत यह है कि TYPE pg_stat_activity (या किसी अन्य तालिका) वाली पंक्तियों (ROW) के साथ एक कॉलम प्रदर्शित किया जाएगा। आपको उन लोगों के लिए जल्द ही यह जानना होगा जो फ़ंक्शन लिखते हैं। आप मैनुअल में अधिक पढ़ सकते हैं ।
सवाल इसलिए जोड़ा गया क्योंकि एक शुरुआत करने वाला गलती से ऐसा परिणाम प्राप्त कर सकता है, और यह नहीं समझ सकता कि क्या मामला है

4. पाठ के साथ काम करें। नियमित अभिव्यक्ति

मुझे लगता है कि आपको न केवल प्रश्नों का निर्माण करने में सक्षम होना चाहिए, बल्कि परिणामों को भी सही तरीके से प्रस्तुत करना होगा।
नियमित अभिव्यक्ति एक अलग विशाल विषय है, जिसमें कई गुणवत्ता वाले लेख हैं। इसलिए, मैं केवल विस्तृत स्पष्टीकरण के बिना, उदाहरण दिखाऊंगा।

4.1। मान लीजिए कि एक पाठ स्तंभ " X " और कई अलग-अलग पंक्तियों के साथ एक तालिका " table_5 " है। प्रत्येक पंक्ति के किसी भी अंतिम 10 वर्णों को क्या क्वेरी मिल सकती है?

उत्तर 4.1

SQL आपको एक ही समस्या के कई समाधानों के साथ आने की अनुमति देता है, उदाहरण के लिए:
सबसे सरल बात जो मन में आती है वह सही है (एक्स, 10)
रेगेक्स का इस्तेमाल किया जा सकता है: सबस्ट्रिंग (एक्स, '। {0,10} $')
आप इस तरह से "डॉज" (सभी इंद्रियों में) ~~nakostylyat~~ कर सकते हैं: रिवर्स (प्रतिस्थापन (10 के लिए रिवर्स) (एक्स))

4.2 एक पाठ स्तंभ "X" के साथ एक तालिका "table_6" है। तालिका में एक पंक्ति (केवल अंग्रेजी और रूसी में सभी पाठ) हैं:

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

ए) एक क्वेरी लिखें जो इस स्ट्रिंग से 42 वें से 68 वें वर्णों को लौटाएगा
B) SQL का उपयोग करके स्ट्रिंग में केवल CAPITAL (रूसी या अंग्रेजी) अक्षर कैसे निकालें?
सी) एसक्यूएल का उपयोग करके स्ट्रिंग में संख्याओं की संख्या ( अंकों का नहीं ) की गणना कैसे करें

एसक्यूएल स्केच

 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

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। पाठ (तालिका सेल) में एकल स्थान के साथ सभी डबल (ट्रिपल या अधिक) रिक्त स्थान कैसे बदलें? (परंपरा से: तालिका " X " के साथ तालिका " तालिका_7 ") (PS यह वांछित परिणाम लौटाने के लिए लिखने के लिए पर्याप्त होगा, अद्यतन तालिका_7 नहीं ... )

उत्तर 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 एक स्ट्रिंग " X " है जिसमें टाइपोस की अनुमति है। रूसी अक्षरों (ई, ओ, एस, सी) के बजाय, बाह्य रूप से अंग्रेजी वर्णमाला के समान वर्णों का उपयोग किया गया था। इन वर्णों को SQL से बदलें।

पुनश्च लाइन में केवल रूसी अक्षर शामिल होने चाहिए, और आपको अंग्रेजी शब्दों में संभावित बदलाव के बारे में चिंता नहीं करनी चाहिए।

(यदि आपको सभी पात्रों को बदलने में कठिनाई हो रही है, तो कम से कम एक को बदलें)

उदाहरण पंक्ति:

X = 'Coeo eoc oe'

उत्तर 4.4

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

4.5 एक स्ट्रिंग लिखने वाली क्वेरी लिखें:
' इवान इवानोव और इवानोविच' प्रजातियों के लिए 'इवान इवानोव

उत्तर ४.५ तक

 --  ,     SELECT initcap('  ') *

जो लोग नकल करते थे उनके लिए बोनस खोज

~~यदि कोई रेडीमेड फंक्शन है तो बढ़िया~~
क्या आप दूसरे तरीके को बदल सकते हैं? (अधिमानतः पैडिंग खोए बिना)।
शायद कार्य विशिष्ट नहीं है, लेकिन यह विकास के लिए उपयोगी होगा।

'इवानोव इवान इवानोविक' 'इवानोव इवान इवानोविक' में परिवर्तित
~~और उलटा मामला?~~

बोनस चैलेंज रिस्पांस

 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. लेन-देन के बारे में थोड़ा सा

डीबीएमएस में लेन-देन एक बहुत महत्वपूर्ण चीज है, मुख्य बिंदुओं को समझना महत्वपूर्ण है।

मैं एक उदाहरण अनुकरण करने की कोशिश करूंगा:

मान लीजिए कि एक टेबल "माल" है, जिसके साथ दो उपयोगकर्ता काम करने जा रहे हैं।
इसमें सभी पंक्तियों के लिए 10 के बराबर एक पूर्णांक छूट स्तंभ है।
डेटाबेस सेटिंग्स मानक हैं (पढ़ें प्रतिबद्ध - प्रतिबद्ध डेटा पढ़ने)।

उपयोगकर्ता User_1 एक लेनदेन खोलता है, निम्नलिखित अनुरोध को निष्पादित करता है:

 BEGIN; UPDATE goods SET discount = discount + 5;

एक दूसरे बाद में, एक अन्य उपयोगकर्ता ( User_2 )
यह लेन-देन खोले बिना लगभग समान अनुरोध करता है:

 UPDATE goods SET discount = discount + 10;

आपको क्या लगता है कि निम्नलिखित स्थितियों में क्या होगा:

A) User_1 अगर लेन-देन खुला छोड़ता है (तो उपयोगकर्ता लेन-देन की पुष्टि नहीं करता है / क्या परिवर्तन वापस नहीं लेता है)
User_1 अनुरोध पर क्या देखेगा:

 SELECT discount FROM goods LIMIT 1;

B) यदि उपयोगकर्ता_1 ROLLBACK करता है तो क्या होगा? User_2 को क्या परिणाम मिलेंगे?

Q) अगर User_1 COMMIT करता है तो क्या होगा? User_2 को क्या परिणाम मिलेंगे?

उत्तर

जहाँ तक मुझे पता है, पोस्ट यूएनआईटी में पोस्टेड यूएनआईटी कमिटेड समर्थित नहीं है, और गंदे डेटा (पुष्टि नहीं) पढ़ा नहीं जा सकता

उत्तर निम्नानुसार होंगे:

A) अनुरोध User_2 COMM_1 या ROLLBACK के लिए User_1 से प्रतीक्षा करेगा। (अनुरोध जमने लगता है)
उसके लेनदेन में User_1 डेटाबेस स्नैपशॉट के अपने संस्करण को देखेगा, जहां छूट पहले से ही 15 के बराबर है

B) यदि User_1 ROLLBACK करता है, तो छूट मान समान रहेगा, और तब User_2 निष्पादित किया जाएगा, जो छूट में 10 जोड़ देगा और छूट 20 हो जाएगी

C) यदि User_1 COMMIT करता है, तो छूट मान 5 से बढ़ जाएगा, और तब User_2 निष्पादित किया जाएगा, जो छूट में 10 जोड़ देगा और छूट 25 हो जाएगी

इस कार्य का दूसरा संस्करण

READ COMMITTED पर उपयोगकर्ता kirill_petrov से कार्य 13 का थोड़ा अलग संस्करण

 --      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;

तालिका में कौन सा डेटा होगा?

निष्कर्ष

मुझे लगता है कि काफी दिलचस्प बिंदुओं को छुआ है।

मुझे आशा है कि कार्य शुरुआती लोगों को प्रेरित करने में मदद करेंगे, क्योंकि यह विशिष्ट लक्ष्यों / उद्देश्यों / दिशाओं के बिना कुछ सीखने के लिए उबाऊ है।

मुझे उन लोगों के लिए खुशी हो सकती है जो सभी सवालों के जवाब देने में आसान थे। और जिन लोगों को मुश्किलें थीं, मुझे आशा है कि विकास की दिशा ~~में~~ एक ~~किक~~ मिली। जो लोग बहुत कुछ नहीं समझते हैं, लेकिन एसक्यूएल सीखना चाहते हैं, मैं अपने आखिरी लेख में पोस्टग्रेजक्यूएल युवा लड़ाकू पाठ्यक्रम को आमंत्रित करता हूं।

मैं किसी भी परिवर्धन, विशेष रूप से दिलचस्प समस्याओं के समाधान (आप मेरा हो सकता है) और अन्य टिप्पणियों के लिए तत्पर हैं!

आपका ध्यान के लिए धन्यवाद! मैं आपको एसक्यूएल सीखने में सफलता की कामना करता हूं!