Test Your Oracle RDBMS Knowledge with This Challenging Quiz

Progress

True / False

RDBMS stands for Relational Database Management System.

True False

RDBMS is an acronym for Relational Database Management System, which is a database system based on a relational model.

True / False

In an RDBMS like Oracle, data is stored in tables.

True False

In an RDBMS, data is organized in tables consisting of rows and columns.

True / False

Oracle is an example of an RDBMS.

True False

Oracle Database is a widely used RDBMS that supports SQL and PL/SQL.

True / False

In an RDBMS, tables can have relationships with other tables using foreign keys.

True False

Tables in an RDBMS can be linked using foreign keys, which enforce referential integrity between related tables.

True / False

In Oracle, an RDBMS supports ACID properties to ensure reliable transactions.

True False

RDBMS systems like Oracle support ACID (Atomicity, Consistency, Isolation, Durability) properties to ensure reliable and consistent transactions.

True / False

In an RDBMS, data redundancy is reduced through normalization.

True False

Normalization in an RDBMS minimizes data redundancy by organizing data into related tables.

True / False

In Oracle RDBMS, a view is a virtual table that is created by a query.

True False

A view in Oracle is a virtual table that is created by a SQL query and can be used like a regular table.

True / False

Oracle RDBMS supports both relational and non-relational data models natively.

True False

While Oracle RDBMS primarily supports the relational model, Oracle also offers other database products like Oracle NoSQL Database for non-relational data models.

True / False

In an Oracle RDBMS, stored procedures and functions can be used to encapsulate complex business logic.

True False

Stored procedures and functions in Oracle RDBMS can encapsulate complex business logic and can be reused across applications.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on the fundamental concepts of RDBMS, including table relationships, SQL querying, and transaction management.

True False

The OCP certification covers advanced topics, including fundamental concepts of RDBMS, such as table relationships, SQL querying, and transaction management, to ensure a thorough understanding of relational database principles.

Q11

Single Choice

What does the acronym RDBMS stand for in Oracle?

Relational Database Management System Real-time Data Management System Relational Data Management SystemReal-time Database Management System

RDBMS stands for Relational Database Management System, a type of database system that stores data in related tables.

Q12

Single Choice

Which of the following is a characteristic of an RDBMS like Oracle?

Data is stored in flat files Data is organized into tables, columns, and rows Data is unstructured and stored in a NoSQL formatData is stored in key-value pairs

An RDBMS stores data in structured formats like tables with rows and columns, which can be related to each other.

Q13

Single Choice

In Oracle RDBMS, what is a "primary key"?

A column that allows duplicate values A column or set of columns that uniquely identify a row in a table A key that connects two tablesA key that allows null values

A primary key is a unique identifier for each row in a table in an RDBMS.

Q14

Single Choice

Which of the following SQL commands is used to define a new table in an Oracle RDBMS?

SELECT INSERT CREATE TABLEUPDATE

The CREATE TABLE command is used to define a new table in an Oracle RDBMS, specifying its columns and data types.

Q15

Single Choice

In Oracle RDBMS, how are relationships between tables typically established?

By using foreign keys By using indexes By using viewsBy using triggers

Relationships between tables in an RDBMS are established using foreign keys, which link one table's column(s) to another table's primary key.

Q16

Single Choice

Given the Oracle SQL query:What does this query do?

SQL Code

CREATE TABLE employees (
 employee_id NUMBER PRIMARY KEY,
 department_id NUMBER,
 first_name VARCHAR2(50),
 last_name VARCHAR2(50)
);

Creates a view named employees Creates a table named employees with four columns Inserts data into the employees tableDeletes the employees table

The query creates a table named employees with specified columns, where employee_id is the primary key.

Q17

Single Choice

In Oracle RDBMS, what is normalization?

The process of creating redundant data The process of optimizing database queries The process of organizing data to reduce redundancy and improve data integrityThe process of denormalizing data for faster access

Normalization in RDBMS is the process of structuring data to minimize redundancy and ensure data integrity.

Q18

Single Choice

Which of the following normal forms in Oracle RDBMS ensures that there are no transitive dependencies?

First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF)Boyce-Codd Normal Form (BCNF)

Third Normal Form (3NF) ensures that there are no transitive dependencies, meaning non-key attributes should not depend on other non-key attributes.

Q19

Single Choice

What is the main advantage of using an RDBMS like Oracle for managing data?

It allows storing data in an unstructured format It supports complex queries and transactions, ensuring data integrity and consistency It is simpler to use than a NoSQL databaseIt does not require schema definitions

An RDBMS like Oracle supports complex queries and transactions, which helps in maintaining data integrity and consistency.

Q20

Single Choice

Consider the following SQL code in an Oracle RDBMS:What does this query demonstrate in the context of an RDBMS?

SQL Code

SELECT e.employee_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;

The use of views in RDBMS The creation of a new table in RDBMS The relational aspect of an RDBMS by joining two tables and filtering dataThe normalization process

The query demonstrates how an RDBMS like Oracle allows joining multiple tables based on relationships (foreign keys) and filtering the data using conditions.

Q21

Multiple Choice

Identify the SQL statements that demonstrate the fundamental concept of a relational database.

SQL Code

CREATE TABLE customers (
 customer_id NUMBER PRIMARY KEY,
 customer_name VARCHAR2(100)
);
CREATE TABLE orders (
 order_id NUMBER PRIMARY KEY,
 customer_id NUMBER,
 CONSTRAINT fk_customer_order FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO customers (customer_id, customer_name) VALUES (1, 'John Doe'); INSERT INTO orders (order_id, customer_id) VALUES (101, 1); INSERT INTO orders (order_id, customer_id) VALUES (102, NULL);INSERT INTO customers (customer_id, customer_name) VALUES (2, 'Jane Smith');

In a relational database, tables are related using keys, such as a primary key and a foreign key. The correct options will ensure relational integrity, meaning the relationships between tables are correctly enforced.

Q22

Multiple Choice

Select the SQL statements that correctly implement normalization in a relational database.

CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, department_name VARCHAR2(100) ); INSERT INTO departments (department_id, department_name) VALUES (1, 'HR'); ALTER TABLE employees ADD department_id NUMBER; UPDATE employees SET department_id = 1 WHERE department_name = 'HR'; CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, employee_name VARCHAR2(100), department_id NUMBER, CONSTRAINT fk_employee_dept FOREIGN KEY (department_id) REFERENCES departments(department_id) );DROP TABLE employees;

Normalization in RDBMS involves organizing data to reduce redundancy and improve data integrity. The correct options will move related data into separate tables, linked by foreign keys.

Q23

Multiple Choice

Which of the following SQL statements correctly use joins to retrieve this data?

SELECT c.customer_name, o.order_id FROM customers c JOIN orders o ON c.customer_id = o.customer_id; SELECT c.customer_name, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id; SELECT c.customer_name, o.order_id FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id;SELECT c.customer_name, o.order_id FROM customers c FULL JOIN orders o ON c.customer_id = o.customer_id;

Joins in RDBMS are used to combine rows from two or more tables based on a related column. The correct options will demonstrate how to use different types of joins to retrieve the combined data.

Q24

Multiple Choice

Identify the SQL statements that enforce referential integrity in a relational database.

SQL Code

CREATE TABLE products (
 product_id NUMBER PRIMARY KEY,
 product_name VARCHAR2(100)
);
CREATE TABLE order_items (
 item_id NUMBER PRIMARY KEY,
 product_id NUMBER,
 CONSTRAINT fk_product_orderitem FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO products (product_id, product_name) VALUES (1, 'Laptop'); INSERT INTO order_items (item_id, product_id) VALUES (101, 1); INSERT INTO order_items (item_id, product_id) VALUES (102, 2);DELETE FROM products WHERE product_id = 1;

Referential integrity in RDBMS ensures that foreign key values in a child table match primary key values in a parent table. The correct SQL statements will demonstrate this concept by enforcing the relationship between tables.

Q25

Multiple Choice

Choose the SQL statements that demonstrate the use of keys in maintaining uniqueness and relationships in a relational database.

CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, department_name VARCHAR2(100) ); CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, employee_name VARCHAR2(100), department_id NUMBER, CONSTRAINT fk_employee_dept FOREIGN KEY (department_id) REFERENCES departments(department_id) ); CREATE TABLE employees ( employee_id NUMBER, employee_name VARCHAR2(100) );ALTER TABLE employees ADD CONSTRAINT fk_employee_dept FOREIGN KEY (department_id) REFERENCES departments(department_id);

Keys in RDBMS are used to uniquely identify rows in a table and establish relationships between tables. The correct SQL statements will include the creation of primary and foreign keys to maintain data integrity.

Q26

Multiple Choice

Which SQL statements demonstrate the concept of indexing in a relational database?

CREATE INDEX idx_order_id ON orders (order_id); CREATE INDEX idx_customer_id ON orders (customer_id); CREATE UNIQUE INDEX idx_order_id ON orders (order_id);CREATE INDEX idx_order_customer ON orders (order_id, customer_id);

Indexing in RDBMS is used to speed up the retrieval of rows by using pointers. The correct SQL statements will include the creation of indexes on columns that are frequently used in query conditions.

Q27

Multiple Choice

Determine the SQL statements that demonstrate transaction management in a relational database.

BEGIN TRANSACTION; INSERT INTO accounts (account_id, balance) VALUES (1, 1000); INSERT INTO transactions (transaction_id, account_id, amount) VALUES (101, 1, 100); COMMIT; BEGIN TRANSACTION; INSERT INTO accounts (account_id, balance) VALUES (1, 1000); ROLLBACK; BEGIN TRANSACTION; INSERT INTO transactions (transaction_id, account_id, amount) VALUES (101, 1, 100); COMMIT;INSERT INTO accounts (account_id, balance) VALUES (1, 1000); INSERT INTO transactions (transaction_id, account_id, amount) VALUES (101, 1, 100);

Transactions in RDBMS ensure that a series of operations either complete successfully or none of them do. The correct SQL statements will include BEGIN TRANSACTION, COMMIT, and ROLLBACK clauses to manage the transaction.

Q28

Multiple Choice

Identify the SQL statements that demonstrate the concept of data integrity in a relational database.

ALTER TABLE employees ADD CONSTRAINT fk_employee_dept FOREIGN KEY (department_id) REFERENCES departments(department_id); ALTER TABLE employees ADD CONSTRAINT chk_department CHECK (department_id IS NOT NULL); CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, department_id NUMBER, CONSTRAINT fk_employee_dept FOREIGN KEY (department_id) REFERENCES departments(department_id) );CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, department_id NUMBER, CONSTRAINT chk_department CHECK (department_id > 0) );

Data integrity in RDBMS is maintained by enforcing rules that prevent invalid data from being entered into the database. The correct SQL statements will use foreign key constraints and checks to enforce data integrity.

Q29

Multiple Choice

Select the SQL statements that demonstrate the use of stored procedures in a relational database.

CREATE OR REPLACE PROCEDURE add_employee( p_employee_id NUMBER, p_employee_name VARCHAR2 ) AS BEGIN INSERT INTO employees (employee_id, employee_name) VALUES (p_employee_id, p_employee_name); COMMIT; END; CREATE PROCEDURE add_employee( p_employee_id NUMBER, p_employee_name VARCHAR2 ) AS BEGIN INSERT INTO employees (employee_id, employee_name) VALUES (p_employee_id, p_employee_name); INSERT INTO employee_log (employee_id, action) VALUES (p_employee_id, 'INSERT'); COMMIT; END; CREATE OR REPLACE PROCEDURE add_employee( p_employee_id NUMBER, p_employee_name VARCHAR2 ) AS BEGIN INSERT INTO employees (employee_id, employee_name) VALUES (p_employee_id, p_employee_name); INSERT INTO employee_log (employee_id, action) VALUES (p_employee_id, 'INSERT'); COMMIT; END;CREATE PROCEDURE add_employee( p_employee_id NUMBER, p_employee_name VARCHAR2 ) AS BEGIN INSERT INTO employees (employee_id, employee_name) VALUES (p_employee_id, p_employee_name); END;

Stored procedures in RDBMS allow you to encapsulate complex logic into a single callable routine. The correct SQL statements will include the creation of a stored procedure with the appropriate logic to insert data and log operations.

Q30

Multiple Choice

Choose the SQL statements that demonstrate the use of views in a relational database.

CREATE VIEW emp_dept_view AS SELECT e.employee_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; CREATE VIEW emp_dept_view AS SELECT e.employee_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id; CREATE VIEW emp_dept_view AS SELECT e.employee_name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;CREATE VIEW emp_dept_view AS SELECT e.employee_name, d.department_name FROM employees e FULL JOIN departments d ON e.department_id = d.department_id;

Views in RDBMS are virtual tables that provide a simplified representation of data from one or more tables. The correct SQL statements will create a view that combines data from multiple tables into a single queryable entity.

Oracle Database Quiz Questions