Oracle SQL Fundamentals

To select all columns from a table in SQL, use the asterisk (*) wildcard with 'SELECT * FROM table_name'. Option A '[all]' is invalid SQL syntax. Option B 'SELECT Persons' would cause an error - it's interpreted as selecting a column named 'Persons', not the table. Option C 'SELECT *.Persons' is invalid syntax. Option D correctly uses the standard SQL wildcard syntax.

iSQLPlus is Oracle's browser-based interface for executing SQL statements (B), accessed via web browser (D). Option A is false because iSQLPlus commands don't directly manipulate table definitions - that's done with SQL DDL. Option C is false because iSQLPlus commands CAN be abbreviated (like DESCRIBE to DESC). The key distinction is that iSQLPlus is the environment/browser interface, while SQL commands interact with the database.

To view a view's definition in Oracle, query USER_VIEWS data dictionary view, which stores the SELECT statement for each view. The TEXT column contains the view definition. DESCRIBE only shows column structure, not the SELECT statement. DEFINE VIEW is not a valid command. USER_SOURCE contains stored procedure/function code, not view definitions.

DESCRIBE is an SQLPlus command that displays column structure of database objects. INSERT, UPDATE, SELECT are SQL commands (DML/DDL), not SQLPlus commands. SQLPlus commands are environment commands like DESCRIBE, SET, SHOW, ACCEPT that control the SQLPlus session, not data manipulation.

This SQL performs selection (WHERE clause filters rows), projection (SELECT clause picks specific columns), and join (WHERE clause connects EMP and DEPARTMENT tables). Intersection and Difference are set operations not shown here. The query filters employees, projects only needed columns, and joins related tables.

:NEW and :OLD are pseudorecords available only in row-level triggers, which fire once for each affected row. Statement-level triggers fire once per SQL statement regardless of how many rows are affected, so there are no individual :NEW or :OLD values to reference.

ORDER BY must be the last clause in a SELECT query. It can only appear after SELECT, FROM, WHERE, GROUP BY, and HAVING clauses. This is because ORDER BY operates on the final result set after all filtering, joining, and aggregation has been performed.

UNION ALL is faster than UNION because it doesn't eliminate duplicate rows. UNION requires additional processing (sorting or hashing) to identify and remove duplicates, while UNION ALL simply combines all rows from both queries. Use UNION ALL when you know there are no duplicates or don't care about them.

Bitmap indexes are best for columns with LOW cardinality (few distinct values like gender, status, or boolean flags). They use bitmaps to represent which rows have each value. High cardinality columns (many distinct values) are better served by B-tree indexes, which would be more efficient for that data distribution.

In Oracle database, the maximum length of a table name is 30 characters. This limit applies to traditional Oracle versions and is a fundamental constraint that database administrators must follow when naming tables. Options A (50), C (20), and D (40) are incorrect as they don't match Oracle's standard naming limitation.

In Oracle SQL, the YY format specifier interprets two-digit years within a sliding window. The year '51' with YY format defaults to 2051 (current century for years 50-99). The RR format uses a different sliding window based on the current year, but YY simply uses the current century boundary.

Oracle's RR format uses a sliding window: if the last two digits of the current year are 00-49, then years 00-49 map to current century and 50-99 map to previous century. Assuming current year context where '51' falls in the 50-99 range, it maps to 1951. This is different from YY which would give 2051.

When the input year is '99', YY and RR formats give different results. YY interprets '99' as 2099 (current century), while RR's sliding window maps '99' to 1999 (previous century, assuming current year is in 00-49 range). Therefore, the outputs are NOT the same.

For year '00', both YY and RR formats resolve to 2000 when the current year is in the 00-49 range. RR's sliding window maps 00-49 to the current century, and YY also maps to current century. Thus, both queries produce the same output.

When '#' is enclosed in single quotes, Oracle treats it as a string literal, not a comment symbol. The query SELECT '#' FROM dual simply returns the character '#' as a string value. No error occurs because it's properly quoted.

When '#' appears without quotes in a SELECT statement, Oracle interprets it as the start of a comment. Since a comment requires valid syntax or continues to end of line, this causes an 'Invalid Character' error. The '#' character is not a valid token outside string literals.

While the first query returns a numeric literal and the second returns a character literal, their standard text output representations are identical in most query tools. Thus, the visual result or output value of both queries is effectively the same.

ROWNUM is a pseudo-column assigned to rows as they are retrieved. In Oracle, ROWNUM values start from 1, and ROWNUM <= 3 will return the first 3 rows. The BETWEEN 0 AND 3 condition effectively filters for ROWNUM <= 3, returning exactly 3 rows from the 30-row table.

ROWNUM conditions have special behavior. ROWNUM IN (1,3) only matches the first row because ROWNUM = 1 is true for the first row, but when checking ROWNUM = 3, the third row never gets ROWNUM = 3 (it fails at 1 or 2). Only the first row satisfies ROWNUM = 1, so exactly 1 row is returned.