TRANSACTION COMMAND

COMMIT Command :

Used to commit all changes made since the beginning of the transaction. It does the

following.

• Makes all the changes made to database during transaction permanent

• Unlocks the rows that are locked during the transaction.

• Ends the transaction.

• Erases all savepoints in the current transaction(covered later).

Changes made to database such as inserting rows, deleting rows, and updating rows are not made permanent until they are committed.

Implicit Commit :Oracle Implicitly issues commit in the following cases.

• Before a DDL command.
• After a DDL command.
• At normal disconnect from the database. For example, when you exit SQL*PLUS using EXIT command.

Note: It is very important to remember not to mix DML commands with DDL command as the later commands are going to issue COMMIT, which might commit incomplete changes also.

ROLLBACK Command :

Used in transaction processing to undo changes made since the beginning of the transaction.

• Undoes the changes made to database in the current transaction
• Releases locks held on rows duration transaction.
• Ends the transaction.
• Erases all savepoints in the current transaction (covered later).

ROLLBACK SEGMENT:

In order to rollback changes that were made, Oracle has to store the data that was existing prior to the change so that previous data can be restored when user rolls back the changes.ROLLBACK SEGMENT is the area where Oracle stores data that will be used to roll back the changes. Every transaction is associated with a ROLLBACK SEGMENT.Generally a few ROLLBACK SEGMENTS are created at the time of creating database. Database Administrator can create some more rollback segments depending upon the requirement usingCREATE ROLLBACK SEGMENT command. Oracle assigns one rollback segment to each transaction. It is also possible to associate a transaction with a particular rollback segmentusing SET ROLLBACK SEGMENT command. It is not relevant to discuss more about the way ROLLBACK SEGMENT functions in a book like this. Please see Oracle Concepts for more information about ROLLBACK SEGEMENT.

SAVEPOINT Command : 

Savepoint is used to mark a location in the transaction so that we can rollback up to that mark and not to the very beginning of the transaction.

The following is the syntax of SAVEPOINT.
SAVEPOINT savepoint_name;

A single transaction may also have multiple savepoints. The following example illustrates

how to use save pointsSQL> update . . .SQL> savepoint s1;Savepoint created.SQL> insert .... ;SQL> insert .... ;SQL> savepoint s2;Savepoint created.SQL> delete ...;SQL> rollback to s1;Rollback complete.SQL> update…SQL> commit;

In the above example, ROLLBACK TO S1; will undo changes made from savepoint S1 tothe point of rollback. That means it will undo INSERT, INSERT, and even DELETE given aftersecond savepoint. It doesn’t undo UPDATE given before first savepoint. The COMMIT givenafter last UPDATE is going to commit first UDPATE and last UPDATE. Because all the remainingare already rolled back by ROLLBACK TO S1 command.If we have given ROLLBACK TO S2; then it would have undone only DELETE given after thesecond savepoint and remaining statements (update, insert, insert, and update) would have been committed.

Locking:It is important from database management system’s point of view to ensure that two user arenot modifying the same data at the time in a destructive manner.Let us consider the following example to understand what will happen if two users are trying to update the same data at the same time.Assume we have PRODUCTS table with details of products. Each product is having quantity on hand (QOH). Transactions such as sales and purchases are going to modify QOH column of the table.The following are the steps that might take place when two transactions – one sale and one purchase – are taking place.

1. Assume QOH of product 10 is 20 units.
2. At 10:00 USER1 has sold 5 units and updated QOH as follows but has not committed the
change. After UPDATE command QOH will be 15.
update products set qoh = qoh – 5 where prodid = 10;
3. At 10:11 USER2 has purchased 10 units and updated QOH as follows and committed. After
UPDATE command QOH will be 25 as 10 it added to 15.
update products set qoh = qoh + 10 where prodid = 10;
4. If at 10:12 USER1 has rolled back the UPDATE then data that was there before the
UPDATE should be restored i.e.20. But the actual data should be 30 as we added 10 units to it at 10:11 and committed.

As you can see in the above example, if two users are trying to update the same row at the same time the data may be corrupted. As shown in the above example at the end of the process QOH should be actually 30 but it is not only 20.
It is possible to ensure that two transactions are interfering with each other by locking the rows that are being modified so that only one transaction at a time can make the change.
Oracle ensures that only one transaction can modify a row at a time by locking the row once the row is updated. The lock will remain until the transaction is completed.
Oracle also ensures that other users will not see the changes that are not committed. That means if transaction T1 has updated a row then until the transaction is committed no other transaction in the system will see the changes made by T1. Instead other transactions will see
only the data that was existing before the change was made.

The following scenario will illustrate the process in detail.
1. Assume QOH of product 10 is 20.
2. Transaction T1 has issued UPDATE command to update QOH of product 10. Oracle locks the row that is updated and does not allow other transactions to update the row. However,
it is possible to read the data from the row.
update products set qoh = qoh + 5 where prodid = 10;

3. If T1 has issued SELECT to retrieve data then it will get 25 in QOH of product 10.
4. If T2 has issued SELECT command, it will see only 20 in QOH of product 10. This is because no uncommitted changes will be available to other transactions.
5. If T2 is trying to update product 10 then Oracle will cause transaction T2 to wait until transaction T1 (that holds lock on this row) is completed. Oracle will wail for lock to be released indefinitely.
6. If transaction T1 is committed then change is made permanent and lock will be released.Now it is possible for other transactions to see the updated data and even update the row if required.Now it is possible for other transactions to see the updated data and even update the row.

The following are the important points to be remembered about Oracle’s locking
mechanism.

• Locking in Oracle is automatic and transparent. That means we never need to ask Oracle to lock row that is being modified. Locking is transparent means user is not aware of the locking process. It happens automatically and in the background.
• Oracle locks the row that is being updated. That means locking is row-level. Other levels that are in user are - page-level and table-level.
• Oracle releases locks held by a transaction when transaction is completed either
• successfully – using COMMIT – or unsuccessfully – using ROLLBACK.
• If a transaction is trying to update a row and if row is already locked then Oracle will wait for the row that is locked to be unlocked indefinitely. It is because of the fact that rows are locked for a small duration in a typical production system. So Oracle prefers to wait to cause any error.
• It is possible to lock table manually using LOCK TABLE command.

Locking the rows that are being updated is an important part of Oracle. It ensures that no two transactions can update the same row at the same time. Locking mechanism is followed by all database management systems. But some smaller database management systems follow page-level locking where not the exact row that is being modified is locked instead the entire page in which the row exists is locked.

DELETING ROWS USING DELETE COMMAND

DELETE command is used to delete rows from a table. The following is the syntax of

DELETE command.DELETE FROM table[WHERE condition;]If WHERE clause is not given then all rows of the table will be deleted.The following command will delete row where CCODE is “c”.
Delete from coursesWhere ccode = ‘c’;It is not possible to delete a parent row while it has child rows. For example, it is not possible to delete a row from COURSES table if the row has dependent rows in BATCHES table or in COURSE_FACULTY table.However, it is possible to delete parent row along with its child rows provided ON DELETE CASCADE option is given at the time of create foreign key constraint in child table.Changes made using INSERT, UPDATE and DELETE are not made permanent until explicitly or implicitly they are committed. See next section for more information.

Transaction :

A transaction is a collection of statements used to perform a single task. These statements are logically related as they perform a single task. All these statements must be executed to successfully complete the task. If any of the statements fails then the all the statements that were executed prior to the statement that failed should be undone otherwise data in the database becomes invalid and inconsistent.

The following example will illustrate the process.

Assume that faculty with code kl (Kevin Loney) is leaving the institute. So all his batches areto be assigned to jc (Jason Couchman). For this the following steps are to be taken.

• Change FCODE of all the batches that are currently being handled by kl to jc.

• Delete rows from COURSE_FACULTY where FCODE is kl.

• Delete row from FACULTY where FCODE is kl.

That means the following are the commands to be executed to perform the above mentioned task.

update batches set fcode = ‘jc’ where fcode = ‘kl’;
delete from course_faculty where fcode = ‘kl’;
delete from faculty where fcode = ‘kl’;

It is important to make sure that all three statements are either successfully completed or all of them are rolled back. To ensure this Oracle provides transaction mechanism.If UPDATE command in the above set of commands begins the transaction then only COMMIT command is given after the second DELETE is executed, the changes are committed. If ROLLBACK command is given then all the changes up to UPDATE will be rolled back. So COMMIT and ROLLBACK command are used to ensure either everything is committed or everything is rolled back.A transaction is a collection of statements which is to be either completely done or not done at all. In other words the process should not be half-done. That means ALL or NOTHING.A transaction begins when previous transaction ends or when the session begins. A transaction ends when COMMIT or ROLLBACK is issued.  A new session starts when you connect to Oracle. For example, when you log on using SQL*PLUS you start a new session. When you exit SQL*PLUS the session is terminated.

ENABLING AND DISABLING CONSTRAINTS

ALTER TABLE can be used to enable and disable constraints without dropping constraints.When a constraint is disabled, Oracle does not enforce the rule defined by constraint. This may be useful when you need to insert a large number of rows and does not want Oracle to apply constraints as it takes a lot of time.
To disable PRIMARY KEY on SUBJECTS table:

ALTER TABLE courses DISABLE PRIMARY KEY;

Or you can drop any constraint by giving its name as follows:

alter table courses disable constraint courses_cat_chk;

If the constraint has depending constraints then you must use CASCADE clause to disable dependent constraints.

If the constraint has depending constraints then you must use CASCADE clause to disable dependent constraints.
You can enable a disabled constraints using ENABLE clause as follows:
alter table courses disable constraint courses_cat_chk;
Note: You can find out status of a constraint by using STATUS column of USER_CONSTRAINTS data dictionary view.

Dropping a table :To drop a table, use DDL command DROP TABLE. It removes the data as well as structure of the table The following is the syntax of DROP TABLE command.

DROP TABLE tablename [CASCADE CONSTRAINTS];

CASCADE CONSTRAINTS :clause is used to drop constraints that refer to primary and unique keys in the dropped table. If you do not give this clause and if referential integrity (references constraint) constraints exists then Oracle displays then Oracle displays error and doesn’t drop the table.

The following command will drop FACULTY table.

DROP TABLE faculty;

Note: When table is dropped, Views, and Synonyms based on the table will be made invalid, though they remain in the system. Also note, dropping a table cannot be undone.

Manipulating data :

As we have seen in the first chapter, SQL commands are divided into DML commands and DDL commands. DDL commands such as CREATE TABLE, ALTER TABLE, and DROP TABLE are dealing with definition of the table or structure of the table.
DML commands such as INSERT, DELETE and UPDATE are used to manipulate the data of the table. We have already seen how to use INSERT command to insert rows into table. Now let us see two other DML commands.

Updating rows using UPDATE command
UPDATE command is used to modify existing data in the rows. The following is the syntax of UPDATE command.
UPDATE table SET column = {expression | subquery}
[, column = {expression | subquery}] ...
[WHERE condition];
If WHERE clause is not given then all the rows of the table will be effected by the
change. In fact, it is more often the result of an error than intentional.
The following command will change course fee of ASP to 6000.
Update courses set fee = 6000
Where ccode = ‘asp’;
It is also possible to change more than one column at a time as follows:
update courses set fee = 6000, duration=30
where ccdoe = ‘asp’;
Note: We will discuss how to use subquery in UPDATE command later in this book.

DROPPING A CONSTRAINTS

Used to drop the constraints defined on the table.
To drop a constraint, the name of the constraint is required. You may Use  USER_CONSTRAINTS data dictionary view to get the list of constraints.

alter table courses drop constraint courses_cat_chk;
CASCADE Option :

You cannot drop a UNIQUE or PRIMARY KEY constraint that is part of a referential integrity constraint without also dropping the corresponding foreign key. To drop PRIMARY KEY or UNIQUE constraint along with REFERENCES constraint use CASCADE option.

To drop PRIMARY KEY constraint of STUDENTS along with related constraint, do the following.

alter table courses
drop primary key cascade;

Note: You can get information about all the constraint using USER_CONSTRAINTS data dictionary view.

Dropping a column :

For the first time Oracle8i has provided a command to drop a column from the table. Till Oracle8, dropping a column is very lengthy task as there was no direct way to drop a column.
Oracle8i has provided a new option with ALTER TABLE command to drop a column – DROP COLUMN.
Actual you have two options when you want to drop a column.
Either you can drop unwanted column straight away. All the data related to the column
being dropped will be removed immediately.
Or you can mark the column for deletion and delete the column at a later stage. Since the column is marked for dropping, it is considered to be dropped. But the data of the column will remain until the column is physically removed.

The second options is especially useful considering the fact that dropping of a column does take a lot of time and if it is done when the load on the system is high then it will severely effect performance of the system. So you can mark a column for dropping and then drop the column when load on the system is low.
To drop column CAT of COURSES table, enter the following:

alter table courses drop column cat;

If column being dropped is either a PRIMARY KEY or UNIQUE key that is referenced by a foreign key, then it is not possible to drop the column. But it is possible if CASCADE CONSTRAINTS option is used. CASCADE CONSTRAINTS option drops all constraints that depend on the column being dropped.
To drop column FCODE column of FACULTY table along with all depending constraints, enter:
alter table faculty drop column fcode cascade constraints;

Note: When you drop a UNIQUE or PRIMARY KEY column then Oracle automatically drops the index that it creates to enforce uniqueness.

SET UNUSED option of ALTER TABLE command is used to mark a column for dropping. But the column is not physically removed. However, the column is treated as deleted. Once a column is marked as UNUSED then it cannot be accessed.
The following example marks column CAT or COURSES table as unused.

alter table courses set unused column cat;

Columns that are marked for deletion can be physically deleted using DROP UNUSED COLUMNS option of ALTER TABLE command as follows:
alter table courses drop unused columns;

Note: We can view the number of columns that are marked for deletion using USER_UNUSED_COL_TABS data dictionary view. USER_TAB_COLUMNS gives information about existing columns of a table.

Note: Until a column is physically dropped from the table it is counted as a column of the table and counted towards the absolute limit of 1000 columns per table.

CHANGING STRUCTURE AND DATA

Altering the structure of a table :

It is possible to modify the structure of the table even after the table is created. ALTER TABLE command is used to alter the structure of the table.

Adding a new column
Adding a new table level constraint
Increasing and decreasing width of a column
Changing data type of the column
Dropping a column
Dropping a constraint
Disabling a constraint
Enabling a disabled constraint

The following is the syntax of ALTER TABLE command.

ALTER TABLE Syntax

ALTER TABLE tablename
[ ADD ( column specification ) ]
[ MODIFY ( column specification) ]
[ DROP constraint-name [CASCADE]
| column [CASCADE CONSTRAINTS]
[ DROP UNUSED COLUMN [column] ]
[SET UNUSED column]
[ ENABLE | DISABLE constraint-name ]

Let us now examine various examples using ALTER TABLE command. It is important to note that some possibilities are applicable only when certain conditions are met. These if and buts are to be remembered while modifying the structure of the table.

Adding a new column or constraint :

It is always possible to add a new column to an existing table. However, if column is to be added to a table that already contains rows, then certain options are not available.

To add a new column CAT (category) to COURSES table, enter the following:
ALTER TABLE courses
ADD (cat varchar2(5));
It is not possible to given any constraint that is not satisfied by existing data. For instance, it is not possible to add CAT as a NOT NULL column as Oracle initializes CAT column in all rows with NULL value. See the following snapshot of the screen.
SQL> alter table courses
2 add ( cat varchar2(5) not null);
alter table courses
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

However, it is possible to have NOT NULL constraint in the following cases:

• If DEFAULT option is used to specify the default value for the column
• When table is empty.

The following example will work as DEFAULT option is used to specify the default value.

alter table courses 

add ( cat varchar2(5) default 'prog' not null);

You can add a table constraint. Once table is created, it is not possible to add constraints other than NOT NULL to columns of the table. However, it is possible to add any  constraint at the table level as follows:

alter table courses
add ( constraint courses_cat_chk check (length(cat) >= 2))
We will see more about functions such as length later in this book. The above constraint
specifies that the column CAT should have at least two characters. It is added as a table constraint as it not possible to add CHECK constraint at the column level to column that already exists.

Modifying attributes of existing columns :

It is also possible to modify the certain attributes of an existing column. The following are possible modifications.

• Increasing the length of the column
• Decrease the length of the column only when column is empty.
• Change the datatype of the column only when column is empty.
• Adding NOT NULL constraint. No other constraint can be added to column. However, it is
• possible to add constraints at table level. See the previous section.

To increase the size of the column CAT, enter the following:

alter table courses
modify (cat varchar2(10));

Note: You can decrease the width and even change the datatype of the column if the column is empty. That means if no row has any value for the column being altered.

ON DELETE CASCADE OPTION

As mentioned earlier, after a foreign key is defined, Oracle will NOT allow any parent row to be deleted if it has dependent rows in the child table.
For example, if CCODE in COURSE_FACULTY table is defined as foreign key referencing CCODE column of COURSES table then it is NOT possible to delete rows from COURSES table if dependent rows exists in COURSE_FACULTY table.

However, by using ON DELETE CASCADE it is possible to delete all child rows while parent row is being deleted.

The following code shows how to use ON DELETE CASCADE option.

CREATE TABLE course_faculty

(ccode varchar2(5)

CONSTRAINT course_faculty_ccode_fk REFERENCES courses(ccode)
ON DELETE CASCADE,
...
);

CHECK Constraint :

Defines the condition that should be satisfied before insertion or updation is done.
The condition used in CHECK constraint may NOT contain:

• A reference to pseudo column SYSDATE
• Subquery

If it is given as column constraint, it can refer only to current column. But if it is given as table constraint, it can refer to more than one column of the table. In neither case it can refer to a column of other tables.
The following example shows how to create CHECK constraint to make sure GRADE column of
COURSE_FACULTY contains letters A, B and C only.

CREATE TABLE course_faculty
( ...,
grade char(1) CONSTRAINT course_faculty_grade_chk
CHECK ( grade in (‘A’,’B’,’C’) ),
...
);
The above CHECK constraint does not allow any other characters other than A, B and C. It must be noted that character comparison is always case sensitive. So to ignore case differences you can convert GRADE to uppercase before comparison made as follows:
CREATE TABLE course_faculty
( ...,
grade char(1) CONSTRAINT course_faculty_grade_chk
CHECK ( upper(grade) in (‘A’,’B’,’C’) ),
...
);
The following is an example of CHECK constraint at table level. The constraint makes sure the starting date (STDATE) of a batch is less than or equal to ending date (ENDDATE) of the batch.
CREATE TABLE batches
( ...,
CONSTRAINT batches_dates_chk
CHECK ( stdate <= enddate),

);

CREATING INTEGRITY CONSTRAINTS

In the following few sections we will see how to integrity constraints.

NOT NULL Constraint :

Used to prevent any null value from entering into column. This is automatically defined for column with PRIMARY KEY constraint.

The following example shows how you can define course name as not null column using NOT NULL constraint.

CREATE TABLE COURSES
( ...,
name varchar2(20)
CONSTRAINT courses_name_nn NOT NULL,
...
);
CONSTRAINT option is used to given a name to constraint. The convention followed here is
TABLENAME_COLUMN_TYPE.

PRIMARY KEY Constraint :

This constraint is used to define the primary key of the table. A primary key is used to
uniquely identify rows in a table. There can be only one primary key in a table. It may consist of more than one column. If primary key is consisting of only one column, it can be given as column constraints otherwise it is to be given as table constraint.

Note: You have to use table constraint to define composite primary key.

Oracle does the following for the column that has PRIMARY KEY constraint.

• Creates a unique index to enforce uniqueness. We will discuss about indexes later in this book.
• Defines NOT NULL constraint to prevent null values.

The following example shows how to use PRIMARY KEY constraint at column level.

CREATE TABLE COURSES
( ccode varchar2(5) CONSTRAINT courses_pk PRIMARY KEY,
... );

The following example shows how to define composite primary key using PRIMARY KEY constraint at the table level.

CREATE TABLE COURSE_FACULTY
( ...,
CONSTRAINT COURSE_FACULTY_PK PRIMARY KEY (ccode,faccode)
);

UNIQUE Constraint :

Enforces uniqueness in the given column(s). Oracle automatically creates a unique index for this column.

The following example creates unique constraint on NAME column of COURSES table.

CREATE TABLE courses
( ... ,
name varchar2(20)
CONSTRAINT courses_name_u UNIQUE,
... );
If two or more columns collective should be unique then UNIQUE constraint must be given at the table level.

FOREIGN KEY Constraint :

A foreign key is used to join the child table with parent table. FOREIGN KEY constraint is used to provide referential integrity, which makes sure that the values of a foreign key are derived from parent key. It can be defined either at the table level or at the column level.
If a foreign key is defined on the column in child table then Oracle does not allow the parent row to be deleted, if it contains any child rows. However, if ON DELETE CASCADE option is given at the time of defining foreign key, Oracle deletes all child rows while parent row is being deleted.

The following example defines foreign key constraint for CCODE of  COURSE_FACULTY table.

CREATE TABLE course_faculty
(ccode varchar2(5)
CONSTRAINT course_faculty_ccode_fk REFERENCES courses(ccode),

Note: When the name of the column in the referenced table is same as the foreign key then column need not be given after the table name. It means REFERENCES courses in the above example will suffice.

CREATING SAMPLE TABLES 2

Batches table :

Contains information about all the batches. These batches include batches that were
completed, that are currently running and that are scheduled but yet to start.

Column Name            Data Type                Description

BCODE                        VARCHAR2(5)       Code that is assigned to each batch.                                                                                  This is the primary key of the table.

CCODE                         VARCHAR2(5)      Course code of the course of this                                                                                       batch. This is a foreign key                                                                                                 referencing CCODE of COURSES                                                                                   table. 

FACCODE                    VARCHAR2(5)           Code of the faculty member taking                                                                                    this batch.

STDATE                        DATE                           Date on which the batch has started                                                                                  or scheduled to start if batch has not                                                                                  yet started.

ENDDATE                     DATE                           Date on which the batch has                                                                                              completed. If batch is not                                                                                                  completed this will be null.

TIMING                        NUMBER(1)                 Number indicating the timing of the                                                                                  batch. 1- morning, 2 – after noon,                                                                                      and 3-evening.

The following are the required constraints of this table. :

• BCODE is the primary key.
• CCODE is a foreign key referencing CCODE of COURSES table.
• FACCODE is a foreign key referencing FACCODE of FACULTY table.
• STDATA must be <= ENDDATE
• TIMING column must be 1, 2 or 3.

STUDENTS table :

Contains information about all the students of the institute. Each student is given a roll number. Roll number will be allotted to each student of each batch.

Column Name             Data Type          Description
ROLLNO                     NUMBER(5)       Roll number that is assigned to each                                                                                  student. This is the primary key of the table.
BCODE                       VARCHAR2(5)    Code of the batch to which student belongs.                                                                      This is the foreign key referencing BCODE                                                                       of BATCHES table.
NAME                         VARCHAR2(30)    Name of the student.
GENDER                     CHAR(1)               Gender of the student. M for male and F for
                                                                     female.
DJ                                DATE                      Date on which the student has joined.
PHONE                       VARCHAR2(10)     Contact number of the student.
EMAIL                        VARCHAR2(30)     Email address of the student

The following are the constraints of the table.

• ROLLNO is the primary key.
• BCODE is a foreign key referencing BCODE of BATCHES table.
• GENDER may be either M or F.

PAYMENTS table :

Contains information about all the payment made by students of all bathes.

Column Name         Data Type              Description

ROLLNO                 NUMBER(5)           Roll number of the student                                                                             paying the fee.
DP                            DATE                       Date on which the amount is                                                                          paid.
AMOUNT               NUMBER(5)             The amount paid by student.

The following are the constraints.

• Primary key is consisting of ROLLNO and DP.
• AMOUNT must be >= 25

CREATING SAMPLE TABLES 1

What is a constraint?

In the previous chapter we have seen how to create a table using CREATE TABLE command.
Now we will understand how to define constraints. Constraints are used to implement standard
and business rules. Data integrity of the database must be maintained. In order to ensure
data has integrity we have to implement certain rules or constraints. As these constraints are
used to maintain integrity they are called as integrity constraints.

Standard rules: 

Standard constraints are the rules related to primary key and foreign key. Every table must have a primary key. Primary key must be unique and not null. Foreign key must derive its values from corresponding parent key. These rules are universal and are called as standard rules.

Business rules : 

These rules are related to a single application. For example, in a payroll application we may
have to implement a rule that prevents any row of an employee if salary of the employee is
less than 2000. Another example is current balance of a bank account
Must be greater than or equal to 500.
Once the constraints are created, Oracle server makes sure that the constraints are not
violated whenever a row is inserted, deleted or updated. If constraint is not satisfied then the
operation will fail.
Constraints are normally defined at the time of creating table. But it is also possible to add
constraints after the table is created using ALTER TABLE command. Constraints are stored in
the Data Dictionary (a set of tables which stores information regarding database).

Types of constraints :

Constraints can be given at two different levels. If the constraint is related to a single column
the constraint is given at the column level otherwise constraint is to be given at the table
level. Base on the where a constraint is given, constraint are of two types:

• Column Constraints

• Table Constraints

Column Constraint :

A constraint given at the column level is called as Column Constraint. It defines a rule for a
single column. It cannot refer to column other than the column at which it is defined. A typical
example is PRIMARY KEY constraint when a single column is the primary key of the table.

Table Constraint :

A constraint given at the table level is called as Table Constraint. It may refer to more than
one column of the table. A typical example is PRIMARY KEY constraint that is used to define
composite primary key. A column level constraint can be given even at the table level, but a
constraint that deals with more than one column must be given only at the table level.
The following is the syntax of CONSTRAINT clause used with CREATE TABLE and ALTER TABLE
commands.
[CONSTRAINT constraint]
{ [NOT] NULL
| {UNIQUE | PRIMARY KEY}
| REFERENCES [schema.] table [(column)]
[ON DELETE CASCADE]
| CHECK (condition) }

The following is the syntax of table constraint. :

[CONSTRAINT constraint]
{ {UNIQUE | PRIMARY KEY} (column [,column] ...)
| FOREIGN KEY (column [,column] ...)

REFERENCES [schema.] table [(column [,column] ...)]
[ON DELETE CASCADE]
| CHECK (condition) }
The main difference between column constraint and table constraint is that in table constraint
we have to specify the name of the column for which the constraint is defined whereas in
column constraint it is not required as constraint is given on immediately after the column.
Now let us understand sample table to be throughout this book. It is very important to
understand these tables to get the best out of this book. I have made these tables to be easy
to understand.

Sample tables:

The following are the sample tables used throughout the book. These tables store information
about course, batches and subject. There are six tables to store the required information by
typical training center.
Let us first understand the meaning of each table.
The following are the required tables of our application.

Table Name                               DescriptionCourses                                       Contains the details of all the courses offered by the                                                         institute.
Faculty                                        Contains the details of the faculty members of the                                                           institute.
Course_faculty                           This table contains information regarding which                                                               faculty can handle
                                                    which course. It also contains rating regarding how                                                          good a faculty
                                                    member is in handling a particular course. The rating                                                       is based on
                                                    previous experience of the faulty member with that                                                         course.
Batches                                       Contains the information about all the batches. It                                                             contains information about all the batches that started                                                     and completed, on going and scheduled but not yet                                                           started.
Students                                      Contains information about all the students. Each                                                             student is assigned a new roll number whenever                                                               he/she joins a new course.
Payments                                    Information about all the payments made by students.                                                       A single student may pay course fee in multiple                                                               installments for a single course.

The following few tables will give the list of columns of each of the table given in table 1.

COURSES Table

Contains information related to each course. Each course is given a unique code called course code.

Column Name          Data Type              Description CCODE                     VARCHAR2(5)      Course Code. This is the primary key of the                                                                     table.
NAME                       VARCHAR(30)      Name of the course.
DURATION               NUMBER(3)         Duration of the course in no. of working                                                                            days.
FEE                            NUMBER(5)         Course fee of the course.
PREREQUISITE       VARCHAR2(100)  Prerequisite knowledge to do the course.     

The following are the required constraints of COURSES table.

• CCODE is primary key.

• FEE must be greater than or equal to 0.

• DURATION must be greater than or equal to 0.

FACULTY Table

Contains information about all the faculty members. Each faculty member is given a code called as FACCODE.
Column Name            Data Type               Description

FACCODE                  VARCHAR2(5)      Faculty code. This is the primary key of the table.
NAME                         VARCHAR2(30)    Name of the faculty.
QUAL                          VARCHAR2(30)    Qualification of the faculty member.
EXP                             VARCHAR2(100)   Experience of the faculty member.

The following are the constraints of FACULTY table.

• FACCODE is primary key.

COURSE_FACULTY table :

Contains information regarding which faculty member can take which course. A single faculty member may be capable of handling multiple courses. However, each member is given a grade depending on his expertise in handling the subject. The grade will be wither A, B or C.

Column Name                 Data Type                        Description

  FACCODE                      VARCHAR2(5)               Faculty code.

 CCODE                            VARCHAR2(5)               Course the faculty can handle.

GRADE                                  CHAR(1)                                Rating of faculty’s ability to handle this                                                                                                    particular code. A – Very good, B- Good,

                                                                                              C- Average.

The following are the constraints of the table.

• FACCODE is a foreign key referencing FACCODE column of FACULTY table.
• CCODE is a foreign key referencing CCODE column of COURSES table.
• Primary key is consisting of FACCODE and CCODE.
• GRADE column must contain either A, B or C.

SQL OPERATOR

A part from standard relational operators (= and >), SQL has some other operators that can be used in conditions.

Operator                                               What it does? BETWEEN value-1 AND value-2       Checks whether the value is in the
                                                              given range. The range is                                                                                                                           inclusive of the given values.
IN(list)                                                  Checks whether the value is matching  
                                                              with any one of the values given in the
                                                              list. List contains values separated by comma(,).
 LIKE pattern                                        Checks whether the given string is matching
                                                              with the given pattern. More on this later.
IS NULL and IS NOT NULL               Checks whether the value is null or not null.

Now, let us see how to use these special operators of SQL.

BETWEEN ... AND Operator  Checks whether value is in the given range. The range includes all the values in the range including the min and max values. This supports DATE type data also.

To display the list of course where DURATION is in the range 20 to 25 days, enter:

select name
from courses
 where duration between 20 and 25;
 NAME --------------------
Oracle database
C programming
ASP.NET
Java Language
Note: BETWEEN.. AND is alternative to using >= and <= operators.

IN Operator 

Compares a single value with a list of values. If the value is matching with any of the values given in the list then condition is taken as true.
The following command will retrieve all courses where duration is either 20 or 30 days.
select name
from courses
where duration in (20,30);
NAME --------------------
VB.NET
C programming
The same condition can be formed even without IN operator using logical operator OR as follows: Select name
from courses
where duration = 20 or duration = 30;
However, it will be more convenient to user IN operator compared with multiple conditions compared with OR operator.

LIKE operator 

This operator is used to search for values when the exact value is not known. It selects rows that match the given pattern. The pattern can contain the following special characters.

Symbol                                  Meaning 

% %. _ (underscore)       Zero or more characters can take the place
                                        of  Any single character can take the place
                                        of underscore. But there must be one letter.

To select the courses where the course name contains pattern .NET, enter:

select name,duration, fee from courses
where name like '%.NET%'
NAME          DURATION     FEE
-------------------- --------- ---------
VB.NET         30                   5500
ASP.NET       25                    5000
The following example selects courses where second letter in the course code is “b” and column PREREQUISITE contains word “programming”.

select * from courses where ccode like '_b%' and prerequisite like '%programming%';
CCODE            NAME          DURATION     FEE         PREREQUISITE
----- -------------------- --------- --------- ------------------------
vbnet                VB.NET            30                 5500          Windows and programming

Remember LIKE operator is case sensitive. In the above example, if CCODE contains value in uppercase (VB), then it won’t be a match to the pattern.

IS NULL and IS NOT NULL operators  

These two operators test for null value. If we have to select rows where a column is containing null value or not null value then we have to use these operators. For example the following SELECT command will select all the courses where the column FEE is null.
select * from courses
where fee is null;

Though Oracle provides NULL keyword, it cannot be used to check whether the value of a column is null. For example, the following condition will always be false as Oracle treats two null values as two different values.
select * from courses
where fee = null;

The above command does NOT work as fee though contains null value will not be equal to NULL. SO, we must use IS NULL operator. 

SELECTION OPERATOR

It is possible to select only the required rows using WHERE clause of SELECT command. It implements selection operator of relational algebra. WHERE clause specifies the condition that rows must satisfy in order to be selected. The following example select rows where FEE is more than or equal to 5000.
select name, fee from courses
where fee >= 5000

NAME                     FEE

-------------------- ---------

VB.NET                 5500
ASP.NET                 5000

The following relational and logical operators are used to form condition of WHERE clause. Logical operators – AND, OR – are used to combine conditions. NOT operator reverses the result of the condition. If condition returns true, NOT will make the overall condition false.

Operator              Meaning 

 =                            Equal to
!= or <>                  Not equal to
>=                          Greater than or equal to
<=                          Less than or equal to
 >                           Greater than
<                            Less than
AND                     Logical ANDing
OR                        Logical Oring
NOT                     Negates result of condition.

The following SELECT command displays the courses where duration is more than 15 days and course fee is less than 4000.
select * from courses
where duration > 15 and fee < 4000;

CCODE    NAME       DURATION FEE      PREREQUISITE
 ----- -------------------- --------- --------- -----------------

c                  C programming      20 3500                 Computer Awareness

The following SELECT command retrieves the details of course with code ORA.
select * from courses where ccode = 'ora';

CCODE   NAME       DURATION FEE      PREREQUISITE
                                                                                                   

ora         Oracle database     25 4500               Windows

Note: When comparing strings, the case of the string must match. Lowercase letters are not equivalent to uppercase letters.

ORDER BY CLAUSE

It is possible to display the rows of a table in the required order using ORDER BY clause. It is used to sort rows on the given column(s) and in the given order at the time of retrieving rows. Remember, sorting takes place on the row that are retrieved and in no way affects the rows in the table. That means the order of the rows will remain unchanged.

Note: ORDER BY must always be the last of all clauses used in the SELECT command.

The following SELECT command displays the rows after sorting rows on course fee.

 select name, fee from courses order by fee;

NAME                      FEE
 -------------------- ---------
C programming         3500
XML Programming  4000
Oracle database        4500
Java Language         4500
ASP.NET                 5000
VB.NET                  5500

Note: Null values are placed at the end in ascending order and at the beginning in descending order.

The default order for sorting is ascending. Use option DESC to sort in the descending order. It is also possible to sort on more than one column. To sort rows of COURSES table in the ascending order of DURATION and descending order of FEE, enter:

select name, duration, fee from courses
order by duration , fee desc;

NAME              DURATION              FEE
-------------------- --------- ---------
XML Programming 15                      4000
C programming        20                      3500
ASP.NET                 25                      5000
Oracle database       25                      4500
Java Language         25                     4500
VB.NET                  30                      5500

First, all rows are sorted in the ascending order of DURATION column. Then the rows that have same value in DURATION column will be further sorted in the descending order of FEE column.

Using column position 

Instead of giving the name of the column, you can also give the position of the column on which you want to sort rows.

For example, the following SELECT sorts rows based on discount to be given to each course.

select name, fee, fee * 0.15
from courses
order by 3;

NAME         FEE            FEE*0.15
 -------------------- --------- ---------
C                 3500            525
XML          4000             600
Oracle        4500             675
Java            4500            675
ASP.NET   5000            750
VB.NET     5500            825

Note: Column position refers to position of the column in the selected columns and not the position of the column in the table.

The above command uses column position in ORDER BY clause. Alternatively you can use column alias in ORDER BY clause as follows:

select name, fee, fee * 0.15 discount
from courses
order by discount;

NAME        FEE              DISCOUNT
-------------------- --------- ---------
C                3500              525
XML          4000              600
Oracle        4500              675
Java            4500              675
ASP.NET   5000              750
VB.NET     5500              825

SELECTING ROWS FROM A TABLE

Let us see how to retrieve data of a table. SELECT command of SQL is used to retrieve data from one or more tables. It implements operators of relational algebra such as projection, and selection. The following is the syntax of SELECT command. The syntax given here is incomplete. For complete syntax, please refer to online documentation

SELECT [DISTINCT | ALL]
 {* | table.* | expr } [alias ]
 [ {table}.*| expr } [alias ] ] ...
 FROM [schema.]object
 [, [schema.]object ] ...
 [WHERE condition]
 [ORDER BY {expr|position} [ASC | DESC]
 [, {expr|position} [ASC | DESC]] ...]

schema is the name of the user whose table is being accessed. Schema prefix is not required if the table is in the current account. Schema prefix is required while we are accessing a table of some other account and not ours. The following is an example of a basic SELECT command.

select * from courses;

 CCODE     NAME DURATION     FEE         PREREQUISITE                                                                                                                                            

ora               Oracle database         25 4500      Windows
vbnet           VB.NET                    30 5500      Windows and programming
c                   C programming        20 3500       Computer Awareness
asp               ASP.NET                  25 5000       Internet and programming
java             Java Language           25 4500      C language
xml             XML Programming    15 4000      HTML,Scripting, ASP/JSP

The simplest SELECT command contains the following:

• Columns to be displayed. If * is given, all columns are selected.

• The name of the table from where rows are to be retrieved. 

Projection : Projection is the operation where we select only a few columns out of the available columns. The following is an example of projection. 

select name,fee from courses;
NAME                             FEE
-------------------- ---------
Oracle database              4500
VB.NET                         5500
C programming              3500
ASP.NET                        5000
Java Language                4500
 XML Programming       4000

Using expressions in SELECT command  It is also possible to include expressions in the list of columns. For example, the following SELECT will display discount to be given for each course.

select name,fee, fee * 0.15 from courses;
NAME                FEE               FEE*0.15
 -------------------- --------- ---------
Oracle database 4500                675
VB.NET            5500                825
C programming 3500                525
ASP.NET           5000                750
Java Language   4500                675
XML                  4000                600

Column Alias  The column heading of an expression will be the expression itself. However, as it may not be meaningful to have expression as the result of column heading, we can give an alias to the column so that alias is displayed as the column heading.
The following example will use alias DISCOUNT for the expression FEE * 0.15.
select name, fee, fee * 0.15 DISCOUNT from courses
NAME                    FEE                    DISCOUNT
-------------------- - -------- ---------
Oracle database        4500                    675
VB.NET                   5500                    825
C programming        3500                    525
ASP.NET                  5000                    750
Java Language          4500                    675
 XML Programming 4000                    600

The following are the arithmetic operators that can be used in expressions.
Operator            Description
+                        Add
 -                       Subtract
*                       Multiply
 /                       Divide