Nested SQL Query or SQL Sub Query in hindi in english

if a SQL query is used inside another SQL query then this type of SQL query is called sub-query. 

यदि किसी SQL क्वेरी का उपयोग, किसी अन्य SQL क्वेरी के अंतर्गत किया जाता है तब इस प्रकार की SQL क्वेरी को सब-क्वेरी कहा जाता है। 

Nested query or inner query or Nesting of query is a special type of query in which we write a select-from-where clause inside another select-from-where clause. 

 नेस्टेड क्वेरी या आंतरिक क्वेरी या नेस्टिंग ऑफ क्वेरी का तात्पर्य यह है कि एक सेलेक्ट-फ्रॉम-व्हेयर क्लॉज का उपयोग, किसी अन्य सेलेक्ट-फ्रॉम-व्हेयर क्लॉज के अंतर्गत किया गया है। 

Nested query is used to solve complex problems where two or more tables data is used to form result set. Key value (Data) provided by sub query is used in main query in form of condition. 

 नेस्टेड क्वेरी का उपयोग जटिल समस्याओं को हल करने के लिए किया जाता है। सब क्वेरी द्वारा प्रदान की गई कीवैल्यू(डेटा) का उपयोग, शर्त के रूप में मुख्य क्वेरी में किया जाता है। 

following rules are applied when we write a sub query: - 

 सब क्वेरी लिखते समय निम्नलिखित नियम लागू किये जाते हैं -

 

1.) sub query is always written in parenthesis(). 

 सब क्वेरी हमेशा छोटे कोष्ठक() में लिखी जाती है। 

2.) sub query returns one value from columns only. 

 सब क्वेरी के अंतर्गत केवल एक कीवैल्यू (डेटा) लिखी जाती है। 

 3.) group by is used in place of order by in sub query. 

 सब क्वेरी order by के स्थान पर group by का प्रयोग करती है। 

 Syntax :- select column_name1, column_name2,... from table1 where column_name op (select column_name from table2 where predicate); we learn following customers table to understand sub query - 

हम निम्न कस्टमर टेबल की सहायता से सब क्वेरी को समाज सकते है- 

 

*Customers Table 

 Example :- select * from customers where ID IN (select ID from customers where salary>40000); 

 ID    Name      Salary 

1       Teena      50,000 

2       Deepak   70,000 

5       Aakash   55,000

Multilevel Indexing (बहुस्तरीय अनुक्रमण) in DBMS

 Multilevel Indexing (बहुस्तरीय अनुक्रमण) -

Index record is a combination of search key value and data pointers. Multilevel index is stored on the disc along with the actual data base files. 

इंडेक्स रिकॉर्ड खोज कुंजी मान और डेटा पॉइंटर का संयोजन होता है। मल्टीलेवल इंडेक्सिंग को वास्तविक डेटा बेस फ़ाइलों के साथ डिस्क पर संग्रहीत किया जाता है।

we know that as the size of data base grows so does the size of the indices. Their is immense need to keep index records in main memory so as to speed up the search operation. 

हम जानते हैं कि जैसे-जैसे डेटा बेस का आकार बढ़ता है, वैसे-वैसे इंडेक्स रिकॉर्ड  का आकार भी बढ़ता है। सर्च आपरेशन को तीव्र बनाने के लिए इंडेक्स रिकॉर्ड को मुख्य मेमोरी में रखने की अत्यधिक आवश्यकता होती है। 0

if single index is used for large data base than we cant keep large size index in memory. which leads to multilevel disc access, Multilevel index helps in breaking down single index in Several smaller indices in order to make outer most level so small then it can be Stored In single disc block which can easily be accommodated anywhere in main memory .Thus, we can perform very fast searching operation on large Data base with the help of Multilevel Indexing.  

 यदि वृहद डेटाबेस हेतु सिंगल इंडेक्स का उपयोग किया जाता है तब हम बड़े आकार के इंडेक्स को मुख्य मेमोरी में नहीं रख सकते हैं। यह स्थिति हमें मल्टीलेवल इंडेक्सिंग की ओर ले जाता है, मल्टीलेवल इंडेक्स सिंगल इंडेक्स को कई छोटे छोटे पूर्ण इंडेक्स (आऊटर मोस्ट इंडेक्स,आऊटर इंडेक्स, इनर इंडेक्स एवं इनर मोस्ट इंडेक्स) में विभाजित करने का कार्य करता है जिससे बाहरी इंडेक्स का स्तर,  इतना छोटा हो जाता है कि, इसे सिंगल डिस्क ब्लॉक में स्टोर किया जा सकता है एवं मुख्य मेमोरी में कहीं भी आसानी से रखा जा सकता है। अतः मल्टीलेवल इंडेक्सिंग की सहायता से एक बड़े डेटा बेस में भी तीव्र गति से सर्चिंग आपरेशन किया जा सकता है।

Characteristics / Advantages of Normalization

 Characteristics / Advantages of Normalisation :-

 After successful completion of normalisation proces resultant relation R Shows following Characteristic advantages- 

(i) Normalised relation doesn't have data redundency and inconsistency problem.

(ii) Normalised relation is very simple, clear and easy to understand and it will also represent database objects and their relationships.

(iii) Normalised database returns Very fast and hundred percent accurate results against any user's query, it  means it will increase the performance of database upto several times. 

(iv) Normalised database utilizes memory space of computer.

(v) Normalized database is free from all types of Anomalies like multi attribute, partial functional dependency, Transitive functional dependency, determinant problem,

multi-value dependency (mvd) and join dependency.

functional dependency, full functional dependency, partial functional dependency

Functional dependency is a constraint applied on a relation in which user can able to access values of all attributes of relation by giving value of a single attribute. it means "We can access record / row / tuple of a relation with the help of only one special attribute (key)"

Let R[A] is a relational schema. 

Here A is a set of attributes present in relation R and x,y are subset of A. 

We can say that y depends on x. 

If set of all YA then T1,T2єR such that

T1[x] = T2[x] => T1[y] = T2[y]

It shows that if two or more tuple's have same value in column x then they also have same value in column y. Thus y column depends on x column. 

We can understand functional dependency with the help of following table-

Here,   T1[A]=T2[A] => T1[C] = T2[C]
thus,  A→C

In above relation values of attribute C depend on values of attribute A but converse is not true (C→A)

There are three types of functional dependency-

(1) full functional dependency-  If relation R has a functional dependency A→B then attribute B is called full functional dependent on attribute A, if there is no Subset cєA exist for which c→B it means value of B only depend on values of A attribute.
Example-  If  all the non-key attributes depend on Primary key of table then this type of functional dependency is called full functional dependency. Similarly in account table all the non-key attributes depend on account number primary key. 

(2) partial functional dependency- If relation R has a functional dependency A→B than B is called partial functional dependent on attribute A if there is Subset cєA exist for which C→B  It means values of  attribute B can be determined by any proper subset of A. 

for example we have a relation R(A,B,C,D) where AC is primary key  AC→B, AC→D, but A→B Here A is alone capable of determining B which means B is partially dependent on AC.

Ex-  

In above table (Roll-no,Name) is a primary key then Roll-no, Name → course, Roll-no→ course, and Name→ course Thus course is partially dependent on primary key (Roll-no, name).

(3) Transitive functional dependency- If a relation R has two or more non-key attributes which are functionally dependent and satisfy following condition 

A→B, B→C=> A→C for R(A, B, C)

Then C attribute is transitively dependent on attribute A.
To understand transitive functional dependency consider following table:-



In above table transitive functional dependency is

D_name →city, city → State => D_name → State.

Conclusion :-
functional dependency is very important for relational model.so it is neccessary that all the non-key attributes must be dependent on key attribute. it means "a relation which shows full functional dependency is free from anomalies but if a relation has partial functional dependency or transitive functional dependency or both then it affects operations of database. It Shows bad anomalies which affects Data Storing, data accessing, data manipulation and applying constraint etc operations of database. Due to which database performance decreases. In addition database with anomalies will generate incorrect result and give slow response against query. Hence, Normalisation is used to remove anomalies like Partial functional dependency, transitive functional dependency and others from the database.

DBMS and SQL Syllabus