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
Why?
If I want component names e product names exists, I can execute JOIN.
If PDTNO and COMPNO are foreign keys, data must exist in pdt_comp table, so we can use join.
The question SPECIFICALLY asks to list product name and their corresponding components if product names and component names EXIST.
So here is the scenario where Option C will be incorrect:
ASH@orcl>select * from product;
PDTNO PDTNAME QTY
———- ———- ———-
1 p1
2
3 p3
3 rows selected.
ASH@orcl>select * from component;
COMPNO COMPNAME QTY
———- ———- ———-
1 c1
2
3 c3
3 rows selected.
PDTNO COMPNO
———- ———-
1 1
2 1
2 2
3 rows selected.
ASH@orcl>l
1 select pdtno,pdtname,compno,compname
2 from product right outer join pdt_comp
3 using (pdtno) left outer join component using (compno)
4* where compname is not null
ASH@orcl>/
PDTNO PDTNAME COMPNO COMPNAME
———- ———- ———- ———-
1 p1 1 c1
2 1 c1
2 rows selected.
Clearly I can retrieve the row where product name missing. The original query must be appended with additional clause ‘and pdtname is not null’
ASH@orcl>l
1 select pdtno,pdtname,compno,compname
2 from product right outer join pdt_comp
3 using (pdtno) left outer join component using (compno)
4 where compname is not null
5* and pdtname is not null
ASH@orcl>/
PDTNO PDTNAME COMPNO COMPNAME
———- ———- ———- ———-
1 p1 1 c1
1 row selected.
Also, I can get the same result with Option A:
ASH@orcl>ed
Wrote file afiedt.buf
1 select pdtno,pdtname,compno,compname
2 from product join pdt_comp
3 using (pdtno) join component using (compno)
4 where compname is not null
5* and pdtname is not null
ASH@orcl>/
PDTNO PDTNAME COMPNO COMPNAME
———- ———- ———- ———-
1 p1 1 c1
1 row selected.