071-新增2

10. View the Exhibit and examine the description of the EMPLOYEES table. You want to calculate the total remuneration for each employee. Total remuneration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission. Which SQL statement would you execute to get the desired output?
    A) SELECT first_ name, salary, salary * 12+salary * commission_pct “Total” FROM EMPLOYEES;
    B) SELECT first_ name, salary, (salary+NVL(commission_ pct,0) * salary) * 12 “Total” FROM EMPLOYEES;
    C) SELECT first_ name, salary, salary * 12 + NVL(salary ,0) * commission_ pct “Total” FROM EMPLOYEES;
    D) SELECT first. name, salary, salary * 12+ (salary * NVL (commission_pct, salary,salary+commission_pct)) “Total” FROM EMPLOYEES;
11. View the Exhibit and examine the structure of the CUSTOMERS table. CUSTOMER_VU is a view based on CUSTOMERS BR1 table which has the same structure as CUSTOMERS table.CUSTOMERS needs to be updated to reflect the latest information about the customers. What is the error in the following MERGE statement?
    MERGE INTO customers c USING customer_ vu cv ON (c.customer_ id = cv. customer_ id) WHEN MATCHED THEN UPDATE SET WHEN NOT MATCHED THENc.customer_ id = cv.customer_ id, c.cust_ name = cv.cust_ name, c.cust_ email = cv.cust_ email, c. income_ level = cv. income_ level INSERT VALUES (cv. customer. id,cv. cust_ name,cv.cust email,cv.income level) WHERE cv. income_ level >100000;
    A) The CUSTOMER_ ID column cannot be updated.
    B) The INTO clause is misplaced in the command.
    C) The WHERE clause cannot be used with INSERT.
    D) CUSTOMER _vu cannot be used as a data source.
    判断Ｂ表和Ａ表是否满足 ON 中条件，如果满足则用 B 表去更新 A 表，如果不满足，则将 B 表数据插入 A 表但是有很多可选项， ON 里面用到的条件字段不能作为更新字段
12. View the Exhibit and examine the structure of the sales table.
    The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times.
    SQL> SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT( * )>10 GROUP BY prod_id HAVING COUNT( * )>10;
    Which statement is true regarding this SQL statement?
    A) It executes successfully and generates the required result.
    B) It produces an error because COUNT( * ) should be specified in the SELECT clause also.
    C) It produces an error because COUNT ( * ) should be only in the HAVING clause and not in the WHERE clause.
    D) It executes successfully but produces no result because COUNT(prod_ id) should be used instead of COUNT( * ).
    统计函数用法