Free Oracle Database SQL Certified Associate Practice Test

Which statement best describes the relationship between a primary key and a foreign key in a relational database?

A database administrator needs to ensure data integrity by preventing orphaned records. Which constraint should be used when defining a foreign key to automatically delete child records when a parent record is deleted?

Examine the following SQL statement: SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 50; Which clause determines which rows are returned by this query?

Which SELECT statement will retrieve all columns from the PRODUCTS table?

A developer writes the following query: SELECT product_name || ' - ' || category FROM products; What is the purpose of the || operator in this statement?

You need to create a query that returns unique department IDs from the EMPLOYEES table, eliminating duplicate values. Which keyword should you use?

Examine this query: SELECT employee_id, last_name, salary FROM employees WHERE salary BETWEEN 5000 AND 10000 AND department_id IN (20, 30, 40); What will this query return?

You want to retrieve all employees whose last names start with 'S'. Which WHERE clause should you use?

What will be the result of the following query? SELECT employee_id, last_name, salary FROM employees ORDER BY salary DESC, last_name; How are the results sorted?

Which SQL function would you use to convert the string '2023-12-25' into a DATE datatype?

Examine the following query: SELECT UPPER(first_name), LOWER(last_name), INITCAP(email) FROM employees; What does this query do?

A developer needs to extract the first 5 characters from a product code. Which function should be used?

What is the result of the following expression? SELECT ROUND(1567.8945, -2) FROM dual;

Examine this query: SELECT employee_id, CASE WHEN salary < 5000 THEN 'Low' WHEN salary BETWEEN 5000 AND 10000 THEN 'Medium' WHEN salary > 10000 THEN 'High' END AS salary_grade FROM employees; What does this query accomplish?

You need to display a custom message when a commission value is NULL. Which function should you use?

What will the following query return? SELECT department_id, COUNT(*), AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 8000;

Which aggregate function would you use to find the total number of non-NULL values in a column?

Examine the following query: SELECT e.employee_id, e.last_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id; What will this query return?

A report needs to show all departments, including those without any employees. Which join type should be used?

Examine this query: SELECT e1.employee_id, e1.last_name, e2.last_name AS manager_name FROM employees e1 LEFT OUTER JOIN employees e2 ON e1.manager_id = e2.employee_id; What type of join is being demonstrated?