oracle sql and dba tutorial logo

Tutorial for Oracle DBA


Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions

42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


 

Oracle SQL Tutorial Contents

Introduction to Databases

CODD'S Rules

Datatypes and Create Table

Oracle SELECT Statement

Formatting in SQL*Plus

UNION, INTERSECT, MINUS Operators and Sorting Query Result

 

Oracle ALTER TABLE ADD COLUMN

Oracle ALTER TABLE MODIFY COLUMN

Oracle SQL Functions

Oracle NVL() Function with Examples

Oracle SUBSTR() function with Examples

Oracle TO_DATE() with complete examples

Oracle DECODE function

Oracle INSTR() function with examples

Oracle TO_CHAR() function

Oracle TO_TIMESTAMP

Number Functions (Math Functions)

Character Functions

Miscellaneous Functions

Aggregate Functions

Date and Time Functions

Oracle Join Queries

GROUP BY Queries, SUB Queries

CUBE, ROLLUP Functions

Oracle DML (INSERT, UPDATE, DELETE...)

Oracle DDL (CREATE, ALTER, DROP...)

COMMIT, ROLLBACK,SAVEPOINT

Data Control Language (GRANT, REVOKE)

 

 

Integrity Constraints (PRIMARY KEY, NOT NULL...)

DEFAULT Values

Dropping Constraints

Disabling and Enabling

Differing Constraints Check

View Info about Constraints

Working with Dates

Oracle Views

Oracle Sequences

Oracle Synonyms

Indexes and Clusters

Table Partitioning

Altering Partition Tables

Dropping Partitions

Merging Partitions

Splitting Partitions

Coalescing Partitions

Oracle Objects and Object Types

 

 

 

 

 

 

 

 

 

Join Queries in Oracle

Joins

A join is a query that combines rows from two or more tables, views, or materialized views. Oracle performs a join whenever multiple tables appear in the query's FROM clause. The query's select list can select any columns from any of these tables. If any two of these tables have a column name in common, you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

Join Conditions

Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition. To execute a join, Oracle combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The columns in the join conditions need not also appear in the select list.

Equijoins

An equijoin is a join with a join condition containing an equality operator ( = ). An equijoin combines rows that have equivalent values for the specified columns.

For example the following query returns empno,name,sal,deptno and department name and city from department table.

select emp.empno,emp.ename,emp.sal,emp.deptno,dept.dname,dept.city from emp,dept where emp.deptno=dept.deptno;

The above query can also be written like, using aliases, given below.

select e.empno, e.ename, e.sal, e.deptno, d.dname, d.city from emp e, dept d where emp.deptno=dept.deptno;

The above query can also be written like given below without using  table qualifiers.

select empno,ename,sal,dname,city from emp,dept where emp.deptno=dept.deptno;

And if you want to see all the columns of both tables then the query can be written like this.

select * from emp,dept where emp.deptno=dept.deptno;

Non Equi Joins.

Non equi joins is used to return result from two or more tables where exact join is not possible.

For example we have emp table and salgrade table. The salgrade table contains grade and their low salary and high salary. Suppose you want to find the grade of employees based on their salaries then you can use NON EQUI join.

select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s
     where e.sal between s.lowsal and s.hisal

Self Joins

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition.

For example the following query returns employee names and their manager names for whom they are working.

Select e.empno, e.ename, m.ename  “Manager” from emp e,
      emp m where e.mgrid=m.empno

Inner Join

An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition.

Outer Joins

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

For example the following query returns all the employees and department names and even those department names where no employee is working.

select e.empno,e.ename,e.sal,e.deptno,d.dname,d.city from emp e, dept d
    where e.deptno(+)=d.deptno;

That is specify the (+) sign to the column which is lacking values.

Cartesian Products

If two tables in a join query have no join condition, Oracle returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product.

 

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved