Test Your Oracle Cardinality Knowledge with This SQL Quiz

Progress

True / False

In Oracle, cardinality refers to the uniqueness of data values in a column.

True False

Cardinality refers to the number of unique values in a column. High cardinality means a column has many unique values, while low cardinality means it has few unique values.

True / False

High cardinality indicates that a column has a large number of duplicate values.

True False

High cardinality means a column has many unique values, not many duplicates. Low cardinality would indicate many duplicates.

True / False

In Oracle, cardinality is an important factor in query optimization.

True False

Cardinality is used by the Oracle optimizer to determine the most efficient way to execute a query.

True / False

Oracle uses statistics to estimate the cardinality of columns in a table.

True False

Oracle gathers and uses statistics about the data distribution and cardinality of columns to make informed decisions about query optimization.

True / False

The DBMS_STATS package in Oracle is used to collect statistics, including cardinality, for the optimizer.

True False

The DBMS_STATS package is used to gather statistics on tables, indexes, and columns, which includes information about cardinality.

True / False

In Oracle, low cardinality columns are often good candidates for bitmap indexes.

True False

Bitmap indexes are typically more efficient for columns with low cardinality, where there are few unique values.

True / False

In Oracle, the estimated cardinality can be viewed in the execution plan of a query.

True False

The execution plan shows the estimated cardinality for each step, helping to understand how Oracle plans to execute the query.

True / False

In Oracle, histograms can be used to improve cardinality estimates for columns with skewed data distributions.

True False

Histograms provide detailed information about the distribution of values in a column, helping Oracle to make better cardinality estimates.

True / False

In Oracle, a full table scan always has higher cardinality than an indexed access path.

True False

Cardinality refers to the number of unique values in a result set, not the method of accessing data. A full table scan may have the same cardinality as an indexed access path depending on the query.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on understanding and interpreting cardinality, its impact on query optimization, and how to gather accurate statistics using Oracle tools.

True False

The OCP certification covers advanced topics, including understanding and interpreting cardinality, its impact on query performance, and the use of tools like DBMS_STATS to gather accurate statistics.

Q11

Single Choice

What does "cardinality" refer to in the context of Oracle databases?

The number of tables in a database The number of rows in a table The number of columns in a tableThe number of unique values in a column

In Oracle databases, "cardinality" typically refers to the number of rows in a table or the number of rows that are returned by a query.

Q12

Single Choice

Which of the following statements is true about high cardinality in Oracle?

High cardinality means there are many rows with the same value. High cardinality means there are many distinct values in a column. High cardinality means there are few rows in a table.High cardinality only applies to primary keys.

High cardinality refers to a column containing a large number of unique or distinct values.

Q13

Single Choice

In Oracle, which type of cardinality would a column like "Employee ID" typically have?

Low cardinality High cardinality Moderate cardinalityConstant cardinality

Employee ID" typically has high cardinality because each employee ID is unique, resulting in a large number of distinct values.

Q14

Single Choice

Consider the following Oracle SQL query:What does the query demonstrate in terms of cardinality?

SQL Code

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

The query calculates the cardinality of the employees table. The query groups rows by department and returns the cardinality of each group. The query filters rows with high cardinality.The query creates a new table with low cardinality.

The query groups the rows by department_id and counts the number of rows in each group, demonstrating the concept of cardinality within each group.

Q15

Single Choice

Which of the following could be an indicator of low cardinality in an Oracle table?

A column where most values are NULL A column where each value is unique A column with many distinct valuesA column with only a few distinct values

Low cardinality means there are few distinct values in a column, so many rows share the same values.

Q16

Single Choice

Given the Oracle SQL query:What is this query demonstrating?

SQL Code

SELECT job_id
FROM employees
GROUP BY job_id
HAVING COUNT(*) > 50;

It filters jobs with low cardinality. It filters jobs with high cardinality. It calculates the total number of jobs.It displays the cardinality of the employees table.

The query groups by job_id and filters those groups where the count is greater than 50, indicating jobs with higher cardinality (more employees).

Q17

Single Choice

What impact does cardinality have on Oracle query optimization?

Low cardinality columns are always indexed first. High cardinality columns are less likely to be indexed. Cardinality influences the choice of index and join methods.Cardinality has no impact on query optimization.

Cardinality affects how Oracle optimizes queries, including decisions about which indexes to use and which join methods are most efficient.

Q18

Single Choice

Consider this Oracle SQL query:If the sale_date column has low cardinality, what might Oracle do to optimize this query?

SQL Code

SELECT product_id
FROM sales
WHERE sale_date = '2024-08-21';

Use a full table scan Use an index on product_id Use an index on sale_dateAvoid using any indexes

If sale_date has low cardinality (few distinct values), Oracle might choose a full table scan over an index because many rows might have the same sale_date.

Q19

Single Choice

In Oracle, how might you increase the cardinality of a column in a table?

By adding more distinct values to the column By removing rows from the table By indexing the columnBy creating a foreign key constraint on the column

Increasing the number of distinct values in a column increases its cardinality.

Q20

Single Choice

Consider the following Oracle SQL query scenario:If department_id has low cardinality, how might this affect query performance?

SQL Code

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

The query will always run faster. The optimizer may choose a hash join over a nested loop join. The query may take longer if many rows belong to the same department_id.The optimizer will avoid using indexes altogether.

If department_id has low cardinality, there are fewer distinct departments, which means more rows per group, potentially leading to longer query execution times.

Q21

Multiple Choice

Identify the correct SQL statements that define cardinality in a one-to-many relationship between tables in Oracle.

SQL Code

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

CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, department_id NUMBER, CONSTRAINT fk_dept_emp FOREIGN KEY (department_id) REFERENCES departments(department_id) ); ALTER TABLE employees ADD CONSTRAINT fk_dept_emp FOREIGN KEY (department_id) REFERENCES departments(department_id); ALTER TABLE departments ADD CONSTRAINT fk_dept_emp FOREIGN KEY (department_id) REFERENCES employees(employee_id);CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, department_id NUMBER );

Cardinality in a one-to-many relationship ensures that each record in the parent table ('departments') can be associated with multiple records in the child table ('employees'). The correct SQL syntax involves using a FOREIGN KEY constraint to enforce this relationship.

Q22

Multiple Choice

Select the correct SQL statements to enforce cardinality in a many-to-many relationship using a junction table in Oracle.

CREATE TABLE enrollments ( student_id NUMBER, course_id NUMBER, CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES students(student_id), CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES courses(course_id) ); CREATE TABLE enrollments ( enrollment_id NUMBER PRIMARY KEY, student_id NUMBER, course_id NUMBER, CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES students(student_id) ); CREATE TABLE enrollments ( student_id NUMBER, course_id NUMBER, CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES students(student_id), CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES courses(course_id) );CREATE TABLE students_courses ( student_id NUMBER, course_id NUMBER, CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES students(student_id), CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES courses(course_id) );

A many-to-many relationship in Oracle is typically implemented using a junction table that contains foreign keys referencing the primary keys of the two related tables. This ensures proper cardinality.

Q23

Multiple Choice

Determine the SQL statements that will correctly define cardinality in a one-to-one relationship between two tables.

CREATE TABLE users ( user_id NUMBER PRIMARY KEY, username VARCHAR2(50) ); CREATE TABLE user_profiles ( profile_id NUMBER PRIMARY KEY, user_id NUMBER UNIQUE, CONSTRAINT fk_user_profile FOREIGN KEY (user_id) REFERENCES users(user_id) ); CREATE TABLE user_profiles ( profile_id NUMBER PRIMARY KEY, user_id NUMBER, CONSTRAINT fk_user_profile FOREIGN KEY (user_id) REFERENCES users(user_id) ); ALTER TABLE user_profiles ADD CONSTRAINT fk_user_profile FOREIGN KEY (user_id) REFERENCES users(user_id);CREATE TABLE user_profiles ( profile_id NUMBER PRIMARY KEY, user_id NUMBER, CONSTRAINT fk_user_profile FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE );

A one-to-one relationship in Oracle is enforced by ensuring that both tables have primary keys and that the child table's primary key is also a foreign key referencing the parent table's primary key.

Q24

Multiple Choice

Which of the following SQL statements will correctly define the cardinality?

CREATE TABLE categories ( category_id NUMBER PRIMARY KEY, category_name VARCHAR2(100), parent_category_id NUMBER, CONSTRAINT fk_parent_category FOREIGN KEY (parent_category_id) REFERENCES categories(category_id) ); ALTER TABLE categories ADD CONSTRAINT fk_parent_category FOREIGN KEY (parent_category_id) REFERENCES categories(category_id); CREATE TABLE categories ( category_id NUMBER PRIMARY KEY, category_name VARCHAR2(100) );CREATE TABLE categories ( category_id NUMBER PRIMARY KEY, parent_category_id NUMBER, CONSTRAINT fk_parent_category FOREIGN KEY (category_id) REFERENCES categories(parent_category_id) );

Hierarchical relationships in Oracle are often implemented within a single table using a self-referencing FOREIGN KEY constraint. This structure is common in categories, organizations, and other hierarchical data models.

Q25

Multiple Choice

Choose the SQL statements that ensure correct cardinality when joining three related tables in Oracle.

SELECT o.order_id, oi.item_id, p.product_name FROM orders o JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id; SELECT o.order_id, oi.item_id, p.product_name FROM orders o LEFT JOIN order_items oi ON o.order_id = oi.order_id LEFT JOIN products p ON oi.product_id = p.product_id; SELECT o.order_id, oi.item_id, p.product_name FROM orders o FULL JOIN order_items oi ON o.order_id = oi.order_id FULL JOIN products p ON oi.product_id = p.product_id;SELECT o.order_id, oi.item_id, p.product_name FROM orders o INNER JOIN order_items oi ON o.order_id = oi.order_id INNER JOIN products p ON oi.product_id = p.product_id;

When joining multiple tables, it is essential to maintain the correct cardinality to ensure that the resulting dataset accurately represents the relationships between the tables. The correct SQL statements will include proper JOIN clauses that respect these relationships.

Q26

Multiple Choice

Which SQL statements will enforce correct cardinality when inserting data into a junction table in Oracle?

INSERT INTO project_assignments (employee_id, project_id) VALUES (101, 2001); INSERT INTO project_assignments (employee_id, project_id) SELECT e.employee_id, p.project_id FROM employees e, projects p WHERE e.employee_id = 101 AND p.project_id = 2001; INSERT INTO project_assignments (employee_id, project_id) VALUES (101, NULL);INSERT INTO project_assignments (employee_id, project_id) VALUES (NULL, 2001);

Inserting data into a junction table requires ensuring that the foreign keys reference valid records in the related tables. The correct SQL statements will include the proper INSERT commands that maintain this relationship.

Q27

Multiple Choice

Determine the SQL statements that correctly handle cardinality when performing a complex query involving multiple joins in Oracle.

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

When performing complex queries with multiple joins, it's crucial to ensure that the cardinality is respected, so the resulting data reflects the correct relationships between the tables.

Q28

Multiple Choice

Identify the SQL statements that correctly define cardinality constraints when creating a new table in Oracle.

CREATE TABLE subscriptions ( subscription_id NUMBER PRIMARY KEY, customer_id NUMBER, CONSTRAINT fk_customer_subscription FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); CREATE TABLE subscriptions ( subscription_id NUMBER, customer_id NUMBER, CONSTRAINT fk_customer_subscription FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); ALTER TABLE subscriptions ADD CONSTRAINT fk_customer_subscription FOREIGN KEY (customer_id) REFERENCES customers(customer_id);CREATE TABLE subscriptions ( subscription_id NUMBER PRIMARY KEY, customer_id NUMBER, CONSTRAINT fk_customer_subscription FOREIGN KEY (subscription_id) REFERENCES customers(customer_id) );

Creating a table with a FOREIGN KEY constraint in Oracle involves defining the relationship between the tables to enforce the correct cardinality. The correct SQL statements will ensure that each subscription is linked to one customer, and each customer can have multiple subscriptions.

Q29

Multiple Choice

Select the SQL statements that define cardinality in a self-referencing table scenario in Oracle.

CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, manager_id NUMBER, CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employees(employee_id) ); ALTER TABLE employees ADD CONSTRAINT fk_manager FOREIGN KEY (manager_id) REFERENCES employees(employee_id); CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, manager_id NUMBER, CONSTRAINT fk_manager FOREIGN KEY (employee_id) REFERENCES employees(manager_id) );CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, manager_id NUMBER );

A self-referencing table in Oracle requires a FOREIGN KEY constraint that references the same table to define a hierarchical relationship, such as employees reporting to a manager.

Q30

Multiple Choice

Choose the SQL statements that enforce cardinality when creating indexes on foreign keys in Oracle.

CREATE INDEX idx_order ON order_items (order_id); CREATE INDEX idx_product ON order_items (product_id); CREATE INDEX idx_order_product ON order_items (order_id, product_id);CREATE UNIQUE INDEX idx_order ON order_items (order_id);

Creating indexes on foreign keys in Oracle helps improve query performance while ensuring that the cardinality of the relationships between the tables is maintained. The correct SQL statements will include the CREATE INDEX command on the foreign key columns.

Oracle Database Quiz Questions