Week 10

Multiple Choice: #1 to #10

1.  Which of the following generates a series of integers that can be stored in a database?

            C. a sequence

2.  Which syntax is correct for removing a public synonym?

            C. DROP PUBLIC SYNONYM synonymname;

3.  Which of the following commands can you use to modify an index?

            E. none of the above

4.  Which of the following generates an integer in a sequence?

            A. NEXTVAL

5.  Which of the following is a valid SQL statement?

            E. only a and c

6.  Suppose the user Juan creates a table called MYTABLE with four columns. The first column has a PRIMARY KEY constraint, the second column has a NOT NULL constraint, the third column has a CHECK constraint, and the fourth column has a FOREIGN KEY constraint. Given this information, how many indexes does Oracle 12ccreate automatically when the table and constraints are created?

            D. 3

7.  Given the table created in Question 6, which of the following commands can Juan use to create a synonym that allows anyone to access the table without having to identify his schema in the table reference?

            B. CREATE PUBLIC SYNONYM

            the table
            FOR mytable;

8.  Which of the following statements is true?

            b. Any unassigned sequence values appears in the USER_SEQUENCE data          dictionarytable as unassigned.

9.  When is creating an index manually inappropriate?

            D. all of the above

10. If a column has high selectivity or cardinality, which index type is most appropriate?

            B. B-tree

Hands-on Exercises: #1 to #3

1. 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. The options should be set to not cycle the values and not cache any values, and no minimum or maximum values should be declared.

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.

            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 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.

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

Extra credit: Review Questions #1, #2

1. How can a sequence be used in a database?

            Used to generate a series of integers.

2. How can gaps appear in values generated by a sequence?

If values are stored in different tables.
If numbers are cached but not used.
If a rollback occurs.