Database and SQL

In standard SQL, GROUP BY cannot reference column aliases defined in the SELECT clause. The statement uses alias 'Departments' in GROUP BY, which is invalid in most databases (Oracle, SQL Server). GROUP BY must use the original column name 'dept_no', not the alias.

SELECT is the standard SQL command for retrieving data from databases. OPEN is used for cursors, GET is not a standard SQL command, and EXTRACT is a function for retrieving date/time components, not data extraction.

Views cannot be used for DELETE if they contain GROUP BY, DISTINCT, or group functions (aggregate functions like COUNT, SUM). These constructs create derived/aggregate data that cannot be mapped back to individual rows.

Views cannot be used for DELETE if they contain GROUP BY, DISTINCT, or group functions (aggregate functions like COUNT, SUM). These constructs create derived/aggregate data that cannot be mapped back to individual rows.

Dropping a view removes only the view definition (metadata). The underlying table and its data are completely unaffected - views are virtual tables, not data storage.

Views with GROUP BY, DISTINCT, or pseudo columns like ROWNUM cannot be modified through DML operations. These constructs create results that cannot be mapped back to base table rows for updates or deletes.

An inline view is a subquery in the FROM clause that has a correlation name (alias). It acts like a temporary table within the query scope, unlike a regular view which is a stored schema object.

Sequences generate unique numeric values in databases, which are most commonly used to populate primary key columns when inserting new records. While sequences can technically generate values for any unique identifier, their primary purpose is generating auto-incrementing values for primary keys. Option D is incorrect because sequences specifically generate numbers, not all types of keys.

Indexes provide performance benefits primarily on large tables by speeding up data retrieval. On small tables, the overhead of maintaining the index outweighs any performance gain, and a full table scan is already fast. Options A and B are actually scenarios where indexes might be useful (high cardinality columns), while Option D is incorrect because indexes are beneficial regardless of update frequency.

TRUNCATE is a DDL command that quickly removes all rows from a table and deallocates the storage space, but it does NOT remove the table structure itself - that would be DROP TABLE. Unlike DROP TABLE which completely removes the table definition, TRUNCATE keeps the table schema intact with all columns and constraints. Options A and B are both correct descriptions of what TRUNCATE does, making C the only option that is 'not right.'

In SQL*Plus, the command set sqlprompt is used to change the command prompt string. The distractors are invalid command names (set prompt is used for other prompt styles, while sql and sqlpromt are syntactically incorrect in SQL*Plus).

All three approaches (A, B, C) are valid SQL techniques for deleting duplicate rows while keeping one instance. They use ROWID (Oracle) or equivalent row identifiers to identify and delete duplicates. Option A keeps the max ROWID; B and C keep the min ROWID per duplicate group. Option D would delete everything.

All four are valid explicit cursor attributes in PL/SQL. %FOUND returns TRUE if the last fetch returned a row. %NOTFOUND is the opposite. %ROWCOUNT counts rows fetched so far. %ISOPEN indicates whether the cursor is currently open. These attributes are essential for controlling cursor-based processing.