DML Queries and the Where clause Using the ssh Oracle databa

***DML Queries and the Where clause “Using the ssh, Oracle database 11g”

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

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.

4-Change the setting of MY_FIRST_SEQ so that the minimum value be generated is -1000

Create an Index

5-Create a bitmap index on the customers table to speed up queries that search for customers based on the state they live in.

6-Write a query that will verify that the index exists

7-Write a query that will remove the index you created question 5

Solution

1.
CREATE SEQUENCE customer_seq
START WITH 1021
INCREMENT BY 1
NOCYCLE
NOCACHE
NOMINVALUE
NOMAXVALUE;

2.
Insert into CUSTOMERS (customer#, lastname, firstname, zip)
Values (customers_seq.NEXVAL, \'SHOULDERS\', \'FRANK\', \'23567\');

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

4.
Alter sequence my_first_seq
MINVALUE -1000

5.
create bitmap index customers_state_idx
on customers (state);