SrinivasOracle: SQL

Introduction of Oracle:
==============

Oracle was developed by Oracle Corporation systems in 1977.

Oracle consist of RDBMS(Relational Database Management System).

RDBMS:
=====

If you want to know the RDBMS first you should know What is DATA? and what is DATABASE? and what is DATABASE MANAGEMENT SYSTEM?.
DATA: Data is nothing but collection of information any one Business Entity.

Example:Emp Info,Product Info,Dept Info.
----------

DATABSE:
-----------
DATABASE is nothing but is a collection of information of all Business entities stored at one place called DB.
DATABSE MANAGEMENT SYSTEM(DBMS):
------------------------------------------------
A Database combined with management system services called DBMS.
Managent system services:
---------------------------------
1)Adding new data into Database.
2)Changing Existed Data.
3)Deleting Unnecessory Data.
4)Providing Security.
5)Providing Authentication.

RDBMS classified into two types ie…,

--OLTP

--OLAP

OLTP(Online Transaction Processing):

=========================

OLTP is a class of software programs capable of supporting transaction-oriented applications on the internet.
OLTP is like day-to-day transactions.
Example of OLTP is ATM transactions like Balance Enquiry.

OLAP(Online Analytical Processing):
=========================

OLAP enables a user to easily and selectively extract ans view data from different points-of-view.
OLAP is also called as Hierarchical data or Data warehouse.
Example of OLAP is ATM Transactions on mini statement.

RDBMS (Relational Database Management System):
-------------------------------------------------------------
=>RDBMS is a collection of interrelated Data of interrelated Objects.
=>In any RDBMS Data can be stored in the form of two-dimensional tables.
=>A table is a collection of rows and columns.
=>A row is called record.
=>Acolumn is called Field.
Oracle versions:
==========
Oracle 1
Oracle 1.1
.
.
.
Oracle 2
Oracle 2.1
.
.
Business Editions(1986):
-------------------------------
Oracle 7
Oracle 8
Oracle 7 and Oracle 8 both are Standalone RDBMS.It is developed by E.F CODD his contains 12 rules.
Disadvantage of Oracle 7 & 8:
----------------------------------
=>Both are not Browser supported.
Oracle 8i:
-----------
=>In Oracle 8i i means Internet Applications or also called as Web pages.
=>Oracle 8i supports Java pages means Online Data Processing.
Oracle 9i:
-----------
=>Oracle 9i supports Tools for E-Business Development.
=>E-Business means ERP(Enterprise Resource Planning).
=>ERP providing for Oracle Apps.
Oracle 10G:
--------------
=>In Oracle 10g g means Grid technology.
=>Grid is nothing but a mesh like Architecture.Each Database server at operations level is connected with other servers using Grid.
=>In 10g also offering DataWareHouse tools.
Oracle 11G:
--------------
=>In Oracle 11g contains Grid technology and Inbuilt DataWareHouse.
=>And next Latest vesion in Oracle is Oracle 12c.
=>12c c means Cloud Computing.
Features of Oracle:
----------------------
=>It supports Client-Server technology.
=>It supports Parallelism.
=>It supports Data Distribution.
=>Oracle is a Platform Independent means Oracle is installed any OS.
=>It supports Scalability.
=>It supports Compatabilty means Oracle is Compatable with any front-end technolgy.
=>It supports Adaptability means is Adaptable with Future enhancements.

Oracle:
=====

Oracle is combination of Sql and Pl/Sql.

SQL:
===

Structured Query Language consisting set of Predefined commands.
Sql act as an interface between User and Database.
By using SQL user has to write Querries and they can be executed against Database.
Sql contains like:

--Commands

--Concepts

--Operators

--Joins

--SubQueries

--Views

--Synonyms

--Indexes
Features of Sql:
===========
=>Only one Sql query executed at once.
=>Each sql query terminated with Semicolon.
=>Sql is not a Casesensitive.
=>Sql is ANSI(American National Standard Institute)standard.

Logon Procedure:
============
Method 1:
-----------

In Sql * plus:

----------------
It is a client tool available along with Oracle software.
Step 1: First you have to install on Oracle Express Edition and then Double click on sqlplus icon.
Step 2:After it opens logon window like:
Username:...............
Password:................
Host String:................

you have to enter all these values and then click on ok button.
Step 3:Then it opens sql * plus window with prompt like:
Sql>

Method 2:
-----------
=>Connecting to oracle by Navigation like:
Start-->All programs-->Oracle ORADB-HOME-->Application Development-->Select Sqlplusw.-->Then it opens logon window.

=>Connecting to Oracle from Command window like:
Start-->Run-->Cmd-->Sqlplusw.

=>Connecting to Oracle from Run window.
Start-->Run-->Sqlplusw

Logout Procedure:
============
=>In Sql * plus prompt window just we can type exit command logout the Sql * plus.
Sql>exit;

PL/SQL:
=====

PL/SQL is a programming language it contains like:

--Procedures

--Functions

--Triggers

--Cursors

In PL/SQL All these concepts Discussing later in PL/SQL section.

Sql Commands:
==========

=>In RDBMS there are 4 catagories of Languages ie...,

DDL(Data Definition Language ) commands:

- Create

- Alter

- Drop
- Truncate

- Rename

DML(Data Modification Language) commands:

- Insert

- Delete

- Update

DCL(Data Control Language) commands:

- Grant

- Revoke

TCL (Transaction Control Language)commands:

- Rollback

- Savepoint

- Commit

Each and every commands discussing one by one.
User Account Creation:
==================
=>First you have create user account in Database like:
Syntax: Create user <username> identified by <password>;
Example:Create user Srinivas identified by Welcome.

=>Once you create the user we have to give the permissions to that user like follow the given Syntax.
Syntax:Grant resource,connect to <username>
Sql>Grant resource,connect to Srinivas.
=>If you want show the current user in database just you have to follow given syntax.
Sql>Show user;
Note:If you does not give the privileges on User ,we do not access the Database Operations.

DDL Commands:
=============
1)How to CREATE tables:
---------------------------------
Create:It is used to create any Database object like user,table,view,index,procedure,function,package,trigger.
Syntax: Create table <table name>(col1 datatype(size),col2 datatype(size),........colN datatype(size));
Example:Create table emp_info(empid number(10),ename varchar2(20),sal number(10),join_date date);

Naming Conventions:
---------------------------
1)Each name must begin with Alphabetic character.
2)Valid character set a-z,A-Z,0-9,$,@,# and _.
Examples:
Valid Invalid
------------ ---------------
A1 A 1
Emp_sal Emp-sal
P_Cost P.cost
E@Comm E?comm
Pid#1 Pid-1
3)Names are not Case Sensitive.
4)Space is not allowed with in a name.
5)Already Existed names not allowed.
6)Predefined keywords are not allowed.
7)Maximum length of a name 32 characters.

2)How to ALTER tables:
===================
=>ALTER is used to change structure of the table by
a)Adding new column:
-----------------------------
Syntax:ALTER table<table name> ADD <col name> Datatype(size);
Example:Adding new column from existed emp table.
SQL>ALTER table emp ADD Mobile number(10);
b)Adding multiple column:
-----------------------------------
Syntax:ALTER table<table name> ADD (<col1 name> Datatype(size),<col2 name> Datatype(size),....);
Example:Adding multiple column from existed emp table.
SQL>ALTER table emp ADD (Gender char(4),Email varchar2(20));
c)By DROPPING column:
----------------------------------
Syntax:ALTER table<table name>DROP column<col name>;
Example:Delete Gender column from emp table.
SQL>ALTER table emp DROP column Gender;
d)By CHANGING datatype and size of a column:
---------------------------------------------------------------
Syntax:ALTER table <table name> MODIFY <col name> <new datatype><new size>;
Example:Modify the Mobile column number datatype to character datatype from emp table.
SQL>ALTER table emp MODIFY Mobile char(10);
NOTE:To change the datatype of a column should be empty.

3)How to DROP a table:
===================
=>If you want to Delete the any table in Database follow the given Syntax.
Syntax:Drop table <table name>;
Example:Drop table emp_info;
4)TRUNCATE:
--------------------
=>It deletes the entire table data permanently.
Syntax:TRUNCATE table <table name>;
Example:TRUNCATE table emp;

NOTE:we can not cancel the truncate operation by using ROLLBACK.

5)How to RENAME a table:
=====================
=>Rename is used to rename the table name.
Syntax:RENAME <old table name> to<new table name>;
Example:Rename the employee table into Emp_info table.
SQL>RENAME emp to emp_info;

DML Commands:
==============
1)INSERT:
--------------
=>It is used to insert the new data or records into a table.
Syntax:insert into <table name>(col1,col2,..........,colN)values(val1,val2,.......valN);
Example:insert into Prod_dtls
(
Pid,Prod_name,Prod_cost,Man_date,Warranty
)
values
(
'p001','Mobile',20000,'12-Feb-13','1 year'
);

OR
Example:insert into Prod_dtls values
(
'p001','Mobile',20000,'12-Feb-13','1 year'
);

-here column names are optional while inserting 'n' values into 'n' columns.

Note:Char,Varchar2,Date values should be enclosed in SingleQuotes.

=>After creation and insertion of the Prod_dtls table data is like:

   Pid Prod_name Proid_cost Man_date Warranty
-------- ------------- ------------ ------------- -------------
p001 Mobile 20000 12-FEB-13 1 year
p002 laptop   40000 12-FEB-13    2 years

Null values:
---------------
=>A missed value in a column is known as Null value.
=>Null value is not equal to zero or space or Other null value.

Insertion of Null values:
==================
=>While inserting the null values into a table they have 2 methods ie...,
1)Implicit insertion:If we miss a value then NULL is to be placed in the column.
Example:insert into Prod_dtls(Pid,Prod_name,Warranty)values('p005','LCD TV','2 years');

   Pid Prod_name Proid_cost Man_date Warranty
-------- ------------- ------------ ------------- -------------
p001 Mobile 20000   12-FEB-13 1 year
p002 laptop 40000   12-FEB-13 2 years

p003 LCD TV NULL NULL 2 years

2)Explicit insertion:Explicitly we can specify keyword at the place of missed value.

Example:insert into Prod_dtls values(NULL,'LED TV',45000,NULL,NULL);

Pid Prod_name Proid_cost Man_date Warranty
-------- ------------- ------------ ------------- -------------
p001 Mobile 20000 12-FEB-13 1 year
p002 laptop 40000 12-FEB-13 2 years

p003 LCD TV NULL NULL 2 years

NULL LED TV 45000 NULL NULL

2)UPDATE:
-----------------
=>UPDATE is used to update the single or multiple columns values.
a)UPDATE single column values:
-------------------------------------------
Syntax:UPDATE <table name> SET <col name>=value/expression [WHERE <condition>];
Example1:Update all employee commission as 300.
SQL>UPDATE emp SET comm=300;
Example2:Update salesman commission with 10% of their salary.
SQL>UPDATE emp SET comm=(0.10*sal) where job='SALESMAN';
b)UPDATE multiple column values:
----------------------------------------------
Syntax:UPDATE <table name> SET <col1 name>=value/expression,<col2 name>=value/expression,..... [WHERE <condition>];
Example2:Update salesman sal with 20% increment and change their designation as Senior-sales and who is getting atleast 1500 salary.
SQL>UPDATE emp SET sal=(sal+0.20*sal),job='SR_SALES' where job='SALESMAN' AND sal>=1500;

3)DELETE:
----------------
=>DELETE is used to delete records from the table.
Syntax:DELETE from <table name> where <condition>;
Example1:Delete the empno of 7369 from employee table.
SQL>DELETE from emp where empno=7369;
Example2:Delete the expired Products information.
SQL>DELETE from Prod_dtls where exp<sysdate;
NOTE:we can cancel the delete operation by using ROLLBACK.

Datatypes of Oracle:

================

Def:

----

It represents the type of data in a column and assigns memory for each value with in the column.

Different datatypes:

-----------------------

1)Char(size):

---------
=>It stores fixed length Strings.
=>Size is optional.
=>Default size is 1 character.
=>Maximum size is 2000 bytes/characters.
Example:Haltkt_num,Empid,etc...

2)Varchar2(size):
-----------------
=>It stores variable length characters.
=>Size specification must.
=>Maximum size is 4000 bytes/characters.
Example:Emp_name,Cust_name,etc...

3)Number(Precision,Scale):
-----------------------------------
=>It stores numeric data.
=>Precision represents total number of digits with in the value.
=>Scale represents maximum number of digits after decimal point.
=>Maximum precision value 38.
=>Scale is optional and Range of scale is -84 to 127.
Example: Emp_sal(10),Prod_cost(7,3).

4)Date:
---------
=>It stores the date type data and Oracle predefined date format is DD-Mon-YY.

5)Raw(size):
----------------
=>It stores binary data like images,thumb impressions,digital signatures.
=>Maximum size is 2000 bytes(less than 2kb).

6)Long:
----------
=>It is similar to varchar2 datatype.
=>Maximum size is 2GB.
=>Important thing is Only one long column is allowed for a table.

7)Long Raw:
----------------
=>It stores the big strings.
=>Maximum size is 4GB.

8)Lob:
--------
=>LOB means Large Object.
=>It stores higher volumes of data.
=>Maximum size is 4GB.
Types of LOB:Three different types of LOB's ie...,
=>CLOB-Char LOB
=>BLOB-Binary LOB
=>NLOB-Multi char LOB.

Some important Commands:
=====================
Cl scr:This command is used to clear the Screen.
Select * from tab:This is used to display names of tables created in the current schema.
Desc:It means Description of table and it will display properties of the table like column names ,datatypes,sizes.
Ed:It means Editor is used to edit the screen whatever you want to modify the data.

Creation and insertion of table:
========================
Create:
---------
=>Create is used to create any database Object.
Syntax: Create table <table name>(col1 datatype(size),col2 datatype(size),........colN datatype(size));
Example:Create table Prod_dtls
(
Pid char(4),Prod_name varchar2(20),Prod_cost number,Man_date date,Warranty varchar2(10)
);
Insert:
---------
=>It is used to insert the new data or records into a table.
Syntax:insert into <table name>(col1,col2,..........,colN)values(val1,val2,.......valN);
Example:insert into Prod_dtls
(
Pid,Prod_name,Prod_cost,Man_date,Warranty
)
values
(
'p001','Mobile',20000,'12-Feb-13','1 year'
);

OR
Example:insert into Prod_dtls values
(
'p001','Mobile',20000,'12-Feb-13','1 year'
);

-here column names are optional while inserting 'n' values into 'n' columns.

Note:Char,Varchar2,Date values should be enclosed in SingleQuotes.

=>After creation and insertion of the Prod_dtls table data is like:

   Pid Prod_name Proid_cost Man_date Warranty
-------- ------------- ------------ ------------- -------------
p001 Mobile 20000 12-FEB-13 1 year
p002 laptop   40000 12-FEB-13    2 years

Null values:
---------------
=>A missed value in a column is known as Null value.
=>Null value is not equal to zero or space or Other null value.

Insertion of Null values:
==================
=>While inserting the null values into a table they have 2 methods ie...,
1)Implicit insertion:If we miss a value then NULL is to be placed in the column.
Example:insert into Prod_dtls(Pid,Prod_name,Warranty)values('p005','LCD TV','2 years');

   Pid Prod_name Proid_cost Man_date Warranty
-------- ------------- ------------ ------------- -------------
p001 Mobile 20000   12-FEB-13 1 year
p002 laptop 40000   12-FEB-13 2 years

p003 LCD TV NULL NULL 2 years

2)Explicit insertion:Explicitly we can specify keyword at the place of missed value.

Example:insert into Prod_dtls values(NULL,'LED TV',45000,NULL,NULL);

Pid Prod_name Proid_cost Man_date Warranty
-------- ------------- ------------ ------------- -------------
p001 Mobile 20000 12-FEB-13 1 year
p002 laptop 40000 12-FEB-13 2 years

p003 LCD TV NULL NULL 2 years

NULL LED TV 45000 NULL NULL
================================================================
Select Clause:
==========
=>Select command is used to retrieve or fetch the data from the table.
Syntax:Select col1,col2,....,colN from <table name>;
Example1:How to display Employee Names from emp table?
SQL>select ename from emp;
Output:

ENAME
-----------
SMITH
ALLEN
WARD

Example2:How to display Employee Names and Sal from emp table?
SQL>select ename from emp;

Output:

ENAME SAL
----------- --------
SMITH 800
ALLEN 1600
WARD 1250

Example3:How to display Employee Details from emp table?
SQL>select * from emp;
Output:In this whatever the columns and their data are displayed within the emp table.

Maintaining user friendly titles in the output by using SELECT query:
------------------------------------------------------------------------------------------
Syntax:Select col1_name "title",col2_name "title" from <table name>;
Example1:Select ename "Emp_name",sal "Emp_sal" from emp;
Output:
Emp_name Emp_sal
----------- --------
SMITH 800
ALLEN 1600
WARD 1250
Note:In above Example the column names have whatever you give the title name, the same name will be displayed on the column name.

Example2:Select ename Emp_name,sal Emp_sal from emp;
Output:

EMP_NAME EMP_SAL
----------- -----------
SMITH 800
ALLEN 1600
WARD 1250

Note:In above Example the column title names are not mentioned in the Double Quotes,So if the column name will be displayed like UPPERCASE.And don't remember one thing ie...,Don't maintain space within a title.

How to display columns data in user required order:
-------------------------------------------------------------------
Example:Select sal,ename,job from emp;
Output:

SAL ENAME JOB
------ ------------ ---------
800 SMITH CLERK
1600 ALLEN SALESMAN
1250 WARD SALESMAN

=============================================================
DISTINCT /UNIQUE Clause:
======================
=>Distinct/Unique clause will display unique values from the specific columns.
Example:Display different job names of the employee uniquely?
SQL>Select DISTINCT job from emp;
Output:

JOB
-----------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST

Note:Suppose if there are multiple columns are available in the table in that time all columns names are enter bit lazy.So in this situation simple we can write like:
SQL>select DISTINCT e.* from emp e;
==============================================================
ORDER BY Clause:
===============
=>It is used to display column data or table data in sorting order or reverse sorting order.
=>By default it will display Ascending ordered data.
=>To display reverse sorting data we should specify DESC keyword.

Syntax:SELECT col1,col2,.....colN,/ * FROM <table name> ORDER BY col1,col2,..colN[ASC/DESC];

Example1:How to display Employee names in sorting order?
SQL>Select ename from emp ORDER BY ename;

Output:

ENAME
-----------
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING

Example2:How to display Employee salary highest salary to least salary ?
SQL>Select sal from emp ORDER BY sal DESC;

Output:
SAL
------
5000
3000
3000
2975
2850
2450
1600
1500

Example3:How to display Employee Details based on Salary order ?
SQL>Select * from emp ORDER BY sal;

Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- --------- ------- -------- --------------- ----- ------------ -----------

7369 SMITH CLERK 7902 17-DEC-80 800 NULL 20

7900 JAMES CLERK 7698 03-DEC-81 950 NULL 30

7876 ADAMS CLERK 7788 12-JAN-83 1100 NULL 20

Example3:How to display Employee Details like Recent employees to Old employees ?
SQL>Select * from emp ORDER BY Hiredate desc;

Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ----------- ------- --------- ---------------- ------ --------- ------------
7876    ADAMS CLERK   7788 12-JAN-83 1100 NULL 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000   NULL   20
7934 MILLER CLERK   7782 23-JAN-82    1300   NULL 10
7902    FORD ANALYST 7566 03-DEC-81 3000   NULL 20

ORDER BY on multiple columns:
-------------------------------------------
Example:How to display Employee Details on Name,Sal and Hiredate?
SQL>Select * from emp ORDER BY ename,sal,hiredate;

Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- ------- ----------- --------------- ------ --------- ------------

7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

Note:In this case ORDER BY priority goes to ename,if ename is duplicated then ORDER BY priority goes to sal and then ORDER BY priority goes to hiredate.
=================================================================
Operators:
========
1)Arithmetic Operators(+,-,*,/):
----------------------------------------
=>Arithmetic operators are used to perform arithmetic calculations on table data or user data.
a)On user data:
Example1:
SQL>Select 10+10 from dual;
Output:
10+10
--------
20
   Example2:
SQL>Select (0.10*70000)/100 from dual;
Output:
  (0.10*70000)/100
---------------------
70
Note:
------
Dual is a temporary or dummy table and used to perform calculations on user own data.

b)On table data:
Example1:Display Employee price,10% of price as discount and product price after discount?
SQL>Select price,(0.10*price) "Discount" price-(0.10*price) "After Discount" from prod_dtls;
Output:
price       Discount   After Discount
-------------- ------------------- ------------------------------
100 10 90
200 20 180

Example2:Display salary,10% of salary as commission and final salary
SQL>Select sal "Basic salary",(0.10*sal) "Commission",(sal+(0.10*sal)) "Final salary" from emp;

Output:
Basic salary Commission Final salary
--------------- ---------------- --------------
800   80   880
1600   160 1760
1250   125 1375

2975 297.5 3272.5

2)Relational Operators(=,<,<=,>,>=):
=============================
=>Relational operators are used to write conditions.
Where Clause:In this clause we can write conditions within the select query.
Syntax:Select * from <table name> WHERE <condition>;
Example1:Display Salaries below 2000
SQL>Select sal from emp WHERE sal<2000;
Output:

SAL
------
800
1600
1250
1250

1500

Example2:Display Manager details from emp.
SQL>Select * from emp WHERE job='MANAGER';

Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------- ----------- ----------- -------- ---------------- ----- -------- ----------

7566 JONES MANAGER 7839 02-APR-81 2975 NULL 20

7698 BLAKE MANAGER 7839 01-MAY-81 2850 NULL 30

7782 CLARK MANAGER 7839 09-JUN-81 245 NULL 10

3)Special operators:

=================
a)BETWEEN Operator:
-------------------
=>BETWEEN operator is used to specify the range.
Syntax:Select * from <table name> WHERE <column_name>BETWEEN<start name>AND<end name>;
Example1:Display salaries between 1000 and 2000.
SQL>Select sal from emp where sal BETWEEN 1000 AND 2000;
Output:

SAL
------
1600
1250
1250
1500
1100
1300

b)IN Operator:
--------------------
=>IN operator is used specify the list of values.
Syntax:Select * from <table name> where <col_name> IN(val1,val2,.....valN);
Example1:Display Employee details from the jobs are CLERK and SALESMAN?
SQL>Select * from emp where job IN('CLERK','SALESMAN');
Example2:Display Employee details HIREDATE on '17-DEC-80' and '08-SEP-81' ?
SQL>Select * from emp where HIREDATE IN('17-DEC-80','08-SEP-81');

c)IS NULL:
---------------
=>It checks the column value is null or not.
=>It will display output of column value is Null.
Syntax:Select * from emp where <col_name> IS NULL;
Example1:Display Employee information who is not getting comm?
SQL>Select * from emp where comm IS NULL;
Example2:Display Product details having no warranty?
SQL>Select * from Prod_dtls where Warranty IS NULL;

d)LIKE Operator:
------------------------
=>LIKE operator is used to represent the Sequence of characters.
=>It uses two symbols ie...,
_:Represents any one character.
%:Represents any number of characters.
Syntax:Select * from <table name> where <col_name> LIKE 'String';
Example1:Display 4 character length employee names?
SQL>Select ename from emp where ename LIKE'____';
Output:

ENAME
----------
WARD
KING
FORD

Example2:Display employee details who is getting 3 digit salary?
SQL>Select * from emp where sal LIKE'___';
Example3:Display employee name begins with 'A'?
SQL>Select ename from emp where ename LIKE'A%';

Output:

ENAME
----------
ALLEN
ADAMS

Example4:Display employee names begins with 'J' and ends with 'S'?
SQL>Select ename from emp where ename LIKE'J%S';

Output:

ENAME
----------
JONES
JAMES

Example5:Display employee names having the character 'S'?
SQL>Select ename from emp where ename LIKE'%S%';

Output:

ENAME
----------
SMITH
JONES
SCOTT
ADAMS
JAMES

Negation Operator:
-------------------------
=>Negation operators are like:
- <>
- !=
- NOT IN
- NOT BETWEEN
-IS NOT NULL
- NOT LIKE
=>In negation operators just simply we can mentioned NOT keyword before the special operators like NOT IN,NOT BETWEEN,NOT LIKE etc.

Some Examples:
Example1:Display employee details except 'CLERK'?
SQL>Select * from emp where job<>'CLERK';
Example2:Display employee except 'CLERK','SALESMAN','MANAGER'?
SQL>Select * from emp where job NOT IN('CLERK','SALESMAN','MANAGER');
Example3:Display employee details whose salary below 1000 and above 3000?
SQL>Select * from emp where sal NOT BETWEEN 1000 AND 3000;

4)Logical Operators(AND,OR):
=========================
AND:It will display output if all conditions are TRUE.
OR:It Display output if any one condition is TRUE.
Syntax:Select * from <table name> where<cond1>[AND/OR]<cond2>[AND/OR]......;
Example1:Display MANAGER details who is getting above 2500?
SQL>Select *from emp where job='MANAGER' AND sal>2500;

Example2:Display male customers from the cities MUMBAI and CHENNAI and female customers from HYD and DELHI?
SQL>Select *from emp where (gender='MALE' AND city IN('MUMBAI','CHENNAI'))OR(gender='FEMALE' AND city IN('HYD','DELHI'));

=============================================================
SQL Functions(Pre Defined Functions):
==============================
=>Oracle provides a set of pre-defined functions.
=>Used to perform user required costs.
=>These functions are divided into 2 categories.
i)Group/Aggregate Functions:
---------------------------------------
=>It acts on group of values and displaying Single output value.
Example:sum(),max(),avg()etc.
ii)Scalar/Single-row functions:
---------------------------------------
=>It acts on group of values and displaying set of output values.
Example:Replace(),Upper(),Lower()etc.

The above categories are further divided into following types:
a)Numeric functions:
---------------------------
=>It acts on number type data.
b)String functions:
------------------------
=>It acts on character type data.
c)Date functions:
---------------------
=>It acts on date type data.
d)Conversion functions:
-------------------------------
=>It acts on one type data(either numeric or date).And display output in character data.

1)GROUP/AGGREGATE FUNCTIONS:
===============================
=>These functions acts on column data only.
i)SUM(col_name):
------------------------
=>Display sum of values from specified column.
Syntax:Select sum(col_name) from <table name> [WHERE<condition>];
Example1:Display sum of all salaries?
SQL>Select sum(sal) from emp;
Example2:Display total salary from employee working to manager.
SQL>Select sum(sal) from emp where job='MANAGER';
Example3:Display total balance available from SB and SAL accounts?
SQL>Select sum(act_bal) from cust_dtls where Acct_type IN('SB','SAL');
Example4:In the above balance only for last year?
SQL>Select sum(act_bal) from cust_dtls where Act_type IN('SB','SAL')AND Act_open BETWEEN '01-jan-14' AND '31-dec-14';

ii)AVG(col_name):
------------------------
=>This function is used to display the average of values from the specified table column.
Syntax:Select AVG(col_name) from <table name> [WHERE<condition>];
Example1:Find out Average price of all Mobiles manufactured date in last quarters of 2014?
SQL>Select AVG(cost) from Prod_dtls where mfg between '01-OCT-14' AND '31-DEC-14 AND pname='MOBILE';
Example2:Display Average salary of Manager employees>
SQL>Select AVG(sal) from emp where job='MANAGER';

iii)Max(col_name):
-------------------------
=>It will display highest value from specified column.
Syntax:Select MAX(col_name) from <table name> [WHERE<condition>];
Example1:Find out maximum salary among the clerks?
SQL>Select max(sal) from emp where job='CLERK';
Example2:Display highest cost mobile details?
SQL>Select * from Prod_dtls where cost IN(Select max(cost) from Prod_dtls where pname='MOBILE';

iv)Min(col_name):
-------------------------
=>It will display least value from specified column.
Syntax:Select MIN(col_name) from <table name> [WHERE<condition>];
Example1:Find out maximum salary among the clerks?
SQL>Select MIN(sal) from emp where job='CLERK';
Example2:Display highest cost mobile details?
SQL>Select * from Prod_dtls where cost IN(Select MIN(cost) from Prod_dtls where pname='MOBILE';

v)Count(col_name):
-------------------------
=>Display number of values from specified column except NULL values.
Syntax:Select COUNT(col_name) from <table name> [WHERE<condition>];
Example:Find out number of expired products?
SQL>Select COUNT(pid) from Prod_dtls where exp<sysdate;

vi)Count(*):
----------------
=>Display number of records from the table?
Example:Find out number of Demat account opened in current year?
SQL>Select COUNT(*) from cust_dtls where Act_type='Demat' AND Act_open BETWEEN '01-JAN-15' AND '31-DEC-15';

2)SCALAR FUNCTIONS:
====================
a)Numeric functions:
**************************

i)ABS(n):
------------
=>ABSOLUTE is used to Display absolute value of n.
Syntax:Select ABS(<value>) from <table name>;
Example:Select ABS(-23) from Dual;
Output:

ABS(-23)
------------
23

ii)MOD(m,n):
------------------
=>MOD is used to display remainder value after m divides n.
=>It returns m, if n is 0.
Syntax:Select MOD(<m,n>) from <table name>;
Example1:Select MOD(15,3) from dual;

Output:

MOD(15,3)
---------------
0

Example2:Select MOD(15,2) from dual; --O/P:1

iii)POWER(m,n):
----------------------
=>POWER(m,n) is display m power nth value.
Syntax:Select POWER(<m,n>) from <table name>;
Example1:Select POWER(5,2) from dual;

Output:

POWER(5,2)
---------------
25

Example2:Select POWER(100,2) from dual;--O/P:10000

iv)SQRT(n):
----------------
=>SQRT(n) is used to display square root value of n.
Syntax:Select SQRT(n) from <table name>;
Example:Select SQRT(25) from dual;

Output:

SQRT(25)
---------------
5

v)ROUND(m,n):
----------------------
=>Display value of m that is rounded to n number of decimal places.
=>If n is omitted,n is rounded to 0.
=>n can be negative ,and rounds of the digits to left of the decimal point.
=>n must be an integer value.
Syntax:Select ROUND(<m,n>) from <table name>;
Example1:Select ROUND(64.125,2) from dual;

Output:

ROUND(64.125,2)
----------------------
64.13

Example2:Select ROUND(64.125,1) from dual;--O/P:64.1
Example3:Select ROUND(64.125,-1) from dual;--O/P:60
Example3:Select ROUND(64.125) from dual;--O/P:64

vi)TRUNC(m,n):
----------------------
=>Display value m that is truncated to the n decimal places.
=>If n is omitted,n is truncated to 0 decimal places.
=>n can be negative ,to truncate m digits left of the decimal point.
Syntax:Select TRUNC(<m,n>) from <table name>;
Example1:Select TRUNC(64.125,2) from dual;

Output:

TRUNC(64.125,2)
----------------------
64.12

Example2:Select TRUNC(23.637) from dual; --O/P:23

vii)GREATEST(val/expr,val/expr.....):
-------------------------------------------------
=>Display highest value from specified values or expressions.
Syntax:Select GREATEST(<val/expr>) from <table name>;
Example1:Select GREATEST(10,300,(0.10*5000),12/8) from dual;

Output:

GREATEST(10,300,(0.10*5000),12/8)
------------------------------------------------
500

viii)LEAST(val/expr,val/expr.....):
--------------------------------------------
=>Display least value from specified values or expressions.
Syntax:Select LEAST(<val/expr>) from <table name>;
Example1:Select LEAST(10,300,(0.10*5000),12/8) from dual;

Output:

LEAST(10,300,(0.10*5000),12/8)
--------------------------------------------
1.5

ix)FLOOR(n):
-------------------
=>It returns smallest integer value less than or equal to n.
=>If the adjustment is done to the lowest near decimal value.
Syntax:Select FLOOR(<n>) from <table name>;
Example1:Select FLOOR(23.24) from dual;

Output:

FLOOR(23.24)

-------------------

Example2:Select FLOOR(19.1234) from dual; --O/P:19

ix)CEIL(n):
-------------------
=>It returns highest integer value greater than or equal to n.
=>If the adjustment is done to the lowest near decimal value.
Syntax:Select CEIL(<n>) from <table name>;
Example1:Select CEIL(23.24) from dual;

Output:

CEIL(23.24)

-------------------

Example2:Select CEIL(19.1234) from dual; --O/P:20

x)SIGN(n):

--------------

=>It returns the sign,specification of a number.

- if n<0,returns -1.

- if n>0,returns 1.

- if n=0,returns 0.

Syntax:Select SIGN(<n>) from <table name>;

Example1:Select SIGN(-23) from dual;

Output:

SIGN(-23)

-------------------

-1

Example2:Select SIGN(23) from dual; --O/P:1

Example3:Select SIGN(0) from dual; --O/P:0

b)String functions:

***********************

=>All string functions are scalar.

i)ASCII('char'):

---------------------

=>ASCII means American Standard Code for Information Interchange.

=>It display ASCII value of given character.

Syntax:Select ASCII(<n>) from <table name>;

Example1:Select ASCII('@') from dual;

Output:

ASCII('@')

--------------

Example2:Select ASCII('A') from dual; --O/P:65

ii)LOWER('str'/col):

---------------------------

=>Display given string characters or column data as lower case.

Syntax:Select LOWER(<'str'/col> from <table name>;

Example1:Select LOWER('SRINIVAS') from dual;

Output:

LOWER('SRINIVAS')

----------------------------

srinivas

Example2:Select LOWER('ENAME') from emp;

Output:

LOWER('ENAME')

----------------------------

ename

Example2:Select LOWER(ENAME) from emp;

Output:

LOWER(ENAME)

-------------------------

smith

allen

ward

jones

martin

blake

iii)UPPER('str'/col):

---------------------------

=>Display given string characters or column data as upper case.

Syntax:Select UPPER(<'str'/col> from <table name>;

Example1:Select UPPER('srinivas') from dual;

Output:

UPPER('srinivas')

----------------------------

SRINIVAS

Example2:Select UPPER('ename') from emp;

Output:

UPPER('ename')

----------------------------

ENAME

Example2:Select UPPER(ENAME) from emp;

Output:

UPPER(ENAME)

-------------------------

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

iv)INITCAP('str'/col):

----------------------------

=>It returns a string with the first letter of each word in upper case,remaining all same.

Syntax:Select INITCAP(<'str'/col>) from <table name>;

Example1:Select INITCAP('hello world') from dual;

Output:

INITCAP('hello world')

----------------------------

Hello World

Example2:Select INITCAP(ename) from emp;

Output:

INITCAP(ename)

----------------------------

Smith

Allen

Ward

Jones

Martin

v)LENGTH('str'/col):
----------------------------
=>It is used to display number of characters from the given String or column values.
=>If the character is NULL,if return NULL.
Syntax:Select LENGTH(<'str'/col>) from <table name>;
Example1:Select LENGTH('srinivas') length from dual;
Output:

length
--------
8

Example2:Select LENGTH('srinivas') 'length' from dual;
Output:Error
Example3:Select ename,length(ename) 'Length of name' from emp;

Output:

ENAME Length of ename
----------- -------------------------
SMITH 5
ALLEN 5
WARD 4
JONES 5
MARTIN 6
BLAKE 5

vi)SUBSTR(col/expr,m,[n]):
------------------------------------
=>It will display substring from the given string.
=>Substring started with 'm'th character through 'n' number of characters.
- if m is 0,it is treated as 1.
- if m is positive,Oracle counts from the beginning of the character to find the 1st character.
- if n is omitted ,Oracle return all characters to the end of character.
- if n is less than 1 or 0,A null is returned.
Syntax:Select SUBSTR(<col/expr,m,[n]>) from <table name>;
Example1:Select SUBSTR('srinivas',4,5) from dual;
Output:

SUBSTR('srinivas',4,5)
----------------------------
nivas

Example2:Select SUBSTR('WELCOME TO ORACLE',-7) from dual;
Output:

SUBSTR('WELCOME TO ORACLE',-7)

-----------------------------------------------------

ORACLE

Example3:Select ename,job from emp where SUBSTR(job,6)=UPPER('man');

Output:

ENAME JOB

------------ ------------

ALLEN SALESMAN

WARD SALESMAN

MARTIN SALESMAN

TURNER SALESMAN

vii)INSTR(col/expr,'c',[m],[n]):

----------------------------------------

=>It will returns the numeric position of specified character.

=>Searches for column/expression beginning with its 'n'th charater for the 'm'th occurrence of character 'c',and return the position of the character.

=>If m can be positive or negative.

=>If m is positive Oracle searches beginning from the column/expression.

=>If m is negative Oracle searches backward from the column/expression.

=>The default values of m and n are 1.

=>If search is unsuccessful the return value is 0.

Syntax:Select INSTR(<col/expr,'c',m,n>) from <table name>;

Example1:Select INSTR('WELCOME TO ORACLE','A',1,1) from dual;

Output:

INSTR('WELCOME TO ORACLE','A',1,1)

------------------------------------------------------

Example2:Select INSTR('WELCOME TO ORACLE','A',-1,1) from dual;

Output:

INSTR('WELCOME TO ORACLE','A',-1,1)

------------------------------------------------------

Example3:Select INSTR('SRINIVAS','N') from dual; O/P:4

Example4:Select ename,job,INSTR(job,'A',1,2) from emp where job='MANAGER';

Output:

ENAME JOB INSTR(job,'A',1,2)

----------- --------- -----------------------

JONES MANAGER 4

BLAKE MANAGER 4

CLARK MANAGER 4

viii)TRANSLATE('str'/col,source char,target char):
------------------------------------------------------------------
=>It is used to translate character by character in a given string.
Syntax:Select TRANSLATE(<'str'/col,From,To>) from <table name>;
Example1:Select TRANSLATE('WELCOME','COME','DONE') from dual;
Output:

TRANSLATE('WELCOME','COME','DONE')
---------------------------------------------------------
WELDONE

Example2:Select job,TRANSLATE(job,'A','O') from emp where job='SALESMAN';

Output:

JOB TRANSLATE(JOB,'A','O')
------- ----------------------------------
SALESMAN   SOLESMON
SALESMAN   SOLESMON
SALESMAN   SOLESMON
SALESMAN   SOLESMON

ix)REPLACE('str'/col,'source str','target str'):
-----------------------------------------------------------
=>It returns the every occurrence of source string replace by the target string.
=>If the replacement string is omitted or null,all occurrence of source string are eliminated.
Syntax:Select REPLACE(<'str'/col,'source str','target str'>) from <table name>;
Example1:Select REPLACE('SRINIVAS','AS','SA') from dual;

Output:

REPLACE('SRINIVAS','AS','SA')
-------------------------------------------
SRINIVSA

Example2:Select job,REPLACE(job,'P') from emp where job='PRESIDENT';

Output:

JOB REPLACE(job,'P')
------ ------------------------
PRESIDENT   RESIDENT

x)LTRIM('str'/col):
-------------------------
=>It will display given string by eliminating blank spaces from left side of the string.
Syntax:Select LTRIM(<'str'/col>) from <table name>;
Example:Select LTRIM(' WELCOME '),INITCAP('to oracle') from dual;

Output:

LTRIM(' WELCOME ') INITCAP('to oracle')
------------------------------------ ------------------------------
WELCOME   To Oracle

xi)RTRIM('str'/col):
-------------------------
=>It will display given string by eliminating blank spaces from right side of the string.
Syntax:Select RTRIM(<'str'/col>) from <table name>;
Example:Select RTRIM(' WELCOME '),INITCAP('to oracle') from dual;

Output:

RTRIM(' WELCOME ') INITCAP('to oracle')
------------------------------------ ------------------------------
WELCOME To Oracle

xii)TRIM('str'/col):
-------------------------
=>It will display given string by eliminating blank spaces from both side of the string.
Syntax:Select TRIM(<'str'/col>) from <table name>;
Example:Select TRIM(' WELCOME '),INITCAP('to oracle') from dual;

Output:

TRIM(' WELCOME ') INITCAP('to oracle')
------------------------------------ ------------------------------
WELCOME To Oracle

* TRIM keywords:
-------------------------
i)leading 'ch' from 'str'/col:
-----------------------------------
=>Display given string by eliminating similar positions of a character from the beginning of the string.
Example:Select TRIM(LEADING 'X' from 'XX1234YXXX') from dual;

Output:

TRIM(LEADING 'X' from 'XX1234YXXX')
-------------------------------------------------------
1234YXXX

ii)trailing 'ch' from 'str'/col:
-----------------------------------
=>Display given string by eliminating similar positions of a character from the ending of the string.
Example:Select TRIM(TRAILING 'X' from 'XX1234YXXX') from dual;

Output:

TRIM(TRAILING 'X' from 'XX1234YXXX')
-------------------------------------------------------
XX1234Y

iii)BOTH 'ch' from 'str'/col:
-----------------------------------
=>Display given string by eliminating similar positions of a character from the beginning and ending of the string.
Example:Select TRIM(BOTH 'X' from 'XX1234YXXX') from dual;

Output:

TRIM(BOTH 'X' from 'XX1234YXXX')
-------------------------------------------------------
1234Y

iv)LPAD('str'/col,n,'ch')
--------------------------------
=>Pads the character value right justified to a total width of n character positions.
=>The default padding character is space.
Syntax:Select LPAD(<'str'/col,n,'ch'>) from <table name>;
Example:Select LPAD('Page 1',12,'*') from dual;

Output:

LPAD('Page 1',12,'*')
--------------------------
******Page 1

v)RPAD('str'/col,n,'ch')
--------------------------------
=>Pads the character value left justified to a total width of n character positions.
=>The default padding character is space.
Syntax:Select RPAD(<'str'/col,n,'ch'>) from <table name>;
Example:Select RPAD('Page 1',12,'*') from dual;

Output:

RPAD('Page 1',12,'*')
--------------------------
Page 1******

CONVERSION FUNCTIONS:
=======================
=>It is used to convert a value from one datatype into another datatype.
=>Datatype conversion in oracle two types.
1)Implicit datatype conversion:
----------------------------------------
=>Implicit data type conversion work according to the convention specified by oracle.
=>CHAR to NUMBER conversion succeed only if character string represent valid number.
=>CHAR to DATE conversion succeed only if the character string represent the default format of DD-MON-YY.
=>The assignments succeeds if the oracle server can convert the datatype of value.
In Assignment Operator:
--------------------------------
Varchar2/Char ==>Number
Varchar2/Char==>Date
Number ==>Varchar2
Date==>Varchar2
2)Explicit Datatype Conversion:
-----------------------------------------
=>SQL provides 3 functions to convert value from one datatype into another.
=>The explicit conversion functions are
- TO_CHAR : To character conversion.
- TO_DATE : To Date conversion.
- TO_NUMBER : To Number conversion.

i)TO_NUMBER('char',num):
--------------------------------------
=>It accepts character value and display the result in number format.
=>Here Character value should contain sequence of digits.
Syntax:Select TO_NUMBER(<'char',num>) from <table name>;
Example 1:Select '$23,000.00',TO_NUMBER('$23,000.00','L99,999.99') from dual;

Output:

'$23,000.00' TO_NUMBER('$23,000.00','L99,999.99')
--------------- -----------------------------------------------------
$23,000.00 23000

Example 2:Select '$23,000.00',TO_NUMBER('$23,000.00','L99,999.99')-1000 from dual;

Output:

'$23,000.00' TO_NUMBER('$23,000.00','L99,999.99')-1000
--------------- -----------------------------------------------------------
$23,000.00 22000

ii)TO_CHAR(Number,fmt):

------------------------------------

=>It accepts numeric data and display it specified character format.

Syntax:Select TO_CHAR(<num,fmt>) from <table name>;

Example 1:Select TO_CHAR('23000','00,000.00') from dual;

Output:

TO_CHAR('23000','00,000.00')

------------------------------------

23,000.00

Example 2:Select sal,TO_CHAR(sal,'00,000.00') from emp;

Output:

SAL TO_CHAR(sal,'00,000.00')

------- -------------------------------

800 00,800.00

1600 01,600.00

1250 01,250.00

2975 02,975.00

1250 01,250.00

2850 02,850.00

iii)TO_CHAR(Date,fmt):

------------------------------------

=>It accepts date value and display the result in character format.

Syntax:Select TO_CHAR(<date,fmt>) from <table name>;

Example 1:Select TO_CHAR(sysdate,'dd/Mon/yy') from dual;

Output:

TO_CHAR(SYSDATE,'dd/Mon/yy')

-------------------------------------------

19/Feb/15

Example 2:Select TO_CHAR(sysdate,'dd Day Mon YYYY') from dual;

Output:

TO_CHAR(sysdate,'dd Day Mon YYYY')

-------------------------------------------

19 Thursday Feb 2015

a)Using Decimal indicator:
----------------------------------
D=>99D99
=>It returns specified position on the decimal character.
=>Default decimal delimiter is period '.'.
=>Only one decimal indicator can be specified in a number format model.
Examples:
SQL>Select 4567,TO_CHAR(4567,'9999D99') FROM dual;

Output:

4567 TO_CHAR(4567,'9999D99')

--------- ---------------------------------

4567 4567.00

SQL>Select 4567,TO_CHAR(4567,'999D99') FROM dual;

Output:

4567 TO_CHAR(4567,'999D99')

--------- ---------------------------------

4567 #######

b)Using Scientific notation indicator:
-----------------------------------------------
EEEE=>9.9EEEE
=>It returns numeric value using scientific notation.
Example:

SQL>Select 4567,TO_CHAR(4567,'9.9EEEE') FROM dual;

Output:

4567 TO_CHAR(4567,'9.9EEEE')

--------- ---------------------------------

4567 4.6E+03

c)Using Group separator:

---------------------------------

G=>9G999

=>It returns specified position of the group separator.

=>Multiple group separators can be specified.

Examples:

SQL>Select 1234567,TO_CHAR(1234567,'99G99G999') FROM dual;

Output:

1234567 TO_CHAR(1234567,'99G99G999')

--------- ----------------------------------------------------

1234567 12,34,567

SQL>Select 1234567,TO_CHAR(1234567,'99G999G999') FROM dual;

Output:

1234567 TO_CHAR(1234567,'99G999G999')

--------- ----------------------------------------------------

1234567 1,234,567

SQL>Select sal,TO_CHAR(sal,'9G999') from emp;

Output:

SAL TO_CHAR(SAL,'9G999')

------- ---------------------------------

800 800

1600 1,600

1250 1,250

2975 2,975

1250 1,250

d)Using Local currency indicator:

--------------------------------------------

L=>L999 OR 999L

=>It returns the specified position on the local currency symbol.

Examples:

SQL>Select 1234,TO_CHAR(1234,'L9999') FROM dual;

Output:

1234 TO_CHAR(1234,'L9999')

------- --------------------------------

1234 $1234

SQL>Select sal,TO_CHAR(sal,'L9999') FROM emp where deptno=20;

Output:

SAL TO_CHAR(SAL,'L9999')

------- --------------------------------

800 $800

2975 $2975

3000 $3000

1100 $1100

3000 $3000

SQL>Select sal,TO_CHAR(sal,'L99G999D99','NLS_CURRENCY=IndRupees')salary FROM emp where deptno=10;

Output:

SAL SALARY

------- ---------------

2450 IndRupees2,450.00

5000 IndRupees5,000.00

1300 IndRupees1,300.00

e)Trailing Minus indicator:
------------------------------------
MI=>9999MI
=>Return negative value with a trailing minus sign '-'.
=>Returns positive value with a trailing Blank.
=>'MI' format should be declared as Trailing argument only.
Examples:

SQL>Select -23000,TO_CHAR(-23000,'L99G999D99MI') FROM dual;

Output:

-23000 TO_CHAR(-23000,'L99G999D99MI')
--------- --------------------------------------------
-23000 $23,000.00-

SQL>Select sal,comm,comm-sal,TO_CHAR(comm-sal,'L99G999D99MI') FROM emp where deptno=30;

Output:

SAL COMM COMM-SAL TO_CHAR(COMM-SAL,'L99G999D99MI')
--------- ---------- -------------- --------------------------------------------------
1600 300 -1300 $1,300.00-
1250 500 -750 $750.00-
1250 1400   150 $150.00
2850 NULL NULL NULL
1500 NULL   NULL NULL
950 NULL   NULL NULL

f)Using negative number indicator:
---------------------------------------------
PR=>9999PR
=>It returns negative number in '<>'.
=>It can appear only as trailing declaration.
Examples:
SQL>Select -23000,TO_CHAR(-23000,'L99G999D99PR') FROM dual;

Output:

-23000 TO_CHAR(-23000,'L99G999D99PR')
-------- --------------------------------------------------
-23000 <$23,000.00>

SQL>Select sal,comm,comm-sal,TO_CHAR(comm-sal,'L99G999D99PR') FROM emp where deptno=30;
Output:

SAL COMM COMM-SAL TO_CHAR(COMM-SAL,'L9999PR')
--------- ---------- -------------- --------------------------------------------------
1600 300 -1300    <$1,300.00>
1250 500 -750    <$750.00>
1250 1400   150    $150.00
2850 NULL NULL NULL
1500 NULL   NULL NULL
950 NULL   NULL NULL

g)Using Roman number indicator:
--------------------------------------------
RN
=>RN returns upper roman number.
=>rn returns lower roman number.
=>The value must be an integer and value between 1 and 3999.
Examples:
SQL>Select 14,TO_CHAR(14,'RN'),TO_CHAR(14,'rn') FROM dual;
Output:

14 TO_CHAR(14,'RN') TO_CHAR(14,'rn')
------- ------------------------ ----------------------
14 XIV xiv

SQL>Select deptno,TO_CHAR(deptno,'RN') from dept group by deptno;
Output:

DEPTNO TO_CHAR(DEPTNO,'RN')
------------ -----------------------------------
30 XXX
20 XX
40 XL
10 X

g)Using Sign indicator:
------------------------------
S=>S99999 OR 99999S
=>It returns negative value with a leading minus sign.
=>Returns positive value with a leading plus sign.
=>Returns negative value with a trailing minus sign.
=>Returns positive value with a trailing plus sign.
=>'S' can appear as First or Last value.
Examples:
SQL>Select 2000,TO_CHAR(2000,'S9999'),TO_CHAR(-2000,'S9999')FROM dual;
Output:

2000 TO_CHAR(2000,'S9999') TO_CHAR(-2000,'S9999')
------- ------------------------------- --------------------------------
2000 +2000 -2000

SQL>Select 2000,TO_CHAR(2000,'9999S'),TO_CHAR(-2000,'9999S')FROM dual;
Output:

2000 TO_CHAR(2000,'9999S') TO_CHAR(-2000,'9999S')
------- ------------------------------- --------------------------------
2000 2000+ 2000-

SQL>Select sal,comm,TO_CHAR(SAL,'S9999'),TO_CHAR(SAL,'9999S')FROM emp WHERE deptno=10;

Output:

SAL COMM TO_CHAR(SAL,'9999S') TO_CHAR(SAL,'9999S')
------- ---------- ------------------------------- --------------------------------
2450 NULL +2450 2450+
5000 NULL +5000 5000+
1300 NULL +1300 1300+

h)Using Hexadecimal indicator:
-----------------------------------------
X=>XXXX
=>It returns the Hexadecimal value of specified number of digits.
=>If a number is not an integer,oracle rounds it to an integer.
=>It accepts only positive values or zero.
Examples:

SQL>Select 2000,TO_CHAR(2000,'XXXX') from dual;
Output:

2000 TO_CHAR(2000,'XXXX')
------- ------------------------------------
2000 7D0

SQL>Select ename,sal,TO_CHAR(sal,'XXXX')HEXDEC   from emp where deptno=10;
Output:

ENAME SAL HEXDEC
---------- ------- ------------------------------------
CLARK 2450 992
KING 5000 1388
MILLER 1300 514

i)Using Group seperator:
--------------------------------
9,999
=>It returns a comma in the specified position.
=>Multiple commas can be specified.
Examples:
SQL>Select 23000,TO_CHAR(23000,'99,999,99') FROM dual;
Output:

23000 TO_CHAR(23000,'99,999,99')
------- -------------------------------------
23000 23,000.00

SQL>Select ename,sal,TO_CHAR(sal,'99,999.99')GRP_SEP from emp where deptno=10;
Ouput:

ENAME SAL GRP_SEP
------------ ------ ----------
CLARK   2450 2,450.00
KING   5000 5,000.00
MILLER   1300 1,300.00

j)Using Decimal indicator:
----------------------------------
99.99
=>It returns a decimal point at the specified position.
=>In Only one period can be specified in a number format model.
Examples:
SQL>Select 50000,TO_CHAR(50000,'L99,999.99') IN_DECIMAL FROM dual;
Output:

50000 IN_DECIMAL
--------- -------------------
50000    $50,000.00

SQL>Select SAL,TO_CHAR(SAL,'L99,999.99') SAL_DECIMAL FROM emp WHERE deptno=10;

Output:

SAL SAL_DECIMAL
--------- -------------------
2450 $2,450.00
5000 $5,000.00
1300 $1,300.00

k)Using Dollar indicator:
--------------------------------
$=>$9999
=>It returns a value with leading dollar sign.
Examples:
SQL>Select 50000,TO_CHAR(50000,'$99,999.99') IN_DOLLAR FROM dual;
Output:

50000 IN_DOLLAR
--------- -------------------
50000 $50,000.00

SQL>Select SAL,TO_CHAR(SAL,'$99,999.99') SAL_DOLLAR FROM emp WHERE deptno=10;

Output:

SAL SAL_DOLLAR
--------- -------------------
2450 $2,450.00
5000 $5,000.00
1300 $1,300.00

l)Using Zero indicator:
-----------------------------
0999 or 9990
=>It returns leading or trailing zero's.
Examples:
SQL>Select 1234,TO_CHAR(1234,'0999999')LEAD_ZEROS FROM dual;
Output:

1234 LEAD_ZEROS
------ --------------------
1234 0001234
SQL>Select 1234,TO_CHAR(1234,'9999990')TRAIL_ZEROS FROM dual;
Output:

1234 TRAIL_ZEROS
------ --------------------
1234 1234

m)Using Digit place marker:
-------------------------------------
9=>9999
=>It returns a value with a specified number of digits with a Leading space when positive or Leading minus when negative.
Examples:
SQL>Select 200,100,TO_CHAR(200-100,'99999'),TO_CHAR(100-200,'99999') FROM dual;
Output:

200 100 TO_CHAR(200-100,'99999') TO_CHAR(100-200,'99999')
---- ------ ----------------------------------- ----------------------------------
200 100 100 -100

SQL>Select 20.24,10,TO_CHAR(20.24-10,'99999'),TO_CHAR(10-20.24,'99999') FROM dual;

Output:

20.24 10 TO_CHAR(20.24-10,'99999') TO_CHAR(10-20.24,'99999')
------ ------ ----------------------------------- ----------------------------------
20.24 10 10 -10

n)Using ISO currency indicator:
-----------------------------------------
C=>C9999
=>It returns a specified position of the ISO currency symbol.
Examples:
SQL>Select 3000,TO_CHAR(3000,'C9999.99') FROM dual;

Output:

3000 TO_CHAR(3000,'C9999.99')

-------- -------------------------------
3000 USD3000.00

SQL>Select ename,sal,TO_CHAR(sal,'C9999.99') FROM emp where deptno=10;

Output:

ENAME SAL TO_CHAR(SAL,'C9999.99')

-------- -------- -------------------------------
CLARK   2450 USD2450.00
KING   5000 USD5000.00
MILLER   1300 USD1300.00

o)AD or A.D/BC or B.C indicator:
--------------------------------------------
=>Indicator AD/BC with or without periods.
Examples:
SQL>Select sysdate,TO_CHAR(Sysdate,'AD') from dual;
Output:

SYSDATE TO_CHAR(SYSDATE,'AD')
------------- -----------------------------------
04-MAR-15   AD

SQL>Select sysdate,TO_CHAR(Sysdate,'A.D'),TO_CHAR(Sysdate,'B.C') from dual;
Output:

SYSDATE TO_CHAR(SYSDATE,'A.D.') TO_CHAR(SYSDATE,'B.C.')
------------- ----------------------------------- ---------------------------------------
04-MAR-15   A.D. A.D.

DATE FUNCTIONS:

================

=>Oracle stores dates in an internal numeric format.

=>The dates in Oracle range from January 1,4712 BC to December 31,9999 AD.

=>The default display and input format for any date is DD-MON-YY.

i)SYSDATE:

-----------------

=>It is a oracle date function,it return current date.

=>SYSDATE is generally selected on DUAL table.

Example:Select SYSDATE from dual;

Output:

SYSDATE

--------------

19-FEB-15

ii)TO_DATE('Date value','Date fmt'):

------------------------------------------------

=>It accepts character format of a date value and display it in Oracle's format.

=>Date fmt is optional.

Syntax:Select TO_DATE(<'Date value','Date fmt'>) from <table name>;

Example 1:Select TO_DATE('09/08/1989','dd/mm/yy') from dual;

Output:

TO_DATE('09/08/1989','dd/mm/yy')

---------------------------------------------

09-AUG-89

Example 1:Select TO_CHAR(TO_DATE('20-AUG-1990'),'DDSP')from dual;

Output:

TO_CHAR(TO_DATE('20-AUG-1990'),'DDSP')

-----------------------------------------------------------

TWENTY

Note:DDSP means Date seperator.Similarly Month and Year seperators are MMSP,YYSP.

iii)ADD_MONTHS(d,n):

-------------------------------

=>Display date value after adding n number of months for specified date.

Syntax:Select ADD_MONTHS(<'date value',n> from <table name>;

Example 1:Select ADD_MONTHS('09-Aug-1989',6) "Adding Months" from dual;

Output:

Adding Months

-------------------

09-FEB-90

Example 1:Select ADD_MONTHS(sysdate,7) "Adding Months" from dual;

Output:

Adding Months

-------------------

19-SEP-15

iv)MONTHS_BETWEEN(d1,d2):

-------------------------------------------

=>Display number of months between specified date.

Syntax:Select MONTHS_BETWEEN(<d1,d2>) from <table name>;

Example 1:Select MONTHS_BETWEEN(sysdate,'14-Oct-12')"Exp" from dual;

Output:

Exp

-------

28.17998506571087216248506571087216248507

Example 2:Select MONTHS_BETWEEN('12-Aug-98','14-Oct-12') from dual;

Output:

MONTHS_BETWEEN('12-Aug-98','14-Oct-12')

-----------------------------------------------------------

-170.064516129032258064516129032258064516

Note:If d1 value is greater than d2 value, the result will be displayed in negative value of months.

Example 3:Display Employee Experience in years?

SQL>Select hiredate,ROUND(MONTHS_BETWEEN(sysdate,hiredate)/12) "Experience in years" from emp;

Output:

HIREDATE Experience in years

--------------- ---------------------------

17-DEC-80 34

20-FEB-81 34

22-FEB-81 34

02-APR-81 34

28-SEP-81 33

01-MAY-81 34

v)LAST_DAY(d):

-----------------------

=>Display date value of last day of the month in the specified date.

Syntax:Select LAST_DAY(<'date'>) from <table name>;

Example 1:Select LAST_DAY('09-Aug-1989') from dual;

Output:

LAST_DAY('09-Aug-1989')

-----------------------------------

31-AUG-89

Example 2:Select LAST_DAY(sysdate) from dual;

Output:

LAST_DAY(sysdate)

-----------------------------------

28-FEB-15

Real time Scenario:
SQL>Select Add_months(Last_day(sysdate),-1)+1 from dual;

Output:

ADD_MONTHS(LAST_DAY(SYSDATE),-1)+1
-------------------------------------------------------------
01-MAR-15

vi)NEXT_DAY(d,'weekDayname'):

----------------------------------------------

=>Display date value of specified week day after given date.

Syntax:Select NEXT_DAY(<d,'weekDayname'> from <table name>;

Example 1:Select NEXT_DAY(sysdate,'Thursday') from dual;

Output:

NEXT_DAY(sysdate,'Thursday')

----------------------------------------

26-FEB-15

Example 2:Select NEXT_DAY(sysdate,'Monday') from dual;

Output:

NEXT_DAY(sysdate,'Thursday')

----------------------------------------

23-FEB-15

JOINS:
======
=>When the data from more than one table in the database by using JOIN.
=>A join is a query that combines rows from two or more tables,views or Materialized views.
=>A join is performed whenever multiple table appear in the queries FROM clause.
Basic examples:
SQL>select ename,job,dname,loc from emp,dept;
SQL>select ename,sal,grade,loc from emp,salgrade,dept;

JOIN Condition:
----------------------
=>Oracle combines pair of rows ,each containing one row from each table,for which the join condition evaluates TRUE.
=>The column in the join condition need not be part of the SELECT list.
=>The WHERE clause of join query can also contains that refers to columns of only one table.
=>If a join involves over two tables then oracle joins the first two based on the condition and then compares the result with the next table ans so on.

TABLE 1========>JOIN 1<======TABLE 2
||
||

RESULT 1======>JOIN 2<=======TABLE 2

RESULT 2

=>The LOB columns can not be specified in the WHERE clause,when the WHERE clause contains any JOINS.

Syntax:

Select table1.col1,table1.col2.......,table2.col1,table2.col2,...... FROM table1,table2,...... WHERE table1.col1=table2.col2.

Guidelines:

---------------

=>When writing a SELECT statement that joins tables,precede the column name with the table name for clarity and enhance database access.

=>If the same column name appears in more than one table,the column must be prefixed with the table name.

=>To joins n tables together,you need a minimum of n-1 join conditions.This rule may not apply if your table has a concatenated primary key,in which case more than one column is required to uniquely identify each row.

i)Equi join or simple joins or inner joins:

-----------------------------------------------------

=>In this based on equality condition tables are joined.

=>Only matching records are displayed.

=>In joining two tables must have at least one common column with same datatype and same values.

Syntax:

SQL>Select col1,col2,....FROM <table1>,<table2>......WHERE <table1>.<common col name> = <table2>.<common col name> And .........

Examples:

SQL>Select emp.ename,emp.job,emp.deptno,dept.deptno,dept.loc from emp,dept where emp.deptno=dept.deptno;

Output:

ENAME JOB EMP_DEPTNO DEPT_DEPTNO LOC
---------- --------- ----------------- ------------------ ---------
SMITH CLERK 20 20 DALLAS
ALLEN SALESMAN 30 30 CHICAGO
WARD SALESMAN 30 30 CHICAGO
JONES MANAGER 20 20 DALLAS
MARTIN SALESMAN 30 30 CHICAGO
BLAKE MANAGER 30 30 CHICAGO
CLARK MANAGER 10 10 NEW YORK
SCOTT ANALYST 20 20 DALLAS
KING PRESIDENT 10 10 NEW YORK
TURNER SALESMAN 30 30 CHICAGO
ADAMS CLERK 20 20 DALLAS
JAMES CLERK 30 30 CHICAGO
FORD ANALYST 20 20 DALLAS
MILLER CLERK 10 10 NEW YORK

SQL>Select empno,ename,emp.deptno,deptno,loc from emp,dept where emp.deptno=dept.deptno and empno=&eno;
Output:
Enter value for eno:7369

EMPNO ENAME DEPTNO LOC
----------- ----------- ----------- -------
7369 SMITH 20 DALLAS

Using table Aliases:
--------------------------
=>Qualifying column names with table names can be very time consuming,particularly if a table columns are very lengthy.
=>You can use table aliases instead of table names.
=>Just as a column alias gives a column another name,a table alias gives a table another name.
=>Table aliases help to keep SQL code smaller,therefore using less memory.

Aliases Guidelines:
-------------------------
=>Tables aliases length up to 30 characters only,but the shorter they are better.
=>If a table alias is used for particular table name in he FROM clause ,then the table alias must be substituted for the table name throughout the select statement.
=>The table alias is valid for the current SELECT statement.

Examples:
SQL> Select e.empno,e.ename,e.job,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno and e.job='MANAGER';

Output:

EMPNO ENAME JOB DEPTNO DNAME

---------- ------------ ------- ----------- -------------

7782 CLARK MANAGER 10 ACCOUNTING

7566 JONES MANAGER 20 RESEARCH

7698 BLAKE MANAGER 30 SALES

SQL>Select e.empno,e.ename,e.job,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname<>'SALES';

Output:

EMPNO ENAME JOB DEPTNO DNAME

---------- ------------ ------- ----------- -------------

7369 SMITH CLERK 20 RESEARCH

7566 JONES MANAGER 20 RESEARCH

7782 CLARK MANAGER 10 ACCOUNTING

7788 SCOTT ANALYST 20 RESEARCH

7839 KING PRESIDENT 10 ACCOUNTING

7876 ADAMS CLERK 20 RESEARCH

7902 FORD ANALYST 20 RESEARCH

7934 MILLER CLERK 10 ACCOUNTING

ii)Non Equi join:

-----------------------

=>A non equi join specifies the relationship between columns belonging to different tables by making use of the relational operations other than =.

=>It is used to join table if value of one column a table falls the range of two column values of other table.

=>It is used BETWEEN operator,is also known as Between join.

Syntax:

Select col1,col2,....FROM <table1>,<table2> WHERE <table1>.<col1> BETWEEN <table2>.<col1> and <table2>.<col2>;

Examples:

SQL>Select distinct e.empno,e.ename,e.sal,s.grade,s.losal,s.hisal from emp e,salgrade s where e.sal between s.losal and s.hisal and e.empno=&eno;

Output:

EMPNO ENAME SAL GRADE LOSAL HISAL

----------- ---------- ------ ------------ ---------- ----------

7900 JAMES 950 1 700 1200

SQL>Select distinct e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where (e.sal>=s.losal and e.sal<=s.hisal) and s.grade=1;

Output:

EMPNO ENAME SAL GRADE

--------- ----------- ------ ------------

7900 JAMES 950 1

7369 SMITH 800 1

7876 ADAMS 1100 1

iii)Self join:

---------------

=>It indicates joining the table to itself.

=>The same table appears twice in the FROM clause and is followed by table aliases.

=>The table aliases must qualify the column names in the join condition.

=>It is used on same table the table must have at least 2 columns with same.

- Datatype

- Value

=>To perform a self join ,oracle combines and returns rows of the table the satisfy the join condition.

Syntax:

SQL>Select columns from Table T1,Table T2 where T1.col1=T2.col2;

Examples:

SQL>Select e.ename "Emp name",m.ename "Manager name" from emp e,emp m where e.mgr=e.empno;
SQL>Select e.ename "Emp name",m.ename "Manager name" from emp e,emp m where e.mgr=e.empno and e.hiredate<m.hiredate;

iv)Cartesian Products:
------------------------------
=>A Cartesian product displays the combination of all rows result.This is done by either omitting the WHERE clause or specifying the CROSS JOIN clause.
=>Cartesian product oracle combines each row of one table with each row of the other.
=>It generates the large number of rows and it is rarely used.
=>A join condition is formed when:

A join condition is omitted.
A join condition is invalid.

=>All rows in the first table are joined to all rows in the second table.
=>To avoid Cartesian product ,always include a valid join condition in WHERE clause.
Examples:
SQL>Select empno,ename,dname,loc from emp,dept;
SQL>Select ename,job,dname,loc from emp,dept where job='MANAGER';

v)Outer joins:
------------------
=>It is used to retrieve the all the rows from one table but matching rows from other table.
=>An outer join extends the result of simple or inner joins.
=>An outer join returns all rows that satisfy the join condition and also those rows from one table for which no rows from the other satisfy the join condition.
=>It is used (+)operator,is called join operator.
=>(+)is used with the table which missing the data.
=>To perform an outer join of a table 'T1' and 'T2' and returns all rows from 'T1',apply the outer join operator(+) to all columns of a table 'T2'.
=>For all rows in 'T1' that have no matching rows in 'T2' oracle returns null for any select list expressions containing column of 'T2'.
Syntax:
SQL>Select Table1.column,Table2.column from Table1,Table2 where Table1.column(+)=Table2.column;
SQL>Select Table1.column,Table2.column from Table1,Table2 where Table1.column=Table2.column(+);

Rules and restrictions:
~~~~~~~~~~~~~~~~~~
=>The (+) operator can specified only in the WHERE clause.
=>If 'T1' and 'T2' are joined by multiple join conditions,we must use the (+) operator in all of these conditions.
=>The (+) operator use only with one table.
=>A condition can not use the IN comparison operator to compare a column marked with the (+)operator with an expression.

Examples:
SQL>Select e.ename,e.job,d.deptno,d.loc from emp e,dept d WHERE e.deptno(+)=d.deptno order by e.deptno;

Output:

ENAME JOB DEPTNO LOC
----------- --------- ------------ ------------
CLARK MANAGER   10 NEW YORK
MILLER CLERK   10 NEW YORK
KING PRESIDENT   10 NEW YORK
JONES MANAGER   20   DALLAS
SMITH CLERK   20 DALLAS
SCOTT ANALYST   20 DALLAS
FORD ANALYST   20 DALLAS
ADAMS CLERK   20 DALLAS
WARD SALESMAN   30 CHICAGO
TURNER SALESMAN   30 CHICAGO
ALLEN SALESMAN   30 CHICAGO
JAMES CLERK   30 CHICAGO
MARTIN SALESMAN   30 CHICAGO
BLAKE MANAGER   30 CHICAGO
null null 40 BOSTON

SQL>Select e.ename,e.job,d.deptno,d.loc from emp e,dept d WHERE e.deptno=d.deptno(+) order by e.deptno;
Output:

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

SQL>Select e.ename "Employee",NVL(m.ename,'Supreme Authority') "Manager" FROM emp e,emp m WHERE e.mgr=m.empno(+);

Output:

Employee Manager
------------ ----------------
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   Supreme Authority

vi)Cross join:
------------------
=>Cross join returns a Cartesian product from the two tables.
Example:
SQL>Select empno,ename,dname,loc from emp CROSS JOIN dept WHERE emp.deptno=dept.deptno;
Output:

EMPNO ENAME DNAME LOC
---------- ------------ ----------- ---------
7369 SMITH RESEARCH DALLAS
7499 ALLEN SALES CHICAGO
7521 WARD SALES CHICAGO
7566 JONES RESEARCH   DALLAS
7654 MARTIN SALES CHICAGO
7698 BLAKE SALES CHICAGO
7782 CLARK ACCOUNTING NEW YORK
7788 SCOTT RESEARCH DALLAS
7839 KING ACCOUNTING NEW YORK
7844 TURNER SALES CHICAGO
7876 ADAMS RESEARCH DALLAS
7900 JAMES SALES   CHICAGO
7902 FORD RESEARCH DALLAS
7934 MILLER ACCOUNTING NEW YORK

vii)Natural join:
---------------------
=>Natural join is used to simple join the two tables.
=>Natural join not accept a alias names.
Example:
SQL> Select ename,sal,dname,loc from emp NATURAL JOIN dept;
Output:

ENAME SAL DANME LOC
----------- ------- ------------ --------
SMITH 800 RESEARCH DALLAS
ALLEN 1600 SALES CHICAGO
WARD 1250 SALES CHICAGO
JONES 2975 RESEARCH DALLAS
MARTIN 1250 SALES CHICAGO
BLAKE 2850 SALES CHICAGO
CLARK 2450 ACCOUNTING NEW YORK
SCOTT 3000 RESEARCH DALLAS
KING 5000 ACCOUNTING NEW YORK
TURNER 1500 SALES CHICAGO
ADAMS 1100 RESEARCH DALLAS
JAMES 950   SALES CHICAGO
FORD 3000 RESEARCH   DALLAS
MILLER 1300 ACCOUNTING NEW YORK

Creating joins with the USING clause:
--------------------------------------------------
=>If several columns have the same names but the datatype do not match,the NATURAL JOIN.
=>Clause can be modified with the USING clause to specify the columns that should be used for an Equi join.
=>Use the USING clause to match only one column,when more than one column matches.
=>Do not use table name or alias in the referenced columns.
=>The NATURAL JOIN and USING clauses are mutually exclusive.
Example:
SQL>Select e.ename,d.dname from emp e join dept d USING(deptno) where deptno=20;

Output:

ENAME DNAME
----------- -------------
SMITH   RESEARCH
JONES   RESEARCH
SCOTT RESEARCH
ADAMS   RESEARCH
FORD   RESEARCH

SUB QUERIES:

============
=>A query with in the other query is known as sub query.
=>The query which provides conditional value s to its parent query.
=>A sub query in the WHERE clause of SELECT statement is called as NESTED SUB QUERY.
=>A sub query in the FROM clause of SELECT statement is called as INLINE VIEW.
=>A sub query is a part of a column in the select list.
=>With in the WHERE clause upto 255 sub queries can be nested.:

=>To provide values for condition in the WHERE clause,HAVING clause,START WITH clause of SELECT,UPDATE and DELETE statements.
=>Sub queries improves performance at query level.
=>Sub queries execution process is inner most query to outer most query.
Syntax:
---------
SELECT<select_list> FROM <table name>WHERE <column name/expr>(SELECT <select_list FROM <table name>);

TYPES OF SUB QUERIES:
------------------------------------
i)Single row sub query:
------------------------------
=>These queries returns only one row from the inner SELECT statement.
=>We should use = operator in between inner query and outer query.
=>Single row operators are >,=,>=,<,<>,<=.
ii)Multiple row sub query:
----------------------------------
=>These queries returns more than one column from the inner SELECT statement.
=>We should use IN operator in between inner query and outer query.
=>Multiple row operators are IN,ANY,ALL.

Guidelines for sub queries:
----------------------------------
=>Sub queries are enclosed in parenthesis.
=>Place the sub queries on the right side of the comparison condition.
=>The ORDER BY clause in the sub query is not needed unless you are performing top analysis.
=>Only one ORDER BY clause can be used for SELECT statement.
Sub query with single row:
-----------------------------------
Example 1:Display Employee details working under ACCOUNTING department?
SQL>Select e.* from emp e,dept d WHERE d.dname='ACCOUNTING' AND d.deptno=e.deptno;
OR
SQL>Select e.* from emp e WHERE deptno=(Select deptno from dept where dname='ACCOUNTING');

Example 2:Display Department details of the empno is 7900?

SQL>Select d.* from dept d where deptno=(Select deptno from emp where empno=7900;

Example 3:Display Employee names and salaries working under sales department having minimum salary 1200?

SQL>Select e.ename,e.sal from emp e where e.sal>=1200 and e.deptno=(Select deptno from dept where dname='SALES');

Example 4:Display Employee details working under RESEARCH and SALES department?

SQL>Select e.* from emp e where deptno IN(Select deptno from dept where dname IN('RESEARCH','SALES'));

Applying GROUP functions in SUB QUERIES:

-------------------------------------------------------------

=>The data from the main query can be displayed by using a Group function is a Sub query.

=>As a group functions returns a single row .The query passes the success state.The inner sub query should not have a GROUP BY clause in this scenario.

Examples:

SQL>Select ename,job,sal from emp where sal>(select avg(sal) from emp);

SQL>Select ename,job,sal from emp where sal<(select variance(sal) from emp);

SQL>Select ename,job,sal from emp where sal=(select max(sal) from emp);

Applying HAVING clause in SUB QUERIES:

----------------------------------------------------------

=>Sub query can also be applied in HAVING clause.

=>Oracle server executes the sub query and the result are returned into the HAVING clause of the main query.

Examples:

SQL>Select job,avg(sal) from emp GROUP BY job HAVING avg(sal)<(Select max(avg(sal)) from emp GROUP BY job);

SQL>Select deptno,min(sal) from emp GROUP BY deptno HAVING min(sal)>(Select min(sal) from emp where deptno=20);

Sub query with Multiple row:
--------------------------------------
=>Sub queries returns more than one row are called Multiple-row Sub queries.
=>The multiple row operator expects one or more values.
=>We use multiple-row operator ,instead of a Single-row Operator,with a multiple-row sub query.
IN : Equal to any member in the list.

ANY/SOME : Compare value to each value returned by the sub query.
ALL : Compare value to every value returned by the sub query.

IN Operator Examples:
SQL>Select ename,sal,job from emp where sal IN(Select max(sal) from emp group by deptno);
SQL>Select ename,job,deptno from emp where job IN(Select job from emp where job='MANAGER');

ANY Operator Examples:
SQL>Select ename,sal,job from emp where sal<ANY(Select sal from emp where deptno in(20,30) and job<>'MANAGER';
SQL>Select ename,sal,job from emp where sal>ANY(Select sal from emp where job='CLERK');

SOME Operator Examples:
SQL>Select ename,sal,job from emp where sal>some(1250,1500);
SQL>Select ename,sal,job from emp where sal>SOME(Select sal from emp where job='CLERK');

ALL Operator Examples:
SQL>Select ename,sal,job from emp where sal>ALL(Select sal from emp where job='CLERK');

CO-RELATED SUB QUERIES:
========================
=>It is another way of performing queries upon the data with a simulation of joins.
Syntax:SELECT <col1,col2,...> FROM <table1><Table_Alias1>WHERE col1 operator(SELECT col1,col2 FROM <table 2><Table_Alias 2>WHERE Table_Alias 1.col Operator Table_Alias 2.col);
Steps Performed:
----------------------
=>In this Parent query processed first.
=>Passes the qualified column value to the sub query WHERE clause.
=>Get a candidate row(fetched by outer query).
=>Execute the inner query using the value of the candidate row.
=>Use the values resulting from the inner query to qualify or disqualify the candidate.
=>Repeat until no candidate row remains.
Exists:
--------
=>Returns true if inner query is success otherwise false.
Example:
SQL>Select ename,sal,deptno from emp where deptno=10 and EXISTS(Select count(*) from emp where deptno=10 and job='ANALYST' group by job having count(*)>3);
Note:In this case Group by clause become as a dummy clause so without using Group by we can use having.
Examples:
SQL>Select ename,sal,deptno from emp where deptno=30 and EXISTS(Select count(*) from emp where deptno=10 and job='SALESMAN' having count(*)>3);

SQL>Select deptno,dname from dept d where NOT EXISTS(Select * from emp e where d.deptno=e.deptno);

SQL>Select s.ename from emp s where EXISTS(Select * from emp t where s.deptno=t.deptno);
SQL>Select s.ename from emp s where NOT EXISTS(Select * from emp t where s.empno=t.mgr);

============================================================

NVL Function:

===========

Def:NVL is used to convert a NULL values into ACTUAL value.

----

syntax:

-------

nvl(expr1,exp2)

==>If expr1 and expr2 are given the actual values NVL function that returns the expr1 value.

==>If expr1 is given the NULL value the NVL function that returns the expr2 value.

Examples:

--------

select nvl(100,200) from dual;--100

select nvl(null,200)from dual;--200

select nvl(100,null)from dual;--100

select nvl(null,null) from dual;--null

select ename,job,nvl(comm,0) from emp;--NVL function converts commision null values into 0.

=================================================================

ü NVL2 Function:

============

Def:If expr1 is not null,NVL2 returns expr2.If expr1 is null NVL2 returns expr3.

----

Note:The datatype of the return value is always the same as the datatype of expr2,unless expr2 is character data.

-----

Syntax:

-------

nvl2(expr1,expr2,expr3)

Examples:

---------

select nvl2(1,2,3) from dual;--returns expr2 value

select nvl2(null,2,3) from dual;--returns expr3 value

select nvl2(1,'srinivas',3) from dual;--returns expr2 value

select nvl2(null,2,'srinivas') from dual;--Error:invalid number

====================================================================

ü NULLIF Function:

===============

Def:
-----
Compares two expressions and returns NULL if there are equal.If there are not equal the function returns expr1 value.

Note:Expr1 and Expr2 datatypes must be same.And is equivalent to CASE expression

-----

Syntax:

-------

nullif(expr1,expr2)

Examples:

-------------

select nullif(100,100)from dual;--returns NULL

select nullif(100,200)from dual;--returns 100

select nullif('srinu',100)from dual;--Error

====================================================================

COALESCE:

==========

Def:It return first non-null expression in the expression list.

---

Note:Expression list datatypes must be same.

----

Syntax:coalesce(expr1,expr2......,expr n)

------

Examples:

------------

select coalesce(100,200,300) from dual;--returns 100

select coalesce(null,200,300) from dual;--returns 200

select coalesce(null,null,300) from dual;--returns 300

select coalesce(null,null,null) from dual;--returns null

select coalesce(null,'srinu',300) from dual;--Error

====================================================================
SQL Constraints:

==============

Def:

----

=>SQL constraints are used to specify rules for the data in a table.

=> If there is any violation between the constraint and the data action, the action is aborted by the constraint.

=>Constraints can be specified when the table is created (inside the CREATE TABLE statement), or after the table is created (inside the ALTER TABLE statement).

Syntax:

---------

CREATE TABLE table_name

(

column_name1 data_type(size) constraint_name,

column_name2 data_type(size) constraint_name,

column_name3 data_type(size) constraint_name,

....

);

Summary:

------------

In SQL, we have the following constraints:

NOT NULL - Indicates that a column cannot store NULL value.

UNIQUE - Ensures that each row for a column must have a unique value.

PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly.

FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table.

CHECK - Ensures that the value in a column meets a specific condition.

DEFAULT - Specifies a default value when specified none for this column.

=====================================================================

ü NOT NULL CONSTRAINT:

=====================

Def:

---

=>The NOT NULL constraint enforces a column to NOT accept NULL values.

=>The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

Example:

----------

=>The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values:

CREATE TABLE PersonsNotNull

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

);

=====================================================================

ü SQL UNIQUE Constraint:

=====================

Def:

---

=>The UNIQUE constraint uniquely identifies each record in a database table.

=>The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

=>A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note:

------

=>you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

SQL UNIQUE Constraint on CREATE TABLE:

--------------------------------------------------------

=>The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created:

Example:

-----------

CREATE TABLE Persons

(

P_Id int NOT NULL UNIQUE,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

);

SQL UNIQUE Constraint on ALTER TABLE:

-----------------------------------------------------

=>To create a UNIQUE constraint on the "P_Id" column when the table is already created, use the following SQL:

ALTER TABLE Persons ADD UNIQUE (P_Id);

=>To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL

Syntax:

----------

ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName);

To DROP a UNIQUE Constraint:

----------------------------------------

=>To drop a UNIQUE constraint, use the following SQL:

ALTER TABLE Persons DROP CONSTRAINT uc_PersonID;

=====================================================================

Primary key:

===========

Def:

---

=>The Primary key constraint uniquely identifies the each record in a database table.

=>Primary key must contain UNIQUE values.

=>Primary key column can not contain NULL values.

=>Primary key is used to eliminate the duplicate values in the database table.

Syntax:

---------

create table tablename(col1 datatype primary key,col2 datatype,.............,colN datatype);

Example:

-----------

Ex1:

---

create table persons(pid number(5) not null primary key,firstname varchar2(20),lastname varchar2(20),address varchar2(50));

Ex2:

---

=>To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Employee

(

E_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT pk_EmployeeID PRIMARY KEY (E_Id,LastName)

);

SQL PRIMARY KEY Constraint on ALTER TABLE:

-------------------------------------------------------------

=>To create a PRIMARY KEY constraint on the "Paticular" column when the table is already created, use the following SQL:

ALTER TABLE Persons ADD PRIMARY KEY (pid);

=>To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Employee ADD CONSTRAINT pk_EmployeeID PRIMARY KEY (E_Id,LastName);

Important note:

--------------------

=>If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).

To DROP a PRIMARY KEY Constraint:

-----------------------------------------------

=>To drop a PRIMARY KEY constraint, use the following SQL:

ALTER TABLE Persons DROP PRIMARY KEY;

(or)

ALTER TABLE Persons DROP CONSTRAINT pk_PersonID;

Practice:

-----------

insert into persons values(2,'sri','ram','banblr');

select *from persons;

delete from persons where pid=2;

alter table persons drop primary key;

alter table persons add primary key(pid);

alter table persons add constraint pk_personid primary key(pid);

insert into Employee values(3,'kiran','ram','banblr','bnglr');

select *from Employee;

alter table Employee drop primary key;

delete from Employee where E_id=1;

ALTER TABLE Employee ADD CONSTRAINT pk_EmployeeID PRIMARY KEY (E_Id,LastName);

=====================================================================

ü SQL FOREIGN KEY Constraint:

==========================

Def:

---

=>A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

=>Ensure the referential integrity of the data in one table to match values in another table.

=>The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

SQL FOREIGN KEY Constraint on CREATE TABLE:

-------------------------------------------------------------

The "Employee" table:

~~~~~~~~~~~~~~~~~~

E_Id LastName FirstName Address City

===== ======== ========= ======== ====

1 k Srinivas Eluru 10 Eluru

2 a Kiran Vizag 10 Vizag

3 d Kishore Rjy 10 Rjy

The "Orders" table:

~~~~~~~~~~~~~~~~

O_Id OrderNo E_Id

==== ======= ====

1 77895 3

2 44678 3

3 22456 2

4 24562 1

Example:

------------

CREATE TABLE Orders

(

O_Id int NOT NULL PRIMARY KEY,

OrderNo int NOT NULL,

E_Id int,constraint fk_OrderId FOREIGN KEY(E_Id) REFERENCES Employee(E_Id)

);

SQL FOREIGN KEY Constraint on ALTER TABLE:

-------------------------------------------------------------

=>To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following SQL:

ALTER table Orders ADD FOREIGN KEY(E_Id) REFERENCES Employee(E_Id);

To DROP a FOREIGN KEY Constraint:

-------------------------------------------------

=>To drop a FOREIGN KEY constraint, use the following SQL:

ALTER TABLE Orders DROP CONSTRAINT fk_OrderId;

=====================================================================

ü SQL CHECK Constraint:

==================

Def:

---

=>The CHECK constraint is used to limit the value range that can be placed in a column.

=>If you define a CHECK constraint on a single column it allows only certain values for this column.

SQL CHECK Constraint on CREATE TABLE:

------------------------------------------------------

=>The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint specifies that the column "P_Id" must only include integers greater than 0.

CREATE TABLE Persons

(

P_Id int NOT NULL CHECK (P_Id>0),

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

);

=>To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

);

SQL CHECK Constraint on ALTER TABLE:

----------------------------------------------------

=>To create a CHECK constraint on the "P_Id" column when the table is already created, use the following SQL:

ALTER TABLE Persons ADD CHECK (P_Id>0);

=>To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes');

To DROP a CHECK Constraint:

--------------------------------------

=>To drop a CHECK constraint, use the following SQL:

ALTER TABLE Persons DROP CONSTRAINT chk_Person;

=====================================================================

ü SQL DEFAULT Constraint:

====================

Def:

---

=>The DEFAULT constraint is used to insert a default value into a column.

SQL DEFAULT Constraint on CREATE TABLE:

--------------------------------------------------------

=>The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255) DEFAULT 'Srinivas'

);

SQL DEFAULT Constraint on ALTER TABLE:

-------------------------------------------------------

=>To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

ALTER TABLE Persons MODIFY City DEFAULT 'Srinivas';

To DROP a DEFAULT Constraint:

------------------------------------------

=>To drop a DEFAULT constraint, use the following SQL:

ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT;

=====================================================================
SQL LOADER:
============

SQL LOADER IN .CSV FILES:

=========================

=>First we want to create csv file by following steps:

1)open the note pad and create .csv file like this:

10,Accounting,Newyork

20,Sales,Bostan

30,Research,Dallas

And then save the file into any directory filename as: anyname.csv and save as type:All files(*.*)

Example:dept.csv

2)Open the Sql * plus environment to create a table like this:

SQL>create table <tablename> as select * from dept where 1=2;

Example:

SQL> create table dept_test as select * from dept where 1=2;

The dept_test structure will be created.

Note:In above statement just create the dept_test table structure from the dept table.(If Dept table is must available in user schema then only you want create like this).

3)Next to create another file in same directory that is called Control file is Prefixed with .ctl i.e like

Syntax:

---------

Load data

Insert

Into table <tablename>

Fields terminated by “,” optionally enclosed by “#”

(col1,col2,…..,coln)

Example:

------------

Load data

Insert

Into table dept_test

Fields terminated by “,” optionally enclosed by “#”

(deptno,dname,loc)

After save this file prefixed with .ctl

Filename as:anyname.ctl

Save as type:All(*.*)

Example:dept.ctl

4)Then open the command prompt type the command like this and then enter:

Syntax:sqlldr username/password data='d:\dept.csv' control='d:\dept.ctl' bad='d:\dept.bad' discard='d:\dept.dsc

Example:sqlldr scott/tiger data='d:\dept.dat' control='d:\dept.ctl' bad='d:\dept.bad' discard='d:\dept.dsc

=>In .dsc means discarded file and .bad means bad file these are automatically created.

5)Finally to execute the above command to insert the records on sql user schema.

Show the data like in SQL *PLUS

SQL>select * from dept_test;

Output:

DEPTNO DNAME LOC

------------- ----------- ---------

10 Accounting Newyork

20 Sales Bostan

30 Research Dallas

SQL LOADER IN FIXED POSITION FILES:

==================================

=>First we want to create .DAT file by following steps:

1)open the note pad and create .dat file like this:

10Accounting Newyork

20Sales Bostan

30Research Dallas

And then save the file into any directory filename as: anyname.dat and save as type:All files(*.*)

Example:dept.dat

2)Open the Sql * plus environment to create a table like this:

SQL>create table <tablename> as select * from dept where 1=2;

Example:

------------

SQL> create table dept_test as select * from dept where 1=2;

The dept_test structure will be created.

Note:In above statement just create the dept_test table structure from the dept table.(If Dept table is must available in user schema then only you want create like this).

3)Next to create another file in same directory that is called Control file is Prefixed with .ctl i.e like

Syntax:

----------

Load data

append

Into table <tablename>

Trailing nullcols

(

Col1 position(from:to) <datatype> external(size),

Col2 position(from:to) <datatype>,

Coln position(from:to) <datatype>

)

Example:

------------

Load data

append

Into table dept_test

Trailing nullcols

(

Deptno position(1:2) integer external(2),

Dname position(3:15) char,

Loc position(16:35) char

)

After save this file prefixed with .ctl

Filename as:anyname.ctl

Save as type:All(*.*)

Example:dept.ctl

4)Then open the command prompt type the command like this and then enter:

Syntax:sqlldr username/password data='d:\dept.dat' control='d:\dept.ctl' bad='d:\dept.bad' discard='d:\dept.dsc

Example:sqlldr scott/tiger data='d:\dept.dat' control='d:\dept.ctl' bad='d:\dept.bad' discard='d:\dept.dsc

=>In .dsc means discarded file and .bad means bad file these are automatically created.

5)Finally to execute the above command to append the records on sql user schema.

Show the data like in SQL *PLUS

SQL>select * from dept_test;

Output:

DEPTNO DNAME LOC

------------ --------- ---------

10 Accounting Newyork

20 Sales Bostan

30 Research Dallas

Learn More Do More...

SrinivasOracle

FB

SQL

2 comments:

Translate