Art Art - 2 months ago 5
SQL Question

If customers can only use a maximum of 1,000 mileage points each time, how do I write a SQL statement that displays the following?

I am new to SQL and have been playing around with it. However, I've stumbled upon a problem that I cannot seem to solve:

If customers can only use a maximum of 1,000 mileage points each time, how do I write a SQL statement that displays their number, name, and usable mileage at this moment (which cannot exceed 1,000)?

The table below is what I am using to answer this question.

I am using SQL*Plus. Any help would be greatly appreciated! I suspect the greatest(totalmileage) function might be used here, but I haven't had much luck in figuring out exactly how.

SQL> select * from customers;

CNU CNAME ACCOUNTBALANCE TOTALMILEAGE
--- ---------- -------------- ------------
101 Andy 85.25 2152
102 Betty 170 108
103 Cindy -55.13 3
104 David 1308.02 5510
105 Ellen 99.77 11150
106 Frank -.02 80

Answer

You can use a CASE statement to return the available miles, which will be either 1000 if they have this many or more, or the balance if they have less:

SELECT CNU,
       CNAME,
       ACCOUNTBALANCE,
       TOTALMILEAGE,
       CASE WHEN COALESCE(TOTALMILEAGE, 0) > 1000
            THEN 1000
            ELSE COALESCE(TOTALMILEAGE, 0) END AS USABLEMILEAGE
FROM customers
Comments