Creating Tables in Oracle

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

create table client_master
(client_no varchar2(6)primary key,
name varchar2(20)not null,
address1 varchar2(30),
address2 varchar2(30),
city varchar2(15),
state varchar2(15),
pincode number(6),
bal_due number(10,2));
--------------------------------------
create table product_master
(product_no varchar2(6)primary key,
description varchar2(5)not null,
profit_percent number(2,2)not null,
unit_measure varchar2(10)not null,
qty_on_hand number(8)not null,
recorder_lvl number(8)not null,
sell_price number(8,2)not null,
cost_price number(8,2)not null);
----------------------------------------------
create table salesman_master
(salesman_no varchar2(8)primary key,
salesman_name varchar2(20)not null,
address1 varchar2(30)not null,
address2 varchar2(30),
city varchar2(20),
pincode varchar2(6),
state varchar2(20),
bal_amt number(8,2)not null,
amt_to_get number(6,2)not null,
reqd_sales number(6,2)not null,
remarks varchar2(50));
-----------------------------------------------
create table sales_order
(s_order_no varchar2(6)primary key,
s_order_date date,
client_no references client_master,
dely_addr varchar2(25),
salesman_no references salesman_master,
dely_type char(1),
filled_yn char(1),
dely_date date ,
de s_order_status varchar2(10));
--------------------------------------------------
create table sales_order_details
( s_order_no references sales_order,
product_no references product_master,
qty_ordered number(8),
qty_disp number(8),
product_rate number(10,2));
-------------------------------------------------------
create table challan_header
(challan_no varchar2(6)primary key
s_order_no references sales_order,
challan_date date not null,
billed_yn char(1)default 'n');
--------------------------
create table challa_details
( challan_no references challan_header,
product_no references product_master,
qty_disp number(4,2));
-------------------------------------------------
insert into client_master values
('&client_no','&name','&address1','&address2','&city','&state',&pincode,&bal_due);
------------------------------------
insert into product_master values
('pooo1','floppies',5,'piece',100,20,525,500);
---------------------------------------------------
insert into sales_order values
('s_order_no','s_order_date','client_no');
---------------------------------------------------------
insert into challan_header values
('&challan_no','&s_order_no','&challan_date','&billed_yn');

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

PL SQL

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

2) declare
b number(2):=1;
a number(3):=&a;
begin
while(b<=10)
loop
dbms_output.put_line(a*b);
b:=b+1;
end loop;
end;
--------------------------------------
3) declare
a number(2):=&a;
b number(3);
begin
b:=mod(a,2);
if(b=0)then
dbms_output.put_line( a ||'is even number');
else
dbms_output.put_line( a ||'is odd number');
end if;
end;
-------------------------------------------------------
4) declare
str varchar2(10):='&str';
rev varchar2(10);
l number(2);
begin
l:=length(str);
while(l>0)
loop
rev:= rev || substr(str,l,1);
l:=l-1;
end loop;
dbms_output.put_line('the reverse string is' || rev);
end;
---------------------------------------------------------------
5) declare
pi number(5,2):=3.14;
r number(2):=&r;
l number(2):=&l;
b number(2):=&b;
ac number(6,2);
ar number(6,2);
begin
ac:=pi*r*r;
dbms_output.put_line('area of circle is'||ac);
ar:=l*b;
dbms_output.put_line('area of rectangle is'||ar);
end;
---------------------------------------------------------------
6)declare
a number(3):=&a;
b number(3):=&b;
c number(4);
begin
c:=a+b;
dbms_output.put_line('the addition is'||c);
c:=a-b;
dbms_output.put_line('the subtraction is'||c);
c:=a*b;
dbms_output.put_line('the multiplication is'||c);
c:=a/b;
dbms_output.put_line('the division is'||c);
end;
------------------------------------------------------------
7)declare
salary emp.sal%type;
ejob emp.job%type;
begin
select sal into salary from emp where ename='BLAKE';
select job into ejob from emp where ename='BLAKE';
dbms_output.put_line('BLAKE is having job of' || ejob ||'and salary of'||salary);
end;
------------------------------------------------------------------------------------------
8) declare
eno emp.empno%type:=&eno;
esal emp.sal%type;
ecom emp.comm%type;
total_sal emp.sal%type;
begin
select sal into esal from emp where empno=eno;
select nvl(comm,0) into ecom from emp where empno=eno;
total_sal:=esal+ecom;
dbms_output.put_line('salary is'||esal||'comm is'||ecom);
dbms_output.put_line('employee whose empno is'||eno||'is getting total salary of'||total_sal);
end;
------------------------------------------------------------------------------------------------------
*9) declare
cursor s is select ename,sal,nvl(comm,0) from emp for update of comm;
esal emp.sal%type;
begin
for emp_rec in s
loop
if(esal>1000 and esal<=1500)then
update emp set comm=1000 where current of s;
end if;
end loop;
commit;
end;
---------------------------------------------------------------------------
*10) declare
cursor c1 is select empno,ename,job,sal,comm,deptno from emp;
eno emp.empno%type;
name emp.ename%type;
post emp.job%type;
esal emp.sal%type;
ecom emp.comm%type;
begin
open c1;
loop
fetch c1 into eno,name,post,esal,ecom;
exit when c1%notfound;
insert into employee values(eno,name,post,esal,ecom);
end loop;
close c1;
end;

10)declare
eno emp.empno%type:=&eno;
name emp.ename%type:=&name;
post emp.job%type:=&post;
esal emp.sal%type:=&esal;
ecom emp.comm%type:=&ecom;
begin
insert into employee values(eno,name,post,esal,ecom);
end;
-----------------------------------------------------

11)declare
str varchar2(10):='&str';
rev varchar2(10);
l number(2);

begin
l:=length(str);
while(l>0)
loop
rev:= rev || substr(str,l,1);
l:=l-1;
end loop;
dbms_output.put_line('the reverse string is' || rev);

if str=rev then
dbms_output.put_line('palindrom');
else
dbms_output.put_line(' not palindrom');
end if;
end;
-----------------------------------------------------------

PL SQL

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

Declare
ENO EMP.EMPNO%TYPE :=&ENO;
DNO EMP.DEPTNO%TYPE :=&DNO;
Begin
IF DNO=20 THEN
UPDATE EMP SET SAL=SAL+SAL*10/100;
ELSIF DNO=30 THEN
UPDATE EMP SET SAL=SAL+SAL*15/100;
END IF;
End;

---------------------------------------------------------------
SHOW DEPTNO AND ENAME
Declare
ENO EMP.EMPNO%TYPE :=&ENO;
ENM VARCHAR2(10);
DNO NUMBER(2);
Begin
SELECT ENAME,DEPTNO INTO ENM,DNO FROM EMP WHERE EMPNO=ENO;
DBMS_OUTPUT.PUT_LINE('ENAME IS ='||ENM||' '||'DEPTNO IS='||DNO);
End;

---------------------------------------------------------------------------
Declare
ENO EMP.EMPNO%TYPE :=&ENO;

Begin
UPDATE EMP SET SAL=3000 WHERE EMPNO=ENO AND JOB='MANAGER';
End;

---------------------------------------------------------------------------
WHICH IS SMALLER AND GREATER
DECLARE
A NUMBER(6):=&A;
B NUMBER(6):=&B;
C NUMBER(6):=&C;
BEGIN
IF(A>B AND A>C) THEN
DBMS_OUTPUT.PUT_LINE('GREATER NUMBER IS='||A);
ELSIF B>C THEN
DBMS_OUTPUT.PUT_LINE('GREATER NUMBER IS='||B);
ELSE
DBMS_OUTPUT.PUT_LINE('GREATER NUMBER IS='||C);
END IF;

IF(A<B AND A<C) THEN
DBMS_OUTPUT.PUT_LINE('SMALLER NUMBER IS='||A);
ELSIF B<C THEN
DBMS_OUTPUT.PUT_LINE('SMALLER NUMBER IS='||B);
ELSE
DBMS_OUTPUT.PUT_LINE('SMALLER NUMBER IS='||C);
END IF;
END;

-------------------------------------------------------------------------------------------
### FABONACCI SERICE

6. DECLARE
A NUMBER(10):=0;
B NUMBER(10):=1;
C NUMBER(10);
I NUMBER(3);
BEGIN
DBMS_OUTPUT.PUT_LINE(A);
DBMS_OUTPUT.PUT_LINE(B);
FOR I IN 3..50
LOOP
C:=A+B;
DBMS_OUTPUT.PUT_LINE(C);
A:=B;
B:=C;
END LOOP;
END;

-----------------------------------------------------------------------------------------------
### PRIME NUMBER 1 TO 100

7. DECLARE
A NUMBER(3);
B NUMBER(3):=2;
BEGIN
WHILE (B<=100)
LOOP
DBMS_OUTPUT.PUT_LINE(B);
B:=B+2;
END LOOP;
END;

-----------------------------------------------------------------------------------------------
### CHECK PRIME OR NOT

9. DECLARE
A NUMBER(3):=&A;
B NUMBER(3);
C NUMBER(2);
BEGIN
FOR B IN 2..A
LOOP
IF MOD(A,B)=0 THEN
C:=0;
ELSE
C:=1;
END IF;
END LOOP;
IF (C=0) THEN
DBMS_OUTPUT.PUT_LINE(' THE NUMBER IS PRIME :: '||A);
ELSE
DBMS_OUTPUT.PUT_LINE(' THE NUMBER IS NOT PRIME :: '||A);
END IF;
END;
-----------------------------------------------------------------------------------------------
### ADDITION OF NUMBER IS 1234==10

13. DECLARE
A NUMBER(6):=&A;
B NUMBER(6);
C NUMBER(6):=0;
D NUMBER(6);
BEGIN
DBMS_OUTPUT.PUT_LINE('THE ORIGNEL VALUE IS :: '||A);
WHILE(A>0)
LOOP
B:=MOD(A,10);
-- DBMS_OUTPUT.PUT_LINE('THE RESULT OF B:: '||B);
-- DBMS_OUTPUT.PUT_LINE('THE RESULT OF A1:: '||A);
C:=C+B;
-- DBMS_OUTPUT.PUT_LINE('THE RESULT OF C:: '||C);
A:=A/10;
-- DBMS_OUTPUT.PUT_LINE('THE RESULT OF A:: '||A);
--A:=D;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE ADDITION IS :: '||C);
END;
------------------------------------------------------------------------------------------------
### ACCEPT JOB & PRINT COUNTER

17. DECLARE
POST EMP.JOB%TYPE:='&POST';
NO EMP.EMPNO%TYPE;
BEGIN
SELECT COUNT(EMPNO) INTO NO FROM EMP WHERE JOB=POST;
DBMS_OUTPUT.PUT_LINE('NO: '||NO);
IF(NO=0) THEN
DBMS_OUTPUT.PUT_LINE(' ZERO ');
ELSIF (NO=1) THEN
DBMS_OUTPUT.PUT_LINE(' ONE ');
ELSE
DBMS_OUTPUT.PUT_LINE(' MORE THAN ONE ');
END IF;
END;