7 + Interview Questions in PL/SQL Programs in PL SQL Interview Questions Page 1 InterviewSolution

1.	Write PL/SQL program to find the sum of digits of a number.
Answer» DECLARE--Declare variables num, sum_of_digits and REMAINDER of datatype Integer num INTEGER; sum_of_digits INTEGER; remainder INTEGER; BEGINnum := 123456; sum_of_digits := 0;-- Find the sum of digits until original number DOESNT become null WHILE num <> 0 LOOP remainder := MOD(num, 10); sum_of_digits := sum_of_digits + remainder; num := TRUNC(num / 10); END LOOP; dbms_output.PUT_LINE('Sum of digits is '\|\| sum_of_digits); END; Input: 9874 Output: 28

Discussion

2.	Write PL/SQL program to convert each digit of a given number into its corresponding word format.
Answer» DECLARE-- declare necessary VARIABLES -- num represents the GIVEN number -- number_to_word represents the word format of the number -- str, len and digit are the intermediate variables used for program executionnum INTEGER; number_to_word VARCHAR2(100); digit_str VARCHAR2(100); len INTEGER; digit INTEGER; BEGIN num := 123456; len := LENGTH(num); dbms_output.PUT_LINE('Input: ' \|\|num); -- Iterate through the number one by one FOR i IN 1..len LOOP digit := SUBSTR(num, i, 1); -- Using DECODE, get the str REPRESENTATION of the digit SELECT Decode(digit, 0, 'Zero ', 1, 'One ', 2, 'Two ', 3, 'Three ', 4, 'Four ', 5, 'Five ', 6, 'Six ', 7, 'Seven ', 8, 'Eight ', 9, 'Nine ') INTO digit_str FROM dual; -- Append the str representation of digit to final result. number_to_word := number_to_word \|\| digit_str; END LOOP; dbms_output.PUT_LINE('Output: ' \|\|number_to_word); END; Input: 12345 Output: One Two Three Four Five

2.

Write PL/SQL program to convert each digit of a given number into its corresponding word format.

Answer»

DECLARE-- declare necessary VARIABLES -- num represents the GIVEN number -- number_to_word represents the word format of the number -- str, len and digit are the intermediate variables used for program executionnum INTEGER; number_to_word VARCHAR2(100); digit_str VARCHAR2(100); len INTEGER; digit INTEGER; BEGIN num := 123456; len := LENGTH(num); dbms_output.PUT_LINE('Input: ' ||num); -- Iterate through the number one by one FOR i IN 1..len LOOP digit := SUBSTR(num, i, 1); -- Using DECODE, get the str REPRESENTATION of the digit SELECT Decode(digit, 0, 'Zero ', 1, 'One ', 2, 'Two ', 3, 'Three ', 4, 'Four ', 5, 'Five ', 6, 'Six ', 7, 'Seven ', 8, 'Eight ', 9, 'Nine ') INTO digit_str FROM dual; -- Append the str representation of digit to final result. number_to_word := number_to_word || digit_str; END LOOP; dbms_output.PUT_LINE('Output: ' ||number_to_word); END;

Input: 12345
Output: One Two Three Four Five

Discussion

3.	Write a PL/SQL code to find whether a given string is palindrome or not.
Answer» DECLARE-- Declared variables string, letter, reverse_string where string is the original string. string VARCHAR2(10) := 'abccba'; letter VARCHAR2(20); reverse_string VARCHAR2(10); BEGINFOR i IN REVERSE 1..LENGTH(string) LOOP letter := SUBSTR(string, i, 1); -- concatenate letter to reverse_string variable reverse_string := reverse_string \|\|''\|\|letter; END LOOP;IF reverse_string = string THEN dbms_output.Put_line(reverse_string\|\|''\|\|' is palindrome'); ELSE dbms_output.Put_line(reverse_string \|\|'' \|\|' is not palindrome'); END IF; END;

Discussion

4.

Write PL/SQL code block to increment the employee’s salary by 1000 whose employee_id is 102 from the given table below.

Answer»

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL_ID	PHONE_NUMBER	JOIN_DATE	JOB_ID	SALARY
100	ABC	DEF	abef	9876543210	2020-06-06	AD_PRES	24000.00
101	GHI	JKL	ghkl	9876543211	2021-02-08	AD_VP	17000.00
102	MNO	PQR	mnqr	9876543212	2016-05-14	AD_VP	17000.00
103	STU	VWX	stwx	9876543213	2019-06-24	IT_PROG	9000.00

DECLARE employee_salary NUMBER(8,2); PROCEDURE update_salary ( EMP NUMBER, salary IN OUT NUMBER ) IS BEGIN salary := salary + 1000; END;BEGIN SELECT salary INTO employee_salary FROM ib_employee WHERE employee_id = 102; DBMS_OUTPUT.PUT_LINE ('Before update_salary procedure, salary is: ' || employee_salary); update_salary (100, employee_salary); DBMS_OUTPUT.PUT_LINE ('After update_salary procedure, salary is: ' || employee_salary);END;/

RESULT:

Before update_salary procedure, salary is: 17000After update_salary procedure, salary is: 18000

Discussion

5.	Write a PL/SQL code to count the number of Sundays between the two inputted dates.
Answer» --declare 2 DATES of type DateDECLARE start_date Date; end_date Date; sundays_count Number:=0; BEGIN -- input 2 dates start_date:='&input_start_date'; end_date:='&input_end_date'; /* Returns the date of the first day after the mentioned date and matching the day specified in second PARAMETER. / start_date:=NEXT_DAY(start_date-1, 'SUNDAY'); --check the condition of dates by USING while loop. while(start_date<=end_date) LOOP sundays_count:=sundays_count+1; start_date:=start_date+7; END LOOP; -- print the count of sundays dbms_output.put_line('Total number of Sundays between the TWO dates:'\|\|sundays_count); END; / Input:* start_date = ‘01-SEP-19’ end_date = ‘29-SEP-19’ Output: Total number of Sundays between the two dates: 5

5.

Write a PL/SQL code to count the number of Sundays between the two inputted dates.

Answer»

--declare 2 DATES of type DateDECLARE start_date Date; end_date Date; sundays_count Number:=0; BEGIN -- input 2 dates start_date:='&input_start_date'; end_date:='&input_end_date'; /* Returns the date of the first day after the mentioned date and matching the day specified in second PARAMETER. */ start_date:=NEXT_DAY(start_date-1, 'SUNDAY'); --check the condition of dates by USING while loop. while(start_date<=end_date) LOOP sundays_count:=sundays_count+1; start_date:=start_date+7; END LOOP; -- print the count of sundays dbms_output.put_line('Total number of Sundays between the TWO dates:'||sundays_count); END; /

Input:
start_date = ‘01-SEP-19’
end_date = ‘29-SEP-19’

Output:
Total number of Sundays between the two dates: 5

Discussion

6.	Write a PL/SQL procedure for selecting some records from the database using some parameters as filters.
Answer» Consider that we are FETCHING details of employees from ib_employee table where SALARY is a parameter for filter. CREATE PROCEDURE get_employee_details @salary nvarchar(30)ASBEGIN SELECT * FROM ib_employee WHERE salary = @salary;END;

Discussion

7.	Write a PL/SQL program using WHILE loop for calculating the average of the numbers entered by user. Stop the entry of numbers whenever the user enters the number 0.
Answer» DECLARE n NUMBER; average NUMBER :=0 ; sum NUMBER :=0 ; count NUMBER :=0 ;BEGIN -- TAKE INPUT from user n := &input_number; WHILE(n&LT;>0) LOOP -- Increment count to find total elements count := count+1; -- Sum of elements ENTERED sum := sum+n; -- Take input from user n := &input_number; END LOOP; -- Average calculation average := sum/count; DBMS_OUTPUT.PUT_LINE(‘Average of entered NUMBERS is ’\|\|average);END;

Discussion

Explore topic-wise InterviewSolutions in .

Write PL/SQL program to find the sum of digits of a number.

Write PL/SQL program to convert each digit of a given number into its corresponding word format.

Write a PL/SQL code to find whether a given string is palindrome or not.

Write PL/SQL code block to increment the employee’s salary by 1000 whose employee_id is 102 from the given table below.

Write a PL/SQL code to count the number of Sundays between the two inputted dates.

Write a PL/SQL procedure for selecting some records from the database using some parameters as filters.

Write a PL/SQL program using WHILE loop for calculating the average of the numbers entered by user. Stop the entry of numbers whenever the user enters the number 0.