CBSE Class 12 Information Practices HOTs Oracle SQL PL

Download HOTS questions and answers for Class 12 Informatics Practices. Read CBSE Class 12 Information Practices HOTs Oracle SQL PL below and download in pdf. High Order Thinking Skills questions come in exams for Informatics Practices in Standard 12 and if prepared properly can help you to score more marks. You can refer to more chapter wise Class 12 Informatics Practices HOTS Questions with solutions and also get latest topic wise important study material as per NCERT book for Class 12 Informatics Practices and all other subjects for free on Studiestoday designed as per latest CBSE, NCERT and KVS syllabus and pattern for Grade 12

164 Write the value of num at every stage of the following program:
Dim num As Integer

Private Sub Command1_Click()
Static num As Integer
num = num + 10
Print num
End Sub
Private Sub Command2_Click()
num = 20
Print num
Call Command1_Click
End Sub
Private Sub Command3_Click()
Call Command1_Click
num = num + 10
Print num
End Sub

165 Write a VB program to generate Pythagorean Triplet from a set of two consecutive numbers m and n where m>n. 

Oracle SQL Revision

166 Re-write the following using a join query:

SELECT ename FROM emp WHERE deptno =

(SELECT deptno FROM emp WHERE ename = 'JONES');2

167 Re-write the following using NOT EXISTS clause:

SELECT dept.dname FROM dept

WHERE dept.deptno NOT IN


SELECT deptno

FROM emp



168 What will be the output of the following query on EMP table:


FROM emp

WHERE (sal, mgr) =

(SELECT sal, mgr FROM emp


WHERE sal > (SELECT MIN(sal) FROM emp)));2

169 What will be the output of the following query:


WHERE empno IN (SELECT empno FROM emp


WHERE sal < (SELECT MAX(sal) FROM emp)));2

170 What will be the output of the following query: 2
(SELECT MAX(sal) FROM emp) AS highest,
(SELECT MIN(sal) FROM emp) AS least,
(SELECT COUNT(*) FROM emp) AS employees,
(SELECT SUM(sal) FROM emp) AS total
FROM dual; 
171 What will be the output of the following query: 2
WHERE sal >(SELECT min(sal) FROM Emp
                          WHERE deptno = (SELECT deptno 
                                                           FROM dept
                                                           WHERE loc = 'NEW YORK')); 
Perform the following queries from EMP and DEPT tables.
172 To find the departments that have employees with a salary higher than the average employee salary 2
173 To find the details of all the employees drawing salary between minimum and Average salary for all the employees. 2 
174 To display the employee name and Annual Salary (sal*12) for all the employees who are drawing annual salary more than 30000. 2
175 To display the deptno and average salary for each department in the emp table with average salary less than the maximum salary for each department. 2
1 Write a PL/SQL code to print the largest among four numbers 2
2 Write a PL/SQL Code to create an equivalent of 4 function calculator which performs the 2 addition, subtraction, multiplication and division on the two number entered by the user and display the result. Operator is also entered by the user
3 Write a PL/SQL coding to find if the year is entered is leap or not Note 1700, 1800, 1900 These year are divisible by 4. But these are not Leap Years  2
4 Write a PL/SQL coding to print the sum of the series 1,2,4,7,11,16 up to n terms 3
5 Write a PL/SQL Script to check whether the give No is single digit, two digit, three digit, 3 four digit, five digit or more e.g.  (7 is single digit, 48 is two digit etc)
6 Write a PL/SQL code which  the accept the amount and show there are how many notes of 1000 Rs, 500 Rs, 100 Rs, 50 Rs, 20 Rs, 10 Rs, 5 Rs, 2 Rs, 1 Rs (e.g amount is 2315 there are  4
Amount  = No of  Notes
1000 Rs =  2
500 Rs   =  0
100 Rs   =  3
50 Rs =  0
20 Rs =  0
10 Rs =  1
5 Rs =  0
2 Rs =  0
1 Rs =   0 )
7 Write a PL/SQL code to Print the following by using any Loop 3
2   3
4   5 6
7   8 9  10
11 12 13 14 15
8 Write a PL/SQL code to Print the following by using any Loop 3
1 2 1
1 2 3 2 1
1 2 3 4 3 2 1
1 2 3 4 5 4 3 2 1
9 Write a PL/SQL code to Print the following by using any Loop
0 1
0 1 2
01 2 3
0 1 2 3 4
0 1 2 3 4 5
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
11 Given the following code fragment.
If (a=0) THEN
end if;
if a=1 THEN
end if;
if a=2 THEN
end if;
if a=3 THEN
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
        X NUMBER;
        Y char(10);
        X := 10;
        Y := ‘ABC’;
        Z NUMBER; 
       Z := X+10;
       DBMS_OUTPUT.PUT_LINE(‘X :’||X);
       DBMS_OUTPUT.PUT_LINE(‘Y :’||Y);
       DBMS_OUTPUT.PUT_LINE(‘Z :’||Z);
       DBMS_OUTPUT.PUT_LINE(‘X :’||X);
       DBMS_OUTPUT.PUT_LINE(‘Y :’||Y);
       DBMS_OUTPUT.PUT_LINE(‘Z :’||Z);
19 Find out the output of following code fragment. Also find out error(s) if any: 2
         FOR I IN 0..9
               FOR J IN 9..I LOOP
               DBMS_OUTPUT.PUT_LINE(‘J||’ ‘);
           END LOOP;
      END LOOP;
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 
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 : 
CURSOR c1 (Test Numeric) IS SELECT * FROM Emp WHERE Sal > Test ;
Test VARCHAR2(10) ;
Test := &TEST;
38 Find error(s) if any 2
Test NUMERIC(10); Test VARCHAR2(10); BEGIN
Test := 10; END; 
39 Find error(s) if any : 2
Cursor c1 (Test Numeric) IS SELECT * FROM Emp WHERE Sal > Test ;
     Test VARCHAR2(10) ;
Test := &TEST; OPEN(c1);
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
S.No                        Name                       Salary
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
Vename emp.ename%type; Vsal emp.sal%type:=1500;
Vcounter number(2):=1;
   Select ename into vename from emp where Sal<vsal;
 Vcounter:= vcounter +1;
Exit when Vcounter > 2;
End Loop;
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.
76 Find the errors from the following PL/SQL code and rewrite the corrected code underline the correction made
       Vno NUMBER(3);
IF Vno > 5
       Raise_application_error(-20001,’Cannot exceed 5’);
77 Why does the following trigger fail when it is executed? Write correct code.
             CREATE OR REPLACE TRIGGER inempsum
             AFTER INSERT
             On Emp
INERT INTO emp_Sum(empno,period,sal) values (:new.empno,SYSDATE,:new.sal);
78 Examine the following trigger:
<<Trigger Body>>
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.

More Study Material