Oracle 1Z0-007: Creating and Managing Tables

Creating and Managing Tables

Describe the main database objects

INDEX

An index can be defined on one or more columns in a table (these columns are referred to as the indexed columns). The index maps each set of indexed column values to the set of unique identifiers for the rows that have those column values. This structure provides a quick way to find the rows within a table using the indexed column(s). An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS];

/*example*/
create index person_idx
ON person (last_name);

SEQUENCE

a sequence is an object in Oracle that is used to generate a number sequence

CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;

/*For example:*/
CREATE SEQUENCE supplier_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

TABLE

A table can be the basic component of a relational database (RDB). It has a number of records (rows) and fields (columns), similar to a spreadsheet), and can be manipulated using SQL commands.

purgeNormally, a table is moved into the recycle bin (as of Oracle 10g), if it is dropped. However, if the purge modifyer is specified as well, the table is unrecoverably (entirly) dropped from the database.
cascade constraints Deletes all foreign keys that reference the table to be dropped, then drops the table.

/*create a table*/create table orders
(
    order_id number primary key
    order_dt date,
    cust_id references customers
)

/*drop a table*/
drop table table_name;/
drop table table_name cascade constraints;/
drop table table_name purge;/
/
/*alter table*/
ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (col1, col2)
REFERENCES table_2 (cola,colb);

VIEW

a view is a virtual or logical table composed of the result set of a pre-compiled query. Unlike ordinary tables in a relational database, a view is not part of the physical schema: it is a dynamic, virtual table computed or collated from data in the database

/*create view*/
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

/*alter view*/ ALTER VIEW All_authors (au_fname, au_lname, address, city, zip)
AS
SELECT au_fname, au_lname, address, city, zip
FROM pubs..authors
WHERE state = 'UT'

/*delete view*/
delete view pubs_view

CLUSTER

PROCEDURE

SYNONYM

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

TRIGGER

Triggers are a special PL/SQL construct similar to procedures. However, a procedure is executed explicitly from another block via a procedure call, while a trigger is executed implicitly whenever the triggering event happens. The triggering event is either a INSERT, DELETE, or UPDATE command. The timing can be either BEFORE or AFTER. The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement.

CONSUMER GROUP

A resource consumer group is a group of active user sessions and defines a logicalcomponent of a workload. This is a concept specified by the administrator. Resource consumer groups may bedefined at the granularity of the user so that all sessions created by a particular user are assigned to the samegroup. Group association for a user session may be changed dynamically from the user's default so that morefine-grained division of CPU time can be achieved across user-sessions.

RESOURCE PLAN

A resource plan is a description of how CPU time should be allocated between different consumer groups

FUNCTION

/*syntax*/
CREATE [OR REPLACE] FUNCTION function_name
    [ (parameter [,parameter]) ]
 RETURN return_datatype
IS | AS
    [declaration_section]
BEGIN
    executable_section
[EXCEPTION
    exception_section]
END [function_name];

/*create function*/
CREATE OR REPLACE Function GetCourse
(name_in IN varchar2)
RETURN number
IS
    cnumber number;

    cursor c1 is
    select course_number
    from courses_tbl
    where course_name = name_in;

BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
    cnumber := 9999;
end if;
close c1;
RETURN cnumber;
EXCEPTION
WHEN OTHERS THEN
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

/*call function in the statement*/
select course_name, GetCourse(course_name) as course_id
from courses
where subject = 'CS';


INDEXTYPE

JAVA CLASS

JAVA RESOURCE

LIBRARY

OPERATOR

Comparison Operators

=

true if two expressions are equal

!= ^= -= <>

logically equivalent-true if two expressions are not equal

>

True if left expression is greater than right expression

>=

True if left expression is greater than or equal to right expression

<

True if left expression is less than right expression

<=

True if left expression is less than or equal to right expression

IN

Is equal to any member of a set or subquery

NOT IN

Does NOT equal any member of a set or subquery

AND, SOME

True if one or more of the values in the list of expressions or subquery satisfies the condition

ALL

True if all of the values in the list of expressions or subquery satisfies the condition

BETWEEN x AND y

True if greater than or equal to x and less than or equal to y (can be reversed in meaning with NOT)

EXISTS

True if the subquery returns at least one row (can be reversed in meaning with NOT)

LIKE pattern [ESCAPE 'c']

True if expression or subquery matches pattern. '%' matches any sequence of characters, '_' matches any single character. If ESCAPE is used, the character 'c' causes the character following to be taken literally (can be reversed in meaning with NOT).

IS NULL

TRUE if the value is NULL (can be reversed in meaning with NOT)

Select Operators

UNION

This combines the results of two queries and returns the set of distinct rows returned by either query

UNION ALL

This combines the results of two queries and returns all rows returned by either query, including duplicates

INTERSECT

This combines the results of two queries and returns the set of distinct rows returned by both queries

MINUS

This combines the results of two queries and returns the distinct rows that were in the first query, but not in the second

Other Select Operators

(+)

Denotes that the preceding column is an outer join

*

Wildcard operator. Equals all columns in a select statement

PRIOR

Denotes a parent-child relationship in a tree-structured query

ALL

Include all duplicate rows in a query (the default)

DISTINCT

Eliminate duplicates in a result set

Logical Operators

||

Concatenates two character (string) values

NOT

Reverses the meaning of another logical expression's result

OR

Logical OR-True if any are true, false else

AND

Logical AND-True if all are true, else false

PACKAGE

/*syntax*/
CREATE OR REPLACE PACKAGE <package_name> AS
PROCEDURE <procedure_name> (<parameters>);
END <package_name>;

/*example*/
CREATE OR REPLACE PACKAGE one_proc AS
PROCEDURE get_table(number_in IN PLS_INTEGER);
END one_proc;

PACKAGE BODY

/*syntax*/
CREATE OR REPLACE PACKAGE BODY <package_name> AS
PROCEDURE <procedure_name> (<parameters>) IS
<define local variables, constants, and exceptions>
BEGIN
<procedure_code>;
END <procedure_name>;
END <package_name>;

/*example*/
CREATE OR REPLACE PACKAGE BODY one_proc AS
PROCEDURE get_table (number_in IN PLS_INTEGER) IS
tabname user_tables.table_name%TYPE;
BEGIN
SELECT table_name
INTO tabname
FROM user_tables
WHERE rownum < number_in;
dbms_output.put_line(tabname);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Too Many Rows');
END get_table;


QUEUE

scheduled jobs

Create tables

Describe the datatypes that can be used when specifying column definition

char(n)

A fixed length character string,will take up 200 bytes regardless of how long the string actually is. The maximum length of char is 2000 bytes.

varchar(n)

A variable length character string, that will take up space accordingly.

CHAR

Up to 32767 bytes in PLSQL. Up to 2000 bytes in Oracle 8i

NCHAR

Up to 32767 bytes in PLSQL. Up to 2000 bytes in Oracle 8i

VARCHAR2

Up to 32767 bytes in PLSQL. Up to 4000 bytes in Oracle 9i.

NVARCHAR

Up to 32767 bytes in PLSQL. Up to 4000 bytes in Oracle 9i.

DATE

A date between Jan 1, 4712 BC and Dec 31, 9999 AD.

NUMBER

The maximum precision is 38 digits.

LONG

Up to 2 gigabytes.

RAW

Up to 32767 bytes in PLSQL. Up to 2000 bytes in Oracle 9i.

LONG RAW

Up to 2 gigabytes.

MISLABEL

BLOB

Up to 4 gigabytes. LOB locators that point to a large binary object within the database

CLOB

Up to 4 gigabytes. LOB locators that point to a large character object within the database

NCLOB

Up to 4 gigabytes. LOB locators that point to a large NLS character object within the database

BFILE

Up to 4 gigabytes. File locators that point to a read-only binary object outside of the database

ROWID

Fixed-length binary data. Every record in the database has a physical address or rowid.

UROWID

Up to 2000 bytes. Universal rowid. Where size is optional.

Alter table definitions

Drop, rename and truncate tables

Drop, rename and truncate tables