Oracle Database SQL - 1z1-071 Exam Questions [2026]

QUESTION NO: 1
Examine the ORDER _ITEms table:

Which two queries return rows where QUANTITY is a multiple of ten?

A. SELECT" FROM order_ items WHERE FLOOR (quantity / 10) = TRUNC (quantity / 10); B. SELECT * FROM order_ items WHERE quantity = TRUNC (quantity, -1); C. SELECT" FROM order_ _items WHERE quantity = ROUND (quantity, 1); D. SELECT * FROM order_ items WHERE MOD (quantity, 10) = 0; E. SELECT FROM order_ items WHERE quantity / 10 = TRUNC (quantity);

Correct Answer: A,D

Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).

QUESTION NO: 2
In which three situations does a new transaction always start?

A. When issuing the first Data Manipulation Language (OML) statement after a COMMIT or ROLLBACK statement was issued in the same session B. When issuing a CREATE TABLE statement after a SELECT statement was issued in the same session C. When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session D. When issuing a TRUNCATE statement after a SELECT statement was issued in the same session E. When issuing a CREATE INDEX statement after a CREATE TABLE statement completed unsuccessfully in the same session F. When issuing a DML statement after a DML statement filed in the same session.

Correct Answer: A,C,D

Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).

QUESTION NO: 3
Examine this partial query:
SELECT ch.channel_type, t.month, co.country_code, SUM(s.amount_sold) SALES FROM sales s, times t, channels ch, countries co WHERE s.time_ id = t.time id AND s.country_ id = co. country id AND s. channel id = ch.channel id AND ch.channel type IN ('Direct Sales', 'Internet') AND t.month IN ('2000-09', '2000-10') AND co.country code IN ('GB', 'US') Examine this output:

Which GROUP BY clause must be added so the query returns the results shown?

A. GROUP BY ch.channel_type,ROLLUP (t month, co. country_ code) ; B. GROUP BY ch.channel_type, t.month, co.country code; C. GROUP BY CUBE (ch. channel_ type, t .month, co. country code); D. GROUP BYch. channel_ type, t.month,ROLIUP (co. country_ code) ;

Correct Answer: B

Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).

QUESTION NO: 4
Which two join conditions in a from clause are non-equijoins?

A. tablet join table2 ON (table1.column = table2.column) where table2.column LIKE 'A' B. table1 natural JOIN table2 C. table1 join table2 using (column1, column2) D. table1 join table2 ON (table1.column >= table2.column) E. table1 join table2 on (table1.column between table2.column] and table2.column2)

Correct Answer: D,E

Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).

QUESTION NO: 5
Examine this SQL statement:
SELECT cust_id, cust_last_name "Last Name
FROM customers
WHERE countryid=10
UNION
SELECT custid CUSTNO, cust_last_name
FROM customers
WHERE countryid=30
Identify three ORDER BY clauses, any one of which can complete the query successfully.

A. ORDERBY2, 1 B. ORDER BY"CUST NO" C. ORDER BY 2, cust_id D. ORDER BY "Last Name" E. ORDER BY CUSTNO

Correct Answer: A,C,D

Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).

QUESTION NO: 6
Table HR.EMPLOYEES contains a row where the EMPLOYEES _ID is 109.
User ALICE has no privileges to access HR.EMPLOYEES.
User ALICE starts a session.
User HR. starts a session and successfully executes these statements:
GRANT DELETE ON employees TO alice;
UPDATE employees SET salary = 24000 WHERE employee_id = 109;
In her existing session ALICE then executes:
DELETE FROM hr.employees WHERE employee_id = 109;
What is the result?

A. The delete command will immediately return an error. B. The delete command will wait for HR's transaction to end then return an error. C. The DELETE command will immediately delete the row. D. The DELETE command will wait for HR's transaction to end then delete the row.

Correct Answer: B

QUESTION NO: 7
Examine the description of the EMPLOYEES table:

You write this failing statement:
SELECT dept_no AS department_id, MAX (salary) As max_sal
FROM employees
WHERE salary >10000
GROUP BY department_id
ORDER BY max_sal;
Which clause causes the error?

A. ORDER BY B. SELECT C. GROUP BY D. WHERE

Correct Answer: C

Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).

QUESTION NO: 8
Examine these statements and results:
SQL> SELECT COUNT(*) FROM emp
COUNT(*)
---------------------
14
sQL> CREATE GLOBAL TEMPORARY TABLE t emp As SELECT * FROM emp;
Table created
SQL> INSERT INTo temp SELECT * FROM emp;
14 rows created
SQL> COMMIT:
Commit complete*
SQL> INSERT INTo temp SELECT * EROM emp;
14. rows created
SQL> SELECT COUNT(*) FROM t emp
How many rows are retrieved by the last query?

A. 14 B. 0 C. 42 D. 28

Correct Answer: A

Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).

QUESTION NO: 9
Which two statements will return the names of the three employees with the lowest salaries?

A. SELECT last_ name, salary
FROM
(SELECT" FROM employees ORDER BY salary)
WHERE ROWNUM <=3 B. SELECT last name, salary
FROM employees
ORDER BY salary
FETCE FIRST 3 RONS ONLY; C. SELECT last_ name, salary
FROM employees
WHERE ROWNUM <=3
ORDER BY salary D. SELECT last_ name, salary
FBOM employees
WEERE
ORDER BY SELECT
ROINUM <= 3
salary FROM
employees); E. SELECT last_ name, salary
FROM employees
FETCH FIRST 3 ROWS ONLY
ORDER BY salary;

Correct Answer: A,E

Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).

QUESTION NO: 10
Evaluate these commands which execute successfully CREATE SEQUENCE ord_seq INCREMENT BY 1 START WITH 1 MAXVALUE 100000 CYCLE CACHE 5000; Create table ord_items( ord_no number(4) default ord_seq.nextval not null, Item_no number(3), Qty number(3), Expiry_date date, Constraint it_pk primary key(ord_no,item_no), Constraint ord_fk foreign key (ord_no) references orders(ord_no)); Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?

A. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ. B. IF sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS. C. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO. D. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers. E. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times

Correct Answer: B,C

Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).

QUESTION NO: 11
Which two statements are true regarding non equijoins?

A. Table aliases must be used. B. The SQL:1999 compliant ANSI join syntax must be used. C. The Oracle join syntax can be used. D. The ON clause can be used. E. The USING clause can be used.

Correct Answer: C,D

Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).

QUESTION NO: 12
Examine the description of the EMPLOYEES table:

Which statement will execute successfully, returning distinct employees with non-null first names?

A. SELECT first_ name, DISTNCT last_ name FROM employees WHERE first_ name IS NOT NULL; B. SELECT DISTINCT * FROM employees WHERE first_ name IS NOT NULL; C. SELECT first_ name, DISTINCT last_ name FROM employees WHERE first_ name < > NULL; D. SELECT Distinct * FROM employees WHERE first_ name < > NULL;

Correct Answer: B