1z0061 Oracle Database 12c: SQL Fundamentals Free Practice Test — 29 Questions

Question 1

Anya, a database administrator for a rapidly growing tech firm, is troubleshooting a critical performance bottleneck. A frequently executed SQL query, intended to list all employees assigned to active projects within their respective departments, is consistently exceeding its allocated execution time. The query joins the `Employees` table (with a composite primary key of `employee_id` and `hire_date`), the `Departments` table (primary key `department_id`), and the `Projects` table (composite primary key of `project_id` and `start_date`). The join conditions are `Employees.department_id = Departments.department_id` and `Employees.employee_id = Projects.assigned_employee_id`. The `Projects` table also contains a `status` column which is frequently filtered to \'Active\'. Given the database\'s significant volume of data, Anya suspects suboptimal indexing is contributing to the timeouts. Which of the following indexing strategies would most likely resolve the performance issue for this specific query, assuming no other relevant indexes exist?

Accepted Answer

Create a composite index on `Employees(employee_id, department_id)` and `Projects(assigned_employee_id)`, and a single-column index on `Departments(department_id)`.

Question 2

Elara, a database administrator, is tasked with generating a report for the sales department. The report requires a list of all customers, displaying their complete names and the date of their most recent order. She has access to two tables: `customers` (with columns `customer_id`, `first_name`, `last_name`) and `orders` (with columns `order_id`, `customer_id`, `order_date`). Which SQL query would accurately produce this report, ensuring each customer appears only once with their latest order date?

Accepted Answer

SELECT c.first_name || ' ' || c.last_name AS full_name, MAX(o.order_date) AS most_recent_order FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.first_name, c.last_name

Question 3

Consider a scenario where a database administrator is tasked with generating a report from the `employees` table. This report must include only those employees whose job roles are either \'PROGRAMMER\' or \'ANALYST\'. Furthermore, these selected employees must also meet one of two additional criteria: either they were hired after January 1st, 2005, or their salary exceeds $5000 and they are not assigned to department number 30. Which of the following SQL `WHERE` clauses accurately reflects these precise filtering requirements?

Accepted Answer

WHERE job_id IN ('PROGRAMMER', 'ANALYST') AND (hire_date > TO_DATE('2005-01-01', 'YYYY-MM-DD') OR (salary > 5000 AND department_id 30))

Question 4

An enterprise database administrator, Kaelen, is tasked with extracting a list of all order identifiers and their respective dates for customers residing in the \"Veridian\" sector whose last name begins with the letter \'S\' and who have cumulatively placed more than five distinct orders within the `SALES_RECORDS` database. The `CUSTOMER_PROFILES` table contains `cust_id`, `sector`, and `last_name`, while the `ORDER_DETAILS` table contains `order_num`, `cust_id`, and `order_dt`. Which SQL statement accurately retrieves this information?

Accepted Answer

SELECT o.order_num, o.order_dt FROM ORDER_DETAILS o WHERE o.cust_id IN (SELECT cp.cust_id FROM CUSTOMER_PROFILES cp JOIN ORDER_DETAILS od ON cp.cust_id = od.cust_id WHERE cp.sector = 'Veridian' AND cp.last_name LIKE 'S%' GROUP BY cp.cust_id HAVING COUNT(od.order_num) > 5)

Question 5

Anya, a database administrator for a large e-commerce platform, is reviewing the performance of a critical query that retrieves details for orders exceeding a specific total value. The current implementation utilizes a subquery within the WHERE clause to filter orders based on the aggregated sum of their line items. Anya is exploring alternative SQL constructs in Oracle Database 12c that might offer better performance and resource utilization for this type of conditional aggregation, particularly when dealing with millions of order records. She needs to ensure the chosen method is both syntactically correct and aligns with best practices for optimizing queries involving aggregate filtering.

Which of the following SQL statements is the most efficient and idiomatic way to achieve this in Oracle Database 12c, considering the need to filter orders based on the sum of their item prices?

Accepted Answer

Joining the `orders` table with `order_items`, grouping by order details, and using a `HAVING` clause to filter by the sum of line item prices.

Question 6

Consider a database system designed to manage client interactions and transactions. A new requirement mandates the retrieval of all client records where the client\'s surname commences with the letter \'S\' and who has, at some point, engaged in a transaction exceeding a value of $5000$. The relevant data resides in two tables: `clients` (containing `client_id`, `first_name`, `surname`) and `transactions` (containing `transaction_id`, `client_id`, `transaction_amount`). Which SQL statement would most accurately and efficiently fulfill this requirement, ensuring that only clients meeting both conditions are returned?

Accepted Answer

SELECT c.client_id, c.first_name, c.surname FROM clients c JOIN transactions t ON c.client_id = t.client_id WHERE c.surname LIKE 'S%' AND EXISTS (SELECT 1 FROM transactions t2 WHERE t2.client_id = c.client_id AND t2.transaction_amount > 5000)

Question 7

Consider a scenario involving a global e-commerce platform\'s sales data, stored in a table named `TRANSACTIONS`. This table includes columns such as `TRANSACTION_ID`, `CUSTOMER_ID`, `PRODUCT_SKU`, `SALE_DATE`, and `COUNTRY_CODE`. A business analyst needs to ascertain the precise number of unique countries from which a particular high-demand item, identified by `PRODUCT_SKU = \'XYZ789\'`, has generated at least one sale. Which of the following SQL statements accurately provides this information?

Accepted Answer

SELECT COUNT(DISTINCT COUNTRY_CODE) FROM TRANSACTIONS WHERE PRODUCT_SKU = 'XYZ789';

Question 8

A database administrator is tasked with extracting the top three highest-paid employees from department 10. They construct the following SQL query:

```sql
SELECT employee_id, salary
FROM (
    SELECT employee_id, salary
    FROM employees
    WHERE department_id = 10
    ORDER BY salary DESC
)
WHERE ROWNUM <= 3;
```
What is the fundamental principle governing the interaction between the `ROWNUM` pseudocolumn and the `ORDER BY` clause within the subquery in this specific query structure to ensure the intended outcome of retrieving the highest salaries?

Accepted Answer

`ROWNUM` is assigned to rows as they are retrieved from the subquery's result set, and the subquery's `ORDER BY` clause dictates this retrieval order, thus ensuring the top N rows are selected based on the specified ordering.

Question 9

Elara, a database administrator for a large consulting firm, needs to generate a report listing all employees who are currently assigned to at least one project. The employee information is stored in an `employees` table, and project assignments are detailed in a `projects` table, with both tables sharing a common `employee_id` column. Elara wants to ensure that no employee without an active project assignment appears in the final report. Which SQL join type would be most appropriate to achieve this specific data retrieval requirement?

Accepted Answer

INNER JOIN

Question 10

Elara, a database administrator responsible for a large e-commerce platform, observes that a critical report generating customer order summaries is performing sluggishly. The current SQL query utilizes a correlated subquery in the `WHERE` clause to filter orders placed by customers who have made a purchase within the last 90 days. Elara believes that rewriting the query to leverage a `JOIN` operation will enhance its efficiency, aligning with her strategy of adopting new methodologies for performance optimization. If the `orders` table contains columns `order_id`, `customer_id`, and `order_date`, and the `customers` table contains `customer_id` and `last_purchase_date`, which of the following SQL statements best represents Elara\'s intended optimization for retrieving order details for customers with a recent purchase, without altering the result set?

Accepted Answer

SELECT o.order_id, o.order_date FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id WHERE c.last_purchase_date >= DATE '2023-10-27'

Question 11

A database administrator is tasked with synchronizing product inventory levels in the `PRODUCTS` table with a staging table `INVENTORY_UPDATES`. The `PRODUCTS` table has a primary key on `product_id` and a `quantity_on_hand` column. The `INVENTORY_UPDATES` table contains new quantities for existing products and entirely new products. The administrator needs to ensure that if any single record update or insertion fails due to a `UNIQUE` constraint violation on `product_id` in the `PRODUCTS` table, the entire batch of synchronizations is rolled back to maintain data integrity. Which SQL statement best facilitates this requirement while demonstrating adaptability to changing data conditions?

Accepted Answer

Using the `MERGE` statement to conditionally update existing products or insert new ones.

Question 12

Elara, a database administrator for a large e-commerce platform, is investigating a performance bottleneck in a critical SQL query. This query retrieves all customer details along with their associated order history, joining the `customers` table with the `orders` table on `customers.customer_id = orders.customer_id`. The `orders` table is known to contain millions of records, while the `customers` table is significantly smaller. Initial profiling indicates that the join operation is the primary contributor to the query\'s sluggishness. Elara needs to implement a change to significantly improve the query\'s execution speed. Which of the following actions would be the most effective initial step to address this performance issue?

Accepted Answer

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

Question 13

A data analyst, Kaelen, is tasked with retrieving a list of all employees from the `employees` table who were hired on or after January 2nd, 2022, and whose annual compensation is either greater than $75,000 or less than $45,000. Which of the following SQL statements accurately reflects Kaelen\'s requirements?

Accepted Answer

SELECT employee_id, first_name, last_name FROM employees WHERE hire_date >= '02-JAN-2022' AND (salary > 75000 OR salary < 45000);

Question 14

Consider a scenario where a company is analyzing employee compensation based on their job roles. An analyst is tasked with categorizing a bonus amount based on specific job identifiers using the `DECODE` function. If the `job_id` is \'SA_REP\', the bonus is 1000; if it\'s \'AD_VP\', the bonus is 2000; if it\'s \'FI_ACCOUNT\', the bonus is 3000. For any other `job_id`, including cases where `job_id` is not explicitly listed or is `NULL`, a default bonus of 500 is applied. What would be the output of the `DECODE` function for the following employee records, when applied as `DECODE(job_id, \'SA_REP\', 1000, \'AD_VP\', 2000, \'FI_ACCOUNT\', 3000, 500)`?

*   Employee 1: `job_id` = \'SA_REP\'
*   Employee 2: `job_id` = \'AD_VP\'
*   Employee 3: `job_id` = \'FI_ACCOUNT\'
*   Employee 4: `job_id` = \'IT_PROG\'
*   Employee 5: `job_id` = NULL

Accepted Answer

1000, 2000, 3000, 500, 500

Question 15

A database administrator is tasked with enhancing the `customer_feedback` table to store the submission date for each piece of feedback. The table currently contains `feedback_id` (primary key) and `feedback_text`. What SQL statement would correctly implement this structural modification in Oracle Database 12c?

Accepted Answer

ALTER TABLE customer_feedback ADD (feedback_date DATE);

Question 16

Consider a scenario where a data warehousing team is tasked with synchronizing a fact table (`sales_fact`) with transactional data from a staging table (`sales_staging`). The objective is to update existing sales records in `sales_fact` if a matching record exists in `sales_staging` based on a composite key of `product_id` and `transaction_date`, and to simultaneously remove any outdated or erroneous sales records from `sales_fact` that also match these criteria. Which of the following `MERGE` statement configurations accurately reflects this requirement, ensuring that only records present in both tables and meeting the deletion criteria are removed, while leaving unmatched records in `sales_fact` untouched?

Accepted Answer

MERGE INTO sales_fact sf USING sales_staging ss ON (sf.product_id = ss.product_id AND sf.transaction_date = ss.transaction_date) WHEN MATCHED THEN UPDATE SET sf.quantity = ss.quantity, sf.amount = ss.amount DELETE WHERE sf.amount < 0;

Question 17

A database administrator is reviewing employee records to identify individuals hired during a specific fiscal period. The objective is to retrieve all records from the `employees` table where the `hire_date` is on or after January 15, 2017, and strictly before February 1, 2018. Which of the following SQL clauses accurately implements this filtering requirement?

Accepted Answer

WHERE hire_date >= DATE '2017-01-15' AND hire_date < DATE '2018-02-01'

Question 18

Anya, a junior database administrator, is tasked with improving the performance of a SQL query that retrieves order information for all active customers. The original query uses a subquery to identify active customers. She knows that transforming such subqueries into join operations can often yield significant performance improvements. Given a `customers` table with `customer_id` and `status` columns, and an `orders` table with `order_id`, `order_date`, and `customer_id` columns, which SQL statement structure would most effectively achieve this optimization by replacing the subquery with a join?

Accepted Answer

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

Question 19

Consider a scenario where a database contains an `employees` table with a `NUMBER` data type column named `EMPLOYEE_ID` and a `VARCHAR2` data type column named `DEPARTMENT_NAME`. A junior database administrator attempts to retrieve all employees whose `EMPLOYEE_ID` is `\'123A\'`. Which of the following outcomes is most likely to occur as a direct result of executing the SQL statement `SELECT * FROM employees WHERE EMPLOYEE_ID = \'123A\';`?

Accepted Answer

The query will fail with an ORA-01722: invalid number error.

Question 20

Elara, a database administrator for a growing e-commerce platform, needs to extract a report of all customer orders placed during the first quarter of 2023 for a specific high-value client, identified by the customer ID \'CUST123\'. The order data is stored in a table named `orders`, which contains columns such as `order_id`, `customer_id`, `order_date`, and `total_amount`. Elara must ensure the query accurately retrieves only those orders that fall within the specified date range and are associated with the designated customer. Which SQL statement will most effectively fulfill this requirement?

Accepted Answer

SELECT order_id, customer_id, order_date, total_amount FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31' AND customer_id = 'CUST123';

Question 21

Database administrator Elara is tasked with generating a report that displays the name of each employee and the name of the department they belong to. A critical business rule dictates that the report must *only* include employees who are currently assigned to a department that is officially registered in the `departments` table. Employees who might have an invalid or unassigned `department_id` in the `employees` table should be completely omitted from the output. Which SQL join strategy is most suitable for Elara to achieve this precise data retrieval?

Accepted Answer

An INNER JOIN between the `employees` table and the `departments` table, using `department_id` as the join condition.

Question 22

Anya, a new database administrator, is responsible for generating a report detailing all orders placed by customers located in California. She needs to extract the full name of the customer (by combining their first and last names), their email address, and the date each order was placed. Given the database schema where customer information is stored in the `customers` table (with columns `customer_id`, `first_name`, `last_name`, `email`, `state`) and order details are in the `orders` table (with columns `order_id`, `customer_id`, `order_date`), which SQL query would most effectively fulfill this requirement?

Accepted Answer

SELECT CONCAT(c.first_name, ' ', c.last_name) AS customer_full_name, c.email, o.order_date FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id WHERE c.state = 'California';

Question 23

Consider a database table named `PROJECT_RESOURCES` with a unique constraint on the `resource_id` column. A developer initiates a transaction and executes the following SQL statements sequentially:

1.  `DELETE FROM PROJECT_RESOURCES WHERE project_id = 101;`
2.  `INSERT INTO PROJECT_RESOURCES (resource_id, resource_name, project_id) VALUES (50, \'Analyst\', 101);`
3.  `INSERT INTO PROJECT_RESOURCES (resource_id, resource_name, project_id) VALUES (50, \'Consultant\', 102);`

The second `INSERT` statement fails due to the unique constraint violation on `resource_id`. If the developer then issues a `ROLLBACK` command, what is the most accurate description of the state of the `PROJECT_RESOURCES` table after this operation?

Accepted Answer

All rows deleted by the first `DELETE` statement are restored, and the attempted `INSERT` statements are not applied.

Question 24

Elara, a database administrator for a growing e-commerce platform, is reviewing a poorly performing SQL query that retrieves details of customers who have placed orders exceeding a specific value. The original query utilizes a correlated subquery within the WHERE clause to calculate the total amount for each order. Recognizing the performance implications of such constructs, Elara aims to refactor the query for efficiency. Which of the following approaches best reflects a strategic adjustment to improve the query\'s execution speed by leveraging more optimal SQL constructs, demonstrating adaptability in her technical strategy?

Accepted Answer

Rewrite the query to calculate the total amount per order in a subquery within the FROM clause, filter this subquery for totals greater than the specified value using a HAVING clause, and then join this result with the customers and orders tables.

Question 25

Elara, a budding SQL developer, is reviewing a query designed to fetch all order details for orders exceeding a total value of $500. The current implementation utilizes a correlated subquery in the WHERE clause. She is exploring ways to enhance efficiency, considering that the `orders` table contains `order_id`, `customer_id`, and `order_date`, while the `order_items` table contains `order_item_id`, `order_id`, and `item_price`. Which of the following SQL statements represents a more optimized approach by leveraging JOINs and aggregate functions to achieve the same result, thereby allowing the database optimizer to potentially utilize indexes more effectively?

Accepted Answer

SELECT o.order_id, o.customer_id, o.order_date FROM orders o JOIN (SELECT order_id, SUM(item_price) AS total_order_value FROM order_items GROUP BY order_id HAVING SUM(item_price) > 500) ot ON o.order_id = ot.order_id

Question 26

A database administrator is tasked with populating a newly created `events` table with historical data. The `events` table has a column named `event_date` of the `DATE` data type. The administrator attempts to execute the following SQL statement: `INSERT INTO events (event_date) VALUES (\'2023-02-30\');`. Following this, they intend to execute `COMMIT;`. What is the most likely outcome of this sequence of operations?

Accepted Answer

The `INSERT` statement fails with a date-related error, and the `COMMIT` statement does not complete the transaction for this row.

Question 27

When faced with a sudden and significant performance degradation in a critical customer order summary report, database administrator Elara needs to diagnose the root cause without impacting the live system further. She suspects the SQL query driving the report has become inefficient due to recent application code changes. What SQL statement should Elara utilize to investigate the proposed execution path of the query and identify potential performance bottlenecks, such as inefficient table access or join methods, before making any modifications?

Accepted Answer

EXPLAIN PLAN FOR SELECT customer_id, COUNT(order_id) FROM orders WHERE order_date > SYSDATE - 30 GROUP BY customer_id;

Question 28

Anya, a database administrator for a global e-commerce platform, is tasked with generating a report of customers who have made purchases within the last quarter, specifically for items that were shipped via \"ExpressLogistics\". The customer information, including their names and email addresses, is stored in the `customers` table. Order details, such as the order date and the customer who placed the order, are in the `orders` table. Shipment information, including the shipping company and the order it pertains to, resides in the `shipments` table. Anya needs to identify customers who have placed at least one order meeting these criteria. Which of the following SQL statements accurately and efficiently retrieves this information?

Accepted Answer

SELECT DISTINCT c.customer_name, c.email FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN shipments s ON o.order_id = s.order_id WHERE o.order_date BETWEEN DATE '2023-01-01' AND DATE '2023-03-31' AND s.shipping_company = 'ExpressLogistics';

Question 29

Consider a database table named `Personnel` with columns `employee_id` (NUMBER) and `emp_name` (VARCHAR2). If a query is executed with the following structure:

```sql
SELECT employee_id, emp_name
FROM Personnel
WHERE emp_name = 200 AND employee_id = \'200\';
```

What is the most likely outcome of this query, assuming no data manipulation has occurred that would cause an invalid number conversion for the `employee_id` column itself?

Accepted Answer

The query will execute successfully and return rows where `employee_id` is numerically 200 and `emp_name` is the character string '200'.

1z0061 Oracle Database 12c: SQL Fundamentals Free Practice Test — 29 Questions

A lot more is already mapped out

About the 1z0061 Oracle Database 12c: SQL Fundamentals Certification