Database and SQL

TOP() is a SELECT clause used to limit result rows, not an UPDATE statement feature. In SQL Server, updates with TOP require a subquery or specific syntax, and in Oracle/database systems, TOP doesn't exist for updates at all.

Oracle DATE columns store both date AND time components (down to seconds). The time portion defaults to midnight (00:00:00) if not specified, but it always exists. Use TIMESTAMP for fractional seconds or explicit time handling.

Analytic functions differ from standard aggregate functions because they return a result for each individual row in the query, rather than collapsing the group into a single summary row. Standard aggregate functions with GROUP BY are what return a single row per group.

Oracle has strict context rules for expressions. For example, you cannot use aggregate functions in WHERE clauses (need HAVING), and certain expressions are prohibited in DEFAULT values, CHECK constraints, or some DDL statements. Expression validity depends on the SQL clause context.

Scalar functions like NVL, COALESCE, NVL2, and DECODE specifically handle null arguments without returning null. They're designed to substitute or evaluate based on null presence. Only functions like LENGTH, SUBSTR, or CONCAT return null when input is null.

ROWID is a physical address that changes if the row is exported/imported, moved due to table reorganization, or in certain partition operations. Using it as a primary key breaks data integrity. Use surrogate keys (sequences) or natural keys instead.

VARCHAR datatype specifies variable-length character strings where storage equals actual length (up to the max). Unlike CHAR which pads with spaces, VARCHAR only stores what you insert. This is fundamental to SQL character data types.

Oracle optimizer hints are embedded in SQL comments using the format /+ hint_name */ or /+ hint_name(table_alias) */. These comments are specially parsed by the optimizer to influence execution plans while remaining valid SQL comments.

Oracle treats empty strings ('') as NULL, unlike many other databases. This means '' = NULL evaluates to TRUE (unknown), and empty strings require NVL or COALESCE handling. This is a critical Oracle-specific behavior for string operations.

In hierarchical queries (CONNECT BY), Oracle processes CONNECT BY first to build the hierarchy, then applies WHERE to filter the results. WHERE operates on the hierarchy tree after it's constructed, not before. This order matters for filtering parent-child relationships.

In Oracle SQL, all set operators (UNION, UNION ALL, INTERSECT, and MINUS) share the same level of precedence. Consequently, Oracle evaluates them in order from left to right as they appear in the query, unless parentheses are explicitly used to specify a different evaluation order.

Basic SELECT syntax is SELECT [select_list] FROM [table]. The select list (columns or expressions) immediately follows SELECT keyword and precedes FROM. This is fundamental SQL structure and never varies.

UNION ALL combines result sets without removing duplicates. UNION (without ALL) performs a sort and deduplication operation, which is slower. Use UNION ALL when you know rows are unique or specifically want duplicates retained.

In Oracle PL/SQL and most major RDBMS systems, there is no ALTER TRIGGER command. To modify a trigger, you must DROP it and then CREATE it again with the new definition. This is a two-step process.

PL/SQL indeed stands for Procedural Language extensions to SQL (or Procedural Language extension of SQL). It is Oracle's procedural programming language that extends SQL with programming constructs like variables, conditions, loops, and exceptions.