Oracle SQL Oracle SQL DDL and Schema Objects 2 — Questions and Answers
Question 1: What is a synonym in Oracle SQL?
- An alternate name for a database object (Correct answer)
- A copy of a table's data
- A stored procedure alias
- A backup of an index
Correct answer: An alternate name for a database object
A synonym is an alternative name for a database object (table, view, sequence, etc.) that simplifies access and hides the actual object name or owner.
Question 2: Which Oracle SQL index type is best suited for columns with very few distinct values like gender (M/F)?
- B-tree index
- Bitmap index (Correct answer)
- Function-based index
- Composite index
Correct answer: Bitmap index
Bitmap indexes are highly efficient for low-cardinality columns because they store a bit for each distinct value, enabling fast logical operations.
Question 3: What does CREATE TABLE AS SELECT do in Oracle SQL?
- Creates a view based on a SELECT
- Creates a new table with structure and data copied from a query result (Correct answer)
- Creates a temporary table
- Creates a table with only column structure, no data
Correct answer: Creates a new table with structure and data copied from a query result
CREATE TABLE ... AS SELECT (CTAS) creates a new table and populates it with the result set of the SELECT query.
Question 4: Which Oracle SQL constraint ensures a column's value meets a specific condition?
- UNIQUE
- NOT NULL
- CHECK (Correct answer)
- FOREIGN KEY
Correct answer: CHECK
A CHECK constraint validates that column values satisfy a specified Boolean condition, such as salary > 0.
Question 5: What is the purpose of the NOCACHE option when creating an Oracle SEQUENCE?
- Prevents the sequence from being cached in memory, issuing numbers one at a time (Correct answer)
- Stops the sequence from cycling
- Creates a descending sequence
- Prevents other sessions from using the sequence
Correct answer: Prevents the sequence from being cached in memory, issuing numbers one at a time
NOCACHE forces Oracle to generate each sequence number with a separate database operation, avoiding gaps but reducing performance.
Question 6: Which privilege is required to create a table in another user's schema in Oracle SQL?
- CREATE TABLE
- CREATE ANY TABLE (Correct answer)
- ALTER TABLE
- INSERT ANY TABLE
Correct answer: CREATE ANY TABLE
CREATE ANY TABLE privilege allows a user to create tables in any schema, not just their own.
Oracle SQL Oracle SQL DDL and Schema Objects 2
What is a synonym in Oracle SQL?