ORACLE Part 6

WE CAN SAVE ALL OUR WORK BY THE FOLLOWING WAY
SPOOL DIRECTORY_PATH\SHARIQ.TXT;

CLOSE THE SPOOL THIS WAY
SPOOL OFF

WE CAN SAVE LAST QUERY BY THE FOLLOWING WAY
SAVE SHARIQ -- SAVE THIS QUERY IN THE BIN DIRECTORY OF THE ORACLE HOME
TO RETRIEVE THAT QUERY
@SHARIQ

ORACLE Part 5

Defining Columns Alias

A Column Alias is basically nickname of the column heading, It is useful in Calculations.

You can use Column Alias by following ways.
1. Immediately After the Column Name
2. Also use it in Double quotations
3. Never used keywords in Aliases but we can used in double quotations

SELECT ENAME "SELECT" FROM EMP;

--CONCATENATION

/* CONCATENATES COLUMNS OR CHARACTER STRING TO OTHER COLUMNS, REPRESENTED BY TWO PIPE SIGNS (||) */

SELECT ENAME || 'WORK AS' || JOB || 'SINCE' || HIREDATE || 'IN DEPARTMENT' || DEPTNNO FROM EMP;

FOR AVOIDING DUPLICATION IN RETRIEVING DATA
-- DISTINCT KEYWORD USED
-- DISTINCT KEYWORD ALWAYS APPEAR AFTER THE SELECT STATEMENT
SELECT DISTINCT DEPTNO FROM EMP;

ORACLE Part 4

Many Times, NULL appears in our result.
To Avoid NULL, we have to use the following function
If NVL Function used with Character then NVL (Char_Column,'ABC')
If NVL Function used with Number then NVL (Numb_Column,0)

Select ename, sal, comm, sal+comm from emp;
Select ename, sal, comm, sal+comm, sal+nvl(comm,0) from emp;
Select ename, sal, comm, sal+comm, sal+nvl(comm,1000) from emp;

ORACLE Part 3

GUIDELINES FOR WRITING SQL STATEMENTS
1. SQL STATEMENTS ARE NOT CASE SENSATIVE
2. SQL STATEMENTS CAN BE SPLIT INTO MANY LINES

FOR DOING ARITHMETIC EXPRESSION

SELECT 2+3 FROM DEPT;
SELECT 3+3/3*3-3 FROM DUAL;
SELECT ENAME, JOB, SAL , SAL+1000 FROM EMP;
SELECT SAL, SAL/30 FROM EMP;

DUAL IS THE DUMMY TABLE IN THE DATABASE.

ORACLE Part 2

-------------- SQL STATEMENTS ----------------------

Data Retrieval
Select

Data Manipulation Language (DML)
Insert, Update, Delete, Merge

Data Definition Language (DDL)
Create, Alter, Drop, Truncate, Rename

Data Control Language (DCL)
Grant, Revoke

Transaction Control
Commit, Rollback, Savepoint

Data Retrieval (Select Statement) -- Capabilities

1. Projection : Select All or Particular Columns From the table
2. Selection : Select All or Particular Columns From the table including Where Clause
3. Joining : Retrieve Data from more than One Table

To Retrieve all the Data from the Table
Select * from Emp;

To Retrieve particular Data from the Table
Select empno, ename, sal from Emp;

To Retrieve particular Data from the Table including Where Clause
Select empno, ename, sal from Emp where deptno = 10;

You can Edit your previous queries through Oracle SQL* Plus, there you have to use Notepad Functionality for it, For this you have to type.
ED

ORACLE Part 1

SQL STANDS FOR STRUCTURED QUERY LANGUAGE.

There are two ways to start Oracle through RUN.
Run -> sqlplus... You can do all your work through Dos prompt
Run -> sqlplusw... You can do all your work through Oracle Interface

To Describe any Table..
Desc ;

To Check the Current Date of the System.
Select sysdate from Dual;

Dual is the temporary table in the Database by which you can do all your rough working.

To check the User from which you are connecting.

Show User;
or
Select user from Dual;

For Single Line Comments, Use -- This is Single Line Comments
For Multi Line Comments, Use /* This is Multi Line Comments */

To Check how many Objects are Lies in the Schema..

Select * from Tab; -- Tables
Select * from Cat; -- Catalogue

Show Structure of the Table

Desc Emp;
Desc Dept;