Execute Sub Queries and Co-Related Nested Queries on Relations. • Implement o Single-row subquery o Multiple-row subquery • Using Group Functions in a Subquery • Using HAVING Clause with Subqueries • Using Null Values in a Subquery • Data retrieval using Correlated Subqueries o EXISTS Operator o NOT EXISTS Operator .

 

EXERCISE : 9

AIM : Execute Sub Queries and Co-Related Nested Queries on Relations.
• Implement
        o Single-row subquery           o Multiple-row subquery
• Using Group Functions in a Subquery
• Using HAVING Clause with Subqueries
• Using Null Values in a Subquery
• Data retrieval using Correlated Subqueries
        o EXISTS Operator                o NOT EXISTS Operator .

Description :

NESTED QUERIES

A query embedded inside another query is called a sub query. Inner query executes initially only once and that result will be used by all the tuples of outer query.

Co-Related nested queries: Correlated subquery is a query in which the inner query is executed for each row of the outer query.

Implement :

o  Single row subquery:

A single row subquery returns zero or one row to the outer SQL statement. You can place a subquery in a WHERE clause, a HAVING clause, or a FROM clause of a SELECT statement.

EXAMPLE :

1.       Find the name and age of the oldest sailor.

select s.sname,s.age from sailors s where age=(select max(s2.age) from sailors s2);

 

o  Multiple row subquery :

ü  Multiple-row subqueries are nested queries that can return more than one row of results to the parent query. Multiple-row subqueries are used most commonly in WHERE and HAVING clauses.

ü  Since it returns multiple rows, it must be handled by set comparison operators (IN, ALL, ANY).

ü  While IN operator holds the same meaning as discussed in the earlier chapter, ANY operator compares a specified value to each value returned by the subquery while ALL compares a value to every value returned by a subquery.

ü  The below query will show the error because single-row subquery returns multiple rows

              EXAMPLE :

1.     Find sailors whose rating is better than some sailor called Horatio

select s.sid from sailors s where s.rating > ANY(select s2.rating from sailors s2 where s2.sname='Horatio');

Using Group functions in a subqueries :

   EXAMPLE :

1.              Find the names of sailors who are older than oldest sailor with a rating of 10.

select s.sname from sailors s where s.age>(select max(s2.age) from sailors s2 where s2.rating=10);

Using HAVING clauses with subqueries :

• The HAVING clause is used along with the GROUP BY clause.  The HAVING clause can be  used to select and reject row groups.
• The format of the HAVING clause is similar to the WHERE clause, consisting of the keyword HAVING followed by a search condition.
• The HAVING clause thus specifies a search condition for groups.

  EXAMPLE :

1.    Find the average age of sailors for each rating level that has at least two sailors.

select s.rating,avg(s.age) from sailors s group by s.rating having 1<(select count(*) from sailors s2 where s.rating=s2.rating);

2.    Find the average age of sailors who are of voting age (i.e., at least 18 years old) for each rating level that has at least two sailors.

select s.rating,avg(s.age) from sailors s where s.age>=18 group by s.rating having 1<(select count(*) from sailors s2 where s.rating=s2.rating);

Using NULL values in a subquery :

A field with a NULL value is a field with no value.

Syntax :

a.    IS NULL :
Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
IS NOT NULL :
Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Example :

Select sid from sailors where sid is null;

Select sid from sailors where sid is not null;

Data retrieval using correlated sub queries :

o   EXISTS operator :

The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns TRUE if the subquery returns one or more records.

Example :

1.Find the names of sailors who have reserved boat number 103.

select s.sname from sailors s where EXISTS(select * from reserves r where r.bid=103 and r.sid=s.sid);

o   NOT EXISTS operator : Negated version of EXISTS

Example:

1. Find the sid’s and names of sailors who have not reserved boat number 103.

select s.sid,s.sname from sailors s where not exists(select * from reserves r where r.bid=103 and r.sid=s.sid);

 

 

 

VIVA QUESTIONS

1. What is nested query?

2. What is co-related nested query?

3. Differentiate Single row sub query and Multiple row sub query.