Test Your Oracle Database Platforms Knowledge

Progress

True / False

Oracle Database is available only as an on-premises solution.

True False

Oracle Database is available both as an on-premises solution and as a cloud service (Oracle Cloud).

True / False

Oracle Autonomous Database is a self-driving, self-securing, and self-repairing cloud database service.

True False

Oracle Autonomous Database is designed to automate many tasks, including tuning, patching, and security, to improve efficiency and reduce manual workload.

True / False

Oracle Database Express Edition (XE) is a free, entry-level edition of Oracle Database.

True False

Oracle Database Express Edition (XE) is a free, lightweight, and easy-to-use edition of Oracle Database suitable for learning and small applications

True / False

Oracle Database Standard Edition 2 (SE2) includes Real Application Clusters (RAC) support.

True False

Oracle Database Standard Edition 2 (SE2) does not include support for Real Application Clusters (RAC); RAC is available in Oracle Database Enterprise Edition.

True / False

Oracle Multitenant allows a single container database to host multiple pluggable databases.

True False

Oracle Multitenant is a feature that enables a single container database to manage multiple pluggable databases, simplifying management and consolidation.

True / False

Oracle NoSQL Database is designed for high-volume, high-velocity data processing and storage.

True False

Oracle NoSQL Database is a distributed database designed for handling large amounts of data and rapid data operations, making it suitable for modern web, mobile, and IoT applications.

True / False

Oracle Exadata is an engineered system optimized for running Oracle Database.

True False

Oracle Exadata is an engineered system specifically designed and optimized to run Oracle Database workloads with high performance and scalability.

True / False

Oracle Database supports both relational and non-relational data models.

True False

Oracle Database supports traditional relational data models as well as non-relational data models, including JSON, XML, and spatial data.

True / False

Oracle Database In-Memory is a feature that enables real-time analytics by storing data in-memory columnar format.

True False

Oracle Database In-Memory allows for real-time analytics by storing data in an in-memory columnar format, significantly improving query performance.

Q10

True / False

Achieving Oracle Certified Professional (OCP) certification requires knowledge of both on-premises and cloud-based Oracle Database platforms.

True False

The Oracle Certified Professional (OCP) certification typically requires a comprehensive understanding of Oracle Database platforms, including both on-premises and cloud environments, as well as practical skills and experience.

Q11

Single Choice

Which of the following is the default username used to connect to an Oracle Database after installation?

root admin systemoracle

The "system" user is one of the default administrative accounts in Oracle Database.

Q12

Single Choice

What is the Oracle SQL command to retrieve all columns from a table named "employees"?

SELECT * FROM employees; GET * FROM employees; EXTRACT * FROM employees;FETCH * FROM employees;

The "SELECT * FROM" command is used in Oracle SQL to retrieve all columns from a specified table.

Q13

Single Choice

In Oracle SQL, which keyword is used to sort the results of a query?

SORT BY ORDER BY GROUP BYARRANGE BY

The "ORDER BY" clause is used in Oracle SQL to sort the result set by one or more columns.

Q14

Single Choice

Which Oracle SQL statement is used to remove a table from the database?

DELETE TABLE employees; DROP TABLE employees; REMOVE TABLE employees;TRUNCATE TABLE employees;

The "DROP TABLE" command is used in Oracle SQL to permanently delete a table and all its data from the database.

Q15

Single Choice

In Oracle, what does the following SQL statement do?

SQL Code

SELECT emp_name, COUNT(*) FROM employees GROUP BY emp_name;

Counts the number of employees in the database Counts the number of employees with the same name Groups employees by department and counts the numberReturns an error because the query is incorrect

This query counts the number of occurrences for each employee name in the "employees" table.

Q16

Single Choice

Which of the following is a valid Oracle SQL statement to create a new table named "departments"?

CREATE TABLE departments (dept_id NUMBER, dept_name VARCHAR2(50)); MAKE TABLE departments (dept_id INT, dept_name TEXT); ADD TABLE departments (dept_id INTEGER, dept_name STRING);FORM TABLE departments (dept_id NUMBER, dept_name CHAR);

The "CREATE TABLE" statement is used in Oracle SQL to create a new table, specifying column names and data types.

Q17

Single Choice

What does the following Oracle SQL statement do?

SQL Code

MERGE INTO target_table t USING source_table s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);

Updates rows in the target table and inserts new rows from the source table Only updates rows in the target table Deletes rows from the source table that don't exist in the target tableCreates a new table by merging the target and source tables

The "MERGE" statement in Oracle SQL performs an UPSERT operation, updating matching rows and inserting new rows that don’t exist in the target table.

Q18

Single Choice

What is the purpose of an Oracle SQL INDEX?

To ensure data integrity To create a unique constraint on a column To improve the speed of data retrievalTo back up the database

An index in Oracle SQL is used to improve the performance of queries by reducing the amount of data that needs to be scanned.

Q19

Single Choice

Given the following Oracle SQL code, what will be the output?

SQL Code

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;

Current date in 'YYYY-MM-DD' format System time in 'YYYY-MM-DD' format Error because TO_CHAR cannot be used with SYSDATEReturns the current time in 'YYYY-MM-DD' format

The "TO_CHAR" function formats the current system date (SYSDATE) to the specified 'YYYY-MM-DD' format.

Q20

Single Choice

Which Oracle feature allows a DBA to revert the database to a previous state without requiring a backup restoration?

Flashback Technology Restore Point RMANData Pump

Oracle's Flashback Technology allows the database to be reverted to a previous state, providing a fast recovery option without the need to restore from a backup. This is a key topic in Oracle certification exams.

Q21

Multiple Choice

Identify the SQL scripts that demonstrate database platform-specific features in Oracle.

BEGIN DBMS_OUTPUT.PUT_LINE('Hello World'); END; CREATE FUNCTION get_employee_count RETURN NUMBER IS v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM employees; RETURN v_count; END; SELECT * FROM employees WHERE ROWNUM <= 10;BEGIN SELECT employee_name INTO v_name FROM employees WHERE employee_id = 101; DBMS_OUTPUT.PUT_LINE(v_name); END;

Platform-specific features in Oracle include the use of PL/SQL blocks, functions, and procedures for complex data operations. The correct scripts will demonstrate the use of these features.

Q22

Multiple Choice

Select the SQL scripts that demonstrate proper use of Oracle-specific data types.

CREATE TABLE documents ( doc_id NUMBER PRIMARY KEY, doc_name VARCHAR2(255), doc_content CLOB ); CREATE TABLE images ( img_id NUMBER PRIMARY KEY, img_name VARCHAR2(100), img_data BLOB ); CREATE TABLE logs ( log_id NUMBER PRIMARY KEY, log_message CLOB, log_date DATE );CREATE TABLE users ( user_id INT PRIMARY KEY, user_name TEXT, user_bio MEDIUMTEXT );

Oracle-specific data types include NUMBER, VARCHAR2, CLOB, BLOB, etc. The correct scripts will use these data types appropriately within table definitions and data manipulations.

Q23

Multiple Choice

Determine the SQL scripts that correctly implement Oracle's partitioning features.

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 orders ( order_id NUMBER, order_date DATE, customer_id NUMBER ) PARTITION BY HASH (order_id) PARTITIONS 4; CREATE TABLE transactions ( trans_id NUMBER, trans_date DATE, trans_amount NUMBER ) PARTITION BY LIST (trans_date) ( PARTITION p1 VALUES ('2023'), PARTITION p2 VALUES ('2024') );CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY, customer_name VARCHAR2(255) );

Oracle's partitioning feature is used to enhance performance and manageability of large tables. The correct scripts will demonstrate the creation of partitioned tables based on range, list, or hash methods.

Q24

Multiple Choice

Identify the SQL scripts that correctly demonstrate the use of Oracle's Advanced Queuing (AQ) feature.

BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE( queue_table => 'message_queue_table', queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE' ); END; BEGIN DBMS_AQADM.CREATE_QUEUE( queue_name => 'message_queue', queue_table => 'message_queue_table' ); END; BEGIN DBMS_AQADM.ENQUEUE( queue_name => 'message_queue', enqueue_options => ENQUEUE_OPTIONS_T(), message_properties => MESSAGE_PROPERTIES_T(), payload => 'Hello, World!', msg_id => 'MSG001' ); END;SELECT * FROM message_queue_table WHERE queue_name = 'message_queue';

Oracle AQ provides a robust messaging system within the database. The correct scripts will include the creation of queues and enqueue/dequeue operations.

Q25

Multiple Choice

Choose the SQL scripts that correctly demonstrate the use of Oracle's Flashback Technology.

SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY); FLASHBACK TABLE employees TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' DAY); FLASHBACK DATABASE TO TIMESTAMP (SYSDATE - 1);SELECT * FROM employees WHERE HIRE_DATE = SYSDATE - 1;

Flashback Technology in Oracle includes Flashback Query, Flashback Table, and Flashback Database. The correct scripts will demonstrate how to use these features to recover data or undo changes.

Q26

Multiple Choice

Select the SQL scripts that correctly implement Oracle's Multitenant Architecture.

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadmin IDENTIFIED BY password ROLES = (dba); ALTER PLUGGABLE DATABASE salespdb OPEN; DROP PLUGGABLE DATABASE salespdb INCLUDING DATAFILES;CREATE DATABASE salesdb;

The Multitenant Architecture in Oracle is used to manage multiple databases as pluggable databases (PDBs) within a container database (CDB). The correct scripts will include the creation of PDBs and operations related to them.

Q27

Multiple Choice

Identify the SQL scripts that correctly implement Oracle's Data Guard feature for disaster recovery.

ALTER DATABASE ADD STANDBY DATABASE TO MANAGED RECOVERY; BEGIN; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER; ALTER DATABASE SWITCHOVER TO standbydb;ALTER SYSTEM SET log_archive_dest_2='SERVICE=standbydb';

Oracle Data Guard is used to manage, monitor, and automate the creation, management, and monitoring of one or more standby databases. The correct scripts will include commands for setting up and managing Data Guard.

Q28

Multiple Choice

Determine the SQL scripts that correctly implement Oracle's Real Application Clusters (RAC).

ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE; ALTER DATABASE OPEN CLUSTER; ALTER SYSTEM SET INSTANCE_NUMBER=2 SCOPE=SPFILE;ALTER SYSTEM SET CLUSTER_DATABASE_INSTANCES=3 SCOPE=SPFILE;

Oracle RAC provides scalability and high availability by allowing multiple instances to access a single database. The correct scripts will include commands that demonstrate the management of RAC instances.

Q29

Multiple Choice

Choose the SQL scripts that correctly demonstrate the use of Oracle's Automatic Storage Management (ASM).

CREATE DISKGROUP data_group EXTERNAL REDUNDANCY DISK '/dev/sda1', '/dev/sda2'; ALTER DISKGROUP data_group ADD DISK '/dev/sda3'; DROP DISKGROUP data_group INCLUDING CONTENTS;ALTER DISKGROUP data_group REBALANCE POWER 4;

Oracle ASM provides a high-performance storage management solution that abstracts and simplifies storage management tasks. The correct scripts will demonstrate the creation and management of disk groups using ASM.

Q30

Multiple Choice

Select the SQL scripts that correctly demonstrate the use of Oracle's Transparent Data Encryption (TDE).

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY 'password'; ALTER TABLESPACE users ENCRYPTION ONLINE USING 'AES256'; ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY 'password';ALTER DATABASE ENCRYPT TABLESPACE users;

Oracle TDE is used to encrypt sensitive data at rest. The correct scripts will demonstrate the setup of TDE, including the creation of wallets and encryption of tablespaces or columns.

Oracle Database Quiz Questions