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;
Can somebody explain, please?
I’ll try … though finding this one tricky myself.
SELECT custname, grade
FROM customers , grades
will give all combinations of customers and grades.
WHERE cust_credit_limit BETWEEN startval AND endval
Will reduce the results to only those where the credit limit is actually between startval and endval.
That is it will return those where the grade actually matches the cust_credit_limit correctly.
But in these results we still have some with low grade/low credit limit.
C.(the easy one I think)
WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit) FROM customers)
Will further reduce the result set to only those having max credit limit.
compared to B.:
WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and endval
Which will reduce the result set by fixing the grades side of things to only the top grade tier.
So C returns the customer(s) and grade for those with maximum cust_credit_limit value.
And B returns the customer(s) and grade with the highest grade.
Personally I think the results might be different if there is multiple customers in the top grade even though they might have slightly different cust_credit_limits.
Anyway:
A. is incorrect because it includes rows with cust_credit_limit not between startval and endval
D. returns an error because it has a group function in the where clause
I’d have perhaps expected a slight variation of D.) like so:
E.
SELECT custname, grade
FROM customers , grades
WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)
FROM customers)
AND (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval AND endval;
It would match the given task better … so it may be an error too.
I think
AND cust_credit_limit BETWEEN startval AND endval;
– This line combines two lines together
Should use = for single column subquery
So,
B & C