pl sql program for 'palindrome'

Write a program in PL/SQL to check whether a number is a palindrome or not.

In this article, we will see the program for Palindrome in Pl/Sql.

A palindrome is a word, phrase, number, or other sequence of characters which reads the same backward or forward. Allowances may be made for adjustments to capital letters, punctuation, and word dividers.

Let's see the program for this.

sql> set serveroutput on
sql>declare
2  g varchar2(20);
3  r varchar2(20);
4  i number(4);
5  begin
6  g:='&g';
7  for i in reverse 1.. length(g) loop
8  r:=r || substr(g,i,1);      
9  end loop;
10  dbms_output.put_line('reverse string is ' || r);
11  if r=g then
12  dbms_output.put_line('String is Palindrome');
13  else
14  dbms_output.put_line('String is not Palindrome');
15  end if;      
16  end;
17  /


Thanks.

Reverse Of a Number

Write a program in PL/SQL to print the reverse of a number.

According to the problem if a number  is 677 then it should print 776.

Program Code:

SQL> set serveroutput on
SQL>Declare
2 num number:=&n;
3 rev number:=0;
4 Begin
5 while(num>0)
6 Loop
7 rev:=rev*10+mod(num,10);
8 num:=num/10;
9 End loop;
10 Dbms_Output.Put_Line('Reverse of number is '|| rev);
11 End;
12 /



Thanks.

PL/SQL Fibonacci Sequence

Write a PL/SQL code to get the Fibonacci Sequence.

First, I will explain what is Fibonacci Sequence and how to get this series.
So, Fibonacci Sequence is a series of numbers 0,1,1,2,3,5,8,13,21.............

In Fibonacci Sequence, first and second elements are 0 and 1 and to get the next elements we will add the previous elements and it will generate the next element.
So, first element=0
      second element =1
      third element=sum of last 2 elements (first element + second element)
                           =0+1
                           =1
      fourth element=second element + third element
                             =1+1
                             =2
      fifth element=third element + fourth element
                           =1+2
                           =3

So in this way we can generate a Fibonacci Sequence.

Program Code:

SQL> declare
  2  a number(5);
  3  b number(5);
  4  c number(5);
  5  n number(5);
  6  i number(5);
  7  begin
  8  n:=6;
  9  a:=0;
 10  b:=1;
 11  for i in 1..n
 12  loop
 13  c:=a+b;
 14  a:=b;
 15  b:=c;
 16   dbms_output.put_line(c);
 17  end loop;
 18  end;

 19  /

Thanks.

PL/SQL factorial program

Write a program in PL/SQL to print the factorial of a number.

In this post I will explain how to get the factorial of any given number. For that first you need to know what is the procedure to find the factorial of a number.

To get the factorial of any given number we have to multiply that number to every number less than that number till 1.

Suppose we have to find the factorial of 5. Then,

factorial(5)=5*4*3*2*1

                  =120

Program Code:

SQL> set serveroutput on

SQL> declare

  2  i number(5);

  3  j number (5);

  4  f number(10);

  5  begin

  6  i:=&i;

  7  f:=1;

  8  for j in 1..i

  9  loop

 10  f:=f*j;

 11  end loop;

 12  dbms_output.put_line(f);

 13  end;

 14  /

Thanks

Difference function

Given a City table, whose fields are described as

+-------------+----------+
| Field | Type |
+-------------+----------+
| ID | int(11) |
| Name | char(35) |
| CountryCode | char(3) |
| District | char(20) |
| Population | int(11) |
+-------------+----------+

print the difference between the maximum and minimum city populations.

>>select (max(population)-min(population)) from city;

Thanks.

Average function

Given a City table, whose fields are described as

 +-------------+----------
| Field | Type |
+-------------+----------+
| ID | int(11) |
| Name | char(35) |
| CountryCode | char(3) |
| District | char(20) |
| Population | int(11) |
+-------------+----------+

print the average population of all cities, rounded down to the nearest integer.

>>select floor(avg(population)) from city; 

Thanks.

'Shortest' and 'Longest' cities name

Given a table STATION that holds data for five fields namely ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.

+-------------+------------+

| Field       |   Type     |

+-------------+------------+

| ID          | INTEGER    |

| CITY        | VARCHAR(21)|

| STATE       | VARCHAR(2) |

| LAT_N       | NUMERIC    |

| LONG_W      | NUMERIC    |

+-------------+------------+

 Write a query to print shortest and longest cities name. If there are more than one cities print lexicographical smallest name.

>>(select CITY, char_length(CITY) as len_city from STATION order by len_city limit 1) union all (select CITY, char_length(CITY) as len_city from STATION order by len_city desc limit 1) order by len_city;

Thanks.

Selecting 'Consonants '

Given a table STATION that holds data for five fields namely ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.

+-------------+------------+

| Field       |   Type     |

+-------------+------------+

| ID          | INTEGER    |

| CITY        | VARCHAR(21)|

| STATE       | VARCHAR(2) |

| LAT_N       | NUMERIC    |

| LONG_W      | NUMERIC    |

+-------------+------------+

Write a query to print the list of CITY that does not start with vowels or does not end with vowels in lexicographical order. Do not print duplicates.

>>select distinct city from station where (city not like '%a' and city not like '%e' and city not like '%i' and city not like '%o' and city not like '%u') or (city not like 'a%' and city not like 'e%' and city not like 'i%' and city not like 'o%' and city not like 'u%') order by city;

Thanks.

Selecting 'vowels'

Given a table STATION that holds data for five fields namely ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.

+-------------+------------+

| Field       |   Type     |

+-------------+------------+

| ID          | INTEGER    |

| CITY        | VARCHAR(21)|

| STATE       | VARCHAR(2) |

| LAT_N       | NUMERIC    |

| LONG_W      | NUMERIC    |

+-------------+------------+

1) Write a query to print the list of CITY that start with vowels (a, e, i, o, u) in lexicographical order. Do not print duplicates.

>>select distinct city from station where city like'a%' or city like'e%' or city like'i%' or city like'o%' or city like'u%' order by city;

2) Write a query to print the list of CITY that ends with vowels (a, e, i, o, u) in lexicographical order. Do not print duplicates.

>>select distinct city from station where city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u' order by city;

SQL 'COUNT()' Function

Given a table STATION that holds data for five fields namely ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.

+-------------+------------+

| Field       |   Type     |

+-------------+------------+

| ID          | INTEGER    |

| CITY        | VARCHAR(21)|

| STATE       | VARCHAR(2) |

| LAT_N       | NUMERIC    |

| LONG_W      | NUMERIC    |

+-------------+------------+

Let NUM be no. of cities and NUMunique be no. of unique cities, then write a query to print the value of NUM - NUMunique


>>select count(city)-count(distinct city) from station;





Thanks.

Basic 'Select' query

Given a table STATION that holds data for five fields namely ID, CITY, STATE, NORTHERN LATITUDE and WESTERN LONGITUDE.

+-------------+------------+

| Field       |   Type     |

+-------------+------------+

| ID          | INTEGER    |

| CITY        | VARCHAR(21)|

| STATE       | VARCHAR(2) |

| LAT_N       | NUMERIC    |

| LONG_W      | NUMERIC    |

+-------------+------------+

Write a query to print the list of CITY in lexicographical order for even ID only. Do not print duplicates.

>>select distinct city from station where mod(id,2)=0 order by city;

Thanks.