Prodapt Interview questions on SQL and Pl/SQL

1.  Difference between function procedure and package..
     
          A function or procedure or an anonymous block is nothing but, SQL and PL/SQL statements are used to perform a task such that it would return a value or values to the calling environment.  (this s a generic definition for function and procedure)
   
     And coming to their difference just remember the below points which they would expect
     Functions:
                     1.  Function return a single value.
                     2.  Functions can be used only in SQL statements.

     Procedure:
                     1.  Procedure can return multiple values.
                     2.  Procedures cannot be used inside SQL statements.
   
    Package:  Its a collection of Functions, procedures, variables, Pl/SQL tables, types, etc into it.  A package consists of Package body and Package specification.  Main advantage of package is it supports OOPS concepts like overloading and encapsulation.

2.  Can we define a procedure in package body without declaring it in package specification.

       Yes we can.  It is a concept of encapsulation.  We can use those procedures inside the package and cannot be used outside the package.  Those procedures are called as private procedures

3.  What is an exception

4.  Name few predefined exceptions.

5.  What are the datatypes a available in PL/SQL?

      Scalar:          NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.
      Composite:   RECORD & TABLE.

6.  How many columns can be created in a table

7.  What is a cursor and what are the types of Cursors?
  There are two types of cursors:  Implict Cursor and Explicit Cursor.
   
     Implict Cursors are the SQL queries which retries data and stores virtually as cursor
     User defined cursors are called Explicit Cursors. They can be declared and defined.

Athena Health Interview questions..

1.       Three people picked 65 apples altogether. At the first tree they each picked the same number of apples. At the second tree they each picked 3 times as many as they picked at the first tree. When they finished at the third tree, the group had 5 times as many apples as they had when they started at that tree. At the fourth tree the group picked just 5 apples. How many apples did each person pick at the first tree?

Answer :

Each person picked 1 apple at the first tree.

Begin with 65 apples picked, and work backwards. Subtract the number of apples picked at the fourth tree (5), and then divide the total (60) by 5 to equal the number of apples picked after the first two trees. The total after the first two trees = 12. Since the number of apples picked at the first two trees (12) is equal to the number of apples picked at the first tree plus 3 times the number picked at the first tree (or a total of 4 times the number picked at the first tree), then the number of apples picked at the first tree must be 12 divided by 4 = 3. Therefore, each person picked 1 apple at the first tree.

Using algebra: Suppose they pick y apples at the first tree. Then, they pick 3y apples at the second tree and have 4y apples altogether. After the third tree, they have 5X4y or 20y apples, and after the fourth tree, they have 20y + 5 apples.

20y + 5 = 65  => 20y = 60 =>  y = 3

So each person picks 1 apple at the first tree.

2.       It rains 3 days a week. When it rains there is Thunderstorm. The probability of Thunderstorm falling on the ground is 1/42000. A Golfer plays golf on Saturday and Sunday. What is the probability of Thunderstorm falling on that Golfer?

The question can be done with help of Combination.

Total possibiliteis of days on which rain occurs = 7C3

Total possiblities containing Sunday or Saturday as one of the days = 7C3-5C3

Probiabiltiy that the thunderstorm occurs on the Sundays and Saturdays = (7C3-5C3)/7C3

Probablitlity tha thuder storms occur on that day =

(7C3-5C3)/7C3 * 1/42000= 25/35*1/42000 = 5/294000

3.       Sona travels 600 km to his house partly by train and partly by car. He takes 8 hrs. if he travels 120km by train and rest by car. He takes 20 min more if he travels 200 km by train and rest by car.Find the speed of the train in km/hr ??

Total distance = 600 kms

Let speed by train be x and speed by car be y.

When he travels 120 kms by train and the rest (480 kms) by car, he takes 8 hrs or 480/60 hours. When he travels 200 kms by train and rest (400 kms) by car, he takes 8 hrs 20 minutes = 500/60 hours.

=> 120/x + 480/y = 480/60 => 240x + 60y = 4xy .. (1)

=> 200/x + 400/y = 500/60 => 240x + 120y = 5xy .. (2)

Solving 1 and 2 we get: x=60 => Speed of train = 60.

4.       Each day a man meets his wife at the train station after work, and then she drives him home. She always arrives exactly on time to pick him up. One day he catches an earlier train and arrives at the station an hour early. He immediately begins walking home along the same route the wife drives. Eventually his wife sees him on her way to the station and drives him the rest of the way home. When they arrive home the man notices that they arrived 20 minutes earlier than usual. How much time did the man spend walking?

A) 45 minutes  B) 50 minutes  C) 40 minutes  D) 55 minutes  E) 35 minutes

Answer

Let t be the time that the man spends walking. Let w be the man's speed of walking. Let v be the car's speed. Let d be the distance from the train station to the couple's home.

Then t + (d-wt)/v = d/v + 1 - (20/60)

[Time taken by man to walk + time taken by the couple to cover the rest of the distance in the car = Time taken by the couple to cover the entire distance from the station to their home in the car + one extra hour as the man had arrived an hour early - 20 minutes earlier that they reach]

Also wt/v + t = 1

[Time taken by man to walk from the station to where his wife picks him up + Time that the wife would have taken to go to the station had the man not walked = 1 hour]

Solving these two equations for t (note that d/v gets eliminated and wt/v gets substituted),

we get 2t = 5/3 => t = 5/6 hours or (5/6)*60 = 50 minutes

The answer is (B)            

5.       A pencil has a regular pentagon as its cross-section, and its maker's logo on one face. If the pencil is rolled on a table, what is the probability that it comes to rest with the logo facing up?

6.       When processing flower-nectar into honeybees’ ex, a considerable amount of water gets reduced. How much flower-nectar must be processed to yield 1kg of honey, if nectar contains 50% water and the honey obtained from this nectar contains 15% water?

A. 3.33 kgs  B. 1.5 kgs C. 1.7 kgs D. 2.1 kgs  E. None of these

flower nectar contains 50% of non water part

in honey this non water part constitutese (100-15)

therefore 0.5 * amount of flower - nector = 0.85 * amount of honey = 0.85 * 1 kg

therefore amount of flower-nector needed = (0.85/0.5) *1 = 1.7 kg

7.       A father is three times as old as his son. After fifteen years the father will be twice as old as his son's age at that time. Hence the father's present age is

A. 36 B. 42 C. 45 D. 48 E. None of the above

Let the son's age be x, then father's age 3x

After 15 years:

Father's age: 3x+15

Son's age:x+15

Now the equation is:2(x+15)=3x+15

Solving we get x=15

Hence, father's present is 45(option C)

8.       In a class if 1 is absent the rest of them can be divided into 6 equal groups and if two are absent can be divided into 7 equal groups.  Find the minimum number of students in the class

Ans:  36 trail and error. :P

 All the best Guys..