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.