1. Oracle SQL Sub Language,Command Categories and Data Types with Examples.

#SQL Command Categories

Below are the SQL commands which are grouped into four major categories depending on their functionality. They are as follows:

Data Definition Language (DDL)

These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.

Data Manipulation Language (DML)

These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are INSERT, UPDATE, and DELETE.

Transaction Control Language (TCL)

These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.

Data Control Language (DCL)

These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

#Data Types:

The following is a list of datatypes available in Oracle/PLSQL, which includes character, numeric, date/time, LOB and ROWID datatypes.

Char Data Types:

Char(Size): to store. Fixed-length strings. Space padded.Maximum size of 2000 bytes.

Varchar2(size):to store. Variable-length string.Maximum size of 4000 bytes and Maximum size of 32KB in PLSQL.

Long:Variable-length strings. (backward compatible).Maximum size of 2GB.

Raw:Variable-length binary strings.Maximum size of 2000 bytes.

Long Raw:Variable-length binary strings.(backward compatible) Maximum size of 2GB.

Numeric Data Types:

Number(p,s):Where p is the precision and s is the scale.

For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.

Float-

Decimal(p,s):Precision can range from 1 to 38.Where 'p' is the precision and 's' is the scale.

For example, decimal(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.

Integer -

Int -

Small Int -

Real -

Double Precision -

Date/Time Data Types:

Date:A date between Jan 1, 4712 BC and Dec 31, 9999 AD.

Timestamp:Includes year, month, day, hour, minute, and seconds.For example:timestamp(6)

Timestamp With Zone:Includes year, month, day, hour, minute, and seconds; with a time zone displacement value.For example:timestamp(5) with time zone.

Timestamp With Local Time Zone :ncludes year, month, day, hour, minute, and seconds; with a time zone expressed as the session time zone.
Ex:Timestamp(4) with local time zone

Interval Year to Month:Time period stored in years and months.

Interval Day to Second:Time period stored in days, hours, minutes, and seconds.

Large Object (LOB) Data Types:

BFILE:File locators that point to a binary file on the server file system (outside the database).Maximum file size of 232-1 bytes.

BLoB:Stores unstructured binary large objects.Store up to 4GB of binary data.

CLOB:Stores single-byte and multi-byte character data.Store up to 4GB of character data.

NCLOB:Stores unicode data,Store up to 4GB of character text data.

ROWID Data Types:The format of the rowid is: BBBBBBB.RRRR.FFFFF,Fixed-length binary data. Every record in the database has a physical address or rowid.

UROWID(Size):Universal Rowid.Where size is optional.