«

»

Mar 09

Interview Questions : SET 2

Question 1:

What is the fallback or no fallback option while creating table DDL?

Answer:

FALLBACK requests that a second copy of the each rows inserted into the table has a duplicate copy in another AMP in the same cluster. This way we can make the copy of the data inserted into tables. While NO FALLBACK will not store any duplicate rows.

||||=====================================================================================================================================================||||

Question 2:

How to find the duplicate rows in the table?

Answer:

Group by those fields and add a count greater than 1 condition for those columns

For example –

SELECT name, COUNT (*) FROM TABLE EMPLOYEE GROUP BY name HAVING COUNT (*)>1;

||||=====================================================================================================================================================||||

Question 3:

Which is more efficient GROUP BY or DISTINCT to find duplicates?

Answer:

With more duplicates GROUP BY is more efficient while if we have fewer duplicates the DISTINCT is efficient.

||||=====================================================================================================================================================||||

Question 4:

What is the difference between TIMESTAMP (0) and TIMESTAMP (6)?

Answer:

TIMESTAMP (0) is CHAR (19) while TIMESTAMP (6) is CHAR (26). The major difference is that TIMESTAMP (6) has microsecond too.

||||=====================================================================================================================================================||||

Question 5:

What is spool space and when running a job if it reached the maximum spool space how you solve the problem?

Answer:

Spool space is the space which is required by the query for processing or to hold the rows in the answer set. Spool space reaches maximum when the query is not properly optimized. We must use appropriate condition in WHERE clause and JOIN on correct columns to optimize the query.

 

 

2 comments

  1. Siddhu

    what is mload?

  2. Nitin

    thanks …. could you pls post query related questions as in some brain teasers for writing an optimised queries

    like de one u have posted above about finding duplicates. i would like to try all de possible ways for getting an output

    eg: to find the records that are uncommon between two tables without using joins
    and queries like removing or block duplicates using rank function

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>