Oracle Data types:
Data types and its description:
Varchar2(size) Variable length character data
Char(size) Fixed length character data
Number(p,s) Variable length numeric data
date Date and time values
long Variable length character data
Raw and long Raw binary data
raw
Blob Maximum size is (4 gigabytes -1) (DB_BLOCK_SIZE
initialization parameter (8 TB to 128 TB
Clob Maximum size is (4 gigabytes-1) (DB_BLOCK_SIZE).
Bfile Binary data stored in an external file (up to 4 GB)
Rowid A base 64 number system representing the unique address
of a row in its table.
NULL Values:
Null means unknown or non-existent.
Examples:
DESCRIBE Command
Provides a description of the specified table.
Returns column names, nullable or not, and data types.
Can be used with the DESC or DESCRIBE keywords.
Syntax:
DESC[RIBE] table_name;
Example:
INFORMATION Command:
Provides a more detailed description of the specified table.
Returns column names, data types, nullable or not, default, low and high
values, the number of distinct values, histograms, table comments,
indexes, references etc.
Can be used with INFO or INFORMATION keywords.
Syntax:
INFO[RMATION] table_name;
Example:
SQL Statement Basics:
SQL statements are not case-sensitive.
SQL statements can be separated into multiple lines.
Keywords cannot be abbreviated or split.
In SQL Developer, SQL statements can be terminated by a semicolon ","
or a forward slash "/" sign.
In SQL*Plus, you have to write semicolons at the end of each SQL
statements.
There need to be at least one space between the commands
SELECT Statement:
SELECT statement is used to retrieve data from the database.
"*" retrieves all data without knowing table metadata.
We can retrieve some specific columns by writing the
column names.
Syntax :
SELECT {column_name1,column_name2,...} FROM table;
Select * from employees;
Examples:
Using Coloumn Aliases:
Renames a column heading.
AS keyword is used to increase readability.
AS keyword is useful for calculations.
Aliases are used with the double quotation marks if our alias
name contains space characters, special characters or it is used
for handling case-sensitivity.
Syntax:
select first_name as Name from employees;
Examples:
Using Quote operator:
The quotation mark is used to increase readability and
usability.
You can use any character as quotation mark delimiter.
[ ] , { }, ( }, < > , or even any character like 'A', '*'...
Usually, [ ] is used as quotation mark delimiter
Syntax:
1. SELECT q'[My Name is Steven and my friend's name is Neena]' my_text FROM
dual;
2. SELECT q'* My name is Asterisk*' text1, Q'bMy name is textb' text2 FROM
dual;
Examples:
DISTINCT & UNIQUE Operator:
They are used to eliminate the duplicate rows.
Syntax:
SELECT DISTINCT JOB_ID FROM EMPLOYEES;
Examples:
Concatenation Operators:
Concatenates two or more strings or columns and returns asa
single output column value. Can be done by two vertical bars
( || )
Concatenating with NULL value does not return NULL,
it returns the other character strings.
Syntax:
I SELECT first_name || ‘ ’|| last- name "Names" FROM employees;
Examples:
Arithmetic Expressions:
Arithmetic expressions are used to perform arithmetic
operations in SQL.
An arithmetic expression can contain
column names, numeric numbers, and
arithmetic operators.
Operators Description
+ Addition
- Subtraction
* Multiplication
Syntax:
SELECT employee_id, salary, salary+ 50*12 "Annual Salary" FROM employees;
Examples:
Arithmetic Expressions and NULL Values:
Arithmetic operations with date values return new date values.
Arithmetic operations with the NULL values eturn NULL.
Syntax:
Select employee_id, salary * commision_pct from employees;
Examples: