PavanTutorials

1. WRITE A PL/SQL PROGRAM TO CHECK THE GIVEN NUMBER IS STRONG OR NOT. SQL> Declare n number:=&n; n1 number:=n; fact number; sum1 number; r number; i number; Begin sum1:=0; while(n>0) loop r:=mod(n,10); n:=trunc(n/10); i:=1; fact:=1; while(i<=r)loop fact:=fact*i; i:=i+1; end loop; sum1:=sum1+fact; end loop; if(sum1=n1) then dbms_output.put_line(n1||' is strong number'); else dbms_output.put_line(n1||' is not a strong number'); end if; end; / Output: Enter value for n: 145 old 9: n:=&n; new 9: n:=145; 145 is strong number PL/SQL procedure successfully completed. 2. WRITE A PL/SQL PROGRAM TO CHECK THE GIVEN STRING IS PALINDROME OR NOT. SQL> Declare len number; s1 varchar2(20) := '&s1'; s2 varchar2(20); Begin len := length(s1); for i...

Lab Cycle - IV Queries 1.Create a view, which contain employee names and their manager names working in sales department. --->create view v1 as (select e.ename,n.ename as managername from emp e,emp n,dept d where e.mgr=n.empno and e.deptno=d.deptno and d.dname='sales'); View created. ============================================================================= 2. Update the employee salary by 25%, whose experience is greater than 10 years. ---> update emp set sal=sal+(sal*0.25) where round(months_between(sysdate,hiredate)/12)>10; 14 rows updated. ============================================================================= 3. Delete the employees, who completed 32 years of service. ---> delete from emp where round(months_between(sysdate,hiredate)/12)>32; 0 rows deleted. ============================================================================= 4. Determine the minimum salary of an employee and his details, who join on the...

Lab Cycle - III Queries 1. For each pilot who is certified for more than three aircraft, find the eid’s and the maximum cruising range of the aircraft that he (or She) certified for. --> select c.eid,max(a.crusingrange) from certified c,aircraft a where c.aid=a.aid group by c.eid having count(eid)>3; Output EID MAX(A.CRUSINGRANGE) 142519864 8430 269734834 8430 567354612 8430 ============================================================================= 2. Find the names of pilots whose salary is less than the price of the cheapest route from Los Angeles to Honolulu. --> select ename from employees where salary<(select min(price) from flights where origin='Los Angeles' and destination='Honolulu'); Output ENAME Milo Brooks ============================================================================= 3. Find the name of the pilots certified from some Boeing aircraft. -->select ename from employees where eid i...

Lab Cycle - II Queries 1. Get Suppliers Names for Suppliers who supply at least one red part. -->select sname from supplier where sno in(select sno from sp where pno in(select pno from parts where color='Red')); Output: SNAME Blake Clark Adams Smith ============================================================================= 2.Get Suppliers Names for Suppliers who do not supply part ‘P2’ --> select sname from supplier where sno in(select sno from sp where pno in(select pno from parts where pno!='P2')); Output: SNAME Blake Clark Adams Jones Smith ============================================================================= 3. Using Group by with Having Clause, Get the part numbers for all the parts supplied by more than one supplier. -->select pno,count(sno) from sp group by pno having count(sno)>1; Output: PNO COUNT(SNO) P4 2 P1 2 P3 3 P6 2 P5 2 =====================================...

Lab Cycle - I Queries 1. Retrieve the list of names and cities of all the clients. --> select name,city from client_master; Output : NAME CITY IvanBayros Bombay Vandana Madras Basu Delhi Vijay Chennai ============================================================================= 2. List the various products available from product_master. --> select description from product_master; Output: DESCRIPTION 1.44Flopys Monitors 540HDD 1.44Drive ============================================================================= 3.Find the list of all clients who stay in the city ‘ CHENNAI’ or ‘DELHI’. --> select name from client_master where city='Chennai' OR city='Delhi'; Output : NAME Basu Vijay ============================================================================= 4. List all the clients located at ‘CHENNAI’. --> select name from client_master where city=’Chennai’; Output : ...