Oracle Database Fundamentals - DDL, Constraints, and Memory Structures

In SQL, the SELECT clause is processed before the ORDER BY clause in the logical query processing phase. Consequently, column aliases defined in the SELECT clause are visible and can be used to sort the result set in the ORDER BY clause.

SQL allows sorting by multiple columns using comma-separated column names in the ORDER BY clause: 'ORDER BY col1, col2, col3'. The practical limit is indeed the number of columns in the table or result set, though very large numbers of sort columns are rare in practice. Multi-column sorting creates a hierarchical sort where results are ordered by the first column, then ties within that column are ordered by the second column, and so on.

SGA stands for System Global Area in Oracle Database architecture. It is a shared memory region allocated when the database instance starts, containing data and control structures for the instance. Other options represent non-existent database terms.

DATAFILEs are the physical operating system files that actually store the data on disk - they are tangible physical components of the database architecture. Logical structures like tables and segments ultimately map to these physical DATAFILEs for storage.

PGA (Program Global Area) is a memory region in Oracle that contains data and control information for a single server process. It is exclusive to that process and is allocated when the process starts, deallocated when it terminates - hence 'Program' not 'Primary' or 'Prime'.

ROWID (pseudo-column for row address), BFILE (external binary file locator), VARCHAR2 (variable-length string), and LONG RAW (legacy binary data) are all valid Oracle data types. SHORT RAW and POINTER are not valid Oracle data types - these are likely fabrications or confusion with other databases.

To create a table, you need CREATE TABLE privilege, CREATE SESSION to connect, and storage area access (tablespace quota). CREATE SYNONYM and CREATE INDEX are separate privileges - a synonym is an alias and indexes are optional database objects that can be created separately.

Oracle data dictionary views have three standard prefixes: USER_ (objects owned by current user), ALL_ (objects accessible to current user), and DBA_ (all objects in database - requires DBA privilege). ORACLE_ and SCHEMA_ are not valid prefixes for data dictionary views.

The ALTER TABLE statement in SQL allows modifications to a table's structure, including modifying column data types (MODIFY), removing columns (DROP), setting defaults (ALTER/MODIFY), and adding columns (ADD). It does not manipulate table records; UPDATE and DELETE are distinct DML operations.

TRUNCATE is a Data Definition Language (DDL) command that quickly deletes all rows by deallocating the table's storage, which cannot be rolled back. DELETE is a Data Manipulation Language (DML) command that removes rows one by one, generating undo logs that allow rollbacks.

A PRIMARY KEY can be defined on single or multiple columns (composite key), automatically creates a UNIQUE index to enforce uniqueness, and implicitly adds NOT NULL constraint because NULL values would violate uniqueness. Options claiming single-column only, NON-UNIQUE index, or NULL constraints are incorrect.

A UNIQUE constraint can be defined on single or multiple columns and automatically creates a unique index to enforce uniqueness. Unlike primary keys, columns with a unique key constraint are permitted to contain NULL values, making the selected options correct.

NOT NULL must be defined as part of the column specification (inline or table-level but with column context). Since NOT NULL prohibits NULL values, it cannot be applied to columns that legitimately hold unknown/missing data - that's the purpose of allowing NULLs.

CHECK constraints must evaluate to a BOOLEAN expression (true/false), apply to every row excluding nulls (nulls don't trigger CHECK constraint violations), and columns in CHECK constraints can accept NULLs unless explicitly restricted by NOT NULL. The constraint only fails when a non-null value violates the condition.

A foreign key establishes a relationship between two tables, ensuring referential integrity. The table containing the foreign key is the dependent or child table, while the referenced table containing the primary or unique key is the parent table, confirming the selected options.

A NOT NULL constraint dictates that a column cannot store null values. Because it is directly tied to the column's definition, it must be added or removed using the MODIFY clause of the ALTER TABLE statement rather than the ADD clause, making the statement true.

Using the ALTER TABLE statement, constraints can be added, dropped, enabled, or disabled, but their internal structure (such as the columns they reference) cannot be directly modified. To modify a constraint's definition, you must drop it and recreate it, validating the statement as true.

An INSERT statement can insert multiple rows at once, such as when using the INSERT INTO ... SELECT syntax or multi-row insert statements. The assertion that only a single record can be inserted per execution is false, making the stored choice correct.