Skip to main content

How to Querying Data by using Subqueries in SQL Programming

In SQL programming, while using subqueries, you can use the =,>, and < comparison operators to create a condition that checks the value returned by the subquery. When a subquery returns more than one value, you might need to apply the operators to all the values returned by the subquery. To perform this task, you can modify the comparison operators in the subquery. The SQL Server provides the ALL and ANY keywords that can be used to modify the existing comparison operators.

The ALL keyword returns a TRUE value, if all the values that are retrieved by the subquery satisfy the comparison operator. It returns a FALSE value if only some values satisfy the comparison operator or if the subquery does not return any rows to the outer statement.

The ANY keyword returns a TRUE value if any value that is retrieved by the subquery satisfies the comparison operator. It returns a FALSE value if no values in the subquery satisfy the comparison operator or if the subquery does not return any rows to the outer statement.

The following list shows the operators that can be used with the ALL and ANY keywords:

  • >ALL, greater than the maximum value in the list.
    The expression|column_name> ALL (10, 20, 30) means ‘greater than 30’
  • >ANY, greater than the minimum value in the list.
    The expression|column_name >ANY (10, 20, 30) means ‘greater than 10’
  • =ANY, any of the values in the list. It acts in the same way as the IN clause.
    The expression|column_name = ANY (10, 20, 30) means ‘equal to either 10 or 20 or 30’
  • <>ANY, not equal to any value in the list.
    The expression|column_name <>ANY (10, 20, 30) means ‘not equal to 10 or 20 or 30’
  • <>ALL, not equal to all the values in the list. It cats in the same way as the NOT IN clause.
    The expression|column_name <>ALL (10, 20, 30) means ‘not equal to 10 and 20 and 30’

The following example displays the employee ID column and the title of all the employees whose vacation hours are more than the vacation hours of employees designated as Recruiter:

SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE VacationHours >ALL (SELECT VacationHours
FROM HumanResources.Employee WHERE JobTitle = 'Recruiter')

In the preceding example, the inner query returns the vacation hours of all the employees who are titled as recruiter. The outer query uses the ‘>ALL’ comparison operator. This retrieves the details of those employees who have vacation hours greater than all the employees titled as recruiter.

The output of the query is displayed in the following figure.

How to Querying Data by using Subqueries in SQL Programming

Comments

Popular posts from this blog

difference between structure and union in C Language

In c language article we will see the difference between union and structure. Both are the user define datatype in c language. See the table which is mentioned below: ASP.NET Video Tutorial Series Structure Union1.The keywordstruct is used to define a structure 1. The keyword union is used to define a union. 2. When a variable is associated with a structure, the compiler allocates the memory for each member. The size of structure is greater than or equal to the sum ofsizes of its members. The smaller members may end with unused slack bytes. 2. When a variable is associated with a union, thecompiler allocates thememory by considering the size of the largest memory. So, size of union is equal to the size of largest member. 3. Each member within a structure is assigned unique storage area of location. 3. Memory allocated is shared by individual members of union. 4. The address of each member will be in ascending order This indicates that memory for each member will start at different offset v…

Difference between Linear search and Binary Search in c language

SQL Video Channel : Download all SQL Video



Binary Search Linear Search Works only on sorted items. such as  1,2,3,4,5,6  etc
Works on sorted as well as unsorted items. 12,4,5,3,2,1 etc Very efficient if the items are sorted Very efficient if the items are less and present in the beginning of the list. such as Suppose your list items are : 12,3,4,5,1 and you want to search 12 number then you get beginning in the list. Works well with arrays and not on linked lists. Works with arrays and linked lists.
Number of comparisons are less More number of comparisons are required if the items are present in the later part of the array or its elements are more.

Memory representation of Linked List Data Structures in C Language

Memory representation of Linked List

             In memory the linked list is stored in scattered cells (locations).The memory for each node is allocated dynamically means as and when required. So the Linked List can increase as per the user wish and the size is not fixed, it can vary.

               Suppose first node of linked list is allocated with an address 1008. Its graphical representation looks like the figure shown below:


      Suppose next node is allocated at an address 506, so the list becomes,



  Suppose next node is allocated with an address with an address 10,s the list become,


The other way to represent the linked list is as shown below:




 In the above representation the data stored in the linked list is “INDIA”, the information part of each node contains one character. The external pointer root points to first node’s address 1005. The link part of the node containing information I contains 1007, the address of next node. The last node …