SQL & PL/SQL Fundamentals Quiz

The SUM function computes total numeric values across multiple rows. To calculate all salaries in a department, SUM(salary) adds each individual salary to produce one combined total. MAX and MIN identify upper and lower values, COUNT tallies rows, while TOTAL and LARGEST are not valid Oracle group functions. SUM is specifically designed for aggregation of numeric fields, making it the appropriate choice for calculating department-wide salary totals.

UTL_FILE provides procedures for reading from and writing to external files on the operating system. It enables PL/SQL programs to interact with text files outside the database using directories defined as Oracle directory objects. It does not create tables, triggers, or autonomous transactions. UTL_FILE is commonly used for data import/export, logging, and integration tasks requiring file-based communication. Therefore, its primary purpose is file input and output operations.

The DELETE statement removes rows, not tables. DELETE FROM CUSTOMER WHERE… allows selective row removal using a condition, preserving the table structure. DROP removes the entire table, UPDATE modifies existing rows, and REMOVE is not valid SQL. DELETE is part of DML, meaning it manipulates table data without altering the underlying schema. Therefore, DELETE FROM CUSTOMER WHERE… is the correct statement for removing specific rows from the CUSTOMER table.

SQL*Plus includes commands that interact with the client tool rather than the database engine. DESCRIBE is an SQL*Plus command that displays table structure, column names, data types, and constraints. The other options—DELETE, UPDATE, SELECT, ALTER—are SQL statements executed by the database, not SQL*Plus commands. Because DESCRIBE is specific to SQL*Plus environments and not standard SQL, it is the only correct choice among the provided options.

The WHERE clause filters rows before grouping or sorting. To limit employees whose salary exceeds 5000, WHERE salary > 5000 is required. ORDER BY arranges results, GROUP BY aggregates rows, and HAVING filters groups after aggregation. Because salary comparison occurs at the row level, not the group level, WHERE is the correct clause. This ensures only employees with salaries greater than 5000 appear in the output before any later operations.

TRUNCATE removes all rows from a table instantly by deallocating data pages rather than deleting rows individually. This makes it faster than DELETE and minimally logged. It preserves the table structure, columns, and constraints. TRUNCATE cannot remove specific rows because it has no WHERE clause. Unlike DROP, it does not remove the table itself. Therefore, TRUNCATE is the correct command for clearing all table data efficiently without deleting the table definition.

Oracle requires the syntax CREATE USER username IDENTIFIED BY password to create a new database user. IDENTIFIED BY sets the password, and omitting it makes the statement invalid. The other options lack mandatory keywords or contain incorrect ordering. Because Oracle strictly enforces this syntax in user creation operations, only CREATE USER username IDENTIFIED BY password is correct. This command registers the user within the database with authentication credentials.

UNIQUE, CHECK, PRIMARY KEY, and NOT NULL are valid Oracle constraints. UNIQUE enforces distinct column values, CHECK validates data conditions, PRIMARY KEY ensures row uniqueness and non-nullability, and NOT NULL prohibits missing values. CASCADE, NONUNIQUE, and CONSTANT are not constraint types. Cascade describes referential actions, not constraints. Therefore, these four represent Oracle’s standard constraint categories used to enforce data integrity and relational consistency across tables.

WITH GRANT OPTION allows a user who receives an object privilege, such as SELECT or INSERT, to pass that privilege to other users or roles. It does not grant DBA privileges or restrict columns. This option provides controlled privilege propagation but must be used carefully to maintain security. It is applied when granting object-level privileges and enables delegation of access rights beyond the original grantee.

CREATE TABLE is a system privilege because it allows creation of schema objects across the database rather than on specific tables. System privileges operate at the database level, enabling structural changes. DELETE, TRUNCATE, and DROP are object privileges applied to individual tables. ALTER TABLE modifies existing objects but is also an object privilege. Therefore, CREATE TABLE is the correct choice because it grants system-wide authority.

The valid CREATE TABLE syntax in Oracle follows the pattern CREATE TABLE table_name (column_name datatype). Options containing “add,” “modified,” or incorrect datatypes are syntactically invalid. Oracle requires parentheses around all column definitions and valid datatypes such as NUMBER, VARCHAR2, or DATE. Therefore, the correct statement is the one that defines a table named EMP with a single numeric column, written using proper structure and accepted Oracle datatype conventions.

The DROP TABLE command completely removes both the table structure and all stored data. Once executed, the table cannot be recovered without backup. It differs from DELETE, which removes rows but preserves structure, and from TRUNCATE, which removes rows but keeps the table. DROP TABLE also enforces referential integrity, preventing the drop if child tables depend on it. Therefore, DROP TABLE deletes both structure and data permanently.

Selecting all records with the last name KING requires a SELECT * statement and a WHERE clause checking equality. LIKE 'KING' is valid but unnecessary unless patterns are used. Using asterisk retrieves all columns, while SELECT all is invalid syntax. Therefore, SELECT * FROM EMP WHERE Last_Name='KING' retrieves every column for employees whose last name exactly matches KING, using correct SQL syntax and structure required for row filtering.

ALTER modifies table structure rather than data. It can add, drop, or change columns, rename objects, or modify constraints. It does not insert, update, or delete rows. ALTER operates at the schema level, adjusting the table definition without affecting stored content. Because schema evolution is essential in application development, ALTER TABLE is used frequently to accommodate new requirements. Therefore, the ALTER statement specifically changes table structure, not table data.

The statement without a FROM clause is incorrect because SQL requires a table source for any column selection. “SELECT empid WHERE …” fails syntactically since SQL must know where the column resides. Every SELECT statement that retrieves data must include a FROM clause unless selecting literal values or expressions only. Therefore, the missing table reference makes the statement invalid, and the other three statements follow correct SQL syntax and structure.

NVL replaces NULL with a specified value, making it ideal for default substitution, display formatting, and avoiding calculation errors. NVL(expr, value) returns value when expr is NULL. COALESCE can also replace NULL but evaluates multiple expressions, while DECODE performs conditional logic and NULLIF returns NULL when two expressions match. NVL is the simplest and most direct function for substituting NULLs in Oracle SQL when only one column needs replacement.

ORDER BY sorts rows in ascending or descending order based on specified columns. GROUP BY aggregates rows, HAVING filters groups, and SORT BY does not exist in SQL. ORDER BY appears last in a SELECT statement because sorting is applied after filtering, grouping, and projection. Its purpose is strictly to arrange the final result set. Therefore, ORDER BY is the correct and only keyword for sorting database query results.

INSERT INTO is the standard SQL syntax for adding new records to a table. It specifies the target table and either the full list of columns or relies on default ordering. The values clause provides corresponding data. The other options, such as ADD RECORD or INSERT NEW, are not recognized SQL commands. INSERT INTO maintains integrity, supports multi-row insertion, and is universally used across relational database systems including Oracle.

DROP TABLE DEPTT permanently removes the table and all its rows from the database. DELETE FROM DEPTT removes data but preserves structure, while UPDATE modifies existing values rather than deleting objects. REMOVE is not a recognized SQL keyword. DROP TABLE executes a DDL operation, meaning it commits implicitly and cannot be rolled back. Therefore, DROP TABLE DEPTT is the correct command to eliminate the table entirely.

Oracle allows dropping a column even when it contains data using ALTER TABLE DROP COLUMN. The operation permanently deletes the column and all stored values. It is a DDL command, meaning it commits implicitly and cannot be undone without recovery. This feature supports schema evolution but should be used cautiously. Therefore, deletion is always possible, although the decision must consider data loss and dependencies.

Oracle 9i permits a maximum of 1000 columns per table or view. This limit applies regardless of datatype or column usage. Although uncommon in well-normalized designs, Oracle supports this high limit for compatibility with complex legacy systems. Only 1000 is correct because the other options underestimate or overestimate Oracle’s documented structural limits. Proper table design encourages fewer columns, but the system supports up to 1000 when necessary.

Querying V$VERSION retrieves Oracle version details, including database, PL/SQL, and component versions. The V$ views expose dynamic performance and configuration information. The $VERSION view does not exist, and other listed options are incorrect. Using SELECT * FROM v$version is the standard and documented method for determining installed Oracle software versions, making it essential for system audits, troubleshooting, and compatibility verification across development and production environments.

Oracle supports up to 255 levels of nested subqueries within a WHERE clause. This high limit allows complex filtering logic, advanced hierarchical queries, and multi-layered conditions. Although such deeply nested queries are rarely practical, the capability supports sophisticated analytical patterns. Exceeding this limit results in a compilation error. Therefore, 255 represents the documented maximum depth for nested subqueries in Oracle SQL statements.

The minimum value of an ascending sequence defaults to 1 in Oracle unless explicitly overridden. Start values and increments can be customized, but ascending sequences prevent values below their defined minimum. The start value does not always equal 1, and increments are not fixed at 1 unless specified. Therefore, only the statement about the default minimum value is correct among the provided options. Oracle sequences are versatile objects supporting customizable behaviors.

SQL includes string functions for manipulating text, numeric functions for calculations, date functions for handling time values, and conversion functions for changing data types. These categories cover most general-purpose operations needed in SQL queries. Options like “integer” and “calendar” are not standard function categories, and “translation” refers to NLS language conversions, not a primary function class. Therefore, the four valid types are string, numeric, date, and conversion.

A primary key must uniquely identify rows in a table, and such uniqueness originates from candidate keys. Candidate keys represent all minimal attribute combinations capable of uniquely identifying tuples. The primary key is chosen from among these. Composite keys may be chosen but are not always required. Determinants define attributes functionally, and foreign keys reference primary keys in other tables. Therefore, candidate keys are the source from which the primary key is selected.

A tuple represents a row in a relational table, consisting of fields corresponding to each column. It stores a single complete record within a dataset. Unlike columns, which represent attributes, rows represent actual data instances. Recognizing tuples is essential for understanding relational theory and SQL operations such as INSERT, UPDATE, and DELETE, which operate on rows. Therefore, the correct interpretation is that a tuple is simply a row in a table.

COMMIT cannot execute inside a normal trigger because triggers operate within the transaction context that fired them. However, Oracle allows COMMIT inside an autonomous transaction defined by PRAGMA AUTONOMOUS_TRANSACTION, which creates a separate transactional unit independent of the main process. This isolation allows committing logs or audit data without affecting the parent transaction. Therefore, COMMIT is permissible only within autonomous transactions, not ordinary triggers.

DBMS_OUTPUT.PUT_LINE has a maximum line length of 255 characters. Exceeding this limit causes an exception. The buffer accumulates output but enforces the per-line restriction. Because PUT_LINE is used for debugging or logging in PL/SQL, developers must ensure message length remains within allowed limits. The constraint applies per invocation, not to the overall buffer, making 255 characters the strict maximum allowable length for each printed line.

NULL has no datatype in Oracle. It represents missing, unknown, or inapplicable data. It behaves differently depending on context and adopts the datatype of surrounding expressions where applicable. Because NULL is a logical marker rather than a stored value, it cannot be classified as CHAR(0), CHAR(1), or any other type. Therefore, the datatype of NULL is best described as “none of the above,” reflecting its unique conceptual role.

Oracle 9i enforces a maximum object name length of 30 characters. Object names include tables, columns, constraints, indexes, and views. Exceeding this limit results in an ORA-00972 error. This constraint ensures uniform metadata handling and compatibility across tools. Although later Oracle versions permit longer names in some contexts, Oracle 9i strictly adheres to the 30-character limit, making it the correct maximum length for all database object names.

ALTER SYSTEM KILL SESSION 'sid,serial#' terminates a session by its identifier and serial number. Oracle requires this syntax to safely release resources held by the session. Incorrect variants either miss required keywords or contain invalid grammar. Killing a session is typically performed when a session is hung, blocking others, or consuming excessive resources. Therefore, the precise and valid command is ALTER SYSTEM KILL SESSION 'sid,serial#'.

Constraint names do not need to start with SYS_C; Oracle generates SYS_C names automatically only when you do not specify your own name. Constraints may be defined at either the column or table level depending on complexity. Constraints can be created later using ALTER TABLE or created during table creation. Therefore, the true statements are that constraints may be created after table creation and also at the time the table is created.