This module explored some of the advanced query techniques available to you in Oracle.
You learned how to create sub-queries WHERE you use the IN clause as the connection between the main query and the subquery.
You examined Oracle's syntax and terminology for outer joins and practiced picking out syntax errors in sample queries.
You saw what makes a subquery become a correlated subquery and found out how to create a correlated subquery. Then you tried your hand at converting a query with a subquery into a query with a correlated subquery and changed the query FROM using the IN clause to using the EXISTS clause.
In this module, you learned how to:
- Identify two proper syntax formats for the IN clause
- Interpret an outer JOIN using Oracle's syntax
- Identify the connection between a query and a subquery
- Use EXISTS instead of IN for a subquery
- Describe the parsing sequence of queries that use queries in place of a table name
In this module you were introduced to the following glossary terms:
- Correlated subquery: A subquery in which part of the subquery refers back to data in the outer query.
- Dynamic list: A list of values created by adding a subquery to a query. This kind of list is dynamic because the list of values always reflects the current values in the database.
- Inner join: A join where only the rows of one table that are matched with the rows of the other table are used in the query results.
- Left outer join: An outer join in which the left table in the Fromclause may contain rows that do not match with a row in the right table.
- Outer join: A special variation of the kind of query that combines two or more tables into a single query.
In an outer join, rows in one table that contain no match with the other table are included in the result set returned from the query.
- Right outer join: An outer join in which the right table in the Fromclause may contain rows that do not match with a row in the left table.
In the next module you will experiment with creating reports within the SQL*Plus environment.