Which combination of joins used in the blanks in the above query gives the correct output?

View the Exhibit and examine the structure of the product, component, and PDT_COMP tables.
In product table, PDTNO is the primary key.
In component table, COMPNO is the primary key.
In PDT_COMP table, <PDTNO, COMPNO) is the primary key, PDTNO is the foreign key
referencing PDTNO in product table and COMPNO is the foreign key referencing the COMPNO in
component table.
You want to generate a report listing the product names and their corresponding component
names, if the component names and product names exist.
Evaluate the following query:
SQL>SELECT pdtno, pdtname, compno, compname
FROM product _____________ pdt_comp
USING (pdtno) ____________ component USING (compno)
WHERE compname IS NOT NULL;
Which combination of joins used in the blanks in the above query gives the correct output?

A.
JOIN; JOIN

B.
FULL OUTER JOIN; FULL OUTER JOIN

C.
RIGHT OUTER JOIN; LEFT OUTER JOIN

D.
LEFT OUTER JOIN; RIGHT OUTER JOIN

Explanation:

Show Hint

← Previous question

Next question →

Leave a Reply 28

Alisha

please help me to understand

Marcelo Thomaz

PRODUCTS
_________________________

pdtno, pdtname, qty

1, ‘PROD 1’, 10
2, ‘PROD 2’, 20
3, ‘PROD 3’, 30
4, ‘PROD 4’, 40

COMPONENT
___________________________
compno, compname, qty

1, ‘COMP 1’, 10
2, ‘COMP 2’, 20
3, ‘COMP 3’, 30
5, ‘COMP 5’, 50

PDT_COMP
___________________________
pdtno, compno

1, 2
2, 1
3, 2
2, 3

Then, the records 4 that exist on PRODUCT don’t have on PDT_COMP and the record 5 on COMPONENT don’t have on PDT_COMP, for this, use RIGHT OUTER JOIN..
Then, using RIGHT OUTER JOIN for retrieve these records

PRODNO, PRODNAME, COMPNO, COMPNAME
1, ‘PROD 1’, 2, ‘COMP 2’
2, ‘PROD 2’, 1, ‘COMP 1’
3, ‘PROD 3’, 2, ‘COMP 2’
2, ‘PROD 2’, 3, ‘COMP 3’

These records don’t have
4, ‘PROD 4’, 40
5, ‘COMP 5’, 50

Mikhail

I think point A is correct also

Mikhail

I think the point A is correct also

Marcelo

This question asks to return the names of the products and their components. Which means that should be returned all rows that satisfy the inner join and the name of the products that do not satisfy the inner join. So it uses the RIGHT OUTER JOIN clause before and LEFT OUTER JOIN later.
If only use the JOIN clause, those product names that do not satisfy the join condition, will be shut out.

bob

Is this dump still valid?

padma

even I have the same doubt. Did you get answer for this? I mean is this dump still valid?

dames

Just passed this one.
95% of the questions from here (including this one) were on the exam.
Or maybe I was just lucky.

str

Right outer join means that tables from right should contain all records (in this case it is pdt_comp, and this doesn’t take product names).
I think that A is correct as well because A and C will give same result.

Sayed

I have tested your example and found the both A and C gave the same result. The question is little bit confusing for me. Is it asking to select pdtno and compno only existed in the pdt_comp table then grab the pdtname and compname from other two tables? In that case JOIN, JOIN and RIGHT OUTER JOIN, LEFT OUTER JOIN five the same result. Because the first JOIN will give me the data of pdtno 1, 2, 3 (pdtname also) and will leave 4 in product table. The RIGHT OUTER JOIN will give the same result. Second JOIN will give me the data of compno 1, 2, 3 (compname also) and will leave 5 in component table. The LEFT OUTER JOIN will give the same result.

John

The fun fact is that the question asks for something completely different.
They want products and components with NAMES.
So I you ask me, there is one condition missing “AND PDTNAME IS NOT NULL.”
But because I know that these questions can be confusing, I would choose the C answer.

Jaime

La pregunta es confusa,
La A y C son correctas.

HCT

Hello guys,
Please, I have a test next tomorrow. Could somebody help me with IZ0-061 test dumps? I’ll appreciate a lot. email me at [email protected]. Thank you so much

Alex

I tested it using SQLPLUS and always get the ora-00918 error. In my opinion, A is correct.

Amit

Hi Guys,

I’m doing preparation for Oracle Database 12c: sql fundamentals, so i have need some dumies for it. if you guys have any idea about Oracle Databse 12c: sql fundamentals then please send me at [email protected] ..

Thanks,
Amit

Amit

Finally Ans is C.

Hey Amit, how did your exam go?

Amit

I have got only 10-15 questions from this dump and i’m so tensed because i beleaved of this Dump. Try ur side too. 🙁

Thomas Vellie

Its so Horrible dump site and got failed.

Vietnam (anticomunism)

C is correct!
Firstly — “if the component names and product names exist” => Using Outer join with direction of outer join focus on component and product (this direction depend on order of you query).

SQL>SELECT pdtno, pdtname, compno, compname
FROM product ______(1)_______ pdt_comp
USING (pdtno) ______(2)______ component USING …
==> product on left hand side so “Left Outer Join” be in (1).
==> component on right hand side so “Right outer Join” be in (2).

Valts

Is there any new dumps available? Can someone send me please? [email protected] I went through this dump about 10, learned every explanation, but I got 56% (65% is needed). In the real exam there is only about 15 questions from this. Can someone help?