Optimize Your Time Wisely......

There are 4 computer jobs which have to be run on 3 processors.Each job requires a certain amount of processor time, but is migratable i.e it can be started on one processor, suspended, continued on another processor, etc., as long as the total time it gets on processors is sufficient.
If the times required by these 4 jobs is 20 secs, 20 secs, 21 secs and 100 secs respectively , find out the maximum possible time for which all 3 processors can be kept busy with optimal scheduling. Also provide a general solution for this problem for 'n' processors where 'p' jobs have to be run which takes p1,p2,p3..... times to finish.

PL/SQL : Lets program our way into Database

* PL/SQL is Oracle Corporation's procedural extension to SQL. Its a 4th generation language which provides the fancy and powerful features of software engineering like overloading,data-encapsulation,collection types, exceptions and information hiding.

--------------

* The basic unit of PL/SQL program is Block. There are 2 types of blocks : Anonymous and Named. Subprograms fall in the category of named blocks. The basic structure of a block is :

DECLARE

-- Declaration block (optional)

BEGIN

-- Programming logic

EXCEPTION

-- Exception-handling (optional)

END

----------

* Subprograms are named PL/SQL blocks that can be called with a set of parameters. PL/SQL has 2 types of subprograms:procedures and functions. Generally, a function is used to calculate a value while procedure is used to perform an action. Procedures act like new statements while functions act like new expressions or operators.

--------

* Package is a database object that bundles logically related data-types , variables , cursors , and subprograms together. It defines a simple clear interface to a set of related procedures and types that can be accessed by SQL statements. Packages usually have 2 parts : a specification and a body. The specification defines the application programming interface ; it defines the types, constants, variables, exceptions, cursors and subprograms. The body fills in the SQL queries for cursors and the code for subprograms. Packages are stored in databases where they can be shared by many applications. When a packaged subprogram is called for the first time, the whole of package gets loaded and cached into the memory thereby saving lots of time on disk I/O for subsequent calls.

-----

* Difference between CHAR and VARCHAR2 Datatypes:

When you assign a character value to a CHAR variable, if the value is shorter than the declared length of the variable, PL/SQL blank-pads the value to the declared length. Information about trailing blanks in the original value is lost. If the character value is longer than the declared length of the CHAR variable, PL/SQL aborts the assignment and raises the predefined exception VALUE_ERROR. PL/SQL neither truncates the value nor tries to trim trailing blanks. However , when you assign a character value to a VARCHAR2 variable, if the value is shorter than the declared length of the variable, PL/SQL neither blank-pads the value nor strips trailing blanks. Character values are assigned intact, so no information is lost. If the character value is longer than the declared length of the VARCHAR2 variable, PL/SQL aborts the assignment and raises VALUE_ERROR. PL/SQL neither truncates the value nor tries to trim trailing blanks.

--

* PL/SQL Collections:

A collection is an ordered group of elements all of the same type. Its a general concept that covers lists , arrays and other datatypes used in programming algorithms. PL/SQL offers 3 types of collections:

1. Nested Tables

2. Associative Arrays(Index-by Tables)

3. Varrays

Variables whose type is either nested table or Varray must be initialized with a constructor before they can be used.

-

* Differences between Nested Table and Array:

a> Nested Tables do not have a declared number of elements, while arrays have a predefined number.

b> Nested tables may not have consecutive subscripts, while arrays are always dense (have consecutive subscripts). Initailly, nested tables are dense, but they can become parse.

-

*Constructor: Constructors are used to initialize an object. It is a system-defined function whose name is same as that of its object. While an ordinary function returns some type, a constructor returns self as result. In PL/SQL , a constructor is used to initialize a nested table or a varray. Until a nested table or varray is initialized , it is automatically null. (The collection itself is null and not its elements)

-

*Transaction: A transaction is a series of SQL DML statements that does a logical unit of work. The statements either fail or suceed in a group. e.g two update statements might credit one bank account and debit another one. It is important not to allow one operation to succeed while the other one fails.

Who is wearing what?

There are few people in a meeting-room and each one of them wears a blue or a grey shirt. Every person counts the number of people wearing blue shirts. You are given a single dimensional array where these counts are recorded. Find out the total number of people wearing the blue shirts? If the given array is invalid, then print "Invalid". Some of these arrays can be:
1. Count[]={2,1,1}
2. Count[]={2,2,2,2,2,2}
3. Count[]={2,10}

Search in the Sequence

There is an infinite sequence of numbers as follows :

1,2,2,3,3,3,4,4,4,4,5,5,5,5,5......(the 'i' number appears i times) . If you are given a number 'k', write a program that will return the kth number in the sequence given above.

e.g if k=4 then answer will be 3.

Who will have the last laugh??

Lets get our thinking caps on:

There are 2000 persons in a hall.Everyone is named in numbers such as 1 to 2000(including 1 and 2000).They are arranged in the ascending order.A gun is given to the person 1.Person 1 will shoot the one next to him and give it to the person who is surviving next to the dead person.(i.e)1 will kill 2 and give it to 3,3 will kill 4 and give it to 5,and so on.This process is repeated continuously until there are two persons left.Who are the two people left in the end? (All are Standing in a circle).

Waiting for your answers!!!! (Write a program also to generalize your solution)

Factorial and Rownumbers (SQL Puzzle 3)

Lets do some mathematics!!

We need to calculate factorial of numbers (say from 1 to 10). One of the useful queries can be as follows:


SELECT
rownum,
EXP(SUM(LN(rownum)) OVER (ORDER BY ROWNUM)) Factorial
FROM all_objects
WHERE rownum < 10

(Note : This query was given to me by my friend and mentor Mr. Bucchibabu).

I hope it helps in some way or other!!

Who is more Popular?? (SQL Puzzle 2)

I have a table with 1 column called 'Name' . The values in the table are as follows:

select * from table

Name
--------
Gaurav Goel
Nitin Jain
Gaurav Sharma
Gaurav Kapoor
Sanjeev Sinha
Sanjeev kapoor
Nishi Kant
Nitin Agrawal

Now I have to retrieve data in order of popularity. The popularity of any name is defined as follows:

Extract the first names for all entries. The one with more popular first names have to be shown first. In case of ties, the name which comes first in the table has to be displayed first. In the above sample values, the required output is :

Gaurav Goel
Gaurav Sharma
Gaurav Kapoor
Nitin Jain
Sanjeev Sinha
Sanjeev kapoor
Nitin Agrawal
Nishi Kant

The query written is :


select name from
(
select rownum rn,name,
substr(name,1,instr(name,' ')) first_name,
count(substr(name,1,instr(name,' '))) over(partition by substr(name,1,instr(name,' '))) occurence
from names
)
order by occurence desc,rn

It seems to work!! Can anybody plz give a better solution??

Number 0f working days between any 2 given dates (SQL Puzzle 1)

Problem: Write a query to calculate number of working days between any 2 given dates.

The working days here refer to the count of days excluding Saturdays and Sundays. Let us take the given 2 dates as '26-Aug-2007' and SYSDATE. (26th August is my Birthday :-D ).

The query looks like:

select count(val) Number_of_working_days from
(
select
to_number(to_char(to_date('26-aug-07')+rownum , 'D')) val,
to_char(to_date('26-aug-07')+ rownum) date1,
to_char(to_date('26-aug-07') + rownum, 'Day') day1
from
all_objects
where
to_date('26-aug-07')+rownum<= sysdate
)
where val not in (6,7)

I genuinely feel that a better query can be written for this purpose. Please provide your valuable inputs!!!

The Magic of 'Start With Connect By' Clause

In Oracle , 'Start With..Connect By' Clause is a powerful way to select data that has hierarchical relationship. (like Parent -> Child or Manger->Employee). We will explore it with the help of an example. First, create a table named "Entities".

CREATE TABLE ENTITIES

(

PARENT_ENTITY VARCHAR2(20 BYTE),

CHILD_ENTITY VARCHAR2(20 BYTE)

);

Now insert some sample values in it.

Insert into ENTITIES (CHILD_ENTITY) Values ('a');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('a', 'af');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('a', 'ab');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('a', 'ax');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('ab', 'abc');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('ab', 'abd');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('ab', 'abe');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('abe', 'abes');
Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('abe', 'abet');
Insert into ENTITIES (CHILD_ENTITY) Values ('b');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('b', 'bg');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('b', 'bh');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('b', 'bi');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('bi', 'biq');

Insert into ENTITIES (PARENT_ENTITY, CHILD_ENTITY) Values ('bi', 'biv');

COMMIT;

This data heirarchy looks like :

(Click on the image to enlarge)

Now we will try to understand the result of the following query:

Main Query:

select level,parent_entity,child_entity
from entities
start with parent_entity is null
connect by prior child_entity=parent_entity

The execution is something like this:
1. First look at your base result i.e
select * from parent_entity. (Keep this in mind)

2. Look at the start by condition...start with parent_entity is null. The startwith condition is used to identify the root of the tree. The 'prior' operator is used to specify the direction in which the query traverses the tree(down from root or up from branches)
The 'data to be scanned' is : (select * from entities where parent_entity is null)
Parent_Entity..............Child_Entity
........NULL............................a

........NULL............................b
So it searches those child_entities where parent_entity is null i.e top of the tree...

The main query result till this stage is:
Level........Parent_Entity............Child Entity
....1....................... NULL.........................a

3. It will now search for allthe childs of 'a'.
The childs of a are : af , ab and ax (this is done as a result of connect by prior child_entity=parent_entity clause )
It will now search for the childs of af. It will show the result in main query. (There are no childs of af)
The main query result till this stage is:
Level........ Parent_Entity....... Child Entity
....1...................... NULL......................a
....2......................... a........................af
It will now search the childs of ab. It will show the result in main query. (There are 3 childs of ab : abc,abd,abe)
Level........ Parent_Entity........ Child Entity
....1........................ NULL.................... a
....2........................... a........................af
....2........................... a........................ab
Now taking 1 child at a time of ab ; it will dig into abc,abd and abe. There are no childs for abc and abd but abe has two childs : abes and abet.

So the result of main query now is:
Level............... Parent_Entity................ Child Entity
....1 .................................NULL............................... a
....2...................................... a...................................af
....2...................................... a...................................ab
....3..................................... ab..................................abc

....3..................................... ab..................................abd
....3..................................... ab..................................abe
....4 .....................................abe................................abes
....4..................................... abe................................abet
It will now serch for childs of abes. There are no childs. It will now search for the childs of abet. There are no childs. So now the it will trace back the loop where it had left i.e at "ax" (3rd child of a) .
4. It will show the row corresponding to ax and serach for its childs (There are no childs for ax).
Result of main query now is:
Level.......... Parent_Entity.......... Child Entity
....1......................... NULL.......................... a
....2............................. a...............................af
....2............................. a...............................ab
....3............................ ab..............................abc
....3............................ ab..............................abd
....3............................ ab..............................abe
....4.............................abe............................abes
....4............................ abe............................abet
....2............................ a................................ax

5. Since there are no childs of ax, it will now trace back to the result set of our "start with" condition and start looking for data corresponding to 'b' ...(read point no. 2).
It will follow the same steps for this level also.

6. The final main query result set will be :
Level........... Parent_Entity.............. Child Entity
....1............................ NULL............................. a
....2................................ a............................af
....2................................ a ...........................ab
....3................................ ab..........................abc
....3................................ ab..........................abd
....3................................ ab..........................abe
....4................................ abe........................abes
....4................................ abe........................abet
....2................................ a............................ax
....1.............................. NULL........................... b
....2................................ b............................bg
....2................................ b............................bh
....2................................ b............................bi
....3................................ bi...........................biq
....3................................ bi...........................biv

Thats it!!!!!
You can alter your tree traversal by changing your "start with" condition.
For example if I write

select level,parent_entity,child_entity
from entities
start with parent_entity = 'a'
connect by prior child_entity=parent_entity

It will cut down the 'b' branch and will show only 'a' hierarchy. :-)) This process is called 'pruning'.

I hope u find it helpful.