Test Your Oracle Database Table Knowledge

Progress

True / False

In Oracle Database, a table is used to store data in rows and columns.

True False

A table in Oracle Database organizes data into rows and columns, allowing for structured data storage and retrieval.

True / False

In Oracle, the CREATE TABLE statement is used to create a new table.

True False

The CREATE TABLE statement in Oracle is used to define a new table and its columns.

True / False

Each table in Oracle must have at least one column.

True False

A table must have at least one column to store data, as columns define the structure of the data.

True / False

In Oracle Database, the ALTER TABLE statement is used to modify the structure of an existing table.

True False

The ALTER TABLE statement allows users to add, delete, or modify columns and constraints in an existing table.

True / False

Oracle tables can only store numeric and character data types.

True False

Oracle tables can store a variety of data types, including numeric, character, date, binary, and others.

True / False

Oracle allows for the creation of temporary tables that are automatically dropped at the end of a session.

True False

Oracle supports temporary tables that exist for the duration of a session or transaction and are automatically dropped afterwards.

True / False

Oracle Database supports partitioning of tables to improve performance and manageability.

True False

Table partitioning in Oracle allows large tables to be divided into smaller, more manageable pieces, which can improve performance and simplify maintenance.

True / False

In Oracle, a table can have multiple primary keys.

True False

A table can have only one primary key, which uniquely identifies each row in the table. However, the primary key can consist of multiple columns (composite key).

True / False

Oracle tables support the use of triggers to automatically execute a specified action when certain events occur.

True False

Triggers in Oracle can be associated with tables to automatically perform actions in response to events like INSERT, UPDATE, or DELETE.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes topics on creating, managing, and optimizing database tables.

True False

The OCP certification exam covers a wide range of topics, including the creation, management, and optimization of database tables, reflecting the candidate's comprehensive knowledge and skills in Oracle Database administration.

Q11

Single Choice

Which SQL command is used to create a new table in an Oracle database?

CREATE NEW TABLE NEW TABLE CREATE TABLEADD TABLE

The CREATE TABLE statement is used to create a new table in an Oracle database.

Q12

Single Choice

In Oracle, what is the maximum number of columns allowed in a table?

1000 2000 500100

Oracle allows a maximum of 1000 columns in a single table.

Q13

Single Choice

Which Oracle data type should be used to store large text data in a table column?

VARCHAR2 NUMBER BLOBCLOB

The CLOB (Character Large Object) data type is used to store large text data in Oracle tables.

Q14

Single Choice

What will happen if you try to insert a NULL value into a column defined with the NOT NULL constraint in Oracle?

The NULL value will be inserted. Oracle will replace the NULL value with a default value. An error will be raised.The row will be inserted without the NULL value.

Inserting a NULL value into a column with a NOT NULL constraint will raise an error in Oracle.

Q15

Single Choice

Consider the following SQL command executed in an Oracle database:What is the purpose of this command?

SQL Code

ALTER TABLE employees ADD CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id);

To add a new column to the employees table. To create a foreign key relationship between the employees and departments tables. To delete the department_id column from the employees table.To create a new table named emp_dept_fk.

The ALTER TABLE statement is used to add a foreign key constraint that links the department_id in the employees table to the department_id in the departments table.

Q16

Single Choice

Which of the following SQL statements in Oracle is used to rename an existing table?

RENAME TABLE ALTER TABLE MODIFY TABLECHANGE TABLE

The ALTER TABLE statement in Oracle is used with the RENAME TO clause to rename an existing table.

Q17

Single Choice

What will be the result of the following SQL command in Oracle?

SQL Code

DROP TABLE employees CASCADE CONSTRAINTS;

The employees table will be deleted, but the foreign keys will remain. The employees table will be deleted, along with all dependent objects such as constraints. Only the constraints on the employees table will be deleted.The employees table will be deleted, but an error will occur if any foreign keys exist.

The DROP TABLE ... CASCADE CONSTRAINTS command in Oracle deletes the table and all its associated constraints.

Q18

Single Choice

Which SQL command in Oracle can be used to add a partition to an existing partitioned table?

ALTER TABLE ... ADD PARTITION CREATE PARTITION ON TABLE MODIFY TABLE ... ADD PARTITIONPARTITION TABLE ... ADD

The ALTER TABLE ... ADD PARTITION command in Oracle is used to add a new partition to an existing partitioned table.

Q19

Single Choice

Given the following SQL command in Oracle:What does this command accomplish?

SQL Code

CREATE TABLE orders (
 order_id NUMBER PRIMARY KEY,
 customer_id NUMBER,
 order_date DATE
) PARTITION BY RANGE (order_date) (
 PARTITION p1 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')),
 PARTITION p2 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
);

It creates a table with a primary key and partitions it by the order date. It creates a table with two columns and no partitions. It creates a table with multiple primary keys.) It creates a table with a composite key.

The command creates a table orders with a primary key and partitions the table into two partitions based on the order_date.

Q20

Single Choice

Which Oracle SQL statement is used to move a table from one tablespace to another?

MOVE TABLE ALTER TABLE ... MOVE TRANSFER TABLERELOCATE TABLE

The ALTER TABLE ... MOVE statement in Oracle is used to move a table to a different tablespace, which can also help in reorganizing the table to improve performance.

Q21

Multiple Choice

Identify the SQL scripts that correctly create a database table with constraints in Oracle.

CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, employee_name VARCHAR2(100), department_id NUMBER, CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id) ); CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, employee_name VARCHAR2(100), department_id NUMBER CONSTRAINT fk_department REFERENCES departments(department_id) ); CREATE TABLE employees ( employee_id NUMBER, employee_name VARCHAR2(100), department_id NUMBER, PRIMARY KEY (employee_id), FOREIGN KEY (department_id) REFERENCES departments(department_id) );CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, employee_name VARCHAR2(100), department_id NUMBER CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id) );

Creating a table with primary key and foreign key constraints ensures data integrity. The correct scripts include the appropriate CREATE TABLE and CONSTRAINT clauses.

Q22

Multiple Choice

Select the SQL scripts that demonstrate correct table creation with default values and constraints in Oracle.

CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE, order_status VARCHAR2(20) DEFAULT 'Pending' ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE, order_status VARCHAR2(20) DEFAULT 'Pending' NOT NULL ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE, order_status VARCHAR2(20) DEFAULT 'Pending' );CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE, order_status VARCHAR2(20) DEFAULT 'Pending' NOT NULL CONSTRAINT chk_status CHECK (order_status IN ('Pending', 'Completed')) );

Setting default values and constraints during table creation is essential for maintaining data consistency. The correct scripts demonstrate how to implement these features in Oracle.

Q23

Multiple Choice

Determine the SQL scripts that correctly implement partitioning for a database table in Oracle.

CREATE TABLE sales ( sale_id NUMBER, sale_date DATE, amount NUMBER ) PARTITION BY RANGE (sale_date) ( PARTITION p2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')), PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) ); CREATE TABLE sales ( sale_id NUMBER, sale_date DATE, amount NUMBER ) PARTITION BY RANGE (sale_date) ( PARTITION p1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')), PARTITION p2 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) ); CREATE TABLE sales ( sale_id NUMBER, sale_date DATE, amount NUMBER ) PARTITION BY LIST (sale_date) ( PARTITION p2022 VALUES ('2022'), PARTITION p2023 VALUES ('2023') );CREATE TABLE sales ( sale_id NUMBER, sale_date DATE, amount NUMBER ) PARTITION BY RANGE (sale_date) ( PARTITION p2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')), PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) );

Partitioning tables can significantly improve performance for large datasets. The correct scripts will include the partitioning clause based on the 'sale_date' column.

Q24

Multiple Choice

Identify the SQL scripts that correctly use Oracle's external table feature.

CREATE TABLE employee_data ( employee_id NUMBER, employee_name VARCHAR2(100), salary NUMBER ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' ) LOCATION ('employee_data.csv') ); CREATE TABLE employee_data ( employee_id NUMBER, employee_name VARCHAR2(100), salary NUMBER ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir ACCESS PARAMETERS ( FIELDS TERMINATED BY ',' ) LOCATION ('employee_data.csv') ); CREATE TABLE employee_data ( employee_id NUMBER, employee_name VARCHAR2(100), salary NUMBER ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' ) LOCATION ('employee_data.csv') );CREATE TABLE employee_data ( employee_id NUMBER, employee_name VARCHAR2(100), salary NUMBER ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir ACCESS PARAMETERS ( FIELDS TERMINATED BY ',' ) LOCATION ('employee_data.csv') );

External tables allow Oracle to access data stored outside of the database. The correct scripts include the CREATE TABLE statement with the ORGANIZATION EXTERNAL clause.

Q25

Multiple Choice

Choose the SQL scripts that correctly demonstrate the use of Oracle's temporal validity feature.

CREATE TABLE contracts ( contract_id NUMBER PRIMARY KEY, contract_name VARCHAR2(100), validity_period PERIOD FOR valid_time ); CREATE TABLE contracts ( contract_id NUMBER PRIMARY KEY, contract_name VARCHAR2(100), valid_time PERIOD FOR validity_period ); CREATE TABLE contracts ( contract_id NUMBER PRIMARY KEY, contract_name VARCHAR2(100), validity_start DATE, validity_end DATE, PERIOD FOR validity_period (validity_start, validity_end) );CREATE TABLE contracts ( contract_id NUMBER PRIMARY KEY, contract_name VARCHAR2(100), validity_period PERIOD FOR valid_time (validity_start DATE, validity_end DATE) );

Oracle's temporal validity feature allows for the creation of tables that include validity periods for records. The correct scripts will demonstrate the use of temporal columns.

Q26

Multiple Choice

Identify the SQL scripts that demonstrate advanced table management with Oracle's compression features.

CREATE TABLE archive_data ( data_id NUMBER, data_value CLOB ) COMPRESS FOR OLTP; CREATE TABLE archive_data ( data_id NUMBER, data_value CLOB ) COMPRESS FOR QUERY; CREATE TABLE archive_data ( data_id NUMBER, data_value CLOB ) COMPRESS FOR ARCHIVE;CREATE TABLE archive_data ( data_id NUMBER, data_value CLOB ) COMPRESS;

Oracle's advanced compression features help optimize storage for large tables. The correct scripts will include the COMPRESSION clause within the CREATE TABLE statement.

Q27

Multiple Choice

Select the SQL scripts that correctly demonstrate the use of Oracle's table partitioning and indexing together.

CREATE TABLE transactions ( transaction_id NUMBER, transaction_date DATE, amount NUMBER ) PARTITION BY RANGE (transaction_date) ( PARTITION jan2024 VALUES LESS THAN (TO_DATE('2024-02-01', 'YYYY-MM-DD')), PARTITION feb2024 VALUES LESS THAN (TO_DATE('2024-03-01', 'YYYY-MM-DD')) ); CREATE INDEX idx_transaction_date ON transactions(transaction_date) LOCAL; CREATE TABLE transactions ( transaction_id NUMBER, transaction_date DATE, amount NUMBER ) PARTITION BY RANGE (transaction_date) ( PARTITION jan2024 VALUES LESS THAN (TO_DATE('2024-02-01', 'YYYY-MM-DD')), PARTITION feb2024 VALUES LESS THAN (TO_DATE('2024-03-01', 'YYYY-MM-DD')) ); CREATE INDEX idx_transaction_date ON transactions(transaction_date) GLOBAL; CREATE TABLE transactions ( transaction_id NUMBER, transaction_date DATE, amount NUMBER ) PARTITION BY RANGE (transaction_date) ( PARTITION jan2024 VALUES LESS THAN (TO_DATE('2024-02-01', 'YYYY-MM-DD')), PARTITION feb2024 VALUES LESS THAN (TO_DATE('2024-03-01', 'YYYY-MM-DD')) ); CREATE INDEX idx_transaction_date ON transactions(transaction_date);CREATE TABLE transactions ( transaction_id NUMBER, transaction_date DATE, amount NUMBER ) PARTITION BY RANGE (transaction_date) ( PARTITION jan2024 VALUES LESS THAN (TO_DATE('2024-02-01', 'YYYY-MM-DD')), PARTITION feb2024 VALUES LESS THAN (TO_DATE('2024-03-01', 'YYYY-MM-DD')) ); CREATE INDEX idx_transaction_date ON transactions(transaction_date) PARTITIONED;

Partitioning and indexing are powerful features that can be combined for better performance. The correct scripts will demonstrate the creation of a partitioned table with indexed partitions.

Q28

Multiple Choice

Determine the SQL scripts that correctly use Oracle's table auditing features.

AUDIT INSERT ON customers; CREATE AUDIT POLICY customer_audit_policy ON customers FOR INSERT BY ACCESS; ENABLE AUDIT POLICY customer_audit_policy; AUDIT INSERT ON customers BY ACCESS; AUDIT POLICY customer_audit_policy ON customers; CREATE AUDIT POLICY customer_audit_policy ON customers FOR INSERT BY ACCESS; ENABLE AUDIT POLICY customer_audit_policy;AUDIT INSERT ON customers; CREATE AUDIT TABLE customer_audit AS SELECT * FROM sys.aud$;

Oracle's auditing features help track changes to database tables. The correct scripts will demonstrate enabling auditing for specific operations and storing audit records.

Q29

Multiple Choice

Identify the SQL scripts that correctly demonstrate the use of Oracle's virtual columns.

CREATE TABLE sales ( sale_id NUMBER, quantity NUMBER, price NUMBER, total_amount AS (quantity * price) ); CREATE TABLE sales ( sale_id NUMBER, quantity NUMBER, price NUMBER, total_amount NUMBER GENERATED ALWAYS AS (quantity * price) ); CREATE TABLE sales ( sale_id NUMBER, quantity NUMBER, price NUMBER, total_amount NUMBER GENERATED ALWAYS AS (quantity * price) VIRTUAL );CREATE TABLE sales ( sale_id NUMBER, quantity NUMBER, price NUMBER, total_amount NUMBER AS (quantity * price) );

Oracle's virtual columns are calculated from other columns in the table. The correct scripts will include the creation of a table with a virtual column defined using a calculation.

Q30

Multiple Choice

Choose the SQL scripts that demonstrate the use of Oracle's table inheritance feature.

CREATE TABLE managers OF employees ( bonus NUMBER ); CREATE TABLE managers ( employee_id NUMBER, employee_name VARCHAR2(100), department_id NUMBER, bonus NUMBER ) INHERITS (employees); CREATE TABLE managers ( employee_id NUMBER, employee_name VARCHAR2(100), department_id NUMBER, bonus NUMBER );CREATE TABLE managers ( bonus NUMBER ) INHERITS (employees);

Table inheritance allows a new table to inherit columns from an existing table, with additional columns added. The correct scripts will demonstrate creating a table with inherited and additional columns.

Oracle Database Quiz Questions