Q._39
Which statement about multiple sub-queries is True?
A. A pair wise comparison produces a cross product.
B. A non-pair wise comparison produces a cross product.
C. In a pair wise subquery, the values returned from the subquery are compared
individually to the values in the outer query.
D. In a non-pair wise subquery, the values returned from the subquery are
compared as a group to the values in the outer query.
Q._50
Mr. King is the president of a company. Five managers report to him. All other
employees report to these managers. Examine the code.
SELECT employee.ename
FROM emp employee
WHERE employee. empno not in
SELECT manager.mgr
FROM emp manager;
The above statement returns no rows selected as the result why?
A. All employees have a manager.
B. None of the employees have a manager.
C. A null value is returned from the sub query.
D. Operator is not allowed in sub queries.
Answer: C
Explanation:
Answer C is correct because a null value is returned from the sub query (president of a
company does not have manager for himself).
Q._57
Evaluate the SQL statement.
SELECT e.id, (.15* e.salary) + (.25* e.bonus),
(s.sale_amount * (.15* e.commision_pct))
FROM employee e , sales
WHERE e.id = s.emp_id;
What would happen if you removed all the parenthesis from calculation?
A. Results will be lower.
B. Results will be higher.
C. Statement will not execute.
D. Statement will achieve some results.
Answer: C
Explanation:
Answer C is correct because after removing all the parenthesis the statement will have
incorrect syntax.
Q._106
Examine the structure of the product and the part tables.
There points a line from id PK to product_id
PRODUCT
id PK Name
PART
id PK Name Product_id Cost
You issue this SQL statement:
SELECT pr.name
FROM part pt, product pr
WHERE pt.product_id(+)=pr.id;
What is the result?
A. An error is generated.
B. A list of product names is displayed.
C. A list of all products is displayed for product with parts.
D. A list of product is displayed for parts that have product assigned.
Answer: A
Explanation:
Answer A is correct because outer join operator need be used for the PRODUCT_ID
column from the PART table with different order of columns:
WHERE pr.id=pt.product_id(+);
Incorrect Answers:
B: A list of product names will not be displayed because of an error.
C: A list of all products for product with parts will not be displayed because of an error.
D: A list of product for parts that have product assigned will not be displayed because of
an error.
Q39：所谓pair，应该是这种形式：where (a, b) in (select a, b from ..)，而non-pair，应该是这种形式：where a in (select a from ....)and b in (select b from ....)，然后再去理解一下吧。
Q50：子查询的结果中如果有null值，假设结果为：1、2、null三个，那么用IN时，还可以相当于是 IN (1, 2)。但是如果用not in 时，主查询不会有结果返回。因为null与任何值做比较的话，都不会成立。not in (1, 2, null)相当与1 and2 andnull，但是null 不管怎么样，都不会成立的。
Q57：按题目来说，去掉所有的括号后，结果应该还是和原来一样，可能TK的题目有误。
Q106：应该 pt.product_id(+)=pr.id 和 pr.id=pt.product_id(+) 是一样的结果，应该是题目有误。结果应该是part所有的记录，包括对应了product和没有对应product的记录。
