10 Write a PL/SQL code to Print the following by using any Loop
0 1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8 9
2 3 4 5 6 7 8 9
3 4 5 6 7 8 9
4 5 6 7 8 9
5 6 7 8 9
6 7 8 9
7 8 9
8 9
9
3
11 Given the following code fragment.
If (a=0) THEN
DBMS_OUTPUT.PUT_LINE('Zero');
end if;
if a=1 THEN
DBMS_OUTPUT.PUT_LINE('One');
end if;
if a=2 THEN
DBMS_OUTPUT.PUT_LINE('Two');
end if;
if a=3 THEN
DBMS_OUTPUT.PUT_LINE('Three');
end if;
Write an alternative code using IF that saves the number of comparisons
12 (i) An insurance company uses the following rules to calculate premium:
(ii) If a person’s health is excellent and the person is between 26 and 35 years of age and lives in a city and is male then premium is Rs.2 per thousand and his policy may not be written for more than Rs. 2 Lakhs.
(iii) If a person satisfies all the above conditions except that the sex is female then the premium is Rs. 1.10 per thousand and her policy may not be written for more than Rs.1.8 Lakh.
(iv) If a person’s health is poor and age is between 25 and 35 and the person lives in a village and is male then the premium is Rs.9 per thousand and his policy may not be written for more than Rs.2500
(v) In all other cases, the person is not insured. Write a program to give the eligibility of a person to be insured, his premium rate and maximum amount of insurance.
13 A company manufactures three products – engine, pumps and fans. It gives a discount of 12 % on orders for engines if the order is for Rs. 7000 or more. The same discount of 12% is given on pumps orders of value Rs.4000 or more and on fan orders for Rs.2000 or more. 3
14 A bank accepts fixed deposits for one year or more and the policy it adopts on interest is as follows:
(i) If a deposit is less than Rs.2000 and for 2 or more years, the interest rate is 5% compounded annually.
(ii) If a deposit is Rs.2000 or more but less than 6000 and for 2 or more years, the interest rate is 7% compounded annually.
(iii) If a deposit is more than Rs.6000 and is for 1 year or more, the interest is 8% compounded annually.
(iv) On all deposits for 5 years or more, interest is 10 % compounded annually.
(v) On all other deposits not covered by above conditions, the interest is 3% compounded annually.
Give the amount deposited and the number of years, write a program to calculate the money in the customer’s account at the end of the specified time.
15 Write a PL/SQL code that lets you display employee numbers and names of employees with employee code more than 7800. You must not define a cursor for it in declare section. 2
16 Write PL/SQL to replace Empname by adding Mr it gender in M otherwise add Ms to the existing Empname.
17 Write a PL/SQL script to calculate commission for a salesman whose no is asked from emp 3 table. Get sales made from the user and calculate the commission as per:
Sales made commission
<10000 500 + 10% of salary
10000 – 20000 1000 + 15% of salary
>20000 1500 + 20% of salary
Write the commission back into the table.
18 Find out the output of following code fragment. Also find out error(s) if any: 2
DECLARE
X NUMBER;
Y char(10);
BEGIN
X := 10;
Y := ‘ABC’;
DECLARE
Z NUMBER;
BEGIN
Z := X+10;
DBMS_OUTPUT.PUT_LINE(‘X :’||X);
DBMS_OUTPUT.PUT_LINE(‘Y :’||Y);
DBMS_OUTPUT.PUT_LINE(‘Z :’||Z);
END;
DBMS_OUTPUT.PUT_LINE(‘X :’||X);
DBMS_OUTPUT.PUT_LINE(‘Y :’||Y);
DBMS_OUTPUT.PUT_LINE(‘Z :’||Z);
END;
19 Find out the output of following code fragment. Also find out error(s) if any: 2
BEGIN
FOR I IN 0..9
LOOP
FOR J IN 9..I LOOP
DBMS_OUTPUT.PUT_LINE(‘J||’ ‘);
END LOOP;
DBMS_OUTPUT.NEW_LINE();
END LOOP;
END;
20 Write a PL/SQL script that incorporates exception handling to handle the following errors.When department no. and commission is obtained for an employee whose empno is given by the user at run time.
The errors to be handled are :
a. Department no. having NULL value.
b. Commission having NULL value.
c. No such employee found.
21 Write a PL/SQL script to obtain the name of a department whose no is asked. Display the 3 department no and department name along from emp and dept tables
22 WAP to accept the age of n employees in a loop and count the number of persons n the following age groups: 3
(i) 26-35 (ii) 36-45 (iii) 45-55
23 Write a PL/SQL script to obtain the current date from the user and display the Month Name, How many days are present in that month and how many days are left in that month. 4
24 Write a PL/SQl Script which accept Rollno, Name, Marks in three subject then display the 4 total mark, percentage, grade and result. Result is pass/ Fail/Supplementary. If student score more than equal to 40 in each subject then declare as Pass but if he score less than 40 in one subject then declare supplementary otherwise declare him fail
25 Write a PL/SQL Code to read a number, then reverse the no and check whether the reversed 2 no and original number are same or not. (Palindrome)
26 Write a PL/SQL Code to print first n Armstrong numbers. Here n is accepted from the user 3
Cursor
27 Write a Pl/SQL Script that uses cursor to calculate bonus for employees as 5% of salary + 2.5% of comm. The calculated bonus along with the employee no, is stored in a table namely, bonuses
28 Write a PL/SQL script to display the employee name, job, and department name. The search 4 condition should allow for case insensitive name searches.
29 Write a PL/SQL block that uses an explicit cursor named cur_student to retrieve the first and last names for all the records in the students table, and then displays each first and last name using DBMS_OUTPUT command. Use a LOOP. Exit when loop ends to process the cursor.
30 Write PL/SQL code that displays the employee number, name and jobs of all those 3 employees whose names start with a particular character or group of characters. Implement the code in such a way that if no information is passed then the information regarding all the employees is displayed. Sort the data by Names.
31 Generate a report in PL/SQL that displays department wise information of all employees.The department names should appear in sorted order; also the information of the employees should be arranged in ascending order of their names. The report should display the data in following format
======================================
Department Name Department No Location At
======================================
Employee No Employee Name Designation
7839 King President
32 Write a PL/SQL script to increment the salaries of employees as per following specifications: 4
For Salesmen if salary + Comm > 2500 then 10% of salary as increment
Otherwise 12% of salary as increment
For Analysts 20% increment
For Clerks 12% increment
For Managers 25% increment
33 A table student is present in the database. The attributes of the table are Rno, name, Mark1,Mark2,Mark3, Totmark. Write a PL/SQL to do the following Update Totmark as Mark1 + Mark2 + Mark3
Also insert details into table studentresult which should contain rno,name,result where result is Pass if totmark is more than 32 otherwise result “Fail”.
34 A table videoLib has the following colums : cid, cname, actor, actress, language, issuedate, returndate. The datatype of issuedate and returndate is date. Write PL/SQL blocks to answer the following questions:
Display the no of days elapsed between issuedate and returndate for all issue cassettes. If one day rent is 20 Rs/- calculate the amount to paid by the member and display the amount.
35 Write PL/SQL to replace Empname by adding Mr if gender is M otherwise add Ms to the existing Empname
36 Write a PL/SQL script that allows you to pass a department no. Then it computes the total 4 wages paid to employees in that department. It also determine how many employees have salaries higher than 2000 and/or commission larger then their salaries.
37 Find error(s) if any :
DECLARE
CURSOR c1 (Test Numeric) IS SELECT * FROM Emp WHERE Sal > Test ;
Test VARCHAR2(10) ;
Begin
Test := &TEST;
OPEN(c1);
END;
38 Find error(s) if any 2
DECLARE
Test NUMERIC(10); Test VARCHAR2(10); BEGIN
Test := 10; END;
39 Find error(s) if any : 2
DECLARE
Cursor c1 (Test Numeric) IS SELECT * FROM Emp WHERE Sal > Test ;
Test VARCHAR2(10) ;
Begin
Test := &TEST; OPEN(c1);
END;
40 Write Pl/SQL script to acquire name,salary of employees who earn in between Rs 3500.00 to Rs 5500.00. Give them an increment of 0.7% and display all the records.
41 User a cursor to retrieve the department number and department name from the DEPTM 2
Table. Pass the department number to another cursor to retrieve from the employee table details of employee name, job, hire date and salary of all the employee who work in that department.
42 Create a cursor to display the data from emp table in the following format 4
==========================================
Report
==========================================
S.No Name Salary
1
2
3
43 Using cursor display the details of all those employees from EMP table whose sum of salary 2 and commission is more then 2500 using Cursor For Loop.
44 Write a cursor to display the empno, ename, sal, deptno, dname and display the details of persons getting salary more than N RS, where N is passed as parameter (e.g 2000)
45 Write a PL/SQL script that uses cursor for loop to calculate bonus for employee as 5% 3 of salary + 2.5% of commission. The calculated bonus should be stored in table bonus.
46 EMP(Ename,Sal,Comm) table give the output produced by the following PL/SQL code on execution
DECLARE
Vename emp.ename%type; Vsal emp.sal%type:=1500;
Vcounter number(2):=1;
Begin
Loop
Select ename into vename from emp where Sal<vsal;
Dbms_output.put_line(vename);
Vsal:=1800;
Vcounter:= vcounter +1;
Exit when Vcounter > 2;
End Loop;
End;
Procedure and Functions
47 Create a Stored procedure that displays the first name and last name of the student whose 2 student id is passed as parameter and display the message “Student Does not exist” when student id is not present. Invoke the above procedure
48 Answer the following questions based on the following Employee table 4
Name of Column Type
ID NUMBER (4)
First_Name VARCHAR2 (30)
Last_Name VARCHAR2 (30)
EMail_ID VARCHAR2 (10)
Salary NUMBER (9,2)
Write a PL/SQL procedure EDSAL to find out whether the salary of an Employee with ID = 1234 is less than 180 or not. If it is less then 5000, modify the Salary of employee by increasing it by 15%.
49 Write a PL/SQL Function CheckDiv that takes two numbers as arguments and returns the value 1 if the first argument passed to it is divisible by the second argument, else will return the value 0 if the second no is zero than raise the user define error ‘No. CAN NOT BE DIVIDED BY ZERO’.
50 Write a PL/SQL Procedure that takes a parameter , and return the reversed digits of the 4 number through Parameter
51 Create a stored procedure named raise_pay that will increase an employee’s salary. The parameters should be the employee’s id number and the percent increase to his salary.
52 Write a PL/SQL procedure that calculates and displays the volume of a cuboid. The 4 procedure takes three parameters for length, width and height of the cuboid respectively. The last two parameters are optional having a default value of –1. If the last two parameters are not passed then the volume of a cube having sides equal to the first parameter is to be calculated.
53 Write a PL/SQL procedure to return a value for finding the sum of first 10 natural number 4 using OUT parameter.
54 Write a Pl/SQL Stored Procedure that takes “maxrows” and “maxcols” as argument to 4 generate a multiplication table using <<labeled>> nested simple loop.
The output will be like this:
1 2 3 4 5
2 4 6 8 10
3 6 9 12 15
4 8 12 16 20
5 10 15 20 25
55 Write a PL/SQL PROCEDURE which accepts a no as parameter and prints all the prime number upto that no.
56 Write a PL/SQL PROCEDURE which accepts two numbers and returns the sum, product, difference by using parameters only?
57 Write a PL/SQL function that takes two numbers as argument and returns the sum of all the numbers between them. If both the numbers are zero then it raises a user define exception
‘Second Parameter cannot be Zero’
58 Write a PL /SQL Procedure which reads two parameters and shows their division and remainder without using the Mod and / Operator?
59 Write PL/SQL Procedure which reads two numbers as parameters and returns their product without using * operator?
60 Write a PL/SQL Procedure which accepts two parameters of number type and interchanges their value without using any other variable?
61 Write a PL/SQL Procedure that takes employee code of an employee as a parameter. In the 4 table “Employee” if the commission field is empty then set it to 100. ( Fields: employee code empno, commission, Comm)
62 Write a PL/SQL procedure called MULTTABLE that takes two numbers as parameters and 2 displays the multiplication table of the second parameter to the first parameter.
63 Write a user defined Power function that takes two numbers as parameters and returns the value of the first no raised to the power of the second. If second parameter is missing then assume it as 1. (Without using inbuilt function).
64 Create a procedure that adds the details (empno, ename, job, sal) of newest employee from 4 table emp into NewPer having structure as (eno, first name, lastname, designation, salary). The lastname of the person is to be passed to the procedure as read only value
65 Create a stored procedure that displays the no of employees from the table emp who joined 4 after a given date or joined within a specified period whose start and end dates are provided. The count of employee should also be made available to the caller program.
66 Create a stored procedure named Increase_Pay that will increase an employee’s pay amount. The parameter should be the employee’s id number and the percentage increase to his salary. If the employee id does not exist then exception “This Employee does not belong to this company”
67 Write a procedure that adds first name and last name as parameters in person table along with a unique id. The id should be calculated as existing last id (Generally maximum id + 1)
68 Write a PL/SQL Procedure to select employee from emp who get a salary of Rs 14000. Give them an increment of 7%. But if more than one employees, it should not display an error message. If no employee is selected then also display a message “ No Such Employee Exist”
69 Write a stored procedure that gives the total_no of orders as per the dates passed to it. Two 4 dates namely start date and end date are to be passed to it
• Default values of start date and end date should be 01/01/2005 and NULL
• If both the dates are passed, the procedure should give total number of orders placed during the period between start date and end date.
If only start date is passed, then it should give the count of orders placed after that date.
70 Create a procedure and function for the following: A Bank allows withdrawal in an account if 4 only the balance after withdrawing amount remains minimum 1000/- Write a withdraw procedure that performs the withdrawal. This procedure first invokes a function Balancecheck by passing the amount to be withdrawn and the account no. Write function Balancecheck with following functionality: the function Balance check scans through the accounts table and determines whether this withdrawal is possible or not. That is whether after withdrawing this amount, there would be minimum Rs 1000/- or not. If the withdrawal is possible, the function returns True otherwise False. Depending upon the return value of the Balancecheck function, the procedure withdraw either performs the withdraw or raise an exception “Transaction not allowed!! Illegal Withdrawal!”
71 Give a SQL statement to define the table GradePoints with following structure 4
Column Name Data Type Size Constraint
Grade CHAR 1 Primary Key
MinMarks NUMBER 5,2 >0
MaxMarks NUMBER 5,2 >0,<100,>MinMarks
Write a PL/SQL procedure that accepts one parameter of each column of the GradePoints table and adds a record to the table if the data for MinMarks and MaxMarks is valid, otherwise the procedure should display appropriate message
72 Write a PL/SQL procedure to find out whether the salary of an employee whose ID pass as parameter is less than 7000 or not. If it is less than 7000, modify the salary of employee by increasing it by 10%.
73 Write a PL/SQL function ISPRIME to return value True if the number passed to it is Prime else return False
74 Write a Procedure in PL/SQL which returns the sum of all even numbers less than given number N which is passed as Read Only Parameter
75 Write a PL/SQL procedure which reads the date of Birth of any candidate as parameter and shows the age of person up to current date how many years, Months and Days old the candidate is.
TRIGGER
76 Find the errors from the following PL/SQL code and rewrite the corrected code underline the correction made
CREATE ASW REPLACE TRIGGER DEPT_UP
AFTER UPDATE ON DEPT FOR EVERY ROW
DECLARE
Vno NUMBER(3);
BEGIN
SELECT COUNT(*) INTO VNO FROM EMP WHERE DEPT=’101;
IF Vno > 5
Raise_application_error(-20001,’Cannot exceed 5’);
End;
77 Why does the following trigger fail when it is executed? Write correct code.
CREATE OR REPLACE TRIGGER inempsum
AFTER INSERT
On Emp
Begin
INERT INTO emp_Sum(empno,period,sal) values (:new.empno,SYSDATE,:new.sal);
End;
78 Examine the following trigger:
CREATE OR REPLACE TRIGGER upd_emp_comm
FOR EACH ROW
Begin
<<Trigger Body>>
End;
Which of the following statements must you add to the trigger definition to make sure this
trigger executes only updating the comm. column of the emp table?
a) AFTER UPDATE(Comm) On emp
b) AFTER UPDATE on emp
c) AFTER UPDATE of comm. On EMP
d) AFTER comm UPDATE on EMP
79 If we have row level triggers and statement level triggers and before and after triggers then in which order triggers are fired if multiple triggers exist for the same table? 2
80 An HR System has an employee table that holds a row for each employee within the company. Each record in the table has a manager field, (mgr), that holds the id for the employees manager. Write a trigger so that when a manager record is deleted, the mgr field of that manager’s employees is set to NULL. 4
81 Write a trigger that allows changes to employee table only during the business hours(i.e. from 8 a.m. to 5.00 p.m.) from Monday to Saturday. There is no restriction on viewing data from the table 4
82 Create a trigger to fill the BillNo field of Bill table with a value generated from the Bill_sequence every time insertion or updation takes place in the Bill table 4
83 Create a trigger that displays the no of employees before every delete in emp table. 4
84 Change the course cost to a default value (3000) if the course cost entered by user exceeds 5000 in course table. 4
85 Create an instead of trigger for Emp_Info view for inserting row in it. The Emp_Info view has been created as
Create View Emp_Info AS
Select e.ename,e.empno,d.dept_type,d.deptno,p.level,p.projno
From emp e, dept d, project p
Where e.empno=d.mgr_no and d.deptno=p.resp_dept;
86 Create a trigger for emp table which makes the entry in ename column in the upper case only 4
87 Write a trigger TOTAL_SALARY to maintain a derived column TOTSAL that stores total salary of all members in a department 4
88 Create a trigger for updation of Column SAL in EMP table, which ensures that SAL cannot be reduced. 4
89 Create a trigger to change the commission amount to 2500 every time the commission amount entered by user exceeds 2500. An appropriate message should also be displayed 4
90 Write PL/SQL code to create two statement level triggers before_delete and after_delete before and after delete respectively on the table of your choice which display the message
‘Ready for Deletion’ and ‘Record Deleted’ respectively 4
91 Create a trigger that prints the change in salary every time salary of an employee is changed 4
92 Give the SQL statement required to create the following table :
Table : Transactions
Column Name Data Type Size Constraint
Invoicenumber Number 10 Primary Key
Itemcode Number 10 Referenced from item Table
Transactiondate Date -- System date
Transactionmode Varchar2 10 Allowed Values : SALE, PURCHASE
Transactionunits Number 5 >0
Write a PL/SQL trigger that give a message :
<n> units of <itemcode> sold/purchased after each sale or purchase is recorded in the above table Transactions.
93 Create a trigger to implement referential integrity policy – “On delete set Null” in the customer_order tables. That is when a customer record is deleted set Null for deleted customer no( cid ) in orders table
94 Consider the tables
Courses( Course_code, Name, Startdate, Duration, Fee, Total_Seats, Available_Seats)
Enrolments ( Enrolno, Course, Enr_Date)
Create a single trigger that is fired each time when
a) An Enrolment is requested
b) Cancellation of an enrolment is requested
c) A Migration is requested
An Enrolment or migration can be possible by within a month of start date of the course. The trigger should also check for the availability of seats in the course to which migration/admission is sought. If the admission/migration is possible then
1. In case of fresh enrolment reduce the no of available seats in the requested course by one
2. In case of migration increase the no of available seats in the old course and reduce the no of available seats in the new course by one. 4
95 Consider a view that reads department number, name, location, employee no, name, job, manager id, hire date and salary from the Dept and Emp tables. Create a trigger that is fired each time user attempts to carry out an insert operation on the view. The trigger should add records to the underlying tables if the records are not there already.
