RDBMS Program


ORACLE

                         It is a very large and multi-user database management system. Oracle is a relational 

database management system developed by 'Oracle Corporation'.Oracle works to efficiently manage 

its resource, a database of information, among the multiple clients requesting and sending data in

the network.It is an excellent database server choice for client/server computing. Oracle supports all 

major operating systems for both clients and servers,including MSDOS, NetWare, UnixWare, OS/2 

and most UNIX flavors.

RDBMS
RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and
for all modern database systems like
·         MS SQL Server
·         IBM DB2
·         Oracle
·         MySQL
·         Microsoft Access.
SQL


SQL is Structured Query Language, which is a computer language for storing,

manipulating and retrieving data stored in relational database.SQL is the standard language

for Relation Database System. All relational database management systems like MySQL,

MS Access, Oracle, and SQL Server use SQL as standard database language.

MS SQL Server using T-SQL,

Oracle using PL/SQL,

MS Access version of SQL is called JET SQL (native format) etc.

NULL VALUE


                   A NULL value in a table is a value in a field that appears to be blank, which means a field

with a NULL value is a field with no value.

TABLE
                   The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows.

FIELD
           
                     Every table is broken up into smaller entities called fields. The fields in the

CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY.

RECORD OR ROW
            A record, also called a row of data, is each individual entry that exists in a table.   

COLUMN
            A column is a vertical entity in a table that contains all information associated with a
specific field in a table.          

SQL commands to interact with relational databases are
Ø  CREATE
Ø  SELECT
Ø  INSERT
Ø  UPDATE
Ø  DELETE
Ø  DROP

DDL - Data Definition Language

CREATE

            Creates a new table, a view of a table, or other object in database.

ALTER

            Modifies an existing database object, such as a table.

DROP

            Deletes an entire table, a view of a table or other object in the database.

DML - Data Manipulation Language

SELECT

            Retrieves certain records from one or more tables.

INSERT

            Creates a record.

UPDATE

            Modifies records.

DELETE

            Deletes records.

DCL - Data Control Language

GRANT

            Gives a privilege to user

REVOKE

            Takes back privileges granted from user

EX.NO:1                                BASIC SQL QUERIES

AIM
          To implement the Basic SQL queries.

Create a Table

The SQL CREATE TABLE statement is used to create new SQL database.

DATE CONSTRAINTS

NOT NULL constraint

Ensures that a column cannot have NULL value.

DEFAULT Constraint

Provides a default value for a column when none is specified.

UNIQUE Constraint

Ensures that all values in a column are different.

 PRIMARY Key

Uniquely identified each rows/records in a database table.

Uniquely identified a rows/records in any another database table.

                        The CHECK constraint ensures that all values in a column satisfy certain
conditions.

CREATE A TABLE

SYNTAX
CREATE TABLE table_name(column1 datatype,column2 datatype,column3

datatype,..... columnN datatype  PRIMARY KEY( one or more columns ));


SQL > CREATE TABLE STUDENT (ROLL_NO VARCHAR2(10),NAME

VARCHAR2(20),CONTACT_NO NUMBER(10),MARK1 INT,

MARK2 INT,TOTAL);
Table created.

INSERT A DATA INTO TABLE
The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.

SYNTAX
            INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

SQL > INSERT INTO STUDENT VALUES('15us01','Bhuvana Devi',1234567890,90,89,0);

SQL > INSERT INTO STUDENT VALUES('15us02','Jeyalakshmi',9524786714,89,78,0);

SQL > INSERT INTO STUDENT VALUES('15us03','Vijayalakshmi',9047728900,67,98,0);

SQL > INSERT INTO STUDENT VALUES('15us15',Arun Kumar',9787232589,98,90,0);

SQL > INSERT INTO STUDENT VALUES('15us16','Bala',9866523400,89,78,0);

SELECT STATEMENT

SQL SELECT statement is used to fetch the data from a database table which returns
data in the form of result table

SYNTAX

            SELECT column1, column2, columnN FROM table_name;

SQL> SELECT *FROM STUDENT;

FROM
                        Fetch all the Fields.

ROLLNO         NAME  CONTACTNO  MARK1  MARK2  TOTAL
-------------- --------------- ------------------ ------------- ------------ ----------
15us01   Bhuvana devi       1234567890              90             89           0

15us02      Jeyalakshmi       9524786714              89            78           0

15us03   Vijayalakshmi      9047728900               67            98           0

15us15      Arun Kumar      9787232589               98            90           0

15us16                  Bala       9866523400              89             78           0

Update QUERY

            The SQL UPDATE Query is used to modify the existing records in a table.

WHERE

            WHERE clause with UPDATE query to update selected rows otherwise all the rows
would be affected.

SYNTAX

UPDATE table_name
 
SET column1 = value1, column2 = value2...., columnN = valueN
 
WHERE [condition];

SQL >UPDATE STUDENT SET NAME='Bhuvana Devi' WHERE Roll_no='15us01';

SQL >UPDATE STUDENT SET TOTAL=Mark1+Mark2;

ROLLNO         NAME  CONTACTNO  MARK1  MARK2  TOTAL
-------------- --------------- ------------------ ------------ ----------- -----------
15us01    Bhuvana Devi      1234567890            90            89        179

15us02        Jeyalakshmi      9524786714            89           78        167

15us03    Vijayalakshmi      9047728900            67            98        165

15us15       Arun Kumar      9787232590            98            90        188

15us16                   Bala       9866523400            89           78         167


ALTER TABLE

The SQL ALTER TABLE command is used to add, delete or modify
columns in an existing table.

SYNTAX

               ALTER TABLE table_name ADD column_name datatype;

Alter table(Add Column GRADE)

SQL> ALTER TABLE STUDENT ADD GRADE varchar2(5);

ROLLNO          NAME  CONTACTNO  MARK1 MARK2 TOTAL GRADE
------------- ---------------- -------------------  ----------- ----------- ---------- ----------
15us01   Bhuvana Devi       1234567890             90         89        179

15us02       Jeyalakshmi       9524786714             89         78        167

15us15      Arun Kumar       9787232590             98         90        188

15us16                   Bala       9866523400             89         78        167

15us03   Vijayalakshmi       9047728900             67         98        165

Update (GRADE)

SQL > UPDATE STUDENT SET GRADE='A+' WHERE TOTAL>180;

SQL > UPDATE STUDENT SET GRADE='A' WHERE TOTAL>170;

SQL > UPDATE STUDENT SET GRADE='B' WHERE TOTAL>160;

ROLLNO         NAME   CONTACTNO   MARK1  MARK2  TOTAL GRADE
------------- ---------------- -------------------- ------------ ----------- ---------- ------------
15us01     Bhuvana Devi       1234567890            90           89          179             A+

15us02        Jeyalakshmi        9524786714           89           78          167             B

15us15       Arun Kumar        9787232590           98           90           188            A

15us16                    Bala        9866523400           89           78           167            B

15us03     Vijayalakshmi       9047728900           67           98           165            B


Delete QUERY

The SQL DELETE Query is used to delete the existing records from a table.

WHERE clause with DELETE query to delete selected rows, otherwise all the records

would be deleted.
SYNTAX
                    DELETE FROM table_name
 
               WHERE [condition];

DELETE THE SPECIFIC DATA

SQL > DELETE FROM STUDENT  WHERE NAME='Vijayalakshmi';

ROLLNO          NAME     CONTACTNO  MARK1  MARK2  TOTAL GRADE
------------- ----------------- --------------------- ----------- ------------ ---------- -----------
15us01      Bhuvana Devi    1234567890                90             89        179         A+

15us02          Jeyalakshmi    9524786714                89            78        167         B

15us15         Arun Kumar    9787232590                98             90        188         A

15us16                      Bala    9866523400                89             78        167         B

1 row deleted.

DROP TABLE

                   The SQL DROP TABLE statement is used to remove a table definition and
all data, indexes, triggers, constraints, and permission specifications for that table.

SYNTAX
            DROP TABLE table_name;
 
DROP FOR SPECIFIC COLUMN

SYNTAX
          DROP TABLE TABLE_NAME DROP column column-name;

Drop column(contactno)

SQL > ALTER TABLE STUDENT DROP COLUMN CONTACT_NO;

ROLLNO              NAME   MARK1  MARK2  TOTAL GRADE
-------------- ------------------ ------------ ------------ ---------- -----------
15us01       Bhuvana Devi               90             89        179        A+

15us02           Jeyalakshmi              89              78        167       B

15us15          Arun Kumar              98              90        188        A

15us16                       Bala              89              78        167        B


EX.NO:2                    TO IMPLEMENT THE VARIOUS LEVEL OF
SELECT QUERIES
AIM

          To implement the various level of SELECT queries.

SELECT QUERY

          SQL SELECT statement is used to fetch the data from a database table which returns
data in the form of result table.

SYNTAX
            SELECT column1, column2, columnN FROM table_name;
 
SPECIFIC FIELD DISPLAY
          
            In which fetch the specific field to display.

Create a table

SQL> CREATE TABLE STUDENT_DET(ROLL_NO VARCHAR2(8) NOT NULL

           ,NAME VARCHAR2(20),GENDER VARCHAR2(2),

ELECTIVE_PAPER VARCHAR2(20), EPCODE VARCHAR2(10) NOT NULL,

MARK1 INT,MARK2 INT,AVG NUMBER(3),GRADE VARCHAR2(4),

PRIMARY KEY(ROLL_NO),UNIQUE(NAME));

Table is created.

INSERT A DATA INTO TABLE

SQL>INSERT INTO STUDENT_DET  VALUES('&ROLL_NO','&NAME','&GENDER',

         '&ELECTIVE_PAPER','&ELECTIVE_CODE',&MARK1,&MARK2,&AVG,

          &GRADE);

Enter the Roll_no                                :15us01                                     

Enter the Name                                   :Bhuvana Devi

Enter the Gender                                :F

Enter the Department                         :CS

Enter the Elective_paper                     :Maths

Enter the Epcode                                :EMn01

Enter the Mark1                                  :90

Enter the Mark2                                  :85        

Enter the Avg                                     :0

Enter the Grade                                  :A+

UPDATE A DATA

SQL >UPDATE STUDENT_DET SET AVG=(MARK1+MARK2)/2;

SQL >UPDATE STUDENT_DET SET GRADE='A' WHERE AVG>90;

SQL > SELECT *FROM STUDENT_DET;
ROLLNO       NAME              G         DEP    ELE_PAPER EPCODE MARK1 MARK2 AVG GRD
------------- ------------- ---- ------------ ------------------- ----------- ---------- ---------- ------- ------
15us01  Bhuvana Devi  F               Cs           Maths       EMn01          90          85        88    A+

15us02      Jeyalakshmi F               Cs           Maths       EMn01           89          90        90     A

15us20                Arun  M              Cs            Maths      EMn01           87          67        77     B

15us21                 Bala  M              Cs            Maths      EMn01          98          67        83     A+



(i) To display the specific field.

SQL > SELECT ROLL_NO,NAME,GENDER FROM STUDENT_DET;

ROLLNO            NAME      G
----------- ------------------- ------
15us01     Bhuvana Devi       F

15us02       Jeyalakshmi         F

15us20                  Arun         M

15us21                   Bala         M

15ma01                  kala          F

 (ii) To display FEMALE candidates of Electivepaper

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,GENDER FROM

STUDENT_DET WHERE GENDER='F';

ROLLNO              NAME  ELECTIVEPAPER     G
------------- ------------------- ------------------------- ------
15us01     Bhuvana Devi                            Maths      F

15us02         Jeyalakshmi                            Maths     F

15ma01                   kala                                   Cs      F

15cy01                     Abi                           Physics      F

15cy02                Anjana                           Physics      F

15py02                   Mani                       Chemistry      F

(iii) To display MALE candidate of Electivepaper.

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,GENDER FROM

STUDENT_DET WHERE  GENDER='M';

ROLLNO      NAME  ELECTIVEPAPER        G
------------ ---------------- -------------------------- -------
15us20               Arun                          Maths          M

15us21                Bala                          Maths          M

15ma03       Bharathi                                 Cs          M

15ma21         Kailash                                Cs          M

 (iv) To display COMPUTER SCIENCE Electivepaper students.

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,EPCODE FROM

STUDENT_DET WHERE EPCODE='ECS01';

ROLLNO         NAME  ELECTIVEPAPER EPCODE
------------ --------------- -------------------------- ------------
15ma01             kala                                Cs           ECs01

15ma03      Bharathi                                Cs           ECs01

15ma21        Kailash                               Cs           ECs01

(v) To display MATHEMATICS Electivepaper students.

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,EPCODE FROM

STUDENT_DET  WHERE  EPCODE='EMN01';

ROLLNO          NAME   ELECTIVEPAPER EPCODE
------------- ---------------- -------------------------- -----------
15us01   Bhuvana Devi                         Maths        EMn01

15us02      Jeyalakshmi                         Maths        EMn01

15us20                  Arun                         Maths        EMn01

15us21                   Bala                         Maths        EMn01

(vi) To display PHYSICS Electivepaper students

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,EPCODE FROM

STUDENT_DET WHERE EPCODE='EPY01';
ROLLNO       NAME  ELECTIVEPAPER   EPCODE
------------- ------------- -------------------------- --------------
15cy01                Abi                        Physics       Eph01

15cy02           Anjana                        Physics       Eph01

15cy45              Anto                        Physics       EPh01


(vii) To display CHEMISTRY Electivepaper students

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,EPCODE FROM

STUDENT_DET WHERE EPCODE='ECY01';
ROLLNO        NAME ELECTIVEPAPER EPCODE
---------- --------------- -------------------- -----------
15py02             Mani        Chemistry      ECy01

15py67             Vicky        Chemistry      Ecy01      

15py68            Vinoth       Chemistry      Ecy01  

(viii) To retrieve the data by using ELECTIVE PAPER(MATHS).

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,EPCODE FROM

STUDENT_DET WHERE ELECTIVE_PAPER='MATHS';
ROLLNO          NAME   ELECTIVEPAPER
----------- ------------------ ------------------------
15us01   Bhuvana Devi                 Maths

15us02      Jeyalakshmi                 Maths

15us20                  Arun                 Maths

15us21                  Bala                  Maths

(ix) To retrieve the data by using ELECTIVE PAPER(PHYSICS).

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,EPCODE FROM

STUDENT_DET WHERE ELECTIVE_PAPER='PHYSICS';

ROLLNO          NAME  ELECTIVEPAPER
----------- ------------------ ------------------------
15cy01                Abi                         Physics

15cy02           Anjana                         Physics

15cy45               Anto                        Physics

(x) To retrieve the data by using ELECTIVE PAPER(CHEMISTRY).

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,EPCODE FROM

STUDENT_DET WHERE ELECTIVE_PAPER='CHEMISTRY';

ROLLNO          NAME ELECTIVEPAPER
----------- --------------- --------------------------
15py02            Mani                    Chemistry

15py67           Vicky                   Chemistry

15py68          Vinoth                   Chemistry


(xi) To retrieve the data by using ELECTIVE PAPER(COMPUTER SCIENCE).

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,EPCODE FROM

STUDENT_DET WHERE ELECTIVE_PAPER='CS';   

ROLLNO       NAME  ELECTIVEPAPER
----------- --------------- --------------------------
15ma01             kala                               Cs

15ma03      Bharathi                               Cs

15ma21        Kailash                              Cs

(xi) To retrieve the specific data by using ROLLNO.

SQL > SELECT * FROM STUDENT_DET WHERE ROLL_NO='15US01';

ROLLNO         NAME   G   DEP  ELEEPAP EPCODE MARK1  MARK2  AVG GRADE
----------- -----------------  --- -------- ------------- ------------ ----------- ------------ ------- -----------
15us01   Bhuvana Devi    F       Cs        Maths    EMn01            90              85      88        A+

INSERT A DATA WITH SAME NAME

SQL > INSERT INTO STUDENT_DET VALUES('15US17','ARUN','M',

            'PHYSICS','CHEMISTRY','ECY01',89,90,78,'B');

SQL > INSERT INTO STUDENT_DET VALUES('15US18','ARUN','M','CHEMISTRY',

            'PHYSICS','EPH01',78,89,67,'B+');

SQL > INSERT INTO STUDENT_DET VALUES('15US19','ARUN','M','MATHS',

'CS','ECS01',89,78,78,'B');

(xii) To retrieve the 3 RECORDS with NAME is SAME.

SQL >SELECT * FROM STUDENT_DET WHERE NAME='ARUN';

ROLLNO   NAME  G            DEP  ELECPA  EPCODE  MARK1   MARK2  AVG GRADE
------------- ----------- --- -------------- ------------ ------------- ------------ ------------ ------- ---------
15us20           Arun   M               Cs       Maths   EMn01                87             67       77        B

15us17           Arun   M       Physics Chemistry  ECy01                 89             90       78        B

15us18           Arun   M   Chemistry     Physics   EPh01                78             89        67        B+


15us19           Arun   M         Maths             Cs    ECs01                89             78        78       B

EX.NO:3        TO IMPLEMENT THE SQL BUILT-IN FUNCTION

AIM
            To implement the SQL BUILT-IN FUNCTION in SQL.

GROUP BY

          The SQL GROUP BY clause is used in collaboration with the SELECT statement to
arrange identical data into groups

ORDER BY

          The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes
the ORDER BY clause.

SYNTAX
               SELECT column1, column2

               FROM table_name

               WHERE [ conditions ]

               GROUP BY column1, column2

               ORDER BY column1, column2

SQL FUNCTION

          SQL has many built-in functions for performing calculations on data.
                                    1.Single value function
                                    2.Group value function
                                    3.List value function

SINGLE VALUE FUNCTION

ABS(X)
                        The ABS() function returns the absolute value of X.

SYNTAX
                                                                                                SELECT ABS(Value);

CEILING(X)
These functions return the smallest integer value that is not smaller than X.

SYNTAX
SELECT CEILING(Value or X);

FLOOR(X)
This function returns the largest integer value that is not greater than X.

SYNTAX
                        SELECT FLOOR(Value or X);

SIGN(X)
This function returns the sign of X (negative, zero, or positive) as -1, 0, or 1.

SYNTAX
                   SELECT SIGN(Value or X);

SIN(X)
This function returns the sine of X.

SYNTAX
                        SELECT SIN(Value or X);

SQRT(X)
This function returns the non-negative square root of X.

SYNTAX
                        SELECT SQRT(Value or X);

 GROUP VALUE FUNCTION

COUNT(*)
                   The SQL COUNT aggregate function is used to count the number of rows in a
database table.

           SYNTAX

                        SELECT COUNT(*);
OR
                        SELECT COUNT(Column_name);

MAXIMUM(Column_name)

                   The SQL MAX aggregate function allows us to select the highest (maximum)
value for a certain column.                 

SYNTAX
            SELECT MAXIMUM(Column_name);

MINIMUM()

                        The SQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.

SYNTAX
            SELECT MINIMIUM(Column_name);

AVERAGE(Column_name)           

                        The SQL AVG aggregate function selects the average value for certain table
column.                      

SYNTAX
            SELECT AVG(Column_name);

SUM(Column_name)

                        The SQL SUM aggregate function allows selecting the total for a numeric
column.

SYNTAX
SELECT SUM(Column_name);

CONCAT(col1,col2)

                        SQL CONCAT function is used to concatenate two strings to form a single
string.

SYNTAX
            SELECT CONCAT(column_name1,column_name2);

LENGTH(X)

                       Returns the length of the string.

SYNTAX
SELECT LENGTH(Column_name);

TRUNCATE(X,D)

This function is used to return the value of X truncated to D number of
decimal places.

(i)If D is 0, then the decimal point is removed.
(ii)If D is negative, then D number of values in the integer part of the value is
truncated.

SYNTAX
SELECT TRUNC(Value,No.of decimals);

LOWER(str)

Returns the string str with all characters changed to lowercase according to the current
character set mapping.

SYNTAX
            SELECT LOWER(STR);

UPPER (str)

Returns the string str with all characters changed to lowercase according to the current
character set mapping.

SYNTAX
            SELECT UPPER(STR);

ROUND(X,D)

This function returns X rounded to the nearest integer. If a second argument,
D, is supplied, then the function returns X rounded to D decimal places. D must be positive or
all digits to the right of the decimal point will be removed.

SYNTAX
            SELECT ROUND(X or Value,D);

LIST VALUE FUNCTION

GREATEST()

The GREATEST() function returns the greatest value in the set of input
parameters (col1,col2).

SYNTAX
SELECT GREATEST(COL1,COL2);

LEAST()
                        The LEAST() function is the opposite of the GREATEST() function. Its purpose
is to return the least-valued item from the value list (col1,col2)

SYNTAX
            SELECT  LEAST(COL1,COL2);

CREATE A TABLE

SQL > CREATE TABLE EM_DET(EMP_ID INT,EMP_NAME VARCHAR2(20) NOT

NULL,DEPARTMENT VARCHAR2(10),SALARY INT,

PF_AMOUNT INT NULL,

LIC_AMOUNT INT NULL,LOAN_AMOUNT INT NULL,

TOTAL INT,UNIQUE(EMP_NAME),PRIMARY KEY(EMP_ID));        

Table is created.

INSERT A DATA INTO THE TABLE

SQL > INSERT INTO EMP_DET VALUES(&EMP_ID,'&EMP_NAME',

'&DEPARTMENT',&SALARY,&PF_AMOUNT,&LIC_AMOUNT,

&LOAN_AMOUNT,&TOTAL);

UPDATE A DATA

SQL > UPDATE EMP_DET SET TOTAL=SALARY-PF-LOAN-LIC;

Enter the Emp_id                    :1006                                           

Enter the Emp_name              :Dhivahar

Enter the Department             :Software

Enter the Salary                      :30000 

Enter the PF_amount              :800    

Enter the LIC_amount                        :3000 

Enter the Loan_amount          :6000

Enter the total                         :20200

SQL >SELECT *FROM EMP_DET;

  EMP_ID    EMP_NAME              DEP  SALARY         PF        LIC     LOAN  TOTAL
------------- -------------------- ---------------- ------------ ---------- ---------- ------------ ----------
     1001               Bhuvana        Software       10000         200       1000       5000        3800

     1002                      Jeya       Electronic       10500         100       2000       3000        5400

     1003                    Kalai        Electrical        20000         500       8000       5000       6500

     1004                    Arun        Software         25000       3000       5000       9000       8000

     1005                  Rajesh              Civil         30000       4000       5000       9000      12000

     1006              Dhivahar    Mechanical        30500          500       8000     10000     12000

     1006              Dhivahar       Software         30000          800       3000       6000     20200

COUNT

SQL > SELECT COUNT(*) FROM EMP_DET;

COUNT(*)
---------------
        7

COUNT THE SPECIFIC PERSON

SQL > SELECT EMP_NAME COUNT(*) FROM EMP_DET WHERE
EMPO_NAME='DHIVAHAR';

COUNT(*)
--------------
         2

MAXIMUM

SQL > SELECT MAX(SALARY) FROM EMP_DET;

MAX(SALARY)
----------------------
      30500

MAXIMUM WITH GROUP

SQL > SELECT EMP_NAME,MAX(SALARY) FROM EMP_DET GROUP BY

EMP_NAME;

EMP_NAME     MAX(SALARY)
-------------------- ---------------------
      Arun                             25000

Bhuvana                             10000

Dhivahar                            30500

        Jeya                            10500

       Kalai                            20000

     Rajesh                            30000

MINIMUM

SQL > SELECT MIN(SALARY) FROM EMP_DET;

MIN(SALARY)
--------------------               
10000

MINIMUM WITH GROUP

SELECT EMP_NAME,MIN(SALARY) FROM EMP_DET GROUP BY EMP_NAME;

EMP_NAME MIN(SALARY)
------------------ -------------------
              Arun                 25000

        Bhuvana                 10000

        Dhivahar                30000

               Jeya                 10500

             Kalai                  20000

           Rajesh                  30000


MAXIMUM AND MINIMUM

SQL > SELECT MAX(SALARY) MAX,MIN(SALARY) MIN FROM EMP_DET;

 MAX        MIN
---------- ----------
  30500     10000

AVERAGE

SQL > SELECT AVG(SALARY) FROM EMP_DET;

AVG(TOTAL)
-------------------
                9700

AVERAGE WITH GROUP

SQL > SELECT EMP_NAME,AVG(SALARY) FROM EMP_DET GROUP BY

EMP_NAME;

    EMP_NAME   AVG(SALARY)
-------------------- -----------------------
     Arun                      25000

Bhuvana                     10000
            
            Dhivahar                    30250
            
            Jeya                     10500

Kalai                     20000
     
            Rajesh                     30000

SUM

SQL > SELECT SUM(SALARY) FROM EMP_DET;

SUM(TOTAL)
-------------------
              67900

SUM WITH GROUP

SQL > SELECT EMP_NAME,SUM(SALARY) FROM EMP_DET GROUP BY

EMP_NAME;

EMP_NAME     SUM(TOTAL)
-------------------- -------------------
                Arun                    8000

           Bhuvana                   3800

           Dhivahar                 32200

                  Jeya                    5400

                 Kalai                    6500

               Rajesh                  12000

RANDOM

SQL > SELECT * FROM EMP_DET ORDER BY EMP_NAME;

 EMP_ID     EMP_NAME           DEP  SALARY          PF        LIC       LOAN      TOTAL
------------- -------------------- --------------- -------------- -------- ---------- ------------- --------------
      1004                     Arun       Software      25000       3000       5000          9000           8000

      1001                Bhuvana      Software      10000         200       1000          5000           3800

      1006                Dhivahar  Mechanical      30500         500       8000        10000         12000

      1007                Dhivahar            Civil       30000         800       3000          6000         20200

      1002                       Jeya     Electronic      10500         100       2000          3000           5400

      1003                      Kalai     Electrical       20000         500       8000         5000            6500

      1005                    Rajesh            Civil       30000       4000       5000         9000         12000

CONCAT

SQL > SELECT CONCAT(EMP_ID,EMP_NAME) FROM EMP_DET;

CONCAT(EMP_ID,EMP_NAME)
--------------------------------------------
1001Bhuvana

1002Jeya

1003Kalai

1004Arun

1005Rajesh

1006Dhivahar

1007Dhivahar

LENGTH

SQL > SELECT LENGTH(EMP_NAME) FROM EMP_DET;

LENGTH(EMP_NAME)
-------------------------------
                                     7
                         4
                                     5

SIN

SQL > SELECT SIN(99.55) FROM EMP_DET;

SIN(99.55)
---------------
-.83103446

ROUND

SQL > SELECT ROUND(99.55,0) FROM EMP_DET;

ROUND(99.55)
--------------------
                   100

TRUNCATE

SQL > SELECT TRUNC(99.55,1) FROM EMP_DET;

TRUNC(99.55,1)
----------------------
                     99.5

SQRT

SQL > SELECT SQRT(SALARY) FROM EMP_DET;

SQRT(SALARY)
-----------------------
                        100

FLOOR

SQL > SELECT FLOOR(99.55) FROM EMP_DET;

FLOOR(99.55)
--------------------
                     99  

ABS(99.55)

SQL > SELECT ABS(99.55) FROM EMP_DET;

ABS(99.55)
---------------
          99.55

ABS(-99.55)

SQL > SELECT ABS(-99.55) FROM EMP_DET;

ABS(-99.55)
----------------
          99.55                            

UPPERCASE

SQL > SELECT UPPER(EMP_NAME) FROM EMP_DET;

UPPER(EMP_NAME)
-----------------------------
     BHUVANA
    JEYA
 KALAI
   ARUN

INITCAP

SQL > SELECT INITCAP(EMP_NAME) FROM EMP_DET;

INITCAP(EMP_NAME)
-------------------------------
Rajesh
         Dhivahar
         Dhivahar

LOWERCASE

SQL > SELECT LOWER(EMP_NAME) FROM EMP_DET;

LOWER(EMP_NAME)
------------------------------
bhuvana
      jeya
     kalai
      arun
    rajesh
dhivahar

USERID

SQL > SELECT USERID FROM EMP_DET;

UID
------
   33
   53

GREATEST

SQL > SELECT GREATEST(SALARY,PF) FROM EMP_DET;

GREATEST
-----------------
            10000
            10500

LEAST

SQL > SELECT LEAST(PF,LIC) FROM EMP_DET;

LEAST
-----------
       200
       100

ASCENDING

SQL > SELECT EMP_NAME FROM EMP_DET ORDER BY EMP_NAME ASC;

EMPNAME
----------------
            Arun
       Bhuvana
       Dhivahar

DESCENDING

SQL > SELECT EMP_NAME FROM EMP_DET ORDER BY EMP_NAME DESC;

EMP_NAME
------------------
         Dhivahar
         Bhuvana
               Arun

CEILING

SQL > SELECT CEIL(99.55) FROM EMP_DET;

CEIL(99.55)
----------------
              100

SIGN

SQL > SELECT SIGN(99.55) FROM EMP_DET;

SIGN(99.55)
-----------------
                    1 
EX.NO:4                SQL QUERY USING OPERATORS

Aim

Write a SQL query to implement the wildcard character using Like
operator,Relational,Arithmetic and Logical operator.

AND Operator

The AND operator displays a record if both the first condition AND the second
Condition are true.

AND operator is used to set multiple conditions with Where clause.

OR Operator
          
             The OR operator displays a record if either the first condition OR the second
condition is true.

BETWEEN..AND

The operator BETWEEN and AND, are used to compare data for a range of values.

LIKE Operator

The SQL LIKE condition allows to use wildcards to perform pattern matching in a
query.

The LIKE condition is used in the WHERE clause of a SELECT, INSERT,

UPDATE, or DELETE statement.

(i)Using '%' in Wildcard in LIKE condition
'%' A substitute for zero or more characters.

(ii)Using '_' in Wildcard in Like condition
'_'  A substitute for a single character.

!= Operator

Checks if the values of two operands are equal or not, if values are not equal then
condition becomes true.

NOT BETWEEN Operator

NOT BETWEEN AND operator checks whether a value is not present between a
starting and a closing expression.

Create a table

SQL > CREATE TABLE EMP(ID INTEGER,NAME VARCHAR2(15),

CITY VARCHAR2(10),COMMISSION_CHARGE VARCHAR2(5),

PRIMARY KEY(ID));
Table is created.

INSERT A DATA INTO TABLE

SQL > INSERT INTO EMP

VALUES(&ID,'&NAME','&CITY',COMMISSION_CHARGE);

Enter the ID                                        :101

Enter the Name                                   :Bhuvana Devi

Enter the City                                     :Chennai

Enter the Commission_charge            :1102.5

SQL > SELECT  *FROM EMP;

ID        NAME               CITY          COMMISSION_CHARGE
---- ----------------------  --------------- -------------------------------------
101      Bhuvana Devi         Chennai                                       1102.5

102      jemis                    Coimbatore                                        8000

103      Ajis                               India                                         4500

104      Kayal                     Bangalore                                      1080.5

 106     Naani                    Kerala                                                4300

 107     Mani                     Osure                                                 2000

 108     Nalli                     Chennai                                              900

(i) Write a SQL query to find those salesman with all information who come from

      the city either Chennai or India.

SQL > SELECT *FROM EMP WHERE CITY='CHENNAI' AND CITY='INDIA';

 ID       NAME                           CITY            COMMISSION_CHARGE
-------- -------------------- --------------- -----------------------------------
101      Bhuvana Devi    Chennai                                            1102.5

103      Ajis                     India                                                    4500

105      Sanjana               India                                                    9500

108      Nalli                   Chennai                                               9000

(ii) Write a SQL statement to find all customers with all information whose names are       

       starting with the  character ’S’.

SQL > SELECT *FROM EMP WHERE NAME LIKE NAME='%S';

ID    NAME    CITY  COMMISSION_CHARGE
----- ----------- --------- ----------------------------------
105 Sanjana     India                               9500

(iii) Write a SQL statement to find all customers with all information whose names are       

        ending with the  character ’A’.

SQL>SELECT *FROM EMP WHERE NAME LIKE NAME='A%';

 ID NAME    CITY    COMMISSION_CHARGE
---- ----------- --------- ------------------------------------
105 Sanjana   India                                              950

106 Nazima   kerala                                           4300

(iv) Write  a SQL query to filter the data with all information which commission

amount is with the range 1000 and 10000;

SQL> SELECT *FROM EMP WHERE COMMISSION_CHARGE BETWEEN

1000 AND 5000;

 ID  NAME                CITY            COMMISSION_CHARGE
----- -------------------- --------------- -----------------------------------
101  Bhuvana Devi          Chennai                                    1102.5

103                  Ajis              India                                                     4500

104               Kayal       Bangalore                                     1080.5

106            Nazima             Kerala                                       4300

107                Mani             Osure                                        2000

(V) Write a SQL query to filter with all information which commission amount  in

with the range 1000 and 10000 except those commission amount value is 1102.50 and

1080.50.

SQL > SELECT *FROM EMP WHERE COMMISSION_CHARGE BETWEEN

1000 AND 10,000 AND   COMMISSION_CHARGE !=1102.50

AND COMMISSION_CHARGE !=1080.50;

ID   NAME      CITY             COMMISSION_CHARGE
----- ------------- --------------- ----------------------------------
102      Jemis     Coimbatore                                        8000

103        Ajis               India                                        4500

105  Sanjana               India                                        9500

106  Nazima             Kerala                                        4300

108      Nalli           Chennai                                        9000


(vi) Write a SQL statement to find those customer name with all other information and

the name started with other than any letter within ‘A’ and ‘L’.

SQL > SELECT NAME FROM EMP WHERE NAME NOT BETWEEN
           'A' AND 'L';
NAME
---------------
Jemis
Sanjana
Nazima
Mani
Nalli

 (vii) Write a SQL statement to find all information the name containing the first

character is ‘N’ and the fourth character is ‘L’ and the remaining character is

anything.

SQL>SELECT NAME FROM EMP WHERE NAME 'N_ _ L%';

NAME
---------------
Nalli

(viii)Write a SQL statement to find the escape charater in customer name.

SQL>SELECT *FROM EMP WHERE NAME LIKE '% %';

 ID  NAME              CITY      COMMISSION_CHARGE
----- -----------------  ---------- --------------------------------
101  Bhuvana Devi  Chennai                                     1102.5

EX.NO:5                    A)TO CREATE A  INTERCOLLEGIATE REPORT
USING JOINS IN SQL

AIM

          Program to create a intercollegiate report using JOIN in SQL.

SQL INNER JOIN

The INNER JOIN keyword selects all rows from both tables as long as there is a match

between the columns in both tables.

SYNTAX

SELECT TAB1_name.COLUMN,TAB2_name.COLUMN FROM TAB1_name

INNER JOIN TAB2_name ON TAB1_name.ID=TAB2_name.ID;

SQL LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching

rows in the right table (table2). The result is NULL in the right side when there is no match.

SYNTAX

SELECT TAB1_name.COLUMN,TAB2_name.COLUMN FROM TAB1_name

LEFT JOIN TAB2_name ON TAB1_name.ID=TAB2_name.ID;

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all rows from the right table (table2), with the

matching rows in the left table (table1). The result is NULL in the left side when there is

no match.

SYNTAX

SELECT TAB1_name.COLUMN,TAB2_name.COLUMN FROM TAB1_name

RIGHT JOIN TAB2_name ON TAB1_name.ID=TAB2_name.ID;

CRAETE A TABLE

SQL > CREATE TABLE TABLE_NAME(SNO INT,DATES DATE,

SNAME VARCHAR2(20),CNAME VARCHAR2(8),QUIZ VARCHAR2(3) NULL,

SOFT_MARK VARCHAR2(3) NULL,PAPER VARCHAR2(3) NULL,

            POSTAL VARCHAR2(3) NULL,LOTNAME VARCHAR2(10) NOT NULL,

PRIMARY KEY(SNO));

Table is created.

INSERT A DATA INTO TABLE

SQL > INSERT INTO TABLE_NAME VALUES(&SNO,TO_DATE

('26 JAN 2017','MMDDYYYY'),'&SNAME','&CNAME','&QUIZ',

'&SOFT_MARK','&PAPER','&POSTAL','&LOTNAME);

Enter the sno               :1

Enter the snam            e          :Bhuvana

Enter the cname          :ANJAC

Enter the Quiz             :Yes

Enter the soft_mark    :Yes

Enter the paper            :No

Enter the postal           :No

Enter the Lotname      :Eclairs

SQL > SELECT *FROM MAINTABLE;     

 SNO               DATES    SNAME           CNAME   QUIZ SOFT PAPER POST  LOTNAME
------------- --------------- ------------- ------------------ --------- -------- --------- -------- -------------
         1       26-JAN-17     Bhuvana            ANJAC       Yes     Yes        No      No         Eclairs
         2       26-JAN-17           Jemi             ANJAC       Yes      No        No      No         Eclairs
         3       26-JAN-17          Kalai             ANJAC         No    Yes        No      No         Eclairs
        4       26-JAN-17        Najima        Kaleeswari       Yes      No     Yes      Yes      Milkybar
        5       26-JAN-17         Muthu        Kaleeswari        No      No        No    Yes       Milkybar
        6       26-JAN-17            Mari        Kaleeswari       Yes    Yes        No      No      Milkybar
        7       26-JAN-17      Lakshmi                   SFR        No      No      Yes      No     Dairymilk    
        8       26-JAN-17          Latha                    SFR       Yes      No      Yes      No     Dairymilk
        9       26-JAN-17            Arul                    SFR        No      No      Yes        No    Dairymilk
        10     26-JAN-17            Jothi          Kamarajar       Yes    Yes       No        No          Safari
        11     26-JAN-17           Arun           Kamarajar       No     Yes       No        No          Safari
        12     26-JAN-17          Vicky          Kamarajar        No      No       No       Yes         Safari
        13     26-JAN-17         Ashok                   PSR         No     Yes      Yes      Yes    Lollypop
        14     26-JAN-17      Bharathi                  PSR       Yes       No        No        No   Lollypop
        15     26-JAN-17        Anjana                   PSR         No     Yes        No      Yes    Lollypop
        16     26-JAN-17       Sanjana            MEPCO       Yes     Yes        No        No       Munch
        17     26-JAN-17       Yogesh             MEPCO        No     Yes      Yes        No       Munch
18       26-JAN-17        Kumar             MEPCO       Yes     Yes        No      Yes       Munch

(i)To get eventwise report on

(i)QUIZ

SQL > INSERT INTO MAINTALE(SNO,SNAME,LOTNAME,QUIZ) SELECT

SNO,SNAME,LOTNAME,QUIZ FROM MAINTABLE

WHERE QUIZ='YES';
       SNO        SNAME LOTNAME               QUIZ
------------- --------------- --------------- ------------------
         1       26-JAN-17     Bhuvana                    Yes
         2       26-JAN-17            Jemi                    Yes
         4       26-JAN-17        Najima                    Yes    
         6       26-JAN-17            Mari                    Yes   
         8       26-JAN-17          Latha                     Yes   
        10     26-JAN-17            Jothi                     Yes
        14     26-JAN-17      Bharathi                     Yes
        16     26-JAN-17       Sanjana                      Yes
        18     26-JAN-17        Kumar                       Yes  


(ii) SOFTWARE_MARKETING

SQL > INSERT INTO MAINTALE(SNO,SNAME,LOTNAME,SOFT) SELECT

SNO,SNAME,LOTNAME,SOFT FROM MAINTABLE

WHERE SOFT='YES';

       SNO      SNAME   LOTNAME    SOFT
------------ --------------- ---------------- ----------
            1     26-JAN-17     Bhuvana           Yes 
            3     26-JAN-17           Kalai           Yes 
6     26-JAN-17            Mari           Yes    
13    26-JAN-17        Ashok           Yes     
10     26-JAN-17          Jothi           Yes
        11     26-JAN-17              Arun           Yes     
        15     26-JAN-17            Anjana          Yes       
        16     26-JAN-17           Sanjana          Yes         
        17     26-JAN-17            Yogesh         Yes     
        18     26-JAN-17             Kumar         Yes

(iii) PAPER PARESENTATION

SQL > INSERT INTO MAINTALE(SNO,SNAME,LOTNAME,PAPER) SELECT

SNO,SNAME,LOTNAME,PAPER FROM MAINTABLE

WHERE PAPER='YES';

    SNO       SNAME LOTNAME      PAPER
---------- --------------- --------------- -------------
          4     6-JAN-17          Najima            Yes      
       7       26-JAN-17      Lakshmi             Yes       
       8       26-JAN-17          Latha              Yes     
       9       26-JAN-17            Arul              Yes       
     13       26-JAN-17         Ashok              Yes     
     17       26-JAN-17       Yogesh              Yes    

(iv) POSTAL PRESENTATION

SQL > INSERT INTO MAINTALE(SNO,SNAME,LOTNAME,POSTAL) SELECT

SNO,SNAME,LOTNAME,POSTAL FROM MAINTABLE

 WHERE POSTAL='YES';

      SNO      SNAME  LOTNAME    POSTAL
 ---------- --------------- ---------------- --------------
        4       26-JAN-17          Najima             Yes     
        5       26-JAN-17           Muthu             Yes       
      12       26-JAN-17            Vicky             Yes          
      13       26-JAN-17           Ashok             Yes     
       15       26-JAN-17         Anjana              Yes       
       18       26-JAN-17          Kumar              Yes 

INNER JOIN USING SUBQUERY(EVENT TABLE)

(i)QUIZ

SQL > SELECT INTCLG.SNO,INTCLG.CNAME,INTCLG.SNAME,

INTCLG.LOTNAME,EV_Q.QUIZ FROM INTCLG INNER

JOIN EV_Q ON INTCLG.SNO=EV_Q.SNO;

SNO     CNAME       SNAME         LOTNAME    QUIZ
------- -------------- --------------- --------------------- ----------  
      1   26-JAN-17     Bhuvana                   Eclairs        Yes
         2  26-JAN-17          Jemi                  Eclairs        Yes
         4  26-JAN-17      Najima               Milkybar       Yes
        6   26-JAN-17          Mari               Milkybar       Yes
        8   26-JAN-17        Latha               Dairymilk       Yes
        10  26-JAN-17         Jothi                     Safari       Yes
        14  26-JAN-17    Bharathi               Lollypop       Yes
        16  26-JAN-17     Sanjana                   Munch       Yes
        18  26-JAN-17       Kumar                  Munch       Yes

(ii)PAPER PRESENTATIOIN

SQL > SELECT INTCLG.SNO,INTCLG.CNAME,INTCLG.SNAME,

INTCLG.LOTNAME,EV_PAPER.PAPER FROM INTCLG

INNER JOIN EV_PAPER ON INTCLG.SNO=EV_PAPER.SNO;

 SNO   CNAME        SNAME       LOTNAME   PAPER
------- -------------- --------------- -------------------- -----------
      4     Kaleeswari      Najima              Milkybar        Yes
     7               SFR      Lakshmi            Dairymilk        Yes
     8               SFR           Latha           Dairymilk        Yes
     9               SFR             Arul           Dairymilk        Yes
    13              PSR           Ashok            Lollypop        Yes
    17       MEPCO         Yogesh              Munch         Yes

(iii)SOFTWARE_MARKETING

SQL > SELECT INTCLG.SNO,INTCLG.CNAME,INTCLG.LOTNAME,

EV_SOFT.SOFT FROM INTCLG INNER JOIN EV_SOFT

ON INTCLG.SNO=EV_SOFT.SNO;

 SNO  SNAME             CNAME    LOTNAME   SOFT
------- ------------- -------------------- ---------------- ----------
1           Bhuvana            ANJAC           Eclairs          Yes
3                 Kalai             ANJAC          Eclairs          Yes
 6                Mari           Kaleeswari       Milkybar       Yes
10               Jothi           Kamarajar           Safari         Yes
11               Arun          Kamarajar           Safari          Yes
13             Ashok                   PSR       Lollypop         Yes
15            Anjana                   PSR       Lollypop         Yes
16           Sanjana           MEPCO           Munch         Yes
17           Yogesh           MEPCO           Munch          Yes
18            Kumar           MEPCO            Munch         Yes

(iv)POSTAL PRESENTATION

SQL > SELECT INTCLG.SNO,INTCLG.CNAME,INTCLG.LOTNAME,

EV_POS.POSTAL  FROM INTCLG INNER JOIN EV_POS 

ON INTCLG.SNO=EV_POS.SNO;

   SNO       SNAME             CNAME   LOTNAME     POSTAL
--------- --------------- -------------------- ------------------ -------------
        4          Najima          Kaleeswari           Milkybar           Yes
        5           Muthu          Kaleeswari          Milkybar            Yes
       12           Vicky           Kamarajar             Safari               Yes
        13         Ashok                     PSR        Lollypop              Yes
        15        Anjana                      PSR       Lollypop              Yes
        18         Kumar                MEPCO         Munch              Yes

To find the Total Number of Colleges using function

COUNT THE COLLEGE NAME

SQL > SELECT CNAME,COUNT(CNAME) FROM INTCLG GROUP BY CNAME;

CNAME      COUNT(CNAME)
------------ ---------------------------
  ANJAC                                  5
Kaleeswari                               5
Kamarajar                                 5
   MEPCO                                5
          PSR                                5
           SFR                               5


TO FIND THE TOTAL NUMBER OF PARTICIPANTS USING FUNCTION.

SQL > SELECT COUNT(SNAME) FROM INTCLG;

COUNT(SNAME)
------------------------
                         30

TO GET A COLLEGEWISE REPORT

(i)SFR

SQL > SELECT SNO,SNAME,CNAME,LOTNAME,QUIZ,SOFT_MARK,PAPER,

POSTAL FROM INTCLG WHERE CNAME='SFR';

 SNO        DATES    SNAME    CNAME LOTNAME QUIZ SOFT PAPER POST
-------- ---------------- ------------ ------------- --------------- ------- -------- --------- --------
       7       26-JAN-17   Lakshmi         SFR       Dairymilk     No      No      Yes        No        
        8       26-JAN-17       Latha         SFR       Dairymilk   Yes      No      Yes        No    
       9       26-JAN-17         Arul         SFR        Dairymilk    No      No      Yes        No    

(ii)ANJAC

SQL > SELECT SNO,SNAME,CNAME,LOTNAME,QUIZ,SOFT_MARK,PAPER,

POSTAL FROM INTCLG WHERE CNAME='ANJAC';

SNO        DATES    SNAME    CNAME LOTNAME QUIZ SOFT PAPER POST
-------- ---------------- ------------ ------------- --------------- ------- -------- --------- --------
      1       26-JAN-17     Bhuvana   ANJAC      Eclairs        Yes     Yes        No       No          
      2       26-JAN-17            Jemi    ANJAC     Eclairs        Yes       No        No      No     
      3       26-JAN-17           Kalai     ANJAC   Eclairs          No     Yes        No       No     

(iii)KALEESWARI

SQL > SELECT SNO,SNAME,CNAME,LOTNAME,QUIZ,SOFT_MARK,PAPER,

POSTAL FROM INTCLG WHERE CNAME='Kaleeswari';

SNO           DATES   SNAME     CNAME     QUIZ  SOFT PAPER  POST LOTNAME
-------- ---------------- ------------ --------------- ---------- -------- ---------- ------- ----------------
      4       26-JAN-17    Najima   Kaleeswari       Yes      No      Yes       Yes       Milkybar
      5       26-JAN-17      Muthu  Kaleeswari        No      No        No      Yes        Milkybar
      6       26-JAN-17         Mari  Kaleeswari       Yes     Yes        No       No       Milkybar

(iv)PSR

SQL > SELECT SNO,SNAME,CNAME,LOTNAME,QUIZ,SOFT_MARK,PAPER,

POSTAL FROM INTCLG WHERE CNAME='PSR';

SNO           DATES   SNAME     CNAME     QUIZ  SOFT PAPER  POST LOTNAME
-------- ---------------- ------------ --------------- ---------- -------- ---------- ------- ----------------
    13     26-JAN-17         Ashok            PSR         No     Yes      Yes       Yes       Lollypop
    14     26-JAN-17      Bharathi            PSR       Yes      No        No         No      Lollypop
    15     26-JAN-17        Anjana            PSR         No     Yes        No       Yes      Lollypop 

(v)MEPCO

SQL > SELECT SNO,SNAME,CNAME,LOTNAME,QUIZ,SOFT_MARK,PAPER,

POSTAL FROM INTCLG WHERE CNAME='MEPCO';

SNO           DATES   SNAME     CNAME     QUIZ  SOFT PAPER  POST LOTNAME
-------- ---------------- ------------ --------------- ---------- -------- ---------- ------- ----------------
     13     26-JAN-17         Ashok             PSR       No     Yes      Yes       Yes      Lollypop
     14     26-JAN-17      Bharathi                        PSR     Yes      No        No         No      Lollypop
     15     26-JAN-17        Anjana             PSR      No     Yes        No       Yes      Lollypop  

COUNT THE EVENTWISE

(i)QUIZ

SQL > SELECT COUNT(QUIZ) FROM EV_Q;

COUNT(QUIZ)
---------------------
                     9

(ii)SOFTWARE_MARKETING

SQL > SELECT COUNT(SOFT) FROM EV_SOFT;

COUNT(SOFT)
---------------------
                      6

(iii)PAPER PRESENTATION

SQL > SELECT COUNT(PAPER) FROM EV_PAPER;

COUNT(PAPER)
-----------------------
                        13

(iv)POSTAL PRESENTATION

SQL > SELECT COUNT(POSTAL) FROM EV_POS;

COUNT(POSTAL)
-------------------------
                            6

UPDATE THE ENTRY_DATE

SQL > UPDATE INTCLG SET ENTRY_DATE=TO_DATE('23 JAN

2017','DDMMYYYY') WHERE CNAME='ANJAC';

SNO     DATES    SNAME       CNAME   QUIZ SOFT PAPER POST         LOT     ETRDTE
----- --------------- ------------- --------------- --------- -------- --------- ------- ------------ -------------
 1       26-JAN-17   Bhuvana        ANJAC      Yes     Yes       No       No     Eclairs   23-JAN-17  
 2       26-JAN-17          Jemi        ANJAC      Yes      No       No       No     Eclairs   23-JAN-17  
 3       26-JAN-17         Kalai        ANJAC       No     Yes       No       No     Eclairs  23-JAN-17  
 4       26-JAN-17      Najima    Kaleeswari     Yes      No     Yes      Yes   Milkybar 27-JAN-17  
 5       26-JAN-17       Muthu    Kaleeswari      No      No       No      Yes   Milkybar 27-JAN-17  
 6       26-JAN-17          Mari    Kaleeswari     Yes    Yes       No       No  Milkybar  27-JAN-17  
 7       26-JAN-17    Lakshmi              SFR       No      No     Yes       No Dairymilk 12-JAN-17     
 8       26-JAN-17        Latha               SFR      Yes     No      Yes       No Dairymilk 12-JAN-17  
 9       26-JAN-17          Arul               SFR     No       No    Yes         No  Dairymilk 12-JAN-17  
 10     26-JAN-17         Jothi      Kamarajar     Yes     Yes     No   No           Safari   18-JAN-17  
 11     26-JAN-17         Arun      Kamarajar      No     Yes     No   No           Safari   18-JAN-17  
 12     26-JAN-17        Vicky      Kamarajar      No      No    No  Yes           Safari   18-JAN-17  
 13     26-JAN-17       Ashok                PSR      No     Yes  Yes  Yes      Lollypop    20-JAN-17  
 14     26-JAN-17    Bharathi                PSR     Yes      No    No   No      Lollypop   20-JAN-17  
 15     26-JAN-17      Anjana               PSR        No    Yes    No  Yes      Lollypop   20-JAN-17       
 16     26-JAN-17     Sanjana         MEPCO     Yes    Yes     No    No       Munch    25-JAN-17  
 17     26-JAN-17     Yogesh         MEPCO      No    Yes    Yes    No        Munch    25-JAN-17  
 18     26-JAN-17      Kumar         MEPCO     Yes    Yes      No Yes         Munch   25-JAN-17  

ENTRY_DATE RANGE

SQL > SELECT *FROM INTCLG WHERE ENTRY_DATE BETWEEN '23 JAN 2017'

AND '25 JAN 2017';

SNO     DATES    SNAME       CNAME   QUIZ SOFT PAPER POST        LOT        ETRDT
----- --------------- ------------- --------------- --------- -------- --------- ------- ------------- ------------
1       26-JAN-17    Bhuvana        ANJAC      Yes     Yes      No       No       Eclairs 23-JAN-17  
 2       26-JAN-17          Jemi        ANJAC      Yes      No      No       No       Eclairs 23-JAN-17  
 3       26-JAN-17         Kalai        ANJAC       No     Yes      No       No       Eclairs 23-JAN-17  
12     26-JAN-17        Vicky      Kamarajar     No      No       No       Yes       Safari 18-JAN-17  
13     26-JAN-17       Ashok                PSR      No    Yes      Yes       Yes Lollypop  20-JAN-17  
14     26-JAN-17    Bharathi                PSR     Yes     No       No        No Lollypop  20-JAN-17  
15     26-JAN-17      Anjana                 PSR     No    Yes       No       Yes Lollypop  20-JAN-17       
16     26-JAN-17     Sanjana          MEPCO     Yes    Yes       No       No    Munch  25-JAN-17  
17     26-JAN-17     Yogesh           MEPCO      No    Yes      Yes      No     Munch  25-JAN-17  
18     26-JAN-17      Kumar           MEPCO     Yes    Yes       No     Yes     Munch  25-JAN-17   

SPECIFIC DATE

SQL > SELECT *FROM INTCLG WHERE ENTRY_DATE='18 JAN 2017';

SNO     DATES    SNAME       CNAME   QUIZ SOFT PAPER POST  LOT  ETRY_DTE
----- --------------- ------------- --------------- --------- -------- --------- ------- -------- ----------------
  10     26-JAN-17    Jothi        Kamarajar     Yes     Yes       No        No   Safari    18-JAN-17  
  11     26-JAN-17   Arun         Kamarajar     No      Yes       No        No  Safari     18-JAN-17  
 12     26-JAN-17   Vicky        Kamarajar      No       No      No        Yes  Safari    18-JAN-17  


EX.NO:5                   B) TO IMPLEMENT THE DATE FUNCTION

                                         Date function is converts in a String to Date.

ADD_MONTHS

                         ADD_MONTHS function adds the specified number of months to a

datetime value

SYNTAX

               SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;
 
ADD_MONTHS

                         Add_months function adds or subtracts the specified number of months from a

datetime expression.

SYNTAX       

               SELECT ADD_MONTHS('11-JAN-2012', 1) FROM dual;

LAST_DAY

                        If the resulting month has fewer days then its last day is set.

SYNTAX

            SELECT LAST_DAY(‘date’) FROM DUAL;

DIFFERENCE BETWEEN TWO DATES

                        The DATEDIFF() function returns the time between two dates.

SYNTAX

            SELECT TO_DATE(‘date’)-TO_DATE(date) FROM DUAL;

NEXT DAY

                        NEXT_DAY function returns the first weekday that is greater than a date.

SYNTAX       

            SELECT SYSDATE,NEXT_DAY(SYSDATE,'MONDAY')

"NEXT MON" FROM DUAL;

MONTHS_BETWEEN

                        Number of months between two dates

SYNTAX

            SELECT MONTHS_BETWEEN(SYSDATE,DATES) FROM INTCLG

GROUP BY DATES;

ROUND

                        ROUND function returns a date rounded to a specific unit of measure.

SYNTAX

SELECT SYSDATE,ROUND(SYSDATE,'MONTH') MONTH FROM DUAL;  

TO_CHAR

                        TO_CHAR function converts a number or date to a string.

SYNTAX

            SELECT TO_CHAR(SYSDATE,'DAY') "TODAY" FROM DUAL;                  

EXTRACT   

            The EXTRACT() function is used to return a single part of a date/time,

such as year, month, day, hour          

SYNTAX

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;

ADD_MONTHS

SQL > SELECT ADD_MONTHS(SYSDATE,5) FROM DUAL;

ADD_MONTHS
---------------------
       30-JUN-17

LAST_DAY

SQL > SELECT LAST_DAY('12 JAN 2017') AS LAST_DAY FROM dual;

LAST_DAY
----------------
31-JAN-17

DIFFERENCE BETWEEN TWO DATES

SQL > SELECT TO_DATE('23 JAN 2017')-TO_DATE('26 JAN 2017') FROM DUAL;

TO_DATE('23JAN2017')-TO_DATE('26JAN2017')
-----------------------------------------
                                       -3

NEXTDAY

SQL > SELECT SYSDATE,NEXT_DAY(SYSDATE,'MONDAY') "NEXT MON" FROM

DUAL;

SYSDATE   NEXT MONTH
-------------- ----------------------
31-JAN-17          06-FEB-17

SQL> SELECT SYSDATE,ROUND(SYSDATE,'MONTH') MONTH FROM DUAL;

SYSDATE   MONTH
--------------- ------------
31-JAN-17 01-FEB-17

SQL> SELECT MONTHS_BETWEEN(SYSDATE,DATES) FROM INTCLG

GROUP BY DATES;

MONTHS_BETWEEN(SYSDATE,DATES)
-------------------------------------------------------
                                                     .177590352

TODAY

SQL > SELECT TO_CHAR(SYSDATE,'DAY') "TODAY" FROM DUAL;

TODAY
------------
Tuesday

EXTRACT

SQL > SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;

EXTRACT(YEARFROMSYSDATE)
------------------------------------------------
                                                      2017 


EX.NO:6                A)TO IMPLEMENT THE SUBQUERY

       USING SUPER MARKET BILL

AIM

            Program to implement the SUBQUERY in SQL using SUPER MARKET BILL.

SUBQUERY

            A Subquery or Inner query or Nested query is a query within another SQL query and

embedded within the WHERE clause.A subquery is used to return data that will be used in the

main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements

along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

SYNTAX

               SELECT column_name [, column_name ]FROM   table1 [, table2 ]

               WHERE  column_name OPERATOR

               (SELECT column_name [, column_name ]

               FROM table1 [, table2 ] [WHERE]);

EXIST

               If a subquery returns any rows at all, exists subquery is true, and not exists subquery is false.
.
SYNTAX
               
               WHERE EXISTS ( SUBQUERY );

INTERSECT

               The SQL INTERSECT clause/operator is used to combine two SELECT

statements,but returns rows only from the first SELECT statement that are identical to a row

in the second

SELECT statement              

SYNTAX
              
               SELECT column1 [, column2 ]FROM table1 [, table2 ]
 
               [WHERE condition]
 
               INTERSECT
 
               SELECT column1 [, column2 ]FROM table1 [, table2 ]
 
               [WHERE condition]
              
NOT EXIST
              
               If a subquery does not returns any rows at all, exists subquery is true, and not exists

subquery is false.
.                                                          
SYNTAX
               
               WHERE NOT EXISTS ( SUBQUERY );

              
CREATE A TABLE

SQL >    CREATE TABLE BILL1(SNO NUMBER,DATES DATE,CUS_NAME

               VARCHAR2(10),BILL_NO NUMBER NOT NULL,PRO_NAME

               VARCHAR2(10),QTY NUMBER NOT NULL,PRICE NUMBER NOT

               NULL,TOTAL NUMBER,PRIMARY KEY(BILL_NO));

INSERT A DATA INTO TABLE
Enter the sno               :101

Enter the dates             :Bhuvana

Enter the cus_name     :12345671

Enter the pro_name     :Soap

Enter the qty               :6

Enter the price             :6

Enter the total             :0

SQL > SELECT *FROM BILL1;

SNO      DATES CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL     
------ -------------- ---------------- ------------- ----------------- ---------- ----------- ----------
  101  23-JAN-17       Bhuvana   12345671              soap             6              6         36     
  102  23-JAN-17              Jeya   12345672       Dry_milk            2              5         10          
  103  23-JAN-17             Mani   12345673          powder            1             25           1           
  104  25-JAN-17             Jothi    12345674                pen            10           10       100           
  105  26-JAN-16             selvi    12345675            rubber            5              5         25           
  106  23-JAN-17       Bharathi    12345676             paper            1            15         16        
  107  25-JAN-17            vicky    12345677           munch            5         288        100           
  108 31-JAN-17         Bharath    12345678            pencil            5             7          35           
  109 31-JAN-17           prabhu   12345679          horlicks            1        150         150   
10    1-JAN-17          lavanya   123456710      chocolate           2          45           90  

INSERT A DATA INTO ANOTHER TABLE USING SUBQUERY

SQL > INSERT INTO BILL1 SELECT FROM BILL2 WHERE SNO IN(SELECT SNO

 FROM BILL1);

SQL > SELECT *FROM BILL2;          

SNO      DATES CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL     
------ -------------- ---------------- ------------- ----------------- ---------- ----------- ----------
  101  23-JAN-17       Bhuvana   12345671              soap             6              6         36     
  102  23-JAN-17              Jeya   12345672       Dry_milk            2              5         10          
  103  23-JAN-17             Mani   12345673          powder            1             25           1           
  104  25-JAN-17             Jothi    12345674                pen            10           10       100           
  105  26-JAN-16             selvi    12345675            rubber            5              5         25           
  106  23-JAN-17       Bharathi    12345676             paper            1            15         16           
  107  25-JAN-17            vicky    12345677           munch            5         288        100           
  108 31-JAN-17         Bharath    12345678            pencil            5             7          35           
  109 31-JAN-17           prabhu   12345679          horlicks            1        150         150   
    10 31-JAN-17          lavanya   123456710      chocolate           2          45           90  

UPDATE THE DATA INTO TABLE

SQL > UPDATE BILL1 SET PRICE=PRICE*2 WHERE DATES IN (SELECT DATES

FROM BILL2 WHERE DATES='23 JAN 2017';

SNO      DATES CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL     
------ -------------- ---------------- ------------- ----------------- ---------- ----------- ----------
  101  23-JAN-17       Bhuvana   12345671              soap             6              6         36     
  102  23-JAN-17              Jeya   12345672       Dry_milk            2              5         10          
  103  23-JAN-17             Mani   12345673          powder            1             25           1           
  104  25-JAN-17             Jothi    12345674                pen            10           10       100           
  105  26-JAN-16             selvi    12345675            rubber            5              5         25           
  106  23-JAN-17       Bharathi    12345676             paper            1            15         16           
  107  25-JAN-17            vicky    12345677           munch            5         288        100           
  108 31-JAN-17         Bharath    12345678            pencil            5             7          35           
  109 31-JAN-17           prabhu   12345679          horlicks            1        150         150   
    10 31-JAN-17          lavanya   123456710      chocolate           2          45           90  

SQL >UPDATE BILL1 SET DATES='26 JAN 17' WHERE DATES IN(SELECT DATES

FROM BILL2 WHERE DATES='26 JAN 16');

SNO    DATES  CUS_NAME  BILL_NO PRO_NAME      QTY PRICE    TOTAL     
------ -------------- ----------------- ------------- ----------------- ---------- ---------- ---------- 
 105  26-JAN-17              Selvi     12345675          Rubber             5          5            25   

SQL > UPDATE BILL1 SET STOCK=50 WHERE STOCK IN (SELECT STOCK FROM

BILL2);

 SNO    DATES CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL  STOCK
------ -------------- ---------------- ------------- ----------------- ---------- ----------- ---------- ---------
  101  23-JAN-17       Bhuvana   12345671              soap             6              6         36            20
  102  23-JAN-17              Jeya   12345672       Dry_milk            2              5         10            50
  103  23-JAN-17             Mani   12345673          powder            1             25           1           50
  104  25-JAN-17             Jothi    12345674                pen            10           10       100           50
  105  26-JAN-16             selvi    12345675            rubber            5              5         25            50
  106  23-JAN-17       Bharathi    12345676             paper            1            15         16            50
  107  25-JAN-17            vicky    12345677           munch            5         288        100            50
  108 31-JAN-17         Bharath    12345678            pencil            5             7          35            50
  109 31-JAN-17           prabhu   12345679          horlicks            1        150         150            50
    10 31-JAN-17          lavanya   123456710      chocolate           2          45           90            50

SQL > SELECT *FROM BILL1 WHERE TOTAL IN(SELECT MIN(TOTAL) FROM

BILL2);

SNO     DATES CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL  STOCK
------ -------------- ---------------- ------------- ----------------- ---------- ----------- ---------- ---------
  103  23-JAN-17            Mani   12345673        powder            1             25           1             50

SQL >SELECT *FROM BILL1 WHERE PRICE IN (SELECT SUM(PRICE) FROM

BILL2);

SNO    DATES CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL  STOCK
------ -------------- ---------------- ------------- ----------------- ---------- ----------- ---------- ---------
107  25-JAN-17             vicky   12345677             munch            5         288        100           50

SQL >SELECT *FROM BILL1 WHERE TOTAL <(SELECT MAX(TOTAL) FROM

BILL2);

SNO   DATES   CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL  STOCK
------ ---------------- ---------------- ------------- ----------------- ---------- ----------- ---------- -------
    101 23-Jan-17         Bhuvana    12345671             Soap             6           28           36        20
    102 23-Jan-17                Jeya    12345672       Dry_Milk            2             24         10          
    103 23-Jan-17               Mani    12345673         Powder             1           114         25          
    105 26-Jan-16               Selvi    12345675         Rrubber             5              5         25          
    106 23-Jan-17         Bharathi    12345676             Paper             1            70         16          
    108 31-Jan-17         Bharath    12345678             Pencil            5              7         35      

SQL > SELECT *FROM BILL1 WHERE SNO IN(SELECT COUNT(SNO) FROM

BILL2);

SNO   DATES   CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL  STOCK
------ ---------------- ---------------- ------------- ----------------- ---------- ----------- ---------- -------
    10 31-JAN-17       Lavanya     123456710     Chocolate             2           45           2         20

SQL > SELECT *FROM BILL1 WHERE PRO_NAME IN (SELECT PRO_NAME

FROM BILL2 PRO_NAME='SOAP');

SNO   DATES   CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL  STOCK
------ ---------------- ---------------- ------------- ----------------- ---------- ----------- ---------- -------
  101   23-JAN-17        Bhuvana   12345671           Soap            6             6           36            20

SQL > SELECT *FROM BILL1 WHERE PRICE IN (SELECT PRICE FROM BILL2

WHERE PRICE BETWEEN 20 AND 40);   

SNO   DATES   CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL  STOCK
------ -------------- ---------------- -------------- ----------------- ---------- ----------- ---------- -------
  107   25-JAN-17          vicky   12345677           Munch             5            20       100   

SQL > SELECT *FROM BILL1 WHERE PRICE IN(SELECT PRICE FROM BILL2

WHERE PRICE BETWEEN 20 AND 100 AND PRICE!=25);

SNO   DATES   CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL  STOCK
------ ---------------- ---------------- ------------- ----------------- ---------- ----------- ---------- -------
 107   25-JAN-17             Vicky  12345677            Munch            5           20          100         20  
 110   31-JAN-17        Lavanya 123456710        Chocolate           2           45            90           

SQL > SELECT *FROM BILL1 WHERE CUS_NAME LIKE 'B%';

SNO  DATES   CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL  STOCK
------ ---------------- ---------------- ------------- ----------------- ---------- ----------- ---------- -------
101     23-JAN-17         Bhuvana  12345671               Soap            6               6         36         20
106     23-JAN-17         Bharathi   12345676              Paper            1             15         16          
108     31-JAN-17          Bharath   12345678             Pencil             5              7          35       

EXIST

SQL > SELECT SNO,CUS_NAME,TOTAL FROM BILL1 WHERE EXISTS

(SELECT *FROM BILL_DET WHERE BILL1.SNO=BILL2.SNO);

SNO CUS_NAME  TOTAL 
------- ---------------- -----------
101         Bhuvana             36
102                Jeya             10
103               Mani             25
104               Jothi             100
105              Selvi              25
106         Bharathi              60
107             Vicky             110
108         Bharath               35
109          Prabhu              150
110         Lavanya              90

NOT EXISTS

SQL >SELECT SNO,CUS_NAME,TOTAL FROM BILL1 WHERE NOT EXISTS

(SELECT *FROM BILL2 WHERE BILL1.SNO=BILL2.SNO);

  SNO CUS_NAME   TOTAL
-------- ----------------- ----------
   10              Lavanya         90

INTERSECT

SQL > SELECT BILL1.SNO,BILL2.CUS_NAME,BILL1.DATES FROM

BILL1  RIGHT JOIN BILL2  ON BILL1.SNO=BILL2.SNO

INTERSECT

SELECT BILL1.SNO,BILL1.CUS_NAME,BILL2.DATES FROM BILL1

 RIGHT JOIN BILL2 ON BILL1.SNO=BILL2.SNO;

 SNO CUS_NAME         DATES
------- ----------------- ---------------
  101         Bhuvana     23-JAN-17
  102                Jeya     25-JAN-17    
  103               Mani     31-JAN-17
  104               Jothi      31-JAN-17  
  105               Selvi      27-JAN-17
  106         Bharathi       23-JAN-17 

EX.NO:6        B)TO IMPLEMENT THE DECODE,CASE,TRANSLATE

AIM

         To implement the DECODE,CASE,TRANSLATE in SQL.

DECODE

            DECODE function has the functionality of an IF-THEN-ELSE statement.

SYNTAX

            DECODE(expression,search,result[,search,result]..[,default] )

CASE

            CASEexpressions let you use IF ... THEN ... ELSE logic in SQL statements without

having to invoke procedures.

SYNTAX

CASE [expression ]

WHEN condition_1 THEN result_1

WHEN condition_1 THEN result_1

WHEN condition_n THEN result_n

ELSE result

END

TRANSLATE

            TRANSLATE function replaces a sequence of characters in a string with another set of

characters. 

SYNTAX

            TRANSLATE(String1,String to REPLACE);
        
DECODE

SQL>SELECT IMPORT,DECODE (IMPORT,'USA',

         'UNITED STATUS OF AMERICA','UK','UNITED KINGDOM',

         'CH','CHINA')AS DECODE FROM BILL2;

 IMPORT                                 DECODE
------------- ------------------------------------
        USA       United status of America

        USA       United status of America

          UK                   United Kingdom

          CH                                      China

SQL>SELECT CUS_NAME,CASE

WHEN TOTAL>100 AND TOTAL<200 THEN 'TOTAL IS GREATER THAN 90'

WHEN TOTAL >200 AND TOTAL<300 THEN 'TOTAL IS GREATER THAN 200'

 ELSE 'NULL' END AS CASE FROM BILL2;

CUS_NAME                          CASE
---------------- -----------------------------
Bhuvana                                   NULL
      Jeya                                   NULL
     Mani                                   NULL
     Jothi         Total is greater than  90

SQL>SELECT SNO,TRANSLATE(SNO,'1','X') AS TRANSLATE FROM BILL2;

 S_NO TRANSLATE
---------------------------
                           x0x
                           x02
                           x03
                           x04


EX.NO:7                TO IMPLEMENT THE VIEW CONCEPT IN SQL

AIM

            To implement the VIEW concept in SQL.

VIEW

            A view is nothing more than a SQL statement that is stored in the database with an

associated name.A view can contain all rows of a table or select rows from a table. A view

can be created from one or many tables which depends on the written SQL query to create a

view.

SYNTAX

               CREATE VIEW view_name AS

               SELECT column1, column2.....

               FROM table_name

               WHERE [condition];

DELETE VIEW

               DROP VIEW view_name;

CREATE A TABLE

SQL > CREATE TABLE CUSTOMER(CUS_ID IN NUMBER,

CUS_NAME VARCHAR2(10),CITY VARCHAR(10),ORDER_DATES DATE,

PRIMARY KEY(CUS_ID),ADDRESS VARCHAR2(15),SALESMAN_ID

NUMBER);

SQL > INSERT INTO CUSTOMER VALUES(&CUS_ID,’&CUS_NAME’,’&CITY’,

’&ORDER_DATES’,’&ADDRESS’,SALESMAN_ID);

Enter the cus_id                      :101

Enter the cus-name                 :Aaa

Enter the city                          :Chennai

Enter the address                    :15,Nehru street

Enter the order_dates             :15 JAN 17

Enter the salesman_id             :301

CUS_ID    CUS_NAME       CITY          OR_DATE        ADDRESS    SALESMAN_ID
----------- ------------------- ----------- --------------------- ------------------- -----------------------
        101                   Aaa   Chennai            15 JAN 17 15,Nehru street                          301
        102                   Bbb       Delhi             23 JAN 17 20,NRK Nagar                          302
        103                   Ccc        Paris             15 JAN 17 20,Gandhi street                        303

CREATE TABLE2(SALESMAN)

SQL >CREATE TABLE SALESMAN(SALESMAN_ID NUMBER,SALES_NAME

VARCHAR2(10),CITY VARCHAR2(10),COMMISSION_CHARGE FLOAT,

PRIMARY KEY(SALESMAN_ID));

INSERT A DATA INTO TABLE

SQL > INSERT INTO SALESMAN VALUES(&SALEMAN_ID,

’&SALES_NAME’,’&CITY’,COMMISSION_CHARGE);

Enter the salesman_id             :301

Enter the salesman_name        :Fff

Enter the city                          :Chennai

Enter the commission_charge :345.5

SALESMAN_ID        SALESMAN_NAME          CITY   COMMISSION_CHARGE
---------------------- ------------------------------- -------------- -------------------------------------
                     301                                     Fff        Chennai                                          345.5
                     302                                     Ggg            Paris                                           98.5
                     303                                     Hhh       London                                       903.25

CREATE A TABLE3(ORDER)

SQL > CREATE TABLE ORDER(ORDER_NO NUMBER,ORDER_DATE DATE,

            PURC_AMOUNT FLOAT,CUS_ID NUMBER,SALESMAN_ID NUMBER,

CUS_NAME VARCHAR2(10));

SQL > INSERT INTO ORDER VALUES(&ORDER_NO,’&ORDER_DATE’,

&PURC_AMOUNT,&CUS_ID,&SALESMAN_ID,’&CUS_NAME’);

Enter the order_no                  :201

Enter the order_date               :2300

Enter the purc_amount           :15 JAN 17

Enter the cus_id                      :101

Enter the salesman_id             :301

Enter the cus_name                 :Ggg

SQL >SELECT *FROM ORDER;

ORDER_NO ORDER_DATE PURC_AMT CUS_ID SALESMAN_ID CUS_NAME
----------------- -------------------- ---------------- ----------- --------------------- ------------------
               201         15 JAN 17                2300         101                       301                  Aaa
               202         16 JAN 17                5000         102                       302                  Bbb
               203         23 JAN 17            7800.5         103                       303                  Ccc

(i)Write a query to create a view for all salesman with columns salesman_id,name and city.

SQL > CREATE OR REPLACE VIEW SALESMAN_VIEW AS SELECT

SALESMAN_ID,SALESMAN_NAME,CITY FROM SALESMAN.

SALESMAN_ID        SALESMAN_NAME          CITY  
---------------------- ------------------------------- --------------
                     301                                     Fff        Chennai                                                               
         302                                     Ggg            Paris                                        
                     303                                     Hhh       London                            

(ii)Write a query to create a view that shows the number of salesman in each city.

SQL > CREATE OR REPLACE VIEW CITY,COUNT(CITY) FROM SALESMAN;

          CITY    COUNT(CITY)
-------------- -----------------------
     Chennai                              1                               
          Paris                              1         
     London                              1

(iii)Write a query to create a view that find the salesmn who issued orders on either

August 17th aug,2012 (or) October 10th,2012.


SQL > CREATE OR REPLACE VIEW SALESMAN_VIEW AS SELECT *FROM

CUSTOMERS WHERE OREDER_DATE IN (SELECT ORDER_DATE FROM

ORDER WHERE ORDER_DATE=’17 AUG 16’ BETWEEEN

 OREDER_DATE=’20 JAN 17’;

CUS_ID    CUS_NAME       CITY          OR_DATE        ADDRESS    SALESMAN_ID
----------- ------------------- ----------- --------------------- ------------------- -----------------------
        101                   Aaa   Chennai            15 JAN 17 15,Nehru street                          301

(iv) Write a query using view add subquery find the salesman who issued orders on

October 10,2017.

SQL > CREATE OR REPLACE VIEW SALESMAN_VIEW AS SELECT *FROM

CUSTOMER WHERE ORDER_DATE IN(SELECT ORDER_DATE FROM

ORDER WHERE  ORDER_DATE=’15 JAN 17’);

CUS_ID    CUS_NAME       CITY          OR_DATE        ADDRESS    SALESMAN_ID
----------- ------------------- ----------- --------------------- ------------------- -----------------------
        101                   Aaa   Chennai            15 JAN 17 15,Nehru street                          301

(iv) Write a query us create a view that shows  the number of orders in each day.

SQL > CREATE OR REPLACE  VIEW CUSTOMER_VIEW AS SELECT

COUNT(ORDER_DATE),ORDER_DATE

FROM ORDER GROUP BY ORDER_DATE;

COUNT(ORDER_DATE)   ORDER_DATE
--------------------------------- ----------------------
                                     2               15 JAN 17
1                                16 JAN 17

1                                25 JAN 17

(v)Write a query to create a view to keeping track the number of customers ordering number

of  Salesman attached,averge amount of orders and total number of orders in a day.

SQL > SELECT COUNT(CUS_NAME),COUNT(SALESMAN_ID),

AVG(PURC_AMOUNT),FROM ORDER WHERE

ORDER_DATE=’15 JAN 17’ OR ORDER BY CUS_NAME;

(vii)Write specific data  in a view

SQL > UPDATE SALESMAN_VIEW SET PURC_AMOUNT=2000 WHERE

CUS_ID=301;

SALESMAN_ID        SALESMAN_NAME          CITY   PURCHASE_AMOUNT
---------------------- ------------------------------- -------------- ----------------------------------
                     301                                     Fff        Chennai                                      2000

(viii)Write a specific record in a view

SQL > DELETE FROM SALESMAN_VIEW WHERE SALESMAN_ID=302;

SALESMAN_ID        SALESMAN_NAME          CITY  
---------------------- ------------------------------- --------------
                     301                                     Fff        Chennai                                                                 
                     303                                     Hhh       London                            

CREATE A FORCE VIEW

SQL > CREATE OR REPLACE  VIEW FORCE_VIEW AS SELECT *FROM ORDER

WHERE PURC_AMOUNT>2500;

ORDER_NO ORDER_DATE PURC_AMT CUS_ID SALESMAN_ID CUS_NAME
----------------- -------------------- ---------------- ----------- --------------------- ------------------
   202         16 JAN 17                5000         102                       302                  Bbb
   203         23 JAN 17                7800.5         103                    303                  Ccc

DROP IN A VIEW

SQL >DROP VIEW SALESMAN_VIEW;

View is dropped.

EX.NO:8                TO IMPLEMENT THE SEQUENCE AND INDEX

SEQUENCE

            Sequence is a feature supported by some database systems to produce unique values

on demand. Some DBMS like MySQL supports AUTO_INCREMENT in place of Sequence.

AUTO_INCREMENT is applied on columns, it automatically increments the column value

by 1 each time a new record is entered into the table. 

CACHE 

Specify how many values of the sequence the database preallocates and keeps in memory

for faster access. 

SYNTAX

            CREATE SEQUENCE SEQ_NAME

            STRAT WITH VALUE

            INCREMENT BY VALUE

            MAXIMUM VALUE

            CACHE VALUE.

INDEX

            Indexes are special lookup tables that the database search engine can use to speed

up data retrieval. Simply put, an index is a pointer to data in a table.An index helps speed up

SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and

INSERT statements. Indexes can be created or dropped with no effect

on the data.

SYNTAX

               CREATE INDEX index_name ON table_name;


DROP INDEX
               
            An index can be dropped using SQL DROP command.

SYNTAX

                    DROP INDEX index_name;

CREATE A SEQUENCE

            CREATE SEQUENCE SEQ_001

            START WITH 1

            INCREMENT BY 2

            MAXVALUE 10

            CACHE 5;     

CREATE A TABLE

SQL > CREATE TABLE TAB_NAME(ROLLNO NUMBER,NAME

           VARCHAR2(10),TOTAL NUMBER  GRADE VARCHAR);

INSERT A DATA INTO TABLE

SQL > INSERT INTO TAB_01(ROLLNO,NAME,TOTAL,GRADE)VALUES

           (SEQ_001.NEXTVAL,'XXX',495,’FIRST

            CLASS WITH DESTINATION’);

SQL > INSERT INTO TAB_01(ROLLNO,NAME,TOTAL,GRADE)VALUES

            (SEQ_001.NEXTVAL,'YYY',460,’FIRST

            CLASS);

SQL > SELECT *FROM  TAB_01;

  Rollno            Name              Total                                    Grade
---------- ----------------- --------------- ----------------------------------
           1              XXX                495    First class with Destination
          3               YYY                460                               First class
          5               ZZZ                 450                          Second class
          7              AAA                 430                              Third class
          9              BBB                  420                            Fourth class

SQL  > SELECT *FROM SEQ_001;

  Rollno            Name              Total                                    Grade
---------- ----------------- --------------- ----------------------------------
           1              XXX                495    First class with Destination
          3               YYY                460                               First class
          5               ZZZ                 450                          Second class
          7              AAA                 430                              Third class
          9              BBB                  420                            Fourth class

UPDATE A DATA INTO TABLE

SQL > UPDATE SEQ_001 SET TOTAL=435 WHERE ROLLNO=’15US07’;

  Rollno            Name              Total                                    Grade
---------- ----------------- --------------- ----------------------------------
           1              XXX                           495    First class with Destination
          3               YYY                            460                               First class
          5               ZZZ                 450                          Second class
          7              AAA                 435                              Third class
          9              BBB                  420                            Fourth class

1 row updated.

DELETE A DATA INTO TABLE

SQL > DELETE FROM SEQ_001 WHERE ROLLNO=’15US09’;

  Rollno            Name              Total                                    Grade
---------- ----------------- --------------- ----------------------------------
           1              XXX                           495    First class with Destination
          3               YYY                            460                               First class
          5               ZZZ                 450                          Second class
          7              AAA                 430                              Third class

1 row deleted.

CREATE  INDEX

SQL > CREATE INDEX IND_NAME ON TAB_NAME(ROLLNO,NAME);

Index is created

DROP  INDEX

SQL > DROP INDEX IND_NAME ON TAB_NAME(ROLLNO,NAME);

Index is dropped. 

PL/SQL

The PL/SQL programming language was developed by Oracle Corporation in the late

1980s as procedural extension language for SQL and the Oracle relational database.

It is a tool within the Oracle programming environment. SQL* Plus is an interactive tool

that allows you to type SQL and PL/SQL statements at the command prompt.

SYNTAX

               DECLARE
               
               <declarations section>
               
               BEGIN
  
                <executable command(s)>
 
               EXCEPTION
  
                <exception handling>
 
               END;
 
DECLARATIONS

This section starts with the keyword DECLARE. It is an optional section and defines

all variables, cursors, subprograms, and other elements to be used in the program.

EXECUTABLE COMMANDS

This section is enclosed between the keywords BEGIN and END and it is a

mandatory section. It consists of the executable PL/SQL statements of the program. It should

have at least one executable line of code, which may be just a NULL command to indicate

that nothing should be executed.

EXCEPTION HANDLING

This section starts with the keyword EXCEPTION. This section is again optional and

contains exception(s) that handle errors in the program.
                                 


TO FINDTHE FIBONACCI SERIES USING PL/SQL
AIM

            Program to find the FIBONACCI SERIES(simple program) in PL/SQL.

CODINGS

//To find the Fibonacci series in p/sql

Declare

C number;
A number;
B number;
I integer;

Begin

Dbms_output.put_line(‘FIBONACCI SERIES’);
FOR I in 0..5 LOOP
C:=a+b;
A:=b;
B:=c;

Dbms_output.put_line(‘c:’||c);

END LOOP;

END;

SQL>Set serveroutput on

SQL>/

FIBONACCI SERIES
C:=0
C:=1
C:=2
C:=3
C:=5    
 PL/SQL is executed successfully.

EX.NO:9     TO WRITE A PL/SQL CODE WITHIN SQL COMMAND

AIM
            Program to write a PL/SQL code within SQL command.

CREATE A TABLE

SQL> CREATE TABLE TABP1(EMP_NO INT,EMP_NAME VARCHAR2(10),          

STREET VARCHAR2(10),CITY VARCHAR2(10),JOIN_DATE DATE,

DESIGNATION VARCHAR2(10));

Table created.

INSERT A DATA INTO THE TABLE

SQL> INSERT INTO TABP1 VALUES(&EMP_NO,'&EMP_NAME','&STREET',

'&CITY',’&JOIN_DATE’,’&DESIGNATION’);

Enter value for  emp_no         : 101

Enter value for emp_name      : Dhinesh

Enter value for street              : MN street

Enter value for city                 : Sivakasi

Enter value for join_date        :02-jan-17

Enter value for designation     :Civil

SQL> SELECT *FROM TABP1;

    EMP_NO   EMP_NAME   STREET             CITY      JOIN_DATE      DESIGNATION
---------------- -----------------  --------------- ------------- ---------------------  -----------------------
             101           Dhinesh      MN street     Sivakasi           02-JAN-17                     Civil
             102           Dhivahar      PK street    Madurai            15-JAN-17                      EEE
             103                 Arun       VS street    Chennai            10-JAN-17                    M.SC
             104                  lohu       NK street    Sivakasi          02-JAN-17                      ECE
             105               Ram         SK street   Bangalore          07-JAN-17                     ECE

CREATE A TABLE2

SQL>CREATE TABLE TABP2(EMP_NO INT,BASIC INT,HRA INT NULL,DA INT

NULL,TOTAL_DEDUCTION INT,NET_SALARY INT,GROSS_SALARY INT);

Table created.

INSERT A DATA INTO A TABLE

SQL> INSERT INTO TABP2 VALUES(&EMP_NO,&BASIC,

          &HRA,&DA,&TOTAL_DEDUCTION,&NET_SALARY,&GROSS_SALARY);

Enter value for emp_no                      : 101

Enter value for basic                           : 50000

Enter value for hra                              : 300

Enter value for da                               : 100

Enter value for total_deduction          : 400

Enter value for net_salary                   : 4920

Enter value for gross_salary                : 49600

EMP_NO BASIC  HRA DA     TOTAL_DEDUCTION      NET_SALARY      GROSS_SAl
-----------    -------- ------- -------- ----------------------------     --------------------   -------------------        
       101     50000    300      100                                400                     49200              49600
       102     30000  1000      500                              1500                     27000              28500
       103     40000  2000    1000                              3000                     15000              19000
       104     25000  1500    1000                              2500                     20000              22500
       105     30000  1000      550                              1550                     26900              28450



1) Write a PL/SQL code to retrieve the employee name, join_date, and designation

from employee database of an employee whose number is input by the user.

DECLARE

ENO TABP1.EMP_NO%TYPE;

ENAME TABP1.EMP_NAME%TYPE;

JOINDATE TABP1.JOIN_DATE%TYPE;

DESIGNATIONS TABP1.DESIGNATION%TYPE;

BEGIN

ENO:=&ENO;

SELECT EMP_NO,EMP_NAME,JOIN_DATE,DESIGNATION INTO

ENO,ENAME,JOINDATE,DESIGNATIONS FROM TABP1

WHERE EMP_NO=ENO;

DBMS_OUTPUT.PUT_LINE('EMP_NAME:' || ENAME);

DBMS_OUTPUT.PUT_LINE('JOIN_DATE:' || JOINDATE);

DBMS_OUTPUT.PUT_LINE('DESIGNATION:' || DESIGNATIONS);

END;

SQL> @D:/ORACLE/PL1.SQL

 14  /

ENTER VALUE FOR ENO: 101

OLD   7:  ENO:=&ENO;

NEW   7:  ENO:=101;

EMP_NAME:DHINESH

JOIN_DATE:02-JAN-17

DESIGNATION:CIVIL

PL/SQL procedure successfully completed.

2) Write a PL/SQL code to calculate tax for an employee of an organization –XYZ and

to display his/her name & tax, by creating a table under employee database as below.

DECLARE

TAX NUMBER:=0;

NET NUMBER;

ENO TABP2.EMP_NO%TYPE;

NAME TABP1.EMP_NAME%TYPE;

BEGIN

ENO:=&ENO;

SELECT NET_SALARY INTO NET FROM TABP2 WHERE

EMP_NO=ENO;

SELECT EMP_NAME INTO NAME FROM TABP1 WHERE

EMP_NO=ENO;

NET:=NET*12;

IF NET>190000 THEN

NET:=NET-190000;

TAX:=NET*0.2;

END IF;

DBMS_OUTPUT.PUT_LINE('NAME OF THE EMPLOYEE IS '||NAME);

DBMS_OUTPUT.PUT_LINE('TAXABLE AMOUNT IS '||TAX);

END;

SQL> set serveroutput on;

SQL> /

ENTER VALUE FOR ENO: 101

OLD   7:  ENO:=&ENO;

NEW   7:  ENO:=101;

NAME OF THE EMPLOYEE IS DHINESH

TAXABLE AMOUNT IS 80080

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

3)Write a PL/SQL code to display employee number, name and basic of 5   highest paid

employees.

DECLARE

I NUMBER:=0;

CURSOR EC IS SELECT TABP1.EMP_NO,EMP_NAME,BASIC FROM TABP1,TABP2
                                          
WHERE TABP1.EMP_NO=TABP2.EMP_NO ORDER BY GROSS_SALARY

DESC;

R EC%ROWTYPE;

BEGIN

OPEN EC;

LOOP

EXIT WHEN I=5;

FETCH EC INTO R;

DBMS_OUTPUT.PUT_LINE(R.EMP_NO||' '||R.EMP_NAME||' '||R.BASIC);

I:=I+1;

END LOOP;

CLOSE EC;

END;

SQL>set serveroutput on

SQL> @d:/oracle/pl3.sql

SQL>  /

101      Dhinesh           50000
102      Dhivahar         30000
105      Rajesh                         30000
104      lohu                 25000
103      Arun               40000

PL/SQL procedure successfully completed.

4) Write a PL/SQL code to update the salary of employees who earn less than the average

salary

Declare

AVERAGE NUMBER;

BS NUMBER;

GS NUMBER;

DIFF NUMBER;

CURSOR EC IS SELECT *FROM EMP_DET;

RW EC%ROWTYPE;


BEGIN’SELECT AVG(BASIC) INTO AVERAGE FROM EMP_DET;

DBMS_OUTPUT.PUT_LINE(‘THE AVERAGE SALARY IS’||AVERAGE);

OPEN EC;

LOOP

FETCH EC INTO RW;

EXIT WHEN EC%NOTFOUND;

IF RW.BASIC<=AVERAGE

THEN

DIFF:=RW.BASIC-AVERAGE;

UPDATE EMP_DET SET

BASIC=AVERAGE,GROSS_SALARY=GROSS_SALARY+DIFF

INTO BS,GS FROM EMP_DET WHERE EMP_NO=RW.EMP_NO;

DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE NO’||EMP_NO);

DBMS_OUTPUT.PUT_LINE(‘OLD BASIC’||RW.BASIC||

’OLD GROSS_SALARY’||RW.GROSS_SALARY);

DBMS_OUTPUT.PUT_LINE(‘UPDATE NEW BASIC=’||BS||

’NEW GROSS_SALARY=’||GS);

END IF;

END LOOP;

CLOSE EC;

END;

SQL>Set serveroutput on

SQL>/

EMPLOYEE NO:101

OLD BASIC:20000

OLD GROSS_SALARY:15000

UPDATE NEW BASIC:25000

NEW GROSS_SALARY:16000

EX.NO:10                  TO IMPLEMENT THE PROCEDURE IN PL/SQL
              
USING STUDENTDETAILS

AIM

               To implement the PROCEDURE in pl/sql using student details.

PROCEDURE

            Subprograms do not return a value directly, mainly used to perform an action.

SYNTAX

               CREATE [OR REPLACE] PROCEDURE PROCEDURE_NAME

               [(PARAMETER_NAME [IN | OUT | IN OUT] TYPE [, ...])]

               {IS | AS}
              
               BEGIN
              
               < PROCEDURE_BODY >
              
               END PROCEDURE_NAME;

PARAMETERS MODE

IN
               An IN parameter lets you pass a value to the subprogram.
              
                It is a read-only parameter
              
               It is the default mode of parameter passing.
              
               Parameters are passed by reference.
OUT
   An OUT parameter returns a value to the calling program.
    Its value and reference the value after assigning it.
IN OUT
            An IN OUT parameter passes an initial value to a subprogram and returns an updated

value to the caller. It can be assigned a value and its value can be read.
           
DROP PROCEDURE

SYNTAX

               DROP PROCEDURE procedure-name;

CREATE A PROCEDURE

CREATE OR REPLACE PROCEDURE PR_ST01(ROLLNO IN VARCHAR2,

ST_NAME IN VARCHAR2,MATHS IN

INTEGER,PHYSICS IN INTEGER,CHEMISTRY IN INTEGER)

IS

P_CUTOFF BHUV_ST01.CUT_OFF%TYPE;

P_GRADE BHUV_ST01.GRADE%TYPE;

BEGIN

P_CUTOFF:=(MATHS/2)+(PHYSICS/4)+(CHEMISTRY/4);

IF P_CUTOFF >=190

THEN

P_GRADE:='FIRST CLASS WITH DESTINATION';

ELSE IF P_CUTOFF >=180

THEN

P_GRADE:='FIRST CLASS';

ELSE IF P_CUTOFF >=150

THEN

P_GRADE:='SECOND CLASS';

ELSE

P_GRADE:='FAIL';

END IF;

END IF;

END IF;

INSERT INTO BHUV_ST01 VALUES(ROLLNO,ST_NAME

,MATHS,PHYSICS,CHEMISTRY,P_CUTOFF,P_GRADE);

END;

SQL> /

Procedure created.

SQL> EXEC PR_ST01('15US03','EEE',176,156,190);

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> SELECT *FROM BHUV_ST01;

ROLLNO  NAME   MAT   PHY  CHEMIS    CUT_OFF                                           GRADE
------------- ---------- -------- -------- ------------ ---------------- -----------------------------------------
     15us01        xxx      190     180           198                190              First class with destination
     15us02       yyy       178     198           167                180                                          First class
     15us03       eee        176     156           190                175                                    Second class
     15us04       rrr         178     156           197                177                                     Second class
     15us05       kkk       178     156               0                128                                                   Fail

PROCEDURE UPDATE

CREATE OR REPLACE PROCEDURE UP_PRO(CUT_OFF INTEGER,

GRADE VARCHAR2)

IS

BEGIN

UPDATE BHUV_ST01 SET GRADE='THIRD CLASS' WHERE CUT_OFF<150;

END;

SQL> /

PROCEDURE CREATED.

SQL> EXEC UP_PRO(128,'THIRD CLASS');

PL/SQL PROCEDURE EXEDUTED SUCCESSFULLY .

SQL> SELECT *FROM BHUV_ST01;

ROLLNO  NAME   MAT   PHY  CHEMIS    CUT_OFF                                           GRADE
------------- ---------- -------- -------- ------------ ---------------- -----------------------------------------
     15us01        xxx      190     180           198                190               First class with destination
     15us02       yyy       178     198           167                180                                          First class
     15us03       eee        176     156           190                175                                    Second class
     15us04       rrr          178    156            197                177                                    Second class
     15us05       kkk        178    156                0                128                                       Third class

PROCEDURE USING DELETE

CREATE OR REPLACE PROCEDURE DEL_PRO(ROLLNO1 IN VARCHAR2)

IS

BEGIN

DELETE FROM BHUV_ST01 WHERE ROLLNO=ROLLNO1;

END;

/

PROCEDURE CREATED.

SQL> EXEC DEL_PRO('15US04');

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> SELECT *FROM BHUV_ST01;

ROLLNO  NAME   MAT   PHY  CHEMIS    CUT_OFF                                           GRADE
------------- ---------- -------- -------- ------------ ---------------- -----------------------------------------
     15us01        xxx      190     180           198                190               First class with destination
     15us02       yyy       178     198           167                180                                          First class
     15us03       eee        176     156           190                175                                    Second class
     15us05       kkk        178    156                0                128                                       Third class

EX.NO:11              TO IMPLEMENT FUNCTION IN PL/SQL

AIM

            To implement the FUNCTION in pl/sql using EB bill.

FUNCTIONS

Subprograms return a single value, mainly used to compute and return a value.

SYNTAX

            CREATE FUNCTION FUNC_NAME[PARAMETER…]

            RETURN DATATYPE

            {IS/AS}

            BEGIN

            {FUNCTION BODY}

            END FUNC_NAME;

CREATE A FUNCTION

 //Create a function using EB bill

 CREATE OR REPLACE FUNCTION CALC_01(BILLNO1 IN INTEGER,

 TOTAL OUT INTEGER)

 RETURN INTEGER

 IS

 PCUR EB_BILL.CUR_READ%TYPE;

 PPREV EB_BILL.PREV_READ%TYPE;

 AMT INTEGER;

 BEGIN

 SELECT CUR_READ,PREV_READ INTO PCUR,PPREV FROM EB_BILL WHERE  

 BILLNO=BILLNO1;

 TOTAL:=PCUR-PPREV;

 IF TOTAL<=100

 THEN

 AMT:=TOTAL*3;

 ELSE IF TOTAL<=200

 THEN

 AMT:=TOTAL*5;

 ELSE

 AMT:=TOTAL*10;

 END IF;

 END IF;

 RETURN AMT;

 END;

 /

Function created.

FUNCTION CALLING

 DECLARE

 BILLNO1 EB_BILL.BILLNO%TYPE;

 NAME1 EB_BILL.NAME%TYPE;

 TOTAL EB_BILL.CUR_READ%TYPE;

 AMT NUMBER(7,2);

 BEGIN

 BILLNO1:=&BILLNO1;

 SELECT NAME INTO NAME1 FROM EB_BILL WHERE BILLNO=BILLNO1;

 AMT:=CALC_01(BILLNO1,TOTAL);

 DBMS_OUTPUT.PUT_LINE('EB BILL');

 DBMS_OUTPUT.PUT_LINE('-------');

 DBMS_OUTPUT.PUT_LINE('NAME:'||NAME1);

 DBMS_OUTPUT.PUT_LINE('BILLNO:'||BILLNO1);

 DBMS_OUTPUT.PUT_LINE('TOTAL'||TOTAL||'UNIT');

 DBMS_OUTPUT.PUT_LINE('AMT:'||AMT);

 END;

SQL>  SET SERVEROUTPUT ON

SQL >/

ENTER VALUE FOR BILLNO1: 1234

OLD   7: BILLNO1:=&BILLNO1;

NEW   7: BILLNO1:=1234;

EB bill
--------
Name:xxx

Billno:1234

Total-27unit

amt:-81

PL/SQL procedure successfully completed.

EX.NO:12                  TO IMPLEMENT THE PACKAGE IN PL/SQL

AIM

            To implement the PACKAGE in pl/sql.

PACKAGE

            Package is a group of related functions,procedures and cursors.

            Package is like a library onv=ce return stored in the database we can used by many

application.

The package has 3 parts
o   Package Creation
o   Package Definition
o   Package Access

SYNTAX

CREATE [OR REPLACE]  PACKAGE PAC_NAME

{IS/AS}

PACKAGE SPECIFICATION;

END PAC_NAME;

PACKAGE DEFINITION

CREATE A PACKAGE 

//Function and Procedure within Package

  CREATE PACKAGE PACK1

  PROCEDURE RECT(L IN NUMBER,B IN NUMBER,AREA OUT NUMBER);

  FUNCTION SQUARE(A IN NUMBER)

  FUNCTION AREA_CIR(R IN NUMBER) RETURN NUMBER;

  FUNCTION FACT(X IN NUMBER) RETURN NUMBER;

  PROCEDURE CEILING(N IN NUMBER,CEIL OUT NUMBER);

  END;

  /

Package created.

CREATE A PACKAGE BODY

//AREA OF RECTANGLE USING PROCEDURE

CREATE OR REPLACE PACKAGE BODY PACK1 AS

PROCEDURE RECT(L IN NUMBER,B IN NUMBER,AREA OUT NUMBER)

AS

BEGIN

AREA:=L*B;

END RECT;

//AREA OF SQUARE USING FUNCTION

FUNCTION SQUARE(A IN NUMBER)

RETURN NUMBER

AS

BEGIN

RETURN A*A;

END SQUARE;

//AREA OF CIRCLE USING FUNCTION

FUNCTION AREA_CIR(R IN NUMBER)

RETURN NUMBER

IS

AREA_CIRCLE NUMBER(7,2);

PI NUMBER(5,2);

BEGIN

PI:=22/7;

AREA_CIRCLE:=PI*R*R;

RETURN AREA_CIRCLE;

END AREA_CIR;

//FACTORIAL USING FUNCTION

FUNCTION FACT(X IN NUMBER)

RETURN NUMBER

IS

F NUMBER;

BEGIN

IF X=0 THEN

F:=1;

ELSE

F:=X*FACT(X-1);

END IF;

RETURN F;

END FACT;

//CEILING USED PROCEDURE

PROCEDURE CEILING(N IN NUMBER,CEIL OUT NUMBER)

AS

BEGIN

CEIL:=N+1.0;

END CEILING;

END PACK1;

 /
package body created.

PACKAGE DEFINTION

DECLARE

AREA1 NUMBER(7,2);

AREA_SQ NUMBER(7,2);

AREA_C NUMBER(7,2);

FACTORIAL NUMBER(7,2);

CE NUMBER(1);

BEGIN

PACK1.RECT(2,3,AREA1);

DBMS_OUTPUT.PUT_LINE('AREA OF RECTANGLE(L=2,B=30):'||AREA1);

AREA_SQ:=PACK1.SQUARE(2);

DBMS_OUTPUT.PUT_LINE('SQUARE(A=2):'||AREA_SQ);

AREA_C:=PACK1.AREA_CIR(2);

DBMS_OUTPUT.PUT_LINE('AREA OF CIRCLE(R=2):'||AREA_C);

FACTORIAL:=PACK1.FACT(5);

DBMS_OUTPUT.PUT_LINE('FACTORIAL(X=5):'||FACTORIAL);


PACK1.CEILING(4.5,CE);

DBMS_OUTPUT.PUT_LINE('CEILING(4.5):'||CE);

END;
 /

AREA OF RECTANGLE(L=2,B=3):6

SQUARE (A=2):4

AREA OF CIRCLE(R=2):12.56

FACTORIAL(X=5):120

CEILING(N=4.5):5

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

EX.NO:13              TO IMPLEMENT THE TRIGGERS
    USING EMPLOYEE DETAILS

AIM

            To implement the TRIGGER using update command in pl/sql.

TRIGGER

            Triggers are stored programs, which are automatically executed or fired when some

events occur. 

SYNTAX

Create [or replace ] trigger trigger_name

{before | after |instead of }

{insert [or] | update [or] | delete }

[of col_name]

On table_name

[referencing old as o new as n]

[for each row]

When (condition)

Declare

                        <Declaration-statements>

Begin

                        <Executable-statements>

Exception
                        <Exception-handling-statements>
End;

CREATE A TABLE

SQL>CREATE TABLE TRITAB02(EMP_ID INT,EMP_NAME VARCHAR2(10),

DEPARTMENT VARCHAR2(10),SALARY INT);

Table created.

INSERT A DATA INTO TABLE

SQL>Insert into tritab02(&emp_id,’&emp_name’,’&department’,&salary);

Enter the emp_id        :101

Enter the emp_name   :Dhinesh

Enter the department  :Mechanical

Enter the salary           :70000

SQL> select *from tritab02;

  EMP_ID EMP_NAME   DEPARTMENT     SALARY
------------- -------------------- --------------------- --------------
           101              Dhinesh            Mechanical        20000
           102            Dhivahar                         CS        20000
           103                 Selva                        Civil       30000
           104                 Lohu                         ECE       30500
           105                 Ajith                  Electrical       25030

CREATE TRIGGER USING UPDATE COMMAND

 CREATE OR REPLACE TRIGGER TR_EMP

 BEFORE UPDATE ON TRITAB02

 FOR EACH ROW

 WHEN(NEW.EMP_ID>0)

 DECLARE

 SAL_DIF NUMBER;

 BEGIN

 SAL_DIF:=:NEW.SALARY-:OLD.SALARY;

 DBMS_OUTPUT.PUT_LINE('OLD SALARY:'||:OLD.SALARY);

 DBMS_OUTPUT.PUT_LINE('NEW SALARY:'||:NEW.SALARY);

 DBMS_OUTPUT.PUT_LINE('SALARY_DIF:'||SAL_DIF);

 END;

Trigger created.

SQL> update tritab02 set salary=10000 where emp_name='Dhinesh';

1 row updated.

SQL> set serveroutput on;

SQL> /

old salry:20000

new salry:200000

salary difference:18000

TRIGGER USING INSERT COMMAND

CREATE OR REPLACE TRIGGER TR_INS

AFTER INSERT ON TT

FOR EACH ROW

BEGIN

INSERT INTO TT1(ID,NAME) VALUES(:NEW.ID,:NEW.NAME);

END;

SQL> @D:/ORACLE/TRIINS.SQL

 SQL>  /

TRIGGER CREATED.

SQL> INSERT INTO TT VALUES(105,'LOHU');

1 row created.


SQL> SELECT *FROM TT;

     ID    NAME
  ---------- ----------
       105 Lohu
       103 Dhinesh

TRIGGER USING DELETE COMMAND

CREATE OR REPLACE TRIGGER TR_DEL

AFTER DELETE ON TT

FOR EACH ROW

BEGIN

DELETE FROM TT1;

END;

SQL> @D:/ORACLE/TRIINS.SQL

 SQL>  /

Trigger created.

SQL> delete from tt;

1 row deleted.

SIMPLE FORM

AIM

            To create a Simple Form.

Step 1: Start→Developer R2.1 and select Form Builder

Step 2:Immediately,you will see the window for Developer/2000 Form Builder for windows

95/NT with the sub-window for Welcome to Form Builder.



Step 3:Select ‘Use the Datablock Wizard’ and click OK—This is  the easiest method to

design a New form.

Step 4:You will now see the Welcome to the  Datablock Wizard Window.Click Next to

proceed.

Step 5:You will now see the window for the Datablock Wizard.Select Table or View as in

the Figure and click Next. 



Step 6:You will now see the window that prompts you to select a table or view—your form

will be created based on this selection.Since no table or view is being shown,click on browse

to look at the list of tables and views in your database.

Step 7:Once you click browse,the connect window will appear.Type  in your

username,password and database to connect to the database.

Step 8:You will now see the table’s window.Select current users and tables and click OK.

Step 9:You will now see the list of table’s created in your databse.Select Sude01 and click

OK.

Step 10:You will now see your selected table and its available columns on your screen.Click

on the single right arrow to select the first column to be shown in your form;in this case the

Rollnocolumn.You will now see this column under the database items selected


sub-window.


Step 11:To move the rest of the columns,simply click on the double arrow and this will select


your columns in the databse items.


Step 12:You will now see the congratulations window.Make sure that “Create the data

block,and then call the Layout Wizard” is selected and click on Finish.

Step 13:You will now see the Layout Wizard,prompting you to select the items that you

would like to show in the form.Make sure that the data block selected is students and then

click the double right arrow to move all the columns of the student block from the available

items to the displayed items.Click on the Next to continue.

Step 14:The window with the prompt for the height and width of the items will appear.Click

Nexr to accept the default values.


Step 15:The Layout Wizard will now prompt you to select the layout or view style of your

block.Select Form and click Next.


Step 16:The Layout Wizard will now prompt you to select a title for the form that you are

creating.Type in students Records.Click Next to continue.


Step 17:Congratulations! You have now successfully created your first form.Click Finish

toView your form.
Step 18:You will now see the canvas view of the form that you have created.You can now

add various objects like push buttons,combo boxes and radio buttons to your form to make it

more graphical and user friendly.We will do this in the next lesson.

Step 19:You can now format the form manually.Click on th frame to select it.Then the drag

the frame to make it bigger.

Step 20:You can now space out the data fields to 

make your form more visually

appealing.You can do this by simply selecting the data field and dragging it to your

desired area.


EX.NO:1                                            SIMPLE REPORT

AIM

            To create a Simple Report.

Step 1:Invoke the Report Builder,

Click Start→All programs→Oracle Report 9i→Report Builder.

Click OK to create the report using Report Wizard.


Step 2:Click Next to proceed further.Select tabular option.Then Click Next


Step 3:Click Next and type the necessary query.

Step 4:Click next→Connect to the databse.


Step 5:Click >> button to move all columns from available fields to Displayed Field

Box→Next.


Step 6:Accept the default and click Next.


Step 7:Accept the default and click Next.


Step 8:Accept the default and click Next.This completes the design of Tabular Type

Report.Click Finish to come out from Report Wizard to display the result.



Step 9:Save the report in the desired location by clicking File→Save.To Save our Report as

Pdf file then go to the web wizard and then select the pdf znd then click save.



EX.NO:2                                            BREAK REPORT

AIM

            To create a Break Report.

Step 1:You will now see the first screen of the Report Wizard.Type in “List of students by

Major” in the Title box.Next,select the radio button for Group Above in order to create breaks

after record groups for each Major.Now,click Next.


Step 2:You will now see your SQL statement again.You can edit your statement here if you

choose to.At this time we will use the query that we had entered earlier.Click Next.

Step 3:You will now be prompted to select the field that you would like to designate as group

fields.You will find that Oracle has already selected Major.Now,select the next tab,Fields.


Step 4:You will now be asked to select the fields that you will display in your report.We

would like to see all the fields,so click on the double right facing arrows to select all the

fields and click Next.


Step 5:You will noe be prompted to select fields for which you would like to calculate

totals.Let us assume that we have been asked to provide the total number of students in each

major and also the Grand total of the number of students.To do this ,select Rollno,and click

on Count.

Step 6:Your Screen should look like with Count(Rollno) in the Total Column.


Step 7:You can now modify your labels and their width.In this case we have put a colon and

a space after Major and have changed the label for CountRollnoperReport to “Number Of

Students:”and click Next.



Step 8:The final modification involves selecting as appropriate template for the report.In this

case,we will select Corporate2 from the list provided.You are free to select any template of

your choice.Click Finish.


Step 9:Your  report should now look like the one in figure.


EX.NO:3                                MATRIX REPORT

Step 1:Invoke Report Builder.

Step 2:Select Create the report use the Report Wizard Option→Click OK→Next→Select

Matrix type→click Next.


Step 3:Click Next→Type the necessary query.


Step 4:Transfer corresponding field from Available Fields to Matrix Row Field→Click Next.


 Step 5:Transfer the corresponding field from Available Fields→Click Next.


Step 6:Transfer corresponding field from available fields to Matrix Cell Field→Click Next.


Step 7:Click Next→Change the label corresponding to our need and delete the label if it is

not necessary.

Step 8:Click Next twice followed by Finish to display the Report.


EX.NO:4                    MASTER DETAIL REPORT

AIM

            To design the Master/Detail Report.

PROCEDURE

Step 1:Invoke the Report Designer and select the option BUILD A NEW REPORT

MANUALLY.

Step 2:In that create a query named Q_MASTER with following Select statement.

SQL>SELECT *FROM STUDE01;

Step 3:Create a second query approximately two inches to the right of Q_MASTER.Name the

query as  Q_DETAIL,and enter the SELECT statement as follows,

SQL>SELECT *FROM BILL_01;


Step 4:Select the Data Lik tool and then create like between two groups and then edit Data Link


property by double clicking it,if necessary.


Step 5:If you need create a Summary Column in Q_DETAIL group for serial number.The

Summary Column must be the first column in the group.Change its properties as mentioned

below:

Name:Sno

Function:Count

Source:Product

Reset At:Q_MASTER

Data type:Number

Width:3

Step 6:Select Tools→DEFAULT LAYOUT.Click OK.

Step 7:If  necessary,align the fields and labels,change the label text and their fonts by editing

its layout.


Step 8:We need to inform Oracle Report that if the texts is larger than the display length,then

the size of the field should be expanded vertically(del_addr property→General layout

tab→Verticals sizing→Expand).

Step 9:Click Apply and OK.



SIMPLE LOGIN FORM USING SQL REPORT

AIM

          To Create a Simple Login form using SQL Report.

PROCEDURE

Step 1:Open ORACLE SQL*PLUS and create the table.

Step 2:Insert a record into the table.

Step 3: Start→Developer R2.1 and select Form Builder

Step 4:Immediately,you will see the window for Developer/2000 Form Builder for windows

95/NT with the sub-window for Welcome to Form Builder.


Step 5: Rename the MODULE  as LOGIN.



Step 6: Right Click on Data Blocks and click on Layout Editor. Now, you see the Editing    

Window.

Step 7: Pick a TEXT_ITEM tool, and draw the two text box on the window.

Step 8: Right click on Text_Item1→Property palette, And change the Name into USER_NAME.

Step 9: Right click on Text_Item2→Property palette, And change the Name into PASSWORD.

Step 10: Pick and draw the two text boxes Fill with USER_NAME and PASSWORD.

Step 11: Pick the Button tool and draw two buttons.

Step 12: Right Click on Button1→Property palette, Change the Name, Label as LOGIN. And 
Change Default Button into Yes.

Step 13: Right Click on Button1→Property palette, Change the Name, Label as CANCEL. And 
Change Default Button into Yes.


Steps 14: Use the other Rectangle, Oval, etc tools to modify your Login Form.


Step 15: Right Click on LOGIN Button→Smart Triggers→WHEN BUTTON PRESSED.

Step 16: Now PL/SQL window was opened, Here write the ActionScript for the Login Button when it pressed.


Step 17: After finished the ActionScript, Connect with the Oracle Server by pressing Ctrl+J


Step 18: Save and Compile the Program.

Step 19: In Menu bar, Program→Run Form or press Ctrl+R to execute the form.

 Step 20: After execution, the Login form window is opened.  In this, Enter the User Name and Password and click LOGIN OR CANCEL.



No comments:

Post a Comment

Compiler Construction Tool - ANTLR

ANTLR A set of language translation tools (formerly PCCTS).       It is one of the "Compiler Construction Tool for Programming l...