Full course of c++ programming language
Click here > C++ programming language
=========================================
1]Write a PL/SQL block for insertion into table EMPDEPT table with thefollowing.
Calculations:
HRA= 50% of BASICDA= 20% of BASIC PF= 7% of BASIC
NETPAY= BASIC + HRA + DA + PF
EMPDEPT (ENO, ENAME, DEPTNO, BASIC, HRA, DA, PF)
SQL> create table Empdept
2 (Eno number primary key,
3 Ename varchar(20),
4 Deptno number,
5 Basic number,
6 HRA number,
7 DA number,
8 PF number,
9 Netpay number);
Table created.
set severoutput on
declare
Eno number:=&Eno;
Ename varchar2(20):=&Ename;
Deptno number:=&Deptno;
Basic number:=&Basic;
HRA number:=Basic*0.5;
DA number:=Basic*0.2;
PF number:=Basic*0.07;
Netpay number:=Basic+HRA+DA-PF;
begin
insert into Empdept(Eno,Ename,Deptno,Basic,HRA,DA,PF,Netpay)values(Eno,Ename,Deptno,Basic,HRA,DA,PF,Netpay);
end;
/
SQL> @q1
Enter value for eno: 4
old 2: Eno number:=&Eno;
new 2: Eno number:=4;
Enter value for ename: 'Mohit'
old 3: Ename varchar2(20):=&Ename;
new 3: Ename varchar2(20):='Mohit';
Enter value for deptno: 103
old 4: Deptno number:=&Deptno;
new 4: Deptno number:=103;
Enter value for basic: 1200
old 5: Basic number:=&Basic;
new 5: Basic number:=1200;
PL/SQL procedure successfully completed.
========================================
2]Write PL/SQL block that will accept employee number from user and deduct an amount of Rs.200 from the
inputted employee, if he has a salary less than 1000 after Salary is deducted, it display message‘s Salary is
less than 1000’. The process is to be fired on table employee (emp_no, name, salary).
SQL> create table employee
2 (emp_no number primary key,
3 name varchar(20),
4 salary number);
Table created.
set serveroutput on
declare
id number:=&id;
sal number;
begin
update employee set salary=salary-200 where emp_no=id;
select salary into sal from employee where emp_no=id;
if sal<1000 then
dbms_output.put_line('salary is less than 1000');
end if;
end;
/
SQL> @q2
Enter value for id: 1
old 2: id number:=&id;
new 2: id number:=1;
salary is less than 1000
PL/SQL procedure successfully completed.
==========================================
3]Consider the following table and Do as Directed
Product (Prod_No, Name, Price_per_Unit, Quantity_Sold, Total_Sale)
Note: Consider ProdNo, Name, PricePerUnit and QuantitySold columns have values.
TotalSale column has null values.
Write a PL/SQL block that accepts prodNo from user to calculate total sale for that product and update it in
product table.
SQL> create table Product
2 (prod_no number primary key,
3 name varchar(20),
4 price_per_unit number,
5 quantity_sold number,
6 total_sale number)
7 /
Table created.
set serveroutput on
declare
p_no number:=&prod_no;
t_sale number;
begin
select price_per_unit*quantity_sold into t_sale from Product where Prod_no=p_no;
update Product set total_sale=t_sale where prod_no=p_no;
dbms_output.put_line('Total sale for product '||p_no||' is '||t_sale);
end;
/
SQL> select * from Product;
PROD_NO NAME PRICE_PER_UNIT QUANTITY_SOLD TOTAL_SALE
---------- -------------------- -------------- ------------- ----------
1 Laptop 30000 3
SQL> @q3
Enter value for prod_no: 1
old 2: p_no number:=&prod_no;
new 2: p_no number:=1;
Total sale for product 1 is 90000
PL/SQL procedure successfully completed.
==========================================
4]Create following tables.
Doctormaster (doct_id, name, address, specialization)
Patientmaster (pat_id, pat_name, DOB, address, phoneno, doct_id)
Write a PL/SQL block to take input of patient id and display patient information if available. If patient
information is not available then print appropriate message.
SQL> create table Doctormaster
2 (doct_id number primary key,
3 name varchar(20),
4 address varchar(20),
5 specialization varchar(20));
Table created.
1 create table Patientmaster
2 (pat_id number primary key,
3 pat_name varchar(20),
4 dob date,
5 address varchar(20),
6 phoneno number(10),
7 doct_id number,
8* constraint d foreign key(doct_id) references Doctormaster(doct_id))
SQL> /
Table created.
DECLARE
v_pat_id number:= &Enter_patient_id;
v_pat_name varchar(20);
v_dob date;
v_address varchar(20);
v_phoneno number(10);
v_doct_id number;
v_doct_name varchar(20);
BEGIN
SELECT pat_name, dob, address, phoneno, doct_id INTO v_pat_name, v_dob, v_address, v_phoneno, v_doct_id
FROM Patientmaster WHERE pat_id = v_pat_id;
SELECT name INTO v_doct_name FROM Doctormaster WHERE doct_id = v_doct_id;
DBMS_OUTPUT.PUT_LINE('Patient ID: ' || v_pat_id);
DBMS_OUTPUT.PUT_LINE('Patient Name: ' || v_pat_name);
DBMS_OUTPUT.PUT_LINE('Date of Birth: ' || v_dob);
DBMS_OUTPUT.PUT_LINE('Address: ' || v_address);
DBMS_OUTPUT.PUT_LINE('Phone Number: ' || v_phoneno);
DBMS_OUTPUT.PUT_LINE('Doctor Name: ' || v_doct_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Patient information not found for ID ' || v_pat_id);
END;
/
SQL> @q4
Enter value for enter_patient_id: 102
old 2: v_pat_id number:= &Enter_patient_id;
new 2: v_pat_id number:= 102;
Patient ID: 102
Patient Name: Parth
Date of Birth: 28-APR-00
Address: mumbai
Phone Number: 4324685786
Doctor Name: dr shah
PL/SQL procedure successfully completed.
==========================================
Comments
Post a Comment