Hands-On Assignments To perform the following assignments, refer to the tables i

Question

Hands-On Assignments To perform the following assignments, refer to the tables in the JustLee Books database. I. Create a sequence for populating the Customer# column of the CUSTOMERS table. When setting the start and increment values, keep in mind that data already exists in this table. Chapter 6 Copyright 3016 Cengage Learning All Rights Reserved. May not be copied, scanned, or duplicated, in ghole or Edtorial review has decmed that any sappressed cemest does not materially affect the electrtek rid y crment may be ees.cd from the cBook·ndor capens). h right o remove additional content at any snt rights restrctions require it The options should be set to not cycle the values and not cache any values, and no minimum or maximum values should be declared. 2. Add a new customer row by using the sequence created in Question 1. The only data currently available for the customer is as follows: last name = Shoulders, first name Frank, and zip = 23567. 3. Create a sequence that generates integers starting with the value 5. Each value should be three less than the previous value generated. The lowest possible value should be 0, and the sequence shouldn't be allowed to cycle. Name the sequence MY_FIRST SEQ. 223 4. Issue a SELECT statement that displays NEXTVAL for MY FIRST SEQ three times. Because the value isn't being placed in a table, use the DUAL table in the FROM clause of the SELECT statement. What causes the error on the third SELECT? 5. Change the setting of MY_FIRST_SEQ so that the minimum value that can be generated is -1000

Explanation / Answer

                                                        Hands-On Assignments :-

1. Create a sequence for populating the Customer# column in the CUSTOMERS table. When setting the start and increment values, keep in mind that data already exists in this table. The options should be set to not cycle the values and not cache any values, and no minimum or maximum values should be declared.

answer =>

     create sequence customers_customer#_seq
      increment by 1
      start with 10
      nocache
      nominvalue
      nomaxvalue
      nocycle;

-------------------------------------------------------------------------------------------------------------------------------------

2. Add a new customer row by using the sequence created in Question 1. The only data currently available for the customer is as follows: last name: Shoulders, first name: Frank, and zip: 23567

ans =>

insert into customers (customer#, lastname, firstname, zip)
Values(customers_customer#_seq.NEXTVAL, 'Shoulders', 'Frank', 23567);

-----------------------------------------------------------------------------------------------------------------------------------

3. Create a sequence that generates integers starting with the value 5. Each value should be 3 less than the previous value generated. The lowest possible value should be 0, and the sequence should not be allowed to cycle. Name the sequence MY_FIRST_SEQ.

ans=>

create sequence my_first_seq
increment by -3
start with 5
MINVALUE 0
MAXVALUE 5
NOCYCLE;

-------------------------------------------------------------------------------------------------------------------------------

4. Issue a SELECT statement that displays NEXTVAL for MY_FIRST_SEQ three times. Because the value isn't being placed in a table, use the DUAL table in the FROM clause of the SELECT statement. What causes the error on the third SELECT?

ans=>

SELECT my_first_seq FROM DUAL;

-------------------------------------------------------------------------------------------------------------------------------

5. Change the setting of MY_FIRST_SEQ so that the min value that will be generated is -1000.

ans=>

Alter sequence my_first_seq
MINVALUE -1000;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

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