See the exhibit and examine the structure of the CUSTOMERS and GRADES tables:
You need to display names and grades of customers who have the highest credit limit.
Which two SQL statements would accomplish the task? (Choose two.)
A.
SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval;
B.
SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval
AND cust_credit_limit BETWEEN startval AND endval;
C.
SELECT custname, grade
FROM customers, grades
WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)
FROM customers)
AND cust_credit_limit BETWEEN startval AND endval;
D.
SELECT custname, grade
FROM customers , grades
WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)
FROM customers)
AND MAX(cust_credit_limit) BETWEEN startval AND endval;
why is b and c correct?
B is correct because return all the clients which have the highest limit.
C is not correct because return one customers or more which have only a value in the limit
CUSTNO CUSTNAME CUSTADDRESS CUST_CREDIT_LIMIT
———- ———- ——————– —————–
1 CUST1 ADR1 2000
2 CUST2 ADR2 5000
3 CUST3 ADR3 7000
G STARTVAL ENDVAL
– ———- ———-
C 0 4999
B 5000 9999
A 10000 99999
B return
CUSTNAME G
———- –
CUST2 B
CUST3 B
C return
CUSTNAME G
———- –
CUST3 B
which is not correct in my opinion ! Because the question ask all the customers with a higher range not the CUSTOMER with a higher range.
Regards!
Because the all queries are conducting cartesian product in join. Then first part in where clause (subquery with avg fuc) will get you all max values including matches which cartesian retrives which are not actual match for customers credit limit. To filter that result you apply the second part of where clause which only retrives the highest credit limit value and grade.
I understand the answer c but why is b correct?
Yop, depending on data B can return more rows than C, C filters just max credit limit customers while B filters customers whose credit limit falls into same grade interval as max credit limit falls into. But if you have to choose two options, then B and C is correct answer.
i think B is wrong it should be C and D
I did not test it, but I think D option will produce an error for second max in where clause, I don’t think you can put it this way, you need to use even subquery or having clause to create condition on aggregate function.