Quries
Section-A (SQL)
SQL> create table dept(deptno number(10)
primary key,dname varchar(15),loc varchar(15));
Table created.
SQL> insert into dept
values(10,'accounting','hyd');
1
row created.
SQL> insert into dept
values(20,'research','nzb');
1
row created.
SQL> insert into dept
values(30,'sales','kmr');
1
row created.
SQL> insert into dept
values(40,'advertisement','rr');
1 row
created.
SQL> insert into dept values(50,'operations','mhbr');
1 row
created.
SQL> select * from dept;
DEPTNO
DNAME LOC
---------- --------------- ---------------
10 accounting
hyd
20 research nzb
30 sales kmr
40 advertisement
rr
50 operations mhbr
SQL> commit;
Commit complete.
SQL> create table emp(EMPNO number(5)
primary key, ENAME varchar2(15), JOB varchar2(10), MGR number(4), HIREDATE
DATE, SAL NUMBER(8,2), COMM NUMBER(8,2), DEPTNO NUMBER(10) REFERENCES
DEPT(DEPTNO));
Table created.
SQL> INSERT INTO EMP
VALUES(1369,'RAJU','CLERK',1698,'17-DEC-1980',1500,NULL,10);
1 row created.
SQL> INSERT INTO EMP
VALUES(1499,'RAVI','SALESMAN',1698,'20-FEB-1981',3000,300,30);
1 row created.
SQL> INSERT INTO EMP
VALUES(1521,'SHARATH','SALESMAN',1698,'25-JAN-1981',1300,500,30);
1
row created.
SQL> INSERT INTO EMP
VALUES(1566,'DHASHARATH','MANAGER',1839,'12-APR-1981',2975,NULL,20);
1
row created.
SQL> INSERT INTO EMP
VALUES(1654,'ARAVIND','SALESMAN',1839,'12-SEP-1991',1250,400,30);
1 row created.
SQL> INSERT INTO EMP
VALUES(1698,'SANDHYA','MANAGER',1839,'1-MAY-1981',2850,NULL,30);
1 row created.
SQL> INSERT INTO EMP VALUES(1782,'JAIPAL','MANAGER',1839,'9-JUL-1981',2450,NULL,10);
1 row created.
SQL> INSERT INTO EMP
VALUES(1788,'LAVANYA','ANALIST',1566,'9-JUL-1985',3000,NULL,20);
1 row created.
SQL> INSERT INTO EMP
VALUES(1839,'PRANEETHA','PRESIDENT',1566,'16-NOV-1981',5000,NULL,10);
1 row created.
SQL> INSERT INTO EMP
VALUES(1844,'VIHARIKA','SALESMAN',1698,'16-SEP-1981',1500,0,30);
1 row created.
SQL> INSERT INTO EMP
VALUES(1876,'LAXMI','CLERK',1788,'9-JUL-1983',1100,NULL,20);
1 row created.
SQL> INSERT INTO EMP VALUES(1900,'NAGESH','CLERK',1788,'19-JUL-1999',1000,NULL,30);
1 row created.
SQL> INSERT INTO EMP
VALUES(1902,'MAHESH','MANAGER',1566,'9-JAN-2014',3000,NULL,20);
1 row created.
SQL> INSERT INTO EMP
VALUES(1934,'SURESH','CLERK',1566,'9-MAR-2014',1300,NULL,10);
1 row created.
SQL> INSERT INTO EMP
VALUES(1340,'KIRAN','CLERK',1782,'29-APR-2014',1300,NULL,10);
1 row created.
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB
MGR HIREDATE
SAL COMM DEPTNO
1369 RAJU
CLERK 1698 17-DEC-80 1500 10
1499 RAVI
SALESMAN 1698
20-FEB-81 3000
300 30
1521 SHARATH
SALESMAN 1698 25-JAN-81 1300 500 30
1566 DHASHARATH MANAGER
1839 12-APR-81 2975 20
1654 A
RAVIND SALESMAN 1839 12-SEP-91 1250
400 30
1698
SANDHYA MANAGER 1839
01-MAY-81
2850 30
1782 JAIPAL MANAGER 1839 09-JUL-81 2450 10
1788
LAVANYA ANALIST 1566 09-JUL-85 3000 20
1839
PRANEETHA PRESIDENT 1566 16-NOV-81
5000
1844 VIHARIKA SALESMAN 1698 16-SEP-81 1500 0
30
1876 LAXMI CLERK 1788 09-JUL-83 1100 20
1900 NAGESH CLERK 1788 19-JUL-99 1000 30
1902 MAHESH MANAGER 1566 09-JAN-14 3000 20
1934 SURESH CLERK 1566 09-MAR-14 1300 10
1340 KIRAN
CLERK 1782 29-APR-14 1300 10
15 rows selected.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM ASSOCIATE;
EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
1369 RAJU CLERK 1698 17-DEC-80 1500 10
1499 RAVI
SALESMAN 1698 20-FEB-81 3000 300 30
1521 SHARATH SALESMAN 1698 25-JAN-81 1300 500 30
1566 DHASHARATH
MANAGER 1839 12-APR-81 2975 20
1654 ARAVIND SALESMAN 1839 12-SEP-91 1250 400 30
1698 SANDHYA MANAGER 1839
01-MAY-81
2850 30
1782 JAIPAL
MANAGER 1839
09-JUL-81 2450 10
1788 LAVANYA
ANALIST 1566 09-JUL-85 3000 20
1839
PRANEETHA PRESIDENT 1566 16-NOV-81 5000 10
1844 VIHARIKA SALESMAN 1698 16-SEP-81
1500 0 30
1876
LAXMI CLERK 1788 09-JUL-83
1100 20
1900
NAGESH CLERK 1788 19-JUL-99
1000 30
1902
MAHESH MANAGER 1566 09-JAN-14
3000 20
1934 SURESH CLERK 1566 09-MAR-14 1300 10
1340 KIRAN
CLERK 1782 29-APR-14
10
15 rows selected.
SQL> COMMIT;
Commit complete.
SQL> create table manager as select * from
emp where job='MANAGER';
Table created.
SQL> SELECT * FROM MANAGER;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
1566 DHASHARATH MANAGER 1839 12-APR-81 2975 20
1698 SANDHYA MANAGER 1839 01-MAY-81 2850 30
1782 JAIPAL MANAGER
1839
09-JUL-81 2450 10
1902 MAHESH MANAGER 1566 09-JAN-14
3000
20
SQL> COMMIT;
Commit complete.
1.SQL> select e.empno,e.ename,e.job,e.sal
from emp e where (select count(*)
from emp where sal>e.sal)<3 order by sal desc;
EMPNO ENAME JOB SAL
---------- --------------- ----------
----------
1839 PRANEETHA
PRESIDENT 5000
1902 MAHESH MANAGER 3000
1499 RAVI SALESMAN 3000
1788 LAVANYA ANALIST 3000
2.SQL>
select manager.ename from associate, manager where manager.sal> (select avg(sal) from
emp where associate.mgr=manager.empno);
ENAME
---------------
DHASHARATH
DHASHARATH
DHASHARATH
DHASHARATH
SANDHYA
SANDHYA
SANDHYA
SANDHYA
JAIPAL
9 rows selected.
3.SQL>
select empno,ename,hiredate from emp where extract(day from hiredate)
<15;
EMPNO ENAME HIREDATE
---------- --------------- ---------
1566 DHASHARATH 12-APR-81
1654 ARAVIND 12-SEP-91
1698 SANDHYA 01-MAY-81
1782 JAIPAL 09-JUL-81
1788 LAVANYA 09-JUL-85
1876 LAXMI 09-JUL-83
1902 MAHESH 09-JAN-14
1934 SURESH 09-MAR-14
8 rows selected.
4.SQL> select manager.ename,
count(associate.empno) from manager, associate
where associate.mgr=manager.empno group by manager.ename having
count(associate.empno)= (select max(count(associate.empno)) from manager,
associate where manager.empno=associate.mgr group by manager.ename);
ENAME
COUNT(ASSOCIATE.EMPNO)
--------------- ----------------------
DHASHARATH 4
SANDHYA 4
5.SQL>
select ename,sal , (Case when sal<1500 then 'Less Salary' when
sal=1500 then 'Exact Salary' when sal>1500 then 'More Salary' else 'No
Salary' end) from emp;
ENAME SAL (CASEWHENSAL
--------------- ---------- ------------
RAJU 1500 Exact Salary
RAVI 3000 More Salary
SHARATH 1300 Less Salary
DHASHARATH 2975 More Salary
ARAVIND 1250 Less Salary
SANDHYA 2850 More Salary
JAIPAL 2450 More Salary
LAVANYA 3000 More Salary
PRANEETHA 5000 More Salary
VIHARIKA 1500 Exact Salary
LAXMI 1100 Less Salary
ENAME SAL (CASEWHENSAL
--------------- ---------- ------------
NAGESH 1000 Less Salary
MAHESH 3000 More Salary
SURESH 1300 Less Salary
KIRAN 1300 Less Salary
15 rows selected.
6.SQL>
update emp set sal=sal+(15/100)*sal where extract(year from
sysdate)-extract(year from hiredate)>10;
12 rows updated.
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL
COMM
---------- --------------- ---------- ----------
--------- ---------- ----------
DEPTNO
----------
1369 RAJU CLERK 1698 17-DEC-80 1725
10
1499 RAVI SALESMAN 1698 20-FEB-81 3450
300
30
1521 SHARATH SALESMAN 1698 25-JAN-81 1495
500
30
EMPNO ENAME JOB MGR HIREDATE SAL
COMM
---------- --------------- ----------
---------- --------- ---------- ----------
DEPTNO
----------
1566 DHASHARATH MANAGER 1839 12-APR-81 3421.25
20
1654 ARAVIND SALESMAN 1839 12-SEP-91 1437.5
400
30
1698 SANDHYA MANAGER 1839 01-MAY-81 3277.5
30
EMPNO ENAME JOB MGR HIREDATE SAL
COMM
---------- --------------- ----------
---------- --------- ---------- ----------
DEPTNO
----------
1782 JAIPAL MANAGER 1839 09-JUL-81 2817.5
10
1788 LAVANYA ANALIST 1566 09-JUL-85 3450
20
1839 PRANEETHA
PRESIDENT 1566
16-NOV-81 5750
10
EMPNO ENAME JOB MGR HIREDATE SAL
COMM
---------- --------------- ----------
---------- --------- ---------- ----------
DEPTNO
----------
1844 VIHARIKA SALESMAN 1698 16-SEP-81 1725
0
30
1876 LAXMI CLERK 1788 09-JUL-83 1265
20
1900 NAGESH CLERK 1788 19-JUL-99 1150
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- --------------- ----------
---------- --------- ---------- ----------
DEPTNO
----------
1902 MAHESH MANAGER 1566 09-JAN-14 3000
20
1934 SURESH CLERK 1566 09-MAR-14 1300
10
1340 KIRAN CLERK 1782 29-APR-14 1300
10
15 rows selected.
7.SQL> delete from emp where extract(year
from sysdate)-extract(year from hiredate)>=30;
10 rows deleted.
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL
COMM
---------- --------------- ----------
---------- --------- ---------- ----------
DEPTNO
----------
1654 ARAVIND SALESMAN 1839 12-SEP-91 1250
400
30
1900 NAGESH CLERK 1788 19-JUL-99 1000
30
1902 MAHESH MANAGER 1566 09-JAN-14 3000
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- --------------- ----------
---------- --------- ---------- ----------
DEPTNO
----------
1934 SURESH CLERK 1566 09-MAR-14 1300
10
1340 KIRAN CLERK 1782 29-APR-14 1300
10
8.SQL>
select * from emp where extract(year from hiredate)=(select extract(year
from hiredate) from emp where sal=(select min(sal) from emp)) ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- --------------- ----------
---------- --------- ---------- ----------
DEPTNO
----------
1900 NAGESH CLERK 1788 19-JUL-99 1000
30
9.SQL>
select count(*) "emp getting comm" from emp where comm is not
null and comm<>0;
emp getting comm
----------------
3
10.SQL>
create view sales_emp22 as select
E.ENAME "ËMPLOYEE",M.ENAME "MANAGER" FROM EMP E,EMP
M WHERE E.MGR =M.EMPNO AND E.DEPTNO=30;
View created.
SQL> SELECT * FROM SALES_EMP22;
EMPLOYEE
MANAGER
--------------- ---------------
VIHARIKA
SANDHYA
SHARATH
SANDHYA
RAVI
SANDHYA
NAGESH
LAVANYA
SANDHYA
PRANEETHA
ARAVIND
PRANEETHA
6 rows selected.
11. SQL>
select associate.ename, associate.sal, manager.sal from associate,
manager where
associate.mgr=manager.empno and associate.sal>manager.sal;
ENAME SAL SAL
--------------- ---------- ----------
RAVI 3000 2850
LAVANYA 3000 2975
PRANEETHA 5000 2975
MAHESH 3000 2975
12. SQL>
select ename,deptno,sal from emp where sal in(select max(sal) from emp
group by deptno);
ENAME DEPTNO SAL
--------------- ---------- ----------
RAVI 30 3000
LAVANYA 20 3000
PRANEETHA 10 5000
MAHESH 20 3000
13. SQL> select emp.ename,emp.job,emp.deptno,dept.dname,dept.loc
from emp ,dept where emp.deptno=dept.deptno order by dept.loc ;
ENAME
JOB DEPTNO DNAME LOC
--------------- ---------- ----------
--------------- ---------------
PRANEETHA
PRESIDENT 10
accounting hyd
JAIPAL
MANAGER 10
accounting hyd
RAJU
CLERK 10
accounting hyd
SURESH
CLERK 10
accounting hyd
KIRAN
CLERK 10
accounting hyd
SANDHYA
MANAGER 30 sales kmr
ARAVIND
SALESMAN 30 sales kmr
SHARATH
SALESMAN 30 sales kmr
RAVI
SALESMAN 30 sales kmr
NAGESH
CLERK 30 sales kmr
VIHARIKA
SALESMAN 30 sales kmr
ENAME
JOB DEPTNO DNAME LOC
--------------- ---------- ----------
--------------- ---------------
DHASHARATH
MANAGER 20
research nzb
LAXMI
CLERK 20
research nzb
MAHESH
MANAGER 20
research nzb
LAVANYA
ANALIST 20
research nzb
15 rows selected.
SQL>
insert into emp values(1345,'RAMESH','SALESMAN',1782,'23-OCT-2013',
15000,500,10);
1 row created.
SQL> SELECT * FROM EMP;
EMPNO
ENAME JOB MGR HIREDATE SAL COMM
---------- --------------- ----------
---------- --------- ---------- ----------
DEPTNO
----------
1369 RAJU CLERK 1698 17-DEC-80 1500
10
1499 RAVI SALESMAN 1698 20-FEB-81 3000
300
30
1521 SHARATH SALESMAN 1698 25-JAN-81 1300
500
30
EMPNO ENAME JOB MGR HIREDATE SAL
COMM
---------- --------------- ----------
---------- --------- ---------- ----------
DEPTNO
----------
1566 DHASHARATH MANAGER 1839 12-APR-81 2975
20
1654 ARAVIND SALESMAN 1839 12-SEP-91 1250
400
30
1698 SANDHYA MANAGER 1839 01-MAY-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL
COMM
---------- --------------- ----------
---------- --------- ---------- ----------
DEPTNO
----------
1782 JAIPAL MANAGER 1839 09-JUL-81 2450
10
1788 LAVANYA ANALIST 1566 09-JUL-85 3000
20
1839 PRANEETHA
PRESIDENT 1566
16-NOV-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL
COMM
---------- --------------- ----------
---------- --------- ---------- ----------
DEPTNO
----------
1844 VIHARIKA SALESMAN 1698 16-SEP-81 1500
0
30
1876 LAXMI CLERK 1788 09-JUL-83 1100
20
1900 NAGESH CLERK 1788 19-JUL-99 1000
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- --------------- ----------
---------- --------- ---------- ----------
DEPTNO
----------
1902 MAHESH MANAGER 1566 09-JAN-14 3000
20
1934 SURESH CLERK 1566 09-MAR-14 1300
10
1340 KIRAN CLERK 1782 29-APR-14 1300
10
EMPNO ENAME JOB MGR HIREDATE SAL
COMM
---------- --------------- ----------
---------- --------- ---------- ----------
DEPTNO
----------
1345 RAMESH SALESMAN 1782 23-OCT-13 15000
500
10
16 rows selected.
14.SQL> select * from emp where sal*12
in(select max(sal) from emp group by deptno);
EMPNO ENAME JOB MGR HIREDATE SAL
COMM
---------- --------------- ----------
---------- --------- ---------- ----------
DEPTNO
----------
1654 ARAVIND SALESMAN 1839 12-SEP-91 1250 400
30
15. SQL>
select dname from dept where deptno not in(select deptno from emp);
DNAME
---------------
advertisement
operations
PL SQL
DBMS PL/SQL Programs
Q1.sum of 2 nos
DECLARE
n1 number;
n2 number;
sum1 number;
BEGIN
n1:=&n1;
n2:=&n2;
sum1:=n1+n2;
dbms_output.put_line('sum of 2 nos is'||sum1);
end;
/
Q2.greatest of 2 nos
DECLARE
n1 number;
n2 number;
BEGIN
n1:=&n1;
n2:=&n2;
if n1>n2
THEN dbms_output.put_line('greatest of 2 nos is'||n1);
ELSE
dbms_output.put_line('greatest is '||n2);
END IF;
END;
/
Q3.print n numbers
DECLARE
n1 number;
BEGIN
n1:=&n1;
FOR i in 1..n1
loop
dbms_output.put_line(''||i);
end loop;
end;
/
Q4. Write a program to find the sum of the digits of the number:
DECLARE
N number ;
S NUMBER :=0;
R NUMBER;
begin
n:=&N;
WHILE N<>0 LOOP
R := MOD(N,10);
S := S + R;
N := TRUNC(N/10);
end loop;
dbms_output.put_line('THE SUM OF THE DIGITS = ' || S);
end;
/
Q5. Program to accept a number from user and print number in reverse order.
Program: -
declare
num1 number(5);
num2 number(5);
rev number(5);
begin
num1:=&num1;
rev:=0;
while num1>0
loop
num2:=num1 mod 10;
rev:=num2+(rev*10);
num1:=floor(num1/10);
end loop;
dbms_output.put_line('Reverse number is: '||rev);
end;
/
Q6. Program to reverse a string.
declare
str1 varchar2(30);
len number(3);
str2 varchar2(30);
i number(3);
begin
str1:='&str1';
len:=length(str1);
for i in reverse 1..len
loop
str2:=str2 || substr(str1,i,1);
end loop;
dbms_output.put_line('Reverse string is: '||str2);
end;
/
Q7.program to find a number prime or not
declare
n number;
i number;
counter number;
begin
n:=&n;
i:=1;
counter:=0;
if n=1
then dbms_output.put_line('1 is neither prime nor composite.');
elsif n=2
then dbms_output.put_line('2 is even prime');
else
for i in 1..n loop
if mod(n,i)=0
then counter:=counter+1;
end if;
end loop;
end if;
if counter=2
then dbms_output.put_line(n||' is a prime No.');
else
dbms_output.put_line(n||' is a not prime No.');
end if;
end;
/
Q8. Program to print sum of first n natural numbers.
Declare
i number:=0;
n number;
sum1 number:=0;
Begin
n:=&n;
while i<n+1
loop
sum1:=sum1+i;
dbms_output.put_line(i);
i:=i+1;
end loop;
dbms_output.put_line('The sum is:'||sum1);
End;
/
Q9.program to find fabonacci series
DECLARE
num NUMBER;
a NUMBER:= 0;
b NUMBER:= 1;
c NUMBER;
BEGIN
num:='&Input_Number';
DBMS_OUTPUT.PUT_LINE(a);
DBMS_OUTPUT.PUT_LINE(b);
FOR i in 3..num LOOP
c := a + b;
DBMS_OUTPUT.PUT_LINE(c);
a:=b;
b:=c;
END LOOP;
END;
/
Q10.Program to find factorial of a number
DECLARE
num NUMBER:='&Input_Number';
i NUMBER;
f NUMBER:=1;
BEGIN
FOR i IN 1..num LOOP
f := f * i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(f||' Is Factorial Of '||num);
END;
/
Q11.program to find multiplication table of a given number
DECLARE
num1 NUMBER;
i NUMBER;
BEGIN
num1:='&INPUT_NUMBER';
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(num1*i);
EXIT WHEN i=10;
END LOOP;
END;
/
Q12.program to find area of the circle
declare
c number(5);
r number:='&no';
a number(5);
begin
c:=3.14*r*r;
dbms_output.put_line('area of circle is'||c);
end;
/
Q13. program to check whether number is Armstrong or not.
declare
pnum number(5);
tot number(5);
lp number(3);
tmp number(5);
begin
pnum:=&pnum;
tmp:=pnum;
tot:=0;
while tmp>0
loop
lp:=tmp mod 10;
tot:= tot + (lp*lp*lp);
tmp:=floor(tmp/10);
end loop;
if(tot like pnum) then
dbms_output.put_line(pnum||' is armstrong.');
else
dbms_output.put_line(pnum||' is not armstrong.');
end if;
end;
/
Q14.program to find greatest of three numbers
declare
a number;
b number;
c number;
begin
a:=&a;
b:=&b;
c:=&c;
if a=b and b=c and c=a then
dbms_output.put_line('ALL ARE EQUAL');
elsif a>b and a>c then
dbms_output.put_line('A IS GREATER');
elsif b>c then
dbms_output.put_line('B IS GREATER');
else
dbms_output.put_line('C IS GREATER');
end if;
end;
/
Q15.program to find palindrome or not
DECLARE
len NUMBER;
str VARCHAR2(20) := '&Input_String';
chkstr VARCHAR2(20);
BEGIN
len := LENGTH(str);
FOR i IN REVERSE 1..len LOOP
chkstr := chkstr||SUBSTR(str,i,1);
END LOOP;
IF chkstr = str THEN
DBMS_OUTPUT.PUT_LINE(str||' Is A Palindrome!');
ELSE
DBMS_OUTPUT.PUT_LINE(str||' Is Not A Palindrome!');
END IF;
END;
Q16.program to generate prime numbers upto n numbers
DECLARE
Num number;
prime integer;
BEGIN
Num:=#
FOR i IN 1..num LOOP
prime :=1;
FOR j IN 2..i-1
LOOP
IF MOD(i,j)=0 THEN
prime:=0;
END IF;
EXIT WHEN prime=0;
END LOOP;
IF prime=1 THEN
DBMS_OUTPUT.PUT_LINE(i);
END IF;
END LOOP;
END;
/
No comments:
Post a Comment