Posted by

Simple Oracle Proagram



                                      CREATE THE DATABASE


Aim:

To create the database

Syntax:

SQL> create table <table name>(column name1 datatype(size), column name2 datatype(size),...............);

Source Code:

SQL> create table std(regno number(20),name char(25), mark1 number(4), mark2 number(4), mark3 number
(4), mark4 number(4), mark5 number(4), total number(5));

Output:

Table created.

SQL> desc std;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 REGNO                                              NUMBER(20)
 NAME                                                CHAR(25)
 MARK1                                              NUMBER(4)
 MARK2                                              NUMBER(4)
 MARK3                                              NUMBER(4)
 MARK4                                              NUMBER(4)
 MARK5                                              NUMBER(4)
 TOTAL                                               NUMBER(5)



                   ALTER THE DATABASE(MODIFY AND ADD)



Aim:

 To alter the database for modify.

Syntax:

SQL>alter table <table name> modify ( column name1 datatype(size), column name2 datatype(size),........);

SQL>alter table <table name> add ( column name1 datatype(size), column name2 datatype(size),........);

Source Code:

SQL> alter table std modify(regno varchar(7));

SQL> alter table std add(address varchar2(40));


Output:

Table altered.

SQL> desc std;


 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 REGNO                                              VARCHAR2(7)
 NAME                                               CHAR(25)
 MARK1                                              NUMBER(4)
 MARK2                                              NUMBER(4)
 MARK3                                              NUMBER(4)
 MARK4                                              NUMBER(4)
 MARK5                                              NUMBER(4)
 TOTAL                                              NUMBER(5)

Table altered.

SQL> desc std;

 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------
 REGNO                                              VARCHAR2(7)
 NAME                                                CHAR(25)
 MARK1                                              NUMBER(4)
 MARK2                                              NUMBER(4)
 MARK3                                              NUMBER(4)
 MARK4                                              NUMBER(4)
 MARK5                                              NUMBER(4)
 TOTAL                                               NUMBER(5)
 ADDRESS                                          VARCHAR2(40)


 

                                       DROP THE DATABASE




Aim:

To create the database for the purpose of drops the table in a database.

Syntax:

SQL>drop table <tablename>;


Source code:

SQL> drop table std;

Output:

Table dropped.

                                      INSERT INTO THE DATABASE


Aim:

To create the database to insert the values into the database.

Syntax:

SQL> insert into std values(&col.name1,&col.name2,........);


Source code:


SQL> insert into std values(&regno,'&name',&mark1,&mark2,&mark3,&mark4,&mark5,&total,'&address');

Output:

Enter value for regno: 09524
Enter value for name: Shiva.M
Enter value for mark1: 95
Enter value for mark2: 95
Enter value for mark3: 95
Enter value for mark4: 96
Enter value for mark5: 100
Enter value for total: 481
Enter value for address: 29/1,periyarnagar,puliyur.
old   1: insert into std values(&regno,'&name',&mark1,&mark2,&mark3,&mark4,&mark5,&total,'&address')
new   1: insert into std values(09524,'Shiva.M',95,95,95,96,100,481,'29/1,periyarnagar,puliyur.')

1 row created.

SQL> /
Enter value for regno: 09525
Enter value for name: Shankar.M
Enter value for mark1: 96
Enter value for mark2: 96
Enter value for mark3: 96
Enter value for mark4: 95
Enter value for mark5: 100
Enter value for total: 477
Enter value for address: 29/1,PeriyarNagar,Puliyur.
old   1: insert into std values(&regno,'&name',&mark1,&mark2,&mark3,&mark4,&mark5,&total,'&address')
new   1: insert into std values(09525,'Shankar.M',96,96,96,95,100,477,'29/1,PeriyarNagar,Puliyur.')

1 row created.

SQL> /
Enter value for regno: 09526
Enter value for name: Amrin.S
Enter value for mark1: 97
Enter value for mark2: 80
Enter value for mark3: 95
Enter value for mark4: 90
Enter value for mark5: 95
Enter value for total: 457
Enter value for address: periyakulathupalayam,vengamedu.
old   1: insert into std values(&regno,'&name',&mark1,&mark2,&mark3,&mark4,&mark5,&total,'&address')
new   1: insert into std values(09526,'Amrin.S',97,80,95,90,95,457,'periyakulathupalayam,vengamedu.'

1 row created.


SQL> /
Enter value for regno: 09527
Enter value for name: Karthika.S
Enter value for mark1: 90
Enter value for mark2: 90
Enter value for mark3: 90
Enter value for mark4: 90
Enter value for mark5: 90
Enter value for total: 450
Enter value for address: patrainearwatertank,Vengamedu.
old   1: insert into std values(&regno,'&name',&mark1,&mark2,&mark3,&mark4,&mark5,&total,'&address')
new   1: insert into std values(09527,'Karthika.S',90,90,90,90,90,450,'patrainearwatertank,Vengamedu

1 row created.

SQL> /
Enter value for regno: 09528
Enter value for name: Dhivya.M
Enter value for mark1: 95
Enter value for mark2: 95
Enter value for mark3: 95
Enter value for mark4: 95
Enter value for mark5: 95
Enter value for total: 475
Enter value for address: ElephantGodownnearwatertank,Vengamedu.
old   1: insert into std values(&regno,'&name',&mark1,&mark2,&mark3,&mark4,&mark5,&total,'&address')
new   1: insert into std values(09528,'Dhivya.M',95,95,95,95,95,475,'ElephantGodownnearwatertank,Ven

1 row created.

SQL> /
Enter value for regno: 09529
Enter value for name: Sankari.M
Enter value for mark1: 80
Enter value for mark2: 80
Enter value for mark3: 80
Enter value for mark4: 80
Enter value for mark5: 80
Enter value for total: 400
Enter value for address: Muttusandhu,Thanthonimalai.
old   1: insert into std values(&regno,'&name',&mark1,&mark2,&mark3,&mark4,&mark5,&total,'&address')
new   1: insert into std values(09529,'Sankar.M',80,80,80,80,80,400,'Muttusandhu,Thanthonimalai.')

1 row created.














ExNo:                                       SELECT THE DATABSE



Aim:

To select the database.

Syntax:

SQL>select *from <table name>;

Source Code:

SQL> select *from std;

Output:


REGNO   NAME            MARK1      MARK2      MARK3      MARK4
------- ------------------------- ----------  ----------      ----------     ----------
     MARK5    TOTAL   ADDRESS
---------- ---------- ----------------------------------------
9524    Shiva.M                 95                95               95                96
       100        481           29/1,periyarnagar,puliyur.

9525    Shankar.M            96                 96              96                 95
       100        477           29/1,PeriyarNagar,Puliyur.

9526    Amrin.S                97                 80              95                 90
        95        457            periyakulathupalayam,vengamedu.


9527    Karthika.S           90                 90               90                 90
        90        450            patrainearwatertank,Vengamedu.

9528    Dhivya.M            95                95               95                  95
        95        475           ElephantGodownnearwatertank,Vengamedu.

9529    Sankari.M           80                 80              80                   80
        80        400        Muttusandhu,Thanthonimalai.





ExNo:                                      DELTE THE DATABASE



Aim:

            To delete the particular row in the given database.

Syntax:

SQL> delete from <table name> where (condition);

Source Code:


SQL> delete from std where regno=9528;


Output:

1 row deleted.

SQL> select *from std;

REGNO   NAME              MARK1      MARK2      MARK3      MARK4
------- ------------------------- ---------- ---------- ---------- ---------------------
     MARK5      TOTAL  ADDRESS
---------- --------------------------------------------------
9524    Shiva.M                 95                 95                 95               96
       100        481            29/1,periyarnagar,puliyur.

9525    Shankar.M             96                96                  96               95
       100        477           29/1,PeriyarNagar,Puliyur.

9526    Amrin.S                 97               80                  95               90
        95        457           periyakulathupalayam,vengamedu.


9527    Karthika.S            90                90                   90              90
        90        450            patrainearwatertank,Vengamedu.

9529    Sankar.M             80                80                    80               80
        80        400          Muttusandhu,Thanthonimalai.



ExNo:                          KEY CONSTRAINTS UNIQUE



Aim:

            To create the database to use the key constraint unique.

Syntax:

SQL>create table <table name>(col.name1 datatype(size) unique, col.name2 datatype(size));

Source code:


SQL> create table dent (regno number(10) unique,name char(25), vb number(3), DM number(3), DBMS number(3));


Output:


Table created.

SQL> insert into dent values(&regno,'&name',&vb,&DM,&DBMS);

SQL> /
Enter value for regno: 09524
Enter value for name: Shiva.M
Enter value for vb: 90
Enter value for dm: 90
Enter value for dbms: 95
old   1: insert into dent values(&regno,'&name',&vb,&DM,&DBMS)
new   1: insert into dent values(09524,'Shiva.M',90,90,95)

1 row created.

SQL> /
Enter value for regno: 09525
Enter value for name: Shankar.M
Enter value for vb: 95
Enter value for dm: 95
Enter value for dbms: 95
old   1: insert into dent values(&regno,'&name',&vb,&DM,&DBMS)
new   1: insert into dent values(09525,'Shankar.M',95,95,95)

1 row created.

SQL> /
Enter value for regno: 09526
Enter value for name: Suren.M
Enter value for vb: 100
Enter value for dm: 100
Enter value for dbms: 100
old   1: insert into dent values(&regno,'&name',&vb,&DM,&DBMS)
new   1: insert into dent values(09526,'Suren.M',100,100,100)

1 row created.

SQL> /
Enter value for regno: 09527
Enter value for name: Hirthik
Enter value for vb: 96
Enter value for dm: 97
Enter value for dbms: 96
old   1: insert into dent values(&regno,'&name',&vb,&DM,&DBMS)
new   1: insert into dent values(09527,'Hirthik',96,97,96)

1 row created.

SQL> /
Enter value for regno: 09526
Enter value for name: renkar
Enter value for vb: 96
Enter value for dm: 97
Enter value for dbms: 95
old   1: insert into dent values(&regno,'&name',&vb,&DM,&DBMS)
new   1: insert into dent values(09526,'renkar',96,97,95)
insert into dent values(09526,'renkar',96,97,95)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C002726) violated









ExNo:                         KEY CONSTRAINTS PRIMARY KEY



Aim:

To create the database using key constraints primary key.

Syntax:

SQL>create table <table name>(col.name1 datatype(size) primary key, col.name2 datatype(size));


Source code:


SQL> create table staff1(sno number(6) primary key, name char(15),Designation char(14), salary numbe
r(6));


Output:


Table created.

       SNO     NAME            DESIGNATION        SALARY
       ---------- ---------------        --------------                  ----------
       22560  Shiva.M          GeneralManager      50000
       22561  Shankar.M      Manager                  40000
       22562  Suren.M          Editor                       55000














ExNo:                                      CONSTRAINT CHECK LIST




Aim:

To create the database using key constraints primary key.

Syntax:

SQL>create table <table name>(col.name1 datatype(size), col.name2 datatype(size).....constraint checklist check(condition));


Source code:


SQL> create table emp3 (eno number(6), ename char(15),Designation char(14), salary number(7) constraint checklist check(salary<50000));

Output:

    EMPNO EMPNAME      DESIGNATION       SALARY
     ---------    ------------           -------------                  ----------
     2315     shiva               manager                   49999
     2316     shankar          accountant                12999
     2317     shivren           clerk                                 35000
     2318     renkar            auditor                      40000
     2319     suren              editor                        45000
















ExNo:                                         AGGREGATE FUNCTION




Aim:

            To select the database using aggregate function.

Syntax:

Sum:

 SQL>Select sum(col.name) from <table name>;

Avg:

SQL>Select avg(col.name) from <table name>;

Min:

SQL>Select min(col.name) from <table name>;

Max:

SQL>Select max(col.name) from <table name>;

Count:

SQL>Select count(col.name) from <table name>;

Source code:

Sum:

 SQL>Select sum(Salary) from emp;

Avg:

SQL>Select avg(Salary) from emp;

Min:

SQL>Select min(Salary) from emp;
Max:

SQL>Select max(Salary) from emp;

Count:

SQL>Select count(Salary) from emp;

Output:


SUM(SALARY)
-----------
     182998

AVG(SALARY)
-----------
    36599.6

MIN(SALARY)
-----------
      12999



MAX(SALARY)
-----------
      49999

COUNT(EMPNO)
------------
           5

















ExNo:                                        CHARACTER FUNCTIONS



Aim:

            To select the database using character function.

Syntax:

INITCAP:

SQL> select initcap(‘string’) from dual;

LOWER AND UPPER CASE:

SQL> select lower(‘string’) from dual;
           
SQL> select upper(‘string’) from dual;

LTRIM AND RTRIM:

SQL> select ltrim (‘string’) from dual;

SQL> select rtrim (‘string’) from dual;


TRANSALATE AND REPLACE:

SQL> select translate (‘string’, char 1, char 2) from dual;

SQL> select replace (‘string’, char 1, char 2) from dual;

SUBSTRING:

SQL> select substring(‘string’, position, length) from dual;

SIN:

SQL> select sin(value) from dual;

SQL> select sinh(value) from dual;


COS:

SQL> select cos(value) from dual;
           
SQL> select cosh(value) from dual;

TAN:

SQL> select tan(value) from dual;

SQL> select tanh(value) from dual;

LOGVALUES:

SQL> select log(value,basevalue) from dual;

EXPONENTIAL:

SQL> select exp(size) from dual;

POWER:

SQL> select power(value) from dual;

ROUND:

SQL> select round(value) from dual;

TRUNC

SQL> select trunc(value) from dual;

Source code:

INITCAP:

SQL> select initcap(‘government arts college’) from dual;

LOWER AND UPPER CASE:

SQL> select lower(‘SHIVREN’) from dual;

SQL> select upper(‘shivren’) from dual;




LTRIM AND RTRIM:

SQL> select ltrim (‘sshivren’) from dual;

SQL> select rtrim (‘shivrenk’) from dual;



TRANSALATE AND REPLACE:

SQL> select translate (‘string’, s, e) from dual;

SQL> select replace (‘string’, s, e) from dual;

SUBSTRING:

SQL> select substring(‘shivren’, 2, 4) from dual;

SIN:

SQL> select sin(30) from dual;

SQL> select sinh(30) from dual;

COS:

SQL> select cos(30) from dual;

SQL> select cosh(30) from dual;

TAN:

SQL> select tan(30) from dual;

SQL> select tanh(30) from dual;

LOGVALUES:

SQL> select log(10,2) from dual;

EXPONENTIAL:

SQL> select exp(5) from dual;




POWER:

SQL> select power(4,4) from dual;

ROUND:

SQL> select round(3.5167) from dual;



TRUNC

SQL> select trunc(3.1567) from dual;

Output:

INITCAP
-----------------------
Government Arts College

LOWER
-------
shivren

UPPER
-------
SHIVREN

LTRIM
------
hivren

RTRIM
-------
shivren

TRANSLATE
-------
etring

REPLACE
-------
etring

SUBSTR
-------
hivr

SIN(30)
-------
-.98803162

SINH(30)
-------
5.3432E+12
COS(30)
-------
.15425145

COSH(30)
-------
5.3432E+12

TAN(30)
-------
-6.4053312

TANH(30)
-------
1

LOG(10,2)
-------
.301029996

EXP(5)
-------
148.413159

POWER(4,4)
-------
256

ROUND(3.5167)
-------
4

TRUNC(3.1567)
-------
3

ExNo:                                        DATE FUNCTION




Aim

To create a database using date function.

Syntax

SQL> create table <tablename>(Col name1 datatype(size),..........,startdate date,enddate date);

Source Code

SQL> create table shiv(pno number(5), name char(15),price number(7,2), startdate date, enddate );

SQL> insert into shiv values (&pno, '&name', &price, '&startdate', '&enddate');

SQL> select *from shiv;

SQL>  select enddate,add_months (enddate,4)from shiv;

SQL> select enddate,last_day(enddate)from shiv;

SQL> Select trunc(startdate,'year')from shiv;
           
            SQL> select round(startdate,'month')from shiv;

            SQL> select sysdate from dual;


Output
Table created.

SQL> desc shiv;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PNO                                                NUMBER(5)
 NAME                                             CHAR(15)
 PRICE                                             NUMBER(7,2)
 STARTDATE                                    DATE
 ENDDATE                                        DATE

 PNO NAME                 PRICE  STARTDATE  ENDDATE
 -------- -----------                ---------- ------------------  ----------------
 524  wheat                   47.75   17-FEB-01    17-JAN-12
 525  Sugar                   67.5     16-JAN-00     15-JAN-12
 526  Rice                      20.5     14-MAR-02   15-FEB-12
 527 Badam                   500.5   18-AUG-05    14-APR-12

ENDDATE   ADD_MONTH
----------------    -------------------
17-JAN-12   17-MAY-12
15-JAN-12   15-MAY-12
15-FEB-12  15-JUN-12
14-APR-12  14-AUG-12


ENDDATE   LAST_DAY
----------------   ---------------
17-JAN-12   31-JAN-12
15-JAN-12   31-JAN-12
15-FEB-12  29-FEB-12
14-APR-12  30-APR-12


TRUNC(STA
------------------
01-JAN-01
01-JAN-00
01-JAN-02
01-JAN-05


ROUND(STA
-------------------
01-MAR-01
01-FEB-00
01-MAR-02
01-SEP-05

SYSDATE
---------------
01-FEB-12





ExNo:                                           FOREIGN KEY



AIM:

To create the database

Syntax:

SQL> create table <table name>(col.name1 datatype(size) primary key, col.name2 datatype(size));

SQL> create table <table name>(col.name1 datatype(size), col.name2 datatype(size) constraint fk referrences filename(col.name));


Source code:

SQL> create table department(dno number(5) primary key, dname char(15), location varchar(18));.

SQL> create table emp12(eno number(6), ename char(15), designation char(14), salary number(7,2), dno number(5) constraints fk references department(dno));



Output:


Table created.

SQL> insert into department values(&dno,'&dname','&location');

Enter value for dno: 420
Enter value for dname: com.science
Enter value for location: main block
old   1: insert into department values(&dno,'&dname','&location')
new   1: insert into department values(420,'com.science','main block')

1 row created.

SQL> /
Enter value for dno: 421
Enter value for dname: physics
Enter value for location: block A
old   1: insert into department values(&dno,'&dname','&location')
new   1: insert into department values(421,'physics','block A')

1 row created.

SQL> /
Enter value for dno: 422
Enter value for dname: chemistry
Enter value for location: block B
old   1: insert into department values(&dno,'&dname','&location')
new   1: insert into department values(422,'chemistry','block B')

1 row created.

SQL> /
Enter value for dno: 423
Enter value for dname: maths
Enter value for location: block C
old   1: insert into department values(&dno,'&dname','&location')
new   1: insert into department values(423,'maths','block C')

1 row created.

SQL> insert into emp12 values(&eno,'&ename','&designation',&salary,&dno);
Enter value for eno: 121
Enter value for ename: shiva
Enter value for designation: ass.lecture
Enter value for salary: 30000
Enter value for dno: 420
old   1: insert into emp12 values(&eno,'&ename','&designation',&salary,&dno)
new   1: insert into emp12 values(121,'shiva','ass.lecture',30000,420)

1 row created.

SQL> /
Enter value for eno: 122
Enter value for ename: shankar
Enter value for designation: ass.lecture
Enter value for salary: 30000
Enter value for dno: 421
old   1: insert into emp12 values(&eno,'&ename','&designation',&salary,&dno)
new   1: insert into emp12 values(122,'shankar','ass.lecture',30000,421)

1 row created.

SQL> /
Enter value for eno: 123
Enter value for ename: shivren
Enter value for designation: ass.lecture
Enter value for salary: 30000
Enter value for dno: 422
old   1: insert into emp12 values(&eno,'&ename','&designation',&salary,&dno)
new   1: insert into emp12 values(123,'shivren','ass.lecture',30000,422)

1 row created.

SQL> /
Enter value for eno: 125
Enter value for ename: suren
Enter value for designation: lecture
Enter value for salary: 35000
Enter value for dno: 424
old   1: insert into emp12 values(&eno,'&ename','&designation',&salary,&dno)
new   1: insert into emp12 values(125,'suren','lecture',35000,424)
insert into emp12 values(125,'suren','lecture',35000,424)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK) violated - parent key not found



ENO ENAME           DESIGNATION    SALARY DNO
---------- -------------------------------------------------------------
121   shiva               ass.lecture                30000        420
122   shankar          ass.lecture                 30000        421
123   shivren           ass.lecture                 30000        422
124   renkar             lecture                      35000        423















ExNo:                PL/SQL PROGRAM FOR CONTROL STRUCTURE




Aim:

            To create the PL/SQL program for control structure.

Source code:

SQL>   declare
  2  no number;
  3  begin
  4  no:=&no;
  5  if no mod 2=0 then
  6  dbms_output.put_line(no ||' is even');
  7  else
  8  dbms_output.put_line(no ||' is an odd');
  9  end if;
 10* end;
 11 /

Output:

Enter value for no: 210
old   4: no:=&no;
new   4: no:=210;
210 is even

PL/SQL procedure successfully completed.















ExNo:                                      PL/SQL PROGRAMS FOR ADD TWO NUMBERS



Aim:

            To create the PL/SQL program for add two numbers.

Source code:

SQL> declare
  2  a number;
  3  b number;
  4  c number;
  5  begin
  6  a:=&a;
  7  b:=&b;
  8  c:=a+b;
  9  dbms_output.put_line(c);
 10  end;
 11  /
Enter value for a: 30
old   6: a:=&a;
new   6: a:=30;
Enter value for b: 20
old   7: b:=&b;
new   7: b:=20;

PL/SQL procedure successfully completed.

SQL> save aa.sql
SP2-0540: File "aa.sql" already exists.
Use "SAVE filename[.ext] REPLACE".
SQL> save a.sql
Created file a.sql
SQL> edit a.squl

SQL> edita.sql
SP2-0042: unknown command "edita.sql" - rest of line ignored.
SQL> edit a.sql

SQL> set serveroutput on;
SQL> run a.sql
  1  declare
  2  a number;
  3  b number;
  4  c number;
  5  begin
  6  a:=&a;
  7  b:=&b;
  8  c:=a+b;
  9  dbms_output.put_line(c);
 10* end;

Output:

Enter value for a: 30
old   6: a:=&a;
new   6: a:=30;
Enter value for b: 20
old   7: b:=&b;
new   7: b:=20;
50

PL/SQL procedure successfully completed.



























ExNo:                            PL/SQL PROGRAM FOR SUBTRACT
 TWO NUMBERS



Aim:

            To create the PL/SQL program to subtract two numbers.

Source code:


SQL>   declare
  2  a number;
  3  b number;
  4  c number;
  5  begin
  6  a:=&a;
  7  b:=&b;
  8  c:=a-b;
  9  dbms_output.put_line(c);
 10* end;
SQL> /

Output:

Enter value for a: 320
old   6: a:=&a;
new   6: a:=320;
Enter value for b: 120
old   7: b:=&b;
new   7: b:=120;
200

PL/SQL procedure successfully completed.











ExNo:                            PL/SQL PROGRAM FOR MULTIPLY
TWO NUMBERS




Aim:

To create the PL/SQL program to subtract two numbers.

Source code:


SQL> declare
  2  a number;
  3  b number;
  4  c number;
  5  begin
  6  a:=&a;
  7  b:=&b;
  8  c:=a*b;
  9  dbms_output.put_line(c);
 10  end;
 11  /


Output:

Enter value for a: 20
old   6: a:=&a;
new   6: a:=20;
Enter value for b: 2
old   7: b:=&b;
new   7: b:=2;
40

PL/SQL procedure successfully completed.









ExNo:                                  PL/SQL PROGRAM FOR DIVIDE
TWO NUMBERS



Aim:

To create the PL/SQL program to subtract two numbers.

Source code:

SQL>  declare
  2  a number;
  3  b number;
  4  c number;
  5  begin
  6  a:=&a;
  7  b:=&b;
  8  c:=a/b;
  9  dbms_output.put_line(c);
 10* end;
 11/

Output:

Enter value for a: 10
old   6: a:=&a;
new   6: a:=10;
Enter value for b: 2
old   7: b:=&b;
new   7: b:=2;
5

PL/SQL procedure successfully completed.

     











ExNo:                              UPDATE THE DATABASE



Aim:
                       
            To update the database.

Syntax:

Update <table name> set <operation>

Update <table name> set <operation>           where <condion>

Source code:

SQL> update stud1 set tot=m1+m2+m3;


Output:

5 rows updated.

SNO     SNAME                   M1         M2      M3        TOT
---------- ---------------                ---------- ---------- ---------- ----------
524       shiva                       99         80          80        259
525       shankar                   90         80         75        245
526       shivren                    85         85         90        260
527       renkar                     80         90         78        248
528v     suren                       85         95        100       280















ExNo:                                                       NOT NULL



Aim:
                       
            To create the database use not null.

Syntax:

SQL> create table <table name>(col.name1 datatype(size) Not Null, col.name2 datatype(size) .....);


Source code:

SQL> create table emp111(empno number(7) notnull, name char(15), designation varchar(10), salary num
ber(7));


Output:


EMPNO NAME        DESIGNATION     SALARY
----------   ---------------   --------------------        ----------
566        shiva          manager               25000
567        shankar     clerk                      15000
568        shivren      auditor                  30000
569        renkar       accountant            10000
















ExNo                                           CURSOR




Aim:

            To create a cursor program using PL/SQL.

Source code:



SQL> declare
  2  salary emp.sal % type;
  3  begin
  4  select sal into salary from emp where empno=7900;
  5  if SQL % Found then
  6  DBMS_OUTPUT.PUT_LINE('salary='||salary);
  7  else
  8  DBMS_OUTPUT.PUT_LINE('salary does not exist');
  9  end if;
 10  end;
 11  /

salary= 1250;

PL/SQL procedure successfully completed.




















ExNo:                                       TRIGGER




Aim

            To create a trigger program using PL/SQL.


Source code:


SAMPLE   TABLE   TO  BE  TRIGGERED:

SQL> CREATE TABLE PERSON(
  2  ID INT,
  3  NAME VARCHAR(30),
  4  DOB DATE,
  5  PRIMARY KEY(ID)
  6  );

BEFORE   INSERT   TRIGGER:

SQL> CREATE OR REPLACE
  2  TRIGGER PERSON_INSERT_BEFORE
  3  BEFORE
  4  INSERT
  5  ON PERSON
  6  FOR EACH ROW
  7  BEGIN
  8  DBMS_OUTPUT.PUT_LINE('BEFORE INSERT OF' || :NEW.NAME);
  9  END;

INSERT   THE   VALUES:


SQL> INSERT INTO PERSON(ID,NAME,DOB) VALUES (1,'AMRIN',SYSDATE);
BEFORE INSERT OFAMRIN

SQL> INSERT INTO PERSON (ID,NAME,DOB) VALUES (2,'AMRINTHAJ',SYSDATE);
BEFORE INSERT OFAMRINTHAJ
AFTER INSERT OFAMRINTHAJ





BEFORE   UPDATE   STATEMENT   TRIGGER:


SQL> CREATE OR REPLACE
  2  TRIGGER PERSON_UPDATE_S_BEFORE
  3  BEFORE UPDATE
  4  ON PERSON
  5  BEGIN
  6  DBMS_OUTPUT.PUT_LINE('BEFORE UPDATEING SOME PERSON(S)');
  7  END;
  8  /

SQL> UPDATE PERSON SET DOB=SYSDATE;
BEFORE UPDATEING SOME PERSON(S)


OUTPUT

Table created.

SQL> DESC PERSON;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 NAME                                               VARCHAR2(30)
 DOB                                                DATE

 
Trigger created.



SQL> SELECT * FROM PERSON;

        ID   NAME                                  DOB
---------- ------------------------------       ---------
         1   AMRIN                                 01-MAR-12
         2   AMRINTHAJ                       01-MAR-12


Trigger created.

Comments

Post a Comment

Popular Posts