अध्याय 01 क्वेरी और SQL फ़ंक्शन
“आप जो भी अनोखी तस्वीर चाहते हैं, वह शायद पहले से ही इंटरनेट या किसी डेटाबेस में मौजूद है… आज की समस्या यह नहीं है कि सही तस्वीर कैसे बनाई जाए, बल्कि यह है कि पहले से मौजूद तस्वीर को कैसे खोजा जाए”
— लेव मैनोविच
1.1 परिचय
कक्षा ग्यारह में हमने डेटाबेस की संकल्पनाओं को समझा और MySQL का उपयोग कर डेटाबेस बनाना सीखा। हमने यह भी सीखा कि SQL क्वेरी का उपयोग कर डेटाबेस में डेटा कैसे भरा, बदला और निकाला जाता है।
इस अध्याय में हम कुछ और SQL कमांड सीखेंगे जो डेटाबेस में विभिन्न प्रश्नों को चलाने के लिए आवश्यक हैं। हम समझेंगे कि एकल पंक्ति फ़ंक्शन, बहु-पंक्ति फ़ंक्शन, रिकॉर्ड्स को आरोही या अवरोही क्रम में सजाना, किसी मानदंड के आधार पर रिकॉर्ड्स को समूहबद्ध करना और SQL का उपयोग कर कई तालिकाओं पर काम करना कैसे होता है।
आइए एक CARSHOWROOM नामक डेटाबेस बनाएँ, जिसकी स्कीमा चित्र 1.1 में दिखाई गई है। इसमें निम्नलिखित चार संबंध हैं:
- INVENTORY: शोरूम की इन्वेंटरी में मौजूद प्रत्येक कार का नाम, मूल्य, मॉडल, निर्माण वर्ष और ईंधन प्रकार संग्रहित करता है,
- CUSTOMER: प्रत्येक ग्राहक की आईडी, नाम, पता, फ़ोन नंबर और ई-मेल संग्रहित करता है,
- SALE: बेची गई कार की चालान संख्या, कार आईडी, ग्राहक आईडी, बिक्री तिथि, भुगतान का तरीका, सेल्सपर्सन की कर्मचारी आईडी और बिक्री मूल्य संग्रहित करता है,
- EMPLOYEE: शोरूम में कार्यरत प्रत्येक कर्मचारी की कर्मचारी आईडी, नाम, जन्म तिथि, शामिल होने की तिथि, पदनाम और वेतन संग्रहित करता है।
चित्र 1.1: डेटाबेस CARSHOWROOM का स्कीमा आरेख
चार संबंधों के रिकॉर्ड क्रमशः तालिकाओं $1.1,1.2,1.3$ और 1.4 में दिखाए गए हैं।
तालिका 1.1 Inventory
mysql>SELECT * FROM INVENTORY;
| Carld | CarName | Price | Model | YearManufacture | Fueltype |
|---|---|---|---|---|---|
| D001 | कार 1 | 582613.00 | LXI | 2017 | पेट्रोल |
| D002 | कार 1 | 673112.00 | VXI | 2018 | पेट्रोल |
| B001 | कार 2 | 567031.00 | सिग्मा 1.2 | 2019 | पेट्रोल |
| BOO2 | कार 2 | 647858.00 | डेल्टा 1.2 | 2018 | पेट्रोल |
| E001 | कार3 | 355205.00 | 5 STR STD | 2017 | CNG |
| E002 | कार3 | 654914.00 | CARE | 2018 | CNG |
| s001 | कार4 | 514000.00 | LXI | 2017 | पेट्रोल |
| s002 | कार4 | 614000.00 | VXI | 2018 | पेट्रोल |
8 पंक्तियाँ सेट में (0.00 सेकंड)
तालिका 1.2 CUSTOMER
mysql>SELECT * FROM CUSTOMER;
| CustId | CustName | CustAdd | Phone | Emai I |
|---|---|---|---|---|
| COOO1 | अमित साहा | L-10, पीतमपुरा | 4564587857 | $\text{amitsaha2@mail.com}$ |
| C0002 | रेहनुमा | J-12, साकेत | 5527688761 | $\text{rehnuma@hotmaiI.com}$ |
| C0OO3 | चार्वी नायर | 10/9, FF, रोहिणी | 6811635425 | $\text{charvi123@yahoo.com}$ |
| COOO4 | गुरप्रीत | A-10/2, SF, मयूर विहार | 3511056125 | $\text{gursingh@yahoo.com}$ |
4 rows in set $(0.00 \mathrm{sec})$
mysql>SELECT * FROM SALE;
तालिका 1.3 SALE
| InvoiceNo | CarId | CustId | SaleDate | PaymentMode | EmpID | SalePrice |
|---|---|---|---|---|---|---|
| I00001 | D001 | c0001 | 2019-01-24 | credit card | E004 | 613247.00 |
| I00002 | s001 | c0002 | 2018-12-12 | online | E001 | 590321.00 |
| I00003 | s002 | c0004 | 2019-01-25 | I Cheque | E010 | 604000.00 |
| I00004 | D002 | c0001 | 2018-10-15 | Bank Finance | E007 | 659982.00 |
| I00005 | E001 | c0003 | 2018-12-20 | Credit card | E002 | 369310.00 |
| I00006 | s002 | c0002 | 2019-01-30 | Bank Finance | E007 | 620214.00 |
6 rows in set $(0.00 \mathrm{sec})$
mysql>SELECT * FROM EMPLOYEE;
तालिका 1.4 EMPLOYEE
| EmpId | Emp Name | DOB | DOJ | Designation | Salary |
|---|---|---|---|---|---|
| E001 | Rushi | 1994-07-10 | 2017-12-12 | Sal esman | 25550 |
| E0O2 | Sanjay | 1990-03-12 | 2016-06-05 | Sal esman | 33100 |
| E0O3 | Zohar | 1975-08-30 | 1999-01-08 | Peon | 20000 |
| E004 | Arpit | 1989-06-06 | 2010-12-02 | Sal esman | 39100 |
| E006 | Sanjucta | 1985-11-03 | 2012-07-01 | Receptionist | 27350 |
| E007 | Mayank | 1993-04-03 | 2017-01-01 | Sal es man | 27352 |
| E010 | Raj kumar | 1987-02-26 | 2013-10-23 | Sal es man | 31111 |
1.2 SQL में फंक्शन
हम जानते हैं कि एक फंक्शन किसी विशेष कार्य को करने के लिए प्रयोग किया जाता है और यह परिणामस्वरूप शून्य या अधिक मान लौटाता है। SQL क्वेरी लिखते समय भी फंक्शन उपयोगी होते हैं। फंक्शन को किसी तालिका के एक या अनेक रिकॉर्ड (पंक्तियों) पर काम करने के लिए लगाया जा सकता है। इनके एक या अनेक पंक्तियों में प्रयोग के आधार पर SQL फंक्शनों को Single row functions और Aggregate functions में वर्गीकृत किया गया है।
1.2.1 Single Row Functions
इन्हें Scalar functions भी कहा जाता है। Single row functions एक एकल मान पर लागू होते हैं और एक एकल मान लौटाते हैं। आकृति 1.2 तीन श्रेणियों—Numeric (Math), String, Date and Time—के अंतर्गत विभिन्न single row functions को सूचीबद्ध करती है।
Math functions इनपुट के रूप में संख्यात्मक मान लेते हैं और परिणामस्वरूप संख्यात्मक मान लौटाते हैं। String functions इनपुट के रूप में वर्ण मान लेते हैं और आउटपुट के रूप में वर्ण या संख्यात्मक मान लौटाते हैं। Date and time functions इनपुट के रूप में दिनांक और समय मान लेते हैं और आउटपुट के रूप में संख्यात्मक या स्ट्रिंग, या दिनांक और समय मान लौटाते हैं।
आकृति 1.2: SQL में single row functions की तीन श्रेणियाँ
(A) Numeric Functions
तीन सामान्यतः प्रयुक्त numeric functions हैं POWER(), ROUND() और MOD()। इनके उपयोग और सिंटैक्स को तालिका 1.5 में दिया गया है।
तालिका 1.5 Math Functions
| फंक्शन | विवरण | उदाहरण सहित आउटपुट |
|---|---|---|
| POWER(X,Y) को POW(X,Y) के रूप में भी लिखा जा सकता है | X को घात Y के अनुसार गणना करता है। | mysql>SELECT POWER $(2,3)$; आउटपुट: 8 |
| ROUND(N,D) | संख्या N को D दशमलव स्थानों तक राउंड करता है। नोट: यदि D=0 है, तो यह संख्या को निकटतम पूर्णांक तक राउंड करता है। |
mysql>SELECT ROUND(2912.564, 1); आउटपुट: 2912.6 mysql> SELECT ROUND(283.2); आउटपुट: 283 |
| $\operatorname{MOD}(\mathrm{A}, \mathrm{B})$ | संख्या A को संख्या B से विभाजित करने के बाद शेषफल लौटाता है। | mysql>SELECT $\operatorname{MOD}(21,2)$; आउटपुट: 1 |
उदाहरण 1.1
बिक्री बढ़ाने के लिए, मान लीजिए कार डीलर अपने ग्राहकों को कुल राशि को 10 आसान ईएमआई (समान मासिक किस्तों) में चुकाने की पेशकश करने का निर्णय लेता है। मान लीजिए कि ईएमआई 10,000 के गुणकों में होनी आवश्यक है। इसके लिए, डीलर इन्वेंटरी तालिका से निम्नलिखित डेटा के साथ CarID और Price को सूचीबद्ध करना चाहता है:
a) GST की गणना Price का 12% के रूप में करें और परिणाम को एक दशमलव स्थान तक राउंड करके प्रदर्शित करें। mysql> SELECT ROUND(12/100*Price,1) “GST” FROM INVENTORY;
| GST |
|---|
b) इन्वेंटरी तालिका में एक नया कॉलम FinalPrice जोड़ें, जिसका मान Price और GST के 12% के योग के रूप में होगा।
mysq|>SELECT * FROM I NVENTORY;ADD FinalPrice Numeric(10,1);
क्वेरी ठीक है, 8 पंक्तियाँ प्रभावित हुईं (0.03 सेकंड)
रिकॉर्ड: 8 डुप्लिकेट: 0 चेतावनियाँ: 0
mysql> UPDATE INVENTORY SET
FinalPrice=Price+Round(Price*12/100,1);
क्वेरी ठीक है, 8 पंक्तियाँ प्रभावित हुईं (0.01 सेकंड)
मिलान पंक्तियाँ: 8 बदली गईं: 8 चेतावनियाँ: 0
mysql> SELECT * FROM INVENTORY;
| CarId | CarName | Price | Model | YearManufacture | Fuel Type | Final Pric |
|---|---|---|---|---|---|---|
| D001 | कार 1 | 582613.00 | LXI | 2017 | पेट्रोल | 652526.6 |
| D002 | कार 1 | 673112.00 | VXI | 2018 | पेट्रोल | 753885.4 |
| B001 | कार 2 | 567031.00 | सिग्मा 1.2 | 2019 | पेट्रोल | 635074.7 |
| B002 | कार 2 | 647858.00 | डेल्टा 1.2 | 2018 | पेट्रोल | 725601.0 |
| E001 | आई कार 3 | 355205.00 | 5STR STD | 2017 | CNG | 397829.6 |
| E002 | आई कार 3 | 654914.00 | CARE | 2018 | CNG | 733503.7 |
| S001 | कार 4 | 514000.00 | LXI | 2017 | पेट्रोल | 575680.0 |
| 5002 | कार 4 | 614000.00 | VXI | 2018 | पेट्रोल | 687680.0 |
8 पंक्तियाँ सेट में $(0.00 \mathrm{sec})$
c) प्रत्येक महीने देय राशि की गणना करें और प्रदर्शित करें (1000 के गुणजों में), जिसकी गणना कार की FinalPrice को 10 किस्तों में बाँटने के बाद की जानी है।
d) राशि को ईएमआई में बाँटने के बाद, मॉड्यूलर विभाजन करके तुरंत देय शेष राशि का पता लगाएँ।
उपरोक्त उल्लिखित समस्याओं को हल करने के लिए निम्न SQL क्वेरी का उपयोग किया जा सकता है:
mysql> select Carld, Finalprice, ROUND( (Finalprice-
MOD(FinalPrice, 10000)/(10,0) “EMI “, MOD(FinalPrice, 10000) “Remaining Amount " FROM I NVENTORY:
| कारआईडी | अंतिम मूल्य | ईएमआई | शेष राशि |
|---|---|---|---|
| डी001 | 652526.6 | 65000 | 2526.6 |
| डी002 | 753885.4 | 75000 | 3885.4 |
| बी001 | 635074.7 | 63000 | 5074.7 |
| बी002 | 725601.0 | 72000 | 5601.0 |
| ई001 | 397829.6 | 39000 | 7829.6 |
| ई002 | 733503.7 | 73000 | 3503.7 |
| एस001 | 575680.0 | 57000 | 5680.0 |
| एस002 | 687680.0 | 68000 | 7680.0 |
उदाहरण 1.2
क) आइए अब SALE तालिका में एक नया कॉलम कमीशन जोड़ें। कॉलम कमीशन की कुल लंबाई 7 होनी चाहिए जिसमें 2 दशमलव स्थान हों।
mysql> ALTER TABLE SALE ADD(Commission Numeric $(7,2))$;
Query OK, 6 rows affected $(0.34 \mathrm{sec})$
Records: 6 Duplicates: O Warnings: 0
ख) आइए अब बिक्री एजेंटों के लिए कमीशन को SalePrice का 12 प्रतिशत के रूप में गणना करें, नव जोड़े गए कॉलम कमीशन में मान डालें और फिर SALE तालिका के उन रिकॉर्ड्स को प्रदर्शित करें जहाँ कमीशन $>73000$ है।
mysql> UPDATE SALE SET
Commission $=12 / 100 *$ SalePrice;
Query OK, 6 rows affected $(0.06 \mathrm{sec})$
Rows matched: 6 Changed: 6 Warnings: 0
mysql> SELECT * FROM SALE WHERE Commission > 73000;
| invoiceno | carid | custid | saledate | paymentmode | empid | saleprice | Commission |
|---|---|---|---|---|---|---|---|
| I00001 | D001 | C0001 | 2019-01-24 | Credit Card | E004 | 613247.00 | 73589.64 |
| I0000 | D002 | C0001 | 2018-10-15 | Bank Finance | E007 | 659982.00 | 79197.84 |
| I00006 | S002 | C0002 | 2019-01-30 | Bank Finance | E007 | 620214.00 | 74425.68 |
c) InvoiceNo, SalePrice और Commission दिखाएँ जिससे कि commission मान 0 तक राउंड हो।
mysql > SELECT InvoiceNo, SalePrice, Round(Commission, 0) FROM SALE;
| I00001 | 613247.00 | 73590 |
|---|---|---|
| I00002 | 590321.00 | 70839 |
| I00003 | 604000.00 | 72480 |
| I00004 | 659982.00 | 79198 |
| I00005 | 369310.00 | 44317 |
| I00006 | 620214.00 | 74426 |
गतिविधि 1.1
CARSHOWROOM डेटाबेस की SALE तालिका का उपयोग करते हुए, निम्नलिखित के लिए SQL क्वेरीज़ लिखें:
a) InvoiceNo और commission मान को शून्य दशमलव स्थानों तक राउंड करके दिखाएँ।
b) उन SALE के विवरण दिखाएँ जहाँ payment mode क्रेडिट कार्ड है।
(B) स्ट्रिंग फंक्शन
स्ट्रिंग फंक्शन किसी तालिका में संग्रहीत अल्फ़ान्यूमेरिक डेटा पर विभिन्न संचालन कर सकते हैं। इनका उपयोग केस बदलने (अपरकेस से लोअरकेस या इसके विपरीत), सबस्ट्रिंग निकालने, स्ट्रिंग की लंबाई गणना करने आदि के लिए किया जा सकता है। स्ट्रिंग फंक्शन और उनके उपयोग को तालिका 1.6 में दिखाया गया है।
तालिका 1.6 स्ट्रिंग फंक्शन
| फ़ंक्शन | विवरण | उदाहरण सहित आउटपुट |
|---|---|---|
| UCASE(string) OR UPPER(string) |
स्ट्रिंग को अपरकेस में बदलता है। | mysqI > SELECT UCASE( “Informatics Practices”): Output: I NFORMATICS PRACTICES |
| LOWER(string) OR LCASE(string) |
स्ट्रिंग को लोअरकेस में बदलता है। | mysql > SELECT LOWER( “Informatics Practices”): Output: informatics practices |
| MID(string, pos, n) OR SUBSTRING(string, pos, n) OR SUBSTR(string, pos, n) |
स्ट्रिंग के निर्दिष्ट स्थान (pos) से शुरू होकर आकार $n$ का एक उपस्ट्रिंग लौटाता है। यदि $n$ निर्दिष्ट नहीं है, तो यह स्थान pos से स्ट्रिंग के अंत तक का उपस्ट्रिंग लौटाता है। | mysql> SELECT MID(“Informatics”, 3, 4); Output:form mysql>SELECT MID(‘Informatics’,7); Output: atics |
| LENGTH(string) | निर्दिष्ट स्ट्रिंग में वर्णों की संख्या लौटाता है। | mysqI > SELECT LENGTH( “Informatics”); Output: 11 |
| LEFT(string, N) | स्ट्रिंग के बाईं ओर से $N$ संख्या में वर्ण लौटाता है। | mysql > SELECT LEFT( “Computer”, 4$) ;$ Output: Comp |
| RIGHT(string, N) | स्ट्रिंग के दाईं ओर से $N$ संख्या में वर्ण लौटाता है। | mysqI> SELECT RIGHT(“SCIENCE”, 3 ); Output: NCE |
| INSTR(string, substring) | दी गई स्ट्रिंग में उपस्ट्रिंग की पहली उपस्थिति का स्थान लौटाता है। यदि उपस्ट्रिंग स्ट्रिंग में मौजूद नहीं है तो 0 लौटाता है। | mysql > SELECT INSTR( “Informatics”, “ma”): Output: 6 |
| LTRIM(string) | अग्रस्थ स्पेस वर्णों को हटाने के बाद दी गई स्ट्रिंग लौटाता है। | mysql>SELECT LENGTH(“DELHI”), LENGTH(LTRIM(“ DELHI”)); Output: |
| RTRIM(string) | अनुस्थ स्पेस वर्णों को हटाने के बाद दी गई स्ट्रिंग लौटाता है। | mysql>SELECT LENGTH(“PEN “) LENGTH(RTRIM(“PEN “)); Output: |
| TRIM(string) | अग्रस्थ और अनुस्थ दोनों स्पेस वर्णों को हटाने के बाद दी गई स्ट्रिंग लौटाता है। | mysql> SELECT LENGTH(“ MADAM “),LENGTH(TRIM(“ MADAM “)); Output: |
उदाहरण 1.3
आइए स्ट्रिंग फंक्शनों की कार्यप्रणाली को समझने के लिए टेबल 1.2 में दिखाए गए CUSTOMER संबंध का उपयोग करें।
a) CUSTOMER टेबल से ग्राहक नाम को लोअर केस में और ग्राहक ईमेल को अपर केस में प्रदर्शित करें।
mysql > SELECT LOWER(CustName), UPPER(Email) FROM CUSTOMER;
| LOWER(Cust Name) | UPPER(Emai I) |
|---|---|
| amitsaha | $\text{AMITSAHA2@GMAIL.COM}$ |
| rehnuma | $\text{REHNUMA@HOTMAIL.COM}$ |
| charvinayyar | $\text{CHARVI123@YAHOO.COM}$ |
| gurpreet | $\text{GURSINGH@YAHOO.COM}$ |
गतिविधि 1.2
CARSHOWROOM डेटाबेस की INVENTORY टेबल का उपयोग करते हुए, निम्नलिखित के लिए SQL क्वेरीज लिखें:
a) यदि CarMake का मान अक्षर ‘$\mathrm{B}$’ से शुरू होता है तो उसे अपरकेस में बदलें।
b) यदि कार के मॉडल की लंबाई 4 से अधिक है तो Model विशेषता से स्थिति 3 से अंत तक सबस्ट्रिंग प्राप्त करें।
b) ईमेल की लंबाई और ‘@’ वर्ण से पहले ईमेल आईडी का भाग प्रदर्शित करें। ध्यान दें - ‘@’ प्रिंट न करें।
mysql > SELECT LENGTH(Email), LEFT(Email, INSTR(Email,”@”) - 1) FROM CUSTOMER;
| LENGTH(Email) | LEFT(Email, INSTR(Email, “@”)-1) |
|---|---|
| 19 | amitsaha2 |
| 19 | rehnuma |
| 19 | charvi123 |
| 19 | gur_singh |
4 rows in set $(0.03 \mathrm{sec})$
INSTR फंक्शन ईमेल पते में “@” की स्थिति लौटाएगा। इसलिए “@” के बिना ईमेल आईडी प्रिंट करने के लिए हमें स्थिति - 1 का उपयोग करना होगा।
गतिविधि 1.3
CARSHOWROOM डेटाबेस की EMPLOYEE टेबल का उपयोग करते हुए, निम्नलिखित के लिए SQL क्वेरीज़ लिखें:
a) कर्मचारी का नाम और उसके EmpId के अंतिम 2 अक्षर प्रदर्शित करें।
b) कर्मचारी का पदनाम और पदनाम में ’ $\mathrm{e}$ ’ अक्षर की स्थिति प्रदर्शित करें, यदि मौजूद हो।
c) मान लें कि चार अंकों का क्षेत्र कोड मोबाइल नंबर में स्थिति संख्या 3 से शुरू होता है। उदाहरण के लिए, 4726309212 मोबाइल नंबर का क्षेत्र कोड 2630 है। अब, रोहिनी में रहने वाले ग्राहक का क्षेत्र कोड प्रदर्शित करने के लिए SQL क्वेरी लिखें।
mysql> SELECT MID(Phone, 3,4) FROM CUSTOMER WHERE Custadd like ‘%Rohini %’ ;
| MID(Phone,3,4) |
|---|
| 1163 |
1 row in set (0.00 sec)
d) ग्राहकों के ईमेल से डोमेन नाम एक्सटेंशन “.com” हटाने के बाद ईमेल प्रदर्शित करें। mysql> SELECT TRIM( “. com” from Email) FROM CUSTOMER;
| TRIM(".com” FROM Email) |
|---|
| amitsaha2@gmail |
| rehnuma@hotmail |
| charvi123@yahoo |
| gur_singh@yahoo |
4 rows in set (0.00 sec)
e) केवल याहू ईमेल वाले सभी ग्राहकों के विवरण प्रदर्शित करें।
mysql> SELECT * FROM CUSTOMER WHERE Email LIKE
“%yahoo%” ;
| Cust I D | Cust Name | CustAdd | Phone | E ma i I |
|---|---|---|---|---|
| C0OO3 | Charvi Nayyar | $10 / 9$, FF, Rohini | 6811635425 | $\text{charvi123@yahoo.com}$ |
| COOO4 | Gurpreet | A- $10 / 2, S F$, MayurVihar | 3511056125 | $\text{gursingh@yahoo.com}$ |
2 rows in set (0.00 sec)t
(C) दिनांक और समय फंक्शन
तिथि और समय डेटा पर संचालन करने के लिए विभिन्न फ़ंक्शनों का उपयोग किया जाता है। कुछ संचालनों में वर्तमान तिथि प्रदर्शित करना, तिथि के प्रत्येक तत्व को निकालना (दिन, माह और वर्ष), सप्ताह का दिन प्रदर्शित करना आदि शामिल हैं। तालिका 1.7 विभिन्न तिथि और समय फ़ंक्शनों को समझाती है।
तालिका 1.7 तिथि फ़ंक्शन
| फ़ंक्शन | विवरण | उदाहरण सहित आउटपुट |
|---|---|---|
| NOW() | यह वर्तमान सिस्टम तिथि और समय लौटाता है। | mysql > SELECT NOW(); आउटपुट: 2019-07-11 19:41:17 |
| DATE() | यह दी गई तिथि/समय अभिव्यक्ति से तिथि भाग लौटाता है। | mysql> SELECT DATE(NOW()); आउटपुट: 2019-07-11 |
| MONTH(date) | यह तिथि से माह को संख्यात्मक रूप में लौटाता है। | mysql > SELECT MONTH(NOW()); आउटपुट: 7 |
| MONTHNAME(date) | यह निर्दिष्ट तिथि से माह का नाम लौटाता है। | mysqI > SELECT MONTHNAME(“2003-11-28”) आउटपुट: November |
| YEAR(date) | यह तिथि से वर्ष लौटाता है। | mysql> SELECT YEAR( “2003-10-03”); आउटपुट: 2003 |
| DAY(date) | यह तिथि से दिन भाग लौटाता है। | mysql> SELECT DAY(“2003-03-24”); आउटपुट: 24 |
| DAYNAME(date) | यह तिथि से दिन का नाम लौटाता है। | mysqI > SELECT DAYNAME(“2019-07-11”) आउटपुट: Thursday |
उदाहरण 1.4
आइए CARSHOWROOM डेटाबेस की EMPLOYEE तालिका का उपयोग करके कुछ तिथि और समय फ़ंक्शनों के कार्य को दर्शाते हैं।
a) सभी कर्मचारियों की जॉइनिंग का दिन, माह संख्या और वर्ष चुनें।
mysql > SELECT DAY(DOJ), MONTH(DOJ), YEAR(DOJ) FROM EMPLOYEE;
| DAY(DOJ) | MONTH(DOJ) | YEAR(DOJ) |
|---|---|---|
| 12 | 12 | 2017 |
| 5 | 6 | 2016 |
| 8 | 1 | 1999 |
| 2 | 12 | 2010 |
| 1 | 7 | 2012 |
| 1 | 1 | 2017 |
| 23 | 10 | 2013 |
7 rows in set (0.03 sec)
गतिविधि 1.4
CARSHOWROOM डेटाबेस की EMPLOYEE टेबल का उपयोग करते हुए, उन सभी कर्मचारियों की जन्म का दिन सूचीबद्ध करें जिनका वेतन 25000 से अधिक है।
b) यदि जॉइनिंग की तिथि रविवार नहीं है, तो इसे निम्नलिखित प्रारूप में प्रदर्शित करें “Wednesday, 26, November, 1979.”
सोचिए और विचार कीजिए
क्या हम अंकगणितीय संचालकों $\left(+,-.{ }^{*}\right.$, or /) का उपयोग डेट फंक्शंस पर कर सकते हैं?
mysql> SELECT DAYNAME(DOJ), DAY(DOJ),
MONTHNAME(DOJ), YEAR(DOJ) FROM EMPLOYEE WHERE
DAYNAME(DOJ)!=‘Sunday’;
| DAYNAME(DOJ) | DAY(DOJ) | MONTHNAME(DOJ) | YEAR(DOJ) |
|---|---|---|---|
| Tuesday | 12 | December | 2017 |
| Friday | 8 | January | 1999 |
| Thursday | 2 | December | 2010 |
| Wednesday | 23 | October | 2013 |
4 rows in set (0.00 sec)
1.2.2 एग्रीगेट फंक्शंस
एग्रीगेट फंक्शंस को बहु-पंक्ति फंक्शंस भी कहा जाता है। ये फंक्शंस रिकॉर्ड्स के एक समूह पर समग्र रूप से कार्य करते हैं, और जिस कॉलम पर फंक्शन लागू किया जाता है उसके लिए एकल मान लौटाते हैं। तालिका 1.8 एकल-पंक्ति फंक्शंस और बहु-पंक्ति फंक्शंस के बीच अंतर दिखाती है। तालिका 1.9 कुछ एग्रीगेट फंक्शंस और उनके उपयोग का वर्णन करती है। ध्यान दें कि कॉलम संख्यात्मक प्रकार का होना चाहिए।
तालिका 1.8 एकल पंक्ति और बहु-पंक्ति फ़ंक्शनों के बीच अंतर
| एकल_पंक्ति फ़ंक्शन | बहु-पंक्ति फ़ंक्शन |
|---|---|
| 1. यह एक समय में एक ही पंक्ति पर काम करता है। | 1. यह पंक्तियों के समूहों पर काम करता है। |
| 2. यह प्रति पंक्ति एक परिणाम देता है। | 2. यह पंक्तियों के समूह के लिए एक परिणाम देता है। |
| 3. इसे Select, Where और Order by क्लॉज़ में इस्तेमाल किया जा सकता है। | 3. इसे केवल select क्लॉज़ में इस्तेमाल किया जा सकता है। |
| 4. गणित, स्ट्रिंग और तिथि फ़ंक्शन एकल पंक्ति फ़ंक्शन के उदाहरण हैं। | 4. Max(), Min(), Avg(), Sum(), Count() और Count(*) बहु-पंक्ति फ़ंक्शन के उदाहरण हैं। |
तालिका 1.9 SQL में एग्रीगेट फ़ंक्शन
| फ़ंक्शन | विवरण | उदाहरण सहित आउटपुट |
|---|---|---|
| MAX(column) | निर्दिष्ट कॉलम से सबसे बड़ा मान लौटाता है। | mysqI > SELECT MAX(Price) FROM I NVENTORY; आउटपुट: 673112.00 |
| MIN(column) | निर्दिष्ट कॉलम से सबसे छोटा मान लौटाता है। | mysqI > SELECT MIN(Price) FROM I NVENTORY; आउटपुट: 355205.00 |
| AVG(column) | निर्दिष्ट कॉलम के मानों का औसत लौटाता है। | mysqI > SELECT AVG(Price) FROM I NVENTORY; आउटपुट: 576091.625000 |
| SUM(column) | निर्दिष्ट कॉलम के मानों का योग लौटाता है। | mysqI > SELECT SUM(Price) FROM I NVENTORY; आउटपुट: 4608733.00 |
| COUNT(column) | निर्दिष्ट कॉलम में NULL मानों को छोड़कर मानों की संख्या लौटाता है। नोट: इस उदाहरण में, मान लें कि MANAGER टेबल में दो विशेषताएँ और चार रिकॉर्ड हैं। |
mysql> SELECT COUNT(MEMNAME) FROM MANAGER; |
| COUNT $(*)$ | टेबल में रिकॉर्ड्स की संख्या लौटाता है। नोट: टेबल में किसी विशेष मानदंड से मेल खाने वाले रिकॉर्ड्स की संख्या प्रदर्शित करने के लिए, हमें COUNT(*) के साथ WHERE क्लॉज़ का उपयोग करना होगा। |
mysql> SELECT COUNT(*) from MANAGER आउटपुट: |
उदाहरण 1.5
a) तालिका INVENTORY से उन रिकॉर्ड्स की कुल संख्या प्रदर्शित करें जिनमें मॉडल VXI है।
mysql>SELECT COUNT(*) FROM INVENTORY WHERE
Model = “VXI”;
| COUNT(*) |
|---|
| 2 |
1 row in set (0.00 sec)
b) तालिका INVENTORY से उपलब्ध विभिन्न प्रकार के मॉडल्स की कुल संख्या प्रदर्शित करें।
mysql> SELECT COUNT(DISTINCT Model) FROM INVENTORY;
| COUNT(DISTINCT MODEL) |
|---|
| 6 |
1 row in set (0.09 sec)
गतिविधि 1.5
a) तालिका SALE से उस ग्राहक द्वारा खरीदी गई कारों के विक्रय मूल्य का योग निकालें जिसकी ID C0001 है।
b) तालिका SALE से अधिकतम और न्यूनतम कमीशन ज्ञात करें।
c) तालिका INVENTORY से सभी LXI मॉडल की कारों की औसत कीमत प्रदर्शित करें।
mysql>SELECT AVG(Price) FROM INVENTORY WHERE
Model = “LXI”;
| AVG(Price) |
|---|
| 548306.500000 |
1 row in set (0.03 sec)
1.3 SQL में GROUP BY
कभी-कभी हमें किसी कॉलम में सामान्य मानों के आधार पर पंक्तियों के समूह को लाना होता है। यह GROUP BY क्लॉज़ का उपयोग करके किया जा सकता है। यह उन पंक्तियों को एक साथ समूहित करता है जिनमें निर्दिष्ट कॉलम में समान मान होते हैं। हम समूहित मानों पर कार्य करने के लिए समुच्चय फलनों (COUNT, MAX, MIN, AVG और SUM) का उपयोग कर सकते हैं। SQL में HAVING क्लॉज़ का उपयोग GROUP BY क्लॉज़ वाली पंक्तियों पर शर्तें निर्दिष्ट करने के लिए किया जाता है।
CARSHOWROOM डेटाबेस की SALE तालिका पर विचार करें:
mysql> SELECT * FROM SALE;
| InvoiceNo | CarId | CustId | SaleDate | PaymentMode | EmpID | SalePrice | Commission |
|---|---|---|---|---|---|---|---|
| I00001 | D001 | C0001 | 2019-01-24 | Credit Card | E004 | 613247.00 | 73589.64 |
| I00002 | S001 | C0002 | 2018-12-12 | Online | E001 | 590321.00 | 70838.52 |
| I00003 | S002 | C0004 | 2019-01-25 | Cheque | E010 | 604000.00 | 72480.00 |
| I00004 | D002 | C0001 | 2018-10-15 | Bank Finance | E007 | 659982.00 | 79197.84 |
| I00005 | E001 | C0003 | 2018-12-20 | Credit Card | E002 | 369310.00 | 44317.20 |
| I00006 | S002 | C0002 | 2019-01-30 | Bank Finance | E007 | 620214.00 | 74425.68 |
6 rows in set (0.11 sec)
CarID, CustID, SaleDate, PaymentMode, EmpID, SalePrice वे कॉलम हैं जिनमें समान मानों वाली पंक्तियाँ हो सकती हैं। अतः इन कॉलमों में Group By clause का उपयोग किसी विशेष प्रकार (कॉलम) के रिकॉर्ड्स की संख्या ज्ञात करने या प्रत्येक कार प्रकार की कीमतों का योग निकालने के लिए किया जा सकता है।
उदाहरण 1.6
a) SALE टेबल से प्रत्येक ग्राहक द्वारा खरीदी गई कारों की संख्या प्रदर्शित करें।
mysql > SELECT CustID, COUNT(*) “Number of Cars”
FROM SALE GROUP BY CustID;
| CustID | Number of Cars |
|---|---|
| C0001 | 2 |
| C0002 | 2 |
| C0003 | 1 |
| C0004 | 1 |
4 rows in set (0.00 sec)
b) SALE टेबल से वे ग्राहक आईडी और कारों की संख्या प्रदर्शित करें जिन्होंने 1 से अधिक कार खरीदी हैं।
mysql> SELECT CustID, COUNT(*) FROM SALE GROUP BY
CustID HAVING COUNT(*) >1;
| CustID | COUNT(*) |
|---|---|
| C0001 | 2 |
| C0002 | 2 |
2 rows in set (0.30 sec)
गतिविधि 1.6
a) प्रत्येक कर्मचारी द्वारा बेची गई कुल कारों की सूची बनाएं।
b) प्रत्येक कर्मचारी द्वारा की गई अधिकतम बिक्री की सूची बनाएं।
c) SALE तालिका से प्रत्येक भुगतान मोड की श्रेणी में लोगों की संख्या प्रदर्शित करें।
mysql > SELECT PaymentMode, COUNT (Payment Mode) FROM SALE GROUP BY Paymentmode ORDER BY Paymentmode;
| PaymentMode | Count(PaymentMode) |
|---|---|
| Bank Finance | 2 |
| Cheque | 1 |
| Credit Card | 2 |
| Online | 1 |
4 rows in set (0.00 sec)
d) वह PaymentMode और उस मोड से किए गए भुगतानों की संख्या प्रदर्शित करें जो एक से अधिक बार हुए हों।
mysql > SELECT PaymentMode, Count (PaymentMode) FROM SALE GROUP BY Paymentmode HAVI NG COUNT(*) >1 ORDER
| PaymentMode | Count(PaymentMode) |
|---|---|
| Bank Finance | 2 |
| Credit Card | 2 |
2 rows in set (0.00 sec)
1.4 संबंधों पर संक्रियाएँ
हम संबंधों पर कुछ संक्रियाएँ कर सकते हैं जैसे यूनियन, इंटरसेक्शन और सेट डिफरेंस, जिनसे दो तालिकाओं के टपल्स को मिलाया जा सके। ये तीनों संक्रियाएँ द्विआधारी (binary) हैं क्योंकि ये दो तालिकाओं पर काम करती हैं। ध्यान दें कि ये संक्रियाएँ तभी लागू की जा सकती हैं जब दोनों संबंधों में गुणों (attributes) की संख्या समान हो और दोनों तालिकाओं में संगत गुणों के डोमेन भी समान हों।
1.4.1 यूनियन (U)
यह संक्रिया दो तालिकाओं की चयनित पंक्तियों को एक साथ जोड़ने के लिए प्रयोग की जाती है। यदि कुछ पंक्तियाँ दोनों तालिकाओं में समान हैं, तो यूनियन संक्रिया का परिणाम उन पंक्तियों को केवल एक बार दिखाएगा। चित्र 1.3 दो सेटों का यूनियन दिखाता है।
चित्र 1.3: दो समुच्चयों का संघ
आइए दो संबंधों DANCE और MUSIC पर विचार करें जो क्रमशः तालिकाओं 1.10 और 1.11 में दिखाए गए हैं।
तालिका 1.10 DANCE
| SNo | Name | Class |
|---|---|---|
| 1 | Aastha | 7A |
| 2 | Mahira | 6A |
| 3 | Mohit | 7B |
| 4 | Sanjay | 7A |
तालिका 1.11 MUSIC
| SNo | Name | Class |
|---|---|---|
| 1 | Mehak | 8A |
| 2 | Mahira | 6A |
| 3 | Lavanya | 7A |
| 4 | Sanjay | 7A |
| 5 | Abhay | 8A |
यदि हमें उन विद्यार्थियों की सूची चाहिए जो किसी एक या दोनों आयोजनों में भाग ले रहे हैं, तो हमें संबंधों DANCE और MUSIC पर संघ (UNION) संक्रिया (जिसे U प्रतीक द्वारा दर्शाया जाता है) लगानी होगी। संघ संक्रिया का आउटपुट तालिका 1.12 में दिखाया गया है।
तालिका 1.12 DANCE $\cup$ MUSIC
| SNo | Name | Class |
|---|---|---|
| 1 | Aastha | 7A |
| 2 | Mahira | 6A |
| 3 | Mohit | 7B |
| 4 | Sanjay | 7A |
| 1 | Mehak | 8A |
| 3 | Lavanya | 7A |
| 5 | Abhay | 8A |
1.4.2 INTERSECT ( )
इंटरसेक्ट संक्रिया दो तालिकाओं से सामान्य टपल प्राप्त करने के लिए प्रयोग की जाती है और इसे $\cap$ प्रतीक द्वारा दर्शाया जाता है। चित्र 1.4 दो समुच्चयों का प्रतिच्छेदन दिखाता है।
चित्र 1.4: दो समुच्चयों का प्रतिच्छेदन
मान लीजिए हमें उन विद्यार्थियों की सूची प्रदर्शित करनी है जो दोनों आयोजनों (DANCE और MUSIC) में भाग ले रहे हैं, तो इन दो तालिकाओं पर प्रतिच्छेदन संक्रिया लागू की जाएगी। INTERSECT संक्रिया का आउटपुट तालिका 1.13 में दिखाया गया है।
तालिका 1.13 DANCE MUSIC
| SNo | Name | Class |
|---|---|---|
| 2 | महिरा | 6A |
| 4 | संजय | 7A |
1.4.3 MINUS (-)
यह संक्रिया उन टपल/पंक्तियों को प्राप्त करने के लिए प्रयोग की जाती है जो पहली तालिका में हैं परंतु दूसरी तालिका में नहीं हैं, और इस संक्रिया को चिह्न - (माइनस) द्वारा दर्शाया जाता है। चित्र 1.5 दो समुच्चयों के बीच माइनस संक्रिया (सेट अंतर भी कहा जाता है) दिखाता है।
चित्र 1.5: दो समुच्चयों का अंतर
मान लीजिए, हमें उन विद्यार्थियों की सूची चाहिए जो केवल MUSIC में भाग ले रहे हैं और DANCE आयोजन में नहीं। तब हम MINUS संक्रिया का प्रयोग करेंगे, जिसका आउटपुट तालिका 1.14 में दिया गया है।
तालिका 1.14 DANCE - MUSIC
| SNo | Name | Class |
|---|---|---|
| 1 | मेहक | 8A |
| 3 | लावण्या | 7A |
| 5 | अभय | 8A |
1.4.4 कार्टेशियन गुणन
कार्टेशियन गुणन संचालन दो संबंधों से टपलों को मिलाता है। यह दो इनपुट संबंधों से सभी पंक्ति युग्मों को परिणामस्वरूप देता है, चाहे उनके पास सामान्य गुणधर्मों पर समान मान हों या न हों। इसे ’ $\mathrm{X}$ ’ द्वारा दर्शाया जाता है।
परिणामी संबंध की डिग्री, विचाराधीन दोनों संबंधों की डिग्रियों के योग के रूप में गणना की जाती है। परिणामी संबंध की कार्डिनैलिटी, उन संबंधों की कार्डिनैलिटी के गुणनफल के रूप में गणना की जाती है जिन पर कार्टेशियन गुणन लागू किया गया है। आइए कार्टेशियन गुणन के आउटपुट को दिखाने के लिए संबंधों DANCE और MUSIC का उपयोग करें। ध्यान दें कि दोनों संबंध डिग्री 3 के हैं। संबंधों DANCE और MUSIC की कार्डिनैलिटी क्रमशः 4 और 5 है। इन दो संबंधों पर कार्टेशियन गुणन लागू करने से डिग्री 6 और कार्डिनैलिटी 20 वाला एक संबंध प्राप्त होगा, जैसा कि तालिका 1.15 में दिखाया गया है।
तालिका 1.15 DANCE X MUSIC
| क्रम संख्या | नाम | कक्षा | क्रम संख्या | नाम | कक्षा |
|---|---|---|---|---|---|
| 1 | आस्था | 7A | 1 | मेहक | 8A |
| 2 | महिरा | 6A | 1 | मेहक | 8A |
| 3 | मोहित | 7B | 1 | मेहक | 8A |
| 4 | संजय | 7A | 1 | मेहक | 8A |
| 1 | आस्था | 7A | 2 | महिरा | 6A |
| 2 | महिरा | 6A | 2 | महिरा | 6A |
| 3 | मोहित | 7B | 2 | महिरा | 6A |
| 4 | संजय | 7A | 2 | महिरा | 6A |
| 1 | आस्था | 7A | 3 | लावण्या | 7A |
| 2 | महिरा | 6A | 3 | लावण्या | 7A |
| 3 | मोहित | 7B | 3 | लावण्या | 7A |
| 4 | संजय | 7A | 3 | लावण्या | 7A |
| 1 | आस्था | 7A | 4 | संजय | 7A |
| 2 | महिरा | 6A | 4 | संजय | 7A |
| 3 | मोहित | 7B | 4 | संजय | 7A |
| 4 | संजय | 7A | 4 | संजय | 7A |
| 1 | आस्था | 7A | 5 | अभय | 8A |
| 2 | महिरा | 6A | 5 | अभय | 8A |
| 3 | मोहित | 7B | 5 | अभय | 8A |
| 4 | संजय | 7A | 5 | अभय | 8A |
20 पंक्तियाँ सेट में (0.03 सेकंड)
1.5 क्वेरी में दो संबंधों का उपयोग
अब तक हमने SQL में केवल एक संबंध का उपयोग कर क्वेरी लिखी हैं। इस खंड में हम दो संबंधों का उपयोग कर क्वेरी लिखना सीखेंगे।
1.5.1 दो तालिकाओं पर कार्टेशियन गुणा
पिछले खंड से हमने सीखा कि ऑपरेटर कार्टेशियन प्रोडक्ट को दो तालिकाओं पर लागू करने पर एक ऐसी तालिका प्राप्त होती है जिसमें अंतर्निहित तालिकाओं के सभी संभावित टपलों के संयोजन होते हैं। जब एक से अधिक तालिकाओं का उपयोग किसी क्वेरी में करना हो, तो हमें FROM क्लॉज़ में अल्पविराम से अलग करके तालिकाओं के नाम निर्दिष्ट करने होते हैं, जैसा कि उदाहरण 1.7 में दिखाया गया है। ऐसी क्वेरी चलाने पर DBMS (MySql) पहले निर्दिष्ट तालिकाओं पर कार्टेशियन प्रोडक्ट लागू करके एक एकल तालिका बनाता है। उदाहरण 1.7 की निम्नलिखित क्वेरी दो तालिकाओं DANCE और MUSIC पर कार्टेशियन प्रोडक्ट लागू करती है:
उदाहरण 1.7
a) संबंधों DANCE और MUSIC के टपलों के सभी संभावित संयोजन प्रदर्शित करें
mysql>SELECT * FROM DANCE, MUSIC;
चूँकि हम क्वेरी में SELECT * का उपयोग कर रहे हैं, इसलिए आउटपुट तालिका 1.15 होगी जिसकी डिग्री 6 और कार्डिनैलिटी 20 है।
b) संबंधों DANCE और MUSIC के टपलों के सभी संभावित संयोजनों में से केवल वे पंक्तियाँ प्रदर्शित करें जिनमें दोनों में नाम विशेषता का मान समान हो।
mys $q l$ > SELECT * FROM DANCE $D$, MUSIC M WHERE D. Name $=$ M. Name;
तालिका 1.16 समान नाम वाले टपल
| Sno | Name | Class | Sno | Name | class |
|---|---|---|---|---|---|
| 2 | Mahira | 6A | 2 | Mahira | 6A |
| 4 | Sanjay | 7A | 4 | Sanjay | 7A |
2 rows in set (0.00 sec)
ध्यान दें कि इस क्वेरी में हमने टेबल उपनामों (DANCE के लिए D और MUSIC के लिए M) का उपयोग किया है, जैसे कॉलम उपनामों की तरह, टेबल्स को छोटे नामों से संदर्भित करने के लिए। यह ध्यान देना महत्वपूर्ण है कि टेबल उपनाम केवल वर्तमान क्वेरी के लिए ही वैध होता है और यदि FROM क्लॉज़ में इसका उपनाम दिया गया है तो क्वेरी में मूल टेबल नाम का उपयोग नहीं किया जा सकता है।
1.5.2 दो तालिकाओं पर JOIN
JOIN ऑपरेशन निर्दिष्ट शर्तों पर दो तालिकाओं से टपल्स को मिलाता है। यह कार्टेशियन उत्पाद से भिन्न है, जो टपल्स के सभी संभावित संयोजन बनाता है। SQL के JOIN क्लॉज़ का उपयोग करते समय, हम FROM क्लॉज़ के भीतर दो तालिकाओं के संबंधित गुणों पर शर्तें निर्दिष्ट करते हैं। आमतौर पर, ऐसा गुण एक तालिका में प्राइमरी कुंजी और दूसरी तालिका में फॉरेन कुंजी होता है। आइए SchoolUniform डेटाबेस में दो तालिकाएं UNIFORM (UCode, UName, UColor) और COST (UCode, Size, Price) बनाते हैं। UCode तालिका UNIFORM में प्राइमरी कुंजी है। UCode और Size तालिका COST में संयुक्त कुंजी है। इसलिए, Ucode दोनों तालिकाओं के बीच एक सामान्य गुण है जिसका उपयोग दोनों तालिकाओं से सामान्य डेटा प्राप्त करने के लिए किया जा सकता है। इसलिए, हमें इस तालिका को बनाते समय Price तालिका में Ucode को फॉरेन कुंजी के रूप में परिभाषित करना होगा।
तालिका 1.17 Uniform तालिका
| Ucode | Uname | Ucolor |
|---|---|---|
| 1 | Shirt | White |
| 2 | Pant | Grey |
| 3 | Tie | Blue |
तालिका 1.18 Cost तालिका
| Ucode | Size | Price |
|---|---|---|
| 1 | L | 580 |
| 1 | M | 500 |
| 2 | L | 890 |
| 2 | M | 810 |
उदाहरण 1.7
UNIFORM और COST तालिकाओं से सम्बन्धित टपलों का UCode, UName, UColor, Size और Price सूचीबद्ध करें।
दी गई क्वेरी को नीचे दिए गए तीन भिन्न तरीकों से लिखा जा सकता है:
a) where क्लॉज़ में शर्त का प्रयोग करते हुए
mysqI > SELECT * FROM UNIFORM U, COST C WHERE
U. UCode = C. UCode;
Table 1.19 क्वेरी का आउटपुट
| UCode | UName | UColor | Ucode | Size | Price |
|---|---|---|---|---|---|
| 1 | Shirt | White | 1 | L | 580 |
| 1 | Shirt | White | 1 | M | 500 |
| 2 | Pant | Grey | 2 | L | 890 |
| 2 | Pant | Grey | 2 | M | 810 |
4 rows in set $(0.08 \mathrm{sec})$
चूँकि दोनों तालिकाओं में Ucode गुण है, अस्पष्टता दूर करने के लिए हमें तालियास का प्रयोग करना पड़ता है। इसलिए हमने SELECT और FROM क्लॉज़ में गुण UCode के साथ क्वालिफायर का उपयोग करके इसकी स्कोप दर्शाई है।
b) JOIN क्लॉज़ का स्पष्ट प्रयोग
mysqI>SELECT * FROM UNIFORM U JOIN COST C ON U. Ucode $=$ C. Ucode;
क्वेरी का आउटपुट Table 1.19 में दिखाए गए समान है। इस क्वेरी में हमने FROM क्लॉज़ में शर्त के साथ स्पष्ट रूप से JOIN क्लॉज़ का उपयोग किया है। इसलिए WHERE क्लॉज़ में कोई शर्त देने की आवश्यकता नहीं है।
c) NATURAL JOIN क्लॉज़ का स्पष्ट प्रयोग
क्वेरी (a) और (b) का आउटपुट जो टेबल 1.19 में दिखाया गया है, में Ucode नामक एक दोहराया गया कॉलम है जिसमें बिल्कुल समान मान हैं। यह अतिरिक्त कॉलम कोई अतिरिक्त जानकारी नहीं देता। JOIN ऑपरेशन का एक एक्सटेंशन NATURAL JOIN कहलाता है जो SQL में JOIN क्लॉज़ की तरह काम करता है, लेकिन अतिरिक्त ऐट्रिब्यूट को हटा देता है। यह ऑपरेटर तभी उपयोग किया जा सकता है जब दोनों टेबल्स में एक साझा ऐट्रिब्यूट हो। ऊपर दी गई SQL क्वेरी NATURAL JOIN का उपयोग करके नीचे दिखाई गई है:
mysql>SELECT * FROM UNIFORM NATURAL JOIN COST;
| UCode | UName | UColor | Size | Price |
|---|---|---|---|---|
| 1 | Shirt | White | L | 580 |
| 1 | Shirt | White | M | 500 |
| 2 | Pant | Grey | L | 890 |
| 2 | Pant | Grey | M | 810 |
आउटपुट से स्पष्ट है कि इस क्वेरी का परिणाम (a) और (b) में लिखी गई क्वेरीज़ के समान है, सिवाय इसके कि ऐट्रिब्यूट Ucode केवल एक बार दिखाई देता है।
दो या अधिक रिलेशन्स पर JOIN ऑपरेशन्स लगाते समय निम्नलिखित बिंदुओं पर विचार किया जाना चाहिए:
- यदि दो टेबल्स को साझा ऐट्रिब्यूट पर समानता की शर्त के आधार पर जोड़ना है, तो FROM क्लॉज़ में ON क्लॉज़ के साथ JOIN या NATURAL JOIN का उपयोग किया जा सकता है। यदि तीन टेबल्स को समानता की शर्त पर जोड़ना है, तो दो JOIN या NATURAL JOIN की आवश्यकता होती है।
- सामान्यतया, $\mathrm{N}$ टेबल्स को समानता की शर्त पर मिलाने के लिए $\mathrm{N}-1$ joins की आवश्यकता होती है।
- JOIN क्लॉज़ के साथ, हम दो टेबल्स के टपल्स को मिलाने के लिए कोई भी रिलेशनल ऑपरेटर उपयोग कर सकते हैं।
सारांश
- एक फंक्शन किसी विशेष कार्य को करने और परिणामस्वरूप एक मान लौटाने के लिए प्रयुक्त होता है।
- सिंगल-रो फंक्शन एक पंक्ति पर कार्य करके एक मान लौटाते हैं।
- मल्टीपल-रो फंक्शन कई पंक्तियों के समूह पर समग्र रूप से कार्य करके एक मान लौटाते हैं।
- न्यूमेरिक फंक्शन संख्यात्मक मानों पर संचालन करके संख्यात्मक मान लौटाते हैं।
- स्ट्रिंग फंक्शन कैरेक्टर प्रकार के मानों पर संचालन करके या तो कैरेक्टर या संख्यात्मक मान लौटाते हैं।
- डेट और टाइम फंक्शन हमें डेट प्रकार के डेटा मानों से निपटने देते हैं।
- GROUP BY फंक्शन उन पंक्तियों को एक साथ समूहीकृत करने के लिए प्रयुक्त होता है जो किसी निर्दिष्ट कॉलम में समान मान रखती हैं। कुछ समूह फंक्शन COUNT, MAX, MIN, AVG और SUM हैं।
- जॉइन एक ऐसा संचालन है जो दो या अधिक तालिकाओं की पंक्तियों को उनके बीच एक या अधिक सामान्य फील्ड्स के आधार पर संयुक्त करने के लिए प्रयुक्त होता है।
अभ्यास
1. निम्नलिखित प्रश्नों के उत्तर दीजिए:
a) RDBMS की परिभाषा दीजिए। कोई दो RDBMS सॉफ्टवेयरों के नाम बताइए।
b) SELECT स्टेटमेंट में निम्नलिखित क्लॉज़ का उद्देश्य क्या है?
i) ORDER BY
ii) HAVING
c) Single_row functions और Aggregate functions के बीच कोई दो अंतर बताइए।
d) Cartesian Product से आप क्या समझते हैं?
e) निम्नलिखित संचालनों को करने के लिए फंक्शनों के नाम लिखिए:
i) भारत को स्वतंत्रता मिलने की तिथि से “Monday”, “Tuesday” जैसा दिन प्रदर्शित करने के लिए।
ii) दी गई स्ट्रिंग के किसी विशेष स्थान से निर्दिष्ट संख्या में कैरेक्टर प्रदर्शित करने के लिए।
iii) उस महीने का नाम प्रदर्शित करने के लिए जिसमें आपका जन्म हुआ था।
iv) अपना नाम बड़े अक्षरों में प्रदर्शित करने के लिए।
2. निम्नलिखित SQL कमांड्स द्वारा उत्पन्न आउटपुट लिखें:
a) SELECT POW $(2,3)$;
b) SELECT ROUND(123.2345, 2), $\operatorname{ROUND}(342.9234,-1)$;
c) SELECT LENGTH(“Informatics Practices”);
d) SELECT YEAR(“1979/11/26”), MONTH(“1979/11/26”), DAY(“1979/11/26”), MONTHNAME(“1979/11/26”);
e) SELECT LEFT(“INDIA”,3), RIGHT(“Computer Science”,4);
f) SELECT MID(“Informatics”,3,4), SUBSTR(“Practices”,3);
3. निम्नलिखित “Product” नामक तालिका पर विचार करें, जो एक किराना दुकान में बेचे जा रहे उत्पादों का विवरण दिखाती है।
| PCode | PName | UPrice | Manufacturer |
|---|---|---|---|
| P01 | Washing Powder | 120 | Surf |
| P02 | Tooth Paste | 54 | Colgate |
| P03 | Soap | 25 | Lux |
| P04 | Tooth Paste | 65 | Pepsodant |
| P05 | Soap | 38 | Dove |
| P06 | Shampoo | 245 | Dove |
a) निम्नलिखित के लिए SQL क्वेरीज़ लिखें:
i. उपयुक्त डेटा प्रकारों और सीमाओं के साथ Product तालिका बनाएं।
ii. Product में प्राइमरी कुंजी की पहचान करें।
iii. प्रोडक्ट कोड, प्रोडक्ट नाम और मूल्य को उनके प्रोडक्ट नाम के अवरोही क्रम में सूचीबद्ध करें। यदि PName समान है तो मूल्य के आरोही क्रम में डेटा प्रदर्शित करें।
iv. Product तालिका में एक नया कॉलम Discount जोड़ें।
v. उन सभी उत्पादों के लिए Product तालिका में डिस्काउंट का मान UPrice का 10 प्रतिशत के रूप में गणना करें जहां UPrice 100 से अधिक है, अन्यथा डिस्काउंट 0 होगा।
vi. Dove द्वारा निर्मित सभी उत्पादों के लिए मूल्य को 12 प्रतिशत बढ़ाएं।
vii.प्रत्येक निर्माता द्वारा निर्मित उत्पादों की कुल संख्या प्रदर्शित करें।
b) Product तालिका में दी गई जानकारी के आधार पर निम्नलिखित क्वेरीज़ को चलाने पर उत्पन्न होने वाला(े) परिणाम(न) लिखें:
i. SELECT PName, Average(UPrice) FROM Product GROUP BY Pname;
ii. SELECT DISTINCT Manufacturer FROM Product;
iii. SELECT COUNT(DISTINCT PName) FROM Product;
iv. SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;
4. अध्याय में दी गई CARSHOWROOM डेटाबेस का उपयोग करते हुए, निम्नलिखित के लिए SQL क्वेरीज़ लिखें:
a) INVENTORY तालिका में एक नया कॉलम Discount जोड़ें।
b) सभी कारों के लिए उपयुक्त डिस्काउंट मान निम्न बातों को ध्यान में रखते हुए सेट करें:
(i) LXI मॉडल पर कोई डिस्काउंट उपलब्ध नहीं है।
(ii) VXI मॉडल पर 10 % डिस्काउंट दिया जाता है।
(iii) LXI मॉडल और VXI मॉडल के अलावा अन्य कारों पर 12 % डिस्काउंट दिया जाता है।
c) उस सबसे महंगी कार का नाम प्रदर्शित करें जिसका फ्यूल टाइप “Petrol” है।
d) Car4 पर उपलब्ध औसत डिस्काउंट और कुल डिस्काउंट की गणना करें।
e) उन कारों की कुल संख्या सूचीबद्ध करें जिन पर कोई डिस्काउंट नहीं है।
5. निम्नलिखित तालिकाओं Student और Stream को Streams_of_Students डेटाबेस में मानें। Stream तालिका की प्राइमरी कुंजी StCode (स्ट्रीम कोड) है जो Student तालिका में फॉरेन कुंजी है। Student तालिका की प्राइमरी कुंजी AdmNo (प्रवेश संख्या) है।
| AdmNo | Name | StCode |
|---|---|---|
| 211 | Jay | NULL |
| 241 | Aditya | S03 |
| 290 | Diksha | S01 |
| 333 | Jasqueen | S02 |
| 356 | Vedika | S01 |
| 380 | Ashpreet | S03 |
| StCode | Stream |
|---|---|
| S01 | Science |
| $\mathrm{SO}_{2}$ | Commerce |
| $\mathrm{SO}_{3}$ | Humanities |
निम्नलिखित के लिए SQL क्वेरीज़ लिखें:
a) डेटाबेस Streams_Of_Students बनाएं।
b) दी गई तालिका में दिए गए डेटा के आधार पर उपयुक्त डेटा प्रकार चुनकर तालिका Student बनाएं।
c) तालिकाओं Student और Stream से प्राइमरी कुंजियों की पहचान करें। साथ ही, तालिका Stream से फॉरेन कुंजी की पहचान करें।
d) Jay ने अब अपना स्ट्रीम Humanities कर लिया है। इस परिवर्तन को दर्शाने के लिए उपयुक्त SQL क्वेरी लिखें।
e) उन छात्रों के नाम प्रदर्शित करें जिनके नाम ‘$a$’ अक्षर से समाप्त होते हैं। साथ ही, छात्रों को वर्णानुक्रम में व्यवस्थित करें।
f) Science और Humanities स्ट्रीम में नामांकित छात्रों के नाम प्रदर्शित करें, छात्र के नाम के वर्णानुक्रम के अनुसार क्रमबद्ध करें, फिर प्रवेश संख्या के आरोही क्रम में (डुप्लिकेट नामों के लिए)।
g) प्रत्येक स्ट्रीम में उन छात्रों की संख्या सूचीबद्ध करें जिनमें 1 से अधिक छात्र हैं।
h) विभिन्न स्ट्रीमों में नामांकित छात्रों के नाम प्रदर्शित करें, जहाँ छात्रों को प्रवेश संख्या के अवरोही क्रम में व्यवस्थित किया गया है।
i) Student और Stream तालिका पर कार्टेशियन प्रोडक्ट दिखाएं। साथ ही, कार्टेशियन प्रोडक्ट लागू करने के बाद उत्पन्न डिग्री और कार्डिनैलिटी का उल्लेख करें।
j) स्ट्रीम तालिका में एक नया कॉलम “TeacherIncharge” जोड़ें। प्रत्येक पंक्ति में उपयुक्त डेटा डालें।
k) शिक्षकों और छात्रों के नामों की सूची बनाएं।
- यदि स्टूडेंट और स्ट्रीम तालिकाओं पर फिर से कार्टेशियन गुणा लागू किया जाता है, तो इस संशोधित तालिका की डिग्री और कार्डिनैलिटी क्या होगी?
BETA
