i am having trouble with this code to create my table in oracle please let me kn

Question

i am having trouble with this code to create my table in oracle please let me know what is wrong in these codes and please provide me with the right code. thank you!

CREATE TABLE P_WORK_OF_ART (
ART_ID CHAR(5) NOT NULL,
ART_TITLE VARCHAR2(150) NOT NULL,
ART_DESC VARCHAR2(1000),
ART_PICFILE VARCHAR2(20) NOT NULL,
ART_CONDITION VARCHAR2(10) NOT NULL,
ART_COND_DATE DATE,
ART_CREATED_YEAR NUMBER(4) NOT NULL,
ART_RECORD_ENTERED_DATE DATE NOT NULL,
LOC_ID CHAR(5),
CONSTRAINTS P_WORK_OF_ART_PK PRIMARY KEY (ART_ID),
CONSTRAINTS P_WORK_OF_ART_FK FOREIGN KEY (LOC_ID) REFERENCES P_LOCATION (LOCATION_ID),
CONSTRAINTS ART_CREATED_YEAR CHECK (ART_CREATED_YEAR BETWEEN 1600 AND 2100),
CONSTRAINTS ART_PICFILE CHECK (UPPER(RIGHT(ART_PICFILE, 4)) IN ('.JPG')),
CONSTRAINTS ART_CONDITION CHECK (ART_CONDITION IN ('EXCELLENT', 'GOOD', 'FAIR', 'POOR', 'UNKNOWN')),
CONSTRAINTS ART_COND_DATE CHECK (ART_COND_DATE BETWEEN TO_DATE (CAST(ART_CREATED_YEAR AS CHAR(4))+'-01-01', 'YYYY/MM/DD') AND TO_DATE('2020-12-31', 'YYYY/MM/DD'))
);

Explanation / Answer

CREATE TABLE P_WORK_OF_ART (
ART_ID CHAR(5) NOT NULL,
ART_TITLE VARCHAR2(150) NOT NULL,
ART_DESC VARCHAR2(1000),
ART_PICFILE VARCHAR2(20) NOT NULL,
ART_CONDITION VARCHAR2(10) NOT NULL,
ART_COND_DATE DATE,
ART_CREATED_YEAR NUMBER(4) NOT NULL,
ART_RECORD_ENTERED_DATE DATE NOT NULL,
LOC_ID CHAR(5),
CONSTRAINTS P_WORK_OF_ART_PK PRIMARY KEY (ART_ID),
CONSTRAINTS P_WORK_OF_ART_FK FOREIGN KEY (LOC_ID) REFERENCES P_LOCATION (LOCATION_ID),
CONSTRAINTS ART_CREATED_YEAR CHECK (ART_CREATED_YEAR BETWEEN 1600 AND 2100),
CONSTRAINTS ART_PICFILE CHECK (UPPER(SUBSTR(ART_PICFILE, -4)) IN ('.JPG')),
CONSTRAINTS ART_CONDITION CHECK (ART_CONDITION IN ('EXCELLENT', 'GOOD', 'FAIR', 'POOR', 'UNKNOWN')),
CONSTRAINTS ART_COND_DATE CHECK (ART_COND_DATE BETWEEN TO_DATE (CAST(ART_CREATED_YEAR AS CHAR(4))||'-01-01', 'YYYY/MM/DD') AND TO_DATE('2020-12-31', 'YYYY/MM/DD'))
);

-- concatenate string with ||

--substring with negative number to count from right

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.