Lesson 8

This module explored some advanced SQL query techniques and some special Oracle extensions that can be used to your advantage. You saw how to create a hierarchical query using the CONNECT BY clause. You worked with group set commands such as INTERSECT to combine the results of two queries. You also learned how to use pseudocolumns in a query. In this module, you learned how to:

1. Use a variety of operators and conditions to write a query
2. Write a query using the CONNECT BY feature
3. Compare the INTERSECT, MINUS, and UNION commands
4. Correctly place the HAVING DISTINCT or UNIQUE clauses in two queries
5. Identify the uses of various pseudocolumns

One of Oracle's more interesting but little used or understood facilities is its connect by clause. Put simply, this method is used to report, in order, the branches of a family tree. Such trees are encountered often in the genealogy of human families as well as corporate management, company divisions, scientific research, theory and (within the realm of SQL) views built upon views. The connect by clause provides a means to report on all of the family members in any of these many trees. It lets you exclude branches or individual members of a family tree, and allows you to travel through the tree either up or down, reporting on the family members encountered during the trip. The earliest ancestor in the tree is technically called the root node. In everyday English, this would be called the trunk. Extending from the trunk are branches, which have other branches, which have still other branches. The forks where one or more branches split away from a larger branch are called nodes, and the very end of a branch is called a leaf, or a leaf node. Figure 3-8 shows a picture of such a tree. The following is a table of cows and bulls born between January 1900 and October 1908. As each offspring is born, it is entered as a row in the table, along with its sex, parents (the cow and bull), and birth date. If you compare the cows and offspring in this table with Figure 3-8, you will find they correspond. EVE has no recorded cow or bull parent because she was born on a different farm, and ADAM and BANDIT are bulls brought in for breeding, again with no parents in the table.

column Cow format a6
column Bull format a6
column Offspring format a10
column Sex format a3
select * from BREEDING
order by Birthdate;

select * from BREEDING
order by Birthdate;

In this module you were introduced to the following glossary terms:

1. Pseudocolumn: A pseudocolumn is data that Oracle makes available to you when it executes SQL commands. These are created for convenience. For example, the Oracle user name that is executing the SQL is contained in the pseudocolumn called USER
2. Group function: A group function is a predefined way to combine rows of information together and display summarized data. A common group function is the SUM function, which adds values into a total sum.
3. Hierarchical query: A form of query that retrieves data according to a hierarchy defined with a recursive relationship on a table. A parent/child list is produced that lists all the "children" items under their "parent" item.
4. Wildcard: A symbol that is used for pattern matching when using the LIKE operator. For example, % and _ are the two wildcards used in Oracle. The % wildcard can match any number of characters. The _ wildcard can match any single character.

In the next module, you will move into some new and more advanced techniques for writing queries and explore some of the Oracle extensions to standard SQL that add power to your query-writing technique.

Click the Quiz link below to take a multiple-choice quiz on the information we have covered in this module.
Oracle Extensions- Quiz