Write a PL/SQL block that will take an input in the form of department number. Decrease the salaries of the employees working in that department by 0.75%. If at least a record is updated keep the change permanent else print appropriate message and terminate the block.

Write a PL/SQL block that will take an input in the form of department number. 
Decrease the salaries of the employees working in that department by 0.75%.
 If at least a record is updated keep the change permanent else print appropriate message and terminate the block.

DECLARE
 DNO EMP.DEPTNO%TYPE;
 SALARY EMP.SAL%TYPE;
 DECR EMP.SAL%TYPE;
BEGIN
 SAVEPOINT S1;
 FOR DATA IN (SELECT EMPNO,DEPTNO,SAL FROM EMP WHERE DEPTNO=&DNO)
 LOOP
  DECR:=(DATA.SAL*0.75)/100;
  SALARY:=DATA.SAL-DECR;
  UPDATE EMP SET SAL=SALARY WHERE EMPNO=DATA.EMPNO;
 END LOOP;
 IF SQL%ROWCOUNT>=1 THEN
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('One or More record(s) Updated. ');
 ELSE
  ROLLBACK TO S1;
  DBMS_OUTPUT.PUT_LINE('ROLLBACK THE UPDATION.');
 END IF;
END;
/

Write a PL/SQL block that will update the salaries of the employees working with SCOTT. If more than FOUR employees salaries are affected by the increment take back the increment, else give the increment permanently.

Write a PL/SQL block that will update the salaries of the employees working with SCOTT. 
If more than FOUR employees salaries are affected by the increment take back the increment,
 else give the increment permanently.

DECLARE
 ENM EMP.ENAME%TYPE;
 TOT EMP.SAL%TYPE;
 CNT NUMBER(3);
BEGIN
 TOT:=0;
 CNT:=0;
 SAVEPOINT A;
 FOR I IN (SELECT EMPNO,ENAME,SAL FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='SCOTT'))
 LOOP
  TOT:=SAL+1000;
  UPDATE EMP SET SAL=TOT WHERE EMPNO=I.EMPNO;
  CNT:=CNT+1;
  END LOOP;
 END LOOP;
 IF CNT>4 THEN
  ROLLBACK TO A;
  DBMS_OUTPUT.PUT_LINE('ROLLBACK THE UPDATION.');
 ELSE
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('COMMITED THE UPDATION.');
 END IF;
END;
/

Write a PL/SQL block that will take an input in the form of department number. Increase the salaries of the employees working in the department by 5.5%. If the increment updates the total salary of that department by 255 take back the increment else give the increment permanently.

Write a PL/SQL block that will take an input in the form of department number. 
Increase the salaries of the employees working in the department by 5.5%.If the increment updates the total salary of that department by 255 take back the increment else give the increment permanently.

DECLARE
 dno emp.deptno%type;
 tot emp.sal%type;
 salary emp.sal%TYPE;
 incr emp.sal%TYPE;
BEGIN
 tot:=0;
 SAVEPOINT s1;
 FOR data IN (SELECT EMPNO,DEPTNO,SAL FROM EMP WHERE DEPTNO=&dno)
 LOOP
  incr:=(data.SAL*5.5)/100;
  salary:=data.SAL+incr;
  tot:=tot+incr;
  UPDATE EMP SET SAL=salary WHERE EMPNO=data.EMPNO;
 END LOOP;
 IF tot>=255 THEN
  ROLLBACK TO s1;
  DBMS_OUTPUT.PUT_LINE('Increment Exceed by 255. so,Increment not given permanently');
 ELSE
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Increment is given permanently.');
 END IF;
END;
/

Write a PL/SQL block to take an input in the form of department number. If the total salary paid to that department is greater than 1900 then raise the user defined exception, print appropriate message and terminate the block. If the total salary is less than or equal to 1900 update the salaries of the employees working in the department by 2.5%.

Write a PL/SQL block to take an input in the form of department number. 
If the total salary paid to that department is greater than 1900 then raise the user defined exception, print appropriate message and terminate the block. If the total salary is less than or equal to 1900 update the salaries of  the employees working in the department by 2.5%.

DECLARE
NO NUMBER(2);
SUM_SAL NUMBER;
SAL_MORE EXCEPTION;
BEGIN
NO:=&NO;
SELECT SUM(SAL) INTO SUM_SAL FROM EMP WHERE DEPTNO=NO;
IF SUM_SAL>1900 THEN
 RAISE SAL_MORE;
ELSE
 DBMS_OUTPUT.PUT_LINE('UPDATE THE SALARIES OF THE EMPLOYEES WORKING IN THE DEPARTMENT BY 2.5%');
END IF;
EXCEPTION
WHEN SAL_MORE THEN
 DBMS_OUTPUT.PUT_LINE('TOTAL SALARY PAID TO THAT DEPARTMENT IS GREATER THAN 1900.');
END;

/

Write a PL/SQL block to take an input in the form of job. Print employee information if the job is with one employee in the EMP table, if the job is with more than one employee print appropriate message and terminate the block, and if the job is not present print appropriate message and terminate the block.

Write a PL/SQL block to take an input in the form of job. 
Print employee information if the job is with one employee in the EMP table, 
if the job is with more than one employee print appropriate message and 
terminate the block, and if the job is not present print appropriate message and terminate the block.

DECLARE
JOB_SEARCH VARCHAR2(9);
NO NUMBER;
JOB_EXCEP1 EXCEPTION;
JOB_EXCEP2 EXCEPTION;
BEGIN
JOB_SEARCH:='&JOB_SEARCH';
SELECT COUNT(*) INTO NO FROM EMP WHERE JOB=JOB_SEARCH;
IF NO=1 THEN
 DBMS_OUTPUT.PUT_LINE('PRINT EMPLOYEE INFORMATION...');
ELSIF NO=0 THEN
 RAISE JOB_EXCEP1;
ELSE
 RAISE JOB_EXCEP2;
END IF;
EXCEPTION
WHEN JOB_EXCEP1 THEN
 DBMS_OUTPUT.PUT_LINE('NO JOB FOUND.');
WHEN JOB_EXCEP2 THEN
 DBMS_OUTPUT.PUT_LINE('MORE THAN ONE JOB FOUND.');
END;
/

Write a PL/SQL block to take an input in the form of department number. Print department information if the department number is present in the DEPT table else print appropriate message and terminate the block.

Write a PL/SQL block to take an input in the form of department number. 
Print department information if the department number is present in the DEPT
 table else print appropriate message and terminate the block.

DECLARE
NO NUMBER(2);
CNT NUMBER;
DNO DEPT.DEPTNO%TYPE;
DNM DEPT.DNAME%TYPE;
DLOC DEPT.LOC%TYPE;
NO_ROWS EXCEPTION;
BEGIN
NO:=&NO;
SELECT COUNT(*) INTO CNT FROM DEPT WHERE DEPTNO=NO;
IF CNT=0 THEN
 RAISE NO_ROwS;
ELSE
 SELECT DEPTNO, DNAME, LOC INTO DNO, DNM, DLOC FROM DEPT WHERE DEPTNO=NO;
 DBMS_OUTPUT.PUT_LINE(DNO||' '||DNM||' '||DLOC);
END IF;
EXCEPTION
WHEN NO_ROWS THEN
 DBMS_OUTPUT.PUT_LINE('NO DATA FOUND.');
END;
/

Write a cursor that will take input as a month. Display the inputted month wise report in the following format.

Write a cursor that will take input as a month. Display the inputted month wise report in the  following format.

DECLARE
MONTH VARCHAR2(3);
CONS NUMBER;
TOT NUMBER;
CURSOR C1 IS SELECT A.CUST_NO, A.CUST_NAME, B.PREV_READ, B.CURR_READ, B.UNIT_PRICE FROM CUSTOMER_MST A,
 CUSTOMER_DTL B WHERE A.CUST_NO=B.CUST_NO AND B.BILL_MON='DEC';


CUST_N CUSTOMER_MST.CUST_NAME%TYPE;
CURR_R CUSTOMER_DTL.CURR_READ%TYPE;
PREV_R CUSTOMER_DTL.PREV_READ%TYPE;
U_PRICE CUSTOMER_DTL.UNIT_PRICE%TYPE;
C_NO CUSTOMER_DTL.CUST_NO%TYPE;
BEGIN
OPEN C1;
MONTH:='&MONTH';
LOOP
FETCH C1 INTO C_NO, CUST_N, PREV_R, CURR_R, U_PRICE;
CONS:=CURR_R-PREV_R;
TOT:=CONS*U_PRICE;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(C_NO||' '||CUST_N||' '||CONS||' '||TOT);
END LOOP;
CLOSE C1;
END;
/

Write a cursor to display the Electricity consumption report in following format

Write a cursor to display the Electricity consumption report in following format

DECLARE
CONS NUMBER;
TOT_CONS NUMBER;
CURSOR C1 IS SELECT B.CUST_NO, A.CUST_NAME, B.CURR_READ, B.PREV_READ, A.CITY FROM CUSTOMER_MST A, CUSTOMER_DTL B WHERE A.CUST_NO=B.CUST_NO;
CUST_N CUSTOMER_MST.CUST_NAME%TYPE;
CUST_C CUSTOMER_MST.CITY%TYPE;
CURR_R CUSTOMER_DTL.CURR_READ%TYPE;
PREV_R CUSTOMER_DTL.PREV_READ%TYPE;
C_NO CUSTOMER_DTL.CUST_NO%TYPE;
BEGIN
OPEN C1;
TOT_CONS:=0;
DBMS_OUTPUT.PUT_LINE('Electricity  Consumption');
DBMS_OUTPUT.PUT_LINE('==================================================');
DBMS_OUTPUT.PUT_LINE('customer no       Name        Comnsumption    City');
DBMS_OUTPUT.PUT_LINE('==================================================');
LOOP
FETCH C1 INTO C_NO, CUST_N, CURR_R, PREV_R, CUST_C;
CONS:=CURR_R-PREV_R;
TOT_CONS:=TOT_CONS+CONS;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(C_NO||' '||CUST_N||' '||CONS||' '||CUST_C);
END LOOP;
DBMS_OUTPUT.PUT_LINE('==================================================');
DBMS_OUTPUT.PUT_LINE('TOTAL CONSUMPTION: '||TOT_CONS);
CLOSE C1;
END;
/

Display the employee information dept wise.

Display the employee information dept wise.

DECLARE
CURSOR C1(NO NUMBER) IS SELECT *FROM EMP WHERE DEPTNO=NO;
EMP_REC C1%ROWTYPE;
BEGIN
OPEN C1(10);
LOOP
FETCH C1 INTO EMP_REC;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(EMP_REC.EMPNO||','||EMP_REC.ENAME);
END LOOP;
CLOSE C1;
END;
/

Display empno, name, salary, job and deptno of employees using cursor & %rowtype

Display empno, name, salary, job and deptno of employees using cursor & %rowtype

DECLARE
CURSOR c1 IS SELECT EMPNO,ENAME,SAL,JOB,DEPTNO FROM EMP;
EMP_REC c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
 FETCH c1 INTO EMP_REC;
 EXIT WHEN c1%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(EMP_REC.EMPNO || ',' || EMP_REC.ENAME|| ',' || EMP_REC.SAL|| ',' || EMP_REC.JOB|| ',' || EMP_REC.DEPTNO);
END LOOP;
CLOSE c1;
END;
/

Print Update Employee Salary using PL/SQL BLOCK using Cursor

Print Update Employee Salary using PL/SQL BLOCK using Cursor

declare

cursor c1 is select empno,sal,deptno from emp where deptno=10;

csal emp.sal%type;
ceno emp.empno%type;
cdno emp.deptno%type;

begin
open c1;

dbms_output.put_line('Employeee Info');
dbms_output.put_line('==============');
dbms_output.put_line('EmpNo   EmpName  EmpSalary');
loop
fetch c1 into ceno,csal,cdno;

update emp set sal=sal+(csal*.10) where deptno=cdno;
exit when c1%notfound;
dbms_output.put_line(ceno||'     '||cdno||'     '||csal);

end loop;
close c1;
end;
/

Print Employee Information ALL using PL/SQL BLOCK using Cursor

Print Employee Information ALL using PL/SQL BLOCK using Cursor

declare

cursor c1 is select * from emp ;

e c1%rowtype;

begin
open c1;

dbms_output.put_line('Employeee Info');
dbms_output.put_line('==============');
dbms_output.put_line('EmpNo   EmpName  EmpSalary');
loop
fetch c1 into e;
exit when c1%notfound;
dbms_output.put_line(e.empno||'     '||e.ename||'     '||e.sal);
end loop;
close c1;
end;
/

Table Creation With Constraint : CUSTMER_MST

TABLE NAME : CUSTOMER_MST

create table customer_mst
(cust_no varchar2(6) primary key,
cust_name varchar2(15) not null,
address varchar2(20),
city varchar2(10),
constraint CHK_CUST_NO check (cust_no like 'ANA%' or cust_no like 'VVN%' or cust_no like 'BAK%'),
CONSTRAINT CHK_CITY check(city in ('ANAND','VVNAGAR','BAKROL')));

insert into customer_mst values('ANA001','ALAN','UNDI SHERI','ANAND');
insert into customer_mst values('VVN001','SMITH','NANA BAZAR','VVNAGAR');
insert into customer_mst values('ANA002','BLAKE','TOWN HALL ROAD','ANAND');
insert into customer_mst values('BAK001','ADAMS','VADTAL ROAD','BAKROL');
insert into customer_mst values('VVN002','WILLIAMS','MOTA BAZAR','VVNAGAR');
insert into customer_mst values('ANA003','RODRICKS','TOWN HALLROAD','ANAND');

Table Creation With Constraint : CUSTMER_DTL

TABLE NAME : CUSTOMER_DTL

create table customer_dtl
(
cust_no varchar2(6) references customer_mst,
bill_mon varchar2(3),
prev_read number(5),
curr_read number(5),
unit_price number(5,2) check(unit_price>0),
due_date date);

insert into customer_dtl values('ANA001','NOV',1234,2545,5.50,'26-DEC-05');
insert into customer_dtl values('VVN001','DEC',256,365,5.50,'20-JAN-06');
insert into customer_dtl values('ANA002','NOV',245,578,5.50,'26-DEC-05');
insert into customer_dtl values('BAK001','DEC',45,145,5.50,'20-JAN-06');
insert into customer_dtl values('VVN002','DEC',254,545,5.50,'20-JAN-06');
insert into customer_dtl values('ANA003','NOV',2255,2365,5.50,'26-DEC-05');

Table Creation With Constraints in Oracle :EMP Table

FOR SYBCA , MCA , Msc IT Students

TABLE NAME : EMP

create table emp
(
empno number(4) primary key,
ename varchar2(10),
job varchar2(9),
mg number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2) references dept(deptno)
);

insert into emp values(7369,'smith','clerk',7902,'17-dec-80',800,0,20);
insert into emp values(7499,'allen','salesman',7698,'20-feb-81',1600,300,30);
insert into emp values(7521,'ward','salesman',7698,'22-feb-81',1250,500,30);
insert into emp values(7566,'jones','manager',7839,'02-apr-81',2975,0,20);
insert into emp values(7654,'martin','salesman',7698,'28-sep-81',1250,0,30);
insert into emp values(7698,'blake','manager',7839,'01-may-81',2850,0,30);
insert into emp values(7782,'clark','manager',7839,'09-jun-81',2450,0,10);
insert into emp values(7788,'scott','analyst',7566,'09-dec-81',3000,0,20);
insert into emp values(7839,'king','president',,'17-nov-81',5000,0,10);
insert into emp values(7844,'turner','salemsman',7698,'08-sep-81',1500,0,30);
insert into emp values(7876,'adams','clerk',7788,'12-jan-83',1100,0,20);
insert into emp values(7900,'james','clerk',7698,'03-dec-81',950,0,30);
insert into emp values(7902,'ford','analyst',7566,'03-dec-81',3000,0,20);
insert into emp values(7934,'miller','clerk',7782,'23-jan-82',1300,0,10);

Table Creation With Constraints in Oracle : DEPT Table

TABLE NAME : DEPT

create table dept
(
deptno number(2) primary key,
dname varchar2(14),
loc varchar2(13));

insert into dept values(10,'accounting','new york');
insert into dept values(20,'research','dallas');
insert into dept values(30,'sales','chicago');
insert into dept values(40,'operations','bostan');

Write a Python Program to Print Summation,Subtraction,Multiplication And Division of Two Numbers

"Print Summation,Subtraction,Multiplication And Division of Two Numbers"

a=eval(input('Enter Value A:'))
b=eval(input('Enter Value B:'))
print('Summation :',(a+b))
print('Subtraction :',(a-b))
print('Multiplication :',(a*b))
print('Division :',(a/b))

Output :

>>>
Enter Value A:20
Enter Value B:10
Summation:30
Subtraction:10
Multiplication:200
Division:2
>>>

Write a Python Program to Print Summation of Two Numbers

Write a Python Program to Print Summation of Two Numbers

"Print Summation of Two Numbers"

a=eval(input('Enter Value A:'))
b=eval(input('Enter Value B:'))
print('Summation :',(a+b))
Output :

>>>
Enter Value A:23
Enter Value B:21
Summation :44
>>>

Write a Python Program to Print Value Using Variable

Write a Python Program to Print Value Using Variable

"Print Value Using Variable"

a=input('Enter Value:')
print('Value :',a)

Output :

>>>
Enter Value:23
Value:23
>>>

Write a Python Program to Print Hello World

Write a Python Program to Print Hello World

"Print Hello World Message"
print('Hello World')

Output :

>>>
Hello World
>>>