WHAT IS ORACLE 11G?
Oracle 11G is a small/medium level database (RDBMS) which provides efficient and effective solutions for handling database and its data. It is based on Client/Server architecture.
What is SQL?
SQL stands for Structured Query Language which is used to communicate with a database (mainly RDBMS). Operations like create, delete, update, retrieve data from database can be performed.Few RDBMS that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, etc.
Features of SQL:
- It is a Non-Procedural language.
- It is a common language to interact with any type of Database such as Oracle, DB2 etc
- It is not a case sensitive language
Sub-languages of SQL:
It is mainly divided into five types:
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Retrieval Language (DRL)
- Transaction Control Language (TCL)
- Data Control Language (DCL)
Data Types in SQL:
Data types: They tell the operating system that what type of data (char, number, float and so on) is stored in the memory.
- Character Data Type:
Data Type Syntax
|
Oracle 11g (maximum size in bytes)
|
Explanation
|
Frequency Of Use
|
Char(size)
|
2000
|
Stores
characters and has fixed length string. Space padded
|
High
|
Nchar(size)
|
2000
|
Stores
characters and has fixed length NLS string Space padded
|
Low
|
Nvarchar2(size)
|
4000
|
Variable
length string
|
Low
|
Varchar2(size)
|
4000
|
Variable
length NLZ string
|
High
|
Long
|
2GB
|
Variable
length string
|
Low
|
Raw
|
2000
|
Variable
length binary string
|
medium
|
Long raw
|
2GB
|
Variable
length binary string
|
low
|
- Numeric Data Type:
Data Type Syntax
|
Explanation
|
Frequency Of Use
|
Number(size)
|
Stores
numeric values
|
High
|
Float
|
Stores
numeric values decimal values included
|
High
|
Decimal
|
Stores
numeric values decimal values included
|
High
|
Integer
|
Stores
numeric values decimal values included
|
High
|
Real
|
Stores
numeric values decimal values included
|
High
|
Double
precision
|
Stores
numeric values decimal values included
|
medium
|
des
|
Stores
numeric values
|
low
|
Operators in SQL:
Operator: It is a special symbol which performs some operation on operands.
Arithmetic Operators:
Operator
|
Description
|
Method Of Use
|
Example
|
+
|
Addition
|
A+B
|
10+2=12
|
-
|
Subtraction
|
A-B
|
10-2=8
|
*
|
Multiplication
|
A*B
|
10*2=20
|
/
|
Division gives
quotient
|
A/B
|
10/2=5
|
Mod
|
remainder
|
mod(A,B)
|
Mod(15,2)=1
|
Logical Operators: manipulate results of conditions
Operator
|
Description
|
NOT
|
Returns TRUE
if the condition is FALSE and vice versa and if the result is unknown it
returns unknown
|
AND
|
Returns
TRUE if both component conditions are TRUE and FALSE if any one condition is
FALSE
|
OR
|
Returns TRUE
if anyone component conditions are TRUE and FALSE if both condition is FALSE
|
Relational Operators: (Basic Set)
Operator
|
Description
|
=
|
Assignment and
Equal to operator
|
!= , ^= , <>
|
Not Equal
|
>
|
Greater than
|
>
|
Lesser than
|
>=
|
Greater than
or Equal to
|
<=
|
Lesser than
or Equal to
|
Relational Operator:
Operator
|
Description (working can be understood when
solving problems based on it which is in later part)
|
In
|
“Equivalent
to any member of”. Equivalent to”=ANY”
|
Not in
|
Equivalent
to “!=ANY”
|
ANY /
SOME
|
Compares a
value to each value in a list. Must be used with relational operator (basic
set). Evaluates to FALSE if the list returns no rows
|
All
|
Compares a
value to each value in a list. Must be used with relational operator (basic
set). Evaluates to TRUE if the list returns no rows
|
Between /
Not between
[not]Between
x and y
|
[not]Greater
than or equal to x and less than or equal to y
|
Like /
Not like
|
TRUE if x
does [not] match the pattern y
|
Is null /
Is not null
|
Test for
nulls. Only used to test for null
|
Exists /
not exists
|
True if a
sub-query returns at least one row
True if a
sub-query returns no row
|
||
|
Concatenates
character string
|
