1z0051 Oracle Database: SQL Fundamentals I Free Practice Test — 29 Questions

Question 1

A project manager is tasked with generating a report of all software engineers within the \'Development\' division who have been with the company for more than five years and whose performance review score is above 85. Given a table named `personnel_records` with columns `person_id`, `full_name`, `job_title`, `division`, `hire_date`, and `performance_score`, which SQL statement accurately retrieves this specific subset of employees?

Accepted Answer

SELECT full_name, job_title FROM personnel_records WHERE division = 'Development' AND job_title = 'Software Engineer' AND hire_date < DATE('now', '-5 years') AND performance_score > 85

Question 2

A database administrator is tasked with auditing project hour submissions for a team. They are reviewing a table named `ProjectEffort` which logs `EmployeeID`, `TaskDescription`, and `ActualHours`. The `ActualHours` column is designed to store the number of hours an employee spent on a task, but it can contain `NULL` values if a task submission is incomplete or pending. The administrator wants to understand how the `COUNT()` and `AVG()` aggregate functions behave with `NULL` data. Specifically, they are interested in the relationship between counting all records, counting records with actual hour entries, and calculating the average hours worked, considering the presence of `NULL`s. Which of the following statements accurately describes the outcome when `COUNT(ActualHours)` and `AVG(ActualHours)` are executed on a table containing `NULL` values in the `ActualHours` column, assuming `COUNT(*)` also returns a value greater than the count of non-NULL `ActualHours` entries?

Accepted Answer

`COUNT(ActualHours)` will count rows where `ActualHours` is not `NULL`, and `AVG(ActualHours)` will calculate the average using only the non-NULL `ActualHours` values, potentially resulting in `COUNT(ActualHours)` being greater than the number of values used in the `AVG` calculation.

Question 3

Anya, a new database administrator, is presented with a database schema containing two tables: `ORDERS` (with columns `order_id`, `order_date`, `customer_id`, `total_amount`) and `CUSTOMERS` (with columns `customer_id`, `customer_name`, `email`, `city`). She needs to generate a report that lists each customer\'s full name alongside all their associated order IDs. Anya is aware that the `customer_id` in the `ORDERS` table serves as a foreign key referencing the `customer_id` in the `CUSTOMERS` table. Which SQL query would most effectively fulfill Anya\'s requirement while ensuring data integrity and avoiding duplicate customer entries for orders?

Accepted Answer

SELECT c.customer_name, o.order_id FROM CUSTOMERS c JOIN ORDERS o ON c.customer_id = o.customer_id;

Question 4

A database administrator is tasked with generating a report of the top five most recently hired employees from the `hr_staff` table, based on their `hire_date`. The administrator needs to ensure the query accurately retrieves these five individuals. Which of the following SQL statements correctly implements this requirement, demonstrating an understanding of how pseudocolumns interact with ordering in Oracle SQL?

Accepted Answer

SELECT employee_id, first_name, hire_date FROM (SELECT employee_id, first_name, hire_date FROM hr_staff ORDER BY hire_date DESC) WHERE ROWNUM <= 5

Question 5

Consider a database table named `employees` with a column `job_title` storing various job descriptions. If the requirement is to retrieve all job titles that commence with the letter \'S\', are followed by precisely one other character, then have another \'S\', and can be followed by any sequence of zero or more additional characters, which SQL `WHERE` clause condition would accurately fulfill this requirement?

Accepted Answer

`job_title LIKE 'S_S%'`

Question 6

Anya, a database administrator for a global logistics firm, needs to extract a list of all employees from the `employees` table who reside in cities starting with the letter \'S\' and whose hire date is on or after January 1st, 2022. Which SQL statement would accurately retrieve this information?

Accepted Answer

SELECT employee_id, first_name, city, hire_date FROM employees WHERE city LIKE 'S%' AND hire_date >= '2022-01-01';

Question 7

Database administrator Anya needs to extract a list of all employees who commenced their employment after December 31st, 2022, and are currently assigned to either the \'Sales\' or \'Marketing\' departments. The `employees` table contains columns such as `employee_id`, `full_name`, `hire_date`, and `department_name`. Which of the following SQL statements accurately fulfills Anya\'s request, ensuring that only employees meeting both the date and department criteria are returned?

Accepted Answer

SELECT employee_id, full_name FROM employees WHERE hire_date > '2022-12-31' AND department_name IN ('Sales', 'Marketing');

Question 8

A data analyst is tasked with generating a report of all unique department names and their associated locations from two separate tables, `HR_DEPARTMENTS` and `REGIONAL_OFFICES`. Both tables contain `department_name` and `location_id` columns. The analyst constructs the following SQL query:

```sql
SELECT department_name, location_id
FROM HR_DEPARTMENTS
UNION
SELECT department_name, location_id
FROM REGIONAL_OFFICES
ORDER BY 1;
```

What is the primary outcome of executing this query, considering the fundamental principles of SQL set operators and sorting?

Accepted Answer

A single list of all department names and their corresponding location IDs, with duplicate entries removed and the results sorted alphabetically by department name.

Question 9

Consider a database table named `EMPLOYEE_SALARIES` with a column `SALARY` defined as `NUMBER(10,2)`. If a query is executed with the following `WHERE` clause: `WHERE SALARY > \'1000\'`, what is the most likely outcome regarding the number of rows returned?

Accepted Answer

No rows will be returned because the numeric literal '1000' is implicitly converted to a number and compared as 1000 > 1000, which is false.

Question 10

Consider a scenario where a human resources analyst needs to identify all employees whose current salary exceeds the departmental average salary. The company’s HR database contains an `employees` table with columns such as `employee_id`, `first_name`, `last_name`, `salary`, and `department_id`. Which of the following SQL statements accurately retrieves this information, ensuring that each employee\'s salary is compared against the average salary of their specific department, not the overall company average?

Accepted Answer

SELECT employee_id, first_name, last_name, salary, department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees GROUP BY department_id);

Question 11

Anya, a junior database administrator, is tasked with extracting a specific subset of employee information from the `employees` table. She needs to retrieve the unique identifier, the given name, the family name, and the electronic mail address for all personnel whose family name commences with the letter \'M\' and whose annual compensation exceeds $60000$. Which of the following SQL statements accurately fulfills this requirement?

Accepted Answer

SELECT employee_id, first_name, last_name, email FROM employees WHERE last_name LIKE 'M%' AND salary > 60000;

Question 12

Anya, a junior database administrator, is asked to retrieve a list of all orders placed by customers in the \"West\" region, along with the date of each order and the total amount. She writes a query that retrieves all columns for every order in the \"West\" region. When questioned about her approach, she states that it\'s the simplest way to get all the information. What fundamental SQL principle is Anya overlooking that would lead to a more efficient and maintainable query?

Accepted Answer

The importance of explicitly listing required columns instead of using `SELECT *`.

Question 13

A data analyst, Elara Vance, needs to extract all transaction records from the `financial_transactions` table that occurred after January 15, 2023, but strictly before March 1, 2023. Which SQL clause combination would be most effective in achieving this precise filtering?

Accepted Answer

WHERE transaction_date > '2023-01-15' AND transaction_date < '2023-03-01'

Question 14

Given a dataset of employee performance metrics where the `performance_score` column is of NUMBER type and can contain `NULL` values, and the `bonus_amount` column is also of NUMBER type and can contain `NULL` values, analyze the outcome of executing the following SQL statement: `SELECT COUNT(performance_score), AVG(performance_score) FROM performance_data WHERE commission_status = \'Active\';`. If the `performance_data` table, filtered by `commission_status = \'Active\'`, contains 5 rows where `performance_score` is not NULL and 3 rows where `performance_score` is NULL, what would be the precise output of the query?

Accepted Answer

5, followed by the average of the non-NULL performance scores

Question 15

Consider a dataset tracking sales performance across different geographical territories, stored in a table named `sales_performance`. This table contains columns such as `territory_name` (VARCHAR2), `sales_representative_id` (NUMBER), and `quarterly_revenue` (NUMBER). A sales manager wants to identify which territories have an average quarterly revenue, calculated across all sales representatives within that territory, that surpasses $50,000$. Which of the following SQL query structures correctly retrieves this information?

Accepted Answer

SELECT territory_name FROM sales_performance GROUP BY territory_name HAVING AVG(quarterly_revenue) > 50000

Question 16

Anya, a database administrator for a global tech firm, is reviewing employee performance data for the Q3 fiscal period. She needs to identify all employees whose quarterly performance score, stored in the `performance_score` column of the `employee_performance` table, exceeded $85\%$. The output should display the `employee_name` and their respective `department`. Which of the following SQL statements accurately retrieves this specific set of data?

Accepted Answer

SELECT employee_name, department FROM employee_performance WHERE performance_score > 85;

Question 17

Anya, a database administrator for a growing e-commerce platform, is experiencing significant performance degradation in a critical SQL query. This query retrieves historical order data, frequently filtering by both a specific customer identifier and a date range. The `orders` table, containing millions of records, has columns `order_id`, `customer_id`, `order_date`, and `total_amount`. Analysis of query execution plans reveals that the current lack of appropriate indexing is causing full table scans for most requests. Anya is considering implementing a new composite index to optimize queries like `SELECT * FROM orders WHERE customer_id = 12345 AND order_date BETWEEN \'2023-01-01\' AND \'2023-01-31\';`. Considering the typical usage patterns where specific customer lookups are common and often combined with temporal filtering, which index strategy would most effectively enhance the performance of such queries?

Accepted Answer

A composite index on `(customer_id, order_date)`

Question 18

A database administrator is reviewing a SQL query intended to retrieve sales records from the `SALES_RECORDS` table. The `SALE_ID` column in this table is defined as a `NUMBER` data type. The query submitted is: `SELECT * FROM SALES_RECORDS WHERE SALE_ID = \'98765X\';` What is the most probable outcome of executing this SQL statement in an Oracle database environment?

Accepted Answer

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

Question 19

Anya, a database administrator for a growing e-commerce platform, is reviewing recent customer transactions. She needs to generate a report that lists the unique identifier for each order, the exact timestamp when the order was submitted, and the final calculated value of each order. However, her analysis is focused on high-value transactions, so she only wants to include orders where the total amount is strictly greater than 500 units of currency. Furthermore, to understand recent purchasing trends, she requires the results to be presented with the most recent orders appearing first. Which SQL statement would accurately fulfill Anya\'s request?

Accepted Answer

SELECT order_id, order_date, total_amount FROM orders WHERE total_amount > 500 ORDER BY order_date DESC;

Question 20

Kaelen, a database administrator tasked with analyzing customer engagement in the city of Veridia, needs to extract a list of customers who joined the service after the beginning of 2023. The customer data is stored in a table named `customers`, which contains columns such as `customer_id`, `first_name`, `last_name`, `city`, and `signup_date`. Kaelen needs to ensure the query precisely targets only those customers who are located in \'Veridia\' and whose `signup_date` falls on or after January 1, 2023. Which SQL statement will most accurately fulfill Kaelen\'s requirement?

Accepted Answer

SELECT customer_id, first_name, last_name FROM customers WHERE city = 'Veridia' AND signup_date > DATE '2023-01-01';

Question 21

Anya, a database administrator, is tasked with generating a report of all employees who have been assigned to a specific department. She needs to retrieve their first name, last name, and their respective department identifiers. The report must exclude any employee records that do not have a department ID associated with them. Which SQL statement will correctly fulfill this requirement?

Accepted Answer

SELECT first_name, last_name, department_id FROM employees WHERE department_id IS NOT NULL;

Question 22

A data analyst is tasked with analyzing sales performance across different geographical territories. They need to understand not only the total sales value but also the number of transactions that actually had a recorded sales amount, distinct from the total number of sales records entered for each territory. Given a table `TerritorySales` with columns `TerritoryName` (VARCHAR2) and `SaleAmount` (NUMBER), which aggregate function, when used in conjunction with a `GROUP BY TerritoryName` clause, would accurately reflect the count of sales transactions where `SaleAmount` is not `NULL`?

Accepted Answer

COUNT(SaleAmount)

Question 23

A database administrator needs to extract a list of the five most recently hired employees from the `hr_staff` table, which contains `staff_id`, `hire_date`, and `staff_name`. The administrator must ensure that the results are accurately ordered by `hire_date` in descending order. Which SQL query construction effectively retrieves this specific subset of data, adhering to the behavior of the `ROWNUM` pseudocolumn when combined with ordering?

Accepted Answer

SELECT staff_id, hire_date, staff_name FROM (SELECT staff_id, hire_date, staff_name FROM hr_staff ORDER BY hire_date DESC) WHERE ROWNUM <= 5

Question 24

During a performance tuning exercise on a large dataset, a developer aims to extract the five most recently registered customer accounts. The developer initially writes a query that filters records based on a `registration_date` column, but the results do not consistently reflect the latest registrations. Which of the following SQL query structures is most effective for accurately retrieving the top five most recent customer accounts, ensuring the ordering is applied prior to the selection of the limited number of rows?

Accepted Answer

SELECT * FROM (SELECT customer_id, registration_date FROM customers ORDER BY registration_date DESC) WHERE ROWNUM <= 5

Question 25

Anya, a data analyst, is working with a table containing product codes, and one of the columns, `product_code`, stores values like \'DatabaseQuery-123\'. She needs to extract the first three characters of the manufacturer\'s abbreviation, which is always located five characters from the end of the `product_code` string. If she uses the SQL statement `SELECT SUBSTR(product_code, -5, 3) FROM products WHERE product_id = 456;`, what will be the output for the `product_code` \'DatabaseQuery-123\'?

Accepted Answer

Que

Question 26

Consider a scenario where a database administrator needs to retrieve a comprehensive list of all personnel from the `employees` table, excluding individuals holding the positions of \'IT Program Manager\' or \'Sales Representative\'. Which SQL statement would most effectively fulfill this requirement by ensuring that records with these specific job identifiers are omitted from the result set?

Accepted Answer

SELECT * FROM employees WHERE job_id NOT IN ('IT_PROG', 'SA_REP');

Question 27

Consider a database table named `employees` with a column `hire_date` of DATE data type. If you execute the following SQL statement: `SELECT employee_name FROM employees WHERE hire_date = \'2007-05-15\';`, what is the most likely outcome regarding the comparison and potential data retrieval, assuming the database is configured with standard NLS settings?

Accepted Answer

The query will correctly compare the `hire_date` column with the specified date string, potentially returning employee names if the hire date matches.

Question 28

Consider a scenario where a database administrator needs to retrieve the fifth highest salary from an `EMPLOYEE_SALARIES` table containing `employee_id`, `salary`, and `department` columns. The administrator intends to use the `ROWNUM` pseudocolumn for this task. Which of the following SQL query structures correctly identifies the fifth highest salary, assuming the `salary` column is ordered in descending order?

Accepted Answer

SELECT salary FROM (SELECT salary FROM EMPLOYEE_SALARIES ORDER BY salary DESC) WHERE ROWNUM = 5

Question 29

Consider a database table named `employees` with a column `job_title` storing various job descriptions. A data analyst needs to retrieve all job titles that commence with the prefix \"Analyst\" and conclude with the suffix \"Developer\", allowing for any sequence of characters, or no characters, in between these two specific strings. Which of the following SQL `SELECT` statements would accurately fulfill this retrieval requirement?

Accepted Answer

SELECT * FROM employees WHERE job_title LIKE 'Analystveloper';

1z0051 Oracle Database: SQL Fundamentals I Free Practice Test — 29 Questions

A lot more is already mapped out

About the 1z0051 Oracle Database: SQL Fundamentals I Certification