IBM Infosphere Data stage 8.1 Notes | Examples | Real Time Scenario's

<div dir="ltr" style="text-align: left;" trbidi="on"><h2 itemprop="name"><strong><span style="color: red; font-size: small;"><st1:stockticker w:st="on">IBM</st1:stockticker> Infosphere Data stage 8.1 Notes & Examples & Real Time Scenario's</span></strong><br /><br /><strong>Concepts</strong><br /><strong><br /></strong>*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/ibm-info-sphere-datastage-tutorial.html" href="http://foryooth.blogspot.in/2014/01/ibm-info-sphere-datastage-tutorial.html" target="_blank">Introductionabout Data stage</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/difference-between-server-jobs-and.html" href="http://foryooth.blogspot.in/2014/01/difference-between-server-jobs-and.html" target="_blank">Difference between server jobs and Parallel jobs</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/pipeline-partition-parallelism-in-ibm.html" href="http://foryooth.blogspot.in/2014/01/pipeline-partition-parallelism-in-ibm.html" target="_blank">Pipeline parallelism and Partition Parallelism</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/pipeline-partition-parallelism-in-ibm.html" href="http://foryooth.blogspot.in/2014/01/pipeline-partition-parallelism-in-ibm.html" target="_blank">Partition Techniques</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/ibm-info-sphere-datastage-configuration.html" href="http://foryooth.blogspot.in/2014/01/ibm-info-sphere-datastage-configuration.html" target="_blank">Configuration file</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/ibm-info-sphere-datastage-etl-tool.html" href="http://foryooth.blogspot.in/2014/01/ibm-info-sphere-datastage-etl-tool.html" target="_blank">Processing Environment</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/search?q=Data+Stage+Client+and+Server+Components" href="http://foryooth.blogspot.in/search?q=Data+Stage+Client+and+Server+Components" target="_blank">Data Stage Client and Server Components</a><br /><br /><strong>Data stage Designer</strong><br /><strong><br /></strong>* <a data-cke-saved-href="http://foryooth.blogspot.in/search?q=Data+stage+Designer" href="http://foryooth.blogspot.in/search?q=Data+stage+Designer" target="_blank">Introduction about Data stage Designer</a><br /><br />* <a data-cke-saved-href="http://foryooth.blogspot.in/search?q=Repository" href="http://foryooth.blogspot.in/search?q=Repository" target="_blank">Repository</a><br /><br />* <a data-cke-saved-href="http://foryooth.blogspot.in/search?q=Repository" href="http://foryooth.blogspot.in/search?q=Repository" target="_blank">Palatte</a><br /><br />* <a data-cke-saved-href="http://foryooth.blogspot.in/search?q=data+stage+full+notes" href="http://foryooth.blogspot.in/search?q=data+stage+full+notes" target="_blank">Types of Links</a><br /><br /><strong>File Stages</strong><br /><strong><br /></strong><div style="margin-left: 39pt;">*  <a data-cke-saved-href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" target="_blank">Sequential file</a></div><div style="margin-left: 39pt;"><br /></div><div style="margin-left: 39pt;">*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/dataset-stage-in-datastage-properties.html" href="http://foryooth.blogspot.in/2014/01/dataset-stage-in-datastage-properties.html" target="_blank">Data set</a></div><div style="margin-left: 39pt;"><br /></div><div style="margin-left: 39pt;">*  <a data-cke-saved-href="http://foryooth.blogspot.in/search?q=File+set" href="http://foryooth.blogspot.in/search?q=File+set" target="_blank">File set</a></div><div style="margin-left: 39pt;"><br /></div><div style="margin-left: 39pt;">*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/02/lookup-stage-in-data-stage-ibm-info.html" href="http://foryooth.blogspot.in/2014/02/lookup-stage-in-data-stage-ibm-info.html" target="_blank">Lookup file set</a></div><div style="margin-left: 39pt;"><br /></div><strong>Database Stages</strong><br /><strong><br /></strong>*  <a data-cke-saved-href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" target="_blank">Oracle Enterprise</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/search?q=ODBC+Enterprise" href="http://foryooth.blogspot.in/search?q=ODBC+Enterprise">ODBC Enterprise</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2013/12/example-jobs-for-dynamic-rdbms-in.html" href="http://foryooth.blogspot.in/2013/12/example-jobs-for-dynamic-rdbms-in.html" target="_blank">Dynamic RDBMS</a><br /><br /><strong>Processing Stages</strong><br /><strong><br /></strong>*  <a data-cke-saved-href="http://foryooth.blogspot.in/search?q=Aggregator" href="http://foryooth.blogspot.in/search?q=Aggregator" target="_blank">Aggregator</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" target="_blank">Change Apply</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" target="_blank">Change Capture</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" target="_blank">Compare</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" target="_blank">Compress</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/ibm-info-sphere-datastage-copy-stage-in.html" href="http://foryooth.blogspot.in/2014/01/ibm-info-sphere-datastage-copy-stage-in.html" target="_blank">Copy</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2013/12/dataage-81-notes-ecode-stage-decode.html" href="http://foryooth.blogspot.in/2013/12/dataage-81-notes-ecode-stage-decode.html" target="_blank">Decode</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2013/12/dataage-81-notes-ecode-stage-decode.html" href="http://foryooth.blogspot.in/2013/12/dataage-81-notes-ecode-stage-decode.html" target="_blank">Difference</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2013/12/dataage-81-notes-ecode-stage-decode.html" href="http://foryooth.blogspot.in/2013/12/dataage-81-notes-ecode-stage-decode.html" target="_blank">Encode</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" target="_blank">Expand</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/search?q=filter" href="http://foryooth.blogspot.in/search?q=filter" target="_blank">Filter</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/search?q=funnel" href="http://foryooth.blogspot.in/search?q=funnel" target="_blank">Funnel</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" target="_blank">Generic</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/02/join-stage-in-ibm-info-sphere-data_4.html" href="http://foryooth.blogspot.in/2014/02/join-stage-in-ibm-info-sphere-data_4.html" target="_blank">Join</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/02/lookup-stage-in-data-stage-ibm-info.html" href="http://foryooth.blogspot.in/2014/02/lookup-stage-in-data-stage-ibm-info.html" target="_blank">Look up</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/02/lookup-stage-in-data-stage-ibm-info.html" href="http://foryooth.blogspot.in/2014/02/lookup-stage-in-data-stage-ibm-info.html" target="_blank">Merge</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/modify-stage-in-ibm-info-sphere-data.html" href="http://foryooth.blogspot.in/2014/01/modify-stage-in-ibm-info-sphere-data.html" target="_blank">Modify</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/search?q=pivote" href="http://foryooth.blogspot.in/search?q=pivote" target="_blank">Pivote</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/search?q=Remove+Duplicate" href="http://foryooth.blogspot.in/search?q=Remove+Duplicate" target="_blank">Remove Duplicate</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/external-filter-stage-in-ibm-info.html" href="http://foryooth.blogspot.in/2014/01/external-filter-stage-in-ibm-info.html" target="_blank">External Filter</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/search?q=sort" href="http://foryooth.blogspot.in/search?q=sort" target="_blank">Sort</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/search?q=Surrogate+Key+Generator" href="http://foryooth.blogspot.in/search?q=Surrogate+Key+Generator" target="_blank">Surrogate Key Generator</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/switch-stage-in-ibm-info-sphere.html" href="http://foryooth.blogspot.in/2014/01/switch-stage-in-ibm-info-sphere.html" target="_blank">Switch</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/search?q=Transformer" href="http://foryooth.blogspot.in/search?q=Transformer" target="_blank">Transformer</a><br /><br /><strong><a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/development-and-debug-stage-in-ibm-info.html" href="http://foryooth.blogspot.in/2014/01/development-and-debug-stage-in-ibm-info.html" target="_blank">Debugging Stages</a></strong><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/what-is-row-column-generator-stage.html" href="http://foryooth.blogspot.in/2014/01/what-is-row-column-generator-stage.html" target="_blank">Column Generator</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/what-is-head-stage-in-ibm-info-sphere.html" href="http://foryooth.blogspot.in/2014/01/what-is-head-stage-in-ibm-info-sphere.html" target="_blank">Head</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/what-is-peek-stage-tutorial-practice_27.html" href="http://foryooth.blogspot.in/2014/01/what-is-peek-stage-tutorial-practice_27.html" target="_blank">Peek</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/what-is-row-column-generator-stage.html" href="http://foryooth.blogspot.in/2014/01/what-is-row-column-generator-stage.html" target="_blank">Row Generator</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/search?q=Sample" href="http://foryooth.blogspot.in/search?q=Sample" target="_blank">Sample</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2014/01/what-is-tail-stage-tutorial-practice.html" href="http://foryooth.blogspot.in/2014/01/what-is-tail-stage-tutorial-practice.html" target="_blank">Tail</a><br /><br /><strong>Datastage Manager</strong><br /><strong><br /></strong>*  <a data-cke-saved-href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" target="_blank">Introduction about Data stage Manager</a><br /><br />*  <a data-cke-saved-href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" target="_blank">Importing the Jobs</a><br /><br />* <a data-cke-saved-href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" href="http://foryooth.blogspot.in/2013/12/data-stage-full-notes-data-ware-housing.html" target="_blank"> Exporting the Jobs</a><br /><br /><br /><a href="http://foryooth.blogspot.in/search/label/Data%20stage%20practice%20bits" target="_blank"><span style="color: red;">Data Stage Practice Bits</span></a><br /><br /><span style="color: blue;"><a href="http://foryooth.blogspot.in/search/label/Data%20Ware%20Housing" target="_blank">Data Warehousing</a></span><br /><br /><span style="font-weight: normal;"><span style="font-size: small;"><br /></span></span><span style="font-weight: normal;"><span style="font-size: small;">ETL, ETL Data Stage, ETL Tool Data Stage, Data Stage, Data Stage Full Notes, IBM Infosphere Data Stage Full Notes, </span></span><span style="font-size: small; font-weight: normal;">IBM Infosphere Data Stage 8.1 & 8.5 & 8.7 Full Notes, IBM Infosphere Data Stage 8.1 & 8.5 & 8.7 Practice Bits, IBM Infosphere Data Stage 8.1 & 8.5 & 8.7 Realtime Scenario's, IBM Infosphere Data Stage 8.1 & 8.5 & 8.7 Examples, IBM Infosphere Data Stage 8.1 & 8.5 & 8.7 Tutorial, IBM Data Stage, Data Ware Housing Concepts, Data Ware Housing</span></h2></div>

IBM Infosphere Data stage 8.1 Notes | Examples | Real Time Scenario's

IBM  Infosphere Data stage 8.1 Notes & Examples & Real Time Scenario's Concepts *   Introductionabout Data stage *   Difference ...

Read more »

IBM Info Sphere Data Stage Interview Questions || Oracle & Data Ware Housing Interview Questions

<div dir="ltr" style="text-align: left;" trbidi="on"><div style="background-color: white; border: 0px none; color: #333333; font-family: 'Droid Sans', Arial, Verdana, sans-serif; font-size: 13px; line-height: 19.5px; list-style: none; outline: none; padding: 1em 0px; text-align: justify;">IBM Info Sphere Data Stage is a ETL Tool. The Huge Number of Applicants or Students or Members are can Try Data Ware Housing Jobs.  A Very Few People Know about Data ware Housing or Data Stage Interview Questions. IBM Info Sphere Data Stage Interview Pattern for remaining people will not have any Idea about Interview for those Candidates we publishing Oracle Interview Questions, data base Interview Interview Questions,Data Stage Interview Questions, Data ware Housing Interview Questions,Interview Procedure, How To start Interview, ETL Data Stage Interview Questions to Know how questions are there and how to Answer the questions in simple ways. Data Ware Housing Interview Questions 2014 The Candidates who are Interested to work in IT Sectors this is a First Step to go. </div><div style="background-color: white; border: 0px none; font-family: 'Droid Sans', Arial, Verdana, sans-serif; font-size: 13px; line-height: 19.5px; list-style: none; outline: none; padding: 1em 0px; text-align: justify;"><b><span style="color: red;">Oracle & Unix Questions </span></b></div><div style="background-color: white; border: 0px none; color: #333333; font-family: 'Droid Sans', Arial, Verdana, sans-serif; font-size: 13px; line-height: 19.5px; list-style: none; outline: none; padding: 1em 0px; text-align: justify;">Data stage Interview Questions is a basic Oracle, data bare,UNIX, SQL contains very Logically. For this type of Questions have very easy tricks and easy way to Explain Answers in Interview. The data ware Housing Interview Questions are given below to know how they Asked Questions and Presentation for Highly Competition for IT Sectors. Candidates are Advised Should Read each and every Question more than a times because if you Succeed in Data ware housing or Data stage Interview. Data Ware Housing Interview Questions & Oracle Interview Questions 2014 For all Candidates will have to become a Expert in Good Technical Knowledge and also have More Interview Questions in Data stage that is helpful for Interview. </div><div style="background-color: white; border: 0px none; font-family: 'Droid Sans', Arial, Verdana, sans-serif; font-size: 13px; line-height: 19.5px; list-style: none; outline: none; padding: 1em 0px; text-align: justify;"><b><span style="color: red;">Data Ware Housing Questions </span></b></div><div style="background-color: white; border: 0px none; color: #333333; font-family: 'Droid Sans', Arial, Verdana, sans-serif; font-size: 13px; line-height: 19.5px; list-style: none; outline: none; padding: 1em 0px; text-align: justify;"><strong style="border: 0px none; list-style: none; margin: 0px; outline: none; padding: 0px;">Data Ware Housing Interview Questions 2014</strong> Candidates will have Ability to work hard because there is no time to prepare the Interview. This Below data Stage Interview Questions , Oracle Interview questions are Very Useful to our Interview Panel. The IBM Info Sphere Data stage Interview Questions 2014 Candidates Should Know the logic and how to get Job in IT Sector, How to Enplane Answers in Interview Details are Given below. There is a way to know for that you have to Data Stage for Prepare Interview.</div><div style="background-color: white; border: 0px none; font-family: 'Droid Sans', Arial, Verdana, sans-serif; line-height: 19.5px; list-style: none; outline: none; padding: 1em 0px; text-align: justify;"><b><i><span style="color: blue;">Oracle in Data stage Interview Questions with Answers : </span></i></b></div><div class="MsoNormal" style="line-height: 150%;"><b><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Arial;"><span style="color: red;">Q : How strong you are in SQL& PL/SQL?</span><o:p></o:p></span></b></div><div class="MsoNormal" style="line-height: 150%;"><b><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Arial;"><span style="color: red;"><br /></span></span></b></div><div class="MsoNormal" style="line-height: 150%;"><b><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Arial;"><span style="color: red;">Ans :</span></span></b></div><div class="MsoNormal" style="line-height: 150%;"><b><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Arial;"><span style="color: red;"><br /></span></span></b></div><div class="MsoNormal" style="line-height: 150%; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; text-indent: -.25in;"><!--[if !supportLists]--><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">1)<span style="font-size: 7pt; line-height: normal;">      </span></span><!--[endif]--><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Arial;">I am good in SQL,I use to write the source qualifier  queries for informatica mappings as per the business requirement.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: 150%; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; text-indent: -.25in;"><!--[if !supportLists]--><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">2)<span style="font-size: 7pt; line-height: normal;">      </span></span><!--[endif]--><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Arial;">  I am comfortable to work with joins; co related queries, sub queries, analyzing tables, inline views and materialized views.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: 150%; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; text-indent: -.25in;"><!--[if !supportLists]--><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin;">3)<span style="font-size: 7pt; line-height: normal;">      </span></span><!--[endif]--><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Arial;">As a informatica developer I could not get more opportunity to work on pl/sql side. But I worked on PL/SQL to informatica migration project so I do have exposure on procedure, function and triggers.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: 150%; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; text-indent: -.25in;"><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Arial;"><br /></span></div><div class="MsoNormal" style="line-height: 150%; margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; text-indent: -.25in;"><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Arial;"><br /></span></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: red;">Q : What is the difference between view and materialized view?</span><o:p></o:p></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";">Ans : </span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;"> <tbody><tr> <td style="border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 230.7pt;" valign="top" width="308"> <div align="center" class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: center;"><b><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">View<o:p></o:p></span></b></div></td> <td style="border-left: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.45pt;" valign="top" width="309"> <div align="center" class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: center;"><b><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Materialized view<o:p></o:p></span></b></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 230.7pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">A view has a logical existence. It does not contain data.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.45pt;" valign="top" width="309"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">A materialized view has a physical existence.<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 230.7pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Its not a database object.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.45pt;" valign="top" width="309"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">It is a database object.<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 230.7pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">We cannot perform DML operation on view.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.45pt;" valign="top" width="309"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">We can perform DML operation on materialized view.<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 230.7pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">When we do select * from view it will fetch the data from base table.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.45pt;" valign="top" width="309"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">When we do select * from materialized view it will fetch the data from materialized view.<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 230.7pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">In view we cannot schedule to refresh.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.45pt;" valign="top" width="309"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">In materialized view we can schedule to refresh.<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 230.7pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><br /></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.45pt;" valign="top" width="309"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">We can keep aggregated data into materialized view. Materialized view can be created based on multiple tables.<o:p></o:p></span></div></td> </tr></tbody></table><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in; text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in; text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: purple;">Q : Materialized View :</span></span></b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><o:p></o:p></span></div><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in; text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in; text-align: justify; text-justify: inter-ideograph;"><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><b>Ans :</b> Materialized view is very essential for reporting. If we don’t have the materialized view it will directly fetch the data from dimension and facts. This process is very slow since it involves multiple joins. So the same report logic if we put in the materialized view. We can fetch the data directly from materialized view for reporting purpose. So that we can avoid multiple joins at report run time.<o:p></o:p></span></div><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in; text-align: justify; text-justify: inter-ideograph;"><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";">It is always necessary to refresh the materialized view. Then it can simply perform select statement on materialized view.<o:p></o:p></span></div><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0in; text-align: justify; text-justify: inter-ideograph;"><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: blue;">Q : Difference between Trigger and Procedure</span><o:p></o:p></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: blue;"><br /></span></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: blue;">Ans :</span></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;"> <tbody><tr> <td style="border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div align="center" class="MsoNormal" style="margin: 0in 0in 0.0001pt 0.5in; text-align: center; text-indent: -0.5in;"><b><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Triggers<o:p></o:p></span></b></div></td> <td style="border-left: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div align="center" class="MsoNormal" style="margin: 0in 0in 0.0001pt 0.5in; text-align: center;"><b><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Stored Procedures<o:p></o:p></span></b></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">In trigger no need to execute manually. Triggers will be fired automatically.<o:p></o:p></span></div><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><b><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Triggers</span></b><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";"> that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Where as in procedure we need to execute manually.<o:p></o:p></span></div></td> </tr></tbody></table><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: #660000;"><br /></span></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: #660000;">Q : Differences between sub-query and co-related sub-query</span></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: #660000;"><br /></span></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: #660000;">Ans :</span></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;"> <tbody><tr> <td style="border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div align="center" class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: center;"><b><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Sub-query<o:p></o:p></span></b></div></td> <td style="border-left: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div align="center" class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: center;"><b><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Co-related sub-query<o:p></o:p></span></b></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">A sub-query is executed once for the parent Query<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Where as co-related sub-query is executed once for each row of the parent query.<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Example:<o:p></o:p></span></div><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Select * from emp where deptno in (select deptno from dept);<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Example:<o:p></o:p></span></div><div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Select a.* from emp e where sal >= (select avg(sal) from emp a where a.deptno=e.deptno group by  a.deptno);<o:p></o:p></span></div></td> </tr></tbody></table><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: #cc0000;">Q : Differences between where clause and having clause</span><o:p></o:p></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";">Ans :</span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;"> <tbody><tr> <td style="border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.05pt;" valign="top" width="308"> <div align="center" class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: center;"><b><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Where clause<o:p></o:p></span></b></div></td> <td style="border-left: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.1pt;" valign="top" width="308"> <div align="center" class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: center;"><b><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Having clause<o:p></o:p></span></b></div></td> </tr><tr> <td colspan="2" style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 462.15pt;" valign="top" width="616"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Both where and having clause can be used to filter the data.<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.05pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Where as in where clause it is not mandatory.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.1pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">But having clause we need to use it with the group by.<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.05pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Where clause applies to the individual rows.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.1pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Where as having clause is used to test some condition on the group rather than on individual rows.<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.05pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Where clause is used to restrict rows.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.1pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">But having clause is used to restrict groups.<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.05pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Restrict normal query by where<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.1pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Restrict group by function by having<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.05pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">In where clause every record is filtered based on where.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.1pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">In having clause it is with aggregate records (group by functions).<o:p></o:p></span></div></td> </tr></tbody></table><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: #20124d;">Q : Differences between stored procedure and functions</span><o:p></o:p></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: #20124d;"><br /></span></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: #20124d;">Ans :</span></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;"> <tbody><tr> <td style="border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div align="center" class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: center;"><b><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Stored Procedure<o:p></o:p></span></b></div></td> <td style="border-left: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div align="center" class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: center;"><b><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Functions<o:p></o:p></span></b></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Stored procedure may or may not return values.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Function should return at least one output parameter. Can return more than one parameter using OUT argument.<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Stored procedure can be used to solve the business logic.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Function can be used to calculations<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Stored procedure is a pre-compiled statement.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">But function is not a pre-compiled statement.<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Stored procedure accepts more than one argument.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Whereas function does not accept arguments.<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Stored procedures are mainly used to process the tasks.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Functions are mainly used to compute values<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Cannot be invoked from SQL statements. E.g. SELECT<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Can be invoked form SQL statements e.g. SELECT<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Can affect the state of database using commit.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Cannot affect the state of database.<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Stored as a pseudo-code in database i.e. compiled form.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 239.4pt;" valign="top" width="319"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Parsed and compiled at runtime.<o:p></o:p></span></div></td> </tr></tbody></table><div style="background-color: white; border: 0px none; font-family: 'Droid Sans', Arial, Verdana, sans-serif; line-height: 19.5px; list-style: none; outline: none; padding: 1em 0px; text-align: justify;"> </div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><br /></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: red;">Q : Differences between rowid and rownum</span><o:p></o:p></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: red;"><br /></span></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: red;">ans :</span></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;"> <tbody><tr> <td style="border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 230.9pt;" valign="top" width="308"> <div align="center" class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: center;"><b><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Rowid<o:p></o:p></span></b></div></td> <td style="border-left: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.25pt;" valign="top" width="308"> <div align="center" class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: center;"><b><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Rownum<o:p></o:p></span></b></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 230.9pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Rowid is an oracle internal id that is allocated every time a new record is inserted in a table. This ID is unique and cannot be changed by the user.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.25pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Rownum is a row number returned by a select statement.<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 230.9pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Rowid is permanent.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.25pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Rownum is temporary.<o:p></o:p></span></div></td> </tr><tr> <td style="border-top: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 230.9pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">Rowid is a globally unique identifier for a row in a database. It is created at the time the row is inserted into the table, and destroyed when it is removed from a table.<o:p></o:p></span></div></td> <td style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 231.25pt;" valign="top" width="308"> <div class="MsoNormal" style="margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 12.0pt; mso-bidi-font-family: "Times New Roman";">The rownum pseudocoloumn returns a number indicating the order in which oracle selects the row from a table or set of joined rows.<o:p></o:p></span></div></td> </tr></tbody></table><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><br /></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: #660000;">Q : How to find out duplicate records in table?</span><o:p></o:p></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><div class="MsoNormal"><span class="msgbodytext"><span style="font-size: 12.0pt; line-height: 115%;"><b>Ans</b> : Select empno, count (*) from EMP group by empno having count (*)>1;<o:p></o:p></span></span></div><div class="MsoNormal"><span class="msgbodytext"><span style="font-size: 12.0pt; line-height: 115%;"><br /></span></span></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><span style="color: red;">Q: How to delete a duplicate records in a table?<o:p></o:p></span></span></b></div><div class="MsoNormal" style="text-align: justify; text-justify: inter-ideograph;"><b><span style="font-size: 12.0pt; line-height: 115%; mso-bidi-font-family: "Times New Roman";"><br /></span></b></div><div class="MsoNormal"><span class="msgbodytext"><span style="font-size: 12.0pt; line-height: 115%;">Ans : Delete from EMP where rowid not in (select max (rowid) from EMP group by empno);</span></span><span style="font-size: 12.0pt; line-height: 115%;"><o:p></o:p></span></div><div class="MsoNormal"><span class="msgbodytext"><span style="font-size: 12.0pt; line-height: 115%;"><br /></span></span></div><div class="MsoNormal" style="line-height: 150%;"><b><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Arial;"><span style="color: purple;">Q: What is your tuning approach if SQL query taking long time? Or how do u tune SQL query?</span><o:p></o:p></span></b></div><div class="MsoNormal" style="line-height: 150%;"><b><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Arial;"><br /></span></b></div><div class="MsoNormal" style="line-height: 150%;"><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Arial;"><span style="color: purple;"><b>Ans:</b></span>  If query taking long time then First will run the query in Explain Plan, The explain plan process stores data in the PLAN_TABLE.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: 150%;"><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Arial;"> it will give us execution plan of the query like whether the query is using the relevant indexes on the joining columns or indexes to support the query are missing.<o:p></o:p></span></div><div class="MsoNormal"><span style="font-size: small;"><span style="line-height: 150%;">If joining columns </span></span><span style="line-height: 24px;">doesn't</span><span style="font-size: small;"><span style="line-height: 150%;"> have index then it will do the full table scan if it is full table scan the cost will be more then will create the indexes on the joining columns and will run the query  it should give  better performance and also  needs to analyze the tables if analyzation happened long back.</span></span><span style="font-size: 12pt; line-height: 150%;"> </span><span style="font-size: 12pt; line-height: 150%;">The ANALYZE statement can be used to gather statistics for a specific </span><span style="font-size: 12pt; line-height: 150%;">table, index or cluster using<o:p></o:p></span></div><div class="MsoNormal"><span style="font-size: 12pt; line-height: 115%;">ANALYZE TABLE employees COMPUTE STATISTICS;<o:p></o:p></span></div><div class="MsoNormal" style="line-height: 150%;"><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Arial;">If still have performance issue then will use HINTS, hint is nothing but a clue. We can use hints like<o:p></o:p></span></div><ul type="disc"><li class="MsoNormal" style="margin-bottom: 1.5pt;"><span style="font-size: 12.0pt;">ALL_ROWS<br /> One of the hints that 'invokes' the </span><span style="font-size: 12.0pt;">Cost based optimizer</span><span style="font-size: 12.0pt;"> <br /> ALL_ROWS is usually used for <i>batch processing</i> or <i>data warehousing</i> systems. <o:p></o:p></span></li></ul><div class="MsoNormal" style="margin-bottom: 1.5pt; margin-left: .5in; mso-margin-top-alt: auto;"><span style="font-size: 12pt; line-height: 115%;">(</span><span style="font-size: 12pt; line-height: 115%;">/*+ ALL_ROWS */</span><span style="font-size: 12pt; line-height: 115%;">)  </span><span style="font-size: 12.0pt; line-height: 115%;"><o:p></o:p></span></div><ul type="disc"><li class="MsoNormal" style="margin-bottom: 1.5pt;"><span style="font-size: 12.0pt;">FIRST_ROWS<br /> One of the hints that 'invokes' the </span><span style="font-size: 12.0pt;">Cost based optimizer</span><span style="font-size: 12.0pt;"> <br /> FIRST_ROWS is usually used for <i>OLTP</i> systems. <o:p></o:p></span></li></ul><div class="MsoNormal" style="margin-bottom: 1.5pt; margin-left: .5in; mso-margin-top-alt: auto;"><span style="font-size: 12pt; line-height: 115%;">(</span><span style="font-size: 12pt; line-height: 115%;">/*+ FIRST_ROWS */</span><span style="font-size: 12pt; line-height: 115%;">)  </span><span style="font-size: 12.0pt; line-height: 115%;"><o:p></o:p></span></div><ul type="disc"><li class="MsoNormal" style="margin-bottom: 1.5pt;"><span style="font-size: 12.0pt;">CHOOSE<br /> One of the hints that 'invokes' the </span><span style="font-size: 12.0pt;">Cost based optimizer</span><span style="font-size: 12.0pt;"> <br /> This hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on </span><span style="font-size: 12.0pt;">statistics gathered</span><span style="font-size: 12.0pt;">. <o:p></o:p></span></li><li class="MsoNormal"><span style="color: windowtext; font-size: 12.0pt;">HASH<br /> Hashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for < or > join conditions.</span><span style="font-size: 12.0pt; mso-fareast-font-family: SimSun; mso-fareast-language: ZH-CN;"><o:p></o:p></span></li></ul><div class="MsoNormal" style="margin-bottom: 10.0pt; margin-left: .5in; margin-right: .9pt; margin-top: 0in; mso-layout-grid-align: none; tab-stops: .5in; text-autospace: none;"><span style="font-size: 12pt; line-height: 115%;">/*+ use_hash */<o:p></o:p></span></div><div class="MsoNormal" style="line-height: 150%;"><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Arial;">Hints are most useful to optimize the query performance.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: 150%;"><span style="font-size: 12.0pt; line-height: 150%; mso-bidi-font-family: Arial;"><br /></span></div><div class="MsoNormal" style="background-color: white; color: #555555; font-family: Arial, Helvetica, sans-serif; line-height: 20px;"><b><span style="color: #20124d; outline: none;"><a href="https://docs.google.com/document/d/1Y1K10j3zpT43vmGVWK1TdPGtrThPXWFZ1wFwfa5O8dg/edit?usp=sharing" style="color: #de2801; outline: none;" target="_blank">Click Here For Transformer in Data Stage <span style="vertical-align: baseline; white-space: pre-wrap;">Date and time functions</span></a></span></b></div><div class="MsoNormal" style="background-color: white; color: #555555; font-family: Arial, Helvetica, sans-serif; line-height: 20px;"><b><br /></b></div><div class="MsoNormal" style="background-color: white; color: #555555; font-family: Arial, Helvetica, sans-serif; line-height: 20px;"><br /></div><div class="MsoNormal" style="background-color: white; color: #555555; font-family: Arial, Helvetica, sans-serif; line-height: 20px;"><b><span style="color: #660000; outline: none;"><a href="https://docs.google.com/document/d/1nkeqKsGKA1ga9J2X7GBi2xBDYvJvYK5eWpeJVYwtW7Y/edit?usp=sharing" style="color: #de2801; outline: none;" target="_blank">Click Here For Transformer Stage in Data Stage Tutorial & Practice Bits</a></span></b></div><div class="MsoNormal" style="background-color: white; color: #555555; font-family: Arial, Helvetica, sans-serif; line-height: 20px;"><b><br /></b></div><div class="MsoNormal" style="background-color: white; color: #555555; font-family: Arial, Helvetica, sans-serif; line-height: 20px;"><b><br /></b></div><div class="MsoNormal" style="background-color: white; color: #555555; font-family: Arial, Helvetica, sans-serif; line-height: 20px;"><b><a href="http://foryooth.blogspot.in/2014/01/transformer-stage-in-ibm-info-sphere.html" style="color: #de2801; outline: none;" target="_blank"><span style="color: blue;">Click Here for Transformer Stage in Data Stage Tutorial Videos</span></a></b></div><div class="MsoNormal" style="background-color: white; color: #555555; font-family: Arial, Helvetica, sans-serif; line-height: 20px;"><b><br /></b></div><h3 style="background-color: white; color: #555555; font-family: Arial, Helvetica, sans-serif; font-weight: normal; line-height: 20px; margin-bottom: 16px;"><b><i><a data-cke-saved-href="http://foryooth.blogspot.in/search/label/Data%20stage%20practice%20bits" href="http://foryooth.blogspot.in/search/label/Data%20stage%20practice%20bits" style="color: #de2801; outline: none; text-decoration: none;" target="_blank"><span style="color: #660000;"><span style="font-size: small;">Click Here For More IBM Info Sphere Data Stage Online Training Tutorial's</span></span></a></i></b></h3><div class="MsoNormal" style="background-color: white; color: #555555; font-family: Arial, Helvetica, sans-serif; line-height: 20px;"><br /><b><span style="color: #0c343d; outline: none;"><a data-cke-saved-href="http://foryooth.blogspot.in/search/label/Data%20Stage" href="http://foryooth.blogspot.in/search/label/Data%20Stage" style="color: #de2801; outline: none;" target="_blank">Click Here for More IBM Info Sphere Data stage Tutorial's</a></span></b></div><div><b><span style="font-size: xx-small;"><br /></span></b></div><div style="background-color: white; border: 0px none; color: #333333; font-family: 'Droid Sans', Arial, Verdana, sans-serif; font-size: 13px; line-height: 19.5px; list-style: none; outline: none; padding: 1em 0px; text-align: justify;"><br /></div></div>

IBM Info Sphere Data Stage Interview Questions || Oracle & Data Ware Housing Interview Questions

IBM Info Sphere Data Stage is a ETL Tool. The Huge Number of Applicants or Students or Members are can Try Data Ware Housing Jobs.  A Very F...

Read more »

Lookup Stage in Data Stage || IBM Info Sphere Data stage Real Time Scenario's

<div dir="ltr" style="text-align: left;" trbidi="on"><div class="MsoNormal"><b><span style="color: red;">LOOKUP STAGE:<o:p></o:p></span></b></div><div class="MsoNormal" style="margin-bottom: 5.0pt; margin-left: 0in; margin-right: 0in; margin-top: 5.0pt; mso-layout-grid-align: none; text-autospace: none;">The Lookup stage is a processing stage. Lookup stage in data stage is used to perform lookup operations on a data set read into memory from any other Parallel job stage that can output data. The most common use for a lookup is to map short codes in the input data set onto expanded information from a lookup table which is then joined to the incoming data and output. For example, you could have an input data set carrying names and addresses of your <st1:country-region w:st="on">U.S.</st1:country-region> customers. The data as presented identifies state as a two <st1:place w:st="on"><st1:placename w:st="on">letter</st1:placename> <st1:placetype w:st="on">U.</st1:placetype></st1:place> S. state postal code, but you want the data to carry the full name of the state. You could define a lookup table that carries a list of codes matched to states, defining the code as the key column. As the Lookup stage reads each line, it uses the key to look up the state in the lookup table. It adds the state to a new column defined for the output link, and so the full state name is added to each address. If any state codes have been incorrectly entered in the data set, the code will not be found in the lookup table, and so that record will be rejected.</div><div class="MsoNormal" style="margin-bottom: 5.0pt; margin-left: 0in; margin-right: 0in; margin-top: 5.0pt; mso-layout-grid-align: none; text-autospace: none;"><a href="https://www.blogger.com/blogger.g?blogID=3469121090747413492" name="wp862694__wp875032"></a>1. Lookup stage in data stage can also be used for validation of a row. If there is no corresponding entry in a lookup table to the key's values, the row is rejected.</div><div class="MsoNormal" style="margin-bottom: 5.0pt; margin-left: 0in; margin-right: 0in; margin-top: 5.0pt; mso-layout-grid-align: none; text-autospace: none;"><a href="https://www.blogger.com/blogger.g?blogID=3469121090747413492" name="wp862694__wp874888"></a>2.The Lookup stage in data stage is one of three stages that join tables based on the values of key columns. The other two are:</div><div class="MsoNormal" style="margin-bottom: 5.0pt; margin-left: .5in; margin-right: 0in; margin-top: 5.0pt; mso-layout-grid-align: none; text-autospace: none; text-indent: -.25in;"><a href="https://www.blogger.com/blogger.g?blogID=3469121090747413492" name="wp862694__wp874892"></a>Join stage - <span style="color: blue;"><a href="http://foryooth.blogspot.in/2014/02/join-stage-in-ibm-info-sphere-data_4.html" target="_blank">Join stage</a></span> </div><div class="MsoNormal" style="margin-bottom: 5.0pt; margin-left: .5in; margin-right: 0in; margin-top: 5.0pt; mso-layout-grid-align: none; text-autospace: none; text-indent: -.25in;"><a href="https://www.blogger.com/blogger.g?blogID=3469121090747413492" name="wp862694__wp874896"></a>Merge stage -<a href="http://foryooth.blogspot.in/search/?q=merge+stage" target="_blank"> <span style="color: blue;">Merge Stage</span></a></div><div class="MsoNormal" style="margin-bottom: 5.0pt; margin-left: 0in; margin-right: 0in; margin-top: 5.0pt; mso-layout-grid-align: none; text-autospace: none;">3.The three stages differ mainly in the memory they use, the treatment of rows with unmatched keys, and their requirements for data being input</div><br /><div class="MsoNormal">4. The Lookup stage can have a reference link, a single input link, a single output link, and a single rejects link.</div><div class="MsoNormal"><br /></div><div class="MsoNormal">Applicants More Information about Lookup stage in IBM Info Sphere Data stage, Lookup Stage, ETL Tool, Data stage, IBM Info Sphere Data Stage, Lookup Stage Tutorial, Lookup Stage Scenario, Lookup stage Practical Examples, Lookup Stage Real Time Examples for <b><span style="color: purple;"><a href="https://docs.google.com/document/d/1KvdRocYyuRnZ-_yKlMYElZSWvGI7HL7kJlZXfgnA2vU/edit" target="_blank">Click On Below Link.</a></span></b></div><div class="MsoNormal"><b><br /></b></div><div class="MsoNormal"><b><a href="https://docs.google.com/document/d/1KvdRocYyuRnZ-_yKlMYElZSWvGI7HL7kJlZXfgnA2vU/edit" target="_blank">Click Here for Lookup Stage Real time Scenario's & Examples</a></b><br /><br /><br /><b><span style="color: #20124d;">Lookup Stage Example & Real Time Scenario's Video :</span></b><br /><br /><br /></div><iframe allowfullscreen="" frameborder="0" height="315" src="//www.youtube.com/embed/lgkIAVLP_-I" width="560">Lookup Stage in Data Stage</iframe> <br /><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><h3 class="post-title entry-title" style="background-color: white; font-family: Oswald, sans-serif; line-height: 24px; margin: 0px 0px 10px; padding: 0px;"><a href="http://foryooth.blogspot.in/2013/12/datastage-81-best-practices-job-design.html" style="display: block; outline: none; text-decoration: none;"><span style="color: blue; font-size: small;">Datastage 8.1 best practices || Job design TIPS || Datastage 8.1 Full Notes</span></a></h3><div class="post-header-line-1" style="background-color: white; color: #555555; font-family: Arial, Helvetica, sans-serif;"></div><h3 class="post-title entry-title" style="background-color: white; font-family: Oswald, sans-serif; line-height: 24px; margin: 0px 0px 10px; padding: 0px;"><a href="http://foryooth.blogspot.in/2014/01/ibm-info-sphere-datastage-etl-tool.html" style="display: block; outline: none; text-decoration: none;"><span style="color: #0c343d; font-size: small;">IBM Info Sphere Datastage & ETL Tool Processes</span></a></h3><div class="post-header-line-1" style="background-color: white; color: #555555; font-family: Arial, Helvetica, sans-serif; font-size: 13px;"></div></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div></div>

Lookup Stage in Data Stage || IBM Info Sphere Data stage Real Time Scenario's

LOOKUP STAGE: The Lookup stage is a processing stage. Lookup stage in data stage is used to perform lookup operations on a data set read int...

Read more »

Join Stage in IBM Info Sphere Data Stage || Examples || Scenario's

<div dir="ltr" style="text-align: left;" trbidi="on">Join Stage is a Processing stage IBM Info Sphere Data Stage. This Join Stage is Used to Join Two Input Table data and Produce to new Out Put data Table is Called Join Stage. This Join Stage is Three Types. There are Inner Join, Outer Join & Full Outer Join. IBM Info Sphere Data Stage Join Stage Using main purpose is Combination of Two Equal or Not Equal Tables to generate New Out Put Table data. More Information about Join Stage Data stage information is Given Below. Join Stage Examples, Join Stage Flow Data, Join Stage Real time Scenario's foe Please Click on Below Link.<br /><br /><h2><b><span style="color: blue;">Join Stage real Time Examples & Scenario's for </span><a href="https://docs.google.com/document/d/1KvdRocYyuRnZ-_yKlMYElZSWvGI7HL7kJlZXfgnA2vU/edit?usp=sharing" target="_blank"><span style="color: red;">Click Here</span></a></b></h2><h2><b><span style="color: blue;">What is Join Stage in <a href="http://foryooth.blogspot.in/search/label/Data%20Stage" target="_blank">IBM Info Sphere Data Stage</a> ?</span></b></h2><div><b><span style="color: blue;"><br /></span></b></div><div><div class="MsoNormal"><b><u><span style="color: red; font-size: 16pt;">JOIN Queries:<o:p></o:p></span></u></b></div><div class="MsoNormal">Join is a query which combines the data from multiple tables</div><div class="MsoNormal">Types of joins:</div><div class="MsoNormal">1. Cartezion join</div><div class="MsoNormal">2. Equi join</div><div class="MsoNormal">3. Non equi join</div><div class="MsoNormal">4.Self join or inner join</div><div class="MsoNormal">5.Outer join</div><div class="MsoNormal">        Left outer join                                                        </div><div class="MsoNormal">        Right outer join</div><div class="MsoNormal"><br /></div><div class="MsoNormal"><b><u><span style="color: red; font-size: 14pt;">Employee Table Data:<o:p></o:p></span></u></b></div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">SQL> select * from emp;</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;"><br /></div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">       EMPNO   ENAME      <st1:stockticker w:st="on">JOB</st1:stockticker>       MGR     DEPTNO</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">      ---------- ---------- ---------- ---------- ----------</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">       111         bhaskar         analyst           444          10</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">       222         prabhakar     clerk               333          20</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">       333         pradeep        manager          111         10</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">       444         srujana          engineer         222          40</div><div class="MsoNormal"><b><u><span style="color: red; font-size: 14pt;">Department Table Data<o:p></o:p></span></u></b></div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">SQL> select * from dept;</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;"><br /></div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">    DEPTNO DNAME      LOC</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">     ------ ---------- ----------</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">        10            marketing        <st1:city w:st="on">hyderabad</st1:city></div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">        20            finance             banglore</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">        30            hr                     <st1:city w:st="on">bombay</st1:city>                                          </div><div class="MsoNormal"><br /></div><div class="MsoNormal"><b><u><span style="color: red; font-size: 14pt;">Examples:<o:p></o:p></span></u></b></div><div class="MsoNormal"><b><u><span style="color: red; font-size: 14pt;">Cartezion join:<o:p></o:p></span></u></b></div><div class="MsoNormal">If we combine a data from multiple tables with out applying any condition then each record in the first table will join with all records in the second table</div><div class="MsoNormal">SQL>select * from emp,dept</div><div class="MsoNormal">SQL>select empno,ename,job ,dname,loc from emp e,dept d</div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><b><u><span style="color: red; font-size: 14pt;"><a href="http://foryooth.blogspot.in/search/label/Data%20Stage" target="_blank">Equi join:</a><o:p></o:p></span></u></b></div><div class="MsoNormal">If we combine a data from multiple tables by applying equal no of conditions on multiple tables then each record in the first table will join with one row in the second table.</div><div class="MsoNormal">This kind of join can be called as Equi join</div><div class="MsoNormal"><br /></div><div class="MsoNormal">SQL>select e.empno,e.ename,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno</div><div class="MsoNormal"><br /></div><div class="MsoNormal"><b><u><span style="color: red; font-size: 14pt;">Inner join:<o:p></o:p></span></u></b></div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">This will display all the records that have matched.</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">Ex:</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">     SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);</div><div class="MsoNormal"><br /></div><div class="MsoNormal"><b><u><span style="color: red; font-size: 14pt;">Left outer join:<o:p></o:p></span></u></b></div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">This will display the all matching records and the records which are in left hand side table those that are not in right hand side table.</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">Ex:</div><div class="MsoNormal" style="line-height: 24px;">     SQL> select empno,ename,job,dname,loc from emp e left outer join dept d</div><div class="MsoNormal" style="line-height: 24px;">             on(e.deptno=d.deptno);</div><div class="MsoNormal" style="line-height: 24px; margin-left: 2.5in; text-align: justify; text-indent: 0.5in;">Or</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">     SQL> select empno,ename,job,dname,loc from emp e,dept d where</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">             e.deptno=d.deptno(+);</div><div class="MsoNormal"><b><u><span style="color: red; font-size: 14pt;">Right outer join:<o:p></o:p></span></u></b></div><div class="MsoNormal" style="line-height: 24px; text-align: justify;"><br /></div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">This will display the all matching records and the records which are in right hand side table those that are not in left hand side table.</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">Ex:</div><div class="MsoNormal" style="line-height: 24px;">     SQL> select empno,ename,job,dname,loc from emp e right outer join dept d</div><div class="MsoNormal" style="line-height: 24px;">              on(e.deptno=d.deptno);</div><div class="MsoNormal" style="line-height: 24px; margin-left: 2.5in; text-align: justify; text-indent: 0.5in;">Or</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">      SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno(+) =</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">             d.deptno;</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;"><br /></div><div class="MsoNormal" style="line-height: 24px; text-align: justify;"><br /></div><div class="MsoNormal" style="line-height: 24px; text-align: justify;"><br /></div><div class="MsoNormal"><b><u><span style="color: red; font-size: 14pt;">Full outer join<o:p></o:p></span></u></b></div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">This will display the all matching records and the non-matching records from both tables.</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">Ex:</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">     SQL> select empno,ename,job,dname,loc from emp e full outer join dept d</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;">              on(e.deptno=d.deptno);</div><div class="MsoNormal" style="line-height: 24px; text-align: justify;"><br /></div><div class="MsoNormal"><b><u><span style="color: red; font-size: 16pt;">Join Stage:<o:p></o:p></span></u></b></div><div class="MsoNormal" style="margin: 5pt 0in;"><a href="https://www.blogger.com/blogger.g?blogID=3469121090747413492" name="wp926302__wp928305"></a>These topics describe Join stages, which are used to join data from two input tables and produce one output table. You can use the Join stage to perform inner joins, outer joins, or full joins.</div><div class="MsoNormal" style="margin: 5pt 0in 5pt 0.5in; text-indent: -0.25in;"><a href="https://www.blogger.com/blogger.g?blogID=3469121090747413492" name="wp926302__wp928307"></a>1.An inner join returns only those rows that have matching column values in both input tables. The unmatched rows are discarded.</div><div class="MsoNormal" style="margin: 5pt 0in 5pt 0.5in; text-indent: -0.25in;"><a href="https://www.blogger.com/blogger.g?blogID=3469121090747413492" name="wp926302__wp926775"></a>2.An outer join returns all rows from the outer table even if there are no matches. You define which of the two tables is the outer table.</div><div class="MsoNormal" style="margin: 5pt 0in 5pt 0.5in; text-indent: -0.25in;"><a href="https://www.blogger.com/blogger.g?blogID=3469121090747413492" name="wp926302__wp926776"></a>3.A full join returns all rows that match the join condition, plus the unmatched rows from both input tables.</div><div class="MsoNormal" style="margin: 5pt 0in;"><a href="https://www.blogger.com/blogger.g?blogID=3469121090747413492" name="wp926302__wp926777"></a>Unmatched rows returned in outer joins or full joins have NULL values in the columns of the other link</div><div class="MsoNormal" style="margin: 5pt 0in;">1.Join stages have two input links and one output link.</div><div class="MsoNormal" style="margin: 5pt 0in;">2. The two input links must come from source stages. The joined data can be output to another processing stage or a passive stage</div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><b><span style="color: red; font-size: 16pt;">Join stage Properties:<o:p></o:p></span></b></div><div class="MsoNormal">1.Join Keys</div><div class="MsoNormal">2.Options</div><div class="MsoNormal"><br /></div><div class="MsoNormal"><b><span style="color: red;">1.Join Keys<o:p></o:p></span></b></div><ul style="margin-top: 0in;" type="square"><li class="MsoNormal">Key=?</li></ul><div class="MsoNormal">      Type: Input Column</div><div class="MsoNormal" style="margin-left: 0.25in;">Name of input column you want to join on. Columns with the same name must appear in both input data sets and have compatible data types.</div><div class="MsoNormal" style="margin-left: 0.25in;"><br /></div><div class="MsoNormal" style="margin-left: 0.25in;"><b>Case sensitive</b>=True/False</div><div class="MsoNormal" style="margin-left: 0.25in;">Type: List</div><div class="MsoNormal" style="margin-left: 0.25in;">Whether this join column is case sensitive or not.</div><div class="MsoNormal" style="margin-left: 0.25in;"><br /></div><div class="MsoNormal"><b><span style="color: red;">2.Options<o:p></o:p></span></b></div><ul style="margin-top: 0in;" type="square"><li class="MsoNormal"><b>Join type= F,I,L,R<o:p></o:p></b></li></ul><div class="MsoNormal">      F->Full outer join</div><div class="MsoNormal">      I->Inner join</div><div class="MsoNormal">      L->Left outer join</div><div class="MsoNormal">      R->Right outer join</div><div class="MsoNormal"><br /></div><div class="MsoNormal"><b><span style="color: red;">Type: List<o:p></o:p></span></b></div><div class="MsoNormal">Type of join operation to perform.</div><div class="MsoNormal"><br /></div><div class="MsoNormal"><b><a href="http://foryooth.blogspot.in/search/label/Data%20Stage" target="_blank">Click Here For IBM Info Sphere Data Stage Scenario's</a></b><br /><div><br /></div></div><div class="MsoNormal" style="margin-left: 0.25in;"></div></div></div>

Join Stage in IBM Info Sphere Data Stage || Examples || Scenario's

Join Stage is a Processing stage IBM Info Sphere Data Stage. This Join Stage is Used to Join Two Input Table data and Produce to new Out Put...

Read more »