Fundamentals of SQL - Basics with example - 11

     This will convert a character string to act like an internal oracle row identifier or rowid.

c) ROWIDTOCHAR

    This will convert an internal oracle row identifier or rowid to character string.

d) TO_NUMBER

    This will convert a char or varchar to number.

e) TO_CHAR

    This will convert a number or date to character string.

f) TO_DATE

    This will convert a number, char or varchar to a date.

GROUP FUNCTIONS

70  Sum

71  Avg

72  Max

73  Min

74  Count

Group functions will be applied on all the rows but produces single output.

a) SUM

     This will give the sum of the values of the specified column.

     Syntax: sum (column)

     Ex:

          SQL> select sum(sal) from emp;

  SUM(SAL)

   ----------

     38600

b) AVG

     This will give the average of the values of the specified column.

     Syntax: avg (column)

     Ex:

          SQL> select avg(sal) from emp;

   AVG(SAL)

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

   2757.14286

c) MAX

     This will give the maximum of the values of the specified column.

     Syntax: max (column)

     Ex:

          SQL> select max(sal) from emp;

    MAX(SAL)

   ----------

     5000

        d) MIN

     This will give the minimum of the values of the specified column.

     Syntax: min (column)

     Ex:

          SQL> select min(sal) from emp;

   MIN(SAL)

   ----------

     500

e) COUNT

     This will give the count of the values of the specified column.

     Syntax: count (column)

     Ex:

          SQL> select count(sal),count(*) from emp;

COUNT(SAL)   COUNT(*)

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

        14                     14

CONSTRAINTS

Constraints are categorized as follows.

Domain integrity constraints

1     Not null

2     Check

Entity integrity constraints

1     Unique

2     Primary key

Referential integrity constraints

1     Foreign key

Constraints are always attached to a column not a table.

We can add constraints in three ways.

2     Column level -- along with the column definition

3     Table level                 -- after the table definition

4     Alter level                  -- using alter command

While adding constraints you need not specify the name but the type only, oracle will internally name the constraint.

If you want to give a name to the constraint, you have to use the constraint clause.

NOT NULL

This is used to avoid null values.

We can add this constraint in column level only.

Ex:

     SQL> create table student(no number(2) not null, name varchar(10), marks

             number(3));

     SQL> create table student(no number(2) constraint nn not null, name varchar(10),

             marks number(3));

CHECK

This is used to insert the values based on specified condition.

We can add this constraint in all three levels.

Ex:

     COLUMN LEVEL

     SQL> create table student(no number(2) , name varchar(10), marks number(3) check

             (marks > 300));

      SQL> create table student(no number(2) , name varchar(10), marks number(3)

             constraint ch  check(marks > 300));

      TABLE LEVEL

      SQL> create table student(no number(2) , name varchar(10), marks number(3), check

             (marks > 300));

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             constraint ch check(marks > 300));

      ALTER LEVEL

      SQL> alter table student add check(marks>300);

      SQL> alter table student add constraint ch check(marks>300);

UNIQUE

This is used to avoid duplicates but it allow nulls.

We can add this constraint in all three levels.

Ex:

      COLUMN LEVEL

     SQL> create table student(no number(2) unique, name varchar(10), marks

             number(3));

      SQL> create table student(no number(2)  constraint un unique, name varchar(10),

             marks number(3));

       TABLE LEVEL

     

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             unique(no));

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             constraint un unique(no));

      ALTER LEVEL

      SQL> alter table student add unique(no);

      SQL> alter table student add constraint un unique(no);

PRIMARY KEY

1     This is used to avoid duplicates and nulls. This will work as combination of unique and not null.

2     Primary key always attached to the parent table.

3     We can add this constraint in all three levels.

Ex:

      COLUMN LEVEL

      SQL> create table student(no number(2) primary key, name varchar(10), marks

             number(3));

      SQL> create table student(no number(2)  constraint pk primary key, name varchar(10),

             marks number(3));

     

      TABLE LEVEL

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             primary key(no));

      SQL> create table student(no number(2) , name varchar(10), marks number(3),

             constraint pk primary key(no));

      ALTER LEVEL

      SQL> alter table student add primary key(no);

      SQL> alter table student add constraint pk primary key(no);

FOREIGN KEY

1     This is used to reference the parent table primary key column which allows duplicates.

2     Foreign key always attached to the child table.

3     We can add this constraint in table and alter levels only.