Semi Zone: Data Stage

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 ...

IBM Info sphere quality stage in datastage tutorial

<div dir="ltr" style="text-align: left;" trbidi="on"><div class="MsoNormal"><span style="color: red;">1.Why investigate:<o:p></o:p></span></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="font-family: Wingdings;">à</span>Discover trends and potential anomalies in data</div><div class="MsoNormal"><span style="font-family: Wingdings; mso-ascii-font-family: "Times New Roman"; mso-char-type: symbol; mso-hansi-font-family: "Times New Roman"; mso-symbol-font-family: Wingdings;">à</span>Identify invalid and default values in a data</div><div class="MsoNormal"> <span style="font-family: Wingdings; mso-ascii-font-family: "Times New Roman"; mso-char-type: symbol; mso-hansi-font-family: "Times New Roman"; mso-symbol-font-family: Wingdings;">à</span>Verify the reliability of the data in the fields to be used as a matching criteria</div><div class="MsoNormal"> <span style="font-family: Wingdings; mso-ascii-font-family: "Times New Roman"; mso-char-type: symbol; mso-hansi-font-family: "Times New Roman"; mso-symbol-font-family: Wingdings;">à</span>Gain complte understanding of the data  in a context</div><div class="MsoNormal"><span style="color: red;">Investiage:<o:p></o:p></span></div><div class="MsoNormal">Verify the domain:</div><div class="MsoNormal">Review each field and verify the data matches the meta data</div><div class="MsoNormal">Identyfy the data formats and missing and default values </div><div class="MsoNormal">Identify the data anomalies:</div><div class="MsoNormal">Format</div><div class="MsoNormal">Structure</div><div class="MsoNormal">Content</div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="color: red;">Feature of investigate:<o:p></o:p></span></div><div class="MsoNormal">Analyze free form and single domain columns</div><div class="MsoNormal">Provide frequency distribution  of distinct values and patterns</div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="color: red;">Investigaet methods:<o:p></o:p></span></div><div class="MsoNormal">Characte distcrete</div><div class="MsoNormal">Character concate nate:</div><div class="MsoNormal">Word invistgate:</div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="color: red;">INVESTIGATE STAGE:<o:p></o:p></span></div><div class="MsoNormal"><span style="color: red;">1.Character Discrete Investigate C mask:<o:p></o:p></span></div><div class="MsoNormal"><span style="color: red;">Job:<o:p></o:p></span></div><div class="MsoNormal" style="margin-left: .25in;"><img height="184" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image002.jpg" v:shapes="_x0000_i1025" width="530" /></div><div class="MsoNormal" style="margin-left: .25in;"><br /></div><div class="MsoNormal" style="margin-left: .25in;"><br /></div><div class="MsoNormal" style="margin-left: .25in;"><br /></div><div class="MsoNormal" style="margin-left: .25in;"><br /></div><div class="MsoNormal" style="margin-left: .25in;"><br /></div><div class="MsoNormal" style="margin-left: .25in;"><br /></div><div class="MsoNormal" style="margin-left: .25in;"><br /></div><div class="MsoNormal" style="margin-left: .25in;"><br /></div><div class="MsoNormal" style="margin-left: .25in;">Add the columns which ever u want to investigate:</div><div class="MsoNormal"><img height="287" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image004.jpg" v:shapes="_x0000_i1026" width="468" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal">Click on change mask and select for all the fields C mask</div><div class="MsoNormal"><br /></div><div class="MsoNormal">At out put it gives 5 columns</div><div class="MsoNormal">1.qsInvColumnName</div><div class="MsoNormal">2.qsInvPattern</div><div class="MsoNormal">3.qsInvSample</div><div class="MsoNormal">4.qsInvCount</div><div class="MsoNormal">5.QsInvPercent</div><div class="MsoNormal"><br /></div><div class="MsoNormal">At output it gives like below</div><div class="MsoNormal"><img height="78" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image006.jpg" v:shapes="_x0000_i1027" width="474" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal">Above screen shot is only for one field it gives same like another fields which ever u selected</div><div class="MsoNormal"><img height="25" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image008.jpg" v:shapes="_x0000_i1028" width="473" /></div><div class="MsoNormal"><img height="120" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image010.jpg" v:shapes="_x0000_i1029" width="492" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="color: red;">1.Character Discrete Investigate T mask:<o:p></o:p></span></div><div class="MsoNormal"><span style="color: red;">Job:<o:p></o:p></span></div><div class="MsoNormal"><span style="color: red;"><img height="133" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image012.jpg" v:shapes="_x0000_i1030" width="508" /><o:p></o:p></span></div><div class="MsoNormal"><br /></div><div class="MsoNormal" style="margin-left: .25in;">Add the columns which ever u want to investigate:</div><div class="MsoNormal"><img height="287" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image004.jpg" v:shapes="_x0000_i1031" width="468" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal">Click on change mask and select for all the fields C mask</div><div class="MsoNormal"><br /></div><div class="MsoNormal">At out put it gives 5 columns</div><div class="MsoNormal">1.qsInvColumnName</div><div class="MsoNormal">2.qsInvPattern</div><div class="MsoNormal">3.qsInvSample</div><div class="MsoNormal">4.qsInvCount</div><div class="MsoNormal">5.QsInvPercent</div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal">At output it gives like below</div><div class="MsoNormal"><span style="color: red;"><img height="226" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image014.jpg" v:shapes="_x0000_i1032" width="453" /><o:p></o:p></span></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="color: red;">3. Character Discrete Investigate X mask:<o:p></o:p></span></div><div class="MsoNormal"><img height="135" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image016.jpg" v:shapes="_x0000_i1033" width="506" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal" style="margin-left: .25in;">Add the columns which ever u want to investigate:</div><div class="MsoNormal"><img height="360" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image018.jpg" v:shapes="_x0000_i1034" width="576" /></div><div class="MsoNormal">Here I selected for Policy Number Column C,T,X alternatively</div><div class="MsoNormal">At out put it gives like below</div><div class="MsoNormal"><img height="68" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image020.jpg" v:shapes="_x0000_i1035" width="498" /></div><div class="MsoNormal"><img height="90" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image022.jpg" v:shapes="_x0000_i1036" width="496" /></div><div class="MsoNormal"><img height="55" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image024.jpg" v:shapes="_x0000_i1037" width="499" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="color: red;">CharacterConcatenate Investigate T mask:<o:p></o:p></span></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="color: red;">Job:<o:p></o:p></span></div><div class="MsoNormal"><img height="139" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image026.jpg" v:shapes="_x0000_i1038" width="573" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal" style="margin-left: .25in;">Add the columns which ever u want to concatenate and investigate:</div><div class="MsoNormal"><img height="288" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image028.jpg" v:shapes="_x0000_i1039" width="576" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal">At out put it gives like below</div><div class="MsoNormal"><img height="75" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image030.jpg" v:shapes="_x0000_i1040" width="564" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="color: red;">CharacterConcatenate Investigate C mask:<o:p></o:p></span></div><div class="MsoNormal"><span style="color: red;">Job:<o:p></o:p></span></div><div class="MsoNormal"><span style="color: red;"><img height="133" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image032.jpg" v:shapes="_x0000_i1041" width="540" /><o:p></o:p></span></div><div class="MsoNormal" style="margin-left: .25in;">Add the columns which ever u want to concatenate and investigate:</div><div class="MsoNormal"><img height="353" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image034.jpg" v:shapes="_x0000_i1042" width="576" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal">At out put it gives like below</div><div class="MsoNormal"><img height="243" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image036.jpg" v:shapes="_x0000_i1043" width="456" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="color: red;">CharacterConcatenate Investigate X mask:<o:p></o:p></span></div><div class="MsoNormal"><span style="color: red;">Job:<o:p></o:p></span></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><img height="114" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image038.jpg" v:shapes="_x0000_i1044" width="576" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal">Add the columns which ever u want to concatenate and investigate</div><div class="MsoNormal"><img height="317" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image040.jpg" v:shapes="_x0000_i1045" width="576" /></div><div class="MsoNormal">At out put it gives like below</div><div class="MsoNormal"><img height="159" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image042.jpg" v:shapes="_x0000_i1046" width="576" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="color: red;">Word Investigate Adding rule set to NAME field <o:p></o:p></span></div><div class="MsoNormal"><span style="color: red;">Job:<o:p></o:p></span></div><div class="MsoNormal"><span style="color: red;"><img height="166" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image044.jpg" v:shapes="_x0000_i1047" width="576" /><o:p></o:p></span></div><div class="MsoNormal">Select the Name rule set from USNAME</div><div class="MsoNormal"><img height="356" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image046.jpg" v:shapes="_x0000_i1048" width="576" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal">Select the Full name field from Available data columns</div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal">Output you will get like below</div><div class="MsoNormal"><img height="393" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image048.jpg" v:shapes="_x0000_i1049" width="251" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><b><span style="color: red;">Word Investigate Adding rule set to NAME field <o:p></o:p></span></b></div><div class="MsoNormal"><b><span style="color: red;">Token report and pattern report<o:p></o:p></span></b></div><div class="MsoNormal"><b><span style="color: red;">Job:<o:p></o:p></span></b></div><div class="MsoNormal"><img height="248" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image050.jpg" v:shapes="_x0000_i1050" width="575" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal">Select the Name rule set from USNAME</div><div class="MsoNormal">And add fullname field from available data columns</div><div class="MsoNormal">If u want two reports tokenreport and pattern report tick mark for both token report box and pattern report box</div><div class="MsoNormal"><br /></div><div class="MsoNormal"><img height="359" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image052.jpg" v:shapes="_x0000_i1051" width="576" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal">Pattern report output u will get like below:</div><div class="MsoNormal">Word investigate pattern report it give 5 columns at output</div><div class="MsoNormal">1.qsInvColumnName</div><div class="MsoNormal">2.qsInvPattern</div><div class="MsoNormal">3.qsInvSample</div><div class="MsoNormal">4.qsInvCount</div><div class="MsoNormal">5.QsInvPercent</div><div class="MsoNormal"><br /></div><div class="MsoNormal">Pattern report Output:</div><div class="MsoNormal"><img height="258" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image054.jpg" v:shapes="_x0000_i1052" width="461" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal">Token Report output u will get like below</div><div class="MsoNormal">Word investigate pattern report it give 3 columns at output</div><div class="MsoNormal">1.qsInvCount</div><div class="MsoNormal">2.qsInvWord</div><div class="MsoNormal">3.qsInvClassCode</div><div class="MsoNormal"><br /></div><div class="MsoNormal">Token report Output</div><div class="MsoNormal"><img height="274" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image056.jpg" v:shapes="_x0000_i1053" width="250" /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><b><span style="color: red;">Classification Code table:<o:p></o:p></span></b></div><div class="MsoNormal"><span style="color: red;"><img height="161" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image058.jpg" v:shapes="_x0000_i1054" width="454" /><o:p></o:p></span></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="color: red;">2.STANDARDIZE STAGE:<o:p></o:p></span></div><div class="MsoNormal"><span style="color: red;">Example job:<o:p></o:p></span></div><div class="MsoNormal"><span style="color: red;"><img height="138" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image060.jpg" v:shapes="_x0000_i1055" width="558" /><o:p></o:p></span></div><div class="MsoNormal"><br /></div><div class="MsoNormal">Open standardize stage and select rule set text field and select standardize rules folder with in that folder select OTHER folder and select COUNTRY folder and select COUNTRY</div><div class="MsoNormal"><span style="font-family: Wingdings; mso-ascii-font-family: "Times New Roman"; mso-char-type: symbol; mso-hansi-font-family: "Times New Roman"; mso-symbol-font-family: Wingdings;">à</span>Next in literal text field enter ZQUSZQ</div><div class="MsoNormal"><span style="font-family: Wingdings; mso-ascii-font-family: "Times New Roman"; mso-char-type: symbol; mso-hansi-font-family: "Times New Roman"; mso-symbol-font-family: Wingdings;">à</span>Add column which column u want to select from available data columns</div><div class="MsoNormal"><span style="font-family: Wingdings; mso-ascii-font-family: "Times New Roman"; mso-char-type: symbol; mso-hansi-font-family: "Times New Roman"; mso-symbol-font-family: Wingdings;">à</span>Select column name <literal> and AddressLine1, AddressLine1, city, state, Zip columns in selected columns list</div><div class="MsoNormal">You will get the below screen after entering all</div><div class="MsoNormal"><span style="color: red;"><img height="325" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image062.jpg" v:shapes="_x0000_i1056" width="576" /><o:p></o:p></span></div><div class="MsoNormal"><span style="font-family: Wingdings; mso-ascii-font-family: "Times New Roman"; mso-char-type: symbol; mso-hansi-font-family: "Times New Roman"; mso-symbol-font-family: Wingdings;">à</span>Next click on OK</div><div class="MsoNormal">you will get the below screen</div><div class="MsoNormal"><span style="color: red;"><img height="283" src="file:///C:/Users/Srkar/AppData/Local/Temp/msohtmlclip1/01/clip_image064.jpg" v:shapes="_x0000_i1057" width="575" /><o:p></o:p></span></div><div class="MsoNormal">next click on Ok</div><div class="MsoNormal"><br /></div><div class="MsoNormal">Output:</div><div class="MsoNormal"><br /></div><div class="MsoNormal"><br /></div><div class="MsoNormal"><span style="color: red;">At output it gives additional column ie ISOCOUNTRYCODE AND<o:p></o:p></span></div><div class="MsoNormal"><span style="color: red;"><br /></span></div><div class="MsoNormal"><span style="color: red;"><br /></span></div><div class="MsoNormal"><span style="color: red;"><br /></span></div><div class="MsoNormal"><span style="color: red;"><br /></span></div><div class="MsoNormal"><span style="color: red;"><br /></span></div><div class="MsoNormal"><span style="color: red;"><br /></span></div><div class="MsoNormal"><span style="color: red;"><br /></span></div><div class="MsoNormal"><span style="color: red;"><br /></span></div><div class="MsoNormal"><b><a href="http://foryooth.blogspot.in/search/label/Data%20Stage" target="_blank">click here to  more datastage tutorials</a></b></div><div class="MsoNormal"><span style="color: red;"><br /></span></div><div class="MsoNormal"><o:p><br /></o:p></div><div class="MsoNormal"><o:p><br /></o:p></div></div>

IBM Info sphere quality stage in datastage tutorial

1.Why investigate: à Discover trends and potential anomalies in data à Identify invalid and default values in a data à Verify the reliabil...

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;"><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><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;"><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><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;"><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><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...