insert range of values in sql-CodePudding

CREATE TABLE RECHARGE(
R_LVL VARCHAR(20) NOT NULL PRIMARY KEY,
AMOUNT NUMBER,
POINTS_1 NUMBER
);

insert into RECHARGE
VALUES ('S1',(2950-4950),250);

i have the code above, and im trying to insert a range for the values in attribute amount, like this :

s1 : 2950-4950
s2: 5000-9950
s3: 10000-30000

so each lvl has it's own range of data, is it possible ?

CodePudding user response：

Number datatype cannot store special characters like -. You have to make it either VARCHAR or store 2 rows with single number like -

insert into RECHARGE
VALUES ('S1',2950,250);

insert into RECHARGE
VALUES ('S1',4950,250);

Or you can use 2 colums also like FROM-TO to show your date range like -

CREATE TABLE RECHARGE(
R_LVL VARCHAR(20) NOT NULL PRIMARY KEY,
AMOUNT_FROM NUMBER,
AMOUNT_TO NUMBER,
POINTS_1 NUMBER
);

insert into RECHARGE
VALUES ('S1',2950,4950,250);

Then in SELECT query you can generate the result like you want.

CodePudding user response：

Range consists of 2 values, store it in 2 columns.

The expression (2950-4950) equals to -2000 (2950 minus 4950).

You should create two columns or have only just one of the boundaries of the ranges stored. I suggest to store both values, because it is easier to query:

CREATE TABLE RECHARGE(
R_LVL VARCHAR(20) NOT NULL PRIMARY KEY,
LOWER_BOUNDARY NUMBER,
UPPER_BOUNDARY NUMBER,
POINTS_1 NUMBER
);

You need to decide if the boundaries are closed (<=, >=) or open (<, >).

So you can query them like this:

SELECT * FROM Recharge WHERE LOWER_BOUNDARY <= 2000 AND UPPER_BOUNDARY > 2000

Where 2000 is the value you are trying to find the range for.

You can make this thing more bulletproof by adding a few constraints:

CHECK constraint to make sure that LOWER_BOUNDARY is lower than UPPER_BOUNDARY.
UNIQUE on LOWER_BOUNDARY to eliminate repetition
FOREIGN KEY to reference the UPPER_BOUNDARY from the previous range for the current ones LOWER_BOUNDARY (boundaries are continuous)