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

INSERTING ROWS INTO A TABLE

Now, let us see how to insert rows into COURSES table. SQL command INSERT is used to insert new row into the table. While inserting rows, you may enter value for each column of the table or selected columns. The following command inserts a row into COURSES table.

insert into courses
 values('ora','Oracle database',25,4500,'Knowledge of Windows');

Note:After inserting the required row, issues COMMIT command to make sure the changes are made permanent. We will discuss more about COMMIT command later in this book but for the time being it is sufficient to know that COMMIT command will make changes permanent. Without COMMIT, rows that are inserted might be lost if there is any power failure.

During insertion, character values are enclosed in single quotes. Unless otherwise specified we have to supply a value for each column of the table. If the value of any column is not known or available then you can give NULL as the value of the column. For example, the following insert will insert a new row with null value for PREREQUISITE column.

insert into courses
values('c','C Programming',25,3000,null);

Note: INSERT command can insert only one row at a time. For multiple row, INSERT command must be issued for multiple times.
DATE type values must be in the format DD-MON-YY or DD-MON-YYYY, where MON is the first three letters of the month (Jan, Feb). If only two digits are given for year then current century is used. For example, if you give 99 for year, Oracle will take it as 2099 as the current century is 2000. So it is important to remember this and give four digits if required.
The following is the complete syntax for INSERT command.
INSERT INTO tablename [(columns list)]
 {VALUES (value-1,...) | subquery }
We will see how to insert row into a table using a subquery later in this blog
Inserting a row with selected columns 
It is possible to insert a new row by giving values only for a few columns instead of giving values for all the available columns
The following INSERT command will insert a new row only two values.
insert into courses(ccode,name)
 values ('odba','Oracle Database Administration');
 The above command will create a new row in COURSES table with values for only two columns – CCODE and NAME. The remaining columns will take NULL value or the default value, if the column is associated with default value. We will discuss more about default value in the next blog

NULL value :

Null value means a value that is not available or not known. When a column’s value is not known then we store NULL value into the column. NULL value is neither 0 nor blank nor any other known value. We have already seen how to store null value into a column and when Oracle automatically stores null value into a column. We will discuss more about how to process null value later in this chapter.