Inquiring Information By means of JOINs As Well As Sub – Queries Part – 4

By Means Of Altered Judgment Operators

At the time of making use of sub – queries, an individual can practice the >, < as well as = judgment operators to form a condition which will examine the data given back by means of the sub – query as soon as a sub – query gives back more than one data, an individual may want to implement the judgmental operators to the every data that are given back by means of the sub – query. In the direction of completing this job, an individual can alter the judgment operators in the sub – query. The SQL Server offers the ALL and ANY keywords which can be castoff to alter the current judgment operators.

The ALL keyword gives back TRUE as a data value, when every data which are recovered by means of the sub – query fulfill the judgment operator and at the same time it gives back a FALSE data value when merely a number of data fulfill the judgment operators otherwise when the sub – query do not give back a single row or tuple to the External Query.

The ANY keyword gives back TRUE data value, when a few data which is recovered by means of the sub – query fulfill the judgment operator and at the same time it gives back FALSE data value when not a single data in the sub – query fulfill the judgment operator otherwise when the sub – query do not gives back at least one row or tuple to the External Query.

The subsequent table displays the operators which can be castoff by means of the ALL and ANY keywords:

The subsequent instances shows the customer ID column or attribute as well as the names of every customer whose rate of interest is not equal to any of the data value returned for the loan type ‘Personal’.

SELECT CustomerID , Name FROM [ XYZBank ] . [ Customer ] . [ Loan ]

WHERE ROI < > ANY ( SELECT ROI FROM [ XYZBank ] . [ Loan ] . [ Details ] WHERE

LoanType = ‘ Personal ’ )

In the previous syntax, the internal sub – query gives back the ROI for the loans which are of type Personal only. The external query make use of the < > ANY judgment operator. This as a consequence gets the details of the customers whose ROI is not equal to the ROI that was retrieved from the internal query.

By Means Of Summative Functions

At the time of using the sub – queries, an individual may practice cumulative functions in the sub – queries to produce totaled data from the internal query. For an instance, in a bank the manager desired to take a look at the Personal loan type which has a lower ROI than the average ROI of all the loan types. So, an individual should get the average of the all the ROI first, and then the individual need to get all the loan records where the ROI for the Personal loan is lesser than the average ROI of all the loan types. For this, an individual can use summative function within the sub – query. The subsequent instance displays LoanID of Personal loan which has less ROI than the average ROI of all the loan types:

SELECT LoanID FROM [ XYZBank ] . [ Loan ] . [ Details ] WHERE ROI < ( SELECT AVG

( ROI ) FROM [ XYZBank ] . [ Loan ] . [ Details ] ) and LoanType = ‘ Personal ’

In the previous instance, the internal query gives back the average ROI of all the loan types. The external query make use of the judgment operator ‘< ’ to get the output of the Personal loan which has the ROI lesser the average ROI of all the loan types.

By Means Of Nested Sub – Queries

A sub – query can have more than one sub – queries in it. Sub – queries are castoff the minute the output set of a query is reliant on the output of additional query, which in result is reliant on the output set of additional sub – query.

Think through an instance. An individual wants to see the loan ID of a customer who is having a savings account with some particular account no. In the direction of completing this job, an individual can practice the subsequent code:

SELECT LoanID , LoanType FROM [ XYZBank ] . [ Customer ] . [ Loan ] WHERE CustomerID = / * Level 1 inner Query* /

( SELECT CustomerID FROM [ XYZBank ] . [ Customer ] . [ Savings ] WHERE AccountNo = 5678900 )

In the previous instance, the internal query gives back the customer ID who is having the savings account no. 5678900. Then the4 external query gives back the loan ID along with the loan type that a particular customer took.

An individual can apply sub – quires till 32 levels. But, the number of levels which can be castoff is subjected to the memory accessible on the database server.

By Means Of Associated Sub – Queries

A associated sub – query can be demarcated as a query which is subjected to the external query for its growth. In a associated sub – query, the WHERE clause mentions a table or relation in the FROM clause. This means that the internal query is assessed for every single row or tuple of the table or relation stated in the external query.

For an instance, the following query displays the employee ID, designation and number of hours spent on vacation for all the employees whose vacation hours are greater than the average vacation hours identified for their title:

SELECT LoanID , CustomerID , ROI FROM [ XYZBank ] . [ Customer ] . [ Loan ] Loan1 WHERE Loan1 . ROI < ( SELECT AVG( Loan2 . ROI ) FROM [ XYZBank ] . [ Customer ] . [ Loan ] Loan2 WHERE Loan1 . LoanType = Loan2 . LoanType )

In the previous instance, the internal query gives back the loan types of the loan, from the [ XYZBank ] . [ Customer ] . [ Loan ] table or relation, whose ROI is equal to the average ROI of all the loans. The external query recovers the LoanID, CustomerID and the ROI of all the loans whose ROI is less than the average ROI recovered by the internal query.

This will be the last part of this series ‘Inquiring Information By means of JOINs As Well As Sub – Queries’. Hope the readers have got some knowledge.

Thanks..