What is SQL?

SQL stands for structured query language and it is the standard language for dealing withRelational databases. SQL was originally developed at IBM in early 1970s or a prototype called system R.It was initially spelt and pronounced as ‘SEQUEL’ but now is popularly called SQL only. It has an ANSI as well as ISO standard.

Which is the different version of SQL?

Sql got its first ANSI standard in 1986. After the next standardization was in 1989 and the one in 1992 became very popular. It was also called SQL2. The latest version is SQL3. Which is being implemented by oracle 8 onwards? It supports some object-oriented features.

Which is the different version of Sql?

SQL can be broadly classified as-

Data definition language (DDL)
Data manipulation Language (DML)
Data control language (DCL)
Transaction control
Data retrieval (queries)

Sometimes the DCL commands are also considered to be part of DDL commands

What do you understand by DDL?

The DDL commands stands for Data definition Language and it related to the structured of an object.
E.g.: Create, Alter, drop, rename, truncate

What do you understand by DML?

The DML commands are those related to the content of the table. They deal with insertion, updating and deletion of rows in a table.
E.g. insert, update, delete

What do you understand by DCL?

The DCL commands are required to give or take back access rights on object.
E.g. GRANT, REVOKE

What is transaction control?

These commands are used to handle the unit of work. A transaction executes either as a whole or none of its statements execute.

What is data retrieval?

The main purpose of data retrieval is to display data (raw, column) in the required format.It is mainly used for querying and reporting purpose.

What is the difference between char and varchar2?

The CHAR and VARCHAR2 both are used to store data.
The CHAR type is used to store fixed-length character data. The default and minimum size is 1 and the maximum size is 2000 characters.
The Char data types uses all the space assigned to it as per the size mentioned and hence has more storage efficiency because of which it processes data faster than VARCHAR2

.What is the concept of DUAL table?

DUAL is the work table of oracle, which has only one raw and column.
The column name is DUMMY with data type CHAR (1).
When you want to perform some temporary calculation using only literals (no variables) then this table is of great use. The actual dummy column is irrelevant.
You can do all your temporary work on this table.

What is the difference between the Where clause and the HAVING clause?

The where clause is used to restrict rows.
It checks for the condition for each and every row of the table. The having clause is used to restrict groups.
It is used immediately after GROUP BY clause and it checks for the conditions considering each group as a whole.
In syntax as well as during execution the WHERE clause is always evaluated before the HAVING clause.

Sample Programs

1>handling oracle's predefined named exception
2>user defined exception handling
3>user named exception handling
4>Cursor Example.
5>Function in PL/SQL
6>Procedure in PL/SQL
7>Trigger in PL/SQL
8>Other Examples

 /*handling oracle's predefined named exception*/  
 declare  
 eno emp.empno%type:=&enter_emp_no;  
 name emp.empname%type;  
 n exception;  
 pragma exception_init(n,-00054);  
 begin  
 select empname into name from emp where empno=eno;  
 dbms_output.put_line(name);  
 exception  
 when n then  
 dbms_output.put_line('Required recources is allocated to another user!');  
 end;  
 /  
 /* user defined exception handling*/  
 declare  
 eno emp.empno%type:=&no;  
 ename emp.empno%type;  
 n exception;  
 begin  
 select empno,empname into eno,ename from emp where empno=eno;  
 if(rowcount>0) then  
      dbms_output.put_line(eno||ename);  
 else  
      raise n;  
 end if;  
 exception when n then  
 dbms_output.put_line('No Data found');  
 end;  
 /*user defined exception handling..*/  
 declare  
 n exception;  
 begin  
 raise n;  
 raise n;  
 exception   
 when n then  
 dbms_output.put_line('No Data found');  
 end;  
 /  

 /*user named exception handling*/  
 declare  
 a number(3):=&a;  
 b number(3):=&b;  
 c number(3);  
 dev exception;  
 pragma exception_init(dev,-01476);  
 begin  
 c:=a/b;  
 dbms_output.put_line('div='||C);  
 exception  
 when dev then  
 dbms_output.put_line('error: divide by zero');  
 end;  
 /  

DECLARE 
    CURSOR c1 IS SELECT empno,empname FROM EMP;
a number;
b varchar2(34);
BEGIN
   OPEN c1;
 IF c1%ISOPEN THEN
    LOOP
      FETCH c1 into a,b;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(a||'   '||b);
    END LOOP; 
END IF;
END;
/




declare
cursor c1 is select empno,empname,city,deptno from emp1;
a emp1.empno%type;
b emp1.empname%type;
c emp1.city%type;
d emp1.deptno%type;
begin
open c1;
loop
fetch c1 into a,b,c,d;
exit when c1%notfound;
if(c='anand') then
dbms_output.put_line(a||'  '||b||'  '||c||'  '||d);
end if;
end loop;
end;
/


declare
cursor c2 is select distinct(deptno) from emp1 order by deptno;
cursor c1 is select empno,empname,deptno from emp1 order by deptno;
a number;
b varchar2(23);
c number;
t number;
begin
open c2;
loop
fetch c2 into t;
 EXIT WHEN c2%NOTFOUND;
dbms_output.put_line('------------------');
open c1;
loop
fetch c1 into a,b,c;
 EXIT WHEN c1%NOTFOUND;
if(c=t ) then
dbms_output.put_line(rpad(a,4,' ')||rpad(b,15,' ')||c);
end if;
end loop;
close c1;
end loop;
close c2;
end;
/

create or replace procedure n2(eno in number) as
ename emp.empno%type;

begin
select empname into ename from emp where empno=eno;
dbms_output.put_line(ename);
end;
/


create or replace procedure n1(eno in number) as
i emp%rowtype;
begin
select * into i from emp where empno=eno;
dbms_output.put_line('empno  ='||i.empnno);
dbms_output.put_line('name   ='||i.empname);
dbms_output.put_line('city   ='||i.city);
dbms_output.put_line('state  ='||i.state);
dbms_output.put_line('country='||i.country);
dbms_output.put_line('phone  ='||i.phone);
end;
/






create or replace procedure n1(eno in number) as
i emp%rowtype;
begin
select * into i from emp where empno=eno;
dbms_output.put_line('------------------');
dbms_output.put_line('empno  ='||i.empno);
dbms_output.put_line('name   ='||i.empname);
dbms_output.put_line('city   ='||i.city);
dbms_output.put_line('state  ='||i.state);
dbms_output.put_line('country='||i.country);
dbms_output.put_line('phone  ='||i.phone);
dbms_output.put_line('------------------');
exception
when no_data_found then
dbms_output.put_line('No Record Found....');
end;
/



create or replace function calc(a in number,b in number,op in varchar2) return number as c number;

begin
if op='add' then
c:=a+b;
elsif op='sub' then
c:=a-b;
elsif op='mul' then
c:=a*b;
elsif op='div' then
c:=a/b;
end if;
return c;
end;
/


create or replace procedure n1(a in number,b in number,op in varchar2) as
c number(20);
d number(20,4);
begin
if op='add' then
c:=a+b;
dbms_output.put_line('addition='||C);
elsif op='sub' then
c:=a-b;
dbms_output.put_line('subtration='||C);
elsif op='mul' then
c:=a*b;
dbms_output.put_line('multiplication='||C);
elsif op='div' then
d:=a/b;
dbms_output.put_line('divison='||d);
end if;
end;
/



create or replace function rev(a in number) return number as r number;
n number:=a;
d number;
begin
while (n<>0)
loop
d:=floor(n mod 10);
r:=floor(r*10)+d;
n:=floor(n/10);
end loop;
return r;
end;
/




create or replace procedure empinfo(a in number) as
i emp%rowtype;
begin
select empno,empname,phone into i.empno,i.empname,i.phone from emp where empno=a;
dbms_output.put_line('------------------');
dbms_output.put_line('Emp No:'||i.empno);
dbms_output.put_line('Emp Name:'||i.empname);
dbms_output.put_line('phone:'||i.phone);
dbms_output.put_line('------------------');
dbms_output.put_line('Powered By Niraj');
exception 
when no_data_found then
dbms_output.put_line(' ');
dbms_output.put_line(' ');
dbms_output.put_line('---------------------');
dbms_output.put_line('No such record found.');
dbms_output.put_line('---------------------');
dbms_output.put_line('Powered By Niraj');
end;
/

 /*handling oracle's predefined named exception*/  
 declare  
 eno emp.empno%type:=&enter_emp_no;  
 name emp.empname%type;  
 n exception;  
 pragma exception_init(n,-00054);  
 begin  
 select empname into name from emp where empno=eno;  
 dbms_output.put_line(name);  
 exception  
 when n then  
 dbms_output.put_line('Required recources is allocated to another user!');  
 end;  
 /  
 /* user defined exception handling*/  
 declare  
 eno emp.empno%type:=&no;  
 ename emp.empno%type;  
 n exception;  
 begin  
 select empno,empname into eno,ename from emp where empno=eno;  
 if(rowcount>0) then  
      dbms_output.put_line(eno||ename);  
 else  
      raise n;  
 end if;  
 exception when n then  
 dbms_output.put_line('No Data found');  
 end;  
 /*user defined exception handling..*/  
 declare  
 n exception;  
 begin  
 raise n;  
 raise n;  
 exception   
 when n then  
 dbms_output.put_line('No Data found');  
 end;  
 /  
 /*user named exception handling*/  
 declare  
 a number(3):=&a;  
 b number(3):=&b;  
 c number(3);  
 dev exception;  
 pragma exception_init(dev,-01476);  
 begin  
 c:=a/b;  
 dbms_output.put_line('div='||C);  
 exception  
 when dev then  
 dbms_output.put_line('error: divide by zero');  
 end;  
 /

Nhcsoft Incorporated

ORACLE

Sample Programs

1>handling oracle's predefined named exception
2>user defined exception handling
3>user named exception handling
4>Cursor Example.
5>Function in PL/SQL
6>Procedure in PL/SQL
7>Trigger in PL/SQL
8>Other Examples

Unordered List

Pages

ORACLE

Sample Programs

1>handling oracle's predefined named exception 2>user defined exception handling 3>user named exception handling 4>Cursor Example. 5>Function in PL/SQL 6>Procedure in PL/SQL 7>Trigger in PL/SQL 8>Other Examples

1>handling oracle's predefined named exception
2>user defined exception handling
3>user named exception handling
4>Cursor Example.
5>Function in PL/SQL
6>Procedure in PL/SQL
7>Trigger in PL/SQL
8>Other Examples