Oracle 1Z0-171 Certification Sample Questions and Answers

1Z0-171 PDF, 1Z0-171 Dumps PDF Free Download, 1Z0-171 Latest Dumps Free PDF, AI Database SQL Associate PDF DumpsThe Oracle AI Database SQL Associate (1Z0-171) Sample Question Set is designed to help you prepare for the Oracle AI Database SQL Certified Associate certification exam. To become familiar with the actual Oracle Certification exam environment, we suggest you try our Sample Oracle 1Z0-171 Certification Practice Exam.

This Oracle AI Database SQL Associate certification sample practice test and sample question set are designed for evaluation purposes only. If you want to test your Oracle 1Z0-171 knowledge to identify your areas of improvement and get familiar with the actual exam format, we suggest you prepare with the Premium Oracle AI Database SQL Certified Associate Certification Practice Exam. Our team of Oracle Database experts has designed Questions-Answers for this premium practice exam by collecting inputs from recently certified candidates. Our premium Oracle 1Z0-171 certification practice exam will boost your confidence as well as your actual Oracle AI Database SQL Associate exam result.

Oracle 1Z0-171 Sample Questions:

01. Three tables exist: EMPLOYEES(employee_id, department_id, job_id), DEPARTMENTS(department_id, department_name), and JOBS(job_id, job_title).
Which query correctly returns each employee's name together with their department name and job title?
a) SELECT e.last_name, d.department_name, j.job_title FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN jobs j ON e.job_id = j.job_id;
b) SELECT e.last_name, d.department_name, j.job_title FROM employees e, departments d, jobs j;
c) SELECT e.last_name, d.department_name, j.job_title FROM employees e JOIN departments d ON e.department_id = d.department_id;
d) SELECT e.last_name, d.department_name, j.job_title FROM employees e JOIN jobs j ON e.department_id = j.job_id;
 
02. What is the ANSI-syntax equivalent of the legacy Oracle Cartesian-product join written as SELECT * FROM employees, departments; (no WHERE condition)?
a) SELECT * FROM employees NATURAL JOIN departments;
b) SELECT * FROM employees INNER JOIN departments;
c) SELECT * FROM employees CROSS JOIN departments;
d) SELECT * FROM employees SELF JOIN departments;
 
03. Both EMPLOYEES and DEPARTMENTS have a column named manager_id. A developer writes: SELECT last_name, manager_id FROM employees JOIN departments ON employees.department_id = departments.department_id; and it fails with an error about an ambiguous column.
What is the correct fix?
a) Remove manager_id from the SELECT list entirely; it can never be selected from either table.
b) Qualify manager_id with a table alias, such as employees.manager_id, to specify which column is meant.
c) Rename one of the two manager_id columns permanently before the query can run.
d) Replace the JOIN...ON syntax with a NATURAL JOIN to resolve the ambiguity automatically.
 
04. Which WHERE condition is equivalent to department_id = 10 OR department_id = 20 OR department_id = 30?
a) department_id BETWEEN 10 AND 30
b) department_id LIKE (10, 20, 30)
c) department_id IN (10, 20, 30)
d) department_id = (10, 20, 30)
 
05. In Oracle SQL, which comparison condition is equivalent to department_id <> 30?
a) department_id == 30
b) department_id NOT (30)
c) department_id IS NOT 30
d) department_id != 30
 
06. What does the GROUP BY clause do when a query also uses a group function such as SUM?
a) It sorts the result rows into ascending order.
b) It filters out rows before any aggregation occurs.
c) It groups rows sharing the same column value(s) so a group function computes once per group.
d) It removes duplicate rows from the final result set.
 
07. A developer writes: SELECT salary, salary * 1.1 AS new_salary FROM employees WHERE new_salary > 5000; and the statement fails.
Why does referencing the alias new_salary in the WHERE clause cause an error?
a) Column aliases can never be used anywhere else in the same statement.
b) The alias name is a reserved SQL keyword and cannot be reused.
c) Arithmetic expressions cannot be given an alias at all.
d) WHERE runs before SELECT-list aliases are assigned, so the alias does not yet exist at that point.
 
08. Session A updates a row's salary but has not yet issued COMMIT. Session B queries the same row at that moment.
What does Oracle's read consistency guarantee about what Session B sees?
a) Session B continues to see the row's prior, committed salary value until Session A commits the change.
b) Session B sees Session A's uncommitted new salary value immediately.
c) Session B's query is blocked and cannot run at all until Session A commits.
d) Session B sees an average of the old and new salary values.
 
09. How does NVL2(commission_pct, 'HAS COMMISSION', 'NO COMMISSION') differ from a simple two-argument NVL call?
a) NVL2 behaves identically to NVL in every respect; it is only an alternate spelling.
b) NVL2 ignores its first argument entirely and always returns its second argument.
c) NVL2 can only be used with numeric columns, while NVL works with any data type.
d) NVL2 returns its second argument if the first is non-NULL, or its third if NULL — unlike NVL's single substitution.
 
10. Using the same subquery values (4000, 5000, 6000) from department_id 60, what does SELECT last_name FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 60); return?
a) Employees whose salary exceeds at least one of the three values
b) Employees whose salary exceeds every value — effectively, the largest, 6000
c) Employees whose salary is exactly equal to the largest value, 6000
d) The same result as the equivalent query using ANY instead of ALL

Answers:

Question: 01

Answer: a

Question: 02

Answer: c

Question: 03

Answer: b

Question: 04

Answer: c

Question: 05

Answer: d

Question: 06

Answer: c

Question: 07

Answer: d

Question: 08

Answer: a

Question: 09

Answer: d

Question: 10

Answer: b

 

Rating: 4.7 / 5 (114 votes)