Software Materials and Interviews info

WELCOME

2008-11-15T11:10:00.000-08:00

WELCOME

Blogging Is Not A Crime

2008-09-30T06:24:00.000-07:00

Blogging Is Not A Crime

I found this arresting chart on Swivel. It plots the number of bloggers who have been incarcerated over the past few years, based on data collected by the World Information Access project. The number of incidents it tracks went from five arrests in 2003 to 35 last year. As blogging expands internationally, so do the risk of speaking one’s mind. (Something many of us take for granted).

Most of those arrests are in countries with oppressive regimes, such as Egypt, China, Saudi Arabia, and Iran. But bloggers have been arrested in Canada, France, Greece, and even the U.S. (with Josh Wolf being one of the most famous incidents—he spent the better part of a year in jail for refusing to turn over journalistic video footage to a grand jury).

A few involve cases of alleged terrorism or pedophilia, but the majority involve some form of political speech. Some typical examples:

Reza Valizadeh (Iran; November, 2007). “For revealing Iranian president’s overpriced dogs that his security team uses.”
Charles Leblanc (Canada; June, 2006): “For taking pictures at a conference for his blog.”
Josh Wolf (USA; August, 2006): “For videotaping a burning police car.”
Hu Jia (China; December, 2007): “For posting his vocal critiques of human rights abuses and environmental degradation in China and calling the Olympics a ‘human rights disaster.’”
Reza Valizadeh (Iran; November, 2007): “For revealing Iranian president’s overpriced dogs that his security team uses.”
Nay Phone Latt (Burma; January, 2008): “For posting pictures of monks and people demonstrating on the streets.”

What is Copy right

2008-09-27T10:11:00.001-07:00

Copyright Issues

The hot topic on web always will be Copyright. What is this copyright? And why people won’t allow you to copy their content. This is my target topic today which inspired me to write this article.
Being online from last 2 years, I saw almost all the blogs and sites will include a copyright link mentioning not to copy their content without permission. Are they really taking the rights on that content or just warning the visitors by keeping a simple menu called Copyright. These are all unanswered questions.
But here, is that really necessary to take copyrights or warn users not to copy. I would say it’s not necessary as far as it’s a book or something important you are inventing into web through your blog.
Anyway here am not covering all about copyrights like how to take rights? What to do if anyone violates them? If you are really interested to know them visit labnol.org where Amit covered all those faqs.
But the conclusion from my side is until you are doing something extra which is not reproducible by others, you don’t need to take rights and warn visitors not to copy that. I don’t mind if my visitors copy content from TZI. Because from this I will get link back and intern traffic from that.
But the people who are copying content need to remember that copying content won’t help you anymore. Ofcourse you can take some content or idea from that article or anything limited.

Simple tips to Promote blog ourself

2008-09-27T10:05:00.000-07:00

9 Simple tips to Promote blog yourself

I am sharing some of the tips I observed in promoting Blog yourselves. Here you don’t need to ask some one to view your blog or no need to request anyone for anything. You just need to observe and use the resources around you.

Here are some of the sources I observed:

1. Include blog URL in signatures: This is a powerful media to drive the traffic again and again. Naturally you will send tons of mail from your inbox to friends, colleague and to many known and unknown people. So if you include URL in signature, it’s natural that people will click on your URL to know what that is. Here you need to attract them to visit again and again. For that you should have an interesting content on your blog. So just include it and receive the traffic.

One more important thing I forgot to say, People will forward your mail to many people in long run so your URL will travel around the web.

2. Place URL in IM’s: Do place your URL in IM’s like Gtalk, Yahoo messenger, MSN, AOL and etc….

3. Place it in Social Networking Profiles: Yes, do mention URL in profiles of social networking site like Orkut, Facebook, Hi5, MySpace and etc…

4. Submit to Search Engines: Don’t forget to submit your blog to search engines like Google, Yahoo, Live, Cuil and etc…

5. Submit to Social Bookmarking Sites: Promote your content on Social Bookmarking Sites like Digg.com, Reddit.com, StumbleUpon.com, Del.icio.us and etc…

6. Share links with other Bloggers: This is really a powerful trick to drive traffic in the initial stages of your blog. Do share links with Blogs under your niche.

7. Place feed headlines in your other Blogs: If you own multiple blogs, it’s always good to place feed headlines from Feedburner on your blogs. And also place a section called my blogs or my network to let visitors know that you own more that one blog.

8. Comments on fellow blogs: This is my personal experience; Commenting on other blogs will work like anything. You will get lots of traffic starting from the owner of the blog where you commented to his visitors.

9. Join Blog directories: Now-a-days every Country or City has its own blogging communities maintaining a list of Bloggers from that region or language or etc… By joining in those will drive traffic from your own region or Country.

Did I miss anything? OR Any other you know?

Please share your tips here!

Google Phone

2008-09-26T10:59:00.000-07:00

FEATURES of GOOGLE PHONE

Touch screen

Instant access to key information with the touch of a finger

Just tap the screen and go

Google G1

Real Web browsing capabilities

Quick and easy access to the Internet one click away

View more pages and rich Web content on your phone

3MP camera

Capture key moments in high-quality fashion wherever you are

Never worry about blurry photos with auto focus built right in

Customizable home screen

Just drag and drop any of your favorite applications, photos, or folders onto your home screen for quick access to what you use all the time

One-click Google Search

Quick and easy access to the Internet in one click

Video playback

Watch your favorite videos, even YouTube, anywhere

Google G1

3G network and Wi-Fi access*

High-speed 3G network connection for surfing the Web or downloading information quickly and effortlessly

Seamlessly transition to open Wi-Fi networks to surf the Web or download information quickly

Android Market

One-touch access to Android Market

Customize and personalize your device to fit your life with a variety of software applications like games, social networking, and on-the-go shopping

Download the applications wirelessly with just a couple short clicks

QWERTY keyboard

The built-in familiar layout lets you type messages easily without scrolling for the letter you want

Hinged screen slides open to reveal keypad and closes to prevent accidental dialing

Easy access to Google applications

One-touch access to Google applications:

Maps (including satellite, traffic, and street views)

Gmail

YouTube

Calendar

Google Talk

IM/text/e-mail*

Music player

With the 1GB memory card, you'll never be without your favorite tunes

Aliens are there

2008-09-15T19:08:00.000-07:00

DO u believe ALIEN'S...........????

IF not see the below pictures

These are the original pictures.

These are seen but the government hiding the truth.

Alien Image 1

Alien Image 2

UFO :used by aliens to travel

Billion million in digit format

2008-09-15T10:55:00.000-07:00

Million: 1,000,000
Billion: 1,000,000,000
Trillion: 1,000,000,000,000
Quintillion: 1,000,000,000,000,000,000
Sextillion: 1,000,000,000,000,000,000,000
Nonillion: 1,000,000,000,000,000,000,000,000,000,000
Centillion: 1 followed by 303 zeros

Do you want to earn money through websites?

2008-09-15T10:04:00.001-07:00

Do you want to earn money through websites?
How to Create a Website and Earn Money
Here I am going to discuss about how easy it is to generate money by just sitting in front of your PCs and laptops. All you need to do is buy a web space online or use any free services provided by a number of vendors.

Steps
These days a lot of revenue is being generated by the online advertising. It is not that a small number that one can ignore.
Most of the popular search engines are making billions of dollars by online marketing and advertising.
This is one of the quickest and easiest way to earn money.
For this all you need to do is buy a web space and start creating a website or use free service provided by many vendors online.
In the first case you can get a most appropriate URL (a link to your website) with a .com extension.
In the second case you need to settle with some of the names that the vendor will provide with a very long tail attached with the URL before the .com
If your URL is big then people may not remember you website that easily. But, if you want something free you need to compromise certain things.
Once you decide which way is better then you can go ahead and build a website.
If you are looking for a free web space then you can use google blogger, yahoo blogger, wordpress etc., they will provide you with free web space as well as give you some basic templates which you can use to create a website.
You can find a lot of people now a days using the blogger services and earning very good money.
For using these free services you should create an account with them and they will provide you with some basic templates that are installed in their web servers.
Also, if you don't like their templates you can create a template of your own and upload it to their server.
Once you have the template of your choice, you can go ahead and create your first post.
Keep posting regularly on your web site and then increase traffic to your page.
Now that you have a good number of people reading your blog (otherwise called web traffic) you can also place free ads on your web site which will earn you money.
So, start creating your website today and earn money.

Antivirus for free : Avast

2008-09-15T07:32:00.000-07:00

#####
One of the most secured antivirus Avast
providing anti virus for free of cost for home users.
version : Avast 4.8 Home edition.
Key: Free key for persons who registered.
For more details visit www.avast.com
Get it now.....!

Hackers into BigBang

2008-09-14T23:06:00.000-07:00

The hackers reportedly mounted an attack on the Large Hadron Collider, which has raised eyebrows over the security of this historic experiment in the world, as it surpasses a vital milestone.
Scientists say that it was a competition between two hacker groups - known in hacking circles as 2600 and 1337, that led them to break into the experiment just before it was to begin.
2600, also known as the Greek Security Team broke into the experiment and left a message saying, "We are 2600.. don't mess with us..." The scientists who were behind the mammoth experiment had received threatening emails prior to the start.
No real damage done, but the hackers got perilously close. The hackers attacked the Compact Muon Solenoid Experiment, or CMS.
Scientists working at Cern, the organisation that runs the vast smasher, were worried about what the hackers could do because they were "one step away" from the computer control system of one of the huge detectors of the machine, a vast magnet that weighs 12,500 tons, measuring around 21 metres in length and 15 metres wide.
If they had hacked into a second computer network, they could have turned off parts of the vast detector and, said the insider, "it is hard enough to make these things work if no one is messing with it."
Fortunately, only one file was damaged but one of the scientists firing off emails as the CMS team fought off the hackers said it was a "scary experience". The hackers breached the CMSMON system, which monitors the CMS software system. CMS takes vast amounts of data during collisions.

CHROME from GOOGLE

2008-09-12T09:57:00.000-07:00

Google released new browser CHROME which is faster in browsing
and wide display area. You can Install it from the following link.
http://www.google.com/chrome/eula.html

N96

2008-08-29T07:59:00.000-07:00

SaysN96 has more advantages than Iphone, may costs over 25k

400 GB DVD

2008-08-29T07:51:00.000-07:00

Pioneer Released 400 GB DVD

Soon there will be a phase out of those 3.5 GB DVD that you are always using when burning DVD movies.
Pioneer has released on what they claim as the world’s first 16-layer optical disc. With 400 GB Capacity [ An ordinary 3.5 GB DVD ) is only Single Layer.
Right now the technology is Read Only but I do believed that soon they will release the Re-write version of this DVD. But how much will the machine to rewrite this DVD cost?
Let us see what will happen next. Maybe I have to go back to this post after a year?
Anyway, it is really great that Technology is advancing. Just imagine if your regular DVD could hold about 4 movies, this DVD would hold about 200 + Movies.

VISUAL STUDIO 2008 Advantages

2008-08-29T07:20:00.000-07:00

Introduction
Visual Studio 2008 code name "Orcas" Beta 2 has just hit the road and, since it is Beta 2, this means Visual Studio 2008 is feature complete and is ready for RTM. Below, we would find a brief introduction of some of the new features introduced with VS 2008 and .NET 3.5 Beta 2.
A quick list of some of the new features are:
Multi-Targeting support
Web Designer and CSS support
ASP.NET AJAX and JavaScript support
Project Designer
Data
LINQ – Language Integrated Query
The features listed and explained in this paper are not complete and this document intends to give you a forehand to start off with VS 2008.
1. Multi-Targeting Support
Earlier, each Visual Studio release only supported a specific version of the .NET Framework. For example, VS 2003 only works with .NET 1.1, and VS 2005 only works with .NET 2.0.
One of the major changes with the VS 2008 release is to support what Microsoft calls "Multi-Targeting". This means that Visual Studio will now support targeting multiple versions of the .NET Framework, and developers will be able to take advantage of the new features that Visual Studio provides without having to migrate their existing projects and deployed applications to use a new version of the .NET Framework.
Now when we open an existing project or create a new one with VS 2008, we can pick which version of the .NET Framework to work with. The IDE will update its compilers and feature-set to match the chosen .NET Framework.
Features, controls, projects, item-templates, and references that do not work with the selected version of the Framework will be made unavailable or will be hidden.
Unfortunately, support has not been included to work with Framework versions 1.1 and earlier. The present release supports 2.0/3.0 and 3.5 .NET Frameworks.
Microsoft plans to continue multi-targeting support in all future releases of Visual Studio.
Creating a New Project with Visual Studio 2008 that Targets .NET 2.0 Framework Library
The screenshots below depict the creation of a new web application targeting .NET 2.0 Framework. Choose File->New Project. As we see in the snapshot below in the top-right of the new project dialog, there is now a dropdown that allows us to choose which versions of the .NET Framework we want to target when we create the new project. The templates available are filtered depending on the version of the Framework chosen from the dropdown:

Can I Upgrade an Existing Project to .NET 3.5?
When we open a solution created using an older version of Visual Studio and Framework, VS 2008 would ask if migration is required. If we opt to migrate, then a migration wizard would start. If we wish to upgrade our project to target a newer version of the Framework at a later point of time, we can pull up the project properties page and choose the Target Framework. The required assemblies are automatically referenced. The snapshot below shows the properties page with the option Target Framework marked.
2. Web Designer, Editing and CSS Support
One feature that web developers will discover with VS 2008 is its drastically improved HTML designer, and the extensive CSS support made available.
The snapshots below depict some of the new web designer features in-built into VS 2008.
Split View Editing
In addition to the existing views, Design view and Code view, VS 2008 brings along the Split view which allows us to view both the HTML source and the Design View at the same-time, and easily make changes in any of the views. As shown in the image below, as we select a tag in code view, the corresponding elements/controls are selected in design view.
CSS Style Manager
VS 2008 introduces a new tool inside the IDE called "Manage Styles". This shows all of the CSS style sheets for the page.
It can be used when we are in any of the views - design, code and split views. Manage Styles tool can be activated by choosing Format -> CSS Styles -> Manage Styles from the menu. A snapshot of the same would look like the following:
Create a new style using the new style dialog window as show in the snapshot below.
Now, the style manager would show .labelcaption style as well in the CSS styles list. However, if we observe that the body element has a circle around it but the .labelcaption does not have one, this is because the style is not in use yet.
We will not select all the labels below and apply our new style .labelcaption.
We can choose to modify the existing style through GUI using "Modify style..." menu option in the dropdown menu as shown above or choose to hand edit the code by choosing the option "Go To Code".
CSS Source View Intellisense
The designer is equipped with the ability to select an element or control in design-view, and graphically select a rule from the CSS list to apply to it.
We will also find when in source mode that we now have intellisense support for specifying CSS class rules. The CSS Intellisense is supported in both regular ASP.NET pages as well as when working with pages based on master pages.
Code Editing Enhancements
Below is a non-exhaustive list of a few new code editing improvements. There are many more about which I don't know yet.
Transparent Intellisense Mode
While using VS 2005/2003 we often find ourselves escaping out of intellisense in order to better see the code around, and then go back and complete what we were doing.
VS 2008 provides a new feature which allows us to quickly make the intellisense drop-down list semi-transparent. Just hold down the "Ctrl" key while the intellisense drop-down is visible and we will be able to switch it into a transparent mode that enables us to look at the code beneath without having to escape out of Intellisense. The screenshot below depicts the same.
Organize C# Using Statements
One of the small, but a nice new feature in VS 2008 is support for better organizing using statements in C#. We can now select a list of using statements, right-click, and then select the "Organize Usings" sub-menu. When we use this command the IDE will analyze what types are used in the code file, and will automatically remove those namespaces that are declared but not required. A small and handy feature for code refactoring.
3. ASP.NET AJAX and JavaScript Support
JavaScript Intellisense
One new feature that developers will find with VS 2008 is its built-in support for JavaScript Intellisense. This makes using JavaScript and building AJAX applications significantly easier. A double click on HTML control in design mode would automatically create a click event to the button and would create the basic skeleton of the JavaScript function. As we see in the depicted image below, JavaScript Intellisense is inbuilt now. Other JavaScript Intellisense features include Intellisense for external JavaScript libraries and adding Intellisense hints to JavaScript functions.
JavaScript Debugging
One new JavaScript feature in VS 2008 is the much-improved support for JavaScript debugging. This makes debugging AJAX applications significantly easier. JavaScript debugging was made available in VS 2005 itself. However, we had to run the web application first to set the breakpoint or use the "debugger" JavaScript statement.
VS 2008 makes this much better by adding new support that allows us to set client-side JavaScript breakpoints directly within your server-side .aspx and .master source files.
We can now set both client-side JavaScript breakpoints and VB/C# server-side breakpoints at the same time on the same page and use a single debugger to step through both the server-side and client-side code in a single debug session. This feature is extremely useful for AJAX applications. The breakpoints are fully supported in external JavaScript libraries as well.
4. Few Other Features and Enhancements
Below is a list of few other enhancements and new features included in Microsoft Visual Studio 2008.
Project Designer
Windows Presentation Foundation (WPF) applications have been added to Visual Studio 2008. There are four WPF project types:
WinFX Windows Application
WinFX Web Browser Application
WinFX Custom Control Library
WinFX Service Library
When a WPF project is loaded in the IDE, the user interface of the Project Designer pages lets us specify properties specific to WPF applications.
Data
Microsoft Visual Studio 2008 Beta 2 includes the following new features to incorporate data into applications:
The Object Relational Designer (O/R Designer) assists developers in creating and editing the objects (LINQ to SQL entities) that map between an application and a remote database
Hierarchical update capabilities in Dataset Designer, providing generated code that includes the save logic required to maintain referential integrity between related tables
Local database caching incorporates an SQL Server Compact 3.5 database into an application and configures it to periodically synchronize the data with a remote database on a server. Local database caching enables applications to reduce the number of round trips between the application and a database server
LINQ – Language Integrated Query
LINQ is a new feature in VS 2008 that broadens great querying capabilities into the language syntax. LINQ introduces patterns for querying and updating data. A set of new assemblies are provided that enable the use of LINQ with collections, SQL databases, and XML documents.
Visual Studio 2008 Debugger
The Visual Studio 2008 debugger has been enhanced with the following features:
Remote debugging support on Windows Vista
Improved support for debugging multithreaded applications
Debugging support for LINQ programming
Debugging support for Windows Communications Foundation
Support for script debugging, including client-side script files generated from server-side script now appear in Solution Explorer
Reporting
Visual Studio 2008 provides several new reporting features and improvements such as:
New Report Projects: Visual Studio 2008 includes two new project templates for creating reporting applications. When we create a new Reports Application project, Visual Studio provides a report (.rdlc) and a form with a ReportViewer control bound to the report.
Report Wizard: Visual Studio 2008 introduces a Report Wizard, which guides us through the steps to create a basic report. After we complete the wizard, we can enhance the report by using Report Designer.
Expression Editor Enhancement: The Expression Editor now provides expressions that we can use directly or customize as required.
PDF Compression: The ReportViewer controls can now compress reports that are rendered or exported to the PDF format.

FOR VISUAL SCREEN SHOTS SEE THIS LINK

OBJECT ORIENTED PROGRAMING CONCEPT

2008-08-29T07:10:00.000-07:00

Why Object Oriented approach?
A major factor in the invention of Object-Oriented approach is to remove some of the flaws encountered with the procedural approach. In OOP, data is treated as a critical element and does not allow it to flow freely. It bounds data closely to the functions that operate on it and protects it from accidental modification from outside functions. OOP allows decomposition of a problem into a number of entities called objects and then builds data and functions around these objects. A major advantage of OOP is code reusability.
Some important features of Object Oriented programming are as follows:
Emphasis on data rather than procedure
Programs are divided into Objects
Data is hidden and cannot be accessed by external functions
Objects can communicate with each other through functions
New data and functions can be easily added whenever necessary
Follows bottom-up approach

Concepts of OOP:
Objects
Classes
Data Abstraction and Encapsulation
Inheritance
Polymorphism

Briefly on Concepts:

Objects
Objects are the basic run-time entities in an object-oriented system. Programming problem is analyzed in terms of objects and nature of communication between them. When a program is executed, objects interact with each other by sending messages. Different objects can also interact with each other without knowing the details of their data or code.

Classes
A class is a collection of objects of similar type. Once a class is defined, any number of objects can be created which belong to that class.

Data Abstraction and Encapsulation
Abstraction refers to the act of representing essential features without including the background details or explanations. Classes use the concept of abstraction and are defined as a list of abstract attributes.Storing data and functions in a single unit (class) is encapsulation. Data cannot be accessible to the outside world and only those functions which are stored in the class can access it.

Inheritance
Inheritance is the process by which objects can acquire the properties of objects of other class. In OOP, inheritance provides reusability, like, adding additional features to an existing class without modifying it. This is achieved by deriving a new class from the existing one. The new class will have combined features of both the classes.

Polymorphism
Polymorphism means the ability to take more than one form. An operation may exhibit different behaviors in different instances. The behavior depends on the data types used in the operation. Polymorphism is extensively used in implementing Inheritance.

Advantages of OOP

Object-Oriented Programming has the following advantages over conventional approaches:
OOP provides a clear modular structure for programs which makes it good for defining abstract datatypes where implementation details are hidden and the unit has a clearly defined interface.
OOP makes it easy to maintain and modify existing code as new objects can be created with small differences to existing ones.
OOP provides a good framework for code libraries where supplied software components can be easily adapted and modified by the programmer. This is particularly useful for developing graphical user interfaces.

SOME WALK - IN - WEBSITES

2008-08-29T06:49:00.000-07:00

http://www.mywalkin.com/
www.walkins.in/
www.chalowalkin.com/
www.yuvajobs.com/walkin.asp
www.walkinsindia.com
koolkampus.com/walkins/

I-phone

2008-08-10T03:04:00.000-07:00

I-Phone a revolution in mobile world. Cost's 20,000 inr in indian market.

HCL OFF CAMPUS AT NISHITA

2008-08-05T13:21:00.000-07:00

HCL OFF Campus Placement Drive on 08.08.2008
at Nishitha College of Engineering & Technology
on TECH SUPPPORT
for more details visit www.nishitha.in

main() / void main() / int main() / int main(void) / int main(int argc, char *argv[])

2008-08-05T00:43:00.000-07:00

=================================================
main() / void main() / int main() / int main(void) / int main(int argc, char *argv[])

A very common question is "What's the difference between void main and int main?". This particular FAQ tries to answer that and more, covering other versions of the main() implementation.
The first thing to note is that this is one of those topics that people seem to like to argue over for hours, days and more. Some arguments are valid, some are not, and some are just plain old opinion.
The C and C++ standards differ when it comes to main(), so I'll detail each one separately.
For C
Under C89, main() is acceptable, although it is advisable to use the C99 standard, under which only these are acceptable:
int main ( void )int main ( int argc, char *argv[] )
Slight variations of the above are acceptable, where int can be replaced by a typedef name defined as int, or the type of argv can be written as char ** argv, and so on.
The first option is used when you do not require access to the command line arguments.
The names argc and argv are identifiers that can be changed if you so desire, but sticking to argc/argv is convention.
The return type of main() must always be an int, this allows a return code to be passed to the invoker.
Under C89, the return statement at the end of main() is required, whereas under C99 if no return statement is present, return 0 is implied. However, it is good programming practice to always use a return statement, even if you don't have to.
For C++
The following are acceptable uses:
int main ( int argc, char *argv[] )int main ()
The first option follows similar conventions to those used by C99.
The second option is used when you do not require access to the command line arguments, and is equivalent to the int main(void) option used by C99.
Again, the return type must always be an int, and the function should return 0; at the end, but it is not required by the standard.
(C) The difference between int main() and int main(void)
A common misconception for C programmers, is to assume that a function prototyped as follows takes no arguments:
int foo();
In fact, this function is deemed to take an unknown number of arguments. Using the keyword void within the brackets is the correct way to tell the compiler that the function takes NO arguments.
What's the deal with void main()
Under regular function calling/returning in C and C++, if your don't ever want to return anything from a function, you define it's return type as void. For example, a function that takes no arguments, and returns nothing can be prototyped as:
void foo(void);
A common misconception is that the same logic can be applied to main(). Well, it can't, main() is special, you should always follow the standard and define the return type as int. There are some exceptions where void main() is allowed, but these are on specialised systems only. If you're not sure if you're using one of these specialised systems or not, then the answer is simply no, you're not. If you were, you'd know it.
Be warned that if you post your "void main" code on the forums, you're going to get told to correct it. Responding with "my teacher said it's OK" is no defence; teachers have a bad habit of being wrong. Be safe, and post only standard code, and you'll find people concentrate on answering your other problems, rather than waste time telling you about this type of thing.
But what about int main(int argc, char *argv[], char *envp[])
As an extension to the guaranteed standard, an additional parameter to main() can, on some systems, be used to gain access to the environment variables. This is isn't guaranteed to work on all compilers, so use it with care if you want to keep your code portable.
===================================================================

DIFFERENCE BETWEEN MALLOC CALLOC

2008-08-05T00:42:00.001-07:00

===============================================================
DIFFERENCE BETWEEN MALLOC CALLOC

Both malloc and calloc do the same thing with almost the same results, they allocate a block of n * sizeof ( T ) bytes of memory. The difference is in how the functions are called and how the memory block is initialized. malloc is called like so:
p = malloc ( n * sizeof ( T ) );
where the user programmer performs the operation to find the final block size. calloc takes two arguments and performs the operation itself:
p = calloc ( n, sizeof ( T ) );
malloc leaves the block of memory uninitialized, but calloc zero fills the memory. This does not mean that
p = calloc ( 1, sizeof ( int * ) );
will result in a pointer to int with a value of NULL. Zero filled memory does not mean that the memory is filled with the data type's equivalent of 0, so this zero fill cannot be relied on except with integral values such as int or char. Pointers and floating point may use a different representation for 0 than all bits zero.
Because of this zero fill, calloc can be slightly less efficient than malloc.

DIFFERENCES BETWEEN C and CPP

2008-08-05T00:40:00.000-07:00

===================================================
DIFFERENCES BETWEEN C and CPP

Put simply, C++ is an extended C meant to improve safety, give the programmer more options, simplify higher level programming, and offer a better approach to large scale programming. C++ is also a larger language with more features and complexity than C, but C++ can improve productivity with its greater amount of features. A list of features that C++ supports which C does not includes:
Classes
Member functions
Constructors and destructors
Derived classes
Virtual functions
Abstract classes
Access control (public, private, protected)
friend functions
Pointers to members
static members
mutable members
Operator overloading
References
Templates
Inline functions
Default arguments
Function overloading
Namespaces
Exception handling
Run-time type identification
// comments
True const
Declarations as statements
Automatically typedef'd struct tags
Type safe linkage
new and delete
bool keyword
Safer and more robust casting
===============================================================

DIFFERENCES BETWEEN COUT,PRINTF

2008-08-05T00:37:00.001-07:00

=================================================================
DIFFERENCES BETWEEN COUT,PRINTF

printf is a function that takes a variable number of arguments, the first argument being a format string explaining how to treat further arguments. cout is an object of the std::ostream class. The two are completely different in form, yet the result is the same, data sent to stdout.
This question is difficult to answer completely as often phrased since cout and printf are wildly different, but most often one of three questions are meant:
Q: "Which is faster?" A: printf, but the difference is too slight to worry about.
Q: "Which is better?" A: Neither, it depends heavily on personal preference and what needs to be done.
Q: "Which should I use?" A: You should use whichever you feel most comfortable with.It is preferred that you use iostreams with C++ instead of the C functions because iostreams are type safe and extensible for user defined types, both of which are very good reasons.
===================================================================

DIFFERENCES BETWEEN NULL AND 0, \0

2008-08-05T00:30:00.000-07:00

==============================================================
DIFFERENCES BETWEEN NULL AND 0, \0

NULL is a macro defined in several standard headers, 0 is an integer constant, '\0' is a character constant, and nul is the name of the character constant. All of these are *not* interchangeable:
NULL is to be used for pointers only since it may be defined as ((void *)0), this would cause problems with anything but pointers.
0 can be used anywhere, it is the generic symbol for each type's zero value and the compiler will sort things out.
'\0' should be used only in a character context.
nul is not defined in C or C++, it shouldn't be used unless you define it yourself in a suitable manner, like:
#define nul '\0'
=============================================================

APTITUDE QUESTIONS

2008-08-02T18:46:00.001-07:00

Aptitude Questions
=================================================================
1.If 2x-y=4 then 6x-3y=?(a)15(b)12(c)18(d)10
Ans. (b)

2.If x=y=2z and xyz=256 then what is the value of x?
(a)12(b)8(c)16(d)6
Ans. (b)

3. (1/10)18 - (1/10)20 = ?(a) 99/1020(b) 99/10(c) 0.9(d) none of these
Ans. (a)

4.Pipe A can fill in 20 minutes and Pipe B in 30 minsand Pipe C canempty the same in 40 mins.If all of them worktogether, find the timetaken to fill the tank(a) 17 1/7 mins(b) 20 mins(c) 8 mins(d) none of these
Ans. (a)

5. Thirty men take 20 days to complete a job working 9hours a day.Howmany hour a day should 40 men work to complete thejob?(a) 8 hrs(b) 7 1/2 hrs(c) 7 hrs(d) 9 hrs
Ans. (b)

6. Find the smallest number in a GP whose sum is 38and product 1728(a) 12(b) 20(c) 8(d) none of these
Ans. (c)

7. A boat travels 20 kms upstream in 6 hrs and 18 kmsdownstream in 4hrs.Find the speed of the boat in still water and thespeed of thewater current?(a) 1/2 kmph(b) 7/12 kmph(c) 5 kmph(d) none of these
Ans. (b)

8. A goat is tied to one corner of a square plot ofside 12m by a rope7m long.Find the area it can graze?(a) 38.5 sq.m(b) 155 sq.m(c) 144 sq.m(d) 19.25 sq.m
Ans. (a)

9. Mr. Shah decided to walk down the escalator of atube station. Hefound that if he walks down 26 steps, he requires 30seconds toreach the bottom. However, if he steps down 34 stairshe would onlyrequire 18 seconds to get to the bottom. If the timeis measured fromthe moment the top step begins to descend to thetime he steps offthe last step at the bottom, find out the height ofthe stair way insteps?
Ans.46 steps.

10. The average age of 10 members of a committee isthe same as it was4 years ago, because an old member has been replacedby a youngmember. Find how much younger is the new member ?
Ans.40 years.

11. Three containers A, B and C have volumes a, b, andc respectively;and container A is full of water while the other twoare empty. Iffrom container A water is poured into container Bwhich becomes 1/3full, and into container C which becomes 1/2 full, howmuch water isleft in container A?

12. ABCE is an isosceles trapezoid and ACDE is arectangle. AB = 10and EC = 20. What is the length of AE?
Ans. AE = 10.

13. In the given figure, PA and PB are tangents to thecircle at A andB respectively and the chord BC is parallel totangent PA. If AC = 6cm, and length of the tangent AP is 9 cm, then whatis the length ofthe chord BC?
Ans. BC = 4 cm.

15 Three cards are drawn at random from an ordinarypack of cards.Find the probability that they will consist of a king,a queen and an ace.
Ans. 64/2210.

16. A number of cats got together and decided to killbetween them999919 mice. Every cat killed an equal number ofmice. Each catkilled more mice than there were cats. How many catsdo you thinkthere were ?
Ans. 991.

17. If Log2 x - 5 Log x + 6 = 0, then what would thevalue / values ofx be?
Ans. x = e2 or e3.

18. The square of a two digit number is divided byhalf the number.After 36 is added to the quotient, this sum is thendivided by 2.The digits of the resulting number are the same asthose in theoriginal number, but they are in reverse order. Theten's place ofthe original number is equal to twice the differencebetween itsdigits. What is the number?
Ans. 46

19.Can you tender a one rupee note in such a mannerthat there shallbe total 50 coins but none of them would be 2 paisecoins.?
Ans. 45 one paisa coins, 2 five paise coins, 2 tenpaise coins, and 1twenty-five paise coins.

20.A monkey starts climbing up a tree 20ft. tall. Eachhour, it hops3ft. and slips back 2ft. How much time would it takethe monkey toreach the top?
Ans.18 hours.

21. What is the missing number in this series? 8 214 6 11 ? 14 6 18 12
Ans. 9

22. A certain type of mixture is prepared by mixingbrand A at Rs.9 akg. with brand B at Rs.4 a kg. If the mixture is worthRs.7 a kg., howmany kgs. of brand A are needed to make 40kgs. ofthe mixture?
Ans. Brand A needed is 24kgs.

23. A wizard named Nepo says "I am only three times myson's age. Myfather is 40 years more than twice my age. Togetherthe three of usare a mere 1240 years old." How old is Nepo?Ans. 360 years old.

24. One dog tells the other that there are two dogs infront of me.The other one also shouts that he too had two behindhim. How many arethey?
Ans. Three.

25. A man ate 100 bananas in five days, each dayeating 6 more thanthe previous day. How many bananas did he eat on thefirst day?
Ans. Eight.

26. If it takes five minutes to boil one egg, how longwill it take toboil four eggs?
Ans. Five minutes.

27. The minute hand of a clock overtakes the hour handat intervals of64 minutes of correct time. How much a day does theclock gain or lose?
Ans. 32 8/11 minutes.

28. Solve for x and y: 1/x - 1/y = 1/3, 1/x2 + 1/y2= 5/9.Ans. x = 3/2 or -3 and y = 3 or -3/2.
29. Daal is now being sold at Rs. 20 a kg. During lastmonth its ratewas Rs. 16 per kg. By how much percent should a familyreduce itsconsumption so as to keep the expenditure fixed?
Ans. 20 %.

30. Find the least value of 3x + 4y if x2y3 = 6.Ans. 10.31. Can you find out what day of the week was January12, 1979?
Ans. Friday.

32. A garrison of 3300 men has provisions for 32 days,when given at arate of 850 grams per head. At the end of 7 days areinforcementarrives and it was found that now the provisions willlast 8 daysless, when given at the rate of 825 grams per head.How, many more mencan it feed?
Ans. 1700 men.

33. From 5 different green balls, four different blueballs and threedifferent red balls, how many combinations of ballscan be chosentaking at least one green and one blue ball?
Ans. 3720.

34. Three pipes, A, B, & C are attached to a tank. A &B can fill itin 20 & 30 minutes respectively while C can empty itin 15 minutes.If A, B & C are kept open successively for 1 minuteeach, how soonwill the tank be filled?
Ans. 167 minutes.

35. A person walking 5/6 of his usual rate is 40minutes late. What ishis usual time?
Ans. 3 hours 20 minutes.

36.For a motorist there are three ways going from CityA to City C. Byway of bridge the distance is 20 miles and toll is$0.75. A tunnelbetween the two cities is a distance of 10 miles andtoll is $1.00 forthe vehicle and driver and $0.10 for each passenger. Atwo-lanehighway without toll goes east for 30 miles to city Band then 20miles in a northwest direction to City C.
1. Which is the shortest route from B to C
(a) Directly on toll free highway to City C(b) The bridge(c) The Tunnel(d) The bridge or the tunnel(e) The bridge only if traffic is heavy on the tollfree highway
Ans. (a)
2. The most economical way of going from City A toCity B, in terms oftoll and distance is to use the
(a) tunnel(b) bridge(c) bridge or tunnel(d) toll free highway(e) bridge and highway
Ans. (a)
3. Jim usually drives alone from City C to City Aevery working day.His firm deducts a percentage of employee pay forlateness. Whichfactor would most influence his choice of the bridgeor the tunnel ?
(a) Whether his wife goes with him(b) scenic beauty on the route(c) Traffic conditions on the road, bridge and tunnel(d) saving $0.25 in tolls(e) price of gasoline consumed in covering additional10 miles on thebridge
Ans. (a)
4. In choosing between the use of the bridge and thetunnel the chieffactor(s) would be:I. Traffic and road conditionsII. Number of passengers in the carIII. Location of one's homes in the center oroutskirts of one of thecitiesIV. Desire to save $0.25
(a) I only(b) II only(c) II and III only(d) III and IV only(e) I and II only
Ans. (a)

37.The letters A, B, C, D, E, F and G, not necessarilyin that order,stand for seven consecutive integers from 1 to 10D is 3 less than AB is the middle termF is as much less than B as C is greater than DG is greater than F
1. The fifth integer is(a) A(b) C(c) D(d) E(e) F
Ans. (a)
2. A is as much greater than F as which integer isless than G(a) A(b) B(c) C(d) D(e) E
Ans. (a)
3. If A = 7, the sum of E and G is(a) 8(b) 10(c) 12(d) 14(e) 16
Ans. (a)
4. A - F = ?(a) 1(b) 2(c) 3(d) 4(e) Cannot be determined
Ans. (a)
5. An integer T is as much greater than C as C isgreater than E. Tcan be written as A + E. What is D?(a) 2(b) 3(c) 4(d) 5(e) Cannot be determined
Ans. (a)
6. The greatest possible value of C is how muchgreater than thesmallest possible value of D?(a) 2(b) 3(c) 4(d) 5(e) 6
Ans. (a)

38.1. All G's are H's2. All G's are J's or K's3. All J's and K's are G's4. All L's are K's5. All N's are M's6. No M's are G's
1. If no P's are K's, which of the following must betrue?
(a) All P's are J's(b) No P is a G(c) No P is an H(d) If any P is an H it is a G(e) If any P is a G it is a J
Ans. (a)
2. Which of the following can be logically deducedfrom the conditionsstated?
(a) No M's are H's(b) No M's that are not N's are H's(c) No H's are M's(d) Some M's are H's(e) All M's are H's
Ans. (a)
3. Which of the following is inconsistent with one ormore of theconditions?
(a) All H's are G's(b) All H's that are not G's are M's(c) Some H's are both M's and G's(d) No M's are H's(e) All M's are H's
Ans. (a)
4. The statement "No L's are J's" isI. Logically deducible from the conditions statedII. Consistent with but not deducible from theconditions statedIII. Deducible from the stated conditions togetherwith the additionalstatement "No J's are K's"
(a) I only(b) II only(c) III only(d) II and III only(e) Neither I, II nor III
Ans. (a)

39.In country X, democratic, conservative and justiceparties havefought three civil wars in twenty years. TO restorestability anagreement is reached to rotate the top officesPresident, PrimeMinister and Army Chief among the parties so that eachparty controlsone and only one office at all times. The three topoffice holdersmust each have two deputies, one from each of theother parties. Eachdeputy must choose a staff composed of equally membersof his or herchiefs party and member of the third party.
1. When Justice party holds one of the top offices,which of thefollowing cannot be true
(a) Some of the staff members within that office arejustice party members(b) Some of the staff members within that office aredemocratic partymembers(c) Two of the deputies within the other offices arejustice party members(d) Two of the deputies within the other offices areconservativeparty members(e) Some of the staff members within the other officesare justiceparty members.
Ans. (a)
2. When the democratic party holds presidency, thestaff of the primeminister's deputies are composedI. One-fourth of democratic party membersII. One-half of justice party members and one-fourthof conservativeparty membersIII. One-half of conservative party members andone-fourth of justiceparty members.
(a) I only(b) I and II only(c) II or III but not both(d) I and II or I and III(e) None of these
Ans. (a)
3. Which of the following is allowable under the rulesas stated:
(a) More than half of the staff within a given officebelonging to asingle party(b) Half of the staff within a given office belongingto a single party(c) Any person having a member of the same party ashis or herimmediate superior(d) Half the total number of staff members in allthree officesbelonging to a single party(e) Half the staff members in a given office belongingto partiesdifferent from the party of the top office holder inthat office.
Ans. (a)
4. The office of the Army Chief passes fromConservative to Justiceparty. Which of the following must be fired.
(a) The democratic deputy and all staff membersbelonging to Justice party(b) Justice party deputy and all his or hers staffmembers(c) Justice party deputy and half of his Conservativestaff members inthe chief of staff office(d) The Conservative deputy and all of his or herstaff membersbelonging to Conservative party(e) No deputies and all staff members belonging toconservative parties.
Ans. (a)

40.In recommendations to the board of trustees atuition increase of$500 per year, the president of the university said"There were nostudent demonstrations over the previous increases of$300 last yearand $200 the year before". If the president'sstatement is accuratethen which of the following can be validly inferredfrom theinformation given:I. Most students in previous years felt that theincreases werejustified because of increased operating costs.II. Student apathy was responsible for the failure ofstudents toprotest the previous tuition increases.III. Students are not likely to demonstrate over newtuition increases.
(a) I only(b) II only(c) I or II but not both(d) I, II and III(e) None
Ans. (a)

41. The office staff of XYZ corporation presentlyconsists of threebookeepers--A, B, C and 5 secretaries D, E, F, G, H.The management isplanning to open a new office in another city using 2bookeepers and 3secretaries of the present staff . To do so they planto seperatecertain individuals who don't function well together.The followingguidelines were established to set up the new officeI. Bookeepers A and C are constantly finding faultwith one anotherand should not be sent together to the new office as ateamII. C and E function well alone but not as a team ,they should beseperatedIII. D and G have not been on speaking terms andshouldn't go togetherIV Since D and F have been competing for promotionthey shouldn't be ateam1.If A is to be moved as one of the bookeepers,whichof the followingcannot be a possible working unit.
A.ABDEHB.ABDGHC.ABEFHD.ABEGH
Ans.B
2.If C and F are moved to the new office,how manycombinations arepossible
A.1B.2C.3D.4
Ans.A
3.If C is sent to the new office,which member of thestaff cannot gowith C
A.BB.DC.FD.G
Ans.B
4.Under the guidelines developed,which of thefollowing must go to thenew office
A.BB.DC.ED.G
Ans.A
5.If D goes to the new office,which of the followingis/are true
I.C cannot goII.A cannot goIII.H must also go
A.I onlyB.II onlyC.I and II onlyD.I and III only
Ans.D

42.After months of talent searching for anadministrative assistant tothe president of the college the field of applicantshas been narroweddown to 5--A, B, C, D, E .It was announced that thefinalist would bechosen after a series of all-day group personalinterviews wereheld.The examining committee agreed upon the followingprocedure
I.The interviews will be held once a weekII.3 candidates will appear at any all-day interviewsessionIII.Each candidate will appear at least onceIV.If it becomes necessary to call applicants foradditonalinterviews, no more 1 such applicant should be askedto appear thenext weekV.Because of a detail in the written applications,itwas agreed thatwhenever candidate B appears, A should also bepresent.VI.Because of travel difficulties it was agreed that Cwill appear foronly 1 interview.1.At the first interview the following candidatesappear A,B,D.Whichof the follwing combinations can be called for theinterview to beheld next week.
A.BCDB.CDEC.ABED.ABC
Ans.B
2.Which of the following is a possible sequence ofcombinations forinterviews in 2 successive weeks
A.ABC;BDEB.ABD;ABEC.ADE;ABCD.BDE;ACD
Ans.C
3.If A ,B and D appear for the interview and D iscalled foradditional interview the following week,which 2candidates may beasked to appear with D?
I. AII BIII.CIV.EA.I and IIB.I and III onlyC.II and III onlyD.III and IV only
Ans.D
4.Which of the following correctly state(s) theprocedure followed bythe search committee
I.After the second interview all applicants haveappeared at least onceII.The committee sees each applicant a second timeIII.If a third session,it is possible for allapplicants to appear atleast twice
A.I onlyB.II onlyC.III onlyD.Both I and II
Ans.A

43. A certain city is served by subway lines A,B and Cand numbers 1 2and 3When it snows , morning service on B is delayedWhen it rains or snows , service on A, 2 and 3 aredelayed both in themorning and afternoonWhen temp. falls below 30 degrees farenheit afternoonservice iscancelled in either the A line or the 3 line,but not both.When the temperature rises over 90 degrees farenheit,the afternoonservice is cancelled in either the line C or the3 line but not both.When the service on the A line is delayed orcancelled, service on theC line which connects the A line, is delayed.When service on the 3 line is cancelled, service onthe B line whichconnects the 3 line is delayed.
Q1. On Jan 10th, with the temperature at 15 degreefarenheit, itsnows all day. On how many lines will service beaffected, including both morning and afternoon.(A) 2(B) 3(C) 4(D) 5Ans. D
Q2. On Aug 15th with the temperature at 97 degreesfarenheit it beginsto rain at 1 PM. What is the minimum numberof lines on which service will be affected?(A) 2(B) 3(C) 4(D) 5Ans. C
Q3. On which of the following occasions would servicebe on thegreatest number of lines disrupted.(A) A snowy afternoon with the temperature at 45degree farenheit(B) A snowy morning with the temperature at 45 degreefarenheit(C) A rainy afternoon with the temperature at 45degree farenheit(D) A rainy afternoon with the temperature at 95degree farenheitAns. B

44. In a certain society, there are two marriagegroups, red andbrown. No marriage is permitted within a group. Onmarriage, malesbecome part of their wives groups; women remain intheir own group.Children belong to the same group as their parents.Widowers anddivorced males revert to the group of their birth.Marriage to morethan one person at the same time and marriage to adirect descendantare forbiddenQ1. A brown female could have hadI. A grandfather born RedII. A grandmother born RedIII Two grandfathers born Brown(A) I only(B) III only(C) I, II and III(D) I and II onlyAns. D
Q2. A male born into the brown group may have(A) An uncle in either group(B) A brown daughter(C) A brown son(D) A son-in-law born into red groupAns. A
Q3. Which of the following is not permitted under therules as stated.(A) A brown male marrying his father's sister(B) A red female marrying her mother's brother(C) A widower marrying his wife's sister(D) A widow marrying her divorced daughter'sex-husbandAns. B
Q4. If widowers and divorced males retained theirgroup they had uponmarrying which of the following would be permissible (Assume that noprevious marriage occurred)(A) A woman marrying her dead sister's husband(B) A woman marrying her divorced daughter'sex-husband(C) A widower marrying his brother's daughter(D) A woman marrying her mother's brother who is awidower.Ans. D
Q5. I. All G's are H'sII. All G's are J's or K'sIII All J's and K's are G'sIV All L's are K'sV All N's are M'sVI No M's are G's

45. There are six steps that lead from the first tothe second floor.No two people can be on the same stepMr. A is two steps below Mr. CMr. B is a step next to Mr. DOnly one step is vacant ( No one standing on that step)Denote the first step by step 1 and second step bystep 2 etc.1. If Mr. A is on the first step, Which of thefollowing is true?(a) Mr. B is on the second step(b) Mr. C is on the fourth step.(c) A person Mr. E, could be on the third step(d) Mr. D is on higher step than Mr. C.Ans: (d)2. If Mr. E was on the third step & Mr. B was on ahigher step thanMr. E which step must be vacant(a) step 1(b) step 2(c) step 4(d) step 5(e) step 6Ans: (a)

3. If Mr. B was on step 1, which step could A be on?(a) 2&e only(b) 3&5 only(c) 3&4 only(d) 4&5 only(e) 2&4 onlyAns: (c)
4. If there were two steps between the step that A wasstanding andthe step that B was standing on, and A was on a higherstep than D , Amust be on step(a) 2(b) 3(c) 4(d) 5(e) 6Ans: (c)
5. Which of the following is false
i. B&D can be both on odd-numbered steps in oneconfigurationii. In a particular configuration A and C must eitherboth an oddnumbered steps or both an even-numbered stepsiii. A person E can be on a step next to the vacantstep.(a) i only(b) ii only(c) iii only(d) both i and iiiAns: (c)

46. Six swimmers A, B, C, D, E, F compete in a race.The outcome is asfollows.i. B does not win.ii. Only two swimmers separate E & Diii. A is behind D & Eiv. B is ahead of E , with one swimmer interveningv. F is a head of D1. Who stood fifth in the race ?(a) A(b) B(c) C(d) D(e) EAns: (e)2. How many swimmers seperate A and F ?(a) 1(b) 2(c) 3(d) 4(e) cannot be determinedAns: (d)3. The swimmer between C & E is(a) none(b) F(c) D(d) B(e) AAns: (a)
4. If the end of the race, swimmer D is disqualifiedby the Judgesthen swimmer B finishes in which place(a) 1(b) 2(c) 3(d) 4(e) 5Ans: (b)

47. Five houses lettered A,B,C,D, & E are built in arow next to eachother. The houses are lined up in the order A,B,C,D, &E. Each of thefive houses has a colored chimney. The roof andchimney of eachhousemust be painted as follows.i. The roof must be painted either green,red ,oryellow.ii. The chimney must be painted either white, black,or red.iii. No house may have the same color chimney as thecolor of roof.iv. No house may use any of the same colors that theevery next houseuses.v. House E has a green roof.vi. House B has a red roof and a black chimney
1. Which of the following is true ?(a) At least two houses have black chimney.(b) At least two houses have red roofs.(c) At least two houses have white chimneys(d) At least two houses have green roofs(e) At least two houses have yellow roofsAns: (c)
2. Which must be false ?(a) House A has a yellow roof(b) House A & C have different color chimney(c) House D has a black chimney(d) House E has a white chimney(e) House B&D have the same color roof.Ans: (b)
3. If house C has a yellow roof. Which must be true.(a) House E has a white chimney(b) House E has a black chimney(c) House E has a red chimney(d) House D has a red chimney(e) House C has a black chimneyAns: (a)
4. Which possible combinations of roof & chimney canhouseI. A red roof 7 a black chimneyII. A yellow roof & a red chimneyIII. A yellow roof & a black chimney
(a) I only(b) II only(c) III only(d) I & II only(e) I&II&IIIAns: (e)

48. Find x+2y(i). x+y=10(ii). 2x+4y=20

49. Is angle BAC is a right angle(i) AB=2BC(2) BC=1.5AC

50. Is x greater than y(i) x=2k(ii) k=2y
===================================================================

APTITUDE QUESTIONS

2008-08-02T02:11:00.000-07:00

=============================================

APTITUDE QUESTIONS WITH ANSWERS AND EXPLANATION

100 Puzzles with Answers and Explanation
http://www.eurl.in/u/Puzzles_with_Answers

Collection of Aptitude Questions with Answers Part -1
http://www.eurl.in/u/Aptitude_Part_1

Collection of Aptitude Questions with Answers Part - 2
http://www.eurl.in/u/Aptitude_Part_2

Collection of Aptitude Questions with Answers Part - 3
http://www.eurl.in/u/Aptitude_Part_3

Collection of Numerical Ability Questions Asked in Different Companies
http://www.eurl.in/u/Numerical-Ability

Collection of Reasoning Questions Asked in Different Companies
http://www.eurl.in/u/Reasoning

Collection of Verbal Questions Asked in Different Companies
http://www.eurl.in/u/Verbal

=============================================================

REQUIREMENT ON RIKTAM TECHNOLOGIES

2008-08-01T22:33:00.000-07:00

send u r resume to riktam@gmail.com

1. Test would consist of 2 rounds of aptitude test (Analytical / Logical / Puzzles). Interviewer may ask basic C/C++/Java questions, but very less.
2. There is no fees to attend the test
3. Your friends can also attend the aptitude test for selection. They should email their resume to riktam@gamil.com

if your cv mathes our requirement u will get immedaite mails stating the written test details.

HR manager....( Riktam Technologies ).. Prasad K9848558696

BPO and TECH SUPPORT

2008-08-01T21:54:00.000-07:00

Hyderabad Jumbo Job Fair - 2008 On August 02, 03 At Hyderabad Walk-in Date: August 02 & 03 Time: 10.00am - 6.00 pm Venue: Hitex Exhibition Centre, Madhapur, Hitech City Hyderabad-500032..

BPO and TECH SUPPORT ONLY

ORACLE INTRODUCTIN

2008-07-28T22:03:00.001-07:00

ORACLE INTRODUCTION

Before database was invented, File system is used to store data in computers. Because of the disadvantages like data redundancy, atomicity, consistency and security in file system, Database Management System (DBMS) was introduced. Collection of related data is called as Database. Collection of inter-related data and a set of programs to access those data is called as Database Management Sysytem (DBMS). Oracle is a Database Management System Software.

After DBMS came in to practice, several models are proposed in DBMS with different types of storage methods for the data within the database. Among them Relational Database Model is the widely used data model. Relational Database model was proposed by E.F.Codd. In Relational Database Model, Data is stored in the form of relations or tables. A table is comprised of rows and columns. A row is also called as a Tuple and a column is called as an Attribute. Relational Database Model was developed based on the concepts of Relational Algebra and Relational Calculus.

Another development in DBMS is introducing Object Oriented Programming (OOP) concepts in DBMS. By this concept, new database models called Object Oriented Database Models were introduced. A new model called Object Relational Database Model was developed by incorporating OOPS concepts in relational database model.

Upto Version 7.3, Oracle is Relational Database Management System Software. But from version 8.0 onwards, Oracle is Object Relational Database Management System Software (ORDBMS). After version 8.0, Oracle version 8i was released. In this version, Oracle has provided the features by using which we can interact with the Internet. After Version 8i, another version 9i was released. Most of the new features in 9i are towards the Architecture and DBA related and some new features are added towards application development side.

Oracle Provides several tools to access the database. Among them the tools that are covered in this book are SQL, PL/SQL, FORMS and REPORTS. Forms and Reports are combinedly called as Developer/2000 or simply D2k. New version of D2k is 6i and is released along with database release 8i.

SQL Introduction and Data Types

2008-07-28T22:03:00.000-07:00

SQL ( Structured Query Language )

SQL is a common language to interact with the database in any RDBMS software.
IBM Corporation first developed SQL. They have developed SQL as a part of
System-R project. After recognizing the ease and flexibility of this part, they have developed it as a separate language and named it as SEQUEL (Structured English Query Language).
Oracle Corporation first commercially released SEQUEL by changing its name to SQL.

Features Of SQL

SQL is a 4th generation (4 GL) or Non procedural language.
Every statement in SQL must end with a semicolon.
SQL is not a case sensitive language.
In SQL at a time a single SQL statement is stored in buffer. To re-execute the statement stored in buffer we have to use ‘/’.
In SQL a single SQL statement can be broken into more than one line without using any continuation character.

Datatypes In SQL

To represent data in database we have to create tables. During the creation of a table we have to specify the columns to be included in the table and the type of values that are going to be stored in those columns. The data types available in SQL are as follows.

Number : This data type is used to store numeric values in a column. For number datatype maximum allowed digits is specified in parantheses. For example, if we specify the datatype as number(5) then we can store –99,999 to +99,999 in that column.

Char : This data type is used to store fixed length character data. Max. Allowed size for Char is 1000 bytes.

Varchar2 : This data type is used to store variable length character data. Max. Allowed Size for varchar2 is 2000 bytes. Difference between char and varchar2 is if we specify the datattype as char(10) and stored the string “Oracle” then even the given string contains 6 characters it occupies 10 bytes in memory by placing blank spaces towards the end. if we specify the datatype as varchar2(10) then it occupies only 6 bytes even max. size is 10.
Long : This data type is used to store variable length character data up to 2 GB.

Raw : This data type is used to store binary data up to 1000 bytes. Binary data means pictures and sounds.

Long Raw : This data type is used to store binary data up to 2 GB.

Date :
This data type is used to store dates in a column. Default Date format in Oracle is DD – MON – YY (Ex: 12-AUG-02).

CONSTRAINTS

2008-07-28T22:01:00.000-07:00

Constraints In O R A C L E

Constraints are nothing but conditions given on a column to maintain data integrity within the database. A constraint clause can constrain a single column or group of columns in a table. The point of these constraints is to get oracle to do most of the work in maintaining the integrity of your database. The more constraints you add to a table definition, the less work you have to do in applications to maintain the data. On the other hand, the more constraints there are in a table, the longer it takes to update the data.
There are two ways to specify constraints. As part of the column definition (a column constraint) or at the end of the create table statement (a table constraint). Clauses that constrain several columns must be table constraints. Constraints in Oracle and their purpose are as follows.

1. Not Null : This constraint is used to restrict the user from inserting null values into a column.

2. Unique : This constraint is used to restrict the user from inserting duplicate values to a column.

3. Primary Key : This constraint is used to restrict the user from inserting duplicate values or null values into a column. Another purpose of Primary Key is a column to be referenced by a Foreign Key must be a Primary Key. Oracle allows only one primary key for the table.

4. Foreign Key : This constraint is used to create relationship between two tables. We create relationship between two tables by giving reference to a primary key column of one table to a column in second table, which is called foreign key column. Foreign Key constraint allows either null values or the values present in a Primary Key column to which it refers.
When a relationship is created between two tables then the table that contains Primary Key is called as Master or parent Table and the table that contains Foreign Key is called as Detailed or child Table and the Primary Key and Foreign Key relationship is called as Master Detail Relationship.
A Foreign Key constraint is also called as Referential Integrity constraint. If a Foreign Key refers to a Primary Key present in the same table then that foreign key is called as Self Referential Key.
The clause on delete cascade added to the references clause tells oracle to delete the dependent row when you delete the corresponding row in the parent table. This action automatically maintaince referential integrity.

5. Check : Many columns must have values that are within a certain range or that satisfy certain conditions. A column level check constraint can’t refer to values in other rows. It can’t use the pseudo columns sysdate, uid, user, userenv, currval, nextval, level or rownum. You can use the table constraint form to refer to multiple columns in a check constraint.

CREATING TABLE, TABLE LEVEL CONSTRAINT

2008-07-28T21:57:00.000-07:00

Creating A Table

To create your own table in oracle, “Create Table” command is used. Create Table command has the following syntax.

Create Table TabName
(col1 datatype [constraint constname] [constspec],
col2 datatype [constraint constname] [constspec],
.
.
.
coln datatype [constraint constname] [constspec]
);

Here Col1, Col2, … , Coln are the column names to be included in the table. Datatype is any valid datatype of SQL. Constraint is keyword. Constname is the name for the constraint specifying on that column. Constspec is the constraint to be given on that column. Specifying constraint name is optional. When you want to give a name to the constraint, then only you have to use the key word constraint. If we don’t specify a name to the constraint, then oracle will automatically give a name to that constraint. You can get the constraint names from data dictionary view user_constraints. Most of oracle’s generated constraint names are of the form SYS_C######. For example, SYS_C000145. Even specifying a constraint on a column is also optional.
These are the basic elements of this command.
The words create table
The name of the table
An opening paranthesis
Column definitions
A closing paranthesis
A SQL terminator

The individual column definitions are separated by commas. There is no comma after the last column definition. The table and column names must start with a letter of the alphabet, but may include letters, numbers, and underscores. Names may be 1 to 30 characters in length, must be unique within the table, and can not be an oracle reserved word.
Example :

Create Table STUDENT
(Sno Number(5) constraint SNO_PK Primary Key,
Sname Varchar2(15) NOT NULL,
Address Varchar2(20) NOT NULL,
Course Varchar2(1) NOT NULL,
Fee Number(7,2)
);

Example :

Create table Marks
(Sno Number(5) constraint SNO_FK references Student(Sno),
M1 Number(3) constraint M1_CHK Check(M1<=100),
M2 Number(3) constraint M2_CHK Check(M2<=100),
M3 Number(3) constraint Mn_CHK Check(M3<=100),
Total Number(5),
Aveg Number(7,2)
);

Table Level Constraints

Constraints can be classified into column level constraints and table level constraints. If constraint was specified immediately after column definition then that constraint is called as column level constraint and if a constraint was specified at the end of the create table statement, then that constraint is called as table level constraint. Table level constraints are generally used when constraint has to be given on more than one column. An example of table level constraint is as follows. In this example, primary key constraint is given on two columns invno and itemno.

Create table Invoice
(Invno number(5) ,
Cname varchar2(20) not null,
Itemno number(5),
Qty number(3) not null,
Uprice number(7,2) not null,
Constraint invitem_pk primary key(invno,itemno)
);

Creating a Table From Another Table

To create a new table from an existing table, the following syntax is used.

Syntax : create table tablename as select statement on the source table;
Example : the following example creates emp1 by selecting the structure and rows of emp table.

Create table Emp1 as select * from emp;

Displaying Structure of The Table

To display the structure of a table i.e. the column names and their data types, describe command is used.

Syntax : DESC TableName

INSERTING DATA INTO TABLE

2008-07-28T21:52:00.000-07:00

Using Merge Command
As of oracle9i, you can use the merge command to perform inserts and updates in to a single table in a single command. Based on the conditions you specify, oracle will take the source data i.e. a table, view, or a query and update existing values if the conditions are met. If the conditions are not met, the row will be inserted.

For example, create a duplicate table of emp with name emp1 and make some changes to emp1. now we can merge the changes made to emp1 in to emp by using merge command as follows.

Merge Into Emp EUsing
(Select Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno From Emp1) E2On(E.Empno=E2.Empno)
When Matched Then
Update Set E.Sal=E2.Sal,E.Comm=E2.CommWhen Not Matched Then Insert values(E2.Empno,E2.Ename,E2.Job,E2.Mgr,E2.Hiredate,E2.Sal,E2.Comm,E2.Deptno)

Inserting Data Into A Table

After creating the table we can insert data into that table by using insert statement. Insert statement has following syntaxes.

Syntax 1: Insert into tabname values( val1, val2, val3, . . . , val n );
Example: Insert into Student values( 1, ‘SAI’, ‘Apet’, ‘ORACLE’, 2000);

Syntax 2: Insert into tabname values( &col1, &col2, &col3, . . . , &co n );
Example : Insert into Student values( &Sno, ‘&Sname’, ‘&Address’, &Fee );

Second syntax will be easier for user when he/she has to insert serveral rows in to the table. By using above two syntaxes we must provide values for all columns of the table even columns don’t have ‘Not Null’ constraint. To insert data into a table by excluding some columns, the following syntaxes are used.

Syntax 3: Insert into tablename(Col1, Col2, Col3,. . .,Col n) values ( Val1, Val2, Val3, . . . ,Val n);
Example: Insert into Marks ( Sno, M1, M2, M3 ) values ( 1, 55, 67, 82 );

Syntax 4: Insert into tablename (Col1, Col2, Col3, ..., Col n) values (‘&Col1’, ‘&Col2’, ‘&Col3’, ... , ‘&Col n’);
Example: Insert into Marks (Sno, M1, M2, M3) values (&sno,&m1,&m2,&m3);

Insert With Select

You also can insert information that has been selected from a table.

Syntax : Insert into Tablename select col1, col2, col3, … , col n from tablename;

Using Append Hint

Oracle uses an optimizer to determine the most efficient way to perform each SQL command. For insert statements, oracle tries to insert each new record into an existing block of data already allocated to the table. This execution plan optimizes the use of space required to store the data. However, it may not provide adequate performance for an insert with a select command that inserts multiple rows.you can override the execution plan by using the append hint to improve the performance of large inserts.the append hint will tell the database to find the last block into which the table’s data has ever been inserted. The new records will be inserted starting in the next block following the last previously used block. Since the data is being written into new blocks of the table, there is much less space management work for the database to do during the insert. Therefore, the insert may complete faster when the append hint is used.

You specify the append hint within the insert command. A hint looks like a comment. It starts with /* and ends with */. The only difference is that the starting set of characters includes a + before the name of the hint.

Syntax : insert /* + APPEND */ in to tablename select statement.

Since the new records will not attempt to reuse available space that the table has already used, the space requirements for the table may increase. In general you should use the append hint only when inserting large volumes of data in to tables with little reusable space. The point at which appended records will be inserted is called the table’s high-water mark.

Multitable Inserts

As of oracle 9i, you can perform multiple inserts in a single command. You can perform all of the inserts unconditionally or you can spcify conditions using a when clause to tell oracle how to manage the multiple inserts. If you specify all, then all of the when clauses will be evaluated. Specifying first tells oracle o skip subsequent when clauses after it finds one that is true for the row being evaluated. You can also use an else clause to tell oracle what to do if none of the when clauses evaluates to true.

Syntax : Insert [ALL] / [FIRST]
[When condition]
Into tablename values(val1, val2, …, val n)
[When condition]
Into tablename values(val1, val2, …, val n)
[When condition]
Into tablename values(val1, val2, …, val n)
.
.
.
else
Into tablename values(val1, val2, …, val n)
Select statement;

UPDATE COMMAND

2008-07-28T21:51:00.000-07:00

Updating A Table

To make modifications to the data present in a table, update statement is used. Update statement has the following syntax.

Syntax:

Update tablename set Col1=Val1, Col2=Val2, ... ,Col n=Val n [ Where Condition ];

Example 1:
The following update statement changes the name of student with id 3 to ‘NAVEEN’.
Update Student set Sname=’Naveen’ where Sno=3;

Example 2: The following example updates the Marks table by calculating total and average.
Update Marks set Total= (M1 + M2 + M3 ), Aveg = ( M1 + M2 + M3 ) / 3;

If you specify the where clause, then the rows that satisfy the given codition in where clause will only be updated. If you omit the where clause then all rows of the table will be updated.

RENAME, DELETE and TRUNCATE

2008-07-28T21:49:00.000-07:00

Renaming a Table

Name of a table can be chaged by using Rename command. Rename command has the following syntax.

Syntax: Rename Oldname to Newname;
Example: The following example changes the name of student table to stu.
Rename Student to Stu;

Deleting a Table

A table can be deleted by using Drop Table command. Drop Table command has the following syntax.

Syntax: Drop Table Tablename;
Example: The following example deletes marks table.
Drop Table Marks;

TRUNCATE

In oracle, the truncate command lets you remove all the rows in the table and reclaim the space for other uses without removing the table definition from the database.

Syntax: Truncate Table Tablename;

Example: The following example deletes all rows in marks table, keeping marks table definition within the database.

Truncate Table Marks;

ALTER COMMAND

2008-07-28T21:42:00.000-07:00

Altering A Table Structure

After creating the table we can change the data type of a column or change the size of a column or add a new constraint or delete a constraint or add a new column or delete a column by using Alter Table command. Syntaxes of Alter table command are as follows.

To change the datatype of a column or to change the size of a column or to add not null constraint to a column, the following syntax is used.

Syntax:

Alter table tablename modify
(Col1 Datatype Not Null,
Col2 Datatype Not Null,
………..
Col n Datatype Not Null
);

Example : The following example increases the size of Sname column in Student table to 20 and adds not null constraint to fee column.
Alter table Student modify (Sname varchar2(20), Fee number(7,2) Not Null );

Restrictions:

Changing the datatype of a column is not possible if that column contains data.
Adding not null constraint is not possible if that column contains NULL values.
Decreasing the Size of a column is not possible if that column contains data.

To add a new column to the table, the following syntax is used.

Syntax:
Alter table tabname ADD
(Col1 Datatype Constraint Consname Consspec,
Col2 Datatype Constraint Consname Consspec,
.
.
.
Col n Datatype Constraint Consname Consspec);

Example : The following example adds a new column with name Fname (Father’s name) to Student table.

Alter table Student ADD (Fname Varchar2(10));

Restrictions :

a) Adding a column with primary key or Not Null constraint is not possible if the table contains data.

To add a constraint to a table, the following syntax is used.

Syntax:
Alter table tabname ADD Constraint consname consspec( Colname );

Example 1: The following example adds Primary Key constraint to Sno Column of Student table.
Alter table Student ADD constraint Sno_PK Primary Key (Sno);

Example 2: The following example adds Foreign Key constraint to Sno Column of Marks table.
Alter table Marks ADD constraint Sno_FK Foreign Key (Sno) references Student(Sno);

Restrictions:

Adding any constraint to a column is not possible if that column contains data that violates that constraint.
Adding Not Null constraint is not possible by using this syntax.

To disable and enable constraints, the following syntax is used.
Syntax:
Alter table tabname Disable/Enable constraint consname;

Example: The following example disables the foreign key constraint on Sno column of Marks table.
Alter table Marks Disable constraint Sno_FK;

Restrictions:

Disabling a primary key is not possible if that Primary key is refered by a foreign key. To delete primary key by deleting the foreign key constraint automatically, we have to use cascade constraints clause at the end of the alter table command.
Enabling a constraint is not possible if that column contains data that violates the constraint.

To delete a constraint or a column, the following syntax is used.

Syntax:
Alter table tabname Drop Column/Constraint Col.Name/ConsName;

Example 1: The following example deletes Foreign Key constraint on Sno column of Marks table.
Alter table Marks Drop constraint Sno_FK;

Example 2: The following example deletes Fee column from Student table.
Alter table Student Drop Column Fee;

Example 2: The following example deletes Fee, Fname columns from Student table in a sinle alter table statement.
Alter table Student Drop (Fee, Fname);

Restrictions:

Deleting a Primary key constraint or primary key column is not possible if that Primary key is refered by a Foreign Key. To delete primary key column by deleting the foreign key constraint automatically, we have to use cascade constraints clause at the end of the alter table command.

We can make a column as unused. Making a column unused does not release the space previously used by the column until you drop the unused columns.

Syntax:
Alter table tabname set unused column ColumnName;

Example : The following example sets total column of marks table unused.
Alter table Marks set unused column total;

To delete all unused columns of a table, drop unused columns clause is used as follows.

Example : Alter table Marks drop unused columns;

OPERATORS

2008-07-28T21:40:00.000-07:00

O p e r a t o r s

The following operators can be used in the where clause to simplify the conditions.

Between … And
In
Like
Is Null

1) Between … And:

This operator is used to test whether the given value is present in given range of values.

Examples:
Find all employees whose Salary is more than 2000 and less than 4000.
Select * from EMP where Sal between 2000 and 4000;
Find all employees joined after 12 th june 1990, and before 31st December 1990.
Select * from EMP where Hiredate between ‘12-Jun-90’ and ‘31-Dec-90’;
Find all employees who are not joined after 12th June 1990, and before 31st December 1990.
Select * from EMP where Hiredate NOT Between ‘12-Jun-90’ and ‘31-Dec-90’;

2) In :

This operator is used to test whether the given value is present in a given list of values.

Examples:
Find all employees whose salary is either 1200 or 1500 or 1750.
Select * from EMP where Sal IN (1200, 1500, 1750);
Find all employees who are working either as a Manager or a Clerk.
Select * from EMP where Job IN (‘MANAGER’, ‘CLERK’);

3) Like:

One of the most powerful features of SQL is a marvelous pattern-matching operator called LIKE, which is able to search through the rows of a database column for values that look like a pattern you describe. It uses two special characters to denote which kind of matching to do : a percent sign, called wildcard, and an underscore, called position marker.

Examples:
Find all employees whose Name starts with ‘S’.
Select * from EMP where Ename LIKE ‘S%’;
Find all employees whose name ends with ‘E’.
Select * from EMP where Ename LIKE ‘%E’;
Find all employees whose name contains the letter ‘A’.
Select * from EMP where Ename LIKE ‘%A%’ ;
Find all employees whose name contains ‘L’ as second letter.
Select * from EMP where Ename LIKE ‘_L%’ ;
Find all employees whose name doesn’t contain the letter ‘A’.
Select * from EMP where Ename NOT LIKE ‘%A%’;
Find all employees whose name contains five letters.
Select * from EMP where Ename like ‘_ _ _ _ _’; (Underscores without space between them)

4) Is Null:

This operator is used to test for a null value in the given column.

Examples:
Find all employees who are not drawing any commission.
Select * from EMP where Comm IS NULL;
Find all employees who are drawing Commission.
Select * from EMP where Comm IS NOT NULL;

BUILT IN FUNCTIONS IN ORACLE

2008-07-28T21:35:00.001-07:00

Built In Functions

As in other programming languages, SQL also provides built in functions. Built in functions in SQL are classified into following categories.

Single row functions.
List Functions.
Group Functions.

1) Single Row Functions: The Built in functions of ORACLE that work on each row of the table separately are called as Single Row Functions. Depending on the data type of the values on which Single Row Functions work, they are classified into following five categories.

Character Functions.
Numeric Functions.
Conversion Functions
Date Functions.
Special Functions.

Character Functions :

Built in Single Row Functions of SQL that work on character type of data are called as Character functions. Character functions in SQL are as follows.

UPPER(String or ColName) : This function converts the given string or values of given column in any case to upper case.

LOWER(String or ColName) : This function converts the given string or values of given column in any case to lower case.

INITCAP(String or ColName) : This function converts the given string or values of given column in any case to title case i.e. converts first character in every word to upper case and remaining characters in the word to lower case.

In general above three functions are used to perform case-insensitive queries against database. For example the following query retrieves all managers from emp table with case insensitivity.

Example: Select * from EMP where UPPER(JOB) = ‘MANAGER’;

LPAD(String or Colname, Len, Char ) : This function pads the given char with in the blank spaces to the left of given string or values of given column by assuming the maximum length of string or column as Len.

Following example pads “*” to the left of employee name. In this example, colname is ename, len is given as 10 and char as “*”. Now if ename is “Scott” then ename is five charcaters and we have given the len as 10. Hence it pads five “*” to the left of ename “scott” and result is “*****Scott”.

Example: Select Lpad(Ename,10,”*”) from Emp;

RPAD(String, Len, Char ) : This function pads the given char with in the blank spaces to the Right of given string or values of given column by assuming the maximum length of string or column as Len.

Following example pads “*” to the right of employee name. In this example, colname is ename, len is given as 10 and char as “*”. Now if ename is “Scott” then ename has five charcaters and we have given the len as 10. Hence it pads five “*” to the right of ename “scott” and result is “Scott*****”.

Example: Select Lpad(Ename,10,”*”) from Emp;

LTRIM(String or ColName,[Char]) : Removes any leading blank spaces i.e. blank spaces on left of given string or values of given column if char is omitted. Otherwise removes the given char from left of given string or values of given column.

RTRIM(String or ColName,[Char]) : Removes any trailing blank spaces i.e. blank spaces on right of given string or values of given column if char is omitted. Otherwise removes the given char from right of given string or values of given column.

LENGTH(String or ColName) : Returns the number of characters in the given string or values of given column seperately.

SUBSTR(String or ColName, M, [N] ) : This function extracts ‘ N ’ number of characters from the given string or values of given column starting from ‘ M ’. If ‘ N ‘ is omitted then all charcters starting from ‘ M ‘ will be retrieved.

Example: Select Substr(‘ORACLE CORPORATION’, 8, 4 ) from Dual;

This example returns “CORP” by extracting 4 characters from the given string “ORACLE CORPORATION” starting from the 8th character. If same query is written without giving value 4, then result will be “CORPORATION”.

INSTR(String or ColName, Substring, M, N ) : Finds Nth occurrence of the given substring with in the given string or values of given column starting from ‘M’.

Example: Select INSTR( ‘ORACLE CORPORATION’, ‘OR’, 1, 2 ) from Dual;

This example returns value 9. Because given substring “OR” is started second time at 9th character starting from first(1) character. If the same query is written as follows

Example: Select INSTR( ‘ORACLE CORPORATION’, ‘OR’, 9, 2 ) from Dual;

Then return value is 12. Because given substring “OR” is started second time at 12th character starting from 9th character. If the given substring is not found in the specified ocuurrece, then this function will return the value 0.

REPLACE(String or ColName, Substring, [RepString] ) : Replaces every occurrence of given substring with in the given string or values of given column with Repstring.

Example: Select REPLACE( ‘ORACLE CORPORATION’, ‘ORA’, ‘X’ ) from Dual;
This example will replace every occurrence of given substring “ORA” in the given string “ORACLE CORPORATION” with given replace string “X” and the result will be “XCLE CORPXTION”. If Replace string is omitted, then the given substring will be removed from the given string or values of given column. If replace string “X” is not given in the above example, then result will be “CLE CORPTION”.

12.) TRANSLATE(String or ColName, substring, RepString ) : Replaces every occurrence of individual characters in the given substring within given string or values of given column with corresponding character in the replace string.

Example: Select Translate( ‘ORACLE CORPORATION’, ‘ORA’, ‘XYZ’ ) from Dual;

This example will replace every occurrence of given characters in the substring “ORA” in the given string “ORACLE CORPORATION” with given charcters in replace string “XYZ”. I.e. Character “O” is replaced with “X”, “R” is replced with “Y” and “A” is replaced with “Z” and the result will be “XYZCLE CXYPXYZTIXN”. If corrosponding charcter in Replace string for a charcater in substring is omitted, then the given charcter in substring will be removed from the given string or values of given column. If replace string “XYZ” is given as “XY” in the above example, then result will be “XYCLE CXYPXYTION”.

CONCAT(String1 or Col1, String2 or Col2) : Concatenates String2 or Col2 at the end of String1 or Col1. Instead of concat function, we can use the Concatenation operator “”(Double Pipe Symbol”).

SOUNDEX(String or ColName) : Returns how the given string or values of given column are pronounced. The following example retrives the employee records, whose name pronounces same as “SKOTT”.

Example: Select * from EMP where SOUNDEX(Ename) = SOUNDEX(‘SKOTT’);

ASCII(Char) : Returns ASCII value of given character.

Example: Select Ascii(‘A’) from Dual; ( Returns 65 )

Chr(N) : Returns ascii character for given ‘N’.

Example: Select Chr(65) from Dual; ( Returns “A” )

ii.) NUMERIC FUNCTIONS :

Built in single row functions of SQL that work on numeric type of data are called as numeric functions. Numeric functions in SQL are as follows.

Sin(n) : Returns Sine value of given ‘ n ‘ in Radians.
ASin(n) : Returns Arc Sine value of given ‘ n ‘ in Radians.
3. SinH(n) : Returns Hyperbolic Sine value of given ‘ n ‘ in Radians.
4. Cos(n) : Returns Co Sine value of given ‘ n ‘ in Radians.
5. ACos(n) : Returns Arc Co Sine value of given ‘ n ‘ in Radians.
6. CosH(n) : Returns Hyperbolic Co Sine value of given ‘ n ‘ in Radians.
7. Tan(n) : Returns Tangent value of given ‘ n ‘ in Radians.
8. Atan(n) : Returns Arc Tangent value of given ‘ n ‘ in Radians.
9. TanH(n) : Returns Hyperbolic Tangent value of ‘ n ‘ in Radians.
10. ABS(n) : Returns absolute value of given ‘ n ’. i.e., without any sign.
Following example returns 8 by removing “-“ sign.

Example : Select Abs(-8) from Dual;

11.Exp(n) : Returns ‘ e ’ value raised to the power of ‘ n ’. ‘ e ‘ is exponentiation and its value is 2.718.
Following example returns (2.718)3

Example : Select Exp(3) from Dual;

12.Ceil(n) : Returns the smallest integer among all the values that are greater than given ‘n‘
In the following example, ‘ n ‘ is given as 4.721. Here integers greater than 4.721 will be 5,6,7, and so on. Hence it returns 5, the smallest integer among all values that are greater than 4.721.

Example: Select CEIL(4.721) from Dual;
13. Floor(n) : Returns the largest integer among all the numbers that are less than ‘ n ‘.
In the following example, ‘ n ‘ is given as 4.721. Here integers less than 4.721 will be 1,2,3, and 4. Hence it returns 4, the largest integer among all values that are less than 4.721.

Example: Select Floor(4.721) from Dual;

14. LN(n) : It returns Natural Logarithmic value of ‘ n ‘.
15. LOG(M,N) : It returns base M logarithmic value of given N.
Following example returns Log2(8) i.e. base 2 logorithmic value of 8.

Example : Select Log(2,8) from Dual;

16. MOD(M,N) : Returns the remainder after integer division of M with N.
Following example returns 2. Integer division of 7 with 5 will result 1 and remainder will be 2.
Example : Select Mod(7,5) from Dual;

17. SQRT(N) : Returns Square root value of given ‘ N ’.
Following example will return 5 as square root of 25 is 5.

Example : Select Sqrt(25) from Dual;

18. POWER(M,N) : Returns the value of ‘ M ’ raised to the power of ‘ N ’ i.e. MN
Following example returns 125 i.e. 53.
Example : Select Power(5,3) from Dual;
19. ROUND(N,P) : Rounds the given ‘ N ‘ value to ‘ P ‘ decimal places.
Following example returns 7.64 by rounding 7.6351 to 2 decimal places. Round function increments the value 7.63 to 7.64. Because immediate next digit of 3 is greater than or equal to 5.

Example : Select ROUND(7.6351, 2 ) from Dual;

20. TRUNC(N,P) : Truncates the given ‘ N ’ value to ‘ P ‘ decimal places.
Following example returns 7.63 by truncating 7.6351 to 2 decimal places. Truncate function Does not increment the value 7.63 to 7.64 even immediate next digit of 3 is greater than or equal to 5.

Example : Select Truncate(7.6351, 2 ) from Dual;

21. SIGN(N) : Returns –1 if the ‘ N’ value is negative, 0 ( Zero ) if the ‘ N ‘ value is Zero and +1 if ‘ N ‘ is positive.

iii) CONVERSION FUNCTIONS :

The Built in single row functions of SQL that are used to convert one type of data to another are called as conversion function. Conversion functions in SQL are as follows.

TO_CHAR(n) : Converts the given numeric value to character format.
Following example converts the given numeric value 14 to character format i.e. “14”

Example : Select To_Char(14) from Dual;

2. TO_NUMBER(Char) : Coverts the numeric value in character format to Numeric format.
Following example converts the given numeric value 14 in charcater format i.e. “14” to numeric format i.e. 14

Example : Select To_Number(‘14’) from Dual;

TO_CHAR(Date, Format) : Converts given Date to Character format by using the specified format.
Following example converts the system date to DD-MONTH-YYYY format. If we assume the system date as ‘5-jan-02’ then result will be ‘05-JANUARY-2002’.
Example : Select To_Char(Sysdate, ‘DD-MONTH-YYYY’) from Dual;
Strings that can be used to specify the format and their purpose are as follows
1. D : Returns Day of week.
2. DD : Returns Day of Month.
3. DDD : Returns Day of Year.
4. DY : Abbrivative form of week.
5. DAY : Complete spelling of week. ( Ex: Sunday, Monday, …)
6. SP : Complete spelling of dates (Fourteen).
7. TH : Places ‘Th’ or ‘St’ or ‘Rd’ at the end of day.(3rd, 2nd , 4th etc)
8. MM : Two digital months. ( 01, 02, 02,…12 )
9. MON : Abbrivative form of Month.
10. RM : Roman numerical month. ( i, ii, iii, iv, v,…, XII )
11. MONTH : Complete Spelling of month. ( January, …, December )
12. Y : Last digit of the year.
13. YY : Last two digits of year.
14. YYY : Last three digits of year.
15. YYYY : 4 Digital year.
16. YEAR : Year spelled out.
17. HH / HH12 : Hour of day in 12-hour format.
18. HH24 : Hour of day in 24-hour format.
19. MI : Minute of year.
20. SS : Second of minute.
21. AM / PM : Represents AM or PM.
22. WW : Returns week of YEAR.

TO_DATE(CharDate, Format) : Converts the given date in character format to Date. This function has two arguments. First argument is the date in character format and second one is the format of date, which we have given, in first argument.
Following example converts the given date in character format ‘Fourteen-August-2002’ to date format i.e. 14-Aug-02.

Example : Select TO_DATE(‘Fourteen-August-2002’, ‘DDSP-MONTH-YYYY’) from Dual;

iv) DATE FUNCTIONS:

Built in single row functions of SQL that work on Date type of data are called as Date functions. Date functions in SQL are as follows.

SYSDATE : Returns current system date.
Example : Select Sysdate From Dual;

ADD_MONTHS(Date, n) : Returns a date after adding ‘n’ number of months to the given date. If ‘n’ value is negative then ‘n’ months are subtracted from the given date.
Following example adds two months to the current system date and returns a date. If the current system date is 13-JAN-2003 then this example will return 13-MAR-2003.

Example : Select Add_Months( Sysdate, 2 ) from Dual;

MONTHS_BETWEEN( Date1, Date2 ) : Return the difference between given two dates in number of months.
Following example returns the difference between sysdate and ‘01-MAR-2003’. If the system date is ‘13-JAN-2003’, then result will be 1.51(Estimated). Actually the difference is 1 month 16 days. Value after decimal point is obtained by using “(16/30)*100” Calculation.

Example : Select MONTHS_BETWEEN(Sysdate, TO_DATE(‘01-MAR-03’)) from Dual;

NEXT_DAY(Date, day) : Returns the date of given day after given date.
Following Example returns the date of ‘Sunday’ after sysdate. If system date is ‘13-JAN-2003’ then result will be ‘19-JAN-2003’.

Example : Select NEXT_DAY(Sysdate, ‘Sunday’) from Dual;

LAST_DAY(Date) : Returns the date of last day of the month in given date.
Following example returns the last day of the month in system date. If system date is ‘13-JAN-2003’, then result will be ‘31-JAN-2003’ i.e. the last day of month in sysdate i.e. January.

Example : Select Last_Day(Sysdate) from Dual;

ROUND(Date, format ) : Rounds the given date to given format.
Following example Rounds the system date to 1 month. When we are rounding to a month, then Depending on the day of month i.e. if day is less than or equal to 15, then first day of current month will be the result. If day is greater than 15, then first day of next month will be returned. If sysdate is ‘13-JAN-2003’, then result will be ‘01-JAN-2003’. Because day is ‘01’. If sysdate is ‘17-JAN-2003’, then result will be ‘01-FEB-2003’. Because day is ‘17’.

Example 1 : Select ROUND(Sysdate, ‘MM’) from Dual;

Following example Rounds the system date to 1 Year. When we are rounding to a year, then Depending on month of the year i.e. if month is less than or equal to 6, then first day of current year will be the result. If month is greater than 6, then first day of next year will be returned. If sysdate is ‘13-MAR-2003’, then result will be ‘01-JAN-2003’. Because month is ‘03’. If sysdate is ‘13-JUL-2003’, then result will be ‘01-JAN-2004’. Because month is ‘7’.

Example 2: Select ROUND(Sysdate, ‘YY’) from Dual;

Following example Rounds the system date to 1 Day. When we are rounding to a Day, then Depending on the time of Day i.e. if Time is less than or equal to 12PM, then time part is rounded to “00:00:00AM” of the same date. If Time is greater than 12PM, then, time part is rounded to “00:00:00AM” of the next day of current date. If sysdate is ‘13-JAN-2003 10:23:22AM’, then result will be ‘13-JAN-2003 00:00:00AM’. Because Time is ’10:23:22AM’. If sysdate is ‘13-JAN-2003 01:24:12PM’, then result will be ‘14-JAN-2003 00:00:00AM’. Because Time is ’01:24:12PM’.

Example 3 : Select To_char(ROUND(Sysdate, ‘DD’),’dd-mon-yy hh12:mi:ssAM’) from Dual;

TRUNC(Date, Format) : Truncates the given date to given format.
Following example truncates the system date to 1 month. Unlike Round function, truncate returns the first day of month in given date even date is either less than or equal to 15 or greater than 15. If system date is ‘13-JAN-2003’, then result will be ‘01-JAN-2003’.

Example 1: Select TRUNC(Sysdate, ‘MM’) from Dual;

Following example truncates the system date to 1 Year. Unlike Round function, truncate returns the first day of year in given date even month is either less than or equal to 6 or greater than 6. If system date is ‘13-JUL-2003’, then result will be ‘01-JAN-2003’.

Example 2: Select TRUNC(Sysdate, ‘YY’) from Dual;

Following example truncates the system date to 1 Day. Unlike Round function, truncate truncates the time part to “00:00:00AM” of the given date even the time is either less than or equl to 12PM or greater than 12PM. If the sysdate is ‘13-JAN-2003 01:24:12” then result will be ‘13-JAN-2003 00:00:00AM’.

Example 3: Select TRUNC(Sysdate, ‘DD’) from Dual;

DATE ARITHMETIC

Date + n : Returns a date after adding ‘n’ no. of days to the given date.
Following example adds 3 days to system date. If system date is ‘14-JAN-2003’, then result wil be ‘17-JAN-2003’.

Example : Select Sysdate+3 from dual;

Date – n : Returns a date after subtracting ‘n’ no. of Days from given date.
Following example subtracts 3 days from system date. If system date is ‘14-JAN-2003’, then result wil be ‘11-JAN-2003’.

Example : Select Sysdate - 3 from dual;

3. DATE1 –DATE2 : Returns the difference between given two dates in no. of days.
Following example calculates the difference between system date and ‘20-JAN-2003’. If system date is ‘14-JAN-2003’, then result will be 6. Result may come in float value i.e. 5.45, when the difference is not exactly the multiple of 24 hours. This function calculates the difference from “00:00:00AM” of ‘14-JAN-2003’ to “00:00:00AM” of ‘21-JAN-2003’(we have given the date as ‘20-JAN-2003’ and this day will be completed at “00:00:00AM” of ‘21-JAN-2003’). When the difference is not exactly the multiple of 24, then this function calculates the decimal value as current hour/24*100.

Example : Select sysdate – to_date(‘20-JAN-2003’) from Dual;

SPECIAL FUNCTIONS

DECODE : Built in single row functions of SQL those works on any type of data are called as Special functions. Special functions in SQL are as follows.

Syntax : DECODE(Expression, Result1, ReturnVal1, Result2, ReturnVal2,… else ReturnVal);
This function is equivalent to if statement in programming languages. If the expression returns a value equivalent to Result1, then function returns ReturnVal1, if the expression returns a value equivalent to Result2, then function returns ReturnVal2 and so on.
Following example displays Employee name and Salary. Value of salary column is displayed as ‘On Target’ if employee’s salary is 1500, ‘Below 1500’ if salary is less than 1500 and actual salary i.e. 2000,3000 and so on if salary is greater than 1500.

Sign function returns –1 if the value passed is negative, 1 if the value assed is positive, and 0 if the value passed is zero. If the result of the expression Sign(sal-1500) is –1, then Decode Function will return ‘Below 1500’. If the result is zero, then decode function will return ‘on target’. If the expression sign(sal-1500) does not return either –1 or zero, the decode function will return the salary of employee.

Example : Select Ename, DECODE(Sign(Sal-1500), -1, ‘Below 1500’, 0, ‘On Target’, Sal) Salary from EMP;

Using Case : As of Oracle 9i, you can use CASE function in place of DECODE. The CASE function uses the keywords when, then, else, and end to indicate the logic path followed, which may take the resulting code easier to follow than an equivalent DECODE.

Following example displays Employee name and Salary. Value of salary column is displayed as ‘On Target’ if employee’s salary is 1500, ‘Below 1500’ if salary is less than 1500 and actual salary i.e. 2000,3000 and so on if salary is greater than 1500.

Example : Select Ename, case sign(sal-1500)
when 0 then 'On Target'
when -1 then 'Below target'
else to_char(sal)
end "salary" from emp

NVL(Col, Val ) : Replaces Null value in the given column with given val.
Following example displays Employee name, Salary and Total Remuneration for all employees. Total Remuneration is calculated by the formula ( (Sal * 12 ) + Comm ). Any arithmetic operation with null value result null value. As only Sales employees are having commission, For all remaining employees the total remuneration will be Null. To solve this problem we will write the formula to calculate total remuneration as ((Sal * 12) + nvl(Comm,0)). This replaces the null value of comm column with zero. Now arithmetic operation is not performed against null value. Hence the result will be correct.

Example : Select Ename, Sal, (Sal*12+NVL(Comm, 0)) TotalRem from EMP;

LIST FUNCTIONS

The built in functions of SQL that work on a given list of values are called as List Funtions. The list functions in SQL are as follows.

1. LEAST(Val1, Val2, …, Valn) : Returns the smallest value in given list of values.

Example 1 : Select Least(10, 15, 120, 380, 0 ) from Dual; ( Result is 0 )
Example 2 : Select Least( ‘ORACLE’, ‘MICROSOFT’, ‘SUN’, ‘ADOBE’ ) from Dual; (Result is ADOBE)

Example 3 : Select Least(To_Date(‘01-JAN-03’), To_Date(‘12-DEC-02’),To_char(‘31-DEC-03’)) from Dual; (Result is ‘12-DEC-02’)

2.GREATEST( Val1, Val2,…,Valn) : Returns the largest value in given list of values.

Example 1 : Select Greatest(10, 15, 120, 380, 0 ) from Dual; ( Result is 380 )

Example 2 : Select Greatest( ‘ORACLE’, ‘MICROSOFT’, ‘SUN’, ‘ADOBE’ ) from Dual; (Result is SUN)

Example 3 : Select Greatest(To_Date(‘01-JAN-03’), To_Date(‘12-DEC-02’),To_char(‘31-DEC-03’)) from Dual; (Result is ‘31-DEC-03’)

GROUP FUNCTIONS

Built in functions of SQL those works on a group of values are called as Group functions. Group functions in SQL are as follows.

1. SUM(Col) : Returns the sum of all values in the given column.
Following example finds the total salary drawn by all employees.

Example : Select SUM(Sal) from EMP;

AVG(Col) : Returns average of all values in the given column.
Following example finds the average salary drawn by all employees.

Example : Select AVG(Sal) from EMP;

3. MAX(Col) : Returns maximum value of the values in the given column.
Following example finds the maximum salary among the salaries of all employees.

Example : Select MAX(Sal) from EMP;

MIN(Col) : Returns the minimum value of the values in the given column.
Following example finds the minimum salary among the salaries of all employees.

Example : Select MIN(Sal) from EMP;

5. COUNT(Col) : Returns the count of Non Null values in the given column.
Following eample finds the number of employees drawing Commission.

Example : Select COUNT(COMM) from EMP;

6. COUNT(*) : Returns the total number of rows in the given table.
Following eample finds the total number of employees in Emp Table.

Example : Select COUNT(*) from EMP;

7. STDDEV(Col) : Returns the standard deviation for all values of the given column.
Following eample calculates the Standard deviation of salaries of all employees.

Example : Select STDDEV(SAL) from EMP;

VARIANCE(Col) : Returns the variance for all values of the given column.
Following eample calculates the Variance of salaries of all employees.

Example : Select VARIANCE(SAL) from EMP;

GROUP BY : Group by clause is used to divide the rows of a table into groups and apply group functions on each group separately.
Following example finds the maximum salary in each deartment.

Example 1 : Select Deptno, Max(Sal) from EMP Group By Deptno;

Following example finds the average Salary for each job type.

Example 2 : Select Job, AVG(Sal) from EMP Group By Job;

Rule : When we are using group by clause in a select statement, then all column names in the selection list except group functions must be included in group by clause.
Following example finds the total salary drawn by each job type in each department. In this example Deptno and job, two columns are listed in selection list along with the group function sum. As per the rule, we must include these two columns in, group by clause.

Example 3 : Select Deptno, Job, Sum(Sal) from emp group by Deptno, Job;

HAVING : When we have to check a condition that includes a group function, then instead of where clause we must use Having clause.
Following example finds sum of salaries of all employees of the departments that have more than 3 employees.

Example 1: Select Deptno, Sum(sal) from EMP Group By Deptno Having COUNT(*) > 3;

Following example finds the number of employees in departments whose average salary is more than 3000.

Example 2: Select Deptno, Count (*) from EMP Group By Deptno Having AVG(Sal) > 3000;

Rule : If we are using having clause in a select statement, then a corresponding Group By clause is MUST in that select statement.

SET OPERATORS

2008-07-28T21:31:00.000-07:00

S E T O P E R A T O R S

Here the data retrieved by a select statement is treated as a set. Set operators are used to combine Two sets retrieved by two different select statements. The two select statements may retrieve data from the same table or from different tables. Set operators in SQL are as follows.

UNION
UNION ALL
INTERSECT
MINUS

1. UNION : The set operator Union combines the data retrieved by two different select statements by eliminating duplicate values that are obtained after combining the data of two select statements.
Following Example finds all jobs that are in departments 10, 20. In this example, first select statement retrieves the list of jobs in department 10. For example, the jobs in department 10 are Manager, Clerk, and Accountant. Second select statement retrieves the list of jobs in department 20. For example, jobs in department 20 are Manager, Clerk, and Analyst. Result of combining these two results is Manager, Clerk, Accountant, Manager, Clerk, and Analyst. Here Manager and Clerk are duplicated. Union eliminates the duplicated values and final result of union is Manager, Clerk, Accountant, and Analyst.

Example : Select JOB from EMP where Deptno =10
UNION
Select JOB from EMP where Deptno = 20;

2. UNION ALL : Like UNION, UNION ALL also combines two sets that are retrieved by two different select statements except that it does not eliminate duplicate values.
If we take the same above example with union all, then the final result is Manager, Clerk, Accountant, Manager, Clerk, and Analyst.

Example : Select JOB from EMP where Deptno =10
UNION ALL
Select JOB from EMP where Deptno = 20;

3. INTERSECT : The set operator INTERSECT results the values that are common in two sets that are retrieved by two different select statements.
Following example finds the jobs that are present in both departments 10 and 20. In this example, first select statement retrieves the list of jobs in department 10. For example, the jobs in department 10 are Manager, Clerk, and Accountant. Second select statement retrieves the list of jobs in department 20. For example, jobs in department 20 are Manager, Clerk, and Analyst. Result of combining these two results is Manager, Clerk, Accountant, Manager, Clerk, and Analyst. Here Manager and Clerk are Common in both sets. Hence the final result is Manager and Clerk.

Example : Select JOB from EMP where Deptno=10
INTESRSECT
Select JOB from EMP where Deptno = 20;

4. MINUS : The set operator MINUS retrieves the values that are present in first set retrieved by first select statement and not in second set retrieved by second select statement.
Following example finds the jobs that are in department 10, but not in department 20. In this example, first select statement retrieves the list of jobs in department 10. For example, the jobs in department 10 are Manager, Clerk, and Accountant. Second select statement retrieves the list of jobs in department 20. For example, jobs in department 20 are Manager, Clerk, and Analyst. Here Accountant job is present in department 10 but not in department 20. Hence the final result is Accountant.

Example : Select JOB from EMP where Deptno=10 MINUS
Select JOB from EMP where Deptno = 20;

RULES FOR WORKING WITH SET OPERATORS :

Both Select statements must contain same number of columns in the selection list.
Corresponding columns in both the select statements must be of same type.
When using order by clause with set operators, order by clause must be written at the end of second select statement only.
In order by clause we have to specify the position of the column in selection list instead of column name.

JOINS IN ORACLE

2008-07-28T21:28:00.000-07:00

J O I N I N G

Combining data from more than one table using a single select statement is called JOINING. Joining in SQL is of these types.

Equi Join
Natural Join
Outer Join
Inner Join
Self Join

1.EQUI JOIN :

Joining more than one table by using a condition that includes ‘ = ’ operator is called as EQUI JOIN.
Following example displays employee details along with Department name in which he is working. Employee details are present in EMP table and Department name exists in another table DEPT. Hence in this example joining is performed between EMP and DEPT. When performing join operation, then we must specify column names in selection list as tablename.columnname when any two tables among all tables we are going to join have a column with same name. Instead of using entire table name, we can use alias names for the tables as in the following example. Using alias names is otional for the columns that are not common in two tables.

Example : Select Ename, Job, Sal, E.Deptno, Dname from EMP E, DEPT D where E.Deptno=D.Deptno;

2. NATURAL JOIN :

As of Oracle 9i, you can use the natural join keyword to indicate that a join should be performed based on all columns that have the same name in the two tables being joined.

Example : Select Ename, Job, Sal, Deptno, Dname from EMP natural join DEPT;

3. OUTER JOIN :

Outer Join is used to display the rows that doesn’t satisfy the given condition, along with the rows that satisfy the given condition. To perform outer join, “right outer join” or “left outer join” or “full outer join” clauses are used. These clauses are used in from clause of the select statement.
The above example, written for the equi-join does not display the department names, in which there are no employees. Because the given condition E.Deptno=D.Deptno will be false for the departments that have no employees. Following example displays the employee details along with their department names. Also displays the department names that have no employees. For this outer-join is performed between emp and dept and “right outer join” clause is used. Because a department exists but corresponding employee does not exist. This means that EMP table does not contain rows corresponding to one or more departments in dept table and DEPT table is towards right side of the equi-join condition.

Example : Select Ename, Job, Sal, D.Deptno, Dname from EMP E right outer join DEPT D on E.Deptno = D.Deptno;

You can replace the on clause with a using clause along with the name of the column the tables have in common – do not qualify the column name with a table name or table alias.

Example : Select Ename, Job, Sal, Deptno, Dname from EMP right outer join DEPT using(deptno)

4. INNER JOIN :

Support for inner join was introduced in Oracle9i. inner joins are the default – they return the rows the two tables have in common, and are the alternative to outer joins. Note that they support on and using clauses. So you can specify your join criteria.
Following example displays employee details along with their grade. Employee details are present in EMP Table and Grade Details are present in SALGRADE Table. SALGRADE table contains LOSAL, HISAL and GRADE Columns. LOSAL and HISAL are the salary range and GRADE is grade of the employees having the salary in that salary range. In this example between…and operator is used. Here as both tables EMPand SALGRADE does not contain columns with same names, Table aliases are not used.

Example : Select Ename, Job, Sal, Deptno, Grade from EMP inner join SALGRADE on Sal BETWEEN Losal AND Hisal;

5. SELF JOIN :

Joining a table to it self is called as Self-Join. Generally self-join is performed on a table that contains self-referential key. Self-referential key is a foreign key that refers to a primary key present in the same table in which it presents.
Following example displays Employee details along with their manager name. In EMP table, EMPNO is primary key and MGR is a foreign key that refers to the primary key EMPNO. As manager is also an employee in the same table, we can get manager’s name by joining EMP table to it self. In self-join we must use alias names or tablename.columnname notation.

Example : Select E.Empno, E.Ename, E.Job, E.Sal, E.Mgr, M.Ename “MANAGER” from EMP E inner join EMP M on E.Mgr = M.Empno;

RULES TO PERFORM JOIN :

User must follow the following rules when performing join operation.

1. In joining statements you must specify the column names in the selection list even you are
Selecting all columns of the table and specifying “*” to select al columns is not possible.
2. If the joining tables contain columns with same name, then tablename.columnname notation or table alias is must except when performing natural join and when using “using” clause.
3. The number of join conditions must be one less than the number of tables you are going to join when performing inner join.

SUB QUERIES

2008-07-28T21:25:00.000-07:00

S U B Q U E R I E S

A statement requesting data from database called as a query. As we are using select statement to request data from database, select statement is called as a query. Writing a select statement within another select statement is called as a Subquery. In a subquery the select statement that contains another select statement within it is called as outer query and the select statement that is within another select statement is called as Inner query. Inner query must be placed in parantheses. This is because inner query must be executed prior to outer query. Sub queries in SQL are of four types and are as follows.

Single Row Sub Queries
Multi Row Sub Queries
Nested Sub Queries
Correlated Sub Queries

1.SINGLE ROW SUBQUERIES :

If an inner query of a subquery returns only one value then that subquery is called as a Single Row Subquery.
Following example displays the employee details whose salary is maximum salary among all employees. In this example, inner query retrieves the maximum salary in EMP table and outer query compares salary of every employee with the salary returned by inner query. When employee salary is equal to the salary returned by inner query, then that employee row is displayed in output.

Example 1 : Select * from EMP where Sal = ( Select Max(Sal) from EMP );

Following example displays all employee details who are working in SALES deparetment. Because EMP table contains only department number and it does not contain department name, first we have to know the DEPTNO of SALES department. Inner query retrieves the DEPTNO of sales department from DEPT table and that DEPTNO is used by outer query to find employees working in SALES department.

Example 2 : Select * from EMP where Deptno=(Select Deptno from DEPT where Dname=’SALES’);

As inner query of above two subqueries returns only one value, these subqueries are called as single row subqueries.

2.MULTI ROW SUB QUERIES :

If an inner query of a subquery returns more than one value then that subquery is called as a Multi Row Subquery.
Following example finds the employees drawing maximum salary for each department. In this example, inner query returns the list of maximum salaries in all departments and outer query will compare employee salary with the list of salaries returned by inner query. If employee salary is equivalent to any one of the slary in the list of slaries returned by inner query, then that employee row is displayed in out put.

Example : Select * from EMP where Sal in ( Select Max(Sal) from EMP Group By Deptno );

But the result of this query is wrong. If maximum salaries of departments 10,20, and 30 are 1500,2800,5000 and an employee of department 30 has a monthly salary 2800, then the row of that employee is also retrieved even he is not drawing maximum salary in his department i.e. 5000. When working with multi-row subqueries, we must perform condition checking with more than one column and the above query corrected is as follows.
Example : Select * from EMP where (Deptno, Sal) in ( Select Deptno, Max(Sal) from EMP Group By Deptno);

In the above subquery, inner query returns more than one value. Hence this subquery is called as multi-row subquery. There are two special operators that can be used with Multi Row subqueries. They are Any/Some and All.

ANY / SOME : This operator can be used only with multi row subqueries. This operator returns true if the given condition is true with any one of the values retrieved by the inner query.
Following example finds all employees who are drawing more salary than any one of the employees working in Department 30. Inner query retrieves a list of slaries drawn by the employees working in department 30 and outer query will compare the salary of each employee with every value returned by inner query. If the given condition is true with any one of the values in the list of salaries returned by inner query, then that row is displayed in the output. If the given condition is not true with all values in the list returned by inner query, then that row is not displayed in the output.
Example : Select * from EMP where Sal > ANY(Select distinct Sal from EMP where Deptno=30);

ALL : This operator can also be used only with multi row subqueries. This operator returns true if the given condition is true with all values returned by inner queery.
Following example finds all employees whose salary is more than every employee working in Department 30. Inner query retrieves a list of slaries drawn by the employees working in department 30 and outer query will compare the salary of each employee with every value returned by inner query. If the given condition is true with all of the values in the list of salaries returned by inner query, then that row is displayed in the output. If the given condition is not true with any one of the value in the list returned by inner query, then that row is not displayed in the output.

Example : Select * from EMP where Sal > ALL(Select distinct sal from EMP where Deptno = 30 );

3. NESTED SUBQUERIES :

If an inner select statement of a subquery contains another select statement within it then that subquery is called as Nested subquery. Subqueries can be nested up to 255 levels.
Following example finds all employees whose salary is more than average salary of sales department. As department name does not existin EMP table, first we have to find the deptno of sales dapartment. Then we have to find the average salary of sales department by using deptno returned by inner most query. Finally this average salary is used by outer query to find the employees whose salary is more than this average salary.

Example : Select * from EMP where Sal > (Select AVG(Sal) from EMP where Deptno = (Select Deptno from DEPT where Dname = ‘SALES’ ));

4. CO-RELATED SUBQUERIES : If an inner query of a subquery contains an outer query column in the condition of where clause then that subquery is called as a co-related subquery.
Following example finds all employees whose Salary is more than average salary of the department in which they are working. Inner query finds the average salary of a department in which an employee, whose row is curently accessed by outer query and this average salary is compared to the salary of that employee and if his salary is more than average salary. Then his row is displayed in the output.

Example : Select * from EMP E where Sal > ( Select AVG(Sal) from EMP S Where E.Deptno = S.Deptno );

The difference between normal subqueries and correlated subqueries is that in normal subqueries the inner query is executed only once. But in correlated subqueries inner query is executed once per each row of the outer query.

TRANSACTION PROCESIING, CONCURRENCY AND LOCKING

2008-07-28T21:19:00.001-07:00

TRANSACTION PROCESSING

A transaction is an operation against the database, which comprises a series of changes to one or more tables. There are two classes of transactions, DML transactions, Which can consist of any number of DML statements and which Oracle treats as a Single entity or Logical unit of work and DDL transactions, which can only consist of one DDL statement.

There can be no halfway situation during the execution of the transaction, where some changes specified within the transaction are made to the database and others are not made. For every transaction either all the changes made to the database are made permanent or none of the changes are carried out.

A transaction begins when the first executable DML or DDL command is encountered and ends when one of the following occurs.
1. Commit or Rollback
2. DDL Command is issued
3. Certain Errors
4. Log Off
5. Machine Failure.
A DDL statement is automatically committed and therefore implicitly ends a transaction.

Making Changes Permanent

In order for changes to become permanent they must be committed to the database. The “ COMMIT ” command makes database changes permanent. The change or changes made to the database between two COMMIT commands therefore makeup a transaction. Until a transaction is committed, none of its changes are visible to other users.

COMMIT
Commit makes changes in the current transaction permanent.
Commit erases all Save Points in the transaction.
Commit ends the transaction.
Commit releases the transaction locks.
You should explicitly end the transaction in application programs, using COMMIT statement. If you do not explicitly COMMIT the transaction and the program terminates abnormally, the last committed transaction will be rolled back.
Implicit commits occur in the following situations.
Before a DDL command.
After a DDL statement.
At normal disconnect from database.

Removing Unwanted Changes :

Uncommitted changes may be abandoned by typing “ROLLBACK“. Rollback will return the data to the state it was in immediately after your last Commit by discarding all changes made since the last commit.

Syntax: RollBack to Savepoint Savepointname;

The Rollback statement is used to UNDO work.
Rollback to a savepoint is optional.
If you use rollback without a “ to Savepoint ” clause, it
Ends the transaction.
Undoes all changes in current transaction.
Erases all Savepoints in that transaction.
Releases the transaction locks.

Save Point :

Syntax : Savepoint SavepointName;

Savepoint can be used to divide a transaction into smaller portions.
Savepoints allow you to arbitrarily hold your work at any point in time, with the option of later committing that work or undoing all or a portion of it.
If you create a second savepoint with the same name as an earlier Savepoint, the earlier Savepoint is deleted.
The maximum number of Savepoints per user process is defaults to 5.

System Failures :

When a transaction is interrupted by a serious error, for example, a system failure, the entire transaction is automatically rolled back. This prevents the error from causing unwanted changes to your data and returns your tables to their status at the time of the last COMMIT.
An automatic roll back is most often caused by a system failure, such as an unintentional system reset or power blackout. Errors in entering commands such as misspelling a column name or trying to perform an unauthorized operation on another user’s table do not interrupt a transaction or cause an automatic rollback.
Auto Commit :
Commit or Rollback may be issued manually or automatically by using the auto commit option of the set command. Syntax: Set AutoCommit On/Off.

CONCURRENCY AND LOCKING

Oracle locks are used to control concurrent access to data. Locks are mechanisms intended to prevent destructive interaction between users accessing the same data. Locks are used to achieve two important goals, data concurrency and read consistency. Locks in Oracle fall into two categories.

Data Locks or DML Locks : These locks protect data. Table locks lock the entire table while Row locks just locks selected rows.

Dictionary Locks : These locks protect the structure of Database objects. Dictionary locks are of two types.
Parse Locks and
DDL Locks.
In this chapter we will discuss only data locks.

Summary Of Table Locks :

Tables can be locked in Exclusive(X), Share(S), RowExclusive(RX), RowShare(RS) and ShareRowExclusive(SRX) modes. Following table shows the posiility of operations against a table when that table is locked in a particular mode. The user who locks the table can perform any operation against the table. The following table describes the posibilities for other users on a table, which is locked by a user. For example, if a table is locked in exclusive mode, then locking that table in any mode and performing DML operations is not posible for other users. In the same way if a table is locked in share mode, then locking that table in share mode and row share mode is possible for other users.

Lock Table Command :

Syntax : Lock Table Tabname1, Tabname2,…, Tabnamen In Lockmode [Nowait];

Here Tabname1, Tabname2, …. , Tabnamen are the names of tables to be locked. Lock mode is one of the modes in which a table can be locked i.e. exclusive, share, row exclusive, row share and share row exclusive. Nowait will allow the user to do other work when another user locks a table, which he accesses. If nowait is not specified, then the system will hang if another user locks the table, which he accesses, until that user releases the lock.

Implicit Locking :

Implicit locking is the locking performed by Oracle automatically without Locking by the user manually. This implicit lock is generally done when updation or deletion is performed against a table and it locks only the rows that are affected by the update or delete statement.

Select…For Update : The select for update statement will overwrite the default locking mechanism. It is used in anticipation of performing an update. The difference between select for update and Update is that select for update locks the rows earlier in a transaction.

Syntax : Select col1, col2,…, coln from tablename For Update [ NOWAIT ]

Dead Lock :

It is quite possible in a multi user environment, that two users will lock each other out of resources. It is also possible that two users will end up locking each other out of different resources. This situation is called a Dead Lock, Because each user is waiting for resources held by the other user.

When Oracle detects a dead lock, it signals an error to one of the participating transactions and rolls back the current statement of that transaction. This resolves the Dead Lock, although other users may still wait until their resource is available. The signaled user should explicitly rollback their transaction.

Dead locks can be avoided if users accessing the same tables lock those tables in the same order as each other. You should pre-define order of access for all tables in your applications and then have all applications follow the same order. If this order is followed in all applications, deadlocks will be kept to a minimum.

Duration Of Locks :

All locks acquired during a transaction are released when the transaction is committed or rolled back.

CONCURRENCY AND LOCKING

2008-07-28T21:19:00.000-07:00

CONCURRENCY AND LOCKING

Oracle locks are used to control concurrent access to data. Locks are mechanisms intended to prevent destructive interaction between users accessing the same data. Locks are used to achieve two important goals, data concurrency and read consistency. Locks in Oracle fall into two categories.

Data Locks or DML Locks : These locks protect data. Table locks lock the entire table while Row locks just locks selected rows.

Dictionary Locks : These locks protect the structure of Database objects. Dictionary locks are of two types.
Parse Locks and
DDL Locks.
In this chapter we will discuss only data locks.

Summary Of Table Locks :

Tables can be locked in Exclusive(X), Share(S), RowExclusive(RX), RowShare(RS) and ShareRowExclusive(SRX) modes. Following table shows the posiility of operations against a table when that table is locked in a particular mode. The user who locks the table can perform any operation against the table. The following table describes the posibilities for other users on a table, which is locked by a user. For example, if a table is locked in exclusive mode, then locking that table in any mode and performing DML operations is not posible for other users. In the same way if a table is locked in share mode, then locking that table in share mode and row share mode is possible for other users.

Lock Table Command :

Syntax : Lock Table Tabname1, Tabname2,…, Tabnamen In Lockmode [Nowait];

Here Tabname1, Tabname2, …. , Tabnamen are the names of tables to be locked. Lock mode is one of the modes in which a table can be locked i.e. exclusive, share, row exclusive, row share and share row exclusive. Nowait will allow the user to do other work when another user locks a table, which he accesses. If nowait is not specified, then the system will hang if another user locks the table, which he accesses, until that user releases the lock.

Implicit Locking :

Implicit locking is the locking performed by Oracle automatically without Locking by the user manually. This implicit lock is generally done when updation or deletion is performed against a table and it locks only the rows that are affected by the update or delete statement.

Select…For Update : The select for update statement will overwrite the default locking mechanism. It is used in anticipation of performing an update. The difference between select for update and Update is that select for update locks the rows earlier in a transaction.

Syntax : Select col1, col2,…, coln from tablename For Update [ NOWAIT ]

Dead Lock :

It is quite possible in a multi user environment, that two users will lock each other out of resources. It is also possible that two users will end up locking each other out of different resources. This situation is called a Dead Lock, Because each user is waiting for resources held by the other user.

When Oracle detects a dead lock, it signals an error to one of the participating transactions and rolls back the current statement of that transaction. This resolves the Dead Lock, although other users may still wait until their resource is available. The signaled user should explicitly rollback their transaction.

Dead locks can be avoided if users accessing the same tables lock those tables in the same order as each other. You should pre-define order of access for all tables in your applications and then have all applications follow the same order. If this order is followed in all applications, deadlocks will be kept to a minimum.

Duration Of Locks :

All locks acquired during a transaction are released when the transaction is committed or rolled back.

USER, PRIVILLEGES and ROLES

2008-07-28T21:12:00.000-07:00

USER, ROLES & PRIVILEGES

Oracle provides exclusive security features to safeguard your information from both unauthorized viewing and intentional or inadvertent damage. This security is provided by granting or revoking privileges on a person by person and privilege by privilege basis and is in addition to any security your computer system already has.

Every Oracle user has a name and password and owns any tables, views and other resources that he creates. An Oracle role is a set of privileges. You can grant or revoke specific privileges to roles and then assign roles to the appropriate user. A user can also grant privileges directly to other users.

Users :

The Oracle system comes with two users already created System and Sys. You log on to the system user to create other users, since system user has that privilege. The create user command is used to create a new user. After creating a user we can change the password of the user or the memory allocation option or tablespace by using alter user command and it has the following syntax.

Syntax : Alter user UNAME identified by PWD Quota unlimited/Nk on System;

Like create user command, alter user command must also be given only from SYSTEM user. There is another command to change the password of the user from within the same user to which the password has to be changed. That command is Password. To change the password by using this command, user must know the old password.

Syntax : Password

We can delete a user by using drop user command and has the following syntax.

Syntax : Drop user Uname [cascade];

Privileges :

To connect to the database or to create any objects within the database or to access the data owned by another user, a user must have a corresponding permission. These permissions are called PRIVILEGES.

Database system privileges let you execute specific sets of commands. The “create table“ privilege, for example, lets you create tables. The “Grant Any Privilege” privilege allows you to grant any system privilege.

Database object privileges give you the ability to perform some operation on various objects. The “Delete” privilege, for example, lets you delete rows from tables and views. The “Select” privilege allows you to Query with a Select from tables, views, sequences and snapshots.

Granting Privileges :

Grant command is used to grant any system privilege or role to another user, to another role, or to public. The grant command has the following syntax for granting system privileges or role. System privileges can be granted to any user from System User only.

Syntax : Grant SystemPrivilege or Role, SystemPrivilege or Role, …
To Username/Role, Username/Role, …
[With Admin Option];

Example : Grant Create User, Alter User, Drop User to Ramesh;

After executing the above statement, user Ramesh can create a new user or alter an existing user or can delete an existing user from the database. The “with Admin Option” clause permits grantee to grant the Privilege/Role on to other user or role. If the above grant statement is written with “with admin option” at the end, then user Ramesh can grant “Create User”, “Alter User” and “Drop User” Privileges to other users, without connecting to system user.

To grant object privileges the grant command has the following syntax.

Syntax : Grant ObjectPrivilege, ObjectPrivilege,… on TableName
To UserName/Role, Username/Role, …
[ With grant Option ];

For example, if you want to grant select, insert and update permissions on student table of your user to scott, then following statement is used.

Example : Grant Select, Insert, Update(Fee) on Student to Scott;

The above grant statement grants select, insert and updation of FEE column permissions on student table to user Scott. This means that Scott can perform selection, insertion on student table and he can update only fee column of student table.

The “with Grant Option“ clause permits grantee to grant the Privilege on to other user or role. If the above grant statement is written with “with grant option” at the end, then user scott can grant “Select”, “Insert” and “Update” Privileges on student table to other users, without connecting to your user.

Instead of granting specific privileges to specific users, we can grant all privileges on a table as follows.

Example : Grant ALL on Student to Scott;

Instead of granting specific privileges to specific users, we can grant specific privileges or all privileges on object or system privileges to all users in the database at a time by using keyword “PUBLIC“ in place of Username. Following grant statement grants all permissions on student table in your user to all users in the database.

Example : Grant ALL on Student to Public;

Revoking Privileges :

Revoke is used to revoke privileges from a user or role. Revoke command has the following syntax for revoking System privileges. System privileges can be revoked from System user or the user who grants those permissions.

Syntax : Revoke SystemPrivilege/Role, SystemPrivilege/Role,…
From UserName/Role, UserName/Role…;

Example : Revoke Create user, Alter user, Drop user from Ramesh;

After executing the above revoke statement, user Ramesh can not create a new user or alter an existing user or drop an existing user.

Revoke command has following syntax for revoking Object privileges.

Syntax : Revoke ObjectPrivilege, ObjectPrivilege, …
on TableName
From Username/Role,Username/Role,…;

Example 1 : Revoke Select, Insert, Update(Fee) on Student From Scott;
Example 2 : Revoke All on Student From Public;

Example 1 revokes select, insert and updation of fee column privileges on student table from user Scott. Example 2 revokes all permissions on student table from all users in the database.

Roles

A role is nothing but a set of privileges. Using following syntax creates a role.

Syntax : Create Role RoleName;

Example : Create Role TEST;

Granting Privileges On A Role :

Example 1 : Grant Select on Student to TEST;
Example 2 : Grant Select, Insert, on Marks to TEST;
Example 3 : Grant ALL on EMP to TEST;

Above Grant statements grant different permissions on different users to the role TEST. You can also grant system privileges to the same role.

Granting A Role To A User :

Example : Grant TEST to Scott;

Above grant statement grants all permissions of Role TEST to the user Scott.

Revoking A Role From A User :

Example : Revoke TEST from Scott;

Above grant statement revokes all permissions of Role Test from User Scott.

Revoking Privileges From A Role :

Example : Revoke Select, Insert on Marks From TEST;

This statement revokes select and insert privileges on Marks table from Role TEST.

Deleting A Role : A Role can be deleted from database by using drop role command and has following syntax.

Syntax : Drop Role RoleName;

Example : Drop Role TEST;

Standard Roles
Oracle provides standard roles for compatibility with previous versions.

The Connect Role : Occasional users, particularly those who do not create tables will usually be given only the connect role. Connect is the simple privilege to use Oracle at all. This right becomes meaningful with the addition of access to specific tables belonging to other users, and the privilege to select, insert, update and delete rows from these tables as each of these rights are granted.

The Resource Role : More sophisticated and regular users of the database may be granted the resource role. Resource role gives users the additional rights to create their own tables, sequences, procedures, triggers and clusters.

The DBA Role : The DBA role has all the system privileges including unlimited space quotas and the ability to grant all privileges to other users.

Synonym : Synonym is nothing but an alias name to a table and is generally used when we have to access a table of another user. Prior to creating a synonym on another users table you must have privilege on that table.

Syntax : Create [ Public ] Synonym Synonymname for Username.TableName;

Example : Create Synonym SS For Scott.Student;

Above example creates a synonym with name SS for student table in user scott. Now you can use SS to access student table of scott instead of scott.student. Public key word will create public synonym that can be used by all users in the database those have permissions on student table of scott.

As other objects, a synonym can also be deleted. Syntax for deleting a synonym is as follows

Syntax : Drop [ Public ] Synonym Sname;

Example : Drop Synonym SS;

If we use public key word to create a synonym then to delete that synonym we must use the key word public.

VIEWS

2008-07-28T21:08:00.000-07:00

V I E W S

· A view is like a window through which data on tables can be viewed or changed.
· A view is derived from another table or view, which is referred to as the base table of the view.
· A view is stored as a select statement only. It is a virtual table i.e. a table that doesn’t physically exist in its own right, but appears to the user as if it exists.
· A view has no data of its own. It manipulates data in the underlying base table.

Creating A View :

Syntax : Create [ OR REPLACE ] [ FORCE ] View
Vname [( Col1, Col2, Col3, … Col n )] As
Select Statement [ WITH CHECK OPTION ]

The “OR REPLACE“ option allows a view to be created even if one exists with that name already. Thus replacing the old version of the view for its owner. This means that the view can be altered without dropping and recreating.

The “FORCE“ option creates the view even if the base table doesn’t exist or there are insufficient table privileges. However, the table must exist before the view is used.

When you create a view with “ WITH CHECK OPTION ” then the view prevents a row from being inserted into the table that violates the condition given in Select statement.

Types Of Views

Depending on the select statement written for the creation of view, views are classified into two types.

Simple View : If a view is created by using a select statement that retrieves data from a single table and doesn’t contain functions and Group By clause, then that view is called as a simple view.

Example : Create Or Replace View EMP10 As Select * from EMP where Deptno = 10;

Complex View : If a view is created by using a select statement that retrieves data from more than one table or contains functions or Group By clause then that view is called as a Complex view. Any DML operation is not possible on a Complex view.

Example : Create Or Replace View EMPDEPT as Select Eno, Ename, Sal, E.Deptno,
Dname, LOC From EMP E, DEPT D where E.Deptno = D.Deptno;

Restrictions On Views :

Using “WITH CHECK OPTION” when the view is created can impose restrictions. There are other constraints to be aware of.

Delete is prohibited if the view contains
Join condition
Group function
Group By Clause
Distinct command
Row Num.

Update is prohibited if the view contains
1. Any of the above
2. Columns defined by expressions. ( like Sal * 12 )

Insert is prohibited if the view contains
1. Any of the above
2. Any NOT NULL columns are not selected by the view.

Uses Of Views :

Restricting access to database. Selecting from a view can display a restricted portion of database.
Allowing users to make simple queries to retrieve results from complicated queries.

Deleting A View :

Syntax : DROP VIEW VIEW NAME

SEQUENCES and CLUSTERS, PSUEDO COLUMNS

2008-07-28T20:42:00.000-07:00

S E Q U E N C E S

The sequence generator can be used to automatically generate sequence numbers for rows in a table. For example, you can use sequence generator to produce unique primary keys. In order to generate sequence number automatically you must define a sequence using create sequence statement. The create sequence statement has the following syntax.

Syntax :
Create Sequence SeqName
Increment by n,
Start with n,
Minvalue n,
Maxvalue n,
Cycle/Nocycle;

Here startwith specifies the value to be generated by the sequence when sequence was called for the first time. Increment by specifies the difference between the values generated by the sequence i.e. the increment for the old value, generated by the sequence to generate new value. Minvalue specifies the minimum value to be generated by the sequence. In general startwith and minvalue are same. Maxvalue specifies the maximum number to be generated by the sequence. Nocycle option specifies that the sequence will end when the value generated by the sequence equals to maxvalue. When nocycle option is used and sequence reaches maxvalue, then generating sequence numbers using that sequence is not possible. Cycle option specifies that when sequence reaches its maxvalue and user calls the sequence again, sequence will continue generating numbers starting from minvalue. It means that sequence will never end.

Example : Create Sequence SeqDno
Increment by 10
Start with 10
Minvalue 10
Maxvalue 100
NoCycle;

Generating Sequence Numbers

Nextval : The pseudo column “Nextval” is used to extract successive sequence number. When you refer to next value a new sequence number is generated. Nextval is mostly useful in DML statement.

Syntax : SequenceName.Nextval
Example : Insert into DEPT values(SeqDno.NEXTVAL, ‘ACCOUNTING’, ‘DALLAS’);

Currval : The pseudo column “Currval” is used to refer to a sequence number that has just been generated. When you refer to Nextval for a given sequence, then the newly generated sequence number is placed in Currval.

Syntax : SequenceName.Currval
Example : Select SeqDno.Currval from Dual;

Rules For Using Nextval And Currval

Nextval and Currval may be used within

The select clause of a Select Statement.
The values list of an insert statement.
The set clause of an update statement.
Outermost select of a subquery.

Nextval and Currval may not be used within

Within the select list of a view.
With the keyword distinct.
With the Order by, Group by or Having clauses of a select statement.
With the set operators Union, Intersect, and Minus.
Within a subquery.

Altering A Sequence :

Syntax: Alter Sequence SeqName
Increment by n
Minvalue n
Maxvalue n
Cycle/Nocycle

In Alter Sequnce command it is not possible to alter the startwith option specified during sequence creation. Alter Sequence command does not effect the numbers that are already generated by the sequence but effects only the values that are going to be generated by the sequence.

Deleting A Sequence :

Syntax : Drop Sequence SeqName;
Example : Drop Sequence DnoSeq;
====================================================================
P S E U D O C O L U M N S

Pseudo columns are the columns that are not present in a table but appears to the user as if they are the columns of a table. Pseudo columns in SQL are as follows.

Sysdate
NextVal
CurrVal
User : Displays the current user name.
UID : Oracle assigns a unique ID to every user during creation of user. The pseudo column UID is used to get the unique ID assigned by Oracle to the current user.
ROWID : Oracle assigns a unique rowid to every row inserted in to a table. The pseudo column ROWID is used to get the unique ID assigned to the row of a table.
ROWNUM : Oracle assigns a number to every row of a table during its selection. The pseudo column ROWNUM is used to get the unique row number assigned by Oracle to the row of a table.
LEVEL : Level pseudo column is used to get the level number of a row during tree walking.

====================================================================

C L U S T E R S

Clustering is a method of storing tables that are intimately related and often joined together into the same area on disk. The Cluster key is the column or columns by which tables are usually joined in a query.

Creating A Cluster : A cluster is created by using the following syntax.

Syntax : Create CLUSTER CLUSTERNAME
(ColName Datatype(Size), ColName Datatype(size),….);

Example : Create Cluster STU_CLU (Sno Number(5));

After creating the cluster, a table can be created within that cluster as follows.

Create Table STUDENT
(Sno Number(5) Primary key,
Sname Varchar2(10),
Fee Number(10)
) CLUSTER STU_CLU(Sno);

To place tables on desired cluster we have to specify the cluster name on which we want to place the cluster at the end of create table statement after closing the parantheses. Along with cluster name we must also specify the cluster key column. In the above create table statement, student table is placed on cluster with name stu_clu. Like this you can place all related tables of student on same cluster. This will improve the database performance during data retrieval from these tables because all tables are stored in the same area on disk.

DML operations are not possible on a table that is created by using cluster option until an index is created on that cluster. Index is created on a cluster by using the following syntax.

Syntax : Create Index Indexname On Cluster Clustername
Example : Create Index STUCLU_IDX on Cluster STU_CLU;

Deleting A Cluster :

Syntax : Drop cluster clustername;
Example : Drop Cluster STU_CLU;

Note : Oracle will not allow the deletion of clusters that contain tables. Hence to delete a cluster we must first delete all tables created on that cluster.

O N D E L E T E C A S C A D E

We have created two tables Student and Marks by giving primary key constraint to Sno column of Student table and foreign key constraint on sno column of marks table that refers the Sno column of Student table. Now the student table is called as “Master Table” and Marks table is called as “Detailed Table”. Now if we want to delete a row from student table, which is having a dependent row or rows in Marks table will generate an error.

If we want to delete the dependent rows in foreign key table automatically when user is deleting the corresponding primary key row then “ON DELETE CASCADE” option is specified at the end of foreign key of Marks table.
Example :

Create Table Marks
( Sno Number(5) Constraint Sno_Fk References Student(Sno) On Delete Cascade,
M1 Number(5),
M2 Number(5),
Tot Number(6));

INDEXES IN ORACLE

2008-07-28T20:39:00.000-07:00

I N D E X E S

In Oracle indexes have two main purposes. They are

i.) To speed up the retrieval of rows via a particular key
ii.) To enforce uniqueness on values in a column.

The use of indexes is strongly recommended to achieve better performance. Once created, oracle will use the index whenever possible to speed up the data retrieval and this use is automatic and usually requires no action by the user who need never know of the existence of the index. Oracle will automatically create index on unique or primary key columns when the table was created.

Creating An Index :

Syntax : Create [Unique][Bitmap] Index idxname On Tabname(Col1, Col2, Col3,… , Col n);

Example : Create Index DNOIDX On EMP(Deptno);

This will create an index with name DNOIDX on Deptno column of EMP table.

Types Of Indexes :

Single Column Index : If an index is created on a single column of a table then that index is called as a Single Column Index.

Concatenated Index : If an index is created on more than one column of a table then that index is called as Concatenated Index.

Unique Index : Unique indexes are created by using unique keyword in the create index command. The purpose of unique index is to enforce uniqueness on the column even unique or primary constraint is not given on that column.

Non-Unique Index : An index created without unique key word is called as a non-unique index.
Bitmap Index : The bitmap index is created by using bitmap keyword in the create index command. The difference between normal and bitmap index is in bitmap indexes the identification of rows is in the form of binary digits i.e. 0, 1.

When Oracle Uses Index : Oracle will not use the index whenever user retrieves data from a table. Oracle uses index in the following situations.
When the indexed column is referenced in where clause.
Oracle will not use the index when the indexed column is used as a part of a function in where clause. (Ex : Upper(Ename))
When a concatenated index is created, all columns listed in the index must be used in the where clause to use index by Oracle.

We can create an Index on a column that allows function-based access. For this during the creation of index specify the column name along with function name.

Example: Create Index EnameIdx On EMP(UPPER(Ename));

Deleting An Index :

Syntax : Drop Index IndexName;

Example : Drop Index DnoIdx;

PL-SQL

2008-07-28T20:29:00.000-07:00

Introduction to PL-SQL

Oracle uses SQL as its primary language for solving any type of Queries. As SQL is a fourth generation language, it is very easy to use and flexible for the user. Even though SQL is very easier and flexible, some complex queries will not be solved by SQL. To solve the complex queries that are not solved by SQL Oracle introduces a procedural or Third generation language called PL/SQL (Procedural Structure Query Language).

PL/Sql Featrues :

PL/SQL is a third generation or procedural language.
Most of the PL/SQL language features are taken from the object oriented programming language ADA.
As it is a procedural language, it provides the features like condition checking, looping, error handling etc.
PL/SQL Programming is called as a Block structured programming.

Data Types :

The keyword of a language that is used to specify the type of value that can be stored in a variable is called as Datatype. Data types in PL/SQL are as follows.

Number
Varchar2
Char
Long
Long raw
Raw
Date
Binary_Integer
Pls_Integer
Boolean
Float
Int

Binary_Integer, Pls_Integer and int are used to store integer values. Although their purpose is same, internal memory representation is different. Float is used to store real values, number is used to store both integer and float values and Boolean is used to store True or False.
Variable : An identifier used to identify a location in memory to store and access constants is called as a variable.

Declaration : A statement in a language that associates a variables with its corresponding data type is called as Declaration statement. PL/SQL uses the following syntax for declaring a variable.

Syntax : Varname Datatype(Size) [ Constant ] [ Default / := Value] ;
Example : Sno Number(5);
Sno Number(5) := 0;
Sno Number(5) Default 0;
Sno Number(5) Constant := 20;

Operators In Pl/Sql :

Arithmetic Operators : +, -, *, /, ** (Exponential operator)
Relational Operators : <, >, <=, >=, != / <> / ~=
Comparison Operator : =
Assignment Operator : :=
Concatenation Operator :
Comments in PL/SQL : -- Single Line Comment
/*…*/ Multiple Line Comment
Note : For the Output to be printed on Screen as a result of any program/procedure, you need to …
SQL> Set Severoutput On

PROGRAMMING LOGICS :

In any language programming logic is of three types.
1. Sequence 2. Selection 3. Iteration
SEQUENCE : Executing the program instructions from start to end in a sequence without skipping any statements and without repeated execution of any statements is called as a Sequence.
SELECTION : Executing the program instructions by selection based on a condition is called as Selection. Selection is again three types.
SINGLE ALTERNATIVE : Specify the statement is to be executed when the given condition is true without specifying the statement to be executed when condition is false is called as a Single Alternative.

Syntax : IF Condition THEN
Statements
END IF;

DOUBLE ALTERNATIVE : Specifying the statements to be executed for both true and false stages of a condition is called as double Alternative.
Syntax : IF Condition THEN
Statemetns
Else
Statements
END IF;
MULTIPLE ALTERNATIVE : Implementing the selection logic with more than one condition is called as multiple alternative.

Syntax : IF Condition THEN
Statements
ELSIF Condition THEN
Statements
ELSIF Condition THEN
Statements
:
:
:
ELSE
Statements
END IF;
ITERATION : Executing the program instructions repeatedly until the given condition is false is called as ITERATION or looping.

WHILE :
Syntax : VAR INIT;
WHILE CONDITION
LOOP
Statements
INC/DEC VAR (Increase or Decrease variable )
END LOOP;
LOOP :
Syntax : VAR INIT;
LOOP
Statements
INC/DEC VAR
EXIT WHEN Condition
END LOOP;
FOR :
Syntax : FOR VAR IN SVAL..EVAL ( Starting Value .. Ending Value )
LOOP
Statements
END LOOP;
REVERSE FOR :
Syntax : FOR VAR IN REVERSE SVAL..EVAL
LOOP
Statements
END LOOP;

PL / SQL BLOCKS :

Basic unit of PL/SQL program is called as a BLOCK. A PL/SQL program is a collection of more than one Block. PL/SQL Blocks are classified into four types.

1. ANONYMOUS BLOCKS : A PL/SQL block without a name is called as Anonymous Block.
2. NAMED BLOCKS : A PL/SQL Block is created with a name is called as a NAMED BLOCK.
3. SUB PROGRAMS : Functions, Procedures and Packages of PL/SQL are called as sub
programs.
4. TRIGGERS : Triggers are PL/SQL blocks that are given a name and stored within the database like subprograms. But Triggers are executed implicitly by Oracle depending on
Triggering event without the intervention of user.

ANONYMOUS BLOCKS :
A PL/SQL Block without a name is called as an Anonymous Block. These blocks are generally created once and used once as they are not stored in the database. Syntax for an anonymous block is …
DECLARE
VAR DECLARATIONS
BEGIN
EXECUTABLE STATEMENTS
EXCEPTION
ERROR HANDLING ROUTINES
END;
DECLARE section is used to declare the variables and constants required by the program. BEGIN section is used to write the actual code of the program, exception section is used to write error handling code and the statement END represents the end of the program.

à Write a PL/SQL block that accepts two numbers from user and prints the sum of those numbers.
DECLARE
A NUMBER(5):=&A;
B NUMBER(5):=&B;
C NUMBER(10);
BEGIN
C := A + B;
DBMS_OUTPUT.PUT_LINE( ‘SUM =’ C );
END;

à Write a PL/SQL block that accepts two numbers from user and prints the sum of the two numbers.

DECLARE
A NUMBER(5):=&A;
B NUMBER(5):=&B;
C NUMBER(5);
BEGIN
C:=A+B;
DBMS_OUTPUT.PUT_LINE('SUM IS : 'C);
END;

à Write a PL/SQL block to accept marks in three subjects for a student and display his marks and average marks.

DECLARE
A NUMBER(3):=&A;
B NUMBER(3):=&B;
C NUMBER(3):=&C;
T NUMBER(3);
V NUMBER(5,2);
BEGIN
T := A + B + C;
V := T/3;
DBMS_OUTPUT.PUT_LINE('TOTAL IS : ' T );
DBMS_OUTPUT.PUT_LINE('AVERAGE IS : ' V );
END;

à Write a PL/SQL block to determine whether a given number is even or odd.

DECLARE
A NUMBER(10):=&A;
BEGIN
IF MOD(A,2) = 0 THEN
DBMS_OUTPUT.PUT_LINE( A ' IS AN EVEN NUMBER' );
ELSE
DBMS_OUTPUT.PUT_LINE( A ' IS ODD NUMBER’ );
END IF;
END;

à Write a PL/SQL block to find the big number among the given three numbers.

DECLARE
A NUMBER(10):=&A;
B NUMBER(10):=&B;
C NUMBER(10):=&C;
BEGIN
IF (A>B) AND (A>C) THEN
DBMS_OUTPUT.PUT_LINE( A ' IS THE BIGGEST NUMBER.' );
ELSIF (B>A) AND (B>C) THEN
DBMS_OUTPUT.PUT_LINE( B ' IS THE BIGGEST NUMBER ' );
ELSE
DBMS_OUTPUT.PUT_LINE( C ' IS THE BIGGEST NUMBER ' );
END IF;
END;

à Write a PL/SQL block to print 1 to 10 natural numbers.

DECLARE
A NUMBER(10):=1;
BEGIN
WHILE (A<10)
LOOP
DBMS_OUTPUT.PUT_LINE( A );
A := A + 1;
END LOOP;
END;

à The Same example with FOR Loop

DECLARE
A NUMBER(10):=1;
BEGIN
FOR A IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE( A );
END LOOP;
END;

à The Same example using LOOP

DECLARE
A NUMBER(10):=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE( A );
A := A + 1;
EXIT WHEN A > 10;
END LOOP;
END;

DECLARE
M NUMBER(37);
A NUMBER(37):=&A;
R NUMBER(37):=0;
BEGIN
WHILE A> 0
LOOP
M := MOD(A,10);
R := (R*10)+M;
A := TRUNC(A/10);
END LOOP;
DBMS_OUTPUT.PUT_LINE(R);
END;

à The same program with LOOP.

DECLARE
M NUMBER(5);
A NUMBER(5):=&A;
R NUMBER(5):=0;
BEGIN
LOOP
M=MOD(A,10);
R=R*10+M;
A= TRUNC(A/10);
EXIT WHEN A<>0;
END LOOP;
DBMS_OUTPUT.PUT_LINE(R);
END;

à Write a program to reverse a string.

DECLARE
LEN NUMBER(5);
STR VARCHAR2(20):='&STR';
REV VARCHAR2(10);
BEGIN
LEN:=LENGTH(STR);
WHILE(LEN>0)
LOOP
REV:=REVSUBSTR(STR,LEN,1);
LEN := LEN-1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(REV);
END;

à The same program using LOOP

DECLARE
LEN NUMBER(5);
STR VARCHAR2(20):='&STR';
REV VARCHAR2(10);
BEGIN
LEN:=LENGTH(STR);
LOOP
REV:=REVSUBSTR(STR,LEN,1);
LEN := LEN-1;
EXIT WHEN LEN=0;
END LOOP;
DBMS_OUTPUT.PUT_LINE(REV);
END;

à The same using FOR LOOP

DECLARE
LEN NUMBER(5);
STR VARCHAR2(20):='&STR';
REV VARCHAR2(10);
i NUMBER(10);
BEGIN
LEN:=LENGTH(STR);
FOR I IN REVERSE 1..LEN
LOOP
REV:=REVSUBSTR(STR,I,1);
END LOOP;
DBMS_OUTPUT.PUT_LINE(REV);
END;

NAMED BLOCKS :
The PL/SQL blocks that are given a label prior to the declaration section are called as NAMED BLOCKS. Like ANANYMOUS block NAMED blocks are also not stored within the database. Hence these are also created once and used once.
Syntax :
<>
DECLARE
VAR declaration
BEGIN
EXECUTABLE STATEMENTS
EXCEPTION
ERROR HANDLING ROUTINES
END LABEL

à Write a PL/SQL block that accepts a number from user and prints in the sum of even numbers between 1 and that number.

DECLARE
N NUMBER(10);
NUM NUMBER(10):=&NUM;
EV NUMBER(10):=0;
BEGIN
FOR NUM IN N..NUM
IF EV:=MOD(A,2)=0
SE:=SE+EV;
END IF;
LOOP
DBMS_OUTPUT.PUT_LINE(SE);
END LOOP;
END;

à Write a program to accept a number and find the sum of the numbers from 1 to that number.

DECLARE
A NUMBER(3):=&A;
S NUMBER(10):=0;
I NUMBER(2):=1;
BEGIN
LOOP
S := S + I;
I := I + 1;
EXIT WHEN I > A;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF NUMBERS FROM 1 TO ' A ' IS ' S);
END;

à Write a PL/SQL block that accepts marks of a student in three subjects calculates the total, average and find his grade.

DECLARE
A NUMBER(3):=&A;
B NUMBER(3):=&B;
C NUMBER(3):=&C;
T NUMBER(3);
AV NUMBER(5,2);
BEGIN
T := A + B + C;
AV := T/3;
IF ( AV > 80 ) THEN
DBMS_OUTPUT.PUT_LINE('DISTINCTION WITH ' T ' MARKS AVERAGE OF ' AV);
ELSIF ( AV > 60 ) THEN
DBMS_OUTPUT.PUT_LINE('FIRST CLASS WITH ' T ' MARKS AVERAGE OF ' AV);
ELSIF ( AV > 50 ) THEN
DBMS_OUTPUT.PUT_LINE('SECOND CLASS WITH ' T ' MARKS AVERAGE OF ' AV);
ELSIF ( AV > 35 ) THEN
DBMS_OUTPUT.PUT_LINE('THIRD CLASS WITH ' T ' MARKS AVERAGE OF ' AV);
ELSE
DBMS_OUTPUT.PUT_LINE('SORRY THIS ONE FAILED WITH AN AVERAGE ' AV);
END IF;
END;

à WRITE A PL/SQL BLOCK THAT ACCEPTS A STRING AND A SINGLE CHARACTER FROM USER AND FINDS THE NUMBER OF OCCURENCES OF GIVEN CHARACTER IN THE STRING.( ARM STRONG NUMBER)
DECLARE
NUM NUMBER(10) := &NUM;
A NUMBER(10);
B NUMBER(10);
C NUBMER(10);
SUM NUMBER(10);
BEGIN
a := MOD(NUM,10);
b := MOD(NUM,100)/10;
c := NUM/100;
SUM := POWER(a,3) + POWER(b,3) + POWER(c,3);
IF SUM = NUM
DBMS_OUTPUT.PUT_LINE( ‘ARMSTRONG NUMBER’ );
ELSE
DBMS_OUTPUT.PUT_LINE( ‘NOT ARMSTRONG’ );
END IF;
END;
à PRIME NUMBER
DECLARE
NUM NUMBER(10) := &NUM;
I NUMBER(10) := 2;
F NUMBER(10) ;= 0;
BEGIN
WHILE I < NUM
LOOP
IF MOD(NUM,I) = 0 THEN
F = 1;
END IF;
I = I+1;
END LOOP;
IF F=1 THEN
DBMS_OUTPUT.PUT_LINE( ‘NOT PRIME NUMBER’ );
ELSE
DBMS_OUTPUT.PUT_LINE( ‘PRIME NUMBER’ );
END IF;
END;

à WRITE A PL/SQL BLOCK, GIVEN A STRING, CHARACTER AND FIND THE NO OF OCCURANCES OF THE CHARACTER IN THE STRING.
DECLARE
STR VARCHAR2(10) := ‘&STR’;
CH CHAR(1) := ‘&CH’;
POS NUMBER(2) := 0;
CNT NUMBER(2) := 0;
BEGIN
POS = INSTR(STR, CH, 1, 1 );
WHILE POS > 0
LOOP
CNT := CNT +1;
POS := INSTR( STR, CH, POS+1, 1 );
END LOOP;
DBMS_OUTPUT.PUT_LINE( CNT );
END;

à WRITE A PL/SQL BLOCK TO ACCEPT VALUES FOR SNO, SNAME, COURSE, FEE AND INSERT THOSE VALUES INTO STUDENT TABLE.
DECLARE
SNO NUMBER(10) := &SNO;
SNAME VARCHAR2(10) := ‘&SNAME’;
COURSE VARCHAR2(10) := ‘&COURSE’;
C NUMBER(10) := &FEE;
BEGIN
INSERT INTO STUDENT VALEUS ( SNO, SNAME, COURSE, FEE );
END;

à

CURSORS

2008-07-28T20:20:00.000-07:00

C U R S O R S

Whenever a SQL statement is in process, Oracle allocates a special memory area for that SQL statement. This memory area is called as context area. The rows retrieved by the given select statement are called as ACTIVE SET. Cursor is a pointer to the active set for accessing the rows of active set from PL/SQL.

TYPES OF CURSORS : Cursors are of two types, Explicit cursors and Implicit cursors. A cursor that is created automatically by Oracle when a select statement is executed is called as an IMPLICIT cursors. A cursor that is created and processed by the user is called as an EXPLICIT cursor.
EXPLICIT CURSOR : A cursor i.e. declared by the user is called as an explicit cursor. Explicit cursors are declared by user. The code associated to those cursors must be written by the user. When using an explicit cursor, user has to follow four steps.
1. Cursor Declaration.
2. Opening cursor.
3. Fetching Cursor.
4. Closing Cursor.

1. CURSOR DECLARATION : Cursor declaration assigns a name to the cursor and associates that cursor with a select statement that retrieves the rows, which the cursor has to point.

Syntax : Cursor CursorName is select statement;

2. OPENING CURSOR : A cursor must be opened before accessing the rows pointed by the cursor. During open statement Oracle executes the select statement associated with the cursor and then points to the first row retrieved by the select statement.

Syntax : Open CursorName;

3. FETCHING DATA : During this stage cursor retrieves the values of current record to local variables and then moves the pointer to the immediate next row.

Syntax : Fetch CursorName Into Local variable list;

4. CLOSING CURSOR : A cursor must be closed after completing work with that cursor. During close stage Oracle closes the cursor and releases all memory resources used by the cursor.

Syntax : Close CursorName;

CURSOR ATTRIBUTES :
The keywords of PL/SQL that determine the status of a cursor or the number of rows retrieved by a fetch statement till the time etc. are called as cursor attributes. Cursor attributes in PL/SQL are as follows.
1.%FOUND
2.%NOTFOUND
3.%ISOPEN
4.%ROWCOUNT
1. %FOUND : This attribute returns true if the previously written fetch statement retrieves a row. Otherwise it returns false.

2. %NOTFOUND : This attribute returns true when the previous fetch statement doesn’t return any row. Otherwise it returns false.

3. %ISOPEN : This attribute returns true if the cursor is already opened. Otherwise it returns false.

4. %ROWCOUNT : This attribute returns the number of rows fetched until now.

Write a PL/SQL block to access all rows of EMP table using a cursor and update salary based on following criteria.
SAL >= 5000 300
SAL >= 3000 500
ELSE 750
DECLARE
S EMP.SAL%TYPE;
CURSOR MYCUR IS SELECT EMPNO, SAL FROM EMP;
ENO EMP.EMPNO%TYPE;
BEGIN
OPEN MYCUR;
FETCH MYCUR INTO ENO, S;
WHILE MYCUR %FOUND
LOOP
IF S>=5000 THEN S:=S+300;
ELSIF S>=300 THEN S:=S+500;
ELSE S:=S + 750;
END IF;

UPDATE EMP SET SAL = S WHERE EMPNO = ENO;
FETCH MYCUR INTO ENO, S;
END LOOP;
CLOSE MYCUR;
END;

The same using LOOP

DECLARE
S EMP.SAL%TYPE;
CURSOR MYCUR IS SELECT EMPNO, SAL FROM EMP;
ENO EMP.EMPNO%TYPE;
BEGIN
OPEN MYCUR;
LOOP
FETCH MYCUR INTO ENO, S;
EXIT WHEN MYCUR%NOTFOUND;
IF S >= 5000 THEN S := S + 300;
ELSIF S >= 300 THEN S := S + 500;
ELSE S := S + 750;
END IF;
UPDATE EMP SET SAL = S WHERE EMPNO = ENO;
END LOOP;
CLOSE MYCUR;
END;

CURSOR FOR LOOP

DECLARE
EMPROW EMP%ROWTYPE;
CURSOR MYCUR IS SELECT * FROM EMP;
BEGIN
FOR MYCUR IN EMPROW

LOOP
IF EMPROW.SAL >= 5000 THEN
EMPROW.SAL := EMPROW.SAL + 300;
ELSIF EMPROW.SAL >= 3000 THEN NOT WORKING
EMPROW.SAL := EMPROW.SAL + 500;
ELSE
EMPROW.SAL := EMPROW.SAL + 750;
END IF;
UPDATE EMP SET SAL = EMPROW.SAL WHERE EMPNO = EMPROW.EMPNO;
END LOOP;
END;

Write a PL/SQL block to create a cursor on student table and print all student names who have no dues.

DECLARE
F STUDENT.FEE%TYPE;
CURSOR MYCUR IS SELECT SNO, FEE FROM STUDENT;
SN STUDENT.SNO%TYPE;
BEGIN
OPEN MYCUR;
FETCH MYCUR INTO SN, F;
WHILE MYCUR%FOUND
LOOP
IF F = 0 THEN
DBMS_OUTPUT.PUT_LINE(SN); DON’T KNOW WORKING OR NOT
END IF;
FETCH MYCUR INTO SN, F;
END LOOP;
CLOSE MYCUR;
END;

The same with LOOP

DECLARE
F STUDENT.FEE%TYPE;
CURSOR MYCUR IS SELECT SNAME, FEE FROM STUDENT;
SNA STUDENT.SNAME%TYPE;
BEGIN
OPEN MYCUR;
LOOP
FETCH MYCUR INTO SNA, F;
EXIT WHEN MYCUR%NOTFOUND
IF F = 0 THEN
DBMS_OUTPUT.PUT_LINE(SNA);
END IF;
END LOOP;
CLOSE MYCUR;
END;

DECLARE
EMPROW EMP%ROWTYPE;
CURSOR MYCUR IS SELECT * FROM EMP;
BEGIN
FOR MYCUR IN EMPROW
LOOP
IF EMPROW.SAL >= 5000 THEN
EMPROW.SAL := EMPROW.SAL + 300;
ELSIF EMPROW.SAL >= 3000 THEN
EMPROW.SAL := EMPROW.SAL + 500;
ELSE
EMPROW.SAL := EMPROW.SAL + 750;
END IF;
UPDATE EMP SET SAL = EMPROW.SAL WHERE EMPNO = EMPROW.EMPNO;
END LOOP;
END;

PARAMETERISD CURSORS
We can also pass parameters to a cursor and select data based on that parameters. To declare a cursor as parameterized cursor syntax is as follows…
Syntax: CURSOR CURNAME(PARAM1 DATATYPE, PARAM2 DATATYPE,… PARAM N DATATYPE ) IS SELECT STATEMENT;

Write a PL/SQL block that accepts a course from user and displays the names of all students who has due in that course.

DECLARE
C STUDENT.COURSE%TYPE;
SN STUDENT.SNAME%TYPE;
CURSOR MYCUR(PC STUDENT.COURSE%TYPE) IS SELECT SNAME, FEE FROM STUDENT WHERE COURSE = PC;
F STUDENT.FEE%TYPE;
BEGIN
C := ‘COURSE’;
OPEN MYCUR(C);
FETCH MYCUR INTO SN, F;
WHILE MYCUR%FOUND
LOOP
IF F > 0 THEN
DBMS_OUTPUT.PUT_LINE(SN);
END IF;
FETCH MYCUR INTO SN, F;
END LOOP;
CLOSE MYCUR;
END;

FOR UPDATE WITH CURSORS : Whenever we are opening a cursor, a snapshot of the corresponding table is created as active set. Any changes made to the table that are committed before opening the cursors will reflect in the active set. If any changes are made to the table after opening the cursor will not reflect in the active set even they are committed.
To avoid this problem we can use for update clause at the end of the select statement written for the cursor. Because of this the rows retrieved by the select statement associated with cursor will be locked and will not allow any changes in the table by other users. This lock will be released when cursor is closed.
If any other user has locked the table or same rows retrieved by a cursor prior to opening the cursor with for update clause will hang the system and it will wait for the user to release the locks. To avoid system hanging you can use NOWAIT clause along with for update.
Example :
C STUDENT.COURSE%TYPE;
SN STUDENT.SNAME%TYPE;
CURSOR MYCUR(PC STUDENT.COURSE%TYPE) IS
SELECT SNAME, FEE FROM STUDENT WHERE COURSE = ‘PC’ FOR UPDATE NOWAIT;
F STUDENT.FEE%TYPE;
- - - - - -
- - - - - -
END;

SUB PROGRAMS, PROCEDURES, FUNCTIONS

2008-07-27T18:57:00.000-07:00

S U B P R O G R A M S
The PL/SQL blocks discussed until now are called as Anonymous blocks or Named blocks. These blocks are not stored within the database and hence they are compiled each time they are executed and they can’t be called from any other PL/SQL block. Procedures and Functions in PL/SQL are the named blocks that are stored within the database. They can be called from other PL/SQL blocks and these are combinely called as SUBPROGRAMS.
Functions and Procedures are similar to functions and Procedures of other third generation languages. The difference between a Function and Procedure is a function must return a value while a Procedure must not return a value.
FUNCTIONS :
Function is a PL/SQL block that performs a specified task and returns a value to the user.

For creating a Function the following syntax is used.
Syntax :
CREATE OR REPLACE FUNCTION FUNCTIONNAME ( ARG1 IN/OUT/IN OUT DATATYPE, ARG2 IN/OUT/IN OUT DATATYPE, … , ARG N INM/OUT/IN OUT DATATYPE ) RETURN TYPE IS/AS
VARIABLE DECLARATION
BEGIN
EXECUTABLE STATEMENTS
EXCEPTION
ERROR HANDLING ROUTINES
END FUNCTIONNAME;

MODES OF PARAMETERS : To a function or procedure in PL/SQL, parameters can be passed in three ways. They are In, OUT and IN OUT. The purpose of these modes is as follows.

1.When a parameter is passed in “ IN ” mode then the value of that parameter is read only within that function or procedure.

2.When a parameter is passed in “ OUT ” mode then that parameter is write only within that function or procedure. Any changes made to the “ OUT ” parameter within a function or procedure will automatically change the value of its corresponding argument.

3.When a parameter is passed in “ IN OUT ” mode then that parameter can be read and write within that function or procedure. Like OUT parameter any changes made to the IN OUT parameter will automatically reflect in corresponding argument.

CREATE OR REPLACE FUNCTION TESTMODE ( A_IN IN NUMBER, A_OUT OUT NUMBER, A_INOUT IN OUT NUMBER ) RETURN BOOLEAN IS TEMP NUMBER(3);
BEGIN
TEMP := A_IN; VALID
A_IN := TEMP; INVALID
A_OUT := TEMP; VALID
TEMP := A_OUT; INVALID
A_INOUT := TEMP; VALID
TEMP := A_INOUT; VALID
RETURN(TRUE);
END TESTMODE;

NOTE : A function or procedure created with OUT or IN OUT parameters must be called from another PL/SQL block using variables as arguments in function or procedure call corresponding to OUT and IN OUT parameters.

SOME PROGRAMMES ARE TO BE WRITTEN AS EXAMPLES.

POSITIONAL AND NAMED NOTATIONS : CREATE OF REPLACE FUNCTION TOTAL
( M1 NUMBER(10),
M2 NUMBER(10),
M3 NUMBER(10)) RETURN NUMBER IS
BEGIN
RETURN( M1 + M2 + M3 );
END;

If we take the above function as an example, it can be called by using following statement.
T := TOTAL(10, 20, 30 );
When the total function is called by using the above syntax, the value 10 is passed to the parameter M1, 20 is passed to M2 and the value 30 is passed to the third parameter M3. This type of argument passing is called as “ Positional Notation ” as argument is passed to the parameter depending on their position.
The same example can be called by using the following statement.
T := TOTAL( M1 => 10, M3 => 30, M2 => 20 );
When the function “TOTAL” is called by using the above statement, value 10 is assigned to the parameter M1, 20 is assigned to M2 and 30 to M3. Notice that even the value 20 is in third position it is assigned to M2. This type of argument passing is called “ NAMED NOTATION ” as values are passed depending on the name of the parameters.

DEFAULT VALUES FOR PARAMETERS :
We can assign default values to the parameters during the creation of procedure or function itself. When default values are specified for parameters then passing values to those parameters during function or procedure call becomes optional.

SQL> CREATE OR REPLACE FUNCTION TOTAL(M1 NUMBER, M2 NUMBER, M3 NUMBER, M4 NUMBER := 0, M5 NUMBER := 0 ) RETURN IS
BEGIN
RETURN ( M1 + M2 + M3 + M4 + M5 );
END;

In the above function TOTAL, last two parameters m4, m5 are given the default value 0.Hence during the total function call, passing values to these two parameters is optional. Hence we use the same TOTAL function for calculating totals of either three numbers or four numbers or five numbers. If the total function has M1 as default parameter and M2 and M3 are compulsory, again M4 as default and M5 as compulsory then to call the total function with three values we must use named notation as follows.

T := TOTAL( M2 => 50, M3 => 40, M5 => 60 );

P R O C E D U R E S
Procedures are the PL/SQL blocks that performs the action specified by the user but doesn’t return any value. Procedures are also subprograms like functions. The main difference between Function and Procedure is a function should return a value while a procedure must not. A procedure is created by using the following Syntax.

Syntax :

CREATE OR REPLACE PROCEDURE PNAME ( PARAM 1 IN/OUT/IN OUT DATATYPE,
PARAM 2 IN/OUT/IN OUT DATATYPE, … …, PARAM N IN/OUT/IN OUT DATATYPE) IS / AS
LOCAL VARABLE DECLARATION
BEGIN
EXECUTABLE STATEMENTS
EXCEPTION
ERROR HANDLING ROUTINES
END PROCEDURE.

Write a Procedure to accept the details of a student and insert then into student table.

CREATE OR REPLACE PROCEDURE STUPRO( S_SNO STUDENT.SNO%TYPE, S_SNAME STUDENT.SNAME%TYPE, S_COURSE STUDENT.COURSE%TYPE) AS
BEGIN
INSERT INTO STUDENT VALUES(S_SNO, S_SNAME, S_COURSE);
END STUPRO;

If we want to call the procedure then we follow as follows…
SQL> EXECUTE STUPRO(1, ‘RAVI’, ‘ORACLE’ );

Write a procedure to accept marks in three subjects, calculate total, average, grade and insert those values into main table.

CREATE OR REPLACE PROCEDURE FIND
( S_M1 NUMBER, S_M2 NUMBER, S_M3 NUMBER ) AS
( S_TOT NUMBER(5), S_AVG NUMBER(5,2), S_GRADE VARCHAR2(10) )
BEGIN
S_TOT := TOTAL( S_M1, S_M2, S_M3, S_TOT, S_AVG, S_GRADE );
INSERT INTO STUDENT VALUES ( S_M1, S_M2, S_M3,S_TOT,S_AVG,S_GRADE);

LOCAL SUBPROGRAMS
A function or procedure can be declared within the declaration section of another function or procedure. When a function or procedure is declared within another function or procedure then those functions and procedures are called as “LOCAL SUB PROGRAMS”. Local sub programs can be used within the function or procedure in which they are declared. But they can’t be used outside that procedure or function.

à CREATE OR REPLACE PROCEDURE ADDMARKS( P_M1 MARKS.M1%TYPE,
P_M2 MARKS.M2%TYPE, P_M3 MARKS.M3%TYPE, P_SNO MARKS.SNO%TYPE ) AS

FUNCTION TOTAL ( M1 NUMBER, M2 NUMBER, M3 NUMBER ) RETURN NUMBER IS
BEGIN
RETURN ( M1 + M2 + M3 );
END TOTAL;

FUNCTION AVEG ( T NUMBER ) RETURN NUMBER IS
BEGIN
RETURN(T/3);
END AVEG;

FUNCTION GRADE ( A NUMBER ) RETURN VARCHAR2 IS
G VARCHAR2(15);
BEGIN
IF A > 80 THEN G := ‘DISTINCTION’ ;
ELSIF A > 60 THEN G := ‘FIRST ’;
ELSIF A > 50 THEN G := ‘SECOND’;
ELSIF A > 35 THEN G := ‘THIRD’;
ELSE G := ‘FAIL’;
END IF;
RETURN(G);
END GRADE;

TOT NUMBER(5);
A NUMBER;
G VARCHAR2(15);
BEGIN
TOT := TOTAL( P_M1, P_M2, P_M3 );
A := AVEG(TOT);
G := GRADE(A);
INSERT INTO MARKS VALUES( P_SNO, P_M1, P_M2, P_M3, TOT, A, G );
END ADDMARKS;

PACKAGES IN PL-SQL

2008-07-27T18:52:00.001-07:00

P A C K A G E S
Packages are the third type of sub programs after functions and procedures. Like procedures and functions packages are also stored in the database. But unlike functions and procedures packages should not be used as local sub programs.
A package consists of two parts, package specification and package body. Package specification consists of only the declaration of variables, functions and procedures that can be used globally.
Package body consists of definitions for the functions and procedures that are declared within the package specification.
The main purpose of a package is to group the related subprograms. As variables can also be declared within a package specification, another purpose of package is to declare the variables and cursors that can be used globally.

PACKAGE SPECIFICATION : Package specification of a package consists of the variables, cursors, declarations that can be used globally and the declarations of functions and procedures to be included within the package. A package specification was created by using the following syntax.
Syntax: SQL> CREATE OR REPLACE PACKAGE PACKAGENAME IS/OR
VAR DECLARATIONS
CURSOR DECLARATIONS
FUNCTION DECLARATIONS
PROCEDURE DECLARATIONS
END PACKAGENAME;

EX : CREATE OR REPLACE PACKAGE STUPACK AS
PROCEDURE ADDSTUDENT ( P_SNO STUDENT.SNO%TYPE,
P_SNAME STUDENT.SNAME%TYPE,
P_COURSE STUDENT.COURSE%TYPE);
PROCEDURE DELSTUDENT ( P_SNO STUDENT.SNO%TYPE);
END STUPACK;

PACKAGE BODY : Package body contains definitions for the functions and procedures declared in the package specification. Package specification and package body name must be same.

Package body creation has the following syntax.
Syntax : CREATE OR REPLACE PACKAGE BODY PBNAME IS/AS
FUNCTION FUNCTIONNAME( ARGU.LIST ) RETURN TYPE IS
LOCAL VARIABLE DECLARATION
BEGIN
EXECUTABLE CODE
EXCEPTION
ERROR HANDLING
END ENAME;
PROCEDURE PNAME ( ARGU.LIST ) IS/AS
LOCAL VARIABLE DECLARATION
BEGIN
EXECUTABLE CODE
EXCEPTION
ERROR HANDLING
END PNAME.
.
.
.
END PBNAME
EXAMPLE : CREATE OR REPLACE PACKAGE BODY STUPACK AS
PROCEDURE ADDSTUDENT ( P_SNO STUDENT.SNO%TYPE,
P_SNAME STUDENT.SNAME%TYPE,
P_COURSE STUDENT.COURSE%TYPE ) IS
BEGIN
INSERT INTO STUDENT VALUES ( P_SNO, P_SNAME, P_COURSE );
END ADDSTUDENT;
PROCEDURE DELSTUDENT ( P_SNO STUDENT.SNO%TYPE ) IS
BEGIN
DELETE FROM STUDENT WHERE SNO=P_SNO;
END DELSTUDENT;
END STUPACK;

Write a package that includes function for calculating total, average and grade and procedure to insert a record into marks table.
CREATE OR REPLACE PACKAGE.STURECORD AS
FUNCTION TOTAL ( S_M1 STUDENT.MM%TYPE,
S_M2 STUDENT.MP%TYPE) RETURN NUMBER IS;
FUNCTION AVERAGE( S_TOT STUDENT.TOTAL%TYPE ) RETURN NUMBER IS;
FUNCTION GRADE ( S_AVERAGE STUDENT.AVERAGE%TYPE) RETURN VARCHAR2 IS;
PROCEDURE INSERTRECORD ( S_SNO STUDENT.SNO%TYPE,
S_M1 STUDENT.MM%TYPE,
S_M2 STUDEMT.MP%TYPE);
END STURECORD;
PACKAGE BODY :
CREATE OR REPLACE PACKAGE BODY STURECORD AS
FUNCTION TOTAL ( S_M1 STUDENT.MM%TYPE,
S_M2 STUDENT.MP%TYPE ) RETURN NUMBER IS
BEGIN
RETURN ( S_M1 + S_M2 );
END TOTAL;
FUNCTION AVERAGE ( S_TOT STUDENT.TOTAL%TYPE ) RETURN NUMBER IS
BEGIN
RETURN ( S_TOT/2 );
END AVERAGE;
FUNCTION GRADE ( S_AVERAGE STUDENT.AVG%TYPE ) RETURN VARCHAR2 IS
GRADE VARCHAR2(10);
BEGIN
IF S_AVERAGE > 90 THEN GRADE = ‘FIRST’;
ELSIF S_AVERAGE > 60 THEN GRADE = ‘SEC’;
ELSE GRADE = ‘FAIL’;
END IF;
RETURN GRADE;
END GRADE;
PROCEDURE INSERTRECORD ( S_SNO STUDENT.SNO%TYPE,
S_M1 STUDENT.MM%TYPE,
S_M2 STUDENT.MP%TYPE ) IS
TOT NUMBER(10);
GRADE VARCHAR2(10);
AVG NUMBER(10);
BEGIN TOT := STURECORD.TOTAL( S_M1, S_M2 );
AVG := STURECORD.AVERAGE(TOT);
GRADE := STURECORD.GRADE( AVG );
INSERT INTO STUDENT VALUES ( S_SNO, S_M1, S_M2, TOT, AVG, GRADE );
END INSERTRECORD;
END STURECORD;

OVERLOADING SUBPROGRAMS
We can crate more than one functionor procedure within a package with same name. When more than one function or procedure within a package are created with same name then those functions or procedures are said to be overloaded. The overloaded functions or procedures may have the same name, but the number of arguments and their datatypes may differ.
EXAMPLE : CREATE OR REPLACE PACKAGE OVERLOADED AS
PROCEDURE ADDSTUDENT ( P_SNO STUDENT.SNO%TYPE,
P_SNAME STUDENT.SNAME%TYPE,
P_COURSE STUDENT.COURSE%TYPE );
PROCEDURE ADDSTUDENT (P_SNO STUDENT.SNO%TYPE,
P_MM STUDENT.MM%TYPE,
P_MP STUDENT.MP%TYPE );
END OVERLOAD;
EXAMPLE : CREATE OR REPLACE PACKAGE BODY OVERLOADED IS
PROCEDURE ADDSTUDENT ( P_SNO STUDENT.SNO%TYPE,
P_SNAME STUDENT.SNAME%TYPE,
P_COURSE STUDENT.COURSE%TYPE )
BEGIN
INSERT INTO STUDENT VALUES ( P_SNO, P_SNAME, P_COURSE );
END ADDSTUDENT;
PROCEDURE ADDSTUDENT (P_SNO STUDENT.SNO%TYPE,
P_M1 STUDENT.MM%TYPE,
P_M2 STUDENT.MP%TYPE ) IS
TOT NUMBER(5);
A NUMBER(5,2);
G VARCHAR2(10);
BEGIN
TOT := TOTAL( P_M1, P_M2 );
A := AVERAGE( TOT );
G := GRADE(A);
INSERT INTO STUDENT VALUES ( P_SNO, P_M1, P_M2, TOT, A, G );
END ADDSTUDENT;
END OVERLOAD;

OVERLOADING IS NOT POSSIBLE IN FOLLOWING SITUTATIONS

1.When the number of arguments and the datatypes are same, but the modes of parameters are different.
Ex : PROCEDURE TEST( A1 IN NUMBER );
PROCEDURE TEST( A1 OUT NUMBER );
2.Overloading is not possible when the functions are differed only in their return type.
Ex: FUNCTION TEST ( A1 IN NUMBER ) RETURN NUMBER;
FUNCTION TEST ( A2 IN NUMBER ) RETURN VARCHAR2;
3.Overloading is not possible when the parameters are different in their datatype and those datatypes are of same family.
Ex : FUNCTION TEST ( A VARCHAR2 ) RETURN NUM;
FUNCTION TEST ( A CHAR ) RETURN NUM;

EXCEPTIONS IN PL-SQL

2008-07-27T18:43:00.000-07:00

TYPES OF EXCEPTIONS
Exceptions in PL/SQL are classified into predefined exceptions and user defined exceptions. The exceptions that are already defined by Oracle are called as Predefined exceptions and the exceptions that are created by the user are called as User Defiend exceptions.
Predefiend Exceptions : Predefiend exceptions are the exceptions that are already defiend by Oracle, and can be used directly with out any coresponding declaration statement. Predefiend exceptions and their purpose are as follows.

1.DUP_VAL_ON_INDEX : This exception is raised when duplicate values are inserted into primary key or unique key columns.

DECLARE
DNO NUMBER(10):=&DN;
DNA VARCHAR2(10):=‘&DNA’;
L VARCHAR2(10):= ‘&L’;
BEGIN
INSERT INTO DEPT VALUES ( DNO, DNA, L);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE( ‘Duplicate value given to primary key column’ );
End;
2.NO_DATA_FOUND : This exception is raised when a select statement doen’t return any value.

DECLARE
J EMP.JOB%TYPE;
ENO EMP.EMPNO%TYPE := ‘&ENO’;
BEGIN
SELECT JOB INTO J FROM EMP WHERE EMPNO=ENO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘EMPNO NOT FOUND.’);
END;

3.TOO_MANY_ROWS : This exception is raised when more than one rows are returned by a select statement.
DECLARE
J EMP.JOB%TYPE;
EN EMP.ENAME%TYPE := ‘&ENAME’;
BEGIN
SELECT JOB INTO J FROM EMP WHERE EMPNO=ENO;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE( ‘MORE THAN ONE RECORD RETURNED’ );
END;
4.INVALID_ NUMBER : This excepton is raised when a non numeric is assigned to a numeric column.
DECLARE
BEGIN
INSERT INTO EMP VALUES ( ‘AA’, ‘DNAME’, ‘LOC’ );
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE(‘NON NUMERIC VALUE IS GIVEN TO COLUMN’);
END;
5.VALUE_ERROR : This exception is raised when a value larger than width of a variable s assigned to a variable.
DECLARE
C VARCHAR2(3);
BEGIN
C:= ‘ABCD’;
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE(‘Value larger than specified width’);
END;
6.INVALID_CURSOR : This exception will be raised when an invalid cursor operation is performed.
DECLARE
CURSOR CDEPT IS SELECT * FROM DEPT;
DNO DEPT.DNO%TYPE;
DN DEPT.DN%TYPE;
L DEPT.LOC%TYPE;
BEGIN
FETCH CDEPT INTO DNO, DN, L;
EXCEPTION
WHEN INVALID_CURSOR THEN
DBMS_OUTPUT.PUT_LINE(‘INVALID OPERATION ON CURSOR’ );
END;

7.CURSOR_ALREADY_OPEN : This exception will be raised when an User tries to open a cursor that is already open.
DECLARE
CURSOR CDEPT IS SELECT * FROM DEPT;
BEGIN
OPEN CDEPT;
OPEN CDEPT;
EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN
DBMS_OUTPUT.PUT_LINE(‘CURSOR IS ALREADY OPENED’ );
END;
8.ZERO_DIVIDE : This exception will be raised when a division is made by using Zero.
9.STORAGE ERROR : This exception will be raised when PL/SQL runs out of memory.
10.NOT_LOGGED_ON : This exception will be raised when you tried to execute an SQL statement against the database and database was not ready.
11.LOGON_DENIED : This exception will be raised when an invalid username and password are given.

USER DEFINED EXCEPTIONS
Oracle also provides the ability to create users own exceptions. For this user has to declare an exception variable in Declaration section. After declaring an exception user has to raise it manually depending on a condition by using raised statement.
DECLARATION : Exception_Variable Exception;
RAISING : Raise Exception_Variable;
The following PL/SQL block created an user defined exception for handling the error raised when user inserts a foreign key violating value into deptno column of EMP table.
DECLARE
ENO EMP.ENO%TYPE := &ENO;
EN EMP.ENAME%TYPE := ‘&EN’;
J EMP.JOB%TYPE := ‘&J’;
M EMP.MGR%TYPE := ‘&M’;
HD EMP.HIREDATE%TYPE := ‘&HD’;
S EMP.SAL%TYPE := &S;
C EMP.COMM%TYPE := &C;
DNO EMP.DEPTNO%TYPE := &DNO;
DNOFK EXCEPTION;
BEGIN
IF DNO > 40 THEN
RAISE DNOFK;
END IF;
INSERT INTO EMP VALUES ( ENO, EN, J, M, HD, S, C, DNO );
EXCEPTION
WHEN DNOFK THEN
DBMS_OUTPUT.PUT_LINE(‘DEPTNO VALUES MUST BE 20, 30 OR 40’ );
END;
“OTHERS” EXCEPTION HANDLER :The “OTHERS” handler will execute for all raised exceptions it should always be the last handler in the block. It is good programming practice that an others handler is placed at the top level of your program to ensure that no errors go and detected.
SQL CODE AND SQL-ERRORS : Inside an “OTHERS” handler, it is often useful to know which Oracle error raised the exception. One reason would be to log which error occurred rather than the fact that an error happened. PL/SQL provides this information via to built in function, SQL code and SQLERRM. SQL code returns the current error code and SQL-Errm returns the current error message text.
DECLARE
ENO EMP.ENO%TYPE := &ENO;
EN EMP.ENAME%TYPE := ‘&EN’;
J EMP.JOB%TYPE := ‘&J’;
M EMP.MGR%TYPE := ‘&M’;
HD EMP.HIREDATE%TYPE := ‘&HD’;
S EMP.SAL%TYPE := &S;
C EMP.COMM%TYPE := &C;
DNO EMP.DEPTNO%TYPE := &DNO;
DNOFK EXCEPTION;
BEGIN
IF DNO > 40 THEN
RAISE DNOFK;
END IF;
INSERT INTO EMP VALUES ( ENO, EN, J, M, HD, S, C, DNO );
EXCEPTION
WHEN DNOFK THEN
DBMS_OUTPUT.PUT_LINE(‘DEPTNO VALUES MUST BE 20, 30 OR 40’ );
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE( ‘DUPLICATE VALUES GIVEN TO EMP’ );
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE( ‘VALUE LONGER THAN SIZE OF COLUMN’ );
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( SQL CODE ’ : ’ SQLERRM );
END;

TRIGGERS

2008-07-27T18:30:00.000-07:00

TRIGGERS

Triggers are PL/SQL blocks like functions, procedures, packages. Like procedures and functions triggers are also stored within the database. Like packages they doesn’t receive any arguments and they can’t be used as local blocks. Procedures and functions must be called externally by the user. But triggers are automatically executed depending on triggering event. Triggering event are the DML operations and executing a trigger is called as trigger firing.
Triggers are mostly used for the following purposes.
To define the complex constraints those are not possible to define at the time of table creation.
To record the auditing information of table i.e. changes made to the table and who made those changes.
To give instructions to other programs depending on the situtation.

CREATING A TRIGGER :
Create trigger is used to create a trigger and it has the following syntax.
à Syntax : CREATE OR REPLACE TRIGGER TNAME
BEFORE/AFTER INSERT OR UPDATE OR DELETE
ON TABNAME
[ FOR EACH ROW ]
[ WHEN CONDITION ]
DECLARE
VAR DECLARATION
BEGIN
EXECUTABLE STATEMENTS;
EXCEPTION
ERROR HANDLING ROUTINES;
END TNAME;

Second line of the syntax specifies the triggering event when the trigger has to fire. This includes BEFORE / AFTER clause and one or more DML operations. For example if you write triggering event as BEFORE INSERT. Then the trigger is fired before inserting a row into the table.
Third line of syntax specifies the table name on which trigger has to create.
For EACH ROW clause specifies that the trigger is fired for each row which is affected by the DML operations. If we exclude EACH ROW clause then the trigger is fired only once for all rows that are affected by the DML operation.
WHEN CONDITION clause specifies a condition when trigger has to fire during the triggering event and is optional.

TYPES OF TRIGGERS :

Triggers are classified into row level triggers and statement level triggers depending on the number of times trigger executes during the triggering event. The triggers that are executed once for each row affected by the DML operations are called as ROW LEVEL TRIGGERS. The triggers that are executed onlyonce for all rows affected by the DML operation are called as STATEMENT LEVEL TRIGGERS.
Depending on the triggering event triggers are classified into fourteen types.

BEFORE INSERT - ROW LEVEL
BEFORE UPDATE - ROW LEVEL
BEFORE DELETE - ROW LEVEL
BEFORE INSERT - STATEMENT LEVEL
BEFORE UPDATE - STATEMENT LEVEL
BEFORE DELETE - STATEMENT LEVEL
AFTER INSERT - ROW LEVEL
AFTER UPDATE - ROW LEVEL
AFTER DELETE - ROW LEVEL
AFTER INSERT - STATEMENT LEVEL
AFTER UPDATE - STATEMENT LEVEL
AFTER DELETE - STATEMENT LEVEL
INSTEAD OF ( ORACLE 8.0 ) - ROW LEVEL
INSTEAD OF - STATEMENT LEVEL

Write a trigger that restricts the user from making any DML operations on SUNDAY.
CREATE OR REPLACE TRIGGER NOTRANS
BEFORE UPDATE OR DELETE OR INSERT
ON EMP
DECLARE
W VARCHAR2(10) := TO_CHAR( SYSDATE, ‘DY’ );
BEGIN
IF W = ‘SUN’ THEN
RAISE_APPLICATION_ERROR( -20000, ‘ NO TRANSACTION IS ALLOWED ON SUNDAY’ );
END IF;
END NOTRANS;

BIND VARIABLES OR PSEUDO RECORDS :
Two pseudo records :OLD and :NEW can be used with row level triggers. :OLD is used to access the values of a record before it is updated and :NEW is used to access the values of a record after update.
:OLD and:NEW are worked with DML operations as follows.

INSERT
UPDATE
DELETE
:OLD
NOT POSSIBLE
POSSIBLE
POSSIBLE
:NEW
POSSIBLE
POSSIBLE
NOT POSSIBLE

Write a trigger to record the auditing information of DEPT table.

CREATE OR REPLACE TRIGGER BEFUPD
BEFORE UPDATE ON DEPT
FOR EACH ROW
BEGIN
INSERT INTO AUDITDEPT VALUES ( :NEW.DEPTNO, :NEW.DNAME,
:NEW.LOC, :OLD.DEPTNO, :OLD.DNAME, :OLD.LOC,
‘ UPDATED BY ’ USER ‘ ON ’ TO_CHAR (SYSDATE, ‘DD-MON-YY
HH.MI.SS.AM’ );
END;

CREATE OR REPLACE TRIGGER BFINSERT
BEFORE INSERT ON DEPT
FOR EACH ROW
BEGIN
INSERT INTO AUDITDEPT
( DEPTNO, DNAME, LOC, REMARKS ) VALUES ( :NEW.DEPTNO, :NEWDNAME, :NEW.LOC, ‘INSERTED BY’ USER ‘ON’ TO_CHAR ( SYSDATE, ‘DD-MON-YYHH.MI.SSAM ’ );
END BFINSERT;

CREATE OR REPLACE TRIGGER BDELETE
BEFORE DELETE
ON EMP
FOR EACH ROW
BEGIN
INSERT INTO AUDITDEPT( ODEPTNO, ODNAME, OLOC ) VALUES
( :OLD.DEPTNO, :OLD.DNAME, :OLD.LOC, ‘DELETED BY’ USER ‘ON’ TO_CHAR( SYSDATE, ‘DD-MON-YY HH-MI-SS’ ));
END BDELETE;

TRIGGERING PREDICATES :
When a trigger is written with more than one triggering event then we can identify the triggering event based on which the trigger was fired by using the the triggering predicates. Triggering predicates are as follws.
INSERTING.
DELETING.
UPDATING.

CREATE OR REPLACE TRIGGER AUDITDEPT
BEFORE INSERT OR UPDATE OR DELETE
ON DEPT
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO AUDITDEPT ( DEPTNO, DNAME, LOC, REMARKS ) VALUES
( :NEW.DEPTNO, :NEW.DNAME, :NEW.LOC, ‘INSERTED BY’ USER ‘ON’ TO_CHAR(SYSDATE, ‘DD-MON-YY HH.MI.SS AM’ ));
ELSIF UPDATING THEN
INSERT INTO AUDITDEPT VALUES ( :NEW.DEPTNO, :NEW.DNAME, :NEW.LOC, :OLD.DEPTNO, :OLD.DNAME, :OLD.LOC, ‘UPDATED BY’ USER ‘ON’ TO_CHAR( SYSDATE, ‘DD-MON-YY HH.MM.SS’ ));
ELSE
INSERT INTO AUDITDEPT ( ODEPTNO, ODNAME, OLOC, REMARKS ) VALUES
( :OLD.DEPTNO, :OLD.DNAME, :OLD.LOC, ‘DELETED BY’ USER ‘ON’ TO_CHAR( SYSDATE, ‘DD-MON-YY HH.MM.SS AM’ ));
END IF;
END;

Write a trigger that restricts the user from inserting a row into EMP table if the commission is more than 30% of the salary.

CREATE OR REPLACE TRIGGER COMMISSION
BEFORE INSERT OR UPDATE
ON EMP
FOR EACH ROW
BEGIN
IF :NEW.COMM >= (:NEW.SAL*30/100) THEN
RAISE_APPLICATION_ERROR( -20002, ‘COMM MUST BE LESS THAN 30% OF THE SALARY’ );
END IF;
END;

INSTEAD OF TRIGGERS : Instead of triggers are used to perform an action against a table in stead of the original operation performed by the user.

CREATE OR REPLACE VIEW EMPDEPT
AS
SELECT EMPNO, ENAME, JOB, HIREDATE, D.DEPTNO, DNAME, LOC FROM
EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

CREATE OR REPLACE TRIGGER INSTEAD
INSTEAD OF INSERT
ON EMPDEPT
FOR EACH ROW
BEGIN
INSERT INTO EMP VALUES ( :NEW.ENAME, :NEW.EMPNO, :NEW.LOC, :NEW.MGR, :NEW.HIREDATE, :NEW.SAL, :NEW.COMM, :NEW.DEPTNO );
END INSTEAD;

DATA DICTIONARY VIEWS ON TRIGGERS :
A list of triggers, event of those triggers the table name on which the trigger was created etc. can be get from the Data Dictionary View.
USER_TRIGGERS :
SEELCT * FROM USER_TRIGGERS;
DISABLE / ENABLE THE TRIGGERS : Wecan disable or enable the trigger by using the follwing syntax.
à ALTER TRIGGER TNAME ENABLE / DISABLE;
Example : Disable the trigger instins.
SQL> ALTER TRIGGER INSTINS;
We can also enable or disable all triggers written on a table at a time by using the follwing syntax.
SQL> ALTER TABLE TABAME DISABLE / ENABLE ALL TRIGGERS;
Example : The following statement disables all triggers on EMP table.
SQL> ALTER TABLE EMP DISABLE ALL TRIGGERS;

DELETING A TRIGGER :
SYNTAX : SQL> DROP TRIGGER TNAME;

ORDER OF TRIGGER FIRING :
BEFORE STATEMENT LEVEL
BEFORE ROW LEVEL
AFTER ROW LEVEL
AFTER STATEMENT LEVEL
RESTRICTIONS ON TRIGGERS :
1.A trigger may not issue any transaction control statements, COMMIT, ROLLBACK or SAVEPOINT.
2.Any procedures or functions that are called by the trigger body can not issue any transaction control statements.
3.The trigger body can not declare any LONG or RAW variables also :NEW and :OLD can not refer to a LONG or a LONGRAW column in the table for which the trigger is defines.

Triggers in forms are fired depending on the user made action on them. The difference between trigger in forms and database triggers is the database triggers are fired depending on the DML events, while triggers in forms are fired depending on the user made action against the objects triggers in foms are classiffied into the following seven types.
1. Interface Event Triggers.
2. Mouse Event Triggers
3. Transactional Triggers
4. Query Processing Triggers
5. Navigational Triggers
6. New Object Triggers
7. Validation Triggers

1. INTERFACE EVENT TRIGGERS : The triggers that are fired, based on the user made action on the interface of the items are called as Interface Event Triggers.

a) WHEN_BUTTON_PRESSED : This trigger will be fired whenever user clicks on a push button.
b) WHEN_CHECKBOX_CHECKED : This trigger will be fired whenever user clicks on a checkbox at runtime.
c) WHEN_RADIO_CHANGED : This trigger will be fired whenever user selects a new radio button within the radio group.
d) WHEN_LIST_CHANGED : This trigger will be fired whenever user selects a new element within a list item.
e) WHEN_LIST_ACTIVATED : This trigger will be fired whenever user activates a list item and this trigger is fired only for the list items of style Tlist.
f) WHEN_IMAGE_PRESSED : This trigger will be fired whenever user clicks on an image item.
g) WHEN_IMAGE_ACTIVATED : This trigger will be fired whenever user double clicks an image item.
h) WHEN_TIMER_EXPIRED : This trigger will be fired whenever the user created timer expires.

2. MOUSE EVENT TRIGGERS : The triggers that are fired based on the user made action on the items using mouse are called as Mouse Event Triggers.

a) WHEN_MOUSE_ENTER : This trigger will be fired whenever the mouse pointer enters into an item.
b) WHEN_MOUSE_LEAVE : This trigger will be fired whenever the mouse pointer leaves an item.
c) WHEN_MOUSE_MOVE : This trigger will be fired whenever the mouse pointer is moved over an item.
d) WHEN_MOUSE_DOWN : This trigger will be fired whenever user pressed the mouse button.
e) WHEN_MOUSE_UP : This trigger will be fired whenever user releases the mouse button.
f) WHEN_MOUSE_CLICK : This trigger will be fired whenever user clicks on an item.
g) WHEN_MOUSE_DOUBLECLICK : This trigger will be fired whenever user double clicks on an item.

3. TRANSACTIONAL TRIGGERS : The triggers that are fired depending on the DML operation performed by the user are called as Transactional Triggers.

a) PRE_INSERT : This trigger will be fired just before inserting a record into the database.
b) POST_INSERT : This trigger will be fired just after a record is inserted into the database.
c) ON_INSERT : This trigger will be fired to replace the default behaviour of forms for insertion.
d) PRE_UPDATE : This trigger will be fired just before updating a record to the database.
e) POST_UPDATE : This trigger will be fired just after updating a record to the database.
f) ON_UPDATE : This trigger will be fired to replace the default behaviour of forms for updation.
g) PRE_DELETE : This trigger will be fired just before deleting a record from the database.
h) POST_DELETE : This trigger will be fired just after deleting a record from the database.
i) ON_DELETE : This trigger will be fired to replace the default behaviour of forms for deletion.

4. QUERY PROCESSING TRIGGERS : The triggers that are fired based on the query execution process are called as Query Processing Triggers.

a) PRE_QUERY : This trigger will be fired just before executing a query against the database.
b) POST_QUERY : This trigger will be fired once for each row retrieved from the database.

5. NAVIGATIONAL TRIGGERS :

a) PRE_FORM : This trigger will be fired just before the focus enters into the first navigational item of first navigation datablock.
b) POST_FORM : This trigger will be fired whenever the navigation control leaves the form.
c) PRE_BLOCK : This trigger will be fired whenever a block is displayed for the first time.
d) POST_BLOCK : This trigger will be fired whenever the focus transfers to a new data block.
e) PRE_TEXT_ITEM : This trigger will be fired whenever the focus reaches a text item.
f) POST_TEXT_ITEM : This trigger will be fired whenever the focus leaves the text item.

6. NEW OBJECT TRIGGERS :

a) WHEN_NEW_FORM_INSTANCE : This trigger will be fired before a form is displayed for the first time.
b) WHEN_NEW_BLOCK_INSTANCE : This trigger will be fired just before a block is displayed for the first time.
c) WHEN_NEW_ITEM_INSTANCE : This trigger will be fired whenever the focus reaches an item other than the current item.
d) WHEN_NEW_RECORD_INSTANCE : This trigger will be fired whenever the focus reaches a record other than the current record.

7. VALIDATION TRIGGERS :

a) WHEN_VALDIATE_ITEM : This trigger will be fired whenever user is trying to leave an item.
b) WHEN_VALIDATE_RECORD : This trigger will be fired whenever user is trying to leave a record.
c) WHEN_VALIDATE_BLOCK : This trigger will be fired whenever user is trying to leave a block.
d) WHEN_VALIDATE_FORM : This trigger will be fired whenever user is trying to leave a form.

BUILT-INS : Built-ins are nothing but the pre-defined functions and procedures within forms. For example the following built-ins are used to perform the basic operations in forms runtime.
1. EXECUTE_QUERY ( NO_VALIDATE ) : This built-in is used to execute the select statement against the database and request the first record.

2. ENTER_QUERY : This built-in is used to take the block into enter query mode.
3. FIRST_RECORD : This built-in is used to display the first record of the current datablock.
4. NEXT_RECORD : This built-in is used to display the next record of the current record in the current datablock.
5. PREVIOUS_RECORD : This built-in is used to display the previous record of the current record in the current datablock.
6. LAST_RECORD : This built-in is used to display the last record of the current datablcok.
7. CREATE_RECORD : This built-in is used to create a new record for the current datablock.
8. DELETE_RECORD : This built-in is used to delete the current record of the current datablock.
9. COMMIT / COMMIT_FORM : This built-in is used to save the changes of the current datablock or all datablocks of a form module to the database.
10. EXIT_FORM : This built-in is used to close the forms at runtime.

Fresher job sites

2008-07-27T17:58:00.000-07:00

Fresher job sites links

www.bixee.com/Fresher/jobs-in/Hyderabad www.naukrihub.com/job-Openings/corporate/software-development-jobs/hyderabad1.html

www.freshershome.com/jobs www.thisismyindia.com/jobs/hyderabad.html

www.jobblogr.com/2008/02/28/bcom-mba-freshers-amazoncom-Hyderabad

www.freshersdreams.com www.yuvajobs.com/jobsview_all.asp

www.hyderabadjobs.thozhilmela.com/freshers-job