1z0071 Oracle Datbase 12c SQL Free Practice Test — 30 Questions

Question 1

A database administrator observes that a critical reporting query, designed to list all employees without any corresponding performance review entries, is experiencing significant latency. The original query structure is `SELECT * FROM employees e WHERE NOT EXISTS (SELECT 1 FROM performance_reviews pr WHERE pr.employee_id = e.employee_id);`. The administrator needs to optimize this query by explicitly selecting only the `employee_id`, `first_name`, and `last_name` columns from the `employees` table and ensuring the `NOT EXISTS` clause remains the primary filtering mechanism. Which of the following SQL statements correctly implements this optimization while adhering to best practices for efficient data retrieval?

Accepted Answer

SELECT e.employee_id, e.first_name, e.last_name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM performance_reviews pr WHERE pr.employee_id = e.employee_id);

Question 2

A development team is implementing a new financial reporting system using Oracle Database 12c. They are concerned about data integrity and consistency, especially when multiple users might be performing complex analytical queries and simultaneous data updates. The team decides to configure the database session isolation level to `SERIALIZABLE` to prevent concurrency anomalies. If Transaction A reads a record and then Transaction B modifies and commits that same record before Transaction A attempts to re-read it, what is the most likely outcome for Transaction A?

Accepted Answer

Transaction A will receive an ORA-08177 error, indicating a serialization conflict, and will need to be retried.

Question 3

Anya, a database administrator for a rapidly growing e-commerce platform, is reviewing performance logs and identifies a frequently executed SQL query that retrieves comprehensive customer order data. The query currently uses `SELECT *` to fetch all columns from the `orders` and `order_items` tables, joined by `order_id`. While this approach provides all available information, it contributes significantly to network latency and server load due to the large number of columns often returned, many of which are not required by the consuming application. Anya needs to modify this query to adhere to best practices for performance and resource utilization. Which of the following modifications would most effectively address the performance bottleneck related to column selection in this scenario?

Accepted Answer

Explicitly list only the required columns in the SELECT clause, such as `SELECT o.order_id, o.customer_id, o.order_date, oi.item_name, oi.quantity` instead of `SELECT *`.

Question 4

Consider a scenario where a table named `product_inventory` contains two columns: `item_id` (a `VARCHAR2` type) and `stock_level` (a `NUMBER` type). You need to retrieve all items where the `stock_level` is exactly 50. If you were to execute the following query: `SELECT item_id FROM product_inventory WHERE item_id = stock_level;` and the `item_id` column contains values like `\'50\'`, `\'abc\'`, and `\'050\'`, what would be the most likely outcome of this query execution in Oracle Database 12c SQL?

Accepted Answer

The query will raise an invalid number error (ORA-01722) and return no rows.

Question 5

A developer is tasked with ensuring that critical operational events are always recorded, even if the primary database transaction fails. They implement a stored procedure that updates an `inventory` table and then, within the same procedure, uses the `PRAGMA AUTONOMOUS_TRANSACTION` directive to log the event details into an `event_history` table. If the `UPDATE` statement on `inventory` fails and the calling transaction is subsequently rolled back, what will be the state of the `event_history` table after the procedure execution and rollback?

Accepted Answer

The event will be recorded in the `event_history` table, as the autonomous transaction commits independently.

Question 6

Anya, a database administrator for a large e-commerce platform, is reviewing the performance of a critical SQL query that retrieves all order information for customers belonging to the \'Premium\' loyalty tier. The current query utilizes a subquery within the `WHERE` clause to filter orders based on customer tier. Anya suspects this subquery might be hindering optimal query execution. Considering Oracle Database 12c\'s advanced query optimization capabilities, which alternative SQL construct would Anya most likely employ to potentially improve the query\'s efficiency and allow the optimizer greater flexibility in generating an execution plan?

Accepted Answer

Rewriting the subquery using the `EXISTS` operator.

Question 7

A business analyst needs a report that lists all customers, including those who have not yet placed any orders. For each customer, the report should display their complete name (first name and last name concatenated) and the date of their most recent order. If a customer has no orders, the report should indicate \'No Orders Yet\' for the order date. Which SQL statement correctly generates this report from the `customers` and `orders` tables, assuming `customers` has `customer_id`, `first_name`, and `last_name` columns, and `orders` has `order_id`, `customer_id`, and `order_date` columns?

Accepted Answer

`SELECT NVL(CONCAT(c.first_name, ' ', c.last_name), 'Unknown Customer') AS customer_name, NVL(TO_CHAR(MAX(o.order_date), 'YYYY-MM-DD'), 'No Orders Yet') AS latest_order_date FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.first_name, c.last_name ORDER BY c.customer_id;`

Question 8

Elara, a database administrator for a multinational e-commerce firm, is tasked with fortifying the Oracle Database 12c environment to safeguard sensitive customer Personally Identifiable Information (PII). Recent audits have highlighted potential vulnerabilities related to privileged user access, and the company faces increasing pressure to comply with global data privacy regulations such as the General Data Protection Regulation (GDPR) and the California Consumer Privacy Act (CCPA). Elara needs to implement a solution that offers robust protection against unauthorized access to PII by internal personnel, including those with high-level database privileges, while maintaining the integrity of operational data. Which of the following Oracle Database 12c security features would provide the most effective strategy for this specific objective?

Accepted Answer

Implementing Oracle Database Vault to enforce strict access controls and segregation of duties, thereby preventing privileged users from accessing sensitive PII without explicit authorization.

Question 9

Analyze the provided SQL query intended to identify the three products with the highest prices from the `products` table. The table structure includes `product_id` (NUMBER), `product_name` (VARCHAR2), and `product_price` (NUMBER). The query is as follows:

```sql
SELECT product_name, product_price
FROM (
    SELECT product_name, product_price
    FROM products
    ORDER BY product_price DESC
)
WHERE ROWNUM <= 3;
```

Given this query and the table's contents, what is the precise result set that will be returned, assuming there are at least three products in the `products` table?

Accepted Answer

The names and prices of the three products with the highest prices, as ordered by `product_price` in descending order.

Question 10

Elara, a database administrator for a multinational tech firm, is tasked with generating a report on employee performance for the July 2023 period. The report needs to identify all employees who were hired in July 2023 and whose annual salary exceeds $75,000. Considering the `employees` table which contains columns `employee_id`, `first_name`, `last_name`, `hire_date` (a DATE data type), and `salary` (a NUMBER data type), which SQL statement would most effectively retrieve this specific dataset?

Accepted Answer

SELECT employee_id, first_name, last_name FROM employees WHERE TO_CHAR(hire_date, 'YYYY-MM') = '2023-07' AND salary > 75000;

Question 11

A project team is tasked with revising the pricing structure for a product catalog. Given a `products` table with columns `product_id` (NUMBER), `product_name` (VARCHAR2), `base_price` (NUMBER), and `region` (VARCHAR2), the business has mandated two distinct price adjustments: first, a 15% increase for all products in the \'APAC\' region, excluding those whose names contain \'DELUXE\'; second, a 10% decrease for products in the \'EMEA\' region, but only if their `product_id` is an even number. Which SQL statement accurately implements these changes while ensuring that only the targeted rows are affected?

Accepted Answer

UPDATE products SET base_price = CASE WHEN region = 'APAC' AND product_name NOT LIKE 'LUXE%' THEN base_price * 1.15 WHEN region = 'EMEA' AND MOD(product_id, 2) = 0 THEN base_price * 0.90 ELSE base_price END WHERE (region = 'APAC' AND product_name NOT LIKE 'LUXE%') OR (region = 'EMEA' AND MOD(product_id, 2) = 0);

Question 12

Elara, a database administrator for a large e-commerce platform, is analyzing query performance for retrieving a customer\'s complete order history. This involves joining the `customers` table, the `orders` table, and the `order_items` table. The typical query filters by a specific `customer_id` and then retrieves all associated orders and their individual items. Elara needs to implement an indexing strategy that provides the most significant performance boost for this common operation. Which of the following indexing strategies would be the most effective for optimizing this customer order history retrieval?

Accepted Answer

Create a composite index on `orders(customer_id, order_id)` and a single-column index on `order_items(order_id)`.

Question 13

During a performance tuning initiative for a critical e-commerce application, Elara, a junior database developer, identifies a frequently executed SQL query that retrieves all order details for a specific date range. The query joins the `customers` and `orders` tables and filters the `orders` table based on the `order_date` column. Initial analysis using `EXPLAIN PLAN` reveals that the `orders` table is being scanned entirely for the date filtering. Considering the immediate need to improve query execution speed for this specific filter, which of the following actions would be the most direct and effective strategy to optimize this query\'s performance, assuming no other indexing strategies are in place for the `orders` table that would directly benefit this filter?

Accepted Answer

Create an index on the `order_date` column of the `orders` table.

Question 14

Elara, a database administrator for a rapidly growing e-commerce platform, is tasked with updating the `product_catalog` table. The business now requires a mandatory `warranty_period_months` column for all products, and new products will have a default warranty of 12 months. Elara must implement this change with minimal impact on the live system, ensuring the application remains available and users can continue browsing and purchasing products without interruption. Which SQL statement best achieves this objective in Oracle Database 12c?

Accepted Answer

ALTER TABLE product_catalog MODIFY (warranty_period_months NUMBER DEFAULT 12 NOT NULL);

Question 15

Elara, a data analyst at \'Innovate Solutions\', is reviewing the performance of an SQL query designed to retrieve order details for active customers. The current query, which effectively returns the desired information, utilizes a subquery within the `WHERE` clause to filter orders based on the customer\'s status. Recognizing the potential for performance enhancements, Elara aims to refactor this query to leverage more efficient SQL constructs. Given the database schema where `orders` table contains `order_id`, `order_date`, `total_amount`, and `customer_id`, and the `customers` table contains `customer_id` and `status`, which of the following rewritten queries would most likely offer superior performance for retrieving order details only for customers with a \'ACTIVE\' status?

Accepted Answer

SELECT o.order_id, o.order_date, o.total_amount FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id WHERE c.status = 'ACTIVE'

Question 16

Anya, a database administrator for a global financial institution, is investigating performance degradation in a critical reporting module. Users are experiencing significant delays when generating monthly account summaries, which involves joining the `transactions` table (containing millions of records) with the `accounts` table. Analysis of the application\'s most frequent queries reveals that they consistently filter the `transactions` table by `account_id` and `transaction_date`, and often sort the results by `transaction_date`. Anya decides to implement a specific indexing strategy to mitigate this bottleneck. Which of the following indexing strategies would most effectively address Anya\'s performance concerns for these types of queries?

Accepted Answer

Create a composite index on the `transactions` table including `account_id` and `transaction_date`.

Question 17

Consider a scenario where the database administrator for a large retail corporation needs to extract a list of all employees who are based in offices located within \'New York\' and earn an annual salary greater than 75,000. Furthermore, the administrator requires this data to be presented in a way that first shows employees by their hiring date, from earliest to latest, and then, for employees hired on the same day, by their salary, from highest to lowest. Which SQL statement would accurately fulfill these requirements?

Accepted Answer

SELECT employee_id, first_name, last_name, salary, hire_date FROM employees WHERE department_location = 'New York' AND salary > 75000 ORDER BY hire_date ASC, salary DESC

Question 18

Elara, a database administrator for a global logistics firm, is overseeing the performance of their Oracle Database 12c instance. A critical nightly batch job, responsible for consolidating shipment data and updating inventory levels, has begun to exhibit unpredictable slowdowns. The job\'s SQL statements, which involve complex joins across several large tables such as `shipments`, `inventory_status`, and `customer_orders`, are the primary suspects. Elara suspects that the optimizer might be making suboptimal choices due to outdated or insufficient statistical information about the underlying data distribution, especially considering recent spikes in shipment volume and the introduction of new product lines. Which of the following actions would most effectively address Elara\'s concern by promoting adaptability and enabling the optimizer to generate efficient execution plans based on current data characteristics, while also demonstrating initiative in proactive system maintenance?

Accepted Answer

Execute `DBMS_STATS.GATHER_SCHEMA_STATS` for the entire `LOGISTICS` schema, utilizing `ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE` and `METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'` to ensure comprehensive and up-to-date statistics for all objects, thereby empowering the optimizer.

Question 19

A database administrator is tasked with establishing referential integrity between the `employees` table and the `departments` table by adding a foreign key constraint on the `employees.department_id` column, referencing `departments.department_id`. During an audit, it was discovered that a small number of employee records might have a `department_id` that does not currently exist in the `departments` table due to a previous data import anomaly. The requirement is to add the foreign key constraint without immediately failing the `ALTER TABLE` statement due to these potential existing data violations, while ensuring that future data modifications will adhere to the constraint. Which of the following `ALTER TABLE` statements correctly implements this requirement?

Accepted Answer

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

Question 20

A data analytics team is tasked with identifying employees whose compensation exceeds the departmental average, alongside those unassigned to any department, from a company\'s `employees` table which contains `employee_id`, `first_name`, `last_name`, `salary`, and `department_id` columns. The `department_id` column can contain `NULL` values. Which SQL statement accurately retrieves this specific employee subset?

Accepted Answer

SELECT e.employee_id, e.first_name, e.last_name, e.salary FROM employees e WHERE e.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e.department_id) OR e.department_id IS NULL

Question 21

Consider a scenario where a database administrator is tasked with synchronizing an archive table (`target_table`) with a staging table (`source_table`). The administrator implements a `MERGE` statement with two `WHEN MATCHED` clauses. The first `WHEN MATCHED` clause is designed to delete rows from `target_table` if their `status` column is \'INACTIVE\'. The second `WHEN MATCHED` clause, which has a lower priority in the statement\'s structure, is intended to update rows in `target_table` if their `last_updated` column is older than 30 days. A `WHEN NOT MATCHED` clause is also present to insert new records from `source_table` into `target_table`. If a row exists in both tables, its `status` is \'INACTIVE\', and its `last_updated` date is 45 days ago, what will be the ultimate state of this specific row in `target_table` after the `MERGE` operation completes?

Accepted Answer

The row will be deleted from `target_table`.

Question 22

A database administrator is tasked with retrieving all employee records from the `employees` table where the `hire_date` falls on October 26, 2023. The database environment\'s National Language Support (NLS) settings for date format are not guaranteed to be consistent across different sessions. Which of the following SQL statements will reliably achieve this objective, ensuring accurate filtering regardless of the session\'s default date format?

Accepted Answer

`SELECT * FROM employees WHERE TO_CHAR(hire_date, 'YYYY-MM-DD') = '2023-10-26';`

Question 23

Elara, a database administrator for a rapidly growing e-commerce platform, is investigating performance degradation in a critical query that retrieves all orders for a specific customer, sorted by the date the order was placed. The `Orders` table is substantial, containing millions of records, and this query is executed frequently by the customer-facing application. Analysis of query execution plans reveals that the database is performing a full table scan for this operation. Elara needs to implement a database object to significantly enhance the retrieval speed of these customer-specific, chronologically ordered order lists, adhering to best practices for composite key indexing in Oracle Database 12c. Which of the following SQL statements would most effectively address this performance bottleneck?

Accepted Answer

CREATE INDEX idx_orders_cust_date ON Orders (customer_id, order_date);

Question 24

Anya, a database administrator for a global retail company, is tasked with generating a report detailing all personnel currently assigned to a specific operational division. She needs to ensure that only employees who are definitively linked to an existing department within the company\'s organizational structure are included in the output. Anya is considering different SQL join strategies to achieve this, prioritizing data integrity and the accurate representation of departmental assignments. Which join type would most effectively fulfill Anya\'s requirement of listing employees exclusively with their corresponding department names, excluding any employees who might not have a departmental affiliation recorded or whose affiliation points to a non-existent department?

Accepted Answer

INNER JOIN

Question 25

A database administrator is reviewing SQL queries executed against a custom HR schema in Oracle Database 12c. The `employees` table within this schema contains an `employee_id` column of data type `NUMBER` and a `last_name` column of data type `VARCHAR2`. A junior developer has submitted the following query for review:

```sql
SELECT employee_id, last_name
FROM employees
WHERE last_name = 101;
```

Based on Oracle\'s implicit data type conversion rules and the potential content of the `last_name` column, what is the most probable outcome of executing this query?

Accepted Answer

The query will raise an ORA-01722: invalid number error due to attempting to implicitly convert the `VARCHAR2` `last_name` to a `NUMBER`.

Question 26

During the performance tuning of a critical customer order summary report, Elara, a database administrator, observes a significant degradation in query response times. The report frequently filters orders based on a date range and also involves joins or filters related to the customer who placed the order. To enhance the report\'s efficiency, Elara is considering the creation of a new index on the `orders` table. Which of the following SQL statements represents the most effective strategy for improving the performance of queries that commonly filter by `order_date` and then utilize `customer_id`?

Accepted Answer

CREATE INDEX orders_date_cust_idx ON orders (order_date, customer_id);

Question 27

Kaelen, a senior database administrator, is onboarding Lyra, a new developer, to a project. Lyra requires access to the `project_tasks` table to analyze existing task data for an upcoming feature development. Kaelen must ensure Lyra can only read all data from this table and is strictly prohibited from making any changes or additions. Which SQL statement correctly implements these precise access controls for Lyra?

Accepted Answer

GRANT SELECT ON project_tasks TO lyra;

Question 28

A database developer is tasked with optimizing a complex SQL query in Oracle Database 12c that retrieves aggregated sales data for each region, requiring calculations across millions of transaction records. The current query, involving multiple joins and subqueries to determine the total revenue and average transaction value per region for the previous fiscal year, exhibits significant performance issues, especially during peak operational hours. The developer needs to implement a strategy that pre-computes and stores these aggregated results to drastically reduce query execution time for frequently accessed reports, without altering the fundamental structure of the underlying transactional tables. Which Oracle SQL feature is best suited to address this performance bottleneck by providing readily available, pre-calculated aggregate data?

Accepted Answer

Creating a materialized view that stores the pre-computed regional sales aggregates.

Question 29

A database administrator is reviewing a SQL query written by a junior developer. The query aims to retrieve the average salary for departments that have more than 5 employees and are located in the \'Sales\' region. The developer has used the following statement:

```sql
SELECT department_id, AVG(salary)
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE region = \'Sales\')
GROUP BY department_id
HAVING COUNT(*) > 5 AND department_name = \'Marketing\';
```

Upon execution, the query fails with an Oracle error indicating an invalid relational operator or function. What is the most accurate explanation for this error and the necessary correction?

Accepted Answer

The `HAVING` clause incorrectly references `department_name`, which is not included in the `GROUP BY` clause nor is it an aggregate function; this condition should be moved to the `WHERE` clause.

Question 30

Consider a scenario in Oracle Database 12c where a table named `projects` has a primary key `project_id`, and another table, `tasks`, has a foreign key `project_id` that references `projects.project_id`. This foreign key is defined with the `ON DELETE CASCADE` clause. If a database administrator executes a `DELETE` statement targeting a specific row in the `projects` table, what is the most accurate and comprehensive outcome regarding the data in both tables within the context of the executing transaction?

Accepted Answer

The specified project row is removed from the `projects` table, and all rows in the `tasks` table that reference this `project_id` are automatically removed as well.

1z0071 Oracle Datbase 12c SQL Free Practice Test — 30 Questions

A lot more is already mapped out

About the 1z0071 Oracle Datbase 12c SQL Certification