Database and SQL

A view is not deletable if its query contains aggregate functions, GROUP BY clauses, or the DISTINCT keyword, as the system cannot map the deletion to specific source table rows. A view must have a FROM clause and can optionally have a WHERE clause without affecting delete support.

Indexes significantly improve SELECT query performance by providing fast lookup paths. However, they impose overhead on DML operations (INSERT, UPDATE, DELETE) because the index must be updated alongside the table data. This is a fundamental trade-off in database design - faster reads come at the cost of slower writes.

In PL/SQL block structure, only the execution section (BEGIN) and its terminator (END) are mandatory. The declaration section (DECLARE) is optional and only needed when declaring variables, cursors, or exceptions. The exception handling section (EXCEPTION) is also optional.

%ROWTYPE attribute declares a variable that inherits the entire row structure from a table or cursor, including all column names and data types. This provides automatic synchronization if the table structure changes.

Within a PL/SQL block, only DML (Data Manipulation Language) commands like SELECT, INSERT, UPDATE, and DELETE are allowed. DDL (Data Definition Language) commands like CREATE and DROP cannot be used directly in PL/SQL - they require dynamic SQL.

Only SELECT statements use the INTO clause to capture returned values. INSERT, UPDATE, and DELETE are DML commands that modify data directly and don't return data to be stored in variables.

PL/SQL iterative control constructs include Cursor For Loop (iterates over cursor rows), Simple Loop (basic LOOP with EXIT), Numeric For Loop (iterates over number range), and While Loop (condition-based iteration). Cursors themselves aren't loops, and Exit Loop isn't a standalone construct.

Simple Loop is called an infinite loop because it has no built-in termination condition. It continues indefinitely until explicitly exited using an EXIT or EXIT WHEN statement. Unlike For Loops (with counter limits) or While Loops (with conditions), Simple Loop relies entirely on manual exit logic.

Implicit cursors are automatically created by PL/SQL for all single-row DML operations (INSERT, UPDATE, DELETE) and single-row SELECT...INTO statements. Multi-row SELECT statements require explicit cursors with loops to process multiple rows.

Explicit cursors are explicitly declared and controlled by the programmer to process multi-row SELECT statements. They provide detailed control over row-by-row processing using OPEN, FETCH, and CLOSE operations, which implicit cursors cannot handle.

The %ROWCOUNT attribute returns the number of rows fetched so far by an open cursor or affected by DML statements. In contrast, %FOUND and %NOTFOUND are boolean attributes indicating whether a row was fetched, and %ISOPEN returns a boolean indicating whether the cursor is open or closed.

The %FOUND cursor attribute evaluates to TRUE if the most recent FETCH statement returned at least one row. %NOTFOUND evaluates to TRUE if no rows were returned, %ROWCOUNT returns the number of rows, and %ISOPEN checks if the cursor is open.

%NOTFOUND evaluates to TRUE when a FETCH operation fails to return a row (no more rows), and FALSE when at least one row is successfully returned. This attribute is commonly used to check if a cursor has exhausted its rows after a fetch operation. %FOUND does the opposite (TRUE when a row is found).

SUBSTR('Oracle Basics', -5, 3) starts 5 characters from the end, giving 'sics' starting from position -5, then extracts 3 characters: 'asi'. Negative positions count from the string's end. So position -5 is 's', -4 is 'i', -3 is 'c', -2 is 's', -1 is the last character. Starting at -5, three characters gives 'asi'.