Friday, April 14, 2017

Genpact PLSQL/SQL Interview Questions

Tell about your experience and your skills.
SQL Loader how it is used.
when External tables are used.
Difference between external tables and SQL loader.
Can i have the same name for a procudure and function? Explain the reason.
What is bitmap index.
is view updatable.
How to make view non updatable.
What are the conditions in which view is not  updatable.
What is materializabe view, In  which scenario will you use materializable view.
What is instead of trigger.
How do you generated 1 to 9 numbers.
I have a table with values in a column from 1 to 9. I want to show all the values from 1 to 9 in a single one column of a row.
There is a name called 'ALEXANDER'. How do u find the count of A in that name.
From the above question find the count of a with out using regular expression.
What is the difference between RANK and DENSE_RANK.
I have  a histroy table , where it will have the history of the rates daily. I want to keep the data for 90 days and from 91 onwards no need to keep in the table. How will you desing a table.
What is the cost in SQL Explain Plan.
What other things you look while checking the SQL plan.
If the plan Cost is low , does that mean the sql query is faster. Explain .

4 comments :

  1. Salaam,


    I love all the posts, I really enjoyed.
    I would like more information about this, because it is very nice., Thanks for sharing.

    SMO being distributed now via NuGet is great. However there is an issue that not only myself, but others have run into as well (see comments on this page): https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/installing-smo
    When trying to do a restore operation on PCs that do not have things like Visual Studio installed, you'll receive this error.
    Could not load file or assembly 'Microsoft.SqlServer.BatchParser.dll' or one of its dependencies. The specified module could not be found.
    The file exists in the deploy directory, but it seems to have issues loading it for whatever reason. No one has responded on docs website, so posting here hoping to get some answers on what's up here. Bug in the NuGet package?
    Once you install like VS2017, this error goes away, because BatchParser is in the GAC at that point, but the docs say now:
    This is a replacement for SharedManagementObjects.msi, which was previously released as part of the SQL Feature Pack for each release of SQL Server. Applications that use SMO should be updated to use the NuGet package instead and will be responsible for ensuring the binaries are installed with the application being developed.
    So, what needs to happen here? Or is there a way to remove the dependency from BatchParser from restore operations, which is where I'm hitting it?




    Follow my new blog if you interested in just tag along me in any social media platforms!


    Obrigado,


    ReplyDelete
  2. Hi There,


    A really interesting, clear and easily readable Oracle SQL, PL/SQL, Linux article of interesting and different perspectives' will clap. So much is so well covered here.

    I have a job which picks a record from a cursor and then it calls a stored procedure which processes the record picked up from the cursor.

    The stored procedure has multiple queries to process the record. In all, procedure takes about 0.3 seconds to process a single record picked up by the cursor but since cursor contains more than 1 Lac records it takes hours to complete the job.

    The queries in the stored procedure are all optimized

    I was thinking of making the procedure run in multi threaded way as in java and other programming language.
    Can it be done in oracle? or is there any other way I can reduce the run time of my job.

    Follow my new blog if you interested in just tag along me in any social media platforms!


    Regards,
    Hima

    ReplyDelete
  3. Hi There,


    A really interesting, clear and easily readable Oracle SQL, PL/SQL, Linux article of interesting and different perspectives' will clap. So much is so well covered here.

    I have a job which picks a record from a cursor and then it calls a stored procedure which processes the record picked up from the cursor.

    The stored procedure has multiple queries to process the record. In all, procedure takes about 0.3 seconds to process a single record picked up by the cursor but since cursor contains more than 1 Lac records it takes hours to complete the job.

    The queries in the stored procedure are all optimized

    I was thinking of making the procedure run in multi threaded way as in java and other programming language.
    Can it be done in oracle? or is there any other way I can reduce the run time of my job.

    Follow my new blog if you interested in just tag along me in any social media platforms!


    Regards,
    Charles

    ReplyDelete
  4. Hi Buddy,


    Fully agree on Oracle SQL, PL/SQL, Linux . We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt .

    I have below kind of data

    Transaction | Debit | Credit | Begening Balance | Ending balance
    INV1 | 100 | | 300 | 400
    INV2 | | 200 | |
    INV3 | 130 | | |
    INV4 | | 150 | |

    This is above data , and we need to calculate begening balance and ending balance.
    Begening balance of INV2 will be ending balance of INV1, and begening balance of INV3
    will be ending balance of INV2, … goes on..
    And ending balance will be calculated as begening balance + debit_amount or begening_balance - credit_amount. but how can i put ending balance of current row to begening_balance of next row.
    Note - First row of begening balance and ending balance will come through gl_balances itself, but need to calculated values from 2nd row.

    Very useful post !everyone should learn and use it during their learning path.


    MuchasGracias,
    Radhey

    ReplyDelete