Database Interview Questions

Explain Teradata utilities. What is MultiLoad , Fast Load, Tpump ?
When do you use each of these? Why? Explain in detail.
Explain Teradata Architecture in detail with diagram.
What are default access rights in teradata? What explicit right can be given to a User?
What happens in a conflict? How do you handle that?
Explain the Teradata Primary Index Mechanics in detail with diagram.
In general, how do you optimze any SQL in Teradata?
What are the various indexes in Teradata? How to use them? Why are they preferred?
Some Data Warehousing concept questions like – What are dimentions, facts?
Explain and compare pros and cons of Snow Flake Schemas, and Start Schemas.
What is Spool Space? Why do you get spool space errors? How do trouble-shoot them?
Explain Stored Procedure in detail.. its structure…exact syntax .. in and out?
Why are they to be used? Compare it with a Macro.
How do you do Backup and recovery in Teradata?
What tools would you use for that?
How do you create tables – exact syntax, and create profiles, Users in Teradata?
How would you load a very large file in Teradata in general? What utility would you use for it? Why?
What are non-additive facts?
What are the different methods ot loading a dimension table? a fact table etc?
What are aggregate tables? How do you design them using Teradata?

1. What are New features of Oracle 8i
2. Types of indexes and the rationale behind choosing a particular index for a situation.
3. How the Indexes are Stored in the Oracle Database?
4. How the Oracle Prepares the Execution Plan and How it chooses the Optimal one?
5. Various types of hints and their usage
6. Various types of joins
7. Optimizers in Oracle (Rule based & cost based, Effect of hints in rule based environment )
8. What is the difference between UNION and UNIONALL
9. What is Partitioning? Types of partitioning. Explain? What is the new kind of partitioning type introduced in 9i?
10. What are the things that you will consider while creating indexes on partitioning tables.
11. What happens to the global index when I truncate the data in one of the partition?
12. What kind of work you have done in database archiving?
13. Have you ever worked in an environment where sybase and oracle databases were involved? Like migrating data between them.?
14. What are the advantages of running a database in Archivelog mode?
15. What are the advantages of running a database in NoArchivelog mode?
16. What is Materialized view? What is a snapshot? What are the similarities and differences between Materialized views and snapshots?
17. How do you setup a replication site?
18. What is a Nested Loop Join?
19. When do you go for hash partitioning?
20. What tuning techniques you have used in your projects?
21. Tell about other projects where you have solved technically challenging problems.
22. Have you worked on RAID technology? Which is better RAID 0+1 or RAID 5?
23. How do get database name at SQL Prompt?
24. Does your current role as database consultant, involve customer interaction?
25. Scenario: A bulk loading onto a very large table is taking considerable long time. What are the various factors (h/w, s/w) that are likely to be the reasons for this?
26. What is the scenario where you take the database to NoArchivelog mode?
27. Does the query needs a hint to access a materialized view?
28. What is Analyze command? What kind of statistics does it generate?
29. How do you know the total number of rows in a table?
30. Have you worked on Oracle 9i?
31. Have you written any shell scripts?
32. What are Archived Logs?
33. What are bit-map indexes? How does they work?
34. What are database links?

Teradata can perform a sync scan (synchronized full table scan) if more than one query is scanning the same table. It does this by starting the second query scan at the same data block in the table where the first query is positioned. Then the queries share the reading of data blocks going forward. When the first one finishes, the second query continues the scan until it reaches its starting point.

While synchronization is encouraged, it”s not forced. The optimizer selects sync scan only on tables too big to fit into the data cache. Small table blocks are likely to be cached, and therefore their I/Os are already potentially shared.

Once a sync scan begins, the DBMS makes a note of it and periodically checks the progress of all participants. It keeps an eye on how many tables are involved in the scan and how much memory is available to support the activity, and it monitors each query”s progress. When synchronization is no longer optimal, the DBMS will spin off one or more of the participants, preventing fast queries from being hampered by slow queries.

No facility to declare variables inside a Teradata macro, however you can pass values into the macro by means of input parameters. These values will then be embedded in the SQL statements that the macro contains.

Teradata stored procedure supports IN, OUT and INOUT parameters, as well as the ability to declare variables within the procedure itself. Such variables could be useful as counters when you have looping logic.

Teradata Interview Questions Website http://teradata.techinterviewquestions.com/

2802: Duplicate row error in DatabaseName.TableName.

When trying to create duplicate row in table using INSERT or UPDATE statement.

SET table DatabaseName.TableName

ColumnName 1 ColumnName 2 ColumnName 3
100 Ben Good
200 Ken Excellent
100 Tom Good

INSERT into DatabaseName.TableName
Values(200,”ken,”Excellent”)

UPDATE DatabaseName.TableName
set ColumnName 2 = Ben
Where ColumnName 2 = Tom ;

Above sqls will give 2802: Duplicate row error in DatabaseName.TableName.

MDIFF

MAVG

MSUM

CSUM