Answer to Question #62310 in Databases | SQL | Oracle | MS Access for johnny

Question

1- Creating Sequences.
a. Connect as the DBA. Create a sequence named VENDOR_SEQUENCE that starts with the value 1 and is incremented by 1. Set the values for MINVALUE = 1 and MAXVALUE = 99999. Allow the sequence to CYCLE automatically and allocate NOCACHE as a sequence option.
b. Attempt to SELECT a value from the VENDOR_SEQUENCE by using the DUAL table in your SELECT command.
2- a range-based partitioning table named p_student with attributes empno,empname,job,salary,department no. Make sure that the data entry of the each department goes to its own provided tablespaces such as the cs department goes to the dept10ts tablespace, the data entry of the cis department goes to the dept20ts tablespace, OTHERS goes to other_dept tablespace. specify the storage size parameter .
3- have the parallel query option, then the fastest method to create a table that has the same columns as the p_STUDENT table, but only for those student in department CS.

Answer 1

1. Creating Sequences

create sequence VENDOR_SEQUENCE start with 1 increment by 1 minvalue 1 maxvalue 99999 cycle nocache;

select VENDOR_SEQUENCE.NEXTVAL from DUAL;

2. Range-based partitioning tables

create table P_STUDENT (
EMPNO number,
EMPNAME varchar2(64),
JOB varchar2(64),
SALARY number(9, 2),
DEPARTMENT_NO varchar2(5)
)
partition by list (DEPARTMENT_NO) (
partition q_cs values ('CS') tablespace dept10ts,
partition q_cis values ('CIS') tablespace dept20ts,
partition q_others values (DEFAULT) storage (initial 2M) tablespace other_dept
);

3. Parallel query option

create table P_STUDENT_CS as
(select * from P_STUDENT where DEPARTMENT_NO = 'CS');