1z0-071 Exam - Question 222

Question

Examine the contents of the EMP table:Examine this query that executes successfully:What is the result?

Examice · Accepted Answer

The query selects the first five rows from the employee table ordered by salary in ascending order. The 'WITH TIES' clause ensures that if there are additional rows with the same salary as the fifth row (in this case, 12000), they will also be included in the result set. Hence, it returns six employees - DeHaan, Ben, Lex, Bill, Neena, and George, who all have the lowest salaries, in ascending order.

Shrimathi · Answer

Option A tested in DB.
If the salary contain like the below example:
id Salary
1 12000
2 15000
3 16000
4 16000
5 17000

in this case, with ties option return extra rows

select id,salary
from emp
order by salary
fetch first 3 rows with ties;

o/p:
1 12000
2 15000
3 16000
4 16000

CMjer · Answer

A is correct, checked in DB

zouve · Answer

A tested

WingL · Answer

A tested.
CREATE TABLE EMP
(
    ID NUMBER(10),
    NAME VARCHAR2(10),
    SALARY NUMBER(10)
)

INSERT INTO EMP VALUES (101, 'JOHN', 26000);
INSERT INTO EMP VALUES (102, 'NEENA', 24000);
INSERT INTO EMP VALUES (103, 'DEHAAN', 12000);
INSERT INTO EMP VALUES (104, 'LEX', 17000);
INSERT INTO EMP VALUES (105, 'BILL', 18000);
INSERT INTO EMP VALUES (106, 'DANIEL', 26000);
INSERT INTO EMP VALUES (107, 'BEN', 12000);
INSERT INTO EMP VALUES (108, 'GEORGE', 25000);

SELECT * FROM EMP
ORDER BY SALARY
FETCH FIRST 5 ROWS WITH TIES;

lucemqy · Answer

A is correct

zangado · Answer

The WITH TIES returns additional rows with the same sort key as the LAST row fetched.

Arth1989 · Answer

A tested.
However, what is the meaning of WITH TIES then. If replacing with ONLY returns the same result.

ArslanAltaf · Answer

With Ties - Query would have returned one more row with an equal salary to the last row's salary if that salary number matches. In this case, it didn't so it returns 5 rows only.

1z0-071 Exam - Question 222

Discussion