Oracle SEQUENCES

Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. After a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

After a sequence is created, you can access its values in SQL statements with the CURRVAL pseudocolumn, which returns the current value of the sequence, or the NEXTVAL pseudocolumn, which increments the sequence and returns the new value.

Prerequisites

To create a sequence in your own schema, you must have the CREATE SEQUENCE system privilege.

To create a sequence in another user's schema, you must have the CREATE ANY SEQUENCE system privilege.

Syntax

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

Sequence

Specify the name of the sequence to be created.

If you specify none of the following clauses, then you create an ascending sequence that starts with 1 and increases by 1 with no upper limit. Specifying only INCREMENT BY -1 creates a descending sequence that starts with -1 and decreases with no lower limit.

• To create a sequence that increments without bound, for ascending sequences, omit the MAXVALUE parameter or specify NOMAXVALUE. For descending sequences, omit the MINVALUE parameter or specify the NOMINVALUE.
• To create a sequence that stops at a predefined limit, for an ascending sequence, specify a value for the MAXVALUE parameter. For a descending sequence, specify a value for the MINVALUE parameter. Also specify NOCYCLE. Any attempt to generate a sequence number once the sequence has reached its limit results in an error.
• To create a sequence that restarts after reaching a predefined limit, specify values for both the MAXVALUE and MINVALUE parameters. Also specify CYCLE. If you do not specify MINVALUE, then it defaults to NOMINVALUE, which is the value 1.

INCREMENT BY:

Tells the system how to increment the sequence. If it is positive, the values are ascending; if it is negative, the values are descending.

START WITH:

Tells the system which integer to start with.

MINVALUE / NOMINVALUE:

MINVALUE integer - Tells the system how low the sequence can go.This value can have 28 or fewer digits. MINVALUE must be equal to or less than START WITH and must be less than MAXVALUE.
NOMINVALUE - Indicates a minimum value of 1 for an ascending sequence or -1026 for for a descending sequence. This is the default.

MAXVALUE / NOMAXVALUE:

MAXVALUE integer - Specifies the maximum value the sequence can generate. This value can have 28 or fewer digits.MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE.
NOMAXVALUE - Indicates a maximum value of 1027 for an ascending sequence or -1 for for a descending sequence. This is the default.

CYCLE / NOCYCLE:

CYCLE- Causes the sequences to automatically recycle to minvalue when maxvalue is reached for ascending sequences; for descending sequences, it causes a recycle from minvalue back to maxvalue.
NOCYCLE- Causes the sequence to not automatically recycle its values when minimum or maximum thresholds are met.

CACHE / NOCACHE:

CACHE-Caches the specified number of sequence values into the buffers in the SGA. This speeds access, but all cached numbers are lost when the database is shut down. The default value is 20; maximum value is maxvalue-minvalue.
NOCACHE-Specifies not to preallocate any sequence values.
If you omit both CACHE and NOCACHE, Oracle caches 20 sequence numbers by default.

ORDER / NOORDER:

ORDER- Forces sequence numbers to be output in order of request. In cases where they are used for timestamping, this may be required.
NOORDER - Causes sequence numbers to not automatically be output in order of request.

In most cases, the sequences numbers will be in order anyway, so ORDER will not be required.ORDER is necessary only to guarantee ordered generation if you are using Oracle with the Oracle Real Application Clusters option in parallel mode. If you are using exclusive mode, Oracle sequences numbers are always generated in order.

Create Sequences Examples

CREATE SEQUENCE customers_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

CREATE SEQUENCE supplier_seq
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

ALTER SEQUENCE

Use the ALTER SEQUENCE statement to change the increment, minimum and maximum values, cached numbers, and behavior of an existing sequence. This statement affects only future sequence numbers.

Example

ALTER SEQUENCE supplier_seq
   MAXVALUE 1500;

ALTER SEQUENCE supplier_seq
   CYCLE
   CACHE 5;

DROP SEQUENCE

Use the DROP SEQUENCE statement to remove a sequence from the database.

DROP SEQUENCE supplier_seq;

Oracle INDEXES

Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.

Index Types

Oracle Database provides several indexing schemes that provide complementary performance functionality. These are:

• B-tree indexes: the default and the most common.
• B-tree cluster indexes: defined specifically for cluster.
• Hash cluster indexes: defined specifically for a hash cluster.
• Global and local indexes: relate to partitioned tables and indexes.
• Reverse key indexes: most useful for Oracle Real Application Clusters applications.
• Bitmap indexes: compact; work best for columns with a small set of values
• Function-based indexes: contain the precomputed value of a function/expression.
• Domain indexes: specific to an application or cartridge.

Indexes are logically and physically independent of the data in the associated table. Being independent structures, they require storage space. You can create or drop an index without affecting the base tables, database applications, or other indexes. The database automatically maintains indexes when you insert, update, and delete rows of the associated table. If you drop an index, all applications continue to work. However, access to previously indexed data might be slower.

Guidelines for Managing Indexes

Here are some guidelines from creating efficient indexes:

• Index if you need to access no more than 10-15% of the data in the table. A full table scan (read all the table) is better if you intend to retrieve a high percentage of the table data, this is because a index read requires two reads
• Do not use indexes on small tables, a full table scan would be fine.
• Create primary keys for all tables as a index will be created by default.
• Index the columns that are involved in multi-table join operations
• Index columns that are used frequently in where clauses.
• Index columns that are involved in order by, group by, union and distinct operations.
• Columns that have long character strings are bad for indexing.
• Columns that are frequently update are bad for indexing.
• Choose tables where few rows have similar values.
• Keep the number of indexes small, to many will cause performance problems on inserting data.

Creating Indexes

This section describes how to create indexes. To create an index in your own schema, at least one of the following conditions must be true:

1. The table or cluster to be indexed is in your own schema.
2. You have INDEX privilege on the table to be indexed.
3. You have CREATE ANY INDEX system privilege.

Creating an Index Explicitly

You can create indexes explicitly (outside of integrity constraints) using the SQL statement CREATE INDEX. The following statement creates an index named emp_ename for the ename column of the emp table:

CREATE INDEX emp_ename ON emp(ename)
      TABLESPACE users
      STORAGE (INITIAL 20K
      NEXT 20k
      PCTINCREASE 75);

Creating a Unique Index Explicitly

Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Non-unique indexes do not impose this restriction on the column values.

Use the CREATE UNIQUE INDEX statement to create a unique index. The following example creates a unique index:

CREATE UNIQUE INDEX dept_unique_index ON dept (dname)
      TABLESPACE indx;

Creating an Index Associated with a Constraint

Oracle Database enforces a UNIQUE key or PRIMARY KEY integrity constraint on a table by creating a unique index on the unique key or primary key. This index is automatically created by the database when the constraint is enabled. No action is required by you when you issue the CREATE TABLE or ALTER TABLE statement to create the index, but you can optionally specify a USING INDEX clause to exercise control over its creation. This includes both when a constraint is defined and enabled, and when a defined but disabled constraint is enabled.

To enable a UNIQUE or PRIMARY KEY constraint, thus creating an associated index, the owner of the table must have a quota for the tablespace intended to contain the index, or the UNLIMITED TABLESPACE system privilege. The index associated with a constraint always takes the name of the constraint, unless you optionally specify otherwise.

Specifying Storage Options for an Index Associated with a Constraint

You can set the storage options for the indexes associated with UNIQUE and PRIMARY KEY constraints using the USING INDEX clause. The following CREATE TABLE statement enables a PRIMARY KEY constraint and specifies the storage options of the associated index:

   CREATE TABLE emp (
     empno NUMBER(5) PRIMARY KEY, age INTEGER)
     ENABLE PRIMARY KEY USING INDEX
     TABLESPACE users;

Collecting Incidental Statistics when Creating an Index

Oracle Database provides you with the opportunity to collect statistics at very little resource cost during the creation or rebuilding of an index. These statistics are stored in the data dictionary for ongoing use by the optimizer in choosing a plan for the execution of SQL statements. The following statement computes index, table, and column statistics while building index emp_ename on column ename of table emp:

CREATE INDEX emp_ename ON emp(ename)
     COMPUTE STATISTICS;

Creating an Index Online

You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index build is taking place, but DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an index online.

The following statements illustrate online index build operations:

CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;

Creating a Function-Based Index

Function-based indexes facilitate queries that qualify a value returned by a function or expression. The value of the function or expression is precomputed and stored in the index.

To illustrate a function-based index, consider the following statement that defines a function-based index (area_index) defined on the function area(geo):

CREATE INDEX area_index ON rivers (area(geo));

In the following SQL statement, when area(geo) is referenced in the WHERE clause, the optimizer considers using the index area_index.

SELECT id, geo, area(geo), desc
     FROM rivers     
     WHERE Area(geo) >5000;

Table owners should have EXECUTE privileges on the functions used in function-based indexes.

Creating a Key-Compressed Index

Creating an index using key compression enables you to eliminate repeated occurrences of key column prefix values.

CREATE INDEX  emp_ename ON emp(ename)
   TABLESPACE users
   COMPRESS 1;

The COMPRESS clause can also be specified during rebuild. For example, during rebuild you can disable compression as follows:

ALTER INDEX emp_ename REBUILD NOCOMPRESS;

Altering Indexes

To alter an index, your schema must contain the index or you must have the ALTER ANY INDEX system privilege.

Altering Storage Characteristics of an Index

ALTER INDEX emp_ename
     STORAGE (PCTINCREASE 50);

ALTER TABLE emp
     ENABLE PRIMARY KEY USING INDEX;

Rebuilding an Existing Index

ALTER INDEX emp_name REBUILD;

Dropping Indexes

To drop an index, the index must be contained in your schema, or you must have the DROP ANY INDEX system privilege.

DROP INDEX emp_ename;

Orace CREATE PACKAGE

Use the CREATE PACKAGE statement to create the specification for a stored package, which is an encapsulated collection of related procedures, functions, and other program objects stored together in the database. The package specification declares these objects. The package body, specified subsequently, defines these objects.

Prerequisites

Before a package can be created, the user SYS must run a SQL script commonly called DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.

To create a package in your own schema, you must have the CREATE PROCEDURE system privilege. To create a package in another user's schema, you must have the CREATE ANY PROCEDURE system privilege.

To embed a CREATE PACKAGE statement inside an Oracle Database precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.

OR REPLACE

Specify OR REPLACE to re-create the package specification if it already exists. Use this clause to change the specification of an existing package without dropping, re-creating, and regranting object privileges previously granted on the package. If you change a package specification, then Oracle Database recompiles it.

Users who had previously been granted privileges on a redefined package can still access the package without being regranted the privileges.

If any function-based indexes depend on the package, then the database marks the indexes DISABLED.

Package Speccification

Specify the package specification, which can contain type definitions, cursor declarations, variable declarations, constant declarations, exception declarations, PL/SQL subprogram specifications, and call specifications, which are declarations of a C or Java routine expressed in PL/SQL.

Examples:

The following SQL statement creates the specification of the emp_mgmt package.

CREATE OR REPLACE PACKAGE emp_mgmt AS 
   FUNCTION hire (last_name VARCHAR2, job_id VARCHAR2, 
      manager_id NUMBER, salary NUMBER, 
      commission_pct NUMBER, department_id NUMBER) 
      RETURN NUMBER; 
   FUNCTION create_dept(department_id NUMBER, location_id NUMBER) 
      RETURN NUMBER; 
   PROCEDURE remove_emp(employee_id NUMBER); 
   PROCEDURE remove_dept(department_id NUMBER); 
   PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER); 
   PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER); 
   no_comm EXCEPTION; 
   no_sal EXCEPTION; 
END emp_mgmt; 
/ 

The specification for the emp_mgmt package declares the following public program objects:

• The functions hire and create_dept.
• The procedures remove_emp, remove_dept, increase_sal, and increase_comm.
• The exceptions no_comm and no_sal.

Before you can call this package's procedures and functions, you must define these procedures and functions in the package body. For an example of a CREATE PACKAGE BODY statement that creates the body of the emp_mgmt package.

CREATE PACKAGE BODY

Use the CREATE PACKAGE BODY statement to create the body of a stored package, which is an encapsulated collection of related procedures, stored functions, and other program objects stored together in the database. The package body defines these objects. The package specification, defined in an earlier CREATE PACKAGE statement, declares these objects.

Example:

CREATE OR REPLACE PACKAGE BODY emp_mgmt AS 
   tot_emps NUMBER; 
   tot_depts NUMBER; 
FUNCTION hire 
   (last_name VARCHAR2, job_id VARCHAR2, 
    manager_id NUMBER, salary NUMBER, 
    commission_pct NUMBER, department_id NUMBER) 
   RETURN NUMBER IS new_empno NUMBER; 
BEGIN 
   SELECT employees_seq.NEXTVAL 
      INTO new_empno 
      FROM DUAL; 
   INSERT INTO employees 
      VALUES (new_empno, 'First', 'Last','first.last@oracle.com', 
              '(123)123-1234','18-JUN-02','IT_PROG',90000000,00, 
              100,110); 
      tot_emps := tot_emps + 1; 
   RETURN(new_empno); 
END; 
FUNCTION create_dept(department_id NUMBER, location_id NUMBER) 
   RETURN NUMBER IS 
      new_deptno NUMBER; 
   BEGIN 
      SELECT departments_seq.NEXTVAL 
         INTO new_deptno 
         FROM dual; 
      INSERT INTO departments 
         VALUES (new_deptno, 'department name', 100, 1700); 
      tot_depts := tot_depts + 1; 
      RETURN(new_deptno); 
   END; 
PROCEDURE remove_emp (employee_id NUMBER) IS 
   BEGIN 
      DELETE FROM employees 
      WHERE employees.employee_id = remove_emp.employee_id; 
      tot_emps := tot_emps - 1; 
   END; 
PROCEDURE remove_dept(department_id NUMBER) IS 
   BEGIN 
      DELETE FROM departments 
      WHERE departments.department_id = remove_dept.department_id; 
      tot_depts := tot_depts - 1; 
      SELECT COUNT(*) INTO tot_emps FROM employees; 
   END; 
PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER) IS 
   curr_sal NUMBER; 
   BEGIN 
      SELECT salary INTO curr_sal FROM employees 
      WHERE employees.employee_id = increase_sal.employee_id; 
      IF curr_sal IS NULL 
         THEN RAISE no_sal; 
      ELSE 
         UPDATE employees 
         SET salary = salary + salary_incr 
         WHERE employee_id = employee_id; 
      END IF; 
   END; 
PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER) IS 
   curr_comm NUMBER; 
   BEGIN 
      SELECT commission_pct 
      INTO curr_comm 
      FROM employees 
      WHERE employees.employee_id = increase_comm.employee_id; 
      IF curr_comm IS NULL 
         THEN RAISE no_comm; 
      ELSE 
         UPDATE employees 
         SET commission_pct = commission_pct + comm_incr; 
      END IF; 
   END; 
END emp_mgmt; 
/ 

Oracle CREATE FUNCTION

Use the CREATE FUNCTION statement to create a standalone stored function or a call specification.

A stored function (also called a user function or user-defined function) is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User functions can be used as part of a SQL expression.

Prerequisites

Before a stored function can be created, the user SYS must run a SQL script that is commonly called DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.

To create a function in your own schema, you must have the CREATE PROCEDURE system privilege. To create a function in another user's schema, you must have the CREATE ANY PROCEDURE system privilege. To replace a function in another user's schema, you must have the ALTER ANY PROCEDURE system privilege.

To invoke a call specification, you may need additional privileges, for example, EXECUTE privileges on a C library for a C call specification.

To embed a CREATE FUNCTION statement inside an Oracle precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.

OR REPLACE

Specify "OR REPLACE" to re-create the function if it already exists. Use this clause to change the definition of an existing function without dropping, re-creating, and regranting object privileges previously granted on the function. If you redefine a function, then Oracle Database recompiles it.

Users who had previously been granted privileges on a redefined function can still access the function without being regranted the privileges.

If any function-based indexes depend on the function, then Oracle Database marks the indexes DISABLED.

Schema

Specify the schema to contain the function. If you omit schema, Oracle Database creates the function in your current schema.

PARAMETERS

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared

1. IN - Specify IN to indicate that you must supply a value for the argument when calling the procedure.
2. OUT - Specify OUT to indicate that the procedure passes a value for this argument back to its calling environment after execution.
3. IN OUT - Specify IN OUT to indicate that you must supply a value for the argument when calling the procedure and that the procedure passes a value back to its calling environment after execution.

If you do not specify IN, OUT, and IN OUT, then the argument defaults to IN.

RETURN Clause

For datatype, specify the datatype of the return value of the function. Because every function must return a value, this clause is required. The return value can have any datatype supported by PL/SQL.

Examples:

The Syntax for a Function is:

CREATE [OR REPLACE] FUNCTION function_name
   [ (parameter [,parameter]) ]

   RETURN return_datatype

IS | AS

   [declaration_section]

BEGIN
   executable_section

[EXCEPTION
   exception_section]

END [function_name];

The following is a simple example of a function:

CREATE OR REPLACE Function FindCourse
   ( 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;

This function is called FindCourse. It has one parameter called name_in and it returns a number. The function will return the course number if it finds a match based on course name. Otherwise, it returns a 99999.

You could then reference your new function in an SQL statement as follows:

select course_name, FindCourse(course_name) as course_id
from courses
where subject = 'Mathematics';

Orace PROCEDURE

Use the CREATE PROCEDURE statement to create a standalone stored procedure or a call specification.

A procedure is a group of PL/SQL statements that you can call by name. A call specification (sometimes called call spec) declares a Java method or a third-generation language (3GL) routine so that it can be called from SQL and PL/SQL. The call spec tells Oracle Database which Java method to invoke when a call is made. It also tells the database what type conversions to make for the arguments and return value.

Stored procedures offer advantages in the areas of development, integrity, security, performance, and memory allocation.

Prerequisites

Before creating a procedure, the user SYS must run a SQL script commonly called DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.

To create a procedure in your own schema, you must have the CREATE PROCEDURE system privilege. To create a procedure in another user's schema, you must have the CREATE ANY PROCEDURE system privilege. To replace a procedure in another schema, you must have the ALTER ANY PROCEDURE system privilege.

To invoke a call spec, you may need additional privileges, for example, the EXECUTE object privilege on the C library for a C call spec.

To embed a CREATE PROCEDURE statement inside an Oracle precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.

OR REPLACE

Specify "OR REPLACE" to re-create the procedure if it already exists. Use this clause to change the definition of an existing procedure without dropping, re-creating, and regranting object privileges previously granted on it. If you redefine a procedure, then Oracle Database recompiles it.

Users who had previously been granted privileges on a redefined procedure can still access the procedure without being regranted the privileges.

PARAMETERS

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared

1. IN - Specify IN to indicate that you must supply a value for the argument when calling the procedure.
2. OUT - Specify OUT to indicate that the procedure passes a value for this argument back to its calling environment after execution.
3. IN OUT - Specify IN OUT to indicate that you must supply a value for the argument when calling the procedure and that the procedure passes a value back to its calling environment after execution.

If you do not specify IN, OUT, and IN OUT, then the argument defaults to IN.

Examples:

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
   tot_emps NUMBER;
   BEGIN
      DELETE FROM employees
      WHERE employees.employee_id = remove_emp.employee_id;
   tot_emps := tot_emps - 1;
   END;
/

CREATE OR REPLACE Procedure UpdateCourse
   ( name_in IN varchar2 )
   
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;
   
   insert into student_courses
   ( course_name,
     course_number )
   values ( name_in,
            cnumber );

   commit;

   close c1;

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Oracle Constraints

Oracle constraints are critical to the scalability, flexibility and integrity of your database data. Constraints apply specific rules to data, ensuring the data conforms to the requirements defined. There are a number of different kinds of constraints that you will be concerned with as a DBA.

There are a following types of Constraints

• NOT NULL: A column with this constraint will not allow NULL values.
• PRIMARY KEY: There can be only one primary key column in a table. This will only UNIQUE values. Does not allow NULL values.
• UNIQUE KEY: We can have any number of primary keys in a table. UNIQUE Constraint also would accept only UNIQUE values.
• FOREIGN KEY: A foreign key is a combination of columns with values based primary. It is also known as referential integrity constraint. Values that a foreign key can take are the values that are present in primary key.
• CHECK: This constraint defines a condition which need to be satisfied by the value entering into the table.

NOT NULL Constraints

NOT NULL constraints are in-line constraints that indicate that a column can not contain NULL values. The previous example of the creation of the MY_STATUS table contained two examples of NOT NULL constraints being defined. For example, the PERSON_ID column is defined as NOT NULL in that example.

If you need to add a NOT NULL constraint to a table after the fact, simply use the alter table command as in this

Example:

ALTER TABLE supplier MODIFY (supplier_id NOT NULL);

Primary Key Constraints

Primary key constraints define a column or series of columns that uniquely identify a given row in a table. Defining a primary key on a table is optional and you can only define a single primary key on a table. A primary key constraint can consist of one or many columns (up to 32). Any column that is defined as a primary key column is automatically set with a NOT NULL status.

Example:

CREATE TABLE supplier
(
  supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

If table is already created then you can use ALTER TABLE Command to add Constraint.

ALTER TABLE supplier ADD CONSTRAINT supplier_pk 
PRIMARY KEY (supplier_id);

Unique Constraints

Unique constraints are like alternative primary key constraints. A unique constraint defines a column, or series of columns, that must be unique in value. You can have a number of unique constraints defined and the columns can have NULL values in them, unlike a column that belongs to a primary key constraint. If you need to add unique key constraints to a table after the fact, simply use the alter table command.

Example:

ALTER TABLE supplier ADD CONSTRAINT  uk_supplier_name
UNIQUE (supplier_name);

Foreign Key Constraints

A Foreign Key Constraint is used to enforce a relationship between two tables.

The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.

A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Example:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
);

ALTER TABLE products add CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id)  REFERENCES supplier(supplier_id);

Check Constraints

Oracle check constraint insures that updated or inserted values meet a specific condition. The Oracle check constraint check condition must return a TRUE or FALSE, much Like the WHERE clause. If the Oracle check constraint condition returns as TRUE when you use Oracle check constraint, the value is accepted by the constraint. If Oracle check constraint returns the condition as FALSE, the value is rejected. Below, we include an Oracle check constraint on the supplier_id column of the suppliers table that insures the value is BETWEEN 100 and 9999.

A check constraint can be defined in either an CREATE TABLE statement or an ALTER TABLE statement.

Example:

CREATE TABLE suppliers
(
  supplier_id numeric(4),
  supplier_name varchar2(50),
  CONSTRAINT check_supplier_id
  CHECK (supplier_id BETWEEN 100 and 9999)
);

By using ALTER TABLE statement.

ALTER TABLE suppliers add CONSTRAINT check_supplier_name
  CHECK (supplier_name IN ('IBM', 'Microsoft', 'NVIDIA'));

Drop a Check Constraints

ALTER TABLE suppliers
drop CONSTRAINT check_supplier_id;

Enable/Disable a Check Constraints

ALTER TABLE suppliers
enable CONSTRAINT check_supplier_id;

ALTER TABLE suppliers
disable CONSTRAINT check_supplier_id;

Oracle Database Triggers

What's a trigger? In the Oracle world, a trigger is an important system-level component that allows you to associate a "block of code" (usually PL/SQL or Java) with a specific system event, namely DML (SQL insert, update and delete) statements.

Oracle defines triggers as "procedures that are stored in the database and implicitly run, or fired, when something happens.What is the "something" that happens to make a trigger fire? There are 12 envents that can cause triggers to fire: Before/After during Insert/Update/Delete on a Row/Table.

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.

Trigger Type

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

Syntax

CREATE or REPLACE TRIGGER trigger_name
BEFORE INSERT OR UPDATE OR DELETE
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   -- variable declarations

BEGIN
   -- trigger code

EXCEPTION
   WHEN ...
   -- exception handling

END;

Examples

1.
CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT OR UPDATE OR DELETE
   ON orders
   FOR EACH ROW
   
DECLARE
   v_username varchar2(10);
   
BEGIN

   -- Find username of person performing INSERT into table
   SELECT user INTO v_username
   FROM dual;
   
   -- Update create_date field to current system date
   :new.create_date := sysdate;
   
   -- Update created_by field to the username of the person performing the INSERT
   :new.created_by := v_username;
   
END;

2.
CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE OR UPDATE OR DELETE
   ON orders
   FOR EACH ROW
   
DECLARE
   v_username varchar2(10);
   
BEGIN

   -- Find username of person performing UPDATE into table
   SELECT user INTO v_username
   FROM dual;
   
   -- Insert record into audit table
   INSERT INTO orders_audit
   ( order_id,
     quantity_before,
     quantity_after,
     username )
   VALUES
   ( :new.order_id,
     :old.quantity,
     :new.quantity,
     v_username );
     
END;

Drop a Triggers

If you had a trigger called orders_before_insert, you could drop it with the following command.

DROP TRIGGER orders_before_insert;

Disable/Enable Triggers

If you had a trigger called orders_before_insert, you could Disable/Enable it with the following commands.

ALTER TRIGGER orders_before_insert DISABLE;

ALTER TRIGGER orders_before_insert ENABLE;

If you had a table called Orders and you wanted to Disable/Enable all triggers on this table, you could execute the following commands.

ALTER TABLE orders DISABLE ALL TRIGGERS;

ALTER TABLE orders ENABLE ALL TRIGGERS;

Oracle Cursor

For every SQL statement execution certain area in memory is allocated. PL/SQL allow you to name this area. This private SQL area is called context area or cursor. A cursor acts as a handle or pointer into the context area. A PL/SQL program controls the context area using the cursor. Cursor represents a structure in memory and is different from cursor variable.

When you declare a cursor, you get a pointer variable, which does not point any thing. When the cursor is opened, memory is allocated and the cursor structure is created. The cursor variable now points the cursor. When the cursor is closed the memory allocated for the cursor is released.

Cursors allow the programmer to retrieve data from a table and perform actions on that data one row at a time.

There are two types of cursors implicit cursors and explicit cursors.

Implicit cursors:

For SQL queries returning single row PL/SQL declares implicit cursors. Implicit cursors are simple SELECT statements and are written in the BEGIN block (executable section) of the PL/SQL. Implicit cursors are easy to code, and they retrieve exactly one row. PL/SQL implicitly declares cursors for all DML statements. The most commonly raised exceptions here are NO_DATA_FOUND or TOO_MANY_ROWS.

Syntax:

SELECT ename, sal INTO ena, esa FROM EMP WHERE EMPNO = 7844;

Note: Ename and sal are columns of the table EMP and ena and esa are the variables used to store ename and sal fetched by the query.

Explicit Cursors:

Explicit cursors are used in queries that return multiple rows. The set of rows fetched by a query is called active set. The size of the active set meets the search criteria in the select statement. Explicit cursor is declared in the DECLARE section of PL/SQL program.

Syntax:

CURSOR cursor_name IS select statement

Sample Code:

DECLARE 
CURSOR emp_cur IS SELECT ename FROM EMP;
BEGIN
     ------- 
     -------
END;

A cursor is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement.

You can manipulate cursors using the OPEN, FETCH, and CLOSE statements.

When you need to get information about the current status of your cursor, or the result of the last fetch from a cursor, you will access cursor attributes.

PL/SQL offers a total of six attributes:

1. %FOUND- Returns TRUE if record was fetched successfully; FALSE otherwise.
2. %NOTFOUND- Returns TRUE is record was not fetched successfully; FALSE otherwise.
   
3. %ROWCOUNT - Returns number of records fetched from cursor at that point in time.
4. %ISOPEN- Returns TRUE is cursor is open; FALSE otherwise.
5. %BULK_ROWCOUNT - Returns the number of records modified by the FORALL statement for each collection element.
6. %BULK_EXCEPTIONS- Returns exception information for rows modified by the FORALL statement for each collection element.

Examples

1. An explicit cursor that selects data.

SQL> declare
  2    cursor emps
  3    is select *
  4         from employees
  5        where rownum < 6
  6        order by 1;
  7
  8    emp employees%rowtype;
  9    row number := 1;
 10  begin
 11    open emps;
 12    fetch emps into emp;
 13
 14    loop
 15      if emps%FOUND then
 16        dbms_output.put_line('Looping over record '
 17                   ||row|| ' of ' || emps%ROWCOUNT);
 18        fetch emps into emp;
 19        row := row + 1;
 20      elsif emps%NOTFOUND then
 21        exit; -- EXIT statement exits the LOOP
 22      end if;
 23    end loop;
 24
 25    if emps%ISOPEN then
 26      close emps;
 27    end if;
 28  end;
 29  /
Looping over record 1 of 1
Looping over record 2 of 2
Looping over record 3 of 3
Looping over record 4 of 4
Looping over record 5 of 5

PL/SQL procedure successfully completed.

2. An explicit cursor fetch loop.

SQL> DECLARE
  2    myLecturerID    lecturer.id%TYPE;
  3    firstName    lecturer.first_name%TYPE;
  4    v_LastName     lecturer.last_name%TYPE;
  5
  6    v_Major lecturer.major%TYPE := 'Computer Science';
  7
  8    CURSOR cursorValue IS
  9      SELECT id, first_name, last_name
 10        FROM lecturer
 11        WHERE major = v_Major;
 12  BEGIN
 13    OPEN cursorValue;
 14    LOOP
 15      FETCH cursorValue INTO myLecturerID, firstName
 16    , v_LastName;
 17      EXIT WHEN cursorValue%NOTFOUND;
 18    END LOOP;
 19    CLOSE cursorValue;
 20  END;
 21  /

PL/SQL procedure successfully completed.

3. Use cursor to store the row count.

SQL> declare
  2       l_emp_count number;
  3       i number; -- We will use this as our counter
  4
  5       CURSOR cursorValue IS
  6       select count(*)
  7       from lecturer;
  8       begin
  9       OPEN cursorValue;
 10       FETCH cursorValue INTO l_emp_count;
 11
 12       FOR i IN 1 .. l_emp_count LOOP
 13       dbms_output.put_line('Employee ' || i);
 14       END LOOP;
 15
 16       CLOSE cursorValue;
 17  end;
 18  /
Employee 1
Employee 2
Employee 3
Employee 4
Employee 5
Employee 6
Employee 7
Employee 8
Employee 9
Employee 10
Employee 11
Employee 12

PL/SQL procedure successfully completed.

4. Cursor with parameter.

SQL> declare
  2    cursor cursorValue (p_deptid in number)
  3    is select *
  4         from employees
  5        where department_id = p_deptid;
  6
  7    l_emp employees%rowtype;
  8  begin
  9    open cursorValue(30);
 10    loop
 11      fetch cursorValue into l_emp;
 12      exit when cursorValue%notfound;
 13      dbms_output.put('Emp id ' || l_emp.employee_id || ' is ');
 14      dbms_output.put_line(l_emp.last_name);
 15    end loop;
 16    close cursorValue;
 17
 18    dbms_output.put_line('Getting employees for department 90');
 19    open cursorValue(90);
 20    loop
 21      fetch cursorValue into l_emp;
 22      exit when cursorValue%notfound;
 23      dbms_output.put('Emp id ' || l_emp.employee_id || ' is ');
 24      dbms_output.put_line(l_emp.last_name);
 25    end loop;
 26    close cursorValue;
 27  end;
 28  /
Getting employees for department 90

PL/SQL procedure successfully completed.

Oracle Views

A view is simply the representation of a SQL statement that is stored in memory so that it can easily be re-used.

Views are known as logical tables. They represent the data of one or more tables. A view derives its data from the tables on which it is based. These tables are called base tables. Views can be based on actual tables or another view also.

Whatever DML operations you performed on a view they actually affect the base table of the view. You can treat views same as any other table. You can Query, Insert, Update and delete from views, just as any other table.

Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table.

The following sections explain how to create, replace, and drop views using SQL commands.

Create View

Use the CREATE VIEW statement to define a view, which is a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables.

OR REPLACE

Specify "OR REPLACE" to re-create the view if it already exists. You can use this clause to change the definition of an existing view without dropping, re-creating, and regranting object privileges previously granted on it.

Create or Replace a View

SQL>  create or replace view view_t as
  2      select id view_id, data view_data
  3        from t;

View created.

Create view without indiating the column name

SQL> CREATE VIEW EmployeeView AS
  2   select First_Name, Last_Name from Employee;

View created.

Create OR REPLACE VIEW from selected columns in a table

SQL> CREATE OR REPLACE VIEW myView (First_Name, Last_Name) AS
  2      select First_Name,
  3             Last_Name
  4      from Employee
  5      where Salary > 2000;

View created.

Create Force View

SQL>  create view invalid_view as
  2      select * from table_that_does_not_exist;
    select * from table_that_does_not_exist
                  *
ERROR at line 2:
ORA-00942: table or view does not exist

SQL>  create force view invalid_view as
  2      select * from table_that_does_not_exist;

Warning: View created with compilation errors.

Create Materialized View

SQL>  create materialized view my_orders_mv
  2      build immediate
  3    refresh on commit
  4     enable query rewrite
  5    as
  6      select employee_id,
  7             count(*) total_orders
  8        from employees
  9       group by employee_id
 10    /
      from employees
           *
ERROR at line 8:
ORA-00439: feature not enabled: Materialized view rewrite

SQL>  set timing on
SQL>  select * from my_orders_mv;
 select * from my_orders_mv
               *
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.07
SQL>  set timing off

6. Create a view with check option

SQL>  create or replace view department_10 as
  2      select *
  3        from employees
  4       where department_id = 10
  5        with check option;

View created.

Oracle Alter View

Use the ALTER VIEW statement to explicitly recompile a view that is invalid or to modify view constraints. Explicit recompilation lets you locate recompilation errors before run time. You may want to recompile a view explicitly after altering one of its base tables to ensure that the alteration does not affect the view or other objects that depend on it.

You can also use ALTER VIEW to define, modify, or drop view constraints.

This statement does not change the definition of an existing view. To redefine a view, you must use CREATE VIEW with the OR REPLACE keywords.

When you issue an ALTER VIEW statement, Oracle Database recompiles the view regardless of whether it is valid or invalid. The database also invalidates any local objects that depend on the view.

If you alter a view that is referenced by one or more materialized views, then those materialized views are invalidated. Invalid materialized views cannot be used by query rewrite and cannot be refreshed.

Alter view to recompile

SQL>  alter view view_t compile;

View altered.

The COMPILE keyword directs Oracle Database to recompile the view.

Dropping Views

Use the SQL command DROP VIEW to drop a view. For example:

DROP VIEW View_Name;

Oracle SYNONYM

Synonyms are a very powerful feature of Oracle and other SQL-compliant relational database systems. They are used as a database shorthand. They make it possible to shorten the specification of long or complex object names. This is especially useful for shared tables or views. In addition, the use of DATABASE LINKS in synonyms allows transparent access to other databases on other nodes or even other entire systems halfway around the globe.

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

Create and drop SYNONYM

SQL> CREATE SYNONYM ShorterNameForEmployee FOR Employee;

Synonym created.

SQL> select * from ShorterNameForEmployee;

ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY      
---- ---------- ---------- --------- --------- ---------- ----------
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto   
02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver 
03   James      Smith      12-DEC-78 15-MAR-90    6544.78 Vancouver 
04   Celia      Rice       24-OCT-82 21-APR-99    2344.78 Vancouver 
05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver 
06   Linda      Green      30-JUL-87 04-JAN-96    4322.78 New York  
07   David      Larry      31-DEC-90 12-FEB-98    7897.78 New York  
08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver 

8 rows selected.

SQL> DROP SYNONYM ShorterNameForEmployee;

Synonym dropped.

Transaction Rollback Commit

All changes of data in an Oracle database can only be done within a transaction. A transaction must either be committed or rolled back.

Data changed within a transaction is not visible to another session until it is commited.

COMMIT :Make changes done in transaction permanent.
ROLLBACK :Rollbacks the state of database to the last commit point.

Transaction Rollback and commit

SQL> create table t ( x number(1) );

Table created.

SQL> insert into t values ( 1 );

1 row created.

SQL> insert into t values ( 2 );

1 row created.

SQL>
SQL> rollback;

Rollback complete.

SQL> select * from t;

no rows selected

SQL> insert into t values ( 1 );

1 row created.

SQL> insert into t values ( 2 );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

         X
----------
         1
         2

Oracle TRUNCATE TABLE

A statement like delete from tablename deletes all records in the table, but it does not free any space (see On table sizes).
In order to free the space as well, use truncate. However, a truncate can not be rolled back.

Basically, a truncate statement resets the high water mark to its initial position.

A truncate statement cannot be used on a synonym.

TRUNCATE TABLE Demo

SQL> TRUNCATE TABLE purchase;

Table truncated.

SQL>
SQL> SELECT * FROM purchase;

no rows selected

Temporary Tables

Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. In Oracle 8i, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

1. Create global temporary table

SQL> CREATE GLOBAL TEMPORARY TABLE supplier
  2  (  supplier_id     numeric(10)     not null,
  3     supplier_name   varchar2(50)    not null,
  4     contact_name    varchar2(50)
  5  )
  6  /

2. Create global temporary table from existing table

SQL> create global temporary table employee_tab
  2        on commit preserve rows
  3        as select * from employee;

Table created.

3. Create global temporary table with 'on commit delete rows' option

SQL> create global temporary table transaction_tab
  2        on commit delete rows
  3        as select *
  4             from employee;

Table created.

4. Temporary tables cannot be forced into logging.

SQL> ALTER TABLE temp_emp LOGGING;
ALTER TABLE temp_emp LOGGING
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table

5. Temporary tables support primary keys.

SQL> CREATE GLOBAL TEMPORARY TABLE temp_emp
  2    AS SELECT * FROM employee;

Table created.

SQL>
SQL> ALTER TABLE temp_emp
  2    ADD CONSTRAINT pk_temp_emp
  3    PRIMARY KEY (emp_no);

Table altered.

6. Temporary tables do not support foreign keys

SQL> CREATE GLOBAL TEMPORARY TABLE temp_emp
  2    AS SELECT * FROM employee;

Table created.

SQL>
SQL> ALTER TABLE temp_emp
  2    ADD CONSTRAINT fk_temp_emp_dept
  3    FOREIGN KEY (dept_no)
  4    REFERENCES department;
ALTER TABLE temp_emp
*
ERROR at line 1:
ORA-14455: attempt to create referential integrity constraint on temporary table

7. You cannot alter a temporary table to change its data duration.
(drop and recreate)

SQL> CREATE GLOBAL TEMPORARY TABLE temp_emp
  2    AS SELECT * FROM employee;

Table created.

SQL>
SQL> ALTER TABLE temp_emp ON COMMIT PRESERVE ROWS;
ALTER TABLE temp_emp ON COMMIT PRESERVE ROWS
                     *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

8. Update a TEMPORARY TABLE and check the table it based on

SQL> UPDATE temp_emp SET salary = 99
  2  WHERE title = 'Designer';

3 rows updated.

SQL>
SQL> select * from employee where title = 'Designer'
  2
SQL> SELECT salary FROM temp_emp
  2  WHERE title = 'Designer';
    SALARY
----------
        99
        99
        99

3 rows selected.

9. Truncate and Drop global temporary table

SQL> create global temporary table session_tab
  2  on commit preserve rows
  3  as select * from employees;

SQL> truncate table session_tab;

Table truncated.

SQL> drop table session_tab;

Table dropped.

ALTER TABLE Statement

The SQL ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table. You can add or remove Pirmary Key, Foreign Key by using ALTER TABLE Comman.

1. Alter table to add Columns in Tables

SQL> alter table people
  2  add (
  3    phone_number    varchar2(10)
  4  );

Table altered.

2. Add two columns to a table

SQL> alter table people
  2  add(
  3    t_name varchar2(40),
  4    st_name varchar2(15)
  5  );

Table altered.

3. Use alter table command to add foreign key constraint

SQL> ALTER TABLE products
  2  add CONSTRAINT fk_supplier
  3    FOREIGN KEY (supplier_id, supplier_name)
  4    REFERENCES supplier(supplier_id, supplier_name);

Table altered.

4. Use alter table to add foreign key with cascade delete

SQL> ALTER TABLE products
  2  add CONSTRAINT fk_supplier
  3    FOREIGN KEY (supplier_id)
  4    REFERENCES supplier(supplier_id)
  5    ON DELETE CASCADE;

Table altered.

5. Alter table to add a foreign key ON DELETE SET NULL

SQL> ALTER TABLE products
  2  add CONSTRAINT fk_supplier
  3    FOREIGN KEY (supplier_id)
  4    REFERENCES supplier(supplier_id)
  5    ON DELETE SET NULL;

Table altered.

6. Alter table to drop unused columns

select * from user_unused_col_tabs;

ALTER TABLE people DROP UNUSED COLUMNS;

7. Alter table to drop two columns in a single command

alter table people drop (first_name, last_name);

8. Marking Columns Unused

alter table people
set unused (first_name, last_name);

9. Alter table to change the storage

alter table t storage ( FREELISTS 2 );

Oracle TABLE

Tables are the basic unit of data storage in an Oracle Database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.

You can specify rules for each column of a table. These rules are called integrity constraints. One example is a NOT NULL integrity constraint. This constraint forces the column to contain a value in every row.

You can also invoke transparent data encryption to encrypt data before storing it in the datafile. Then, if users attempt to circumvent the database access control mechanisms by looking inside datafiles directly with operating system tools, encryption prevents these users from viewing sensitive data.

After you create a table, insert rows of data using SQL statements. Table data can then be queried, deleted, or updated using SQL.

Creating Tables

Before tables can be used, they have to be created and this is generally the job of the DBA. Creating tables is done with the create table command. The create table command does the following:

1. Defines the table name.
2. Defines the columns in the table and the datatypes of those columns.
3. Defines what tablespace the table resides in (optional).
4. Defines other characteristics of the table (optional).

1. Create table with data type: VARCHAR2, Date, Number(8,2)

SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(20 BYTE),
  4    Last_Name          VARCHAR2(20 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(20 BYTE),
  9    Description        VARCHAR2(80 BYTE)
 10  )
 11  /

Table created.

2. Creating Table with combined primary key

SQL> create table employee_history
  2  (employee_id       number(6) not null,
  3   salary            number(8,2),
  4   hire_date         date default sysdate,
  5   termination_date  date,
  6   termination_desc varchar2(4000),
  7   constraint emphistory_pk
  8    primary key (employee_id, hire_date)
  9  );

Table created.

3. Create table with foreign key

SQL>     CREATE TABLE products
  2      (      product_id      numeric(10)     not null,
  3             supplier_id     numeric(10)     not null,
  4             CONSTRAINT fk_supplier
  5               FOREIGN KEY (supplier_id)
  6               REFERENCES supplier(supplier_id)
  7      );

Table created.

4. Creating Table and indicate tablespace

SQL> create table subjects (
  2  subject_id    number not null,
  3  subject_name  varchar2(30) not null,
  4  description   varchar2(4000)
  5  )
  6  tablespace users;

Table created.

Hierarchical Query

Using hierarchical queries, you can retrieve records from a table by their natural relationship. Be it a family tree or a employee/manager tree or what ever.

Tree walking enables you to construct a hierarchical tree if the relationship lie in the same table. For instance, a manager column which exists in the emp table which defines the managerial hierarchy.

We shall take up an example of the emp table in Scott schema. Here King is top most in the hierarchy.

empno ename job    mgr   hiredate
7369 SMITH CLERK    7902   17-Dec-80
7499 ALLEN SALESMAN   7698   20-Feb-81
7521 WARD SALESMAN   7698   22-Feb-81
7566 JONES MANAGER    7839   2-Apr-81
7654 MARTIN SALESMAN   7698   28-Sep-81
7698 BLAKE MANAGER    7839   1-May-81
7782 CLARK MANAGER    7839   9-Jun-81
7788 SCOTT ANALYST    7566   19-Apr-87
7839 KING PRESIDENT         17-Nov-81
7844 TURNER SALESMAN   7698   8-Sep-81
7876 ADAMS CLERK    7788   23-May-87
7900 JAMES CLERK    7698   3-Dec-81
7902 FORD ANALYST    7566   3-Dec-81
7934 MILLER CLERK    7782   23-Jan-82

If we have to query the employees reporting to King directly

SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate 
FROM   emp 
WHERE  mgr = 7839

7566 JONES MANAGER 7839 2-Apr-81
7698 BLAKE MANAGER 7839 1-May-81
7782 CLARK MANAGER 7839 9-Jun-81

But if we have to walk down the tree and check who all are reporting to Jones, Blake and Clark (recursively).

SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate 
FROM   emp 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr;

Level

LEVEL psedo column shows the level or rank of the particular row in the hierarchical tree. If you see the below query, It shows the level of KING and the level of the guys reporting directly to him.

SELECT empno, 
       ename, 
       job, 
       mgr, 
       hiredate, 
       LEVEL 
FROM   emp 
WHERE  LEVEL <= 2 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr;

empno ename job mgr hiredate level
7839 KING PRESIDENT 17-Nov-81 1
7566 JONES MANAGER 7839 2-Apr-81 2
7698 BLAKE MANAGER 7839 1-May-81 2
7782 CLARK MANAGER 7839 9-Jun-81 2

Here The level is used in the where clause to restrict the records till the second level.
Level also can be used to format the Output to form a graph structure.

SELECT Lpad(ename,Length(ename) + LEVEL * 10 - 10,'-') 
FROM   emp 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr;

KING
----------JONES
--------------------SCOTT
------------------------------ADAMS
--------------------FORD
------------------------------SMITH
----------BLAKE
--------------------ALLEN
--------------------WARD
--------------------MARTIN
--------------------TURNER
--------------------JAMES
----------CLARK
--------------------MILLER

Correlated Subquery

Correlated subquery references the outer query in the subqueries WHERE clause. Internally, correlated subqueries are very expensive to process because the inner query must be executed for every row returned by the outer query.

A correlated subquery references one or more columns in the outer query.

The following subquery is known as a correlated subquery because the subquery is related to the outer query.

SQL> SELECT empno, mgr,ename, sal
  2  FROM emp outer
  3  WHERE sal >
  4    (SELECT AVG(sal)
  5     FROM emp inner
  6     WHERE inner.empno = outer.mgr);

     EMPNO        MGR ENAME             SAL
---------- ---------- ---------- ----------
      7788       7566 SCOTT            3000
      7902       7566 FORD             3000

SQL>

Using EXISTS with a Correlated Subquery

SQL> --Using EXISTS with a Correlated Subquery
SQL>
SQL> SELECT empno, ename
  2  FROM emp outer
  3  WHERE EXISTS
  4    (SELECT empno
  5     FROM emp inner
  6     WHERE inner.mgr = outer.empno);

     EMPNO ENAME
---------- ----------
      7902 FORD
      7698 BLAKE
      7839 KING
      7566 JONES
      7788 SCOTT
      7782 CLARK

6 rows selected.

SQL>

Using NOT EXISTS with a Correlated Subquery

SQL> SELECT empno, ename
  2  FROM emp outer
  3  WHERE NOT EXISTS
  4    (SELECT 1
  5     FROM emp inner
  6     WHERE inner.mgr = outer.empno);

     EMPNO ENAME
---------- ----------
      7844 TURNER
      7521 WARD
      7654 MARTIN
      7499 ALLEN
      7934 MILLER
      7369 SMITH
      7876 ADAMS
      7900 JAMES

8 rows selected.

Oracle Subquery

A subquery is a query within a query. In Oracle, you can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

A subquery in the WHERE clause of a SELECT statement is also called a nested subquery. You can nest up to 255 levels of subqueries in the WHERE clause.

A subquery in the FROM clause of a SELECT statement is also called an inline view. Oracle Database imposes no limit on the number of subquery levels in the FROM clause of the top-level query.

A subquery can contain another subquery.

If columns in a subquery have the same name as columns in the containing statement, then you must prefix any reference to the column of the table from the containing statement with the table name or alias. To make your statements easier to read, always qualify the columns in a subquery with the name or alias of the table, view, or materialized view.

1. When a query is > ALL, each row in the result set is greater than the highest value returned.

SQL> SELECT empno, sal
  2  FROM Emp
  3  WHERE sal > ALL (SELECT sal
  4                       FROM Emp
  5                       WHERE job ='SALESMAN');

     EMPNO        SAL
---------- ----------
       7566      2975    
       7698      2850    
       7782      2450    
       7788      3000    
       7839      5000    
       7902      3000    

14 rows selected.

SQL>

2. 'ALL' with a Multiple Row Subquery.

'ALL' with a Multiple Row Subquery: must place an =, <>, <, >, <=, or >= operator before ALL

SQL> SELECT id, last_name
  2  FROM employee
  3  WHERE salary > ALL
  4    (SELECT salary
  5     FROM employee where description='Tester');

ID   LAST_NAME
---- ----------
07   Larry

3. Subquery > 'ANY' each row in the result set is greater than the lowest value returned.

SQL> SELECT *
  2  FROM Emp
  3  WHERE sal > ANY (SELECT sal
  4                       FROM Emp
  5                       WHERE job ='SALESMAN');

   EMPNO ENAME      JOB              MGR HIREDATE         SAL 
-------- ---------- --------- ---------- --------- ---------- 
    7839 KING       PRESIDENT            17-NOV-81       5000 
    7902 FORD       ANALYST         7566 03-DEC-81       3000 
    7788 SCOTT      ANALYST         7566 09-DEC-82       3000 
    7566 JONES      MANAGER         7839 02-APR-81       2975 
    7698 BLAKE      MANAGER         7839 01-MAY-81       2850 
    7782 CLARK      MANAGER         7839 09-JUN-81       2450    
    7499 ALLEN      SALESMAN        7698 20-FEB-81       1600 
    7844 TURNER     SALESMAN        7698 08-SEP-81       1500 
    7934 MILLER     CLERK           7782 23-JAN-82       1300 

9 rows selected.

4. 'ANY' with a Multiple Row Subquery.

'ANY' with a Multiple Row Subquery: must place an =, <>, <, >, <=, or >= operator before ANY

SQL> SELECT id, last_name
  2  FROM employee
  3  WHERE salary < ANY
  4    (SELECT salary
  5     FROM employee where city='New York');

ID   LAST_NAME
---- ----------
08   Cat
01   Martin
05   Black
04   Rice
06   Green
03   Smith
02   Mathews

7 rows selected.

5. Use sub query as a virtual table(Inline Views)

SQL> SELECT id "Emp #", First_name "Name", salary "Salary", ROWNUM rank
  2  FROM
  3    (SELECT id, first_name, salary FROM employee ORDER BY salary);

Emp  Name           Salary       RANK
---- ---------- ---------- ----------
01   Jason         1234.56          1
03   James         2334.78          2
08   James         2334.78          3
05   Robert        2334.78          4
06   Linda         2334.78          5
07   David         2334.78          6
02   Alison        2334.78          7
04   Celia         2334.78          8

8 rows selected.

6. Compare with data from subquery and Using of Aggregate Function

SQL> SELECT eName
  2  FROM Emp
  3  WHERE sal > (SELECT min(e.sal)
  4                       FROM Emp e, dept d
  5                       WHERE d.deptno = e.deptno);

7. An example of a nested three-level subquery

SQL> SELECT eName
  2  FROM Emp
  3  WHERE sal > (SELECT min(e.sal)
  4                       FROM Emp e, dept d
  5                       WHERE d.deptno = e.deptno);

8. If an inner query returns a NULL, the outer query also returns NULL

SQL> SELECT eName
  2  FROM Emp
  3  WHERE sal > (SELECT sal
  4                       FROM Emp
  5                       WHERE empno = 0);

no rows selected

9. Working with multi-column subqueries(a pairwise comparison)

SQL> SELECT empno, ename
  2  FROM Emp
  3  WHERE (sal, deptno) = (SELECT sal, deptno FROM Emp WHERE empno =  7934);

10. Using the EXISTS and NOT EXISTS operators

SQL> SELECT empno, eName
  2  FROM Emp e
  3  WHERE EXISTS (SELECT 1
  4                FROM dept d
  5                WHERE e.deptno = d.deptno);

11. Writing Single Row Subqueries

SQL> SELECT first_name, last_name
  2  FROM employee
  3  WHERE id =
  4    (SELECT id
  5     FROM employee
  6     WHERE last_name = 'Cat');

12. Subqueries in a HAVING Clause: Uses a subquery in the HAVING clause of the outer query

SQL> SELECT city, AVG(salary)
  2  FROM employee
  3  GROUP BY city
  4  HAVING AVG(salary) <
  5    (SELECT MAX(AVG(salary))
  6     FROM employee
  7     GROUP BY city);

13. Subqueries May Not Contain an ORDER BY Clause

SQL> SELECT id, first_name, salary
  2  FROM employee
  3  WHERE salary >
  4    (SELECT AVG(salary)
  5     FROM employee)
  6  ORDER BY id DESC;

14. Sub query with table join

SQL> SELECT e.empno, ename, j.jobtitle
  2  FROM employee e,
  3    (SELECT empno, jobtitle
  4     FROM job) j
  5  WHERE e.empno = j.empno;

15. Subquery just returns a literal value: improve performance of your query

SQL> SELECT empno, ename
  2  FROM emp outer
  3  WHERE EXISTS
  4    (SELECT 1
  5     FROM emp inner
  6     WHERE inner.mgr = outer.empno);

16. EXISTS typically offers better performance than IN with subqueries

When a list of values contains a null, NOT EXISTS returns true, but NOT IN returns false.

SQL> SELECT empno, ename
  2  FROM emp outer
  3  WHERE NOT EXISTS
  4    (SELECT 1
  5     FROM emp inner
  6     WHERE inner.mgr = outer.empno);

17. Writing Nested Subqueries

SQL> SELECT city, AVG(salary)
  2  FROM employee
  3  GROUP BY city
  4  HAVING AVG(salary) <
  5    (SELECT MAX(AVG(salary)) FROM employee WHERE id IN 
  6      (SELECT id FROM employee)
  7     GROUP BY city);

18. subqueries in the SELECT column list (New Way)

SQL> SELECT e.lastname, e.salary,
  2     (SELECT avg(salary) FROM employee
  3      WHERE dept_no = e.dept_no) avg_sal_dept
  4  FROM employee e
  5  order by 1
  6  /

19. subqueries in the SELECT column list (Old way)

SQL> SELECT e.lastname, e.salary, d.avg_sal_dept
  2  FROM employee e, (SELECT dept_no, avg(salary) as avg_sal_dept
  3                    FROM employee
  4                    GROUP BY dept_no)  d
  5  WHERE e.dept_no = d.dept_no
  6  order by 1
  7  /

Oracle DELETE Command

The SQL DELETE statement allows you to delete a single record or multiple records from a table. You can rollback deleted records before commit.

DELETE By Using Where Clause

WHERE clause is used to control which rows are affected by the DELETE statement

SQL> DELETE FROM Employee
  2     WHERE EMPID = '04';

1 rows deleted.

SQL> DELETE FROM Employee
  2     WHERE salary > 3000;

5 rows deleted.

If you don't mention the WHERE condition then all rows will be deleted.

Delete by using subquery

Suppose we want to delete all employees whose salary is greater than average salary.
Then Write a DELETE Statement Containing a Subquery.

SQL> DELETE FROM employee
  2     WHERE salary >
  3        (SELECT AVG(salary)
  4         FROM employee);

4 rows deleted.

Oracle INSERT Command

In Oracle PL/SQL, an INSERT statement adds one or more records to any single table in a relational database. The INSERT statement may also be used add rows to the base table of a view, a partition of a partitioned table or a subpartition of a composite-partitioned table, or an object table or the base table of an object view.

1. Insert with subquery

You can insert data into one table from another table by using select command as a subquery.

SQL> INSERT INTO emp

  2      (empno, ename)

  3      SELECT deptno, dname

  4      FROM dept;

Insert into ... select

SQL> BEGIN
  2       INSERT INTO emp1
  3           SELECT * FROM emp;
  4       COMMIT;
  5  END;
  6  /

PL/SQL procedure successfully completed.

2. Insert With Functions You can use variables such as SYSDATE and USER in your INSERT statements
SQL> BEGIN
&nbsp&nbsp 2&nbsp&nbsp&nbsp&nbsp INSERT INTO emp (empno, hiredate)
&nbsp&nbsp 3&nbsp&nbsp&nbsp&nbsp VALUES (53, SYSDATE);
&nbsp&nbsp 4&nbsp&nbsp&nbsp&nbsp END;
&nbsp&nbsp 5&nbsp&nbsp&nbsp&nbsp /

PL/SQL procedure successfully completed.
3. Date calculation in insert statement
SQL> insert into old_log values ('Completed chapter 1', sysdate - 60 );

1 row created.
4. Use Trunc in insert statement.
SQL> INSERT INTO product VALUES ('S', 45, 1, trunc(sysdate));

1 row created.
5. Use the Insert Statement to Add records to existing Tables.
Examples:
Insert into emp values (101,'Sami','G.Manager','8-aug-1998',2000);
If you want to add a new row by supplying values for some columns not all the columns then you have to mention the name of the columns in insert statements. For example the following statement inserts row in emp table by supplying values for empno, ename, and sal columns only. The Job and Hiredate columns will be null.
Insert into emp (empno,ename,sal) values (102,’Ashi’,5000);
Now you want to add rows from old_emp table to emp table. Then you can give the following insert statement.
Insert into emp (empno, ename, sal)
&nbsp&nbsp&nbsp&nbsp select empno, ename, sal from old_emp;
Multitable Insert
Suppose we have two table with the following structure.
Sales_table
&nbsp&nbsp prodid
&nbsp&nbsp prodname
&nbsp&nbsp mon_amt
&nbsp&nbsp tue_amt
&nbsp&nbsp wed_amt
&nbsp&nbsp thu_amt
&nbsp&nbsp fri_amt
&nbsp&nbsp sat_amt

Weekly_Sales_table
&nbsp&nbsp prodid
&nbsp&nbsp prodname
&nbsp&nbsp weekday
&nbsp&nbsp amount

Now we want to add the rows from SALES_TABLE table into Weekly_Sales_Table Table in the following Structure.
To achieve the above we can give a multi table INSERT statement given below.
Insert all
&nbsp&nbsp&nbsp Into week_sales(prodid,prodname,weekday,amount)
&nbsp&nbsp&nbsp Values (prodid,prodname,’Mon’,mon_amt)
&nbsp&nbsp&nbsp Into week_sales(prodid,prodname,weekday,amount)
&nbsp&nbsp&nbsp Values (prodid,prodname,’Tue’,tue_amt)
&nbsp&nbsp&nbsp Into week_sales(prodid,prodname,weekday,amount)
&nbsp&nbsp&nbsp Values (prodid,prodname,’Wed’,wed_amt)
&nbsp&nbsp&nbsp Into week_sales(prodid,prodname,weekday,amount)
&nbsp&nbsp&nbsp Values (prodid,prodname,’Thu’,thu_amt)
&nbsp&nbsp&nbsp Into week_sales(prodid,prodname,weekday,amount)
&nbsp&nbsp&nbsp Values (prodid,prodname,’Fri’,fri_amt)
&nbsp&nbsp&nbsp Into week_sales(prodid,prodname,weekday,amount)
&nbsp&nbsp&nbsp Values (prodid,prodname,’Sat’,sat_amt)
Select prodid,prodname,mon_amt,tue_amt,wed_amt,thu_amt
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Fri_amt,sat_amt from sales;

Oracle MERGE Statement

Use the MERGE statement to select rows from one table for update or insertion into another table. The decision whether to update or insert into the target table is based on a condition in the ON clause. It is a new feature of Oracle Ver. 9i. It is also known as UPSERT i.e. combination of UPDATE and INSERT.

For example suppose we are having sales and sales_history table with the following structure.

SALES

Prod	Month	Amount
SONY SONY SONY SONY SONY SONY	JAN FEB MAR APR MAY JUN	2200 3000 2500 3200 3100 5000

SALES HISTORY

Prod	Month	Amount
SONY SONY SONY AKAI	JAN MAR APR JAN	2000 2500 3000 3200

Now we want to update sales_history table from sales table i.e. those rows which are already present in sales_history, their amount should be updated and those rows which are not present in sales_history table should be inserted.

merge into sales_history sh

   using sales s

   on (s.prod=sh.prod and s.month=sh.month)

when matched then update set sh.amount=s.amount

when not matched then insert values (prod,month,amount);

After the statement is executed sales_history table will look like this.

SALES HISTORY

Prod	Month	Amount
SONY SONY SONY SONY AKAI SONY SONY	JAN FEB MAR APR JAN MAY JUN	2200 3000 2500 3200 3200 3100 5000

Oracle UPDATE Command

The Oracle UPDATE statement processes one or more rows in a table and sets one or more columns to the values you specify.

Chooses which rows to update in the database table. Only rows that meet the where condition are updated. If you omit this clause, all rows in the table are updated.

1. Modify multiple rows with a single UPDATE statement

SQL> UPDATE Employee
  2  SET City ='L.A.'
  3  WHERE City = 'New York';

2 rows updated.

2. Without the WHERE clause, this UPDATE affects all rows in the table

SQL> UPDATE Employee
  2  SET Salary = Salary * 1.1;

8 rows updated.

3. Update with where condition

SQL> update employee 
     set salary = salary * 1.15 
     where id = '02';

1 row updated.

4. Change value case with update statement

SQL> UPDATE product
  2  SET    product_name = 'product Number'
  3  WHERE  product_name = 'Product Number';

1 row updated.

5. Update records in one table based on values in another table by using Subquery

SQL> UPDATE emp
  2      SET ename = ( SELECT dname
  3                    FROM dept
  4                    WHERE emp.deptno = dept.deptno)
  5      WHERE EXISTS
  6        ( SELECT dname
  7                    FROM dept
  8                    WHERE emp.deptno = dept.deptno);

6. Writing an UPDATE Statement Containing a Subquery

Set the new column value equal to the result returned by a single row subquery

SQL> UPDATE employee
  2  SET salary =
  3    (SELECT AVG(salary)
  4     FROM employee);

8 rows updated.

7. You can update a column and set it back to the default using the DEFAULT keyword in an UPDATE statement

SQL> UPDATE order_status
  2  SET status = DEFAULT
  3  WHERE order_status_id = 2;

1 row updated.

8. Use arithmetic operators and functions in the SET clause to modify data

SQL> UPDATE Employee
  2  SET Salary = Salary + 50
  3  WHERE ID = '01';

1 row updated.

9. An example of using a function in the SET clause

SQL> UPDATE Employee
  2  SET First_Name = UPPER(First_Name)
  3  WHERE ID = '01';

1 row updated.

10. Use TO_DATE('December 31, 2002, 11:30 P.M.','Month dd, YYYY, HH:MI P.M.') in set statement

SQL> UPDATE product
  2  SET    last_stock_date = TO_DATE('December 31, 2002, 11:30 P.M.',
  3                                   'Month dd, YYYY, HH:MI P.M.')
  4  WHERE  product_name LIKE '%Zinc%';

0 rows updated.

11. Change multiple rows and multiple columns in the same UPDATE statement

SQL> UPDATE employee
  2  SET
  3    salary = salary * 1.20,
  4    first_name = LOWER(first_name)
  5  WHERE
  6    salary >= 4000;

4 rows updated.

12.Update all records without condition

SQL>  update employee set city = 'BOSTON';

8 rows updated.

EXISTS Operator

Where exists (subquery)

The WHERE EXISTS subquery is used when we want to display all rows where we have a matching column in both tables. In most cases, this type of subquery can be re-written with a standard join to improve performance.

Where not exists (subquery)

The WHERE NOT EXISTS subquery is used to display cases where a selected column does not appear in another table.

As a general rule, the use of the NOT EXISTS subqueries are discouraged because the query can often be re-written as a standard join with much faster performance.

Some related examples.

1. Use EXISTS to link two queries.

SQL> SELECT * FROM emp WHERE EXISTS
  2  (select * from dept where dept.deptno = emp.deptno);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL
---------- ---------- --------- ---------- --------- ---------- 
      7934 MILLER     CLERK           7782 23-JAN-82       1300 
      7839 KING       PRESIDENT            17-NOV-81       5000 
      7782 CLARK      MANAGER         7839 09-JUN-81       2450 
      7902 FORD       ANALYST         7566 03-DEC-81       3000 
      7876 ADAMS      CLERK           7788 12-JAN-83       1100 
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000 
      7566 JONES      MANAGER         7839 02-APR-81       2975 
      7369 SMITH      CLERK           7902 17-DEC-80        800 
      7900 JAMES      CLERK           7698 03-DEC-81        950 
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL 
---------- ---------- --------- ---------- --------- ---------- 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250 
      7521 WARD       SALESMAN        7698 22-FEB-81       1250 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600 

2. The EXISTS condition can also be combined with the NOT operator.

SQL>     SELECT *
  2      FROM emp
  3      WHERE not exists (select * from dept 
  4                        Where dept.deptno = emp.deptno);

3. Test the NOT EXISTS version.

SQL> SELECT cust_no, lastname
  2    FROM customer c
  3    WHERE NOT EXISTS
  4    (SELECT cust_no FROM ord
  5     WHERE cust_no = c.cust_no);
   CUST_NO LASTNAME
---------- --------------------
         5 Hill
         8 Chili
         3 Jason
        10 Hack
        14 Pete
        13 Richer
        11 Bill

7 rows selected.

4. Display the customer number and last name of all customers with no orders.

SQL> select cust_no, lastname
  2    from customer c
  3    where not exists
  4    (select * from ord o where o.cust_no = c.cust_no);

   CUST_NO LASTNAME
---------- --------------------
         5 Hill
         8 Chili
         3 Jason
        10 Hack
        14 Pete
        13 Richer
        11 Bill

7 rows selected.

GROUP BY Clause

Whenever you do a group by or order by operation, Oracle will invoke a sorting operation. Using the group by inside SQL syntax is also useful for doing summaries, rollups and cubic operations.

Grouping is a special type of sorting. With sorting, all the rows are sorted by the columns specified. Grouping sorts the rows into groups so that multi row functions can be specified at the group level.

Some related examples.

1. Use avg, sum, max and count functions with group.

SQL> select job_id, avg(salary), sum(salary), max(salary), count(*)
  2        from employees
  3       group by job_id
  4      /

JOB_ID             AVG(SALARY) SUM(SALARY) MAX(SALARY)   COUNT(*)
----------- ----------- ----------- ----------- ----------
PROG                 21000       84000       24000          4
DBA                  20000       40000       20000          2
MGR                  27500       55000       30000          2

3 rows selected.

2. Must include a nonaggregate column in the SELECT list in the GROUP BY clause.

SQL> SELECT City, MAX(Salary) AS "Highest Cost"
  2  FROM Employee
  3  GROUP BY City;

CITY       Highest Cost
---------- ------------
New York        7897.78
Toronto         1234.56
Vancouver       6661.78

3. Grouping at Multiple Levels: group by more than one column.

SQL> SELECT count(*), city, description
  2  FROM employee
  3  GROUP BY city, description;

  COUNT(*) CITY       DESCRIPTION
---------- ---------- ---------------
         1 New York   Manager
         4 Vancouver  Tester
         1 Toronto    Programmer
         1 Vancouver  Manager
         1 New York   Tester

4. Column sequence in the group by impacts the ordering.

SQL> SELECT count(*), city, description
  2  FROM employee
  3  GROUP BY description, city;

  COUNT(*) CITY       DESCRIPTION
---------- ---------- ---------------
         1 Toronto    Programmer
         4 Vancouver  Tester
         1 Vancouver  Manager
         1 New York   Manager
         1 New York   Tester

SQL>
SQL> SELECT count(*), city, description
  2  FROM employee
  3  GROUP BY  city, description;

  COUNT(*) CITY       DESCRIPTION
---------- ---------- ---------------
         1 New York   Manager
         4 Vancouver  Tester
         1 Toronto    Programmer
         1 Vancouver  Manager
         1 New York   Tester

5. Using the ORDER BY Clause to Sort Groups.

SQL> SELECT city, COUNT(city)
  2  FROM employee
  3  GROUP BY city
  4  ORDER BY COUNT(city);

CITY       COUNT(CITY)
---------- -----------
Toronto              1
New York             2
Vancouver            5

6. You don't have to include the columns used in the GROUP BY clause in your SELECT clause.

SQL> SELECT COUNT(description)
  2  FROM employee
  3  GROUP BY city
  4  ORDER BY COUNT(id);

COUNT(DESCRIPTION)
------------------
                 1
                 2
                 5

7. GROUP BY and HAVING clauses.

SQL> SELECT city, AVG(salary)
  2  FROM employee
  3  GROUP BY city
  4  HAVING AVG(salary) > 3000;

CITY       AVG(SALARY)
---------- -----------
New York       6110.28
Vancouver      3823.78

8. Using a Column Multiple Times in a GROUP BY Clause.

SQL> SELECT division_id, job_id, SUM(salary)
  2  FROM employee
  3  GROUP BY division_id, ROLLUP(division_id, job_id);

DIV JOB SUM(SALARY)
--- --- -----------
BUS PRE      800000
SAL MGR      350000
SAL WOR      490000
SUP MGR      200000
BUS          800000
SAL          840000
SUP          200000
BUS          800000
SAL          840000
SUP          200000

10 rows selected.

HAVING Clause

The Oracle HAVING clause is used in conjunction with the Oracle GROUP BY clause to limit the returned rows after the grouping.

SQL statements can utilize both a WHERE clause and an Oracle HAVING clause. The WHERE clause will filter rows as they are selected from the table, and before grouping, the Oracle HAVING clause will filter rows after the grouping.

Some related examples.

1. Example using the MAX function with having clause.

SQL> SELECT JOB, MAX(sal) as "Highest salary"
  2      FROM emp
  3      GROUP BY job
  4      HAVING MAX(sal) < 50000;

JOB       Highest salary
--------- --------------
CLERK               1300
SALESMAN            1600
PRESIDENT           5000
MANAGER             2975
ANALYST             3000

2. Using the HAVING Clause.

SQL> SELECT City, AVG(Salary) AS "Average Salary"
  2  FROM Employee
  3  GROUP BY City
  4  HAVING AVG(Salary) > 500;

CITY       Average Salary
---------- --------------
New York          6110.28
Toronto           1234.56
Vancouver         3823.78

3. Any conditions based on the outcome of a group function must be in the HAVING clause.

SQL> SELECT City, AVG(Salary) AS "Average Salary"
  2  FROM Employee
  3  WHERE City ='Vancouver'
  4  GROUP BY City
  5  HAVING AVG(Salary) > 1000;

CITY       Average Salary
---------- --------------
Vancouver         3823.78

4. Using the SUM function in HAVING Clause.

SQL> SELECT job, SUM(sal) as "Total salary"
  2      FROM emp
  3      GROUP BY job
  4      HAVING SUM(sal) > 1000;

JOB       Total salary
--------- ------------
CLERK             4150
SALESMAN          5600
PRESIDENT         5000
MANAGER           8275
ANALYST           6000

5. Using HAVING with an Analytical Function.

SQL> -- if a HAVING clause is added, 
SQL> -- it will have its effect just before the RANK.
SQL> SELECT j.jobtitle, COUNT(*), MAX(orig_salary) maxsalary,
  2    MIN(orig_salary) minsalary
  3    --   RANK() OVER(ORDER BY MAX(orig_salary)) rankorder
  4  FROM employee e, job j
  5  WHERE e.orig_salary < 43000
  6    AND e.empno = j.empno
  7  GROUP BY j.jobtitle
  8  HAVING MAX(orig_salary) > 34000
  9  ORDER BY j.jobtitle desc;

no rows selected

6. Sub query inside having clause.

SQL> SELECT count(*), city
  2  FROM employee
  3  GROUP BY city
  4  HAVING count(*) =
  5  (SELECT max(count(*))
  6  FROM employee
  7  GROUP BY city);

  COUNT(*) CITY
---------- ----------
         5 Vancouver

Oracle LIKE Operater

Oracle Like Condition

The LIKE condition allows you to use wildcards in the where clause of an SQL statement. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.

The patterns that you can choose from are.

% allows you to match any string of any length (including zero length).

_ allows you to match on a single character.

Some related examples.

1. Like with '__' (Any two characters).

SQL> select * from Employee
  2  WHERE First_Name LIKE'A____n';

ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY     
---- ---------- ---------- --------- --------- ---------- ---------
02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver 

2. Like with '_' and '%'.

SQL> SELECT First_Name
  2  FROM Employee
  3  WHERE First_Name LIKE'_e%';

FIRST_NAME
----------
Celia

3. Use two '%' in Like statement.

SQL> SELECT First_Name
  2  FROM Employee
  3  WHERE First_Name LIKE'%a%';

FIRST_NAME
----------
Jason
James
Celia
Linda
David
James

6 rows selected.

4. Using a NOT operator with like.

SQL> SELECT ID, First_Name, Last_Name
  2  FROM Employee
  3  WHERE City NOT LIKE'%Van%';

ID   FIRST_NAME LAST_NAME
---- ---------- ----------
01   Jason      Martin
06   Linda      Green
07   David      Larry

5. Use % in word ending.

SQL> SELECT * FROM product WHERE  product_name LIKE 'Chrome%';

no rows selected