Handling multiple Tables

We will learn Working with multiple Tables which covers : Joins - Inner Join; Using Table Aliases; Outer Join; Self Join; Non-Equi Join Subqueries - Simple Subqueries; Multilevel Subqueries; Correlated Subqueries; PredicatesSet Operators - Set Operator Examples

Aliases:An alias in oracle is another name used for a column or a table inside SELECT query. They are accessible only inside the query where they are created. Once the query execution is over alias will perish. An alias can be a single word or can be more than one word. If alias used is more than one word it must be enclosed in double quotes

example:current_date or "current date"Student_name or "Student name"Aliases are used in places when ever there is multiple usage of longer table names. example :STUDENT_MASTER_RECORD can be aliased as SMR ACCOUNT_MASTER_TABLE as AMT .
There are 2 types of aliases i) Table alias : It is another name used to refer the table within the query.example :SQL> SELECT ENAME , SAL FROM EMP E WHERE E.DEPTNO = 10;In the above query "E" is an alias for EMP.
ii) column aliasSQL> SELECT SYSDATE "Current Date" FROM DUAL;"Current Date" is an alias.


Working Wih Multiple Tables
To retrieve desired data from more than one table we use join.We will have more than one table name after FROM key word in SELECT statement. A SELECT statement on multiple tables without a proper JOIN condition will lead to a cartesian product.(i.e. No.Of Output rows = No.of rows in table1 X No of rows in table2....)
No of join conditions = No of tables joined - 1
Types Of Joins:

1) EQUI JOIN: The equi join is normally used to join tables with primary key foreign key relation ships.
Example :SQL> SELECT ENAME,JOB,DEPTNO FROM EMP;

ENAME JOB DEPTNO
---------- --------- ----------
SMITH CLERK 20
ALLEN SALESMAN 30
WARD SALESMAN 30
JONES MANAGER 20
MARTIN SALESMAN 30
BLAKE MANAGER 30
CLARK MANAGER 10
SCOTT ANALYST 20
KING PRESIDENT 10
TURNER SALESMAN 30
ADAMS CLERK 20
JAMES CLERK 30
FORD ANALYST 20
MILLER CLERK 10

SQL> SELECT DEPTNO,DNAME,LOC FROM DEPT;

DEPTNO DNAME LOC
---- ----------- -------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

To display ENAME of EMP table ,DNAME of DEPT table we need to join those tables as shown below.

SQL> SELECT E.DEPTNO , ENAME , DNAME FROM EMP E, DEPT DWHERE E.DEPTNO=D.DEPTNO;

DEPTNO ENAME DNAME
-------- ---------- --------------
10 CLARK ACCOUNTING
10 KING ACCOUNTING
10 MILLER ACCOUNTING
20 SMITH RESEARCH
20 ADAMS RESEARCH
20 FORD RESEARCH
20 SCOTT RESEARCH
20 JONES RESEARCH
30 ALLEN SALES
30 BLAKE SALES
30 MARTIN SALES
30 JAMES SALES
30 TURNER SALES
30 WARD SALES

In the above query "E" and "D" are the alias names for the tables EMPand DEPT respectively. As column DEPTNO is there in both the tables, tablename is used along with column name DEPTNO. This is to avoid the ambiguity

2) NON-EQUI JOIN :A join condition where any relation operator other than "=" equal to operator is used.
Consider the following examples Using SALGRADE and EMP tables.

SQL> SELECT * FROM SALGRADE;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

SQL> SELECT ENAME,SAL, GRADE FROM EMP E,SALGRADE SWHERE SAL BETWEEN LOSAL AND HISAL
ENAME SAL GRADE
---------- ---------- ----------
SMITH 800 1
JAMES 950 1
ADAMS 1100 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
TURNER 1500 3
ALLEN 1600 3
CLARK 2450 4
BLAKE 2850 4
JONES 2975 4
SCOTT 3000 4
FORD 3000 4
KING 5000 5

3) OUTER JOIN :In EQUI JOIN rows that does not satisfy specified condition would not be displayed. Example: DEPTNO 40 is not displayed in the previous examplebecause there are no employees in it. If we want to diplay its detail also then we have to use OUTER JOIN.Otherwise OUTER JOIN is imilar to EQUI JOIN except for the difference it uses outer join (+) operator. (A plus within parenthesis) towards the side not having required data. Outer join operator will substitute null values when there are no values available.

SQL> SELECT E.DEPTNO,ENAME,DNAME FROM EMP E , DEPT D 2 WHERE E.DEPTNO (+) = D.DEPTNO;

DEPTNO ENAME DNAME
---------- ---------- --------------
10 CLARK ACCOUNTING
10 KING ACCOUNTING
10 MILLER ACCOUNTING
20 SMITH RESEARCH
20 ADAMS RESEARCH
20 FORD RESEARCH
20 SCOTT RESEARCH
20 JONES RESEARCH
30 ALLEN SALES
30 BLAKE SALES
30 MARTIN SALES
30 JAMES SALES
30 TURNER SALES
30 WARD SALESOPERATIONS

SQL> SELECT D.DEPTNO , NVL(ENAME, ' No employee') , DNAME FROM EMP E,DEPT DWHERE D.DEPTNO= E.DEPTNO(+);
DEPTNO NVL(ENAME,'N DNAME
------- ------------ --------------
10 CLARK ACCOUNTING
10 KING ACCOUNTING
10 MILLER ACCOUNTING
20 SMITH RESEARCH
20 ADAMS RESEARCH
20 FORD RESEARCH
20 SCOTT RESEARCH
20 JONES RESEARCH
30 ALLEN SALES
30 BLAKE SALES
30 MARTIN SALES
30 JAMES SALES
30 TURNER SALES
30 WARD SALES
40 No employee OPERATIONS

4) SELF JOIN:When we join a table to itself it is called self join.To join atable itself means that each row of the table is combined with itselfand with every other row of the table. The self join can be seen as join of two copies of the same table.

SQL> SELECT E.ENAME,M.ENAME FROM EMP E,EMP WHERE E.MGR=M.EMPNO;
ENAME ENAME
---------- ----------
SCOTT JONES
FORD JONES
ALLEN BLAKE
WARD BLAKE
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
MILLER CLARK
ADAMS SCOTT
JONES KING
CLARK KING
BLAKE KING
SMITH FORD
Above query uses EMP table to display names of the employees and their managers
SQL> SELECT E.ENAME,NVL(M.ENAME,'He is managed by none') FROM EMP E,EMP MWHERE E.MGR=M.EMPNO(+)

ENAME NVL(M.ENAME,'HEISMANA
---------- ---------------------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
KING He is managed by none

SUBQUERY :A query within another quey. A select statement whose output is substituted in the condition of another select statement .(A query is a statement written for returning specific data). The subquery is executed only once. A subquery is enclosed in parenthesis
Conside the following queries.

1) SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH';
2) SELECT ENAME FROM EMP WHERE DEPTNO= 20

These two queries can be combined as follows.

SQL> SELECT ENAME FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH');

The SELECT statement inside parenthesis is called subquery and the one whichuses the values returned by it as main query.

SQL> SELECT ENAME FROM EMPWHERE JOB=( SELECT JOB FROM EMP WHERE ENAME = 'SCOTT');

ENAME
----------
SCOTT
FORD

CORRELATED QUERY:In a correlated subquery the table used in outer query refers to the table used in the inner query. The correlated subquery is executed repeatedly once for each row of the main query table.

Query to diplay name of highest salary taker.
SQL> SELECT EMPNO, ENAME FROM EMP AWHERE 1 > ( SELECT COUNT(*) FROM EMP BWHERE A.SAL <> SELECT EMPNO, ENAME,SAL FROM EMP AWHERE 1 > ( SELECT COUNT(*) FROM EMP BWHERE A.SAL > B.SAL)

EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800

SET OPERATORS They combine results from two or more queries into one result.Data typeof all selected colums must be of same type.

UNION: It returns rows of first query plus rows of second query but avoids duplicates.(UNION ALL will give duplicates also).

To display designations in department 10 and 20
SQL> SELECT JOB FROM EMP WHERE DEPTNO=10UNION SELECT JOB FROM EMP WHERE DEPTNO=20;

JOB
---------
ANALYSTCLERKMANAGERPRESIDENT

To display designations in department 10,20 and 30
SQL> SELECT JOB FROM EMP WHERE DEPTNO=10UNIONSELECT JOB FROM EMP WHERE DEPTNO=20UNIONSELECT JOB FROM EMP WHERE DEPTNO=30;

JOB
---------
ANALYSTCLERKMANAGERPRESIDENTSALESMAN

Similar output can be produced by writing
SQL> SELECT DISTINCT JOB FROM EMP;

JOB
---------
ANALYSTCLERKMANAGERPRESIDENTSALESMAN

INTERSECT: It returns rows that are common to all queries.
To diplay the designations that are common in DEPTNO 10 and 20 write.
SQL> SELECT JOB FROM EMP WHERE DEPTNO=10INTERSECTSELECT JOB FROM EMP WHERE DEPTNO=20;

JOB
---------
CLERKMANAGER

MINUS : It returns rows unique to the first query.To diplay designations unique in DEPTNO 10 we can write.
SQL> SELECT JOB FROM EMP WHERE DEPTNO=10MINUS SELECT JOB FROM EMP WHERE DEPTNO=20;
JOB---------PRESIDENT
SQL> SELECT JOB FROM EMP WHERE DEPTNO=10MINUSSELECT JOB FROM EMP WHERE DEPTNO=20MINUSSELECT JOB FROM EMP WHERE DEPTNO=30;
JOB---------PRESIDENT

Introducing Oracle for beginners

What is Oracle?
Its a RDBMS.Relational Database Management System.
Oracle - It is a Greek word meaning God- Who fulfils all the Dreams.
Oracle Inc is the world's largest supplier of relational databaseproducts (notably Oracle9i), which are used most of the Fortune 500companies and of course by all the leading E-business and Internetsites.
Oracle's relational database was the world's first to support theStructured Query Language (SQL), now an industry standard.Founder of Oracle Inc: CEO Lawrence J. Ellison and a few associatesformed Oracle in 1977.
What is a RDBMS?
A relational database is a collection of data items organized as a setof formally-described tables from which data can be accessed orreassembled in many different ways without having to reorganize thedatabase tables. The relational database was invented by E. F. Codd at IBM in 1970.The standard user and application program interface to a relationaldatabase is the structured query language (SQL). SQL statements are usedboth for interactive queries for information from a relational databaseand for gathering data for reports.
What Are Codd's Rules?
Dr. E. F. Codd's 12 rules for defining a fully relational database.
Note that based on these rules there is no fully relational databasemanagement system available today. In particular, rules 6, 9, 10, 11 and12 are difficult to satisfy.

• Foundation Rule:A relational database management system must manage its stored datausing only its relational capabilities.
• Information Rule All information in the database should be represented in one and onlyone way - as values in a table.
• Guaranteed Access Rule Each and every datum (atomic value) is guaranteed to be logicallyaccessible by resorting to a combination of table name, primary keyvalue and column name.
• Systematic Treatment of Null Values Null values (distinct from empty character string or a string of blankcharacters and distinct from zero or any other number) are supported inthe fully relational DBMS for representing missing information in asystematic way, independent of data type.
• Dynamic On-line Catalog Based on the Relational Model The database description is represented at the logical level in the sameway as ordinary data, so authorized users can apply the same relationallanguage to its interrogation as they apply to regular data.
• Comprehensive Data Sublanguage Rule A relational system may support several languages and various modes ofterminal use. However, there must be at least one language whosestatements are expressible, per some well-defined syntax, as characterstrings and whose ability to support all of the following iscomprehensible:data definitionview definitiondata manipulation (interactive and by program)integrity constraintsauthorizationtransaction boundaries (begin, commit, and rollback).
• View Updating Rule All views that are theoretically updateable are also updateable by the system.
• High-level Insert, Update, and Delete The capability of handling a base relation or a derived relation as asingle operand applies nor only to the retrieval of data but also to theinsertion, update, and deletion of data.
• Physical Data Independence Application programs and terminal activities remain logically unimpairedwhenever any changes are made in either storage representation or accessmethods.
• Integrity Independence Integrity constraints specific to a particular relational database mustbe definable in the relational data sublanguage and storable in thecatalog, not in the application programs.
• Distribution Independence The data manipulation sublanguage of a relational DBMS must enableapplication programs and terminal activities to remain logicallyunimpaired whether and whenever data are physically centralized ordistributed.
• Nonsubversion Rule If a relational system has or supports a low-level(single-record-at-a-time) language, that low-level language cannot beused to subvert or bypass the integrity rules or constraints expressedin the higher-level (multiple-records-at-a-time) relational language.