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
-----------
DATE FUNCTIONS
SQL> select sysdate from dual;
SYSDATE
---------
SQL> select
sysdate,add_months(sysdate,4) result from dual;
SYSDATE RESULT
---------
---------
SQL> select sysdate, last_day(sysdate) result from dual;
SYSDATE RESULT
---------
---------
SQL> select
sysdate, next_day(sysdate,'sunday') result from dual;
SYSDATE RESULT
---------
---------
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;
-------------------- --------------------
prajan
chennai
anand
chennai
kumar
chennai
ravi
chennai
TO CREATE SSTUD2 TABLE
SQL> create table sstud2 ( sname varchar2(20), dept varchar2
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.
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 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 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> 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