ORACLE - Complete Tutorial for Beginners

DATA DEFINITION LANGUAGE COMMANDS

COMMANDS

SQL> create table stud (sname varchar2(30), sid varchar2(10), sage number(2), sarea varchar2(20));

Table created.

SQL> desc stud;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- --------------------------------
 SNAME                                                          VARCHAR2(30)
 SID                                                                 VARCHAR2(10)
 SAGE                                                             NUMBER(2)
 SAREA                                                          VARCHAR2(20)

SQL>alter table stud modify ( sage number(10));

Table altered.


SQL> alter table stud add ( sdept varchar2(20));

Table altered.

SQL> desc stud;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- --------------------------------
 SNAME                                                          VARCHAR2(30)
 SID                                                                 VARCHAR2(10)
 SAGE                                                             NUMBER(10)
 SAREA                                                          VARCHAR2(20)
 SDEPT                                                           VARCHAR2(20)

SQL> alter table stud drop ( sdept varchar2(20));

Table altered.


SQL> desc studs;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 SNAME                                                          VARCHAR2(30)
 SID                                                                 VARCHAR2(10)
 SAGE                                                             NUMBER(10)
 SAREA                                                          VARCHAR2(20)

SQL> truncate table studs;

Table truncated.

SQL> desc studs;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 SNAME                                                          VARCHAR2(30)
 SID                                                                 VARCHAR2(10)
 SAGE                                                             NUMBER(10)
 SAREA                                                          VARCHAR2(20)
 SDEPT                                                           VARCHAR2(20)

SQL> drop table studs;

Table dropped.




DATA MANIPULATION LANGUAGE COMMANDS

DESCRIPTION
THE ORACLE TABLE – DUAL
            Dual is a small oracle table which consists of only one row and one column and contains the value X in that column.
INSERT
            This command is used to insert values into the table.
SELECT
            This command is used to display the contents of the table or those of a particular column.
RENAME
            This command renames the name of the table.
ARITHMETIC OPERATIONS
            Various operations such as addition, multiplication, subtraction and division can be performed using the numbers available in the table.
DISTINCT
            This keyword is used along with select keyword to display unique values from the specified column. It avoids duplicates during display.
ORDER BY CLAUSE
            The order by clause arranges the contents of the table in ascending order (by default) or in descending order (if specified explicitly) according to the specified column.
CONCATENATION OPERATOR
            This combines information from two or more columns in a sentence according to the format specified.
LOGICAL OPERATORS
1        AND : The oracle engine will process all rows in a table and displays the result only when all of the conditions specified using the AND operator are specified.
2        OR : The oracle engine will process all rows in a table and displays the result only when any of the conditions specified using the OR operators are satisfied.
3        NOT : The oracle engine will process all rows in a table and displays the result only when none of the conditions specified using the NOT operator are specified.
4        BETWEEN : In order to select data that is within a range of values, the between operator is used. (AND should be included)
PATTERN MATCH
1        LIKE PREDICATE : The use of like predicate is that it allows the comparison of one string value with another string value, which is not identical. This is achieved by using wildcard characters which are % and _. The purpose of % is that it matches any string and _ matches any single character.
2        IN AND NOT IN PREDICATE : The arithmetic operator = compares a single value to another single value. In case a value needs to be compared to a list of values then the in predicate is used.The not in predicate is the opposite of the in predicate. This will select all the rows whose values do not match all of the values in the list.
NUMERIC FUNCTIONS
1        ABS: It returns the absolute value of ‘n’.
2        POWER:  It returns m raised to nth power. n must be an integer else an error is returned.
3        ROUND: It returns n rounded to m places right of the decimal point. If m is omitted, n is rounded to zero places. m must be an integer.
4        SQRT: It returns square root of n. n should be greater than zero.
STRING FUNCTIONS
1        LOWER: It returns char with letters in lower case.
2        INITCAP: It returns char with the first letter in upper case.
3        UPPER:  It returns char with all letters forced to upper case.
4        SUBSTR: It returns a portion of char beginning at character m, exceeding up to n characters. If n is omitted result is written up to the end character. The 1st position of char is one.
5        LENGTH: It returns the length of char
6        LTRIM: It removes characters from the left of char with initial characters removed up to the 1st character not in set.
7        RTRIM: It returns char with final characters removed after the last character not in the set. Set is optional. It defaults to spaces.
8        LPAD: It returns char1, left padded to length n with the sequence of characters in char2. char2 defaults to blanks.
9        RPAD: It returns char1, right padded to length n with the characters in char2, replicated as many times as necessary. If char2 is omitted, it is padded with blanks.
AGGREGATE FUNCTIONS
1        AVG (N): It returns average value of n ignoring null values.
2        MIN (EXPR): It returns minimum value of the expression.
3        COUNT (EXPR): It returns the number of rows where expression is not null.
4        COUNT (*): It returns the number of rows in the table including the duplicates and those with null values.
5        MAX (EXPR): It returns maximum value of the expression.
6        SUM(N): It returns sum of values of n.
CONVERSION FUCTIONS
1        TO_NUMBER(CHAR): It converts the char value containing a number to a value of number data type.
2        TO_CHAR(N,FMT): It converts a value of number data type to a value of char data type, using the optional format string. It accepts a number n and a numeric format fmt in which the number has to appear. If fmt is omitted, n is converted to a char value exactly long enough to hold significant digits.
3        TO_CHAR(DATE, FMT): It converts a value of data type to char value. It accepts a date as well as the format in which the date has to appear. Fmt must be a date format. If fmt is omitted, date is the default date format.
DATE FUNCTIONS

1        SYSDATE : The sysdate is a pseudo column that contains the current date and time. It requires no arguments when selected from the table dual and returns the current date.
2        ADD_MONTHS(D,N): It returns date after adding the number of months specified with the function.
3        LAST_DAY(D): It returns the last date of the month specified with the function
4        MONTHS_BETWEEN(D1,D2): It returns number of months between D1 and D2.
5        NEXT_DAY(DATE, CHAR): It returns the date of the first week day named by char . char must be a day of the week.
GROUP BY CLAUSE
            The group by clause is another section of the select statement. This optional class tells oracle to group rows based on distinct values that exists for specified columns.
HAVING CLAUSE
            The having clause can be used in conjunction with the group by clause. Having imposes a condition on the group by clause, which further filters the groups created by the group by clause.
SET OPERATIONS
1        UNION CLAUSE: Multiple queries can be put together and their output combined using the union clause. The union clause merges the output of two or more queries into a single set of rows and columns.
2        INTERSECT CLAUSE: Multiple queries can be put together and their output can be combined using the intersect clause. The intersect clause outputs only rows produced by both the queries intersected. The output in an intersect clause will include only those rows that are retrieved by both the queries.
JOIN OPERATIONS
1        INNER JOIN/ NATURAL JOIN/ JOIN: It is a binary operation that allows us to combine certain selections and a Cartesian product into one operation.
2        OUTER JOIN: It is an extension of join operation to deal with missing information.
            Left Outer Join: It takes tuples in the left relation  that did not match with any tuple in the right relation, pads the tuples with null values for all other attributes from the right relation and adds them to the result of the natural join.
            Right Outer Join: It takes tuples in the right relation  that did not match with any tuple in the left relation, pads the tuples with null values for all other attributes from the left relation and adds them to the result of the natural join.
            Full Outer Join: It combines tuples from both the left and the right relation and pads the tuples with null values for the missing attributes and them to the result of the
natural join.

COMMANDS

CREATION OF TABLE

SQL>create table stud (sname varchar2(30), sid varchar2(10), sage number(10), sarea varchar2(20), sdept varchar2(20));

Table created.

INSERTION OF VALUES INTO THE TABLE
SQL> insert into stud values ('ashwin',101,19,'anna nagar','aeronautical');

1 row created.

SQL> insert into stud values ('bhavesh',102,18,'nungambakkam','marine');

1 row created.

SQL> insert into stud values ('pruthvik',103,20,'anna nagar','aerospace');

1 row created.

SQL> insert into stud values ('charith',104,20,'kilpauk','mechanical');
1 row created.

SQL> select * from stud;
SNAME                           SID        SAGE    SAREA                   SDEPT
------------------------------ ---------- --------- -------------------- --------------------
ashwin                             101               19  anna nagar                aeronautical
bhavesh                           102               18  nungambakkam        marine
pruthvik                           103               20  anna nagar               aerospace
charith                             104               20  kilpauk                     mechanical
RENAMING THE TABLE ‘STUD’
SQL> rename stud to studs;

Table renamed.

ARITHMETIC OPERATION

SQL> select sname, sid+100 "stid" from studs;

SNAME                               stid
------------------------------ ---------
ashwin                                201
bhavesh                              202
pruthvik                              203
charith                                204

CONCATENATION OPERATOR

SQL> select sname || ' is a ' || sdept || ' engineer. ' AS  "PROFESSION" from studs;

PROFESSION
-------------------------------------------------------------------
ashwin is a aeronautical engineer.
bhavesh is a marine engineer.
pruthvik is a aerospace engineer.
charith is a mechanical engineer.

DISPLAY ONLY DISTINCT VALUES

SQL> select distinct sarea from studs;

SAREA
--------------------
anna nagar
kilpauk
nungambakkam

USING THE WHERE CLAUSE
SQL> select sname,sage from studs where sage<=19;

SNAME                               SAGE
------------------------------ ---------
ashwin                                 19
bhavesh                               18
BETWEEN OPERATOR
SQL> select sname,sarea, sid from studs where sid between 102 and 104;

SNAME                             SAREA                  SID
------------------------------ -------------------- ----------
bhavesh                           nungambakkam           102
pruthvik                           anna nagar                  103
charith                             kilpauk                        104
IN PREDICATE
SQL> select sname,sarea , sid from studs where sid in(102,104);

SNAME                          SAREA                SID
------------------------------ -------------------- ----------
bhavesh                        nungambakkam         102
charith                          kilpauk                      104

PATTERN MATCHING
SQL> select sname, sarea from studs where sarea like '%g%';

SNAME                          SAREA
------------------------------ --------------------
ashwin                          anna nagar
bhavesh                        nungambakkam
pruthvik                        anna nagar

LOGICAL AND OPERATOR
SQL> select sname ,sid from studs where sid>102 and sarea='anna nagar';

SNAME                           SID
------------------------------ ----------
pruthvik                           103

LOGICAL OR OPERATOR
SQL> select sname ,sid from studs where sid>102 or sarea='anna nagar';

SNAME                          SID
------------------------------ ----------
ashwin                             101
pruthvik                          103
charith                             104
 NOT IN PREDICATE
SQL> select sname, sid from studs where sid not in(102,104);

SNAME                          SID
------------------------------ ----------
ashwin                           101
pruthvik                         103

UPDATING THE TABLE
SQL> alter table studs add ( spocket varchar2(20) );

Table altered.

SQL> update studs set spocket=750 where sid=101;

1 row updated.

SQL> update studs set spocket=500 where sid=102;

1 row updated.

SQL> update studs set spocket=250 where sid=103;

1 row updated.

SQL> update studs set spocket=100 where sid=104;

1 row updated.

SQL> select * from studs;


SNAME                          SID          SAGE  SAREA                SDEPT
------------------------------ ---------- --------- -------------------- --------------------
SPOCKET
--------------------

ashwin                    101            19     anna nagar                aeronautical    
750
bhavesh                  102            18    nungambakkam         marine
500
pruthvik                  103            20   anna nagar                 aerospace
250
charith                    104            20    kilpauk                      mechanical
100

AGGREGATE FUNCTIONS
SQL> select avg( spocket ) result from studs;

   RESULT
---------
    400

SQL>  select min(spocket) result  from studs;

RESULT
--------------------
100

SQL> select count(spocket) result from studs;

   RESULT
---------
        4

SQL> select count(*) result from studs;

   RESULT
---------
        4

SQL> select count(spocket) result from studs where sarea='anna nagar';

   RESULT
---------
        2
SQL> select max(spocket) result from studs;

RESULT
--------------------
750

SQL> select sum(spocket) result from studs;

   RESULT
---------
     1600

NUMERIC FUNCTIONS
SQL> select abs(-20) result from dual;

   RESULT
---------
       20

SQL> select power (2,10) result from dual;

   RESULT
---------
     1024

SQL> select round(15.359,2) result from dual;

   RESULT
---------
    15.36

SQL> select sqrt (36) result from dual;

   RESULT
---------
        6

STRING FUNCTIONS

SQL> select lower('ORACLE') result from dual;

RESULT
------
oracle

SQL> select upper('oracle') result from dual;

RESULT
------
ORACLE

SQL> select initcap('Oracle') result from dual;

RESULT
------
Oracle
SQL> select substr('oracle' ,2 ,5) result from dual;  

RESULT
-----
racle

SQL> select lpad('oracle',10,'#') result from dual;

RESULT
----------
####oracle

SQL> select rpad ('oracle',10,'^') result from dual;

RESULT
----------
oracle^^^^

CONVERSION FUNCTIONS

SQL> update studs set sage=to_number(substr(118,2,3));

4 rows updated.

SQL> select * from studs;

SNAME                          SID             SAGE SAREA                SDEPT
------------------------------ ---------- --------- -------------------- --------------------
SPOCKET
--------------------
ashwin                         101               18 anna nagar           aeronautical
750
bhavesh                        102               18 nungambakkam         marine
500
pruthvik                       103               18 anna nagar           aerospace
250
charith                        104               18 kilpauk              mechanical
100

SQL> select to_char( 17145, '099,999')  result from dual;

RESULT
--------
 017,145

SQL> select to_char(sysdate,'dd-mon-yyyy')  result from dual;

RESULT
-----------
16-jul-2008

DATE FUNCTIONS
SQL> select sysdate from dual;

SYSDATE
---------
16-JUL-08

SQL> select  sysdate,add_months(sysdate,4) result from dual;

SYSDATE   RESULT
---------           ---------
16-JUL-08 16-NOV-08

SQL> select sysdate, last_day(sysdate) result from dual;

SYSDATE   RESULT
---------          ---------
16-JUL-08 31-JUL-08

SQL> select  sysdate, next_day(sysdate,'sunday') result from dual;

SYSDATE   RESULT
---------         ---------
16-JUL-08   20-JUL-08

SQL> select months_between('09-aug-91','11-mar-90') result from dual;

   RESULT
     ---------
 16.935484

GROUP BY CLAUSE
SQL> select sarea, sum(spocket) result from studs group by sarea;

SAREA                          RESULT
--------------------             ------------
anna nagar                        1000
nungambakkam                  500
kilpauk                               100

HAVING CLAUSE
SQL> select sarea, sum(spocket) result from studs group by sarea having spocket<600;

SAREA                          RESULT
--------------------             ------------
nungambakkam                  500
kilpauk                               100

DELETION
SQL> delete from studs where sid=101;

1 row deleted.

SQL> select * from studs;

SNAME                          SID          SAGE  SAREA                SDEPT
------------------------------ ---------- --------- -------------------- --------------------
SPOCKET
-------------------
bhavesh                          102               18 nungambakkam         marine
500
pruthvik                         103               20  anna nagar           aerospace
250
charith                        104               20   kilpauk              mechanical
100

CREATING TABLES FOR DOING SET OPERATIONS
TO CREATE PRODUCT TABLE
SQL> create table product(prodname varchar2(30), prodno varchar2(10));

Table created.

SQL> insert into product values('table',10001);

1 row created.

SQL> insert into product values('chair',10010);

1 row created.

SQL> insert into product values('desk',10110);

1 row created.

SQL> insert into product values('cot',11110);

1 row created.

SQL> insert into product values('sofa',10010);

1 row created.

SQL>

SQL> insert into product values('tvstand',11010);

1 row created.

SQL> select * from product;

PRODNAME                       PRODNO
------------------------------ ----------
table                                   10001
chair                                   10010
desk                                   10110
cot                                     11110
sofa                                   10010
tvstand                              11010

TO CREATE SALE TABLE
SQL> create table sale(prodname varchar2(30),orderno number(10),prodno varchar2(10));

Table created.

SQL>  insert into sale values('table',801,10001);

1 row created.

SQL> insert into sale values('chair',805,10010);

1 row created.

SQL> insert into sale values('desk',809,10110);

1 row created.

SQL> insert into sale values('cot',813,11110);

1 row created.

SQL> insert into sale values('sofa',817,10010);

1 row created.

SQL> select * from sale;

PRODNAME                         ORDERNO   PRODNO
------------------------------         ---------           ----------
table                                          801                   10001
chair                                         805                    10010
desk                                         809                     10110
cot                                            813                    11110
sofa                                          817                    10010
SET OPERATIONS
SQL> select prodname from product where prodno=10010 union select prodname from sale where prodno=10010;

PRODNAME
------------------------------
chair
sofa

SQL> select prodname from product where prodno=11110 intersect select prodname from sale where prodno=11110;

PRODNAME
------------------------------
cot



NESTED   QUERIES & JOIN QUERIES


CREATING TABLES FOR DOING JOIN AND NESTED QUERY OPERATIONS
TO CREATE SSTUD1 TABLE
SQL> create table sstud1 ( sname varchar2(20) , place varchar2(20));

Table created.

SQL> insert into sstud1 values ( 'prajan','chennai');

1 row created.

SQL> insert into sstud1 values ( 'anand','chennai');

1 row created.

SQL> insert into sstud1 values ( 'kumar','chennai');

1 row created.

SQL> insert into sstud1 values ( 'ravi','chennai');

1 row created.

SQL> select * from sstud1;

SNAME                PLACE
-------------------- --------------------
prajan               chennai
anand                chennai
kumar                chennai
ravi                 chennai

TO CREATE SSTUD2 TABLE
SQL> create table sstud2 ( sname varchar2(20), dept varchar2(10), marks number(10));

Table created.

SQL> insert into sstud2 values ('prajan','cse',700);

1 row created.

SQL> insert into sstud2 values ('anand','it',650);

1 row created.

SQL> insert into sstud2 values ('vasu','cse',680);

1 row created.

SQL> insert into sstud2 values ('ravi','it',600);

1 row created.

SQL> select * from sstud2;

SNAME                DEPT           MARKS
-------------------- ---------- ---------
prajan                     cse              700
anand                     it                 650
vasu                       cse              680
ravi                        it                 600


JOIN OPERATIONS
SQL> select sstud1.sname, dept from sstud1 inner join sstud2 on ( sstud1.sname= sstud2.sname);

SNAME                DEPT
-------------------- ----------
anand                it
prajan               cse
ravi                 it

SQL> select sstud1.sname, dept from sstud1 join sstud2 on ( sstud1.sname= sstud2.sname);

SNAME                DEPT
-------------------- ----------
anand                it
prajan               cse
ravi                 it

SQL> select sstud1.sname, dept from sstud1 left outer join sstud2 on ( sstud1.sname= sstud2.sname);

SNAME                DEPT
-------------------- ----------
prajan               cse
anand                it
ravi                   it
kumar

SQL> select sstud1.sname, dept from sstud1 right outer join sstud2 on ( sstud1.sname= sstud2.sname)


SNAME                DEPT
-------------------- ----------
prajan               cse
anand                it
ravi                   it
                       cse

SQL> select sstud1.sname, dept from sstud1 full outer join sstud2 on ( sstud1.sname=

NESTED QUERIES

SQL> select sname from sstud1 where sstud1.sname in ( select sstud2.sname from
  2  sstud2 );

SNAME
--------------------
anand
prajan
ravi

SQL> select sname from sstud1 where sstud1.sname not in ( select sstud2.sname from sstud2 );

SNAME
--------------------
kumar

SQL> select sname from sstud2 where marks > some(select marks from sstud2
  2  where dept='cse');

SNAME
--------------------
prajan


VIEWS
DEFINITION
           
A view is an object that gives the user the logical view of data from the underlying table.
Any relation that is not part of the logical model but is made visible to the user as a virtual relation is called a view. They are generally used to avoid duplication of data.

Views are created for the following reasons,
1        Data simplicity
2        To provide data security
3        Structural simplicity (because view contains only limited number of rows and colmns)

TYPES OF VIEWS

1        Updatable views – Allow data manipulation
2        Read only views – Do not allow data manipulation

TO CREATE THE TABLE ‘FVIEWS’

SQL> create table fviews( name varchar2(20),no number(5), sal number(5), dno number(5));

Table created.

SQL> insert into fviews values('xxx',1,19000,11);

1 row created.

SQL> insert into fviews values('aaa',2,19000,12);

1 row created.

SQL> insert into fviews values('yyy',3,40000,13);

1 row created.

SQL> select * from fviews;

NAME                        NO       SAL       DNO
-------------------- --------- --------- ---------
xxx                          1     19000        11
aaa                          2     19000        12
yyy                          3     40000        13


TO CREATE THE TABLE ‘DVIEWS’

SQL> create table dviews( dno number(5), dname varchar2(20));

Table created.

SQL> insert into dviews values(11,'x');

1 row created.

SQL> insert into dviews values(12,'y');

1 row created.

SQL> select * from dviews;

      DNO DNAME
--------- --------------------
       11 x
       12 y

CREATING THE VIEW ‘SVIEW’ ON ‘FVIEWS’ TABLE

SQL> create view sview as select name,no,sal,dno from fviews where dno=11;

View created.

SQL> select * from sview;

NAME                        NO       SAL       DNO
--------------------   ---------     ---------   ---------
xxx                              1         19000        11

UPDATES MADE ON THE VIEW ARE REFLECTED ONLY ON THE TABLE WHEN THE STRUTURE OF THE TABLE AND THE VIEW ARE NOT SIMILAR -- PROOF

SQL> insert into sview values ('zzz',4,20000,14);

1 row created.



SQL> select * from sview;

NAME                        NO       SAL       DNO
-------------------- --------- --------- ---------
xxx                          1     19000        11

SQL> select * from fviews;

NAME                        NO       SAL       DNO
-------------------- ---------      ---------    ---------
xxx                          1            19000        11
aaa                          2             19000        12
yyy                         3             40000        13
zzz                          4             20000        14

UPDATES MADE ON THE VIEW ARE REFLECTED ON BOTH THE VIEW AND THE TABLE WHEN THE STRUTURE OF THE TABLE AND THE VIEW ARE SIMILAR – PROOF

CREATING A VIEW ‘IVIEW’ FOR THE TABLE ‘FVIEWS’

SQL> create view iview as select * from fviews;

View created.

SQL> select * from iview;

NAME                        NO       SAL       DNO
-------------------- --------- --------- ---------
xxx                          1     19000        11
aaa                          2     19000        12
yyy                          3     40000        13
zzz                          4     20000        14

PERFORMING UPDATE OPERATION

SQL> insert into iview values ('bbb',5,30000,15);

1 row created.

SQL> select * from iview;

NAME                        NO       SAL       DNO
-------------------- --------- --------- ---------
xxx                          1     19000        11
bbb                          5     30000        15

SQL> select * from fviews;

NAME                        NO       SAL       DNO
-------------------- --------- --------- ---------
xxx                          1     19000        11
aaa                          2     19000        12
yyy                          3     40000        13
zzz                          4     20000        14
bbb                          5     30000        15

CREATE A NEW VIEW ‘SSVIEW’ AND DROP THE VIEW

SQL> create view ssview( cusname,id) as select name, no from fviews where dno=12;

View created.

SQL> select * from ssview;

CUSNAME                     ID
-------------------- ---------
aaa                          2

SQL> drop view ssview;
View dropped.
TO CREATE A VIEW  ‘COMBO’ USING BOTH THE TABLES ‘FVIEWS’ AND ‘DVIEWS’

SQL> create view combo as select name,no,sal,dviews.dno,dname from fviews,dviews where fviews.dno=dviews.dno;

View created.

SQL> select * from combo;
NAME                        NO       SAL       DNO DNAME
-------------------- --------- --------- --------- --------------------
xxx                          1     19000        11 x
aaa                          2     19000        12 y
TO PERFORM MANIPULATIONS ON THIS VIEW
SQL> insert into combo values('ccc',12,1000,13,'x');
insert into combo values('ccc',12,1000,13,'x')


DATACONTROL LANGUAGE COMMANDS

DESCRIPTION

            The DCL language is used for controlling the access to the table and hence securing the database. This language is used to provide certain priveleges to a particular user. Priveleges are rights to be allocated. The privilege commands are namely,
1        Grant
2        Revoke
            The various priveleges that can be granted or revoked are,
1        Select
2        Insert
3        Delete
4        Update
5        References
6        Execute
7        All

GRANT COMMAND: It is used to create users and grant access to the database. It requires database administrator (DBA) privilege, except that a user can change their password. A user can grant access to their database objects to other users.

REVOKE COMMAND: Using this command , the DBA can revoke the  granted database priveleges from the user.

SYNTAX

GRANT COMMAND

Grant < database_priv [database_priv…..] > to <user_name> identified by <password> [,<password…..];

Grant <object_priv> | All on <object> to <user | public> [ With Grant Option ];

REVOKE COMMAND

Revoke <database_priv> from <user [, user ] >;

Revoke  <object_priv> on <object> from < user | public >;

<database_priv> -- Specifies the system level priveleges to be granted to the users or roles. This includes create / alter / delete any object of the system.
<object_priv> -- Specifies the actions such as alter / delete / insert / references / execute / select / update for tables.
<all> -- Indicates all the priveleges.
[ With Grant Option ] – Allows the recipient user to give further grants on the objects.
            The priveleges can be granted to different users by specifying their names or to all users by using the “Public” option. 

EXAMPLES

Consider the following tables namely “DEPARTMENTS” and “EMPLOYEES”
Their schemas are as follows ,
Departments ( dept _no , dept_ name            , dept_location );
Employees ( emp_id , emp_name , emp_salary );

SQL> Grant all on employees to abcde;

Grant succeeded.

SQL> Grant select , update , insert on departments to abcde with grant option;

Grant succeeded.

SQL> Revoke all on employees from abcde;

Revoke succeeded.

SQL> Revoke select , update , insert on departments from abcde;

Revoke succeeded.




 PROCEDURES AND FUNCTIONS

DEFINITION

            A procedure or function is a logically grouped set of SQL and PL/SQL statements that perform a specific task. They are essentially sub-programs. Procedures and functions are made up of,
1        Declarative part
2        Executable part
3        Optional exception handling part
These procedures and functions do not show the errors.

KEYWORDS AND THEIR PURPOSES

REPLACE: It recreates the procedure if it already exists.
PROCEDURE: It is the name of the procedure to be created.
ARGUMENT: It is the name of the argument to the procedure. Paranthesis can be omitted if no arguments are present.
IN: Specifies that a value for the argument must be specified when calling the procedure ie. used to pass values to a sub-program. This is the default parameter.
OUT:  Specifies that the procedure passes a value for this argument back to it’s calling environment after execution ie. used to return values to a caller of the sub-program.
INOUT: Specifies that a value for the argument must be specified when calling the procedure and that procedure passes a value for this argument back to it’s calling environment after execution.
RETURN: It is the datatype of the function’s return value because every function must return a value, this clause is required.

PROCEDURES – SYNTAX          

 create or replace procedure <procedure name> (argument {in,out,inout} datatype ) {is,as}
 variable declaration;
 constant declaration;
 begin
 PL/SQL subprogram body;
 exception
 exception PL/SQL block;
 end;

FUNCTIONS – SYNTAX

 create or replace function <function name> (argument in datatype,……) return datatype {is,as}
 variable declaration;
 constant declaration;
 begin
 PL/SQL subprogram body;
 exception
 exception PL/SQL block;
 end;

CREATING THE TABLE ‘ITITEMS’ AND DISPLAYING THE CONTENTS

SQL> create table ititems(itemid number(3), actualprice number(5), ordid number(4), prodid number(4));

Table created.

SQL> insert into ititems values(101, 2000, 500, 201);

1 row created.

SQL> insert into ititems values(102, 3000, 1600, 202);

1 row created.

SQL> insert into ititems values(103, 4000, 600, 202);

1 row created.

SQL> select * from ititems;

ITEMID  ACTUALPRICE     ORDID    PRODID
---------        -----------               --------       ---------
      101          2000                       500        201
      102          3000                     1600        202
      103          4000                       600        202

PROGRAM FOR GENERAL PROCEDURE – SELECTED RECORD’S PRICE IS INCREMENTED BY 500 , EXECUTING THE PROCEDURE CREATED AND DISPLAYING THE UPDATED TABLE

SQL> create procedure itsum(identity number, total number) is price number;
  2  null_price exception;
  3  begin
  4  select actualprice into price from ititems where itemid=identity;
  5  if price is null then
  6  raise null_price;
  7  else
  8  update ititems set actualprice=actualprice+total where itemid=identity;
  9  end if;
 10  exception
 11  when null_price then
 12  dbms_output.put_line('price is null');
 13  end;
 14  /

Procedure created.

SQL> exec itsum(101, 500);

PL/SQL procedure successfully completed.

SQL> select * from ititems;

 ITEMID   ACTUALPRICE      ORDID     PRODID
---------            -----------              ---------      ---------
      101             2500                      500          201
      102             3000                    1600          202
      103            4000                       600          202

PROCEDURE FOR ‘IN’ PARAMETER – CREATION, EXECUTION

SQL> set serveroutput on;
SQL> create procedure yyy (a IN number) is price number;
  2  begin
  3  select actualprice into price from ititems where itemid=a;
  4  dbms_output.put_line('Actual price is ' || price);
  5  if price is null then
  6  dbms_output.put_line('price is null');
  7  end if;
  8  end;
  9  /

Procedure created.

SQL> exec yyy(103);
Actual price is 4000

PL/SQL procedure successfully completed.

PROCEDURE FOR ‘OUT’ PARAMETER – CREATION, EXECUTION

SQL> set serveroutput on;
SQL> create procedure zzz (a in number, b out number) is identity number;
  2  begin
  3  select ordid into identity from ititems where itemid=a;
  4  if identity<1000 then
  5   b:=100;
  6  end if;
  7  end;
  8  /

Procedure created.

SQL> declare
  2  a number;
  3  b number;
  4  begin
  5  zzz(101,b);
  6  dbms_output.put_line('The value of b is '|| b);
  7  end;
  8  /
The value of b is 100

PL/SQL procedure successfully completed.

PROCEDURE FOR ‘INOUT’ PARAMETER – CREATION, EXECUTION

SQL> create procedure itit ( a in out number) is
  2  begin
  3  a:=a+1;
  4  end;
  5  /
 
Procedure created.

SQL> declare
  2  a number:=7;
  3  begin
  4  itit(a);
  5  dbms_output.put_line(‘The updated value is ‘||a);
  6  end;
  7  /

The updated value is 8

PL/SQL procedure successfully completed.

CREATE THE TABLE ‘ITTRAIN’ TO BE USED FOR FUNCTIONS

SQL>create table ittrain ( tno number(10), tfare number(10));

Table created.

SQL>insert into ittrain values (1001, 550);

1 row created.

SQL>insert into ittrain values (1002, 600);

1 row created.

SQL>select * from ittrain;

     TNO       TFARE
   ---------    ------------
    1001         550
    1002         600 

PROGRAM FOR FUNCTION AND IT’S EXECUTION

SQL> create function aaa (trainnumber number) return number is
  2  trainfunction ittrain.tfare % type;
  3  begin
  4  select tfare into trainfunction from ittrain where tno=trainnumber;
  5  return(trainfunction);
  6  end;
  7  /
Function created.

SQL> set serveroutput on;
SQL> declare
  2  total number;
  3  begin
  4  total:=aaa (1001);
  5  dbms_output.put_line('Train fare is Rs. '||total);
  6  end;
  7  /
Train fare is Rs.550

PL/SQL procedure successfully completed.



FACTORIAL OF A NUMBER USING FUNCTION — PROGRAM AND EXECUTION

  SQL>  create function itfact (a number) return number is
  2   fact number:=1;
  3   b number;
  4   begin
  5   b:=a;
  6   while b>0
  7   loop
  8   fact:=fact*b;
  9   b:=b-1;
 10   end loop;
 11  return(fact);
 12  end;
 13  /

Function created.

 SQL> set serveroutput on;
SQL> declare
  2  a number:=7;
  3  f number(10);
  4  begin
  5  f:=itfact(a);
  6  dbms_output.put_line(‘The factorial of the given number is’||f);
  7 end;
  8  /

The factorial of the given number is 5040

PL/SQL procedure successfully completed.



SQL APPLICATION


GIVEN APPLICATION

      Given relational schema are
1        sempls ( eno primary key, ename, edob, gender,doj,desg, bpay, dno)
2        sdments ( dno, dname)
3        spros(  pno primary key,pname,dno)
4        sworks ( eno , pno , datework, intime, outtime)

               A department can control any number of projects but a project is controlled only by one department. An employee can work in any number of projects on a day but an employee is not permitted to work more than once on a project on the same day. Develop suitable queries.

TO CREATE ‘SEMPLS’ TABLE

SQL> create table sempls( eno number(10)  primary key, ename varchar2(10), edob varchar2(15), gender varchar2(10), doj varchar2(15),desg varchar2(30), bpay number(10), dno number(10));

Table created.

SQL> insert into sempls values( 1, 'bala','15/1/84','m','16th july','lec',7000,1);

1 row created.

SQL> insert into sempls values( 2, 'kala','13/9/84','m','18th july','lec',10000,2);

1 row created.

SQL> insert into sempls values( 3, 'mala','17th june','f','19th june','lec',19000,1);

1 row created.

SQL> insert into sempls values(4, 'nila','20th june','f','19th june','sr.lec',20000,1);

1 row created.

SQL> insert into sempls values( 5, 'vina','2nd jan','f','12th july','prof.',50000,2);

1 row created.

SQL> select * from sempls;

      ENO ENAME      EDOB            GENDER     DOJ             DESG        BPAY       DNO

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

        1           bala       15/1/84                  m          16th july        lec               7000         1
        2           kala       13/9/84                  m          18th july        lec              10000         2
        3           mala      17th june                f           19th june       lec              19000         1
      4              nila      20th june               f          19th june       sr.lec              20000         1
     5               vina       2nd jan                f          12th july         prof.              50000         2

TO CREATE ‘SDMENTS’ TABLE

SQL> create table sdments( dno number(10), dname varchar2(30));

Table created.

SQL> insert into sdments values (1, 'cse');

1 row created.

SQL> insert into sdments values (2, 'it');

1 row created.

SQL> select * from sdments;

      DNO      DNAME
---------       ------------------------------
        1           cse
        2            it

TO CREATE ‘SPROS’ TABLE

SQL> create table spros (pno number(20) primary key, pname varchar2(30),dno number(10));

Table created.

SQL> insert into spros values(81, 'aaa',1);

1 row created.

SQL> insert into spros values(82, 'bbb',1);

1 row created.

SQL> insert into spros values(83, 'ccc',1);

1 row created.

SQL> insert into spros values(84, 'ddd',2);

1 row created.

SQL> insert into spros values (85, 'eee',2);

1 row created.

SQL> select * from spros;

      PNO    PNAME                                DNO
---------      ------------------------------ ---------
       81         aaa                                        1
       82         bbb                                       1
       83         ccc                                        1
       84         ddd                                       2
       85         eee                                        2

TO CREATE ‘SWORKS’ TABLE

SQL> create table sworks (eno number(10) , pno number(20) , datework varchar2(20) , intime number(10),outtime number(10));

Table created.

SQL> insert into sworks values(1,81,'11th july',9,10);

1 row created.

SQL> insert into sworks values(1,82,'11th july',10,11);

1 row created.

SQL> insert into sworks values(1,83,'11th july',11,12);

1 row created.

SQL> insert into sworks values(1,84,'11th july',12,1);

1 row created.

SQL> insert into sworks values(1,85,'11th july',1,2);

1 row created.

SQL> insert into sworks values(2,85,'12th july',8,9);

1 row created.

SQL> select * from sworks;

      ENO       PNO        DATEWORK             INTIME   OUTTIME
---------        ---------   --------------------            ---------       ---------
        1                81             11th july                      9                10
        1                82             11th july                     10               11
        1                83             11th july                     11               12
        1                84             11th july                     12                 1
        1                85             11th july                      1                  2
        2                85             12th july                      8                  9

QUERY 1

    This querylists the details of employees who earn a basic pay that is less than the average basic pay of the employees.

SQL> select * from sempls where bpay < (select avg(bpay) from sempls);

      ENO ENAME      EDOB            GENDER     DOJ             DESG     BPAY       DNO
-------     ---------- --------------- ------------------ ------- ------------------ ------------ ---------        
        1           bala       15/1/84                  m         16th july           lec         7000         1
        2          kala       13/9/84                  m         18th july           lec         10000        2
        3           mala      17th june                f          19th june          lec         19000        1
        4           nila        20th june                f          19th june       sr.lec        20000        1

QUERY 2

        This query lists the department number , number of employees in each department.

SQL> select  dno,count(eno) from sempls group by dno;

      DNO     COUNT(ENO)
  ---------       ----------
        1               3
        2               2

QUERY 3

    This query lists the details of employees who earn a basic pay in the range 10000 to 20000.

SQL> select * from sempls where bpay between 10000 and 20000;

      ENO ENAME      EDOB    GENDER     DOJ        DESG     BPAY       DNO
     -------  ---------     ----------     -----------     ------- --------------- ------------------------------

        2          kala       13/9/84         m          18th july       lec         10000         2
        3          mala      17th june       f          19th june       lec         19000         1
        4          nila        20th june       f          19th june       sr.lec     20000         1

QUERY 4

       This query lists the details of employees who have worked in projects controlled by department name = cse.

SQL> select * from sempls, sdments,spros where sdments.dno=spros.dno and sdments.dno=sempls.dno and dname='cse';

     ENO ENAME      EDOB            GENDER     DOJ             DESG
--------- ---------- --------------- ---------- --------------- ------------------------------
     BPAY       DNO       DNO DNAME                                PNO
--------- --------- --------- ------------------------------ ---------
PNAME                                DNO
------------------------------ ---------
        1 bala       15/1/84         m          16th july       lec
     7000         1         1 cse                                   81
aaa                                    1

        3 mala       17th june       f          19th june       lec
    19000         1         1 cse                                   81
aaa                                    1

        4 nila       20th june       f          19th june       sr.lec
    20000         1         1 cse                                   81
aaa                                    1


QUERY 5

     This query lists the employee number, employee  name, department number, date worked if the employee has worked in more than 4 projects on a day.

SQL> select sempls.eno,ename,dno,datework from sempls,sworks where sempls.eno in(select eno from (select eno,datework from sworks group by eno,datework having count(pno)>4))   and datework in (select datework from (select eno,datework from sworks group by eno,datework ha
ving count(pno)>4));

      ENO ENAME       DNO    DATEWORK
--------- ---------- --------- --------------------
        1       bala               1           11th july
        1       bala               1           11th july
        1       bala               1           11th july
        1       bala               1           11th july
       



RESULT
         Thus the application was implemented and the output was verified.


CURSORS


TO CREATE THE TABLE ‘SSEMPP’

SQL> create table ssempp( eid number(10), ename varchar2(20), job varchar2(20), sal number (10),dnonumber(5));

Table created.

SQL> insert into ssempp values(1,'nala','lecturer',34000,11);

1 row created.

SQL> insert into ssempp values(2,'kala',' seniorlecturer',20000,12);

1 row created.

SQL> insert into ssempp values(5,'ajay','lecturer',30000,11);

1 row created.

SQL> insert into ssempp values(6,'vijay','lecturer',18000,11);

1 row created.

SQL> insert into ssempp values(3,'nila','professor',60000,12);

1 row created.

SQL> select * from ssempp;

    EID    ENAME                JOB                        SAL       DNO
--------- -------------------- --------------------     ---------     ---------
        1       nala                   lecturer                  34000        11
        2       kala                  seniorlecturer         20000         12
        5       ajay                  lecturer                  30000         11
        6      vijay                 lecturer                   18000         11
        3      nila                   professor                60000         12

TO WRITE A PL/SQL BLOCK TO DISPLAY THE EMPOYEE ID AND EMPLOYEE NAME USING CURSOR FOR LOOP

SQL> set serveroutput on;
SQL> declare
  2  begin
  3  for emy in (select eid,ename from ssempp)
  4  loop
  5  dbms_output.put_line('Employee id and employee name  are '|| emy.eid ‘and’|| emy.ename);
  6  end loop;
  7  end;
  8  /
Employee id and employee name are 1 and nala
Employee id and employee name are 2 and kala
Employee id and employee name are 5 and ajay
Employee id and employee name are 6 and vijay
Employee id and employee name are 3 and nila

PL/SQL procedure successfully completed.

TO WRITE A PL/SQL BLOCK TO UPDATE THE SALARY OF ALL EMPLOYEES  WHERE DEPARTMENT NO IS 11 BY 5000 USING CURSOR FOR LOOP AND TO DISPLAY THE UPDATED TABLE

SQL> set serveroutput on;
SQL> declare
  2  cursor cem is select eid,ename,sal,dno from ssempp where dno=11;
  3  begin
  4  --open cem;
  5  for rem in cem
  6  loop
  7  update ssempp set sal=rem.sal+5000 where eid=rem.eid;
  8  end loop;
  9  --close cem;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> select * from ssempp;

      EID      ENAME                JOB                 SAL       DNO
---------    -------------------- -------------------- --------- ---------
        1       nala                       lecturer             39000        11
        2       kala                  seniorlecturer        20000        12
        5       ajay                      lecturer             35000        11
        6     vijay                       lecturer             23000        11
        3     nila                       professor            60000        12

TO WRITE A PL/SQL BLOCK TO DISPLAY THE EMPLOYEE ID AND EMPLOYEE NAME  WHERE DEPARTMENT NUMBER IS 11 USING EXPLICIT CURSORS

  1  declare
  2  cursor cenl is select eid,sal from ssempp where dno=11;
  3  ecode ssempp.eid%type;
  4  esal empp.sal%type;
  5  begin
  6  open cenl;
  7  loop
  8  fetch cenl into ecode,esal;
  9  exit when cenl%notfound;
 10  dbms_output.put_line(' Employee code and employee salary are' || ecode ‘and’|| esal);
 11  end loop;
 12  close cenl;
 13* end;
SQL> /
Employee code and employee salary are 1 and 39000
Employee code and employee salary are 5 and 35000
Employee code and employee salary are 6 and 23000

PL/SQL procedure successfully completed.

TO WRITE A PL/SQL BLOCK TO UPDATE THE SALARY BY 5000 WHERE THE JOB IS LECTURER , TO CHECK IF UPDATES ARE MADE USING IMPLICIT CURSORS AND TO DISPLAY THE UPDATED TABLE

SQL> declare
  2  county number;
  3  begin
  4  update ssempp set sal=sal+10000 where job='lecturer';
  5  county:= sql%rowcount;
  6  if county > 0 then
  7  dbms_output.put_line('The number of rows are '|| county);
  8  end if;
  9  if sql %found then
 10  dbms_output.put_line('Employee record modification successful');
 11  else if sql%notfound then
 12  dbms_output.put_line('Employee record is not found');
 13  end if;
 14  end if;
 15  end;
 16  /
The number of rows are 3

Employee record modification successful

PL/SQL procedure successfully completed.

SQL> select * from ssempp;

      EID   ENAME                JOB                        SAL       DNO
--------- -------------------- -------------------- ---------        ---------
        1        nala                 lecturer                 44000        11
        2        kala                 seniorlecturer       20000        12
        5        ajay                 lecturer                 40000        11
        6        vijay                lecturer                 28000        11
        3        nila                 professor                60000        12




TRIGGERS
DEFINITION

            A trigger is a statement that is executed automatically by the system as a sideeffect of a modification to the database. The parts of a trigger are,
1        Trigger statement: Specifies the DML statements and fires the trigger body. It also specifies the table to which the trigger is associated.
2        Trigger body or trigger action: It is a PL/SQL block that is executed when the triggering statement is used.
3        Trigger restriction: Restrictions on the trigger can be achieved

The different uses of triggers are as follows,
1        To generate data automatically
2        To enforce complex integrity constraints
3        To customize complex securing authorizations
4        To maintain the replicate table
5        To audit data modifications

TYPES OF TRIGGERS

            The various types of triggers are as follows,
1        Before: It fires the trigger before executing the trigger statement.
2        After: It fires the trigger after executing the trigger statement.
3        For each row: It specifies that the trigger fires once per row.
4        For each statement: This is the default trigger that is invoked. It specifies that the trigger fires once per statement.

VARIABLES USED IN TRIGGERS

1        :new
2        :old

These two variables retain the new and old values of the column updated in the database. The values in these variables can be used in the database triggers for data manipulation




SYNTAX

 create or replace trigger triggername [before/after] {DML statements}
 on [tablename] [for each row/statement]
 begin
 -------------------------
 -------------------------
 -------------------------
exception
end;

TO CREATE A SIMPLE TRIGGER THAT DOES NOT ALLOW INSERT UPDATE AND DELETE OPERATIONS ON THE TABLE

SQL> create trigger ittrigg before insert or update or delete on itempls for each row
  2  begin
  3  raise_application_error(-20010,'You cannot do manipulation');
  4  end;
  5 
  6  /

Trigger created.

SQL> insert into itempls values('aaa',14,34000);
insert into itempls values('aaa',14,34000)
            *
ERROR at line 1:
ORA-20010: You cannot do manipulation
ORA-06512: at "STUDENT.ITTRIGG", line 2
ORA-04088: error during execution of trigger 'STUDENT.ITTRIGG'

SQL> delete from itempls where ename='xxx';
delete from itempls where ename='xxx'
            *
ERROR at line 1:
ORA-20010: You cannot do manipulation
ORA-06512: at "STUDENT.ITTRIGG", line 2
ORA-04088: error during execution of trigger 'STUDENT.ITTRIGG'

SQL> update itempls set eid=15 where ename='yyy';
update itempls set eid=15 where ename='yyy'
       *
ERROR at line 1:
ORA-20010: You cannot do manipulation
ORA-06512: at "STUDENT.ITTRIGG", line 2
ORA-04088: error during execution of trigger 'STUDENT.ITTRIGG'

TO DROP THE CREATED TRIGGER

SQL> drop trigger ittrigg;

Trigger dropped.

TO CREATE A TRIGGER THAT RAISES AN USER DEFINED ERROR MESSAGE AND DOES NOT ALLOW UPDATION AND INSERTION

SQL> create trigger ittriggs before insert or update of salary on itempls for each row
  2  declare
  3  triggsal itempls.salary%type;
  4  begin
  5  select salary into triggsal from itempls where eid=12;
  6  if(:new.salary>triggsal or :new.salary<triggsal) then
  7  raise_application_error(-20100,'Salary has not been changed');
  8  end if;
  9  end;
 10  /

Trigger created.


SQL> insert into itempls values ('bbb',16,45000);
insert into itempls values ('bbb',16,45000)
            *
ERROR at line 1:
ORA-04098: trigger 'STUDENT.ITTRIGGS' is invalid and failed re-validation


SQL> update itempls set eid=18 where ename='zzz';
update itempls set eid=18 where ename='zzz'
       *
ERROR at line 1:
ORA-04298: trigger 'STUDENT.ITTRIGGS' is invalid and failed re-validation




No comments:

Post a Comment