INF3707 ASSIGNMENT 2 SOLUTIONS 2021

INF3707 ASSIGNMENT 2 SOLUTIONS 2021

Questions 1)

1.1)
CREATE SEQUENCE my_first_seq
INCREMENT BY -3
START WITH 5
MAXVALUE 5
MINVALUE 0
NOCYCLE;



1.2)

SELECT my_first_seq.NEXTVAL FROM DUAL;


Error Explanation: The sequence running out of values to issue because the minimum
value of 0 was reached and the CYCLE option is set to NOCYCLE.

, 1.3)

An error will occur because you cannot use SYSDATE in the condition of a check
constraint


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),
expiry_date date,
CONSTRAINT its_pky PRIMARY KEY (ord_no, item_no),
CONSTRAINT ord_fky FOREIGN KEY(ord_no) REFERENCES orders(order#) );


Question 2)

2.1)

SELECT title, retail - cost as profit FROM BOOKS WHERE (retail - cost) >= 10.00
ORDER BY 2 DESC;

2.2)