Oracle Database and SQL Fundamentals

In database terminology, horizontal rows are called 'records' (each row represents one complete entry), and vertical columns are called 'fields' (each column represents a specific attribute or property). Records contain the actual data values, while fields define what type of data each column holds.

MAX is an aggregate function that works on any datatype - it finds the maximum value in a set regardless of whether the values are numbers, strings, or dates. TO_CHAR and TO_DATE are type conversion functions that require specific input types, while LPAD is a string manipulation function.

Normalization is the process of organizing database tables to reduce redundancy and improve data integrity by splitting large tables into smaller, related ones. It eliminates duplicate data and ensures dependencies are logical, not based on arbitrary rules.

CUBE is an SQL extension that produces n-dimensional cross-tabulation by creating subtotals for all possible combinations of grouping columns. Rollup creates hierarchical subtotals, while rollback and commit are transaction control commands.

To match strings ending with 'abc', use '%abc' where % matches any characters (including none) before abc, and abc is the literal ending. 'abc%' would match strings starting with abc, while '%abc_' requires at least one character after abc.

In data warehousing and SQL, the CUBE operator is used to generate a result set that contains multidimensional cross-tabulations, representing all possible combinations of grouping columns (n-dimensional cross-tabulation).

CUBE operation generates all combinations (unions of rollups) across multiple dimensions - it's essentially a combination of multiple ROLLUP operations. Rollback and commit are transaction controls, not aggregation commands.

In Oracle, LONG is a legacy datatype with restrictions - you cannot create indexes on LONG columns. NUMBER, VARCHAR2, and RAW all support indexing. LONG has been deprecated in favor of CLOB. This is a well-known limitation of the LONG datatype.

In PL/SQL, the POSITIVE subtype restricts values to integers starting at 1. NATURAL restricts values to non-negative integers starting at 0. SIGNTYPE restricts values to -1, 0, or 1. Thus, their minimum values are 1, 0, and -1 respectively.

In PL/SQL, PLS_INTEGER is indeed a subtype of BINARY_INTEGER. They're both integer types with similar performance characteristics, used for mathematical operations. NUMBER is not a subtype of INTEGER - they're separate types. POSITIVE is a predefined subtype, not defined in SYS package.

In PL/SQL, VARRAYs (variable-size arrays) are defined with a maximum limit and hold a fixed maximum number of elements. Associative arrays are known as index-by tables, and nested tables do not persist in database columns without a storage table.

In PL/SQL, NATURAL, POSITIVE, and SIGNTYPE are predefined subtypes of the NUMBER (or BINARY_INTEGER) data type. NATURALN is not a valid predefined PL/SQL subtype.

Pseudocolumns behave like columns but aren't actual table columns. ROWNUM assigns a sequential number to each row returned. LEVEL indicates the depth in hierarchical queries. CURRVAL returns the current value of a sequence. COUNT is an aggregate function that calculates row counts, not a pseudocolumn. Oracle uses these special values for metadata and query control.

The ORDER BY clause allows sorting by the column's position (index) in the SELECT list. For example, 'ORDER BY 2' sorts by the second column selected. While this practice is often discouraged in production code for readability, it is syntactically valid.

NVL, NULLIF, and COALESCE are standard SQL functions designed specifically to handle NULL values. While DECODE can handle NULLs in its logic, it is primarily a conditional transformation function rather than a dedicated NULL-handling function like the others.

In Oracle SQL, the CASCADE CONSTRAINTS clause is used with ALTER TABLE DROP COLUMN to automatically drop any referential integrity constraints (like foreign keys) that refer to the column being dropped. This prevents errors when removing columns that are part of a relationship.

TABLE, SEQUENCE, and INDEX are database objects in Oracle and other RDBMS. TABLE stores data, SEQUENCE generates unique numbers, and INDEX improves query performance. VARCHAR2 is a data type used to define columns, not a database object itself.

In SQL databases, DDL (Data Definition Language) commands like RENAME typically cause an automatic commit of the current transaction. DML commands like INSERT and DELETE don't auto-commit unless autocommit mode is enabled. CONNECT establishes a session but doesn't commit anything.