Package in Oracle

Steps

1.

create table customer99999(id int, name char(20), age int, address char(20), salary int)

 2.

Insert into customer99999 values(1,'Raj',23,'Jalandhar',20000)

Insert into customer99999 values(2,'Raju',24,'Salempur',50000)

Insert into customer99999 values(3,'Ram',25,'Simraungadh',60000)

Insert into customer99999 values(4,'Raman',26,'Ludhina',70000)

Insert into customer99999 values(5,'Raju',27,'Simra',80000)

select * from customer99999

ID	NAME	AGE	ADDRESS	SALARY
1	Raj	23	Jalandhar	20000
2	Raju	24	Salempur	50000
3	Ram	25	Simraungadh	60000
4	Raman	26	Ludhina	70000
5	Raju	27	Simra	80000

How to create Package

create package customer5_salary

as

procedure find_salary(customer_id customer99999.id%type);

end customer5_salary;

Results

Package created.

How to crate package body

create or replace package body customer5_salary

as

procedure find_salary(customer_id customer99999.id%type)

is

customer_salary customer99999.salary%type;

begin

select salary into customer_salary

from customer99999

where id = customer_id;

dbms_output.put_line('Salary:'||customer_salary);

end find_salary;

end customer5_salary;

Results

Package body created.

Declare Value

set serveroutput on
declare

code customer99999.id%type:=&customer1_id;

begin

customer5_salary.find_salary(code);

end;

Results

old 2: code customer99999.id%type:= &cc_id; 

new 2: code customer99999.id%type:= 3; 
Salary:60000 
PL/SQL procedure successfully completed

How to remove pendrive shortsuts

Steps

Press Windows Key+R

 Press cmd & Press Ok 

 Press Pendrive Drive like F:

Press ENTER KEY

Press attrib -s -h /s /d *.* 

Press ENTER KEY

How to Copy codes from other Accounts in Fact

It helps us to copy all master Account, From one company to Another Company.

     1.       Click on master menu.

     2.       Click on copy codes from other Accounts & press Enter Key.

     3.       Double click on PL Layout & Select on sample Data & Press Ok.

     4.       Click on Tag All Button & Finally click on Ok button.

     5.       Again click on Master Menu.

     6.        Click on copy codes from other Accounts & press Enter Key.

     7.       Double click on BS Layout & Select on Manufacturing sample Data & Press Ok.

     8.       Finally save the setting.

How to set the system control accounts or ledgers in Fact

Steps

1. Select the account Menu.

2. Select the preferences.

3. Select the system controls.

4. Select the P/ L and Cash, stock, purchase, sales one by one and set the all required system control ledgers.

5. Click on cancel or press ESC key to get out of the setting.

Note:- Now open main Company and copy P/L Layout and B/s Layout by using “Copy codes from another account” option from master menu.

Now Setting  these system control accounts

   1. Click on Account menu.

   2. Click on Preferences option.

   3. Select the System Controls.

Format of Profit & Cash Book A/c

Format of Stock A/c

Format of Sales A/c

 Format of Purchase A/c

All Entrepreneurs Should Watch Movies

Introduction

Movies are a better way to learn from others as stories, fiction and nonfiction, because psychologically people things in image not in word. So it can be easy to understand all movies as a compare to stories. There is a lot of things to be learnt from these stories as they show you how exceptional people who have come before you have done it.

The Godfather

An offer you can’t refuse.

The Godfather movies are told about the any offer you can’t refuse, offer is a chance of success. It is one of the greatest films in the world based on the best book I have ever read. The Godfather is the story about the growth of a small family business as it becomes the largest organised crime family in New York, fighting off opposition at any expense. The Godfather and his son Michael Corleone are the brains behind the family and this film gives you an "insight into what it takes to become one of the most powerful family businesses in the country.

Glengarry Glen Ross

Lie. Cheat. Steal. All in A Day’s Work.

In the fast paced world of Chicago real estate, competition and incentive is what decides who the best is. That’s why they’re having a little sales competition – First prize is a Cadillac El Dorado, second prize is a set of steak knives, third prize is you’re fired. With stakes that high, and the pressure on, the salesmen show that they’re willing to do anything it takes to succeed in business, but the most interesting part of this is film, is what exactly that is.

Wall Street

Every dream has its price.

Wall Street movies are related stock exchange. The first movie is Wall Street money never sleeps and second is The Wolf of Wall Street; both movie stories are true story in stock exchange. In the life of an ambitious young stockbroker, Bud Fox will do just about anything it takes to succeed, and for him, that includes a little bit of insider trading. See what happens when the desire to succeed takes over your life and the motto of ‘Greed is good’ directs how you do business. But what happens when you’re pushed to your limits and asked to do something you can’t or won’t do? Suddenly everything comes to a head when Fox is asked to help do something, not only illegal, but morally reprehensible.

Catch Me If You Can

The true story of a real fake

Again, this is another true story, about a young conman who successfully impersonated an airline pilot, doctor, and lawyer and history professor during his stint as fraudulent cheque casher. What’s amazing about this film and the life of Frank Abegnale Jr. is how much he managed to achieve in a much tougher environment and under more stress than most people are used to. Whether it’s legal or not, business is business and Frank was one of the best at what he did.

The Corporation

This film takes a good, long look at what happens when you take a business and you model it to be legally a person – a corporation. What happens though when the sole purpose of this person is to make money for its shareholders though? It starts to act without a conscience which threatens the world, our future and the people working for it. This isn’t just a film about evil corporations though; it’s also about what we can do to stop them from taking over everything that we know. I saw this film in the cinema and its well worth seeing again, it’s completely eye opening.

Shaw shank Redemption

Fear can hold you prisoner. Hope can set you free.

This is one of the greatest films of all time, and one I’m sure that the majority of readers have seen before, purely because of its undeniable greatness. It’s the story of a man who manages to succeed in the worst possible conditions imaginable – false imprisonment for the murder of his cheating wife and lover. From making friends with another con inside the prison, Andy manages to make the best out of a very bad situation and through perseverance manages to achieve more than he ever thought possible.

The Social Network

You don’t get to 500 million friends without making a few enemies

This is another story of the young, successful and intelligent man, only this time; it’s based on a true story of the world’s youngest billionaire – Mark Zuckerberg, the creator and owner of Facebook. The story is pretty amazing and has been made famous by this film, he starts off as a Harvard student with a strong streak of arrogance and huge potential, and he creates a new social network based upon the most prestigious email address in the country, the Harvard email address. The rest, as they say, is history.

The Game

There are no rules in The Game.

What do you get the guy who has everything? Nicholas Van Orton (played by Michael Douglas) is a wealthy San Francisco banker whose life is wrapped up in his business, which has left him as somewhat of a loner. His brother Conrad gives him the gift of a lifetime, which would prove to change his perspective on the world and the way that he’s going to live it. When bad things start to happen to you, you start to question what’s really important in live, business, success and money, or actually living.

Risky Business

There’s a time for playing it safe and a time for Risky Business.

A good story about what you have to do when you take your dad’s Porsche out for a trip and starts to realize that you’re going to need a lot of money, which you’re going to have to raise in a very creative way. With his parents away and responsibility out of the window, Joel starts to realize that it’s not all as easy as he was expecting, having to look after friends and source cash any way he can. An interesting look at what people can come up with when they’re put on the spot to come up with some fast cash.

SQL/ORACLE interview Questions

What is a Procedure ?

A Procedure consist of a set of SQL and PL/SQL statements that are grouped together as a unit to solve a specific problem or perform a set of related tasks.

What is difference between Procedures and Functions ?

A Function returns a value to the caller where as a Procedure does not.

Difference between procedure and function.?

Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.

What is difference between Rename and Alias?

Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed

What is a cursor?

Oracle uses work area to execute SQL statements and store processing information PL/SQL

construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.

What is a outer join?

Outer Join--Its a join condition used where you can query all the rows of one of the tables in the join condition even though they don’t satisfy the join condition.

What is difference between UNIQUE and PRIMARY KEY constraints?

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.

What is the difference between candidate key, unique key and primary key.

Candidate keys are the columns in the table that could be the primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.

What is SAVE POINT ?

For long transactions that contain many SQL statements, intermediate markers or savepoints can be declared which can be used to divide a transaction into smaller parts. This allows the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction.

Cursors

Steps
1. create table customer(id int,name char(20), age int, address char(20), salary int)

2. insert into customer values(1,'Raj',23,'Jalandhar',20000)
    insert into customer values(2,'Raju',25,'Simraungadh',20000);
   insert into customer values(3,'Ram',25,'Ludhania',25000);
   insert into customer values(4,'Ramu',26,'Janakpur',30000);
   insert into customer values(5,'Raman',29,'Birgunj',35000);

select*from customer

ID	NAME	AGE	ADDRESS	SALARY
1	Raj	23	Jalandhar	20000
2	Raju	25	Simraungadh	20000
3	Ram	25	Ludhania	25000
4	Ramu	26	Janakpur	30000
5	Raman	29	Birgunj	35000

set serveroutput on

declare

Total_rows number(2);

begin

update customer9999

set salary=salary+500;

if sql%notfound then

dbms_output.put_line('No customer selected');

else if sql%found then

Total_rows:=sql%rowcount;

dbms_output.put_line(Total_rows||'customer selected');

end if;

end if;

end;

Results

5customer selected 
PL/SQL procedure successfully completed.

Function Question with Answer in Sql/Oracle

WAP to print the maximum number using function.

create or replace function findmax(x number, y number)
return number
is
z number;
begin
if x>y then
z:=x;
else
z:=y;
end if;
return z;
end;

set serveroutput on
declare
a number;
b number;
c number;
begin
a:=&a;
b:=&b;
c:=findmax(a,b);
dbms_output.put_line('maximun'||c);
end;

Results
old 6: a:=&a; 
new 6: a:=10; 
old 7: b:=&b; 
new 7: b:=12; 
maximun12 
PL/SQL procedure successfully completed.

OR

set serveroutput on
declare
a number;
b number;
c number;
function findmax(x number, y number)
return number
is
z number;
begin
if x>y then
z:=x;
else
z:=y;
end if;
return z;
end;
begin
a:=&a;
b:=&b;
c:=findmax(a,b);
dbms_output.put_line('Maximun'||c);
end;

Results
old 18: a:=&a; 
new 18: a:=12; 
old 19: b:=&b; 
new 19: b:=15; 
Maximun15 

PL/SQL procedure successfully completed.

Loops in Oracle

WAP to print the 10 different circle using loop.
set serveroutput on
declare
area number;
begin
area:=2;
while area <=10 loop
dbms_output.put_line('The Area is '||3.14 * area * area);
area := area+2 ;
end loop;
end;

Results
The Area is 12.56 
The Area is 50.24 
The Area is 113.04 
The Area is 200.96 
The Area is 314 
The Area is 452.16 
The Area is 615.44 
The Area is 803.84 
The Area is 1017.36 
The Area is 1256 
PL/SQL procedure successfully completed.

Write a program to accept a number and print it in reverse order?

set serveroutput on
DECLARE
A number;
Rev number;
R number;
begin
A:=&A;
Rev:=0;
R:=0;
WHILE A !=0 loop
R:=mod(A,10);
Rev:=Rev*10+R;
A:=trunc(A/10);
end LOOP;
dbms_output.put_line('The Reverse of a Given Number is '||Rev);
end;

Results
old 6: A:=&A; 
new 6: A:=567; 
The Reverse of a Given Number is 765 
PL/SQL procedure successfully completed.

Write to print the Table of any number enter by user?
set serveroutput on
declare
N number;
i number;
begin
n:=&n;
i:=1;
dbms_output.put_line('Table of'||N);
while i<=10 loop
dbms_output.put_line(N*i);
i:=i+1;
end loop;
end;

Results
old 5: n:=&n; 
new 5: n:=4; 
Table of4 
4 
8 
12 
16 
20 
24 
28 
32 
36 
40 
PL/SQL procedure successfully completed.

WAP to print the digit of a number in reverse order?
set serveroutput on
DECLARE
A number;
Rev number;
R number;
begin
A:=&A;
Rev:=0;
R:=0;
WHILE A>0 loop
R:=A mod 10;
Rev:=Rev*10+R;
A:=trunc(A/10);
end LOOP;
dbms_output.put_line('The Reverse of a Given Number is '||Rev);
end;

Results
old 6: A:=&A; 
new 6: A:=456; 
The Reverse of a Given Number is 654 
PL/SQL procedure successfully completed.

WAP to print the square of 10 numbers using loop?

set serveroutput on
declare
A number;
square number;
begin
A:=1;
while A<11 loop
dbms_output.put_line('Square of A:'||square);
A:=A+1;
square:=A*A;
End loop;

End;

Results
Square of A: 
Square of A:4 
Square of A:9 
Square of A:16 
Square of A:25 
Square of A:36 
Square of A:49 
Square of A:64 
Square of A:81 
Square of A:100 

PL/SQL procedure successfully completed.

WAP to print the Cube of 10 numbers using loop?

set serveroutput on
declare
A number;
cube number;
begin
A:=1;
while A<11 loop
dbms_output.put_line('Cube of A:'||cube);
A:=A+1;
cube:=A*A*A;
End loop;
End;

WAP to print the Average Marks of 5 Student using loop?
set serveroutput on
DECLARE
S1 number;
S2 number;
S3 number;
S4 number;
S5 number;
i number;
Average number;
total number;
begin
i:=1;
while i<=10 loop
S1:=&S1;
S2:=&S2;
S3:=&S3;
S4:=&S4;
S5:=&S5;
total:=S1+S2+S3+S4+S5;
Average:=total/5;
i:=i+1;
end loop;
dbms_output.put_line('Average'||Average);
end;

Results
old 13: S1:=&S1; 
new 13: S1:=78; 
old 14: S2:=&S2; 
new 14: S2:=78; 
old 15: S3:=&S3; 
new 15: S3:=66; 
old 16: S4:=&S4; 
new 16: S4:=45; 
old 17: S5:=&S5; 
new 17: S5:=45; 
Average62.4 
PL/SQL procedure successfully completed.

Write the program to print the First 20 Number.

set serveroutput on
declare
A number;
begin
A:=1;
while A<20 loop
dbms_output.put_line('Value of A:'||A);
A:=A+1;
End loop;
End;

Results
Value of A:1 
Value of A:2 
Value of A:3 
Value of A:4 
Value of A:5 
Value of A:6 
Value of A:7 
Value of A:8 
Value of A:9 
Value of A:10 
Value of A:11 
Value of A:12 
Value of A:13 
Value of A:14 
Value of A:15 
Value of A:16 
Value of A:17 
Value of A:18 
Value of A:19 
PL/SQL procedure successfully completed.


Write the program to print the First 20 Even Number.

set serveroutput on
declare
A number;
begin
A:=2;
while A<40 loop
dbms_output.put_line('Even Number of A:'||A);
A:=A+2;
End loop;
End;

Results
Even Number of A:2 
Even Number of A:4 
Even Number of A:6 
Even Number of A:8 
Even Number of A:10 
Even Number of A:12 
Even Number of A:14 
Even Number of A:16 
Even Number of A:18 
Even Number of A:20 
Even Number of A:22 
Even Number of A:24 
Even Number of A:26 
Even Number of A:28 
Even Number of A:30 
Even Number of A:32 
Even Number of A:34 
Even Number of A:36 
Even Number of A:38 
PL/SQL procedure successfully completed.

Write the program to print the First 20 Odd Number.

set serveroutput on
declare
A number;
begin
A:=1;
while A<40 loop
dbms_output.put_line('Odd Number of A:'||A);
A:=A+2;
End loop;
End;

Results
Odd Number of A:1 
Odd Number of A:3 
Odd Number of A:5 
Odd Number of A:7 
Odd Number of A:9 
Odd Number of A:11 
Odd Number of A:13 
Odd Number of A:15 
Odd Number of A:17 
Odd Number of A:19 
Odd Number of A:21 
Odd Number of A:23 
Odd Number of A:25 
Odd Number of A:27 
Odd Number of A:29 
Odd Number of A:31 
Odd Number of A:33 
Odd Number of A:35 
Odd Number of A:37 
Odd Number of A:39 
PL/SQL procedure successfully completed.


Write to print the sum of first 20 Even Number.

set serveroutput on
declare
A number;
S number;
begin
A:=2;
S:=0;
while A<40 loop
A:=A+2;
S:=S+A;
End loop;
dbms_output.put_line('Sum of A:'||S);
End;

Results
Sum of A:418 
PL/SQL procedure successfully completed.