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
A : JOIN, JOIN
select pdtno, pdtname, compno, compname
from product join pdt_comp using(pdtno)
join component using (compno);
select p.pdtno, p.pdtname, c.compno, c.compname
from product p, component c, pdt_comp pc
where p.pdtno = pc.pdtno
and c.compno = pc.compno
— only records from pdt_comp table present.
1 p1 2 c2
1 p1 3 c3
2 p2 2 c2
B. FULL OUTER JOIN, FULL OUTER JOIN
select pdtno, pdtname, compno, compname
from product full outer join pdt_comp using(pdtno)
full outer join component using (compno)
select p.pdtno, p.pdtname, c.compno, c.compname
from product p, component c, pdt_comp pc
where p.pdtno = pc.pdtno (+)
and c.compno(+) = pc.compno
union
select p.pdtno, p.pdtname, c.compno, c.compname
from product p, component c, pdt_comp pc
where p.pdtno(+) = pc.pdtno
and c.compno = pc.compno (+)
1 p1 3 c3
1 p1 2 c2
2 p2 2 c2
3 p3
4 p4
4 c4
1 c1
C.RIGHT OUTER JOIN, LEFT OUTER JOIN
select pdtno, pdtname, compno, compname
from product RIGHT outer join pdt_comp using(pdtno)
LEFT outer join component using (compno)
1 p1 2 c2
1 p1 3 c3
2 p2 2 c2
D. LEFT OUTER JOIN , RIGHT OUTER JOIN
1 p1 2 c2
1 p1 3 c3
2 p2 2 c2
4 c4
1 c1
Hence, C, A give same result.
C