INF assignment 3 solutions

INF3707 2023 ASSIGNMENT 3
SOLUTIONS

Crystal Indigo!
Crystal Indigo!
Providing all solutions you need anytime
+27 76 626 8187

, Question 1 Chapter 6
1.1 Create a sequence that generates integers starting with the value
5. Each value should be three less than the previous value generated.
The lowest value should be 0, and the sequence should not be allowed
to cycle. Name the sequence MY_FIRST_SEQ.
CREATE SEQUENCE MY_FIRST_SEQ
INCREMENT BY -3
START WITH 5
MAXVALUE 5
MINVALUE 0
NOCYCLE;

1.2 Issue a SELECT statement that displays NEXTVAL for
MY_FIRST_SEQ three times. Since the value is not being stored in the
table, use dual table in the FROM clause of the SELECT statement. What
caused the error on the third SELECT?
SELECT MY_FIRST_SEQ.NEXTVAL
FROM DUAL;
you would encounter an error similar to "ORA-08002: sequence MY_FIRST_SEQ.NEXTVAL
exceeds MAXVALUE and cannot be instantiated." This error occurs because the sequence has
reached its maximum value and cannot generate a new value.

1.3 Evaluate the SQL commands


Generally the error is being caused when working with SYSDATE, the table is checking the
expiry_date with SYSDATE. So each time when the system date change it will not be validated.
create table ord_items
(ord_no NUMBER(4) DEFAULT ord_seq.ord_seq.NEXTVAL NOT NULL,
Item_no NUMBER(3),
qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),
todays_date date DEFAULT SYSDATE,
expiry_date date ,
CONSTRAINT its_pky PRIMARY KEY (ord_no,Item_no),
CONSTRAINT ord_fky FOREIGN KEY (ord_no) references orders(order#),
CONSTRAINT check_expiry CHECK(expiry_date> todays_date));