Test Your Oracle CREATE TABLE Knowledge

Progress

True / False

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

True False

The CREATE TABLE statement is used to define a new table and its structure within the Oracle Database.

True / False

In Oracle, every table must have at least one column defined.

True False

A table must have at least one column defined to store data.

True / False

The data type of a column in an Oracle table can be specified when using the CREATE TABLE statement.

True False

When creating a table, you must specify the data type for each column to define what kind of data it can hold.

True / False

In Oracle, the CREATE TABLE statement can include constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK.

True False

Constraints can be included in the CREATE TABLE statement to enforce rules on the data in the table.

True / False

The CREATE TABLE statement in Oracle can include a DEFAULT value for a column.

True False

You can specify a default value for a column in the CREATE TABLE statement, which will be used if no value is provided for that column.

True / False

In Oracle, the CREATE TABLE statement cannot reference other tables.

True False

The CREATE TABLE statement can reference other tables, particularly when defining FOREIGN KEY constraints.

True / False

In Oracle, the CREATE TABLE statement can be used to create a partitioned table.

True False

Oracle supports partitioned tables, and you can define partitions within the CREATE TABLE statement to improve performance and manageability.

True / False

In Oracle, virtual columns can be defined in the CREATE TABLE statement.

True False

Virtual columns can be defined using expressions in the CREATE TABLE statement, and these columns are calculated based on other column values.

True / False

In Oracle, you can create an INDEX as part of the CREATE TABLE statement.

True False

Indexes are typically created using the CREATE INDEX statement separately, not directly within the CREATE TABLE statement.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on optimizing the CREATE TABLE statement for performance and storage management.

True False

The OCP certification covers advanced topics, including how to optimize table creation for performance and efficient storage management, reflecting the candidate's deep understanding of Oracle Database management.

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 the CREATE TABLE statement, which keyword is used to specify the data type for a column that stores text in Oracle?

NUMBER TEXT VARCHAR2CHAR

The VARCHAR2 data type is used to store variable-length character strings in an Oracle table.

Q13

Single Choice

Which of the following is a correct syntax to create a table named employees with a single column employee_id as a primary key?

CREATE TABLE employees (employee_id NUMBER PRIMARY); CREATE TABLE employees (employee_id PRIMARY KEY); CREATE TABLE employees (employee_id NUMBER PRIMARY KEY);CREATE TABLE employees (employee_id PRIMARY NUMBER);

The correct syntax for creating a table with a primary key includes specifying the column name, data type, and the PRIMARY KEY constraint.

Q14

Single Choice

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

SQL Code

CREATE TABLE departments (
 department_id NUMBER,
 department_name VARCHAR2(50),
 location_id NUMBER DEFAULT 100
);

Creates a table with the specified columns and a default value of 100 for location_id. Creates a table without the location_id column. Returns an error because a default value cannot be specified.Creates a table with location_id defaulting to NULL.

The CREATE TABLE statement creates a table with three columns, where location_id has a default value of 100.

Q15

Single Choice

Which Oracle SQL statement is used to create a table projects with a foreign key referencing the departments table?

SQL Code

CREATE TABLE projects (
 project_id NUMBER PRIMARY KEY,
 project_name VARCHAR2(100),
 department_id NUMBER,
 CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Creates the table with no foreign key. Creates the table with a foreign key that references the department_id column in the departments table. Returns an error because foreign keys cannot be used in a CREATE TABLE statement.Creates the table with a foreign key that references any column in the departments table.

The CREATE TABLE statement includes a FOREIGN KEY constraint that references the department_id column in the departments table.

Q16

Single Choice

What happens if you execute the following Oracle SQL command?

SQL Code

CREATE TABLE employees_copy AS SELECT * FROM employees;

Copies the structure and data from the employees table to a new table employees_copy. Copies only the structure of the employees table, without any data. Returns an error because you cannot create a table with data.Creates an empty table with the same structure as employees.

The CREATE TABLE ... AS SELECT statement creates a new table with the same structure and data as the specified table.

Q17

Single Choice

Which of the following Oracle SQL statements creates a partitioned table?

SQL Code

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'))
);

Creates a table with no partitions. Creates a table partitioned by the sale_date column. Returns an error because partitioning is not allowed in Oracle.Creates a table with partitions based on the sale_id column.

The PARTITION BY RANGE clause creates a partitioned table where rows are stored in different partitions based on the sale_date column.

Q18

Single Choice

In Oracle, how can you create a table archive_employees that inherits the structure of an existing employees table without copying the data?

SQL Code

CREATE TABLE archive_employees AS SELECT * FROM employees WHERE 1=0;

Creates a new table with the same structure and data. Creates a new table with the same structure but without copying the data. Creates a new table with different structure and no data.Returns an error because the WHERE clause cannot be used in CREATE TABLE

The condition WHERE 1=0 ensures that no rows are selected, so only the structure of the employees table is copied to archive_employees.

Q19

Single Choice

Which of the following is a correct Oracle SQL command to create a temporary table?

SQL Code

CREATE GLOBAL TEMPORARY TABLE temp_orders (
 order_id NUMBER,
 order_date DATE
) ON COMMIT DELETE ROWS;

Creates a permanent table. Creates a temporary table where data is deleted at the end of the transaction. Creates a temporary table where data persists beyond the transaction.Creates a table that is only visible to the user who created it.

The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table, and the ON COMMIT DELETE ROWS clause specifies that the data is deleted when the transaction is committed.

Q20

Single Choice

Which of the following is true about creating tables with the Oracle CREATE TABLE statement in an Oracle Exadata environment?

Tables cannot be partitioned in an Exadata environment. You can create hybrid columnar compressed tables to optimize storage. All tables are automatically replicated across multiple nodes.Tables cannot have more than 500 columns.

Oracle Exadata supports Hybrid Columnar Compression, which optimizes storage by compressing data in a columnar format, making it ideal for large data warehouses.

Q21

Multiple Choice

Identify the SQL scripts that correctly create a basic table with primary key constraints in Oracle.

CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY, customer_name VARCHAR2(100), contact_number VARCHAR2(15) ); CREATE TABLE customers ( customer_id NUMBER, customer_name VARCHAR2(100), contact_number VARCHAR2(15) PRIMARY KEY (customer_id) ); CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY, customer_name VARCHAR2(100), contact_number VARCHAR2(15) );CREATE TABLE customers ( customer_id NUMBER, customer_name VARCHAR2(100), contact_number VARCHAR2(15) PRIMARY KEY (customer_id) );

The CREATE TABLE statement in Oracle allows you to define the table structure, including constraints like primary keys. The correct scripts will include the CREATE TABLE statement with the appropriate PRIMARY KEY constraint.

Q22

Multiple Choice

Select the SQL scripts that correctly create a table with foreign key constraints in Oracle.

CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE, customer_id NUMBER, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE, customer_id NUMBER, CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE, customer_id NUMBER, CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );CREATE TABLE orders ( order_id NUMBER, order_date DATE, customer_id NUMBER, CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

Foreign key constraints enforce referential integrity between tables. The correct scripts will include the CREATE TABLE statement with the FOREIGN KEY constraint referencing another table.

Q23

Multiple Choice

Determine the SQL scripts that correctly create a table with default values and check constraints in Oracle.

CREATE TABLE products ( product_id NUMBER PRIMARY KEY, product_name VARCHAR2(100), price NUMBER DEFAULT 0 CHECK (price >= 0) ); CREATE TABLE products ( product_id NUMBER PRIMARY KEY, product_name VARCHAR2(100), price NUMBER DEFAULT 0 CHECK (price >= 0) ); CREATE TABLE products ( product_id NUMBER PRIMARY KEY, product_name VARCHAR2(100), price NUMBER DEFAULT 0 CHECK (price >= 0) );CREATE TABLE products ( product_id NUMBER PRIMARY KEY, product_name VARCHAR2(100), price NUMBER DEFAULT 0 CHECK (price >= 0) );

Default values and check constraints ensure that data meets specified criteria. The correct scripts will demonstrate the use of the DEFAULT and CHECK clauses in the CREATE TABLE statement.

Q24

Multiple Choice

Identify the SQL scripts that correctly create a partitioned 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 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 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 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')) );

Partitioning a table can improve performance and manageability. The correct scripts will include the CREATE TABLE statement with a PARTITION BY RANGE clause.

Q25

Multiple Choice

Select the SQL scripts that correctly create a table with index-organized storage in Oracle.

CREATE TABLE inventory ( item_id NUMBER, item_name VARCHAR2(100), quantity NUMBER, PRIMARY KEY (item_id) ) ORGANIZATION INDEX; CREATE TABLE inventory ( item_id NUMBER, item_name VARCHAR2(100), quantity NUMBER, PRIMARY KEY (item_id) ) ORGANIZATION INDEX; CREATE TABLE inventory ( item_id NUMBER, item_name VARCHAR2(100), quantity NUMBER, PRIMARY KEY (item_id) ) ORGANIZATION INDEX;CREATE TABLE inventory ( item_id NUMBER, item_name VARCHAR2(100), quantity NUMBER, PRIMARY KEY (item_id) ) ORGANIZATION INDEX;

An index-organized table (IOT) stores data in a B-tree index structure, improving access performance for certain queries. The correct scripts will include the ORGANIZATION INDEX clause.

Q26

Multiple Choice

Determine the SQL scripts that correctly create a table with advanced storage options in Oracle.

CREATE TABLE documents ( document_id NUMBER PRIMARY KEY, document_name VARCHAR2(200), document_content CLOB ) COMPRESS FOR OLTP ENCRYPT USING 'AES256'; CREATE TABLE documents ( document_id NUMBER PRIMARY KEY, document_name VARCHAR2(200), document_content CLOB ) COMPRESS FOR OLTP ENCRYPT USING 'AES256'; CREATE TABLE documents ( document_id NUMBER PRIMARY KEY, document_name VARCHAR2(200), document_content CLOB ) COMPRESS FOR OLTP ENCRYPT USING 'AES256';CREATE TABLE documents ( document_id NUMBER PRIMARY KEY, document_name VARCHAR2(200), document_content CLOB ) COMPRESS FOR OLTP ENCRYPT USING 'AES256';

Advanced storage options like compression and encryption can optimize storage and secure sensitive data. The correct scripts will demonstrate the use of these options in the CREATE TABLE statement.

Q27

Multiple Choice

Identify the SQL scripts that correctly create a table with virtual columns in Oracle.

CREATE TABLE transactions ( transaction_id NUMBER PRIMARY KEY, quantity NUMBER, price NUMBER, total_amount AS (quantity * price) ); CREATE TABLE transactions ( transaction_id NUMBER PRIMARY KEY, quantity NUMBER, price NUMBER, total_amount NUMBER GENERATED ALWAYS AS (quantity * price) ); CREATE TABLE transactions ( transaction_id NUMBER PRIMARY KEY, quantity NUMBER, price NUMBER, total_amount AS (quantity * price) );CREATE TABLE transactions ( transaction_id NUMBER PRIMARY KEY, quantity NUMBER, price NUMBER, total_amount AS (quantity * price) );

Virtual columns are derived from expressions rather than stored data. The correct scripts will include the definition of a virtual column using an expression in the CREATE TABLE statement.

Q28

Multiple Choice

Select the SQL scripts that correctly create a table with temporal validity in Oracle.

CREATE TABLE contracts ( contract_id NUMBER PRIMARY KEY, contract_name VARCHAR2(100), valid_from DATE, valid_to DATE, PERIOD FOR valid_time (valid_from, valid_to) ); CREATE TABLE contracts ( contract_id NUMBER PRIMARY KEY, contract_name VARCHAR2(100), valid_from DATE, valid_to DATE, PERIOD FOR valid_time (valid_from, valid_to) ); CREATE TABLE contracts ( contract_id NUMBER PRIMARY KEY, contract_name VARCHAR2(100), valid_from DATE, valid_to DATE, PERIOD FOR valid_time (valid_from, valid_to) );CREATE TABLE contracts ( contract_id NUMBER PRIMARY KEY, contract_name VARCHAR2(100), valid_from DATE, valid_to DATE, PERIOD FOR valid_time (valid_from, valid_to) );

Temporal validity allows you to specify the valid time period for each row. The correct scripts will include the PERIOD FOR clause to define the temporal validity in the CREATE TABLE statement.

Q29

Multiple Choice

Determine the SQL scripts that correctly create a table with LOB (Large Object) columns in Oracle.

CREATE TABLE media_files ( file_id NUMBER PRIMARY KEY, file_name VARCHAR2(255), file_data BLOB ); CREATE TABLE media_files ( file_id NUMBER PRIMARY KEY, file_name VARCHAR2(255), file_data CLOB ); CREATE TABLE media_files ( file_id NUMBER PRIMARY KEY, file_name VARCHAR2(255), file_data BLOB );CREATE TABLE media_files ( file_id NUMBER PRIMARY KEY, file_name VARCHAR2(255), file_data CLOB );

LOB columns in Oracle are used to store large data like images or documents. The correct scripts will demonstrate the creation of a table with a BLOB or CLOB column for storing large objects.

Q30

Multiple Choice

Identify the SQL scripts that correctly create a table with identity columns in Oracle.

CREATE TABLE employees ( employee_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, employee_name VARCHAR2(100), department_id NUMBER ); CREATE TABLE employees ( employee_id NUMBER GENERATED ALWAYS AS IDENTITY, employee_name VARCHAR2(100), department_id NUMBER ); CREATE TABLE employees ( employee_id NUMBER GENERATED ALWAYS AS IDENTITY, employee_name VARCHAR2(100), department_id NUMBER );CREATE TABLE employees ( employee_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, employee_name VARCHAR2(100), department_id NUMBER );

Identity columns in Oracle allow for auto-incremented values. The correct scripts will include the IDENTITY clause in the CREATE TABLE statement for the 'employee_id' column.

Oracle Database Quiz Questions