QUESTION 16
Evaluate the following two queries:
<code>SQL > SELECT cust_last_name, cust_city
\t\t\t\t\t\tFROM customers
WHERE cust_credit_limit IN (1000, 2000,
3000)/<code>
Which statement is true regarding the above two queries?
A. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.
B. There would be no change in performance.
C. Performance would degrade in query 2.
D. Performance would improve in query 2.
答案：B
解析：在简单查询中，IN和OR 效率相同。
QUESTION 17
Examine the business rule:
Each student can work on multiple projects and each project can have multiple students.
You must design an Entity Relationship (ER) model for optimal data storage and allow for generating reports in this format:
<code>STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK/<code>
Which two statements are true? (Choose two.)
A. The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities.
B. The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships.
C. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
D. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
E. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.
答案：BE
解析：两个实体不能是多对多的关系。
QUESTION 18
View the Exhibit and examine the details of PRODUCT_INFORMATION table.
You have the requirement to display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
<code>SELECT product_name
FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088;/<code>
Which statement is true regarding the execution of the query?
A. It would not execute because the same column has been used in both sides of the AND logical operator to form the condition.
B. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.
C. It would execute and the output would display the desired result.
D. It would execute but the output would return no rows.
答案：D
解析：WHERE 子句有逻辑问题(category_id = 12 AND category_id = 13)，但是SELECT语句会执行。
QUESTION 19
Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)
A. The outer query stops evaluating the result set of the inner query when the first value is found.
B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.
答案：AC
解析：关键字EXISTS可检测一个子查询是否返回了行。
QUESTION 20
View the exhibit and examine the structure of the STORES table.
<code>Name NULL? Type
------------------ -------------- ----------------
STORE_ID NUMBER
NAME VARCHAR2(100)
ADDRESS VARCHAR2(200)
CITY VARCHAR2(100)
COUNTRY VARCHAR2(100)
START_DATE DATE
END_DATE DATE /<code>
You must display the NAME of stores along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price.
The stores displayed must have START_DATE in the range of 36 months starting from 01-Jan-2000 and above.
Which SQL statement would get the desired output?
<code>A. SELECT name, concat (address| | ','| |city| |', ', country) AS full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36;/<code>
<code>B. SELECT name, concat (address| | ','| |city| |', ', country) AS full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;/<code>
<code>C.SELECT name, address||', '||city||', '||country AS full_address, start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;/<code>
<code>D. SELECT name, concat (address||','| |city| |', ', country) AS full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;/<code>
答案：C
解析：
- concat一次连接两个字段（可嵌套），||可以同时连接多个
- MONTHS_BETWEEN（d1，d2），返回d1和d2的时间间隔（d1-d2，较晚日期较大）
本人水平有限，欢迎指正。
閱讀更多 洞察數據 的文章
