Scribd
Upload
24 views12 pages

PLSQL Progs

The document contains SQL commands for creating tables and performing various operations on employee and department data, including queries for salary analysis, employee updates, and data manipulation. It also includes PL/SQL code for procedures, loops, and conditional statements to manage employee salaries and check for specific number properties. The document showcases examples of data retrieval, updates, and checks for conditions such as prime numbers and palindromes.

Uploaded by

Gk Pradeep
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
24 views12 pages

PLSQL Progs

The document contains SQL commands for creating tables and performing various operations on employee and department data, including queries for salary analysis, employee updates, and data manipulation. It also includes PL/SQL code for procedures, loops, and conditional statements to manage employee salaries and check for specific number properties. The document showcases examples of data retrieval, updates, and checks for conditions such as prime numbers and palindromes.

Uploaded by

Gk Pradeep
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 12

Create table Dept(deptno number(3),dname char(10),loc char(10));

Create table Employee(sno number(3),name char(10),job char(8),mgr char(8),hdate date,sal


number(6),comm number(6),deptno number(3));

/*Enter five records for each table */

1. Find out the details of top 3 earners of a company.

Select * from employee where rownum<=3 order by sal asc;

2. Display those managers name whose salary is more than average salary of his employees.

Select * from employee where sal>= (Select avg(sal) from employee);

3. Display those employees who joined the company before 15 of the month?

Select * from employee where hdate<='15-dec-2011';

6. update employee salary by 15%, whose experience is greater than 10 years.

update employee set sal=sal+15/100 where sysdate-hdate>10*365;


0 row(s) updated.
7. Delete the employees, who completed 30 years of service.

Delete from employee where sysdate-hdate>30*365;


0 row(s) deleted.
8. Determine minimum salary of an employee and his details who joined on the same date.

Select * from employee where sal=( Select min(sal) from employee);

9. Determine the count of employees, who are taking commission.

Select count(*) from employee where comm is not null and comm>0;

10. Create a view,which cointain employee names and their manager names working in sales
department.

Create view view1 as select name,mgr from employee where deptno=11;

View created

12. Determine the names of employees, who take highest salary in there departments.

Select * from employee where sal=( Select max(sal) from employee);

14. Determine the employees, who are located at the same place.

Select * from employee where sal=(Select min(sal)from employee);


15. Determine the department which does not contain any employees

Select deptno from employee where sno is null and name is null;
no data found

pl/sql
16.

DECLARE

var_rows number(5);

BEGIN

update employee set sal=sal+(sal*(30/100)) where sysdate-hdate>=40*365;

IF SQL%NOTFOUND THEN

dbms_output.put_line('None of the salaries where updated');

ELSIF SQL%FOUND THEN

var_rows := SQL%ROWCOUNT;

dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');

END IF;

END;

OutPut:
None of the salaries where updated

17. Amstrong

DECLARE
n number;

num1 number;

s number;

d number;

BEGIN

n:=153;

num1:=n;

s:=0;

while n<>0

loop

d:=mod(n,10);

s:=s+(d*d*d);

n:=round(n/10);

end loop;

dbms_output.put_line(s);

if s<>num1 then

dbms_output.put_line(' given number is not Amstrong');

else

dbms_output.put_line(' given number is Amstrong');

end if;

END;

Output:

given number is Amstrong


Statement processed.
19. Swapping

DECLARE

a number;

b number;

BEGIN

a:=5;

b:=6;

dbms_output.put_line(' values before swapping');

dbms_output.put_line(' value of a is '||a);

dbms_output.put_line(' value of b is '||b);

a:=a+b;

b:=a-b;

a:=a-b;

dbms_output.put_line(' value of a is '||a);

dbms_output.put_line(' value of b is '||b);

END;

Output:

values before swapping


value of a is 5
value of b is 6
value of a is 6
value of b is 5

Statement processed.

20.

DECLARE

var_rows number(5);
var_sno number;

vi number;

BEGIN

dbms_output.put_line(var_sno||'Enter Employee number ');

var_sno:=&vi;

update employee set sal=sal+(sal*(20/100)) where sno=var_sno;

IF SQL%NOTFOUND THEN

dbms_output.put_line('None of the salaries where updated');

ELSIF SQL%FOUND THEN

var_rows := SQL%ROWCOUNT;

dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');

END IF;

END;

OutPut:

Enter Employee number


Salaries for 1employees are updated

1 row(s) updated.

0.00 seconds

21. Multiplication table

DECLARE

num1 number;

n number;

i number;

BEGIN

num1:=3;
i:=1;

while i<=10

loop

dbms_output.put_line(num1||' * '||i||' = '||num1*i);

i:=i+1;

end loop;

i:=1;

while i<=10

loop

dbms_output.put_line(7||' * '||i||' = '||7*i);

i:=i+1;

end loop;

END;

Output:

3 * 1 = 3
3 * 2 = 6
3 * 3 = 9
3 * 4 = 12
3 * 5 = 15
3 * 6 = 18
3 * 7 = 21
3 * 8 = 24
3 * 9 = 27
3 * 10 = 30
7 * 1 = 7
7 * 2 = 14
7 * 3 = 21
7 * 4 = 28
7 * 5 = 35
7 * 6 = 42
7 * 7 = 49
7 * 8 = 56
7 * 9 = 63
7 * 10 = 70
Statement processed.
22. Prime Number

DECLARE

num number:=25;

i number:=2;

BEGIN

while i<num

loop

if(mod(num,i)<>0) then

i:=i+1;

else

exit;

end if;

end loop;

if i<>num then

dbms_output.put_line(' Given number is not Prime');

else

dbms_output.put_line(' Given number is prime');

end if;

END;

Output:

Given number is not Prime

Statement processed.

0.01 seconds
24.

DECLARE

var_rows number(5);

BEGIN

update employee set sal=sal+(sal*(10/100)) where deptno=30;

IF SQL%NOTFOUND THEN

dbms_output.put_line('None of the salaries where updated');

ELSIF SQL%FOUND THEN

insert into RaiseTable values(105,'kk','12-jan-2012',8500,2000,30);

END IF;

END;

25.

CREATE OR REPLACE PROCEDURE employee_sal_update

is

var_rows number(5);

begin

update employee set sal=sal+500 where ((comm/sal)*100)<=9;

IF SQL%NOTFOUND THEN

dbms_output.put_line('There is no employee whose commission is less than 9 percentage ');

ELSIF SQL%FOUND THEN

var_rows := SQL%ROWCOUNT;

dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');

END IF;
end;

/* To run procedure*/

BEGIN

employee_sal_update;

END;

OutPut:
There is no employee whose commission is less than 9 percentage

Statement processed.

26. Polindrum

DECLARE

num1 number;

r number;

d number;

n number;

BEGIN

num1:=121;

n:=num1;

r:=0;

while num1<>0

loop

d:=mod(num1,10);

r:=r*10+d;

num1:=round(num1/10);

end loop;
dbms_output.put_line(r);

if r=n then

dbms_output.put_line(' given number is polindrum');

else

dbms_output.put_line(' given number is not polindrum');

end if;

END;

Output:

121
given number is polindrum

Statement processed.

0.00 seconds

27.
CREATE OR REPLACE PROCEDURE employee_sal_update_marc
is
var_rows number(5);
begin
update employee set sal=sal+(sal*(15/100)) where job='marc';
IF SQL%NOTFOUND THEN
dbms_output.put_line('There is no employee whos job is marcketing ');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries of ' || var_rows || 'employees are updated');
END IF;
end;
/
Output
Procedure created.

/* To run procedure*/

BEGIN

employee_sal_update_marc;

END;

OutPut:

Salaries of 2 employees are updated


Statement processed.

0.00 seconds

You might also like