ASSIGNMENT 03 2023 ANSWERS
Note code answers highlighted in yellow
INF3707
Question 1
1.1 CREATE SEQUENCE MY_FIRST_SEQ
START WITH 5
INCREMENT BY -3
MINVALUE 0
MAXVALUE 5
NOCYCLE;
PIC OF CODE
Explanation:
1. CREATE SEQUENCE: This SQL statement is used to create a new sequence.
2. MY_FIRST_SEQ: This is the name given to the sequence.
3. START WITH 5: It sets the initial value of the sequence to 5.
4. INCREMENT BY -3: The sequence increments or, in this case, decrements by -3 for
each new value generated.
, 5. MINVALUE 0: This sets the lower bound for the sequence, ensuring it never goes
below 0.
6. MAXVALUE 5: This sets the upper bound for the sequence, ensuring it does not
exceed 5.
7. NOCYCLE: This option prevents the sequence from cycling, meaning it will stop
once it reaches the maximum or minimum value and not wrap around to the
start value.
With these settings, the sequence will generate the following values: 5, 2, -1, 0. Since
the minimum value is set to 0 and the sequence does not cycle, it will stop at 0 and
not continue further.
1.2
SELECT my_first_seq.NEXTVAL
FROM DUAL;
Explanation: Caused by the sequence running out of values to issue, as the minimum value of 0 was
reached and the CYCLE option is set to NOCYCLE.
1.3
Error details :
-DDL statement for ord_items table will check the expiry_date with the sysdate
-Each time system date will change and hence which will not validate
-Instead add a column with todays_date date default sysdate which will add the todays date
into the column and then this date is checked with expiry_date
Sequence :
, Create Sequence ord_seq
Increment by 10
Start with 120
MaxValue 9999
nocycle;
Table Name : ord_items
create table ord_items
(ord_no number(4) default 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));
Note code answers highlighted in yellow
INF3707
Question 1
1.1 CREATE SEQUENCE MY_FIRST_SEQ
START WITH 5
INCREMENT BY -3
MINVALUE 0
MAXVALUE 5
NOCYCLE;
PIC OF CODE
Explanation:
1. CREATE SEQUENCE: This SQL statement is used to create a new sequence.
2. MY_FIRST_SEQ: This is the name given to the sequence.
3. START WITH 5: It sets the initial value of the sequence to 5.
4. INCREMENT BY -3: The sequence increments or, in this case, decrements by -3 for
each new value generated.
, 5. MINVALUE 0: This sets the lower bound for the sequence, ensuring it never goes
below 0.
6. MAXVALUE 5: This sets the upper bound for the sequence, ensuring it does not
exceed 5.
7. NOCYCLE: This option prevents the sequence from cycling, meaning it will stop
once it reaches the maximum or minimum value and not wrap around to the
start value.
With these settings, the sequence will generate the following values: 5, 2, -1, 0. Since
the minimum value is set to 0 and the sequence does not cycle, it will stop at 0 and
not continue further.
1.2
SELECT my_first_seq.NEXTVAL
FROM DUAL;
Explanation: Caused by the sequence running out of values to issue, as the minimum value of 0 was
reached and the CYCLE option is set to NOCYCLE.
1.3
Error details :
-DDL statement for ord_items table will check the expiry_date with the sysdate
-Each time system date will change and hence which will not validate
-Instead add a column with todays_date date default sysdate which will add the todays date
into the column and then this date is checked with expiry_date
Sequence :
, Create Sequence ord_seq
Increment by 10
Start with 120
MaxValue 9999
nocycle;
Table Name : ord_items
create table ord_items
(ord_no number(4) default 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));
