Nested Queries

Let us see some more complex queries in this section.

Sub-queries:

Some of the basic concerns of sub-queries are:

• A sub-query is a SELECT statement that is fixed in a clause of another SELECT statement. They are often referred to as a SUB SELECT or NESTED SELECT or INNER SELECT.
• The sub-query (inner query) implements first before the main query. The product of the sub-query is used by the outer query (main query).
• Sub-query can be placed in HAVING or WHERE or FROM clauses.
• Format of using sub-queries:

                 SELECT

                 FROM

                 WHERE expr OPERATOR

                                                          (SELECT FROM

WHERE);

                Operator has a comparison operator (one or multiple row operators)

Single row operator:   >, =, >=, <, <=, <>

                         Multiple row operators: IN, ALL, ANY,

• Order by clause can't be used in sub-query, if specified it must be the end clause in the main select statement.
• Parts of sub-queries:

         ‰  Single row sub-query: It gives only single row from the inner select statement.

        ‰   Multiple row sub-queries: It gives more than single row from the inner select statement

        ‰  Multiple column sub-queries: It gives more than single column from the inner select statement.

Single row operators are used with one row sub queries and multiple row operators are used with numerous row sub queries.

• The Inner and Outer queries can get data from different tables.
• Group Functions are used in sub queries.

Posted Date: 3/11/2013 3:36:58 AM | Location : United States

Ask an Expert

Related Discussions:- Nested queries, Assignment Help, Ask Question on Nested queries, Get Answer, Expert's Help, Nested queries Discussions

Write discussion on Nested queries
Your posts are moderated

Write your message here..

Related Questions

Defining internal classes and operations, Defining Internal Classes and Ope... Defining Internal Classes and Operations When we enlarge algorithms, new classes can be added to the store intermediate results. A complex operation could be looked at, as a c

Explain domain-oriented relational calculus?, How does Tuple-oriented relat... How does Tuple-oriented relational calculus vary from domain-oriented relational calculus? The tuple-oriented calculus uses a tuple variables i.e., variable whose only allowed

Write short notes on extension and intension, Write short notes on extensio... Write short notes on extension and intension? In any data model, it is significant to distinguish among the description of the database and the database itself the description

What is a b+-tree index, What is a B+-Tree index? A B+-Tree index takes... What is a B+-Tree index? A B+-Tree index takes the form of a balanced tree in which each path from the root of the root of the root of the tree to a leaf of the tree is of the

Which component is not included in database environment, Which component is... Which component is not included in database environment ? A users component is not included in database environment.

Explain the relational completeness, Explain the Relational Completeness ... Explain the Relational Completeness Codd described the term relational completeness to consider to a language that is complete with respect to first-order predicate calculus ex

Determine bcnf-dcnf and key relation, (A) For the relation; CarSales (R... (A) For the relation; CarSales (RegNo, BuyerName, Address, SalesPerson, Make, Model, Commission) The following functional dependences hold, RegNo _ID à Make, Model Reg

Determine advantages of merging an association into a class, What are the a... What are the advantages and disadvantages of merging an association into a class?  Advantages: i)  Less number of tables ii) Faster performance due to the fewer tables

Data analyzing and desing, Critically explained the benefits and limit... Critically explained the benefits and limitations of different database technologies with proper examples

aggregate functions-data manipulation language, Aggregate functions ... Aggregate functions Some of these functions are min, count, max, and avg. These functions help in getting consolidated information from a set of tuples. Example: