Introduction to Oracle Database

General

SQL (Structured Query Language) is a 4th generation programming language commonly used for managing relational databases.

Double quotation marks (“”) can be used in SQL to make column names case sensitive.

In the context of databases, a schema is like a folder that contains objects owned by a user account, such as tables, views, and indexes.

1NF (First Normal Form): Ensures that there are no repeating groups within a table, maintaining a 2-dimensional structure.
2NF (Second Normal Form): Requires that each table has a primary key that is not composite.
3NF (Third Normal Form): Eliminates data that does not depend on the primary key, ensuring data integrity.
Boyce-Codd Normal Form: Addresses certain rare logical inconsistencies that may arise in a relational database.
4NF (Fourth Normal Form): Ensures that every multivalued dependency is dependent on a super key.
5NF (Fifth Normal Form): Specifies that every join dependency is a result of the candidate keys in the database.

System level namespace includes USER, ROLES, and PUBLIC SYNONYMS.
Schema level namespace consists of TABLE, VIEW, SEQUENCE, PRIVATE SYNONYMS, and USER DEFINED TYPES.
INDEX and CONSTRAINT fall under the schema level namespace in Oracle databases.

CHAR(n): Fixed-length character data type that retrieves values with spaces padded to the defined length.
VARCHAR2(n): Variable-length character data type.
NUMBER(n, m): Numeric data type where ‘n’ represents precision and ‘m’ represents scale.
DATE: Stores date and time information with formats controlled by NLS_DATE_FORMAT and NLS_TERRITORY settings.
TIMESTAMP(n): Stores date and time with fractional seconds precision ranging from 1 to 9 digits.
TIMESTAMP(n) WITH TIMEZONE: Extends TIMESTAMP data type to include time zone information.
TIMESTAMP(n) WITH LOCAL TIMEZONE: Stores date and time in the local time zone of the database.
INTERVAL YEAR(n) TO MONTH: Represents a period of time in years and months.
INTERVAL DAY(n1) TO SECOND(n2): Represents a period of time in days, hours, minutes, and seconds.
BLOB: Binary Large Object data type for storing large binary data.
CLOB: Character Large Object data type for storing large text data.
NCLOB: National Character Large Object data type for storing Unicode text data.