SQL: रिपोर्टिंग और विश्लेषण

इस ट्यूटोरियल में, आप ANSI (American National Standards Institute) SQL पर ध्यान देंगे, जो Oracle, MySQL, Microsoft SQL Server आदि जैसे हर डेटाबेस पर काम करता है! चलिए SQL का परिचय (Structured Query Language) और एक डेटा वैज्ञानिक को इसकी आवश्यकता क्यों पड़ती है, से शुरू करते हैं।

SQL और डेटा साइंस

ट्यूटोरियल के इस भाग में, आप देखेंगे कि एक डेटा वैज्ञानिक को SQL क्यों सीखना चाहिए। आइए देखें कि SQL आपके डेटा वैज्ञानिक के करियर में कैसे मदद करेगा:

डेटा विश्लेषक, BI (बिज़नेस इंटेलिजेंस) डेवलपर, प्रोग्रामर, डेटाबेस प्रोग्रामर जैसी अधिकांश डेटा साइंस नौकरियों के लिए SQL एक आवश्यकता बन गया है। SQL आपको डेटाबेस के साथ संवाद करने और अपने डेटा पर काम करने की सुविधा देता है।
यदि आपने Tableau या किसी अन्य डेटा रिपोर्टिंग या विज़ुअलाइज़ेशन सॉफ़्टवेयर का उपयोग किया है, तो आपने देखा होगा कि आप अपने प्रोजेक्ट को एक डेटाबेस से कैसे जोड़ते हैं, और केवल फ़ील्ड्स बताकर ग्राफ़ और प्लॉट्स को रिपोर्ट पर ड्रैग-एंड-ड्रॉप करते हैं, और सॉफ़्टवेयर आपके लिए बाकी सब कर देता है। जब आप डेटा एट्रिब्यूट्स का ड्रैग-एंड-ड्रॉप करते हैं या ग्राफिकल यूज़र इंटरफ़ेस से एक क्लिक में ऑपरेशंस करते हैं, तो सॉफ़्टवेयर के पीछे डेटाबेस से इंटरैक्ट करने के लिए SQL चल रहा होता है। SQL सीखकर, आप सीधे डेटाबेस के साथ इंटरैक्ट कर सकते हैं।
SQL को PHP, Java जैसी सभी एप्लिकेशन प्रोग्रामिंग भाषाओं के साथ उपयोग किया जा सकता है। आप एप्लिकेशन में SQL को एकीकृत करके अपनी खुद की डेटा विज़ुअलाइज़ेशन बना सकते हैं या डेटाबेस से डेटा लेकर उन्हें वेब सर्विसेज़ या APIs में उपयोग के लिए XML, JSON फ़ॉर्मैट में बदल सकते हैं।
वर्षों में डेटाबेस विकसित हुए हैं; जैसे-जैसे बिग डेटा चर्चा का विषय बना है और डेटा हमारे दैनिक जीवन का हिस्सा बना है, NoSQL डेटाबेस लोकप्रिय हो रहे हैं। SQL सीखने से आपको डेटाबेस में मजबूत नींव बनाने में मदद मिलेगी और यह समझने में सहायता मिलेगी कि कब स्ट्रक्चर्ड डेटाबेस का उपयोग करना है, कब NoSQL डेटाबेस का, और उनके अंतर की सराहना कैसे करनी है।

यहां मैं SQL और डेटाबेस में प्रयुक्त कुछ परिचयात्मक अवधारणाओं और शब्दावलियों से गुजरूंगा जो आपको तेज़ी से कोड करना सीखने में मदद करेंगे! यदि आप पहले से जानते हैं कि रिलेशनल डेटाबेस क्या है और SQL क्या है, तो आप कोड पर जा सकते हैं।

SQL और डेटाबेस का परिचय

डेटाबेस सूचना का एक व्यवस्थित संग्रह है। डेटाबेस को प्रबंधित करने के लिए, हमें डेटाबेस मैनेजमेंट सिस्टम (DBMS) की आवश्यकता होती है। DBMS एक प्रोग्राम है जो अनुरोध पर डेटाबेस में डेटा को संग्रहीत, पुनर्प्राप्त और संशोधित करता है।

समय के साथ अलग-अलग प्रकार के डेटाबेस रहे हैं: पदानुक्रमित (Hierarchical), नेटवर्क, रिलेशनल डेटाबेस और अब NoSQL डेटाबेस। रिलेशनल डेटाबेस रिलेशन्स या द्वि-आयामी तालिकाओं (two-dimensional tables) का संग्रह है।

RDBMS में प्रयुक्त शब्दावली निम्नलिखित है:

शब्द	विवरण
टेबल	टेबल RDBMS की मूल भंडारण संरचना है। एक टेबल वास्तविक दुनिया की किसी वस्तु से संबंधित आवश्यक सभी डेटा को संग्रहीत करती है। उदाहरण: Employees.
एकल पंक्ति या टपल	किसी विशेष कर्मचारी के लिए आवश्यक सभी डेटा का प्रतिनिधित्व। टेबल की प्रत्येक पंक्ति को एक प्राइमरी की द्वारा पहचाना जा सकता है, जो डुप्लीकेट पंक्तियों की अनुमति नहीं देता।
कॉलम या एट्रिब्यूट	आमतौर पर किसी एंटिटी की विशेषता को संदर्भित करता है
प्राइमरी की	एक फ़ील्ड जो किसी पंक्ति की विशिष्ट पहचान करती है
फॉरेन की	फॉरेन की वह कॉलम है जो बताता है कि टेबल्स एक-दूसरे से कैसे संबंधित हैं। फॉरेन की किसी अन्य टेबल की प्राइमरी की को संदर्भित करती है।

आप प्राइमरी और फॉरेन की का उपयोग करके एकाधिक टेबल्स को आपस में जोड़ सकते हैं। रिलेशनल डेटाबेस की प्रत्येक पंक्ति को प्राइमरी की (PK) द्वारा अद्वितीय रूप से पहचाना जाता है। आप फॉरेन की (FK) का उपयोग करके किसी अन्य टेबल को संदर्भित कर सकते हैं। उदाहरण के लिए:

रिलेशनल डेटाबेस को Structured Query Language या SQL का उपयोग करके एक्सेस किया जा सकता है। हर डेटाबेस ANSI SQL, यानी मानक SQL, को सपोर्ट करेगा, साथ ही कुछ ऑपरेशंस को सरल बनाने के लिए अपना सिंटैक्स भी होगा। इस ट्यूटोरियल में, आप ANSI SQL सीखेंगे ताकि आप सभी डेटाबेस के साथ काम कर सकें। ANSI SQL को पाँच भागों में बांटा जा सकता है। मैं सभी के नाम बताऊंगा, पर यहां केवल दो प्रासंगिक भाग हैं—डेटा रिट्रीवल और DML:

डेटा रिट्रीवल:
- Select.
डेटा मैनिप्युलेशन लैंग्वेज (DML):
- Insert, Update, Delete, Merge
डेटा डेफ़िनिशन लैंग्वेज (DDL):
- Create, Alter, Drop, Rename, Truncate.
डेटा कंट्रोल लैंग्वेज (DCL):
- Grant, Revoke.
ट्रांज़ेक्शन कंट्रोल:
- Commit, Rollback, Savepoint.

RDBMS के लिए अलग-अलग विक्रेता उपलब्ध हैं। सबसे आम और प्रचलित हैं:

Oracle (Oracle Corporation)
Microsoft SQL Server (Microsoft)
MySQL (Oracle Corporation)
PostgreSQL (PostgreSQL Global Development Group)
SQLite (D. Richard Hipp द्वारा विकसित)

तो आप SQL क्वेरीज़ कहाँ चलाते हैं? यह हो सकता है:

ऐसा रिपोर्टिंग सॉफ़्टवेयर जो डेटाबेस से पढ़ने और डेटा देखने देता है (उदाहरण: Tableau, Microsoft BI)
डेटाबेस प्रशासन के लिए GUI (उदाहरण: Oracle के लिए TOAD, SQL Developer; MySQL के लिए phpMyAdmin)
डेटाबेस के लिए कंसोल-आधारित डायरेक्ट इंटरफ़ेस (उदाहरण: Oracle डेटाबेस के लिए SQL Plus)

यदि आपके पास किसी डेटाबेस में लॉगिन करने की साख है, तो आप क्वेरीज़ या अपने डेटाबेस विक्रेता के अनुसार GUI का उपयोग करके डेटाबेस ऑब्जेक्ट्स देख सकते हैं।

बस इतना ही! अब SQL सीखना शुरू करते हैं...

SQL डेटा प्रकार

डेटाबेस में प्रत्येक कॉलम का एक नाम, एक डेटा प्रकार और कभी-कभी उससे जुड़ा एक आकार होता है। आवश्यकताओं और डेटा की मात्रा के अनुसार कौन-सा डेटा प्रकार उपयोग करना चाहिए, यह डेटाबेस डेवलपर का कार्य है।

एक डेटा वैज्ञानिक के रूप में, आपको डेटा प्रकारों से परिचित होना चाहिए क्योंकि इससे आपको डेटाबेस फ़ंक्शंस का सही उपयोग करने और सटीक क्वेरी लिखने में मदद मिलेगी। डेटाबेस में प्रत्येक प्रकार के कॉलम के लिए एक डेटा प्रकार होता है, जैसे किसी व्यक्ति का नाम, संग्रहीत टेक्स्ट, संख्याएँ, डेटाबेस में संग्रहीत कोई इमेज आदि।

यहां Oracle Server, SQL Server और MySQL Server के बुनियादी डेटा प्रकार दिखाए जाएंगे:

आगे पढ़ने के लिए:

SQL और डेटा रिपोर्टिंग

इस ट्यूटोरियल में प्रयुक्त सभी SQL के लिए, निम्न नमूना डेटाबेस स्कीमा मानें:

मान लें कि एक डेटाबेस में दो टेबल हैं—emp जो कर्मचारी डेटा रखता है और dept जो विभागों के अभिलेख रखता है।

emp टेबल में कर्मचारी संख्या (empno), कर्मचारी नाम (ename), वेतन (sal), कमीशन (comm), नौकरी का नाम (job), प्रबंधक आईडी (mgr), नियुक्ति तिथि (hiredate) और विभाग संख्या (deptno) हैं। चूंकि प्रबंधक भी कर्मचारी होता है और उसकी भी एक कर्मचारी संख्या होगी, इसलिए mgr उन empno में से एक है जिसकी नौकरी "MANAGER" है।

dept टेबल में विभाग संख्या (deptno), विभाग का नाम (dname) और विभाग का स्थान (loc) है।

ध्यान दें कि अलग-अलग डेटाबेस में दिनांक का फ़ॉर्मैट अलग होता है। यहां DD-MON-YY Oracle डेटाबेस का डिफ़ॉल्ट दिनांक फ़ॉर्मैट है। Microsoft SQL Server और MySQL का डिफ़ॉल्ट फ़ॉर्मैट YYYY-MM-DD होता है।

आपके डेटाबेस टेबल अलग हो सकते हैं, इसलिए आपको केवल अपने टेबल नाम और एट्रिब्यूट्स के नाम के अनुसार समायोजन करना होगा। इस ट्यूटोरियल में, आप डेटाबेस से केवल पढ़ेंगे—लिखना, अपडेट करना या नई टेबल/ऑब्जेक्ट बनाना नहीं करेंगे। इसलिए डेटा लॉस या बदलाव की कोई चिंता नहीं!

सूचना: स्कीमा टेबल्स, फ़ंक्शंस, प्रोसीजर, व्यूज़ जैसे ऑब्जेक्ट्स का संग्रह है जो किसी उपयोगकर्ता के होते हैं

ऊपर दिए गए डेटाबेस स्कीमा में, आप देख सकते हैं कि emp टेबल में Employee एंटिटी के लिए छह एट्रिब्यूट्स हैं। dept टेबल में Department एंटिटी के लिए तीन एट्रिब्यूट्स हैं।

SELECT स्टेटमेंट का उपयोग करके डेटा प्राप्त करना

SELECT स्टेटमेंट डेटाबेस से जानकारी प्राप्त करता है। SELECT स्टेटमेंट का उपयोग करके आप निम्न कार्य कर सकते हैं:

1. प्रोजेक्शन: आप SQL में प्रोजेक्शन का उपयोग करके टेबल के वे कॉलम चुन सकते हैं जिन्हें आप अपनी क्वेरी द्वारा लौटाना चाहते हैं। आप आवश्यकता अनुसार टेबल के कम या अधिक कॉलम चुन सकते हैं।

2. सेलेक्शन: आप SQL में सेलेक्शन का उपयोग करके टेबल की वे पंक्तियाँ चुन सकते हैं जिन्हें आप क्वेरी द्वारा वापस पाना चाहते हैं।

आप जिन पंक्तियों को देखना चाहते हैं उन्हें सीमित करने के लिए विभिन्न मानदंडों का उपयोग कर सकते हैं।

3. जॉइनिंग: आप SQL में जॉइन का उपयोग करके अलग-अलग टेबल्स में संग्रहीत डेटा को उनके बीच एक लिंक बनाकर साथ ला सकते हैं।

बेसिक SELECT स्टेटमेंट आपको यह निर्दिष्ट करने देता है कि किस टेबल से कौन-से कॉलम चाहिए। SELECT क्लॉज़ कॉलम/एक्सप्रेशन बताता है और FROM क्लॉज़ बताता है कि किस टेबल से डेटा लेना है।

उदाहरण के लिए, सभी कर्मचारियों के नाम और उनकी नौकरियाँ क्या हैं: SELECT ename, job FROM employee; ऊपर की SQL क्वेरी, ऊपर चर्चा किए गए डेटाबेस स्कीमा के आधार पर, आपको निम्न आउटपुट देगी:

ename	job
A	Salesman
B	Manager
C	Manager

सभी एट्रिब्यूट्स और सभी पंक्तियाँ चुनने के लिए * ऑपरेटर का उपयोग किया जाता है:

SELECT *
FROM employee;
The output will be:

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

SQL स्टेटमेंट लिखने के कुछ सुझाव:

SQL स्टेटमेंट केस-सेंसिटिव नहीं होते

रीडेबिलिटी बढ़ाने के लिए क्लॉज़ अलग लाइनों पर लिखें

आप SQL स्टेटमेंट एक या एक से अधिक लाइनों में लिख सकते हैं

आप डेट और नंबर डेटा पर +, -, /, * ऑपरेटर्स का उपयोग करके एक्सप्रेशन भी बना सकते हैं ताकि आवश्यक डेटा जेनरेट हो। उदाहरण के लिए, आपसे सभी कर्मचारियों के वेतन का 20% निकालने को कहा गया है। क्वेरी इस प्रकार होगी:

SELECT ename, sal*(20/100)
FROM employee;
Output:

    ENAME      SAL*(20/100)
   ---------- ------------
    SMITH               160
    ALLEN               320
    WARD                250
    JONES               595
    BLAKE               570

सूचना:

एक्सप्रेशन स्पष्ट करने के लिए कोष्ठक (parentheses) का उपयोग किया जाता है

भाग और गुणा को जोड़ और घटाव पर प्राथमिकता मिलती है

यदि समान प्राथमिकता हो तो एक्सप्रेशन बाएँ से दाएँ मूल्यांकन होता है

डेटाबेस में NULL मानों को अलग तरह से ट्रीट किया जाता है। NULL मान का अर्थ है कि मान अज्ञात है। NULL के साथ किया गया कोई भी ऑपरेशन NULL देता है। अलग-अलग डेटाबेस में NULL को संभालने के लिए अलग फ़ंक्शंस होते हैं। एक सामान्य फ़ंक्शन COALESCE है, जो MySQL, Microsoft SQL Server और Oracle में प्रयुक्त होता है।

SELECT ename, sal+COALESCE(comm,0)
FROM employee;
Output:

    ENAME      SAL+COALESCE(COMM,0)
---------- --------------------
    SMITH                       800
    ALLEN                      1900
    WARD                       1750
    JONES                      2975
    BLAKE                      2850

कॉलम उपनाम (Aliases) और कंकैटनेशन

आप ऊपर के आउटपुट्स में देख सकते हैं कि कॉलम का नाम वही है जो डेटाबेस फ़ील्ड या चुने गए एक्सप्रेशन का है। कभी-कभी, रिपोर्ट बनाते समय आप हेडर्स को अपना नाम देना चाहते हैं। यह aliases का उपयोग करके किया जा सकता है।

SELECT ename AS "Emp Name", sal*(20/100) as "20% of Salary"
FROM employee;
Output:

    Emp Name      20% of Salary
   ---------- ------------
    SMITH               160
    ALLEN               320
    WARD                250
    JONES               595
    BLAKE               570

जब आप ऐसा उपनाम देते हैं जिसमें स्पेस हैं, तो आपको डबल कोट्स का उपयोग करना होगा। अन्यथा AS के बाद डबल कोट्स की आवश्यकता नहीं होती। कभी-कभी आप कीवर्ड AS को छोड़ भी सकते हैं।

आप कंकैटनेशन से अपना आउटपुट फ़ॉर्मैट कर सकते हैं। आप CONCAT फ़ंक्शन या विक्रेता-विशिष्ट ऑपरेटर्स जैसे || या + का उपयोग करके आउटपुट में अपने कथन जोड़ सकते हैं:

Oracle CONCAT() और || सपोर्ट करता है, लेकिन CONCAT() केवल दो आर्ग्युमेंट लेता है। आपको नेस्टेड CONCAT() का उपयोग करना होगा।
MySQL CONCAT() उपयोग करता है
Microsoft SQL Server '+' ऑपरेटर और CONCAT() का उपयोग करता है।

Oracle: SELECT '20% of salary of '||ename||' is '||sal*(20/100) as "20% of salary" FROM employee;

SELECT CONCAT(CONCAT('20% of salary of',ename),CONCAT(' is ',sal(20/100))) as "20% of salary" FROM employee; MySQL और Microsoft SQL Server: SELECT CONCAT('20% of salary of ',ename,' is ',sal(20/100)) as "20% of salary" FROM employee; सभी का आउटपुट समान होगा:

    20% of salary
-----------------------------------------------------------------------
    20% of salary of SMITH is 160
    20% of salary of ALLEN is 320
    20% of salary of WARD is 250
    20% of salary of JONES is 595
    20% of salary of BLAKE is 570

DISTINCT का उपयोग करके दोहराई पंक्तियाँ हटाना

SELECT deptno
FROM employee;
Above query will result in:


    DEPTNO
----------
        20
        30
        30
        20
        30

यहां deptno 20 और 30 दोहर रहे हैं। आप SELECT क्लॉज़ में DISTINCT कीवर्ड का उपयोग करके ऐसी पंक्तियाँ हटा सकते हैं।

SELECT distinct ename, deptno, job
FROM employee;



    DEPTNO
----------
        30
        20

डेटा को सीमित करना और सॉर्ट करना

WHERE क्लॉज़ का उपयोग किसी शर्त के आधार पर डेटा फ़िल्टर करने के लिए होता है।

वे सभी कर्मचारी खोजें जिनकी नौकरी CLERK है:

SELECT ename, job
FROM employee
WHERE job='CLERK';
    ENAME      JOB
---------- ---------
    SMITH      CLERK

आप अलग-अलग शर्तों के साथ अपने परिणामों को फ़िल्टर करना चाह सकते हैं। इसके लिए ऑपरेटर्स, कंडीशनल सिंबल और विशेष कीवर्ड का उपयोग किया जाता है:

=, <>, !=, >=, <=, >, < का उपयोग सीधे होता है, जैसा कि ऊपर की क्वेरी में = का उपयोग दिखा है।

AND और OR सिंटैक्स: SELECT column1, column2,.. FROM table_name WHERE condition1 AND condition2 AND condition 3...;

SELECT column1, column2,.. FROM table_name WHERE condition1 OR condition2 OR condition 3...; वे कर्मचारी नाम खोजें जिनकी नौकरी MANAGER है और जो विभाग 30 से संबंधित हैं:

SELECT ename
FROM employee
WHERE job='MANAGER' AND deptno=30;
    ENAME
    ----------
    BLAKE

SELECT ename
FROM employee
WHERE job='MANAGER' OR deptno=30;
    ENAME
    ----------
    ALLEN
    WARD
    JONES
    BLAKE

NOT सिंटैक्स: SELECT column1, column2, ... FROM table_name WHERE NOT condition; वे सभी कर्मचारी खोजें जिनकी नौकरी SALESMAN नहीं है:

SELECT ename, job
from employee
WHERE NOT job='SALESMAN';
    ENAME      JOB
    ---------- ---------
    SMITH      CLERK
    JONES      MANAGER
    BLAKE      MANAGER

आप AND, OR और NOT तीनों ऑपरेटर्स का उपयोग करके जटिल शर्तें बना सकते हैं। प्राथमिकता क्रम है:

वे सभी कर्मचारी खोजें जिनकी नौकरी CLERK नहीं है और जो विभाग 20 से संबंधित हैं:

SELECT ename, job
from employee
WHERE NOT job='SALESMAN' AND sal>800;
    ENAME      JOB
    ---------- ---------
    JONES      MANAGER
    BLAKE      MANAGER

यहां पहले NOT निष्पादित हुआ और उसके बाद AND का मूल्यांकन हुआ।

अन्य उपयोगी कंडीशनल ऑपरेटर्स नीचे दिए गए हैं:

BETWEEN...AND:

SELECT *
FROM employee
WHERE sal BETWEEN 1000 AND 2000;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

LIKE:

LIKE दो वाइल्डकार्ड्स का उपयोग करता है: प्रतिशत % और अंडरस्कोर _ जो पैटर्न में वर्णों की संख्या को दर्शाते हैं।

% का अर्थ है शून्य, एक, या एकाधिक वर्ण
- %M%: किसी भी स्थिति में M रखने वाली स्ट्रिंग से मेल खाएँ
- M%: M से शुरू होने वाले मान से मेल खाएँ
- %M: M पर समाप्त होने वाले मान से मेल खाएँ
- M%A: M से शुरू और A पर समाप्त

पैटर्न केस-सेंसिटिव होते हैं।

_ ज्ञात वर्ण के पहले या बाद में अज्ञात वर्णों की संख्या निर्दिष्ट करता है। एक अंडरस्कोर एक वर्ण के बराबर है।
- _r%: दूसरे स्थान पर r रखने वाले मान से मेल खाएँ।

वे सभी कर्मचारी नाम प्राप्त करें जिनके नाम 'B' से शुरू होते हैं:

SELECT *
FROM employee
WHERE ename LIKE 'B%';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

वे सभी कर्मचारी नाम प्राप्त करें जिनके नाम 'A' से शुरू होते हैं और 'A' के बाद किसी भी स्थिति में 'E' हो:

SELECT *
FROM employee
WHERE ename LIKE 'A%E%';

        EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

IN(value1, value2, value3,..):

IN() फ़ंक्शन एक, दो या एकाधिक मान ले सकता है और WHERE क्लॉज़ में कॉलम को कोष्ठकों में दिए गए मानों से मिलाने देता है:

SELECT ename, job, hiredate
FROM employee
WHERE job IN ('CLERK','SALESMAN');
    ENAME      JOB       HIREDATE
    ---------- --------- ---------
    SMITH      CLERK     17-DEC-80
    ALLEN      SALESMAN  20-FEB-81
    WARD       SALESMAN  22-FEB-81

आप IN() में SELECT स्टेटमेंट का भी उपयोग कर सकते हैं जो कुछ मान लौटाएगा। उदाहरण के लिए:

SELECT ename, job, hiredate
FROM employee
WHERE deptno IN (select deptno FROM department WHERE loc='CHICAGO');
    ENAME      JOB       HIREDATE
    ---------- --------- ---------
    ALLEN      SALESMAN  20-FEB-81
    WARD       SALESMAN  22-FEB-81
    BLAKE      MANAGER   01-MAY-81

IN() में SELECT स्टेटमेंट को सब-क्वेरी भी कहा जाता है। इस ट्यूटोरियल में आगे सब-क्वेरीज़ पर और चर्चा होगी!

IS NULL:

IS NULL का उपयोग दिए गए एट्रिब्यूट में NULL मानों की जाँच के लिए किया जाता है। उदाहरण के लिए, वे सभी कर्मचारी खोजें जिन्हें कमीशन नहीं मिला:

SELECT ename, job, sal
FROM employee
WHERE comm IS NULL;
    ENAME      JOB              SAL
    ---------- --------- ----------
    SMITH      CLERK            800
    JONES      MANAGER         2975
    BLAKE      MANAGER         2850

यदि आप उन कर्मचारियों के नाम पाना चाहते हैं जिन्हें कमीशन मिला, तो आप IS NOT NULL का उपयोग करेंगे:

SELECT ename, job, sal, comm
FROM employee
WHERE comm IS NOT NULL;
    ENAME      JOB              SAL       COMM
    ---------- --------- ---------- ----------
    ALLEN      SALESMAN        1600        300
    WARD       SALESMAN        1250        500

दिनांक कॉलम का उपयोग करके परिणाम फ़िल्टर करने के लिए आपको डिफ़ॉल्ट दिनांक फ़ॉर्मैट का उपयोग करना होगा। यदि कोई अन्य फ़ॉर्मैट उपयोग करना है, तो दिनांक फ़ंक्शंस लागू करने होंगे जिन्हें आप इस ट्यूटोरियल में आगे देखेंगे। 21 फ़रवरी 1981 के बाद नियुक्त किए गए सभी कर्मचारियों को खोजें:

(यहां Oracle का डिफ़ॉल्ट दिनांक फ़ॉर्मैट उपयोग हुआ है)

SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81';
    ENAME      JOB              SAL       COMM
    ---------- --------- ---------- ----------
    WARD       SALESMAN        1250        500
    JONES      MANAGER         2975
    BLAKE      MANAGER         2850

उपरोक्त सभी ऑपरेटर्स के संयोजन से जटिल कंडीशनल एक्सप्रेशंस बनाए जा सकते हैं, पर आपको प्राथमिकता क्रम का ध्यान रखना होगा। प्राथमिकता वह क्रम है जिसमें ऑपरेटर्स का मूल्यांकन किया जाता है। अलग-अलग डेटाबेस के लिए प्राथमिकता नियम निम्न हैं:

दो उपयोगी फ़ंक्शंस हैं: ANY(), ALL() जिन्हें कंडिशंस में उपयोग किया जा सकता है। उदाहरण:

SELECT ename, job, sal, comm
FROM employee
WHERE deptno=ANY(SELECT deptno from dept WHERE loc='NEW YORK');

SELECT ename, job, sal, comm
FROM employee
WHERE deptno=ALL(SELECT deptno from dept WHERE dname='SALES');

ऊपर के उदाहरणों में सबक्वेरी का उपयोग देखें। आप आगे सबक्वेरी के बारे में और सीखेंगे।

ORDER BY क्लॉज़ का उपयोग करके परिणाम क्रमित करना

आप अपने परिणामों को किसी भी एट्रिब्यूट या एकाधिक एट्रिब्यूट्स के अनुसार आरोही (ASC) या अवरोही (DESC) क्रम में लगा सकते हैं। आप SELECT क्लॉज़ में दिए गए aliases के अनुसार भी ऑर्डर कर सकते हैं:

SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY sal desc;
    ENAME      JOB              SAL       COMM
    ---------- --------- ---------- ----------
    JONES      MANAGER         2975
    BLAKE      MANAGER         2850
    WARD       SALESMAN        1250        500

ध्यान दें: डिफ़ॉल्ट क्रम आरोही होता है, जिसे ASC से दर्शाते हैं, और ASC लिखने की आवश्यकता नहीं होती।

SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY sal;
    ENAME      JOB              SAL       COMM
    ---------- --------- ---------- ----------
    WARD       SALESMAN        1250        500
    BLAKE      MANAGER         2850
    JONES      MANAGER         2975

आप ORDER BY क्लॉज़ में एकाधिक कॉलम निर्दिष्ट कर सकते हैं, जो दिए गए क्रम में लागू होंगे। उदाहरण के लिए, कर्मचारियों को पहले deptno के अनुसार आरोही क्रम में, और फिर नामों को अवरोही क्रम में सॉर्ट करें:

SELECT ename, job, sal, comm, deptno
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY deptno ASC, ename DESC;
    ENAME      JOB              SAL       COMM     DEPTNO
    ---------- --------- ---------- ---------- ----------
    JONES      MANAGER         2975                    20
    WARD       SALESMAN        1250        500         30
    BLAKE      MANAGER         2850                    30

आउटपुट को अनुकूलित करने के लिए सिंगल-रो फ़ंक्शंस का उपयोग

सभी RDBMS में कई फ़ंक्शंस होते हैं जो सामान्य कार्यों में मदद करते हैं, जैसे स्ट्रिंग की लंबाई पाना, स्ट्रिंग्स जोड़ना, फ़ॉर्मैटिंग फ़ंक्शंस, गणितीय फ़ंक्शंस आदि। SQL में रो फ़ंक्शंस दो प्रकार के होते हैं:

सिंगल-रो फ़ंक्शंस
मल्टिपल-रो फ़ंक्शंस

सिंगल-रो फ़ंक्शंस:

ये फ़ंक्शंस प्रत्येक पंक्ति पर लागू होते हैं और प्रति पंक्ति परिणाम लौटाते हैं। CONCAT() एक कैरेक्टर मैनिप्युलेशन सिंगल-रो फ़ंक्शन है। इन्हें SELECT, WHERE और ORDER BY क्लॉज़ में उपयोग किया जा सकता है। सभी RDBMS में निम्न के लिए सिंगल-रो फ़ंक्शंस उपलब्ध हैं:

कैरेक्टर मैनिप्युलेशन फ़ंक्शंस
डेट और टाइम फ़ंक्शंस
नंबर फ़ंक्शंस
कन्वर्ज़न फ़ंक्शंस

अलग-अलग डेटाबेस में एक ही कार्यक्षमता के लिए फ़ंक्शन नाम अलग हो सकते हैं। यहां मैं Microsoft SQL Server, Oracle और MySQL में सामान्य उपयोगी फ़ंक्शंस बताऊंगा। इनमें से कुछ आप ऊपर के सेक्शंस में सीख चुके हैं। इस सेक्शन के अंत में, आगे अभ्यास के लिए इन विक्रेताओं की फ़ंक्शंस सूची के लिंक मिलेंगे।

आइए प्रत्येक श्रेणी पर चलते हैं:

कैरेक्टर मैनिप्युलेशन फ़ंक्शंस

LOWER(): स्ट्रिंग को लोअरकेस में बदलता है।

SELECT lower(ename) as ename
FROM employee;
    ENAME
    ----------
    smith
    allen
    ward
    jones
    blake

UPPER(): स्ट्रिंग को अपरकेस में बदलता है।

SELECT upper(ename) as ename
FROM employee;
    ENAME
    ----------
    SMITH
    ALLEN
    WARD
    JONES
    BLAKE

SUBSTR()[Oracle, MySQL]: निर्दिष्ट अनुसार सबस्ट्रिंग लौटाता है SUBSTR(string, start-position, length)

SUBSTRING()[SQL Server]: निर्दिष्ट अनुसार सबस्ट्रिंग लौटाता है SUBSTRING(string, start-position, length).

SELECT SUBSTR(ename,2,3) as substr_ename
FROM employee;

SQL Server के लिए फ़ंक्शन नाम SUBSTRING से बदल दें।

SUBSTR_ENAME
------------
MIT
LLE
ARD
ONE
LAK

LENGTH()[Oracle, MySQL]: कोष्ठकों में दी गई स्ट्रिंग की लंबाई लौटाता है

LEN()[SQL Server]: कोष्ठकों में दी गई स्ट्रिंग की लंबाई लौटाता है

SELECT LENGTH(ename) as len_ename
FROM employee;

SQL Server के लिए फ़ंक्शन नाम LEN कर दें।

लेफ़्ट/राइट पैडिंग, रिप्लेस जैसे फ़ंक्शंस के सिंटैक्स अलग-अलग डेटाबेस में भिन्न होते हैं। तीनों विक्रेता डेटाबेस के कैरेक्टर और स्ट्रिंग फ़ंक्शंस की सूची:

नंबर फ़ंक्शंस

फ़ंक्शन नाम	कार्य
ROUND(m,n):	मान m को n दशमलव स्थानों तक राउंड करता है।
ABS(m,n):	किसी संख्या का परिमूल्य (absolute value) लौटाता है
FLOOR(n):	FLOOR n के बराबर या उससे कम का सबसे बड़ा पूर्णांक लौटाता है।
MOD(m,n):	m को n से भाग देने पर शेषफल लौटाता है (SQL Server में उपलब्ध नहीं, वहाँ % ऑपरेटर: 35 % 6)

Oracle:

    SELECT ROUND(45.926,2),MOD(11,5),FLOOR(34.4),ABS(-24) from dual;

OUTPUT:

    ROUND(45.926,2)  MOD(11,5) FLOOR(34.4)   ABS(-24)
--------------- ---------- ----------- ----------
          45.93          1          34         24

MySQL:

    SELECT ROUND(45.926,2),MOD(11,5),FLOOR(34.4),ABS(-24);

SQL Server:

    SELECT ROUND(45.926,2) as round,11%5 as mod, FLOOR(34.4) as floor,ABS(-24) as abs;

तीनों विक्रेताओं के लिए न्यूमेरिक फ़ंक्शंस की सूची:

कन्वर्ज़न फ़ंक्शंस

कन्वर्ज़न फ़ंक्शंस का उपयोग एक डेटा प्रकार को दूसरे में बदलने के लिए किया जाता है। कन्वर्ट फ़ंक्शंस सर्वर से सर्वर भिन्न होते हैं। यहां मैं Oracle सर्वर की एक क्वेरी का उदाहरण दूँगा जो to_char फ़ंक्शन का उपयोग करती है। इससे आपको समझ आएगा कि आउटपुट को अनुकूलित करने के लिए कन्वर्ज़न फ़ंक्शंस कैसे उपयोग किए जा सकते हैं। Oracle का डिफ़ॉल्ट दिनांक फ़ॉर्मैट DD-MON-YY है।

SELECT ename, to_char(hiredate,'DD, MONTH YYYY') as Hiredate, to_char(hiredate,'DY') as Day
from employee;
    ENAME      Hiredate                                     DAY
    ---------- -------------------------------------------- ------------
    SMITH      17,DECEMBER  1980                            WED
    ALLEN      20,FEBRUARY  1981                            FRI
    WARD       22,FEBRUARY  1981                            SUN
    JONES      02,APRIL     1981                            THU
    BLAKE      01,MAY       1981                            FRI

प्रत्येक डेटाबेस सर्वर के लिए बहुत से कन्वर्ज़न फ़ंक्शंस हैं। आगे पढ़ने और खोज के लिए निम्न लिंक देखें:

डेट और टाइम फ़ंक्शंस

डेट और टाइम फ़ंक्शंस आपको तारीख में दिन जोड़ने, दो तारीखों के बीच महीने निकालने आदि जैसी प्रक्रियाएँ करने देते हैं। ऐसी फ़ंक्शंस रिपोर्ट बनाते समय फ़ायदेमंद होती हैं। डेट फ़ंक्शंस भी डेटाबेस के अनुसार बदलते हैं। कोई फ़ंक्शन एक जैसा कार्य कर सकता है लेकिन किसी दूसरे डेटाबेस में उसका नाम अलग हो सकता है। यहां आप कुछ उदाहरण देखेंगे और फिर Oracle, SQL Server और MySQL—सबसे आम डेटाबेस—के डेट फ़ंक्शंस के लिंक मिलेंगे। अपने डेटाबेस सर्वर पर ये उदाहरण आज़माएँ!

अपने SQL परिणामों का समूह बनाना

ग्रुपिंग फ़ंक्शंस या मल्टिपल-रो फ़ंक्शंस समूहों पर लागू होते हैं और प्रति समूह एक परिणाम लौटाते हैं। आपको समूह फ़ंक्शंस तब चाहिए जब आप ऐसी बातें जानना चाहते हैं जैसे प्रति तिमाही कुल बिक्री, किसी अवधि में किसी उत्पाद का औसत मूल्य, इस महीने आपकी कंपनी को मिली सबसे बड़ी निवेश राशि इत्यादि।

ग्रुप फ़ंक्शंस का उपयोग करके समेकित डेटा की रिपोर्टिंग

GROUP BY क्लॉज़ का उपयोग परिणामों को समूहित करने के लिए होता है—इसे अक्सर COUNT, MAX, MIN, AVG और SUM जैसे फ़ंक्शंस के साथ उपयोग किया जाता है। GROUP BY का सिंटैक्स है:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

GROUP BY क्लॉज़ के लिए कुछ बातों का ध्यान रखें:

Alias नाम GROUP BY क्लॉज़ में उपयोग नहीं किए जा सकते
WHERE क्लॉज़ हमेशा GROUP BY से पहले आएगा
HAVING क्लॉज़ GROUP BY के बाद आएगा और समूह फ़ंक्शंस पर शर्तें लगाएगा।
ORDER BY क्लॉज़ हमेशा सबसे अंत में आएगा

COUNT एक समूह फ़ंक्शन है जो शर्त के अनुसार या बिना शर्त पंक्तियों की संख्या गिनता है। COUNT फ़ंक्शन NULL मानों की अनदेखी करता है।

उन सभी कर्मचारियों की गिनती करें जिनका नाम 'A' से शुरू होता है:

SELECT count(ename)
FROM employee
WHERE ename LIKE 'A%';

Output:

COUNT(ENAME)
------------
           1

टेबल employee में कुल वेतन, औसत वेतन, न्यूनतम वेतन और अधिकतम वेतन निकालें:

SELECT sum(sal) as "TOTAL SAL", avg(sal) as "AVG SAL", min(sal) as "MIN SAL", max(sal) as "MAX SAL"
FROM employee;

     TOTAL SAL    AVG SAL    MIN SAL    MAX SAL
    ---------- ---------- ---------- ----------
      9475       1895        800       2975

आप ऊपर के परिणाम को प्रत्येक विभाग के लिए समूहित भी कर सकते हैं:

SELECT sum(sal) as "TOTAL SAL", avg(sal) as "AVG SAL", min(sal) as "MIN SAL", max(sal) as "MAX SAL", deptno
FROM employee
GROUP BY deptno;
     TOTAL SAL    AVG SAL    MIN SAL    MAX SAL     DEPTNO
    ---------- ---------- ---------- ---------- ----------
      5700       1900       1250       2850         30
      3775     1887.5        800       2975         20

आप निम्न फ़ंक्शंस का उपयोग करके वैरियंस और मानक विचलन (Standard Deviation) भी निकाल सकते हैं:

Oracle और MySQL के लिए	MS SQL Server
SELECT STDDEV(column_name) FROM table_name;	SELECT STDEV(column_name) FROM table_name;
SELECT VARIANCE(column_name) FROM table_name;	SELECT VAR(column_name) FROM table_name;

आप एक से अधिक कॉलम के आधार पर भी समूह बना सकते हैं।

प्रत्येक विभाग के भीतर प्रत्येक नौकरी के वेतन का कुल योग निकालें:

SELECT sum(sal) as "TOTAL SAL", deptno, job
FROM employee
GROUP BY deptno, job;

     TOTAL SAL     DEPTNO   JOB
    ---------- ---------- ---------
       800         20       CLERK
      2850         30       SALESMAN
      2975         20       MANAGER
      2850         30       MANAGER

आप HAVING क्लॉज़ का उपयोग करके समूह परिणामों को सीमित कर सकते हैं। यह केवल समूह शर्तों के लिए उपयोग होता है।

ध्यान दें: समूह फ़ंक्शंस पर शर्तें लगाने के लिए WHERE का उपयोग न करें।

SELECT sum(sal) as "TOTAL SAL", deptno, job
FROM employee
GROUP BY deptno, job
HAVING sum(sal)>1000
ORDER BY sum(sal);
    TOTAL SAL     DEPTNO    JOB
    ---------- ---------- ---------
      2850         30       SALESMAN
      2850         30       MANAGER
      2975         20       MANAGER

एकाधिक टेबल्स से डेटा प्रदर्शित करना

इस सेक्शन में निम्न जॉइन्स पर चर्चा होगी:

Cartesian product/Cross join
Inner join/EquiJoins
Natural joins
Outer Joins (Left, Right, Full)
Self Join

आपको ऐसी रिपोर्ट्स प्रदर्शित करनी होंगी जो कई टेबल्स से डेटा लें। नीचे का उदाहरण देखें:

ऊपर जैसी रिपोर्ट बनाने के लिए, आपको employee और department टेबल्स को लिंक करना होगा और दोनों से डेटा लेना होगा। इसके लिए SQL में Joins का उपयोग होता है।

Cartesian Product/Cross Join:

Cartesian product तब बनता है जब रिलेशन R की हर टपल रिलेशन S की हर टपल के साथ संयोजन बनाती है।

Cartesian product ऑपरेशन, जिसे Cross Join भी कहते हैं, दो टेबल्स का गुणा करके ऐसी रिलेशन बनाता है जो दोनों टेबल्स की सभी संभव टपल-युग्मों से बनी हो। यदि एक रिलेशन R में I टपल और M एट्रिब्यूट हों और दूसरा रिलेशन S में J टपल और N एट्रिब्यूट हों, तो Cartesian product में IxJ टपल और M+N एट्रिब्यूट होंगे। SQL मानक CROSS JOIN से भी Cartesian product बनाया जा सकता है।

SELECT empno, ename, dname
FROM employee, department;

OR

SELECT empno, ename, dname
FROM employee CROSS JOIN department;
       EMPNO      ENAME      DNAME
    ---------- ---------- --------------
      7369        SMITH      ACCOUNTING
      7499        ALLEN      ACCOUNTING
      7521        WARD       ACCOUNTING
      7566        JONES      ACCOUNTING
      7698        BLAKE      ACCOUNTING
      7369        SMITH      RESEARCH
      7499        ALLEN      RESEARCH
      7521        WARD       RESEARCH
      7566        JONES      RESEARCH
      7698        BLAKE      RESEARCH
      7369        SMITH      SALES

     EMPNO        ENAME      DNAME
    ---------- ---------- --------------
      7499        ALLEN      SALES
      7521        WARD       SALES
      7566        JONES      SALES
      7698        BLAKE      SALES

15 पंक्तियाँ चुनी गईं।

चूँकि employee टेबल में 5 टपल हैं और department में 3 टपल, इसलिए Cartesian product में 5x3=15 पंक्तियाँ हैं। Cartesian product तब जनरेट होता है जब:

जॉइन कंडीशन का उपयोग नहीं किया जाता
जॉइन कंडीशन मान्य नहीं है या सही तरह से बनी नहीं है

जब दो या अधिक टेबल्स से डेटा चाहिए होता है, तो शामिल टेबल्स को जोड़ने के लिए सामान्य एट्रिब्यूट्स का उपयोग कर जॉइन कंडीशन बनाई जाती है। ये प्रायः प्राइमरी की और फॉरेन की होते हैं।

Cartesian product का उपयोग परीक्षण प्रयोजनों के लिए बड़े पैमाने पर डेटा के सिमुलेशन में किया जाता है।

*Inner Join/EquiJoin:

Inner Join या Equijoin (Oracle डेवलपर्स द्वारा प्रयुक्त शब्द) प्राइमरी और फॉरेन की रिश्तों का उपयोग करके दो या अधिक टेबल्स को जोड़ता है:

SELECT ename, dname
FROM employee e,department d
WHERE e.deptno=d.deptno;

OR

SELECT ename, dname
FROM employee e
JOIN department d
ON e.deptno=d.deptno;

    ENAME      DNAME
    ---------- --------------
    SMITH      RESEARCH
    ALLEN      SALES
    WARD       SALES
    JONES      RESEARCH
    BLAKE      SALES

ऊपर की क्वेरी में, department टेबल में deptno प्राइमरी की है और employee टेबल में फॉरेन की। आप ऊपर दिए तार्किक ऑपरेटर्स का उपयोग करके अतिरिक्त शर्तें जोड़ सकते हैं।

JOINS के लिए ध्यान रखने योग्य बिंदु:

यदि वही कॉलम नाम एक से अधिक टेबल्स में आता है, तो कॉलम नाम से पहले टेबल नाम लगाना चाहिए। स्पष्टता के लिए सामान्यतः भी ऐसा करना अच्छा अभ्यास है।

n टेबल्स को जोड़ने के लिए कम-से-कम n-1 जॉइन कंडीशंस चाहिए। उदाहरण के लिए, चार टेबल्स को जोड़ने के लिए न्यूनतम तीन जॉइन्स आवश्यक हैं।

टेबल aliases: टेबल aliases का उपयोग किया जाता है, जैसे FROM employee e, department d में e और d aliases हैं, ताकि डेटाबेस इंजन के लिए यह पहचानना आसान हो कि कॉलम किस टेबल से है—खासकर जब एक से अधिक टेबल्स में समान कॉलम हों—अन्यथा ambiguous column त्रुटि मिल सकती है। और यह लंबे टेबल नाम बार-बार लिखने से भी बचाता है।

आप nonequi-joins भी बना सकते हैं, जो समानता (equality) के अलावा अन्य शर्तों पर टेबल्स को जोड़ते हैं। salgrade नामक एक और टेबल मानें जिसमें वेतन के आधार पर कर्मचारी के ग्रेड की जानकारी है:

आप प्रत्येक कर्मचारी का ग्रेड वेतन के आधार पर जानना चाहते हैं और ग्रेड salgrade टेबल में संग्रहीत हैं जबकि वेतन employee टेबल में:

SELECT e.ename, e.sal, s.grade
FROM employee e, salgrade s
WHERE e.sal between s.losal AND s.hisal;
    ENAME             SAL      GRADE
    ---------- ---------- ----------
    JONES            2975          4
    BLAKE            2850          4
    ALLEN            1600          3
    WARD             1250          2
    SMITH             800          1

उदाहरण: आपसे कर्मचारियों के नाम, उनका वेतन, ग्रेड और विभाग का नाम देने को कहा गया है। अब, जैसा कि आप जानते हैं, वेतन employee टेबल में, ग्रेड salgrade टेबल में और विभाग का नाम department टेबल में है—तो आपको तीन टेबल्स को साथ जोड़ना होगा:

SELECT e.ename, e.sal, d.dname, s.grade
FROM employee e, department d, salgrade s
WHERE e.deptno=d.deptno
AND e.sal BETWEEN s.losal AND s.hisal;

Output:
ENAME             SAL DNAME               GRADE
---------- ---------- -------------- ----------
JONES            2975 RESEARCH                4
BLAKE            2850 SALES                   4
ALLEN            1600 SALES                   3
WARD             1250 SALES                   2
SMITH             800 RESEARCH                1

ऊपर के उदाहरण में, तीन टेबल्स शामिल हैं और दो जॉइन कंडीशंस हैं, जिनमें से एक nonequi-join है।

Natural Join:

Natural joins टेबल्स को स्वतः उन कॉलम्स का मिलान करके जोड़ने देते हैं जिनके नाम समान हों। यदि नाम समान हैं पर डेटा प्रकार अलग है, तो त्रुटि मिलती है।

सिंटैक्स: SELECT FROM table1 NATURAL JOIN table2; SELECT FROM employee NATURAL JOIN department; चूँकि natural join स्वयं मेल खाते कॉलम ढूँढता है, यह एक से अधिक समान नाम वाले कॉलम ढूँढ सकता है जिनके डेटा प्रकार अलग हो सकते हैं, जिससे त्रुटि हो सकती है। इसलिए USING क्लॉज़ का उपयोग किया जाता है ताकि यह बताया जा सके कि किन कॉलम्स पर equi join करना है।

ध्यान दें: NATURAL JOIN और USING दो अलग क्लॉज़ हैं और अलग-अलग उपयोग होते हैं। एक की उपस्थिति में दूसरा उपयोग नहीं किया जा सकता; ये परस्पर अनन्य हैं।

SELECT e.ename, d.dname, e.sal
FROM employee e JOIN department d
USING (deptno)
WHERE deptno=20;

output:

ENAME      DNAME                 SAL
---------- -------------- ----------
SMITH      RESEARCH              800
JONES      RESEARCH             2975

USING क्लॉज़ में दिए गए कॉलम्स के आगे कहीं भी SQL स्टेटमेंट में टेबल नाम या प्रीफ़िक्स नहीं लगा सकते। उदाहरण के लिए निम्न गलत है:

SELECT e.ename, d.dname, e.sal
FROM employee e JOIN department d
USING (d.deptno)
WHERE d.deptno=20;

d.deptno गलत है। केवल deptno का उपयोग होना चाहिए। Outer Joins:

Outer joins तीन प्रकार के होते हैं:

Left Outer Join: दो टेबल्स के बीच ऐसा जॉइन जो inner join के परिणामों के साथ-साथ बाएँ टेबल की अप्रमेलित पंक्तियाँ भी लौटाए, LEFT OUTER JOIN कहलाता है
Right Outer Join: दो टेबल्स के बीच ऐसा जॉइन जो inner join के परिणामों के साथ-साथ दाएँ टेबल की अप्रमेलित पंक्तियाँ भी लौटाए, RIGHT OUTER JOIN कहलाता है।
Full Outer Join: दो टेबल्स के बीच ऐसा जॉइन जो inner join के परिणामों के साथ-साथ दोनों ओर की अप्रमेलित पंक्तियाँ भी लौटाए, FULL OUTER JOIN कहलाता है।

इन्हें एक-एक करके देखें:

SELECT e.ename, s.grade
FROM salgrade s LEFT OUTER JOIN employee e
ON e.sal BETWEEN s.losal AND s.hisal;

Output:

ENAME          DEPTNO DNAME
---------- ---------- --------------
SMITH              20 RESEARCH
JONES              20 RESEARCH
ALLEN              30 SALES
WARD               30 SALES
BLAKE              30 SALES.sal BETWEEN s.losal AND s.hisal;

Output:

ENAME          DEPTNO DNAME
---------- ---------- --------------
SMITH              20 RESEARCH
JONES              20 RESEARCH
ALLEN              30 SALES
WARD               30 SALES
BLAKE              30 SALESSELECT e.ename, d.deptno, d.dname

FROM employee e RIGHT OUTER JOIN department d
ON e.deptno=d.deptno;

output:

ENAME          DEPTNO DNAME
---------- ---------- --------------
SMITH              20 RESEARCH
ALLEN              30 SALES
WARD               30 SALES
JONES              20 RESEARCH
BLAKE              30 SALES
                   10 ACCOUNTINGSELECT e.ename, d.deptno, d.dname

FROM employee e FULL OUTER JOIN department d
ON e.deptno=d.deptno;

Output:
ENAME          DEPTNO DNAME
---------- ---------- --------------
SMITH              20 RESEARCH
ALLEN              30 SALES
WARD               30 SALES
JONES              20 RESEARCH
BLAKE              30 SALES
                   10 ACCOUNTING

department टेबल में deptno 10 के लिए एक अप्रमेलित पंक्ति थी, और employee टेबल के लिए कोई अप्रमेलित पंक्ति नहीं मिली। इसी प्रकार, salgrade में grade 5 के लिए एक अप्रमेलित पंक्ति है।

Self-Join:

ऐसी स्थितियाँ होती हैं जब आप किसी टेबल को स्वयं से जोड़ना चाहते हैं। उदाहरण के लिए, employee टेबल में सभी कर्मचारियों सहित प्रबंधकों की जानकारी है (ऊपर employee टेबल देखें)। यदि आप कर्मचारियों और उनके प्रबंधकों को खोजना चाहते हैं, तो आपको टेबल को स्वयं से जोड़ना होगा। Self-Join इस प्रकार बनाए जाते हैं:

SELECT e.ename as Employee, m.ename as Manager
FROM employee e, employee m
WHERE e.mgr=m.empno;
    EMPLOYEE   MANAGER
    ---------- ----------
    ALLEN      BLAKE
    WARD       BLAKE

क्वेरीज़ हल करने के लिए सबक्वेरी का उपयोग

सबक्वेरी एक क्वेरी के भीतर क्वेरी है। बड़ी क्वेरीज़ को हिस्सों में तोड़ने के लिए सबक्वेरी उपयोगी होती है। सबक्वेरीज़ नेस्ट की जा सकती हैं और निम्न SQL क्लॉज़ में उपयोग की जा सकती हैं:

WHERE
FROM
HAVING

एक उदाहरण लें; आपसे ऐसे कर्मचारी को खोजने को कहा गया है जिसका वेतन किसी अन्य कर्मचारी, यहाँ James, से अधिक हो। आप इसे कैसे सुलझाएँगे? इस क्वेरी को इस प्रकार तोड़ा जा सकता है:

James का वेतन खोजें
James के वेतन की तुलना सभी कर्मचारियों से करें

यह सबक्वेरी का उपयोग करके हल किया जा सकता है। सबक्वेरी (भीतरी क्वेरी) मुख्य (बाहरी) क्वेरी से पहले एक बार निष्पादित होगी।

SELECT empno, ename
FROM employee
WHERE sal>(SELECT sal from employee where ename='JAMES');

सबक्वेरी के प्रकार:

सिंगल-रो सबक्वेरी: ऐसी क्वेरी जो भीतरी SELECT स्टेटमेंट से केवल एक पंक्ति लौटाती है।
मल्टिपल-रो सबक्वेरी: ऐसी क्वेरी जो भीतरी SELECT स्टेटमेंट से एक से अधिक पंक्तियाँ लौटाती है।

ध्यान रखने योग्य बिंदु:

सबक्वेरीज़ को कोष्ठकों में घेरें।

सबक्वेरीज़ को तुलना ऑपरेटर के दाईं ओर रखें।

सिंगल-रो सबक्वेरीज़ के साथ सिंगल-रो ऑपरेटर्स (>, < , >= , <= , < >) का उपयोग करें।

मल्टिपल-रो सबक्वेरीज़ के साथ मल्टिपल-रो ऑपरेटर्स (IN, ANY, ALL) का उपयोग करें।

सिंगल-रो सबक्वेरी:

आपसे उन कर्मचारियों के नाम खोजने को कहा गया है जिनका नौकरी शीर्षक empno 7521 के समान है

SELECT ename, job
FROM employee
WHERE job=(SELECT job FROM employee WHERE empno=7521);
    ENAME      JOB
    ---------- ---------
    ALLEN      SALESMAN
    WARD       SALESMAN

उन विभागों का अधिकतम वेतन चुनें जहाँ अधिकतम वेतन विभाग 20 के अधिकतम वेतन से अधिक या बराबर है

SELECT deptno, max(sal)
FROM employee
GROUP BY deptno
HAVING max(sal)>=(SELECT max(sal) FROM employee WHERE deptno=20);
    DEPTNO   MAX(SAL)
---------- ----------
        20       2975

मल्टिपल-रो सबक्वेरी:

वे कर्मचारी खोजें जिनका वेतन उन कर्मचारियों के किसी भी वेतन के बराबर है जिनकी नौकरी Manager है। यह मल्टिपल-रो सबक्वेरी होगी क्योंकि सबक्वेरी एक से अधिक पंक्तियाँ लौटा सकती है।

SELECT ename, job, sal
FROM employee
WHERE sal IN (SELECT sal FROM employee WHERE job='MANAGER');
    ENAME      JOB              SAL
    ---------- --------- ----------
    JONES      MANAGER         2975
    BLAKE      MANAGER         2850

जब सबक्वेरीज़ FROM क्लॉज़ में उपयोग होती हैं, तो वे एक अस्थायी टेबल की तरह कार्य करती हैं जो स्टोरेज पर भौतिक रूप से मौजूद नहीं होती, बल्कि डेटा का एक व्यू होती है। उदाहरण:

SELECT e.ename, e.job, e.sal
FROM employee e, (SELECT deptno FROM department WHERE loc='DALLAS') d
WHERE e.deptno=d.deptno;
    ENAME      JOB              SAL
    ---------- --------- ----------
    SMITH      CLERK            800
    JONES      MANAGER         2975

SET ऑपरेटर्स का उपयोग

SQL में Set ऑपरेटर्स का उपयोग एकाधिक क्वेरीज़ के परिणामों को एक ही परिणाम में संयोजित करने के लिए होता है। यह गणित की सेट थ्योरी और उसके ऑपरेशंस जैसे UNION, MINUS, INTERSECT का उपयोग करता है। यहां आप देखेंगे कि SET ऑपरेटर्स से क्वेरीज़ को कैसे ऑप्टिमाइज़ किया जा सकता है। इस ट्यूटोरियल में निम्न SET ऑपरेटर्स पर चर्चा होगी।

सूचना: SET ऑपरेटर्स का उपयोग करने वाली क्वेरीज़ को compound statements कहा जाता है।

UNION और UNION ALL
INTERSECT
EXCEPT (SQL मानक) और MINUS (Oracle-विशिष्ट)

UNION

दो रिलेशन्स (टेबल्स) R और S मानें, तो UNION R की सभी पंक्तियाँ और S की सभी पंक्तियाँ लेकर डुप्लीकेट्स हटा देता है। लौटाई गई पंक्तियों की संख्या अधिकतम r+s हो सकती है, जहाँ r R में पंक्तियों की संख्या है और s S में।

सभी विभाग नाम चुनें, जिनमें वे विभाग नाम भी शामिल हों जिनके कर्मचारी 23-OCT-1999 से पहले हायर हुए थे

SELECT dname
FROM department
UNION
SELECT dname
FROM department, employee
WHERE department.deptno=employee.deptno AND employee.hiredate<to_date('23-OCT-1999');
    DNAME
    --------------
    ACCOUNTING
    OPERATIONS
    RESEARCH
    SALES

UNION के लिए ध्यान रखने योग्य बिंदु:

क्वेरी में उपयोग सभी SELECT स्टेटमेंट्स में चुने गए कॉलम्स की संख्या और उनके डेटा प्रकार समान होने चाहिए।

कॉलम्स के नाम समान होना आवश्यक नहीं।

आउटपुट SELECT क्लॉज़ के पहले कॉलम के आरोही क्रम में सॉर्ट होता है।

डुप्लीकेट जाँच के दौरान NULL मानों की अनदेखी नहीं होती।

UNION ALL एक या अधिक क्वेरीज़ के परिणामों को मिलाता है और डुप्लीकेट्स नहीं हटाता, इसलिए DISTINCT का उपयोग नहीं किया जा सकता। एक और टेबल emp मानें जिसमें वर्ष 2000 के कर्मचारी हैं। emp और employee टेबल से सभी कर्मचारी संख्या, नाम और नौकरी प्राप्त करें:

SELECT empno, ename, job
FROM employee
UNION ALL
SELECT empno, ename, job
FROM emp;
       EMPNO      ENAME      JOB
    ---------- ---------- ---------
      7369        SMITH      CLERK
      7499        ALLEN      SALESMAN
      7521        WARD       SALESMAN
      7566        JONES      MANAGER
      7698        BLAKE      MANAGER
      7369        SMITH      CLERK
      7499        ALLEN      SALESMAN
      7521        WARD       SALESMAN
      7566        JONES      MANAGER
      7654        MARTIN     SALESMAN
      7698        BLAKE      MANAGER

     EMPNO        ENAME      JOB
    ---------- ---------- ---------
      7782        CLARK      MANAGER
      7788        SCOTT      ANALYST
      7839        KING       PRESIDENT
      7844        TURNER     SALESMAN
      7876        ADAMS      CLERK
      7900        JAMES      CLERK

INTERSECT

सेट थ्योरी के समान, इंटरसेक्शन दोनों सेट्स के सामान्य मान देता है। दो रिलेशन्स R और S मानें, तो INTERSECT दोनों में सामान्य सभी टपल्स का सेट लौटाएगा।

SELECT empno, ename, job
FROM employee
INTERSECT
SELECT empno, ename, job
FROM emp;
     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK
      7499 ALLEN      SALESMAN
      7521 WARD       SALESMAN
      7566 JONES      MANAGER
      7698 BLAKE      MANAGER

INTERSECT के लिए ध्यान रखने योग्य बिंदु:

क्वेरी में उपयोग सभी SELECT स्टेटमेंट्स में चुने गए कॉलम्स की संख्या और उनके डेटा प्रकार समान होने चाहिए।

कॉलम्स के नाम समान होना आवश्यक नहीं।

INTERSECT NULL मानों की अनदेखी नहीं करता

EXCEPT और MINUS:

EXCEPT (SQL Server में) और MINUS (Oracle-विशिष्ट) दोनों समान कार्य करते हैं। कार्य यह है कि पहले क्वेरी द्वारा चुनी गई वे सभी विशिष्ट पंक्तियाँ चुनें जो दूसरी क्वेरी द्वारा नहीं चुनी गईं। इस ऑपरेटर को समझने के लिए निम्न टेबल देखें:

मान लें, आपको ऐसा उत्पाद ढूँढना है जिसका quantity 1 से 100 के बीच है लेकिन 50-75 के बीच नहीं है। यदि आप SQL Server उपयोग कर रहे हैं तो यहां EXCEPT और Oracle सर्वर पर MINUS उपयोग करेंगे।

SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 1 AND 100
EXCEPT
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 50 AND 75;

Oracle सर्वर के लिए:

SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 1 AND 100
MINUS
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 50 AND 75;

      PROD_NAME               QTY      
    -------------       ----------
          COLGATE               1
          SENSODYNE             100
          SENSODYNE TOOTHBRUSH  30

EXCEPT/MINUS के लिए ध्यान रखने योग्य बिंदु:

क्वेरी में उपयोग सभी SELECT स्टेटमेंट्स में चुने गए कॉलम्स की संख्या और उनके डेटा प्रकार समान होने चाहिए।

कॉलम्स के नाम समान होना आवश्यक नहीं।

MINUS ऑपरेटर के कार्य करने के लिए WHERE क्लॉज़ में दिए गए सभी कॉलम्स SELECT क्लॉज़ में भी होने चाहिए।

बधाई हो!

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

यदि आप SQL के बारे में और सीखना चाहते हैं, तो DataCamp का निःशुल्क Intro to SQL for Data Science कोर्स करें।

संदर्भ

विषय

SQL डेटा विश्लेषण

प्रौद्योगिकियाँ

विषय

SQL: रिपोर्टिंग और विश्लेषण

AI के साथ खोजें

SQL और डेटा साइंस

SQL और डेटाबेस का परिचय

SQL डेटा प्रकार

SQL और डेटा रिपोर्टिंग

SELECT स्टेटमेंट का उपयोग करके डेटा प्राप्त करना

कॉलम उपनाम (Aliases) और कंकैटनेशन

DISTINCT का उपयोग करके दोहराई पंक्तियाँ हटाना

डेटा को सीमित करना और सॉर्ट करना

ORDER BY क्लॉज़ का उपयोग करके परिणाम क्रमित करना

आउटपुट को अनुकूलित करने के लिए सिंगल-रो फ़ंक्शंस का उपयोग

कैरेक्टर मैनिप्युलेशन फ़ंक्शंस

नंबर फ़ंक्शंस

कन्वर्ज़न फ़ंक्शंस

डेट और टाइम फ़ंक्शंस

अपने SQL परिणामों का समूह बनाना

ग्रुप फ़ंक्शंस का उपयोग करके समेकित डेटा की रिपोर्टिंग

एकाधिक टेबल्स से डेटा प्रदर्शित करना

क्वेरीज़ हल करने के लिए सबक्वेरी का उपयोग

SET ऑपरेटर्स का उपयोग

बधाई हो!

संदर्भ

विषय

SQL के बारे में और जानें

SQL में रिपोर्टिंग

प्रौद्योगिकियाँ

विषय

SQL के बारे में और जानें

SQL में रिपोर्टिंग