Question

Would anyone know how to code this in SQL? I can't quite figure it out.

The following drop command is inserted for convenience so that if you need to recompile your code, it will drop the table

DROP TABLE Orders Cascade constraints

DROP TABLE OrderLine CASCADE CONSTRAINTS;

-- CREATE TABLE Orders

(

ordernum INTEGER PRIMARY KEY,

priority CHAR(10) NOT NULL,

cost INTEGER NOT NULL, /*

IC1: The priority is one of: high, medium, or low */

>>

/* IC2: The cost of a high priority order is above 2000. */

>>

/* IC3: The cost of a medium priority order is between 800 and 2200 (inclusive). */

>>

/* IC4: The cost of a low priority order is less than 1000. */

>>

Thank you in advance!

Answer 1

Here I am putting the constraints only:

CONSTRAINT IC1 CHECK (priority='high' OR priority='medium' OR priority='low'),
CONSTRAINT IC2to4 CHECK (
                         (priority = 'high'   AND cost > 2000) OR
                         (priority = 'medium' AND cost >= 800 AND cost <= 2200) OR
                         (priority = 'low'   AND cost > 1000) 
                       )

The Complete SQL create table query will look like:

CREATE TABLE Orders

(

ordernum INTEGER PRIMARY KEY,

priority CHAR(10) NOT NULL,

cost INTEGER NOT NULL, 

CONSTRAINT IC1 CHECK (priority='high' OR priority='medium' OR priority='low'),
CONSTRAINT IC2to4 CHECK (
                         (priority = 'high'   AND cost > 2000) OR
                         (priority = 'medium' AND cost >= 800 AND cost <= 2200) OR
                         (priority = 'low'   AND cost > 1000) 
                       )

);

Still any doubt do comment, else give a upvote. Thanks.