OCP-1Z0-051-V9.02-145题

tgidl

　　145. View the Exhibit and examine the structure of the CUSTOMERS table.
　　You issue the following SQL statement on the CUSTOMERS table to display the customers who are in the
　　same country as customers with the last name 'KING' and whose credit limit is less than the maximum  小于最大值
　　credit limit in countries that have customers with the last name 'KING':
　　SQL> SELECT cust_id,cust_last_name
　　FROM customers
　　WHERE country_id IN(SELECT country_id                     
　　FROM customers                     
　　WHERE cust_last_name ='King')
　　AND cust_credit_limit < (SELECT MAX(cust_credit_limit)                        
　　FROM customers                          
　　WHERE country_id IN(SELECT country_id                                          
　　FROM customers                                          
　　WHERE cust_last_name='King'));
　　Which statement is true regarding the outcome of the above query?
　　

　　
　　A. It executes and shows the required result.
　　B. It produces an error and the < operator should be replaced by < ALL to get the required output.
　　C. It produces an error and the < operator should be replaced by < ANY to get the required output.
　　D. It produces an error and the   IN operator should be replaced by = in the WHERE clause of the main
　　query to get the required output.
　　Answer: A
　　
　　答案解析：
　　1、首先找出the last name 'KING'所在countries
　　
sh@TEST0910> select country_id from customers where cust_last_name='King';COUNTRY_ID----------     52772     52790     52790     527792、再找出KING所在城市的最大的cust_credit_limitsh@TEST0910> select MAX(cust_credit_limit) from customers where country_id in  2  (select country_id from customers where cust_last_name='King');MAX(CUST_CREDIT_LIMIT)----------------------                 150003、再找出和KING所在一样城市的客户的id和姓名，他们的cust_credit_limit要小于KING所在城市的最大的cust_credit_limit的其余的&#20540;。&#20540;太多，去前5个。按题意，选A。sh@TEST0910> SELECT cust_id,cust_last_name FROM customers WHERE country_id IN  2  (select country_id from customers where cust_last_name='King') and cust_credit_limit <  3  (select MAX(cust_credit_limit) from customers where country_id in  4  (select country_id from customers where cust_last_name='King')) and rownum<6;   CUST_ID CUST_LAST_NAME---------- ----------------------------------------     49671 Ruddy      3228 Ruddy      6783 Ruddy     10338 Ruddy     13894 Ruddy　　
         版权声明：本文为博主原创文章，未经博主允许不得转载。