RE student_name LIKE'R%';
C. SELECTSUM(subject1+subject2+subject3)
FROM marks
WHERE student_name ISNULL;
D. SELECT SUM(DISTINCTNVL(subject1,0)), MAX(subject1)
FROM marks
WHERE subject1 >subject2;
Answer: CD
解析：
A选项，不能在where后使用聚集函数
B选项，这里没有分组，无法使用sum
C，D正确
110. View the Exhibitand examine the structure of the CUSTOMERS table.
Using the CUSTOMERStable, you need to generate a report that shows the average credit limit for
customers in WASHINGTONand NEW YORK.
Which SQL statementwould produce the required result
A. SELECT cust_city,AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN('WASHINGTON','NEW YORK')
GROUP BYcust_credit_limit, cust_city;
B. SELECT cust_city,AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN('WASHINGTON','NEW YORK')
GROUP BYcust_city,cust_credit_limit;
C. SELECT cust_city,AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN('WASHINGTON','NEW YORK')
GROUP BY cust_city;
D. SELECT cust_city,AVG(NVL(cust_credit_limit,0))
FROM customers
WHERE cust_city IN('WASHINGTON','NEW YORK');
Answer: C
解析：
这里考察和上题的B选项一样，需要分组，才能使用avg()函数
所以正确答案为C选项
111. View the Exhibitand examine the structure of the CUSTOMERS table.
Which statement woulddisplay the highest credit limit available in each income level in each city inthe
CUSTOMERS table
A. SELECT cust_city,cust_income_level, MAX(cust_credit_limit )
FROM customers
GROUP BY cust_city,cust_income_level, cust_credit_limit;
B. SELECT cust_city,cust_income_level, MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city,cust_income_level;
C. SELECT cust_city,cust_income_level, MAX(cust_credit_limit)
FROM customers
GROUP BYcust_credit_limit, cust_income_level, cust_city ;
D. SELECT cust_city,cust_income_level, MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city,cust_income_level, MAX(cust_credit_limit);
Answer: B
解析：
这里也和上题考察的知识点一样，需要分组，并以cust_city和cust_income_level
所以正确答案为B选项
Answer: B
112. View the Exhibitand examine the structure of the PROMOTIONS table.
eva luate the followingSQL statement:
SQL>SELECTpromo_category, AVG(promo_cost) Avg_Cost, AVG(promo_cost)*.25 Avg_Overhead
FROM promotions
WHEREUPPER(promo_category) IN ('TV', 'INTERNET','POST')
GROUP BY Avg_Cost
ORDER BY Avg_Overhead;
The above querygenerates an error on execution.
Which clause in theabove SQL statement causes the error
A. WHERE
B. SELECT
C. GROUP BY
D. ORDER BY
Answer: C
解析：
Group by 后面不能用分组函数
所以C正确
113. Examine the structureof the ORDERS table:
Name Null Type
ORDER_ID NOT NULLNUMBER(12)
ORDER_DATE NOT NULLTIMESTAMP(6)
CUSTOMER_ID NOT NULLNUMBER(6)
ORDER_STATUS NUMBER(2)
ORDER_TOTAL NUMBER(8,2)
You want to find thetotal value of all the orders for each year and issue the following command:
SQL>SELECTTO_CHAR(order_date,'rr'), SUM(order_total)
FROM orders
GROUP BYTO_CHAR(order_date,'yyyy');
Which statement is trueregarding the outcome
A. It executessuccessfully and gives the correct output.
B. It gives an errorbecause the TO_CHAR function is not valid.
C. It executessuccessfully but does not give the correct output.
D. It gives an errorbecause the data type conversion in the SELECT list does not match the datatype
conversion in the GROUPBY clause.
Answer: D
解析：
To_char需要前后转换格式一样，测试：
scott@ORCL>select to_char(hiredate,'rr') from emp group byto_char(hiredate,'yyyy')
2 ;
s