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

What is meant by buffer blocks and disk buffer, What is meant by buffer blo... What is meant by buffer blocks and disk buffer   The blocks residing temporarily in main memory are referred to as buffer blocks. The area of memory where blocks reside t

Explain embedding sql statements, Question 1 Explain the functions of t... Question 1 Explain the functions of the following Storage Manager Buffer Manager Transaction Manager 2 Discuss about system catalog in a relational DBMS 3

Explain the 10 reasons to use mysql, Question 1 Explain the 10 reasons to ... Question 1 Explain the 10 reasons to use MySQL Question 2 Write the SQL statements to demonstrate the following using SELECT command Expression Evaluation ORDER BY

Explain the lock based protocol, Lock Based Protocol A lock is nothing ... Lock Based Protocol A lock is nothing but a mechanism that tells the DBMS whether a particular data item is being used by any transaction for read/write purpose. As there are t

Data warehousing and business intelligence, I need help with data warehousi... I need help with data warehousing and business intelligence course project about designing a data warehouse for a jewelry company that does not have one

What is boyce-code normal form, Boyce-Code Normal Form (BCNF) A relatio... Boyce-Code Normal Form (BCNF) A relationship is said to be in BCNF if it is already in 3NF and the left hand side of each dependency is a candidate key. A relation who is in 3N

What are the different kinds of database end users, What are the different ... What are the different kinds of database end users? Discuss the major activities of each? End-Users - End-users are the people whose jobs need access to the database for que

Define deadlock prevention, Define Deadlock prevention These protocols... Define Deadlock prevention These protocols make sure that the system will never enter a deadlock state. There are two methods to deadlock prevention. One approach makes sure t

Database Design & Management, I need help for my Uni assignments I need help for my Uni assignments

Describe the different raid levels, Describe the different RAID levels.  Di... Describe the different RAID levels.  Discuss the choices of the different RAID levels for different applications.          Ans:   There are several combinations of these approa