1 / 56

Notice that No where clause In the syntax

Notice that No where clause In the syntax. Equijoins sometimes called Simple or inner join. We can use column alias to recognize between columns have same name from two different tables. Join conditions containing something other than equality operation (operator) never use = sign. Note :

deliz
Télécharger la présentation

Notice that No where clause In the syntax

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Notice that No where clause In the syntax

  2. Equijoins sometimes called Simple or inner join We can use column alias to recognize between columns have same name from two different tables.

  3. Join conditions containing something other than equality operation (operator) never use = sign

  4. Note: Can’t use the IN operator or be linked to another condition by the OR operator.

  5. Note that department_name Contracting is retrieved although it has no department_id

  6. Note that last_name Grant is retrieved although it has no department_id or departmant_name

  7. Hierarchical queries are closely related to self- joins. You can use self-joins to see who is someone's direct manager. With hierarchical queries you can see not only the direct manager, but also who that manager works for, and so on. • This lesson defines and gives examples of hierarchical queries. • Using hierarchical queries, you can build an organization chart showing the structure of a company or a department. • You can also use hierarchical queries to build a family tree with the eldest members of the family found close at the base or trunk of the tree and the youngest members representing branches of the tree. Branches can have their own branches, and so on.

  8. HIERARCHICAL QUERIES • hierarchical queries have their own new keywords: START WITH, CONNECT BY PRIOR, and LEVEL. • START WITH is used to tell Oracle which row to use as its Root of the tree it is constructing • CONNECT BY PRIOR tells Oracle how to do the inter-row joins • LEVEL is how many steps down from the top of the tree we have taken.

  9. LEVEL is a pseudo-column used with hierarchical queries, and it counts the number of steps it has taken from the root of the tree.

  10. If you wanted to create a report displaying company management levels, beginning with the highest level and indenting each of the following levels, then this would be easy to do using the LEVEL pseudo column and the LPAD function to indent employees based on their level.

  11. So if you wanted to just not include a single row in your result you would use the WHERE clause to exclude that row, but in the result it would then look like Gietz worked directly for Kochhar, which he does not.

  12. What is wrong in the following statement: SELECT last_name, department_id, salary FROM employees START WITH last_name = 'King' CONNECT BY PRIOR manager_id = employee_id;

  13. Note never use TABLE alias with USING clause

  14. The column used in USING clause not necessary to be in select clause

  15. Join using ON to specify the condition or the column used

  16. Observe the employee_id column and the manager_id column

More Related