SQL WEEK7

                                                 SQL  QUREIES

  Set serveroutput ON

Write a PL/SQL block to find the sum of the digits of a given number.

DECLARE
V_NUM number(7);
V_B number(7);
V_sum number(10);
BEGIN
V_NUM:=#
V_sum:=0;
V_B:=1;
while V_B <= V_NUM LOOP
V_sum:=V_B+V_sum;
V_B:=V_B+1;
END LOOP;
dbms_output.put_line('ADDITION is'||V_sum);
END;
/

ok.....Write a PL/SQL block to check an input string is palindrome or not palindrome.

DECLARE
str varchar2(50):='&string'; 
counter int:=length(str); 
BEGIN 
dbms_output.put_line(counter); 
LOOP 
exit WHEN counter=0; 
exit WHEN not(substr(str,counter,1)=substr(str,((length(str)+1)-counter),1)); 
counter:=counter-1; 
END LOOP; 
IF counter=0 
THEN dbms_output.put_line(str||'is palindrom'); 
ELSE 
dbms_output.put_line(str||'is not palindrom'); 
END IF; 
END; 
/

ok.....Write a PL/SQL block to accept employee number and display Employee Name, Department name, salary of employees in the format – ‘RAVI works in Marketing department and draws 32000/- as salary’.

DECLARE
T_ENO EMP.EMPNO%TYPE;
T_NAME EMP.ENAME%TYPE;
T_SAL EMP.SAL%TYPE; 
T_DNAME DEPART.DNAME%TYPE;
BEGIN
T_ENO:=&T_ENO;
SElECT E.ENAME,D.DNAME,E.SAL INTO T_NAME,T_DNAME,T_SAL FROM EMP E,DEPART D WHERE EMPNO=T_ENO AND E.DEPTNO=D.DNO;
dbms_output.put_line(T_NAME||' WORKS IN '||T_DNAME||' DEPARTMENT AND DRAWAS '||T_SAL||' /-SALARY');
END;
/

Create a Table EMPSAL with fields-Empno, Empname, Sal, HRA, DA, Gross Salary, PF, Net Salary (assume appropriate datatype and size).
Write a PL/SQL block to accept an employee number existing in EMP table and calculate HRA, DA, Gross Salary, PF, Net_Salary of that employee. Insert the empno, empname, Sal, HRA, DA, Gross Salary, PF, Net Salary into the table EMPSAL:
Use the following formula to calculate salary components- HRA=50% of Sal
DA=20% of Sal
PF=12% of Sal.
Gross_sal= Sal+ HRA+DA Net_Sal= Gross_sal-PF



ok ...CREATE TABLE EMPSAL(Empno VARCHAR2(3), Empname VARCHAR(10),SAL NUMBER(10,2), HRA NUMBER(5), DA NUMBER(5), GROSSAL NUMBER(5), PF NUMBER(5),NETSAL NUMBER(5));

DECLARE 
V_ENO EMP.EMPNO%TYPE;
V_NAME EMP.ENAME%TYPE;
V_SAL EMP.SAL%TYPE; 
SAL NUMBER; 
HRA NUMBER;
DA NUMBER;
GROSSAL NUMBER;
PF NUMBER;
NETSAL NUMBER;
BEGIN
V_ENO:=&V_ENO;
SELECT ENAME , SAL INTO V_NAME,V_SAL FROM EMP WHERE EMPNO=V_ENO;
HRA:=V_SAL*50/100;
DA:=V_SAL*20/100;
PF:=V_SAL*12/100;
GROSSAL:=V_SAL +HRA+DA;
NETSAL:=GROSSAL-PF;
INSERT INTO EMPSAL VALUES(V_ENO,V_NAME,V_SAL,HRA,DA,PF,GROSSAL,NETSAL);
dbms_output.put_line('RECORDS INSERTED INTO TABLE');
END;
/

ok ...Write a PL/SQL block to calculate the area of a circle for a value of radius varying from 3 to 7. Store the Radius and the corresponding values of calculated Area in an empty table named CIRCLE, consisting of two columns radius & area.
CREATE TABLE CIRCLE(RADIUS NUMBER(10,8),AREA NUMBER(10));

DECLARE
PI CONSTANT NUMBER :=3.14;
RADIUS NUMBER:=0;
AREA NUMBER:=0;
BEGIN
FOR RADIUS IN 3..7
LOOP
AREA :=PI*RADIUS*RADIUS;
INSERT INTO CIRCLE VALUES(RADIUS,AREA);
END LOOP;
END;
/