Subject: Oracle DBA Problem C: Tablespace and Indexes (a) Write SQL statements t

Question

Subject: Oracle DBA

Problem C: Tablespace and Indexes
(a) Write SQL statements to create the following tables as user SCOTT for an
order entry system that you are implementing. The tables and the columns are
shown below:

Use tablespace USER5.
(b) How do you find out which files and blocks contain the rows for the orders
table?
(c) You are now considering creating indexes on the NAME and REGION
columns of the CUSTOMERS table. Suppose there are many distinct values for
column NAME, but only a few distinct values for column REGION. What types of
index are appropriate for the two columns? Write SQL to create the indexes,
naming them CUST_NAME_IDX and CUST_REGION_IDX, respectively, and
placing them in tablespace indx05.

Data Type and Size Table Column VARCHAR 2 (3) CUSTOMERS CUST CODE 5 VARCHAR 2 (50) NAME REGION VARCHAR 2 (5) NUMBER (3) ORDERS ORD ID ORD DATE DATE VARCHAR 2 (3) CUST CODE DATE OF DELY DATE

Explanation / Answer

(a). CREATE TABLESPACE USER5

CREATE TABLE CUSTOMERS (

CUST_CODE varchar2 (3)

NAME varchar2 (50)

REGION varchar2 (5)

);

CREATE TABLE ORDERS (

ORD_ID number (3)

ORD_DATE date

CUST_CODE varchar2 (3)

DATE_OF_DELY date

);

(b). To find all the coloumns or the files and blocks in the rows of any table we use the SELECT statement.

Here,

SELECT * FROM ORDERS;

This statement will select all the fields available in the table ORDERS.

(c). CREATE TABLESPACE INDX05

CREATE INDEX CUST_NAME_IDX

   ON CUSTOMERS (NAME) ;

CREATE INDEX CUST_REGION_IDX

   ON CUSTOMERS (REGION) ;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---