Exam (elaborations)
INF3707 Assignment 3
- Course
- Institution
INF3707 Assignment 3
[Show more]
Seller
Follow
MasterVincent
Reviews received
Content preview
INF3707Assignment 3 2023
[School]
[Course title]
, Question 1: Chapter 6 (11 marks) 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. (5
marks) 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? (2 marks) 1.3 Evaluate the following SQL
commands: CREATE SEQUENCE ord_seq Increment by 10 Start with 120
Maxvalue 9999 Nocycle; 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
CHECK (expiry_date > SYSDATE), CONSTRAINT its_pky PRIMARY KEY
(ord_no, item_no), CONSTRAINT ord_fky FOREIGN KEY(ord_no)
REFERENCES orders(order#) ); The command to create the table fails. What
caused the failure? Rewrite the CREATE Table ord_items code to correct the
error. (4 marks)
1.1 Creating the sequence MY_FIRST_SEQ that generates integers starting
with the value 5, decreasing by three, and with a minimum value of 0:
sqlCopy code
CREATE SEQUENCE MY_FIRST_SEQ START WITH 5 INCREMENT BY -3 MINVALUE 0
NOCYCLE;
1.2 Issuing a SELECT statement to display the NEXTVAL for MY_FIRST_SEQ
three times using the dual table:
sqlCopy code
SELECT MY_FIRST_SEQ.NEXTVAL FROM dual; -- 1st time SELECT MY_FIRST_SEQ.NEXTVAL
FROM dual; -- 2nd time SELECT MY_FIRST_SEQ.NEXTVAL FROM dual; -- 3rd time (Error)
The error on the third SELECT occurs because the sequence has reached its
minimum value, which is 0, and it cannot generate negative values due to
the NOCYCLE option. When the sequence reaches 0, further calls to NEXTVAL
will result in an error.
1.3 The provided SQL commands have a couple of issues:
1. The referenced table "orders" is not created, so the foreign key
constraint will fail. We need to ensure the referenced table exists
before creating the "ord_items" table.
2. The column "item_no" is missing its data type in the "ord_items" table.
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller MasterVincent. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $2.70. You're not tied to anything after your purchase.
Can Stuvia be trusted?
4.6 stars on Google & Trustpilot (+1000 reviews)
79223 documents were sold in the last 30 days
Founded in 2010, the go-to place to buy study notes for 14 years now