Oracle 12C SQL (ICT 3722) Exam with Questions and Solutions

It covers key domains which include; Database Objects, Controlling User Access (DCL) and Advanced SQL Features (12c Specifics). Which statement best describes the primary purpose of a sequence in Oracle 12c SQL? a. To enforce referential integrity between tables b. To automatically generate unique numeric values c. To store temporary data during transactions d. To define relationships between primary and foreign keys Correct Answer: b Rationale: A sequence in Oracle 12c SQL is a database object designed to generate unique numeric values, most commonly used for primary key generation. It ensures that each call produces a distinct number, reducing the risk of duplication in multi-user environments. Which SQL command is used to create a sequence for generating primary key values? a. CREATE INDEX b. CREATE TABLE c. CREATE SEQUENCE d. ALTER SEQUENCE Correct Answer: c Rationale: The CREATE SEQUENCE statement is used to define a new sequence object in Oracle. It allows specification of start value, increment, maximum value, caching behavior, and cycling options. A developer creates the following sequence: START WITH 1 INCREMENT BY 1 CACHE 20. What is the main advantage of using CACHE 20? a. It prevents gaps in sequence numbers permanently b. It stores 20 sequence values in memory for faster access c. It resets the sequence after every transaction d. It ensures sequence values are always reused Correct Answer: b Rationale: The CACHE option preallocates a set of sequence numbers in memory, improving performance by reducing disk I/O. However, it may result in gaps if the database restarts unexpectedly.