Source : https://www.linkedin.com/pulse/step-by-step-guide-creating-sql-hierarchical-queries-bibhas-mitra 1. Introduction Hierarchical query is a type of SQL query that is commonly leveraged to produce meaningful results from hierarchical data. Hierarchical data is defined as a set of data items that are related to each other by hierarchical relationships. Hierarchical relationships exist where one item of data is the parent of another item. The real-life examples of the hierarchical data that are commonly stored in databases include the following: Employee hierarchy (employee-manager relationship) Organizational hierarchy Graph of links between web pages Connected social networking graph A set of tasks in a project File system Unless your database is a hierarchical database like IBM Information Management System (IMS), you will need to write hierarchical SQL to retrieve relational data in hierarchical format that establishes parent-child relationships between entities ...
http://www.packtpub.com/article/external-tables-oracle-10g-11g-database-part1 In this two-part article by Hector R. Madrid, we will learn about the External Tables in Oracle 10g/11g Database. When working in data warehouse environments, the Extraction—Transformation—Loading (ETL) cycle frequently requires the user to load information from external sources in plain file format, or perform data transfers among Oracle database in a proprietary format. This requires the user to create control files to perform the load. As the format of the source data regularly doesn't fit the one required by the Data Warehouse, a common practice is to create stage tables that load data into the database and create several queries that perform the transformation from this point on, to take the data to its final destination. A better approach, would be to perform this transformation 'on the fly' at load time. That is what External Tables are for. They are basically external files, managed either...
Views Views are known as logical tables. They represent the data of one of more tables. A view derives its data from the tables on which it is based. These tables are called base tables. Views can be based on actual tables or another view also. Whatever DML operations you performed on a view they actually affect the base table of the view. You can treat views same as any other table. You can Query, Insert, Update and delete from views, just as any other table. Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table. The following sections explain how to create, replace, and drop views using SQL commands. Creating Views Suppose we have EMP and DEPT table. To see the empno, ename, sal, deptno, department name and location we have to give a join query like this. select e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc From emp e, dept d where e.deptno=d.deptno; So everytime we want to see emp details and department names wher...
Comments