slides rdbms 5

8/11/2019 Slides Rdbms 5

1/42


2/42


3/42


4/42


5/42


6/42


7/42


8/42

Sub-queries:

A sub-query is a query within a query. A sub-query answers the queries that have multiple parts; thesub-query answers one part of the question, and the parent query answers the other part. Whenyou nest many sub-queries, the innermost query is evaluated first.

SubQueries can be used in SELECT , FROM, WHERE and HAVING clauses.

For e.g :Select all sales reps who have a higher quota than sales rep 101.

We need to analyze this query and understand how to break it into sub problems

1. First we need to find out what is the quota of sales rep 101

2. Based on this info, we need to select sales reps who have a higher quota than this value

3. So, the inner query will find the quota of sales rep 101 and the outer query will extract sales repsexceeding this quota value. The solution would look like:

SELECTRep

FROMSalesReps

WHEREQuota >

SELECTQuotaFROMSalesReps

WHEREEmpl_Num = 101;


9/42


10/42


11/42


12/42


13/42


14/42

You can reduce the three level sub query by joining Customer and CustomerPurchase of first andsecond query.

(left as an exercise)


15/42


16/42


17/42

Note:

The column names in the parent queries are available for reference in sub-queries. The column names from the tables in the sub-query cannotbe used in the parent queries. The scope is only the current query level and its sub-queries.


18/42

Note:



19/42

Note:



20/42

Note:



21/42

Constant can be used in the LHS of where clause


22/42


23/42


24/42

EXISTS:

The EXISTS operator is always followed by a sub-query in parentheses. EXISTS evaluates to TRUEif the sub-query returns at least one row.


25/42


26/42


27/42


28/42


29/42


30/42


31/42


32/42


33/42


34/42


35/42


36/42


37/42


38/42

A not exists checks for the opposite condition than an Exists. I t checks for the non-existence of a condition.


39/42


40/42


41/42


42/42

slides rdbms 5

Documents