Explore topic-wise InterviewSolutions in .

Normalization is the process of efficiently organizing data in a DATABASE. The two goals of the normalization process are eliminate redundant data (storing the same data in more than one table) and ensure data dependencies make SENSE (only storing related data in the table).

FIRST normalization form:

• Eliminate duplicate columns from the same table.
• Create separate tables for each group of related data and identify each row with a unique column or set of columns (primary key).

Second normal form:

• Removes sub set of data that apply to multiple rows of table and place them in separate table.
• Create relationships between these new tables and their predecessors through the use of FOREIGN keys.

THIRD normal form:

• Remove column that are not dependent upon the primary key.

Normalization is the process of efficiently organizing data in a database. The two goals of the normalization process are eliminate redundant data (storing the same data in more than one table) and ensure data dependencies make sense (only storing related data in the table).

First normalization form:

Second normal form:

Third normal form:

Primary INDEX MECHANISM is the ideal type of index for DATA warehouse.

Primary Index mechanism is the ideal type of index for data warehouse.

OLTP designs are for real time DATA and they are not normalized and preaggregated. They are not GOOD for decision SUPPORT SYSTEMS.

OLTP designs are for real time data and they are not normalized and preaggregated. They are not good for decision support systems.

STAR SCHEMA is De-NORMALIZED and SNOWFLAKE schema is normalized.

Star schema is De-normalized and snowflake schema is normalized.

Spool space is used to hold intermediate rows during processing, and to hold the rows in the answer SET of a transaction. Spool space REACHES maximum when the query is not properly optimized. Use APPROPRIATE conditions in WHERE clause of the query to LIMIT the answer set.

Spool space is used to hold intermediate rows during processing, and to hold the rows in the answer set of a transaction. Spool space reaches maximum when the query is not properly optimized. Use appropriate conditions in WHERE clause of the query to limit the answer set.

TPump provides an alternative to MultiLoad for the low VOLUME batch maintenance of LARGE databases under control of a Teradata system. Instead of updating Teradata databases overnight, or in batches throughout the day, TPump updates information in real TIME, acquiring every BIT of data from the client system with low processor utilization. It does this through a continuous feed of data into the data warehouse, rather than the TRADITIONAL batch updates. Continuous updates results in more accurate, timely data. And, unlike most load utilities, TPump uses row hash locks rather than table level locks. This allows you to run queries while TPump is running. This also means that TPump can be stopped instantaneously. As a result, businesses can make better decisions that are based on the most current data.

TPump provides an alternative to MultiLoad for the low volume batch maintenance of large databases under control of a Teradata system. Instead of updating Teradata databases overnight, or in batches throughout the day, TPump updates information in real time, acquiring every bit of data from the client system with low processor utilization. It does this through a continuous feed of data into the data warehouse, rather than the traditional batch updates. Continuous updates results in more accurate, timely data. And, unlike most load utilities, TPump uses row hash locks rather than table level locks. This allows you to run queries while TPump is running. This also means that TPump can be stopped instantaneously. As a result, businesses can make better decisions that are based on the most current data.

An INNER join gets data from both tables where the specified data exists in both tables. An OUTER join gets data from the SOURCE table at all times, and RETURNS data from the outer JOINED table ONLY if it matches the criteria.

An inner join gets data from both tables where the specified data exists in both tables. An outer join gets data from the source table at all times, and returns data from the outer joined table ONLY if it matches the criteria.

YES we can COLLECT STATISTICS on table LEVEL. The syntax is COLLECT STAT ON TAB_A;

Yes we can collect statistics on table level. The syntax is COLLECT STAT ON TAB_A;

YES we can COLLECT STATISTICS on MULTIPLE COLUMNS.

Yes we can collect statistics on multiple columns.

Collects demographic DATA for one or more columns of a table, hash index, or join index, computes a statistical profile of the COLLECTED data, and STORES the synopsis in the data dictionary. The OPTIMIZER uses the synopsis data when it generates its table access and join PLANS.

Collects demographic data for one or more columns of a table, hash index, or join index, computes a statistical profile of the collected data, and stores the synopsis in the data dictionary. The Optimizer uses the synopsis data when it generates its table access and join plans.

Creating a SECONDARY index causes TERADATA to build a sub-table to contain its index rows, thus adding another set of rows that requires updating each time a table row is inserted, deleted, or UPDATED. Secondary index sub-tables are also duplicated WHENEVER a table is DEFINED with FALLBACK, so the maintenance overhead is effectively doubled.

Creating a secondary index causes Teradata to build a sub-table to contain its index rows, thus adding another set of rows that requires updating each time a table row is inserted, deleted, or updated. Secondary index sub-tables are also duplicated whenever a table is defined with FALLBACK, so the maintenance overhead is effectively doubled.

PRIMARY index is the mechanism for assigning a data row to an AMP and a LOCATION on the AMP’s DISKS. Indexes also used to ACCESS rows from a table without having to search the ENTIRE table. Secondary indexes enhance set selection by specifying access paths less frequently used than the primary index path. Secondary indexes are also used to facilitate aggregate operations.

If a secondary index covers a query, then the Optimizer determines that it would be less costly to accesses its rows directly rather than using it to access the base table rows it points to. Sometimes multiple secondary indexes with low individual selectivity can be overlapped and bit mapped to provide enhanced.

Primary index is the mechanism for assigning a data row to an AMP and a location on the AMP’s disks. Indexes also used to access rows from a table without having to search the entire table. Secondary indexes enhance set selection by specifying access paths less frequently used than the primary index path. Secondary indexes are also used to facilitate aggregate operations.

If a secondary index covers a query, then the Optimizer determines that it would be less costly to accesses its rows directly rather than using it to access the base table rows it points to. Sometimes multiple secondary indexes with low individual selectivity can be overlapped and bit mapped to provide enhanced.

An index is a MECHANISM that can be used by the SQL query optimizer to make table access more performant. Indexes enhance DATA access by providing a moreor- less direct path to stored data and AVOIDING the necessity to perform full table scans to LOCATE the small number of ROWS you typically want to retrieve or update.

An index is a mechanism that can be used by the SQL query optimizer to make table access more performant. Indexes enhance data access by providing a moreor- less direct path to stored data and avoiding the necessity to perform full table scans to locate the small number of rows you typically want to retrieve or update.

Clique is a group of DISK ARRAYS PHYSICALLY cabled to a group of NODES.

Clique is a group of disk arrays physically cabled to a group of nodes.

Node is a database RUNNING in a server. We USED 318 nodes and each node has 2 to 4 AMPS.

Node is a database running in a server. We used 318 nodes and each node has 2 to 4 AMPS.

TIMESTAMP (0) is CHAR (19) and TIMESTAMP (6) is CHAR (26)
EVERYTHING is same EXCEPT that TIMESTAMP (6) has MICROSECONDS too.

TIMESTAMP (0) is CHAR (19) and TIMESTAMP (6) is CHAR (26)
Everything is same except that TIMESTAMP (6) has microseconds too.

With more DUPLICATES GROUP BY is more EFFICIENT, if only a few duplicates EXIST DISTINCT is more efficient.

With more duplicates GROUP BY is more efficient, if only a few duplicates exist DISTINCT is more efficient.

By USING SHOW table COMMAND.

By using show table command.

Up to 64 TABLES.

Up to 64 tables.

I USE explain statement to CHECK if the QUERY is doing what I wanted it to do.

I use explain statement to check if the query is doing what I wanted it to do.

Using RELEASE MLOAD. It removes access LOCKS from the target tables in Teradata. It MUST be ENTERED from BTEQ and not from MultiLoad. To proceed, you can do RELEASE MLOAD <table name>.

Using RELEASE MLOAD. It removes access locks from the target tables in Teradata. It must be entered from BTEQ and not from MultiLoad. To proceed, you can do RELEASE MLOAD <table name>.

FALLBACK requests that a SECOND COPY of each row inserted into a table be stored on another AMP in the same CLUSTER. This is done when AMP goes down or disk FAILS.

FALLBACK requests that a second copy of each row inserted into a table be stored on another AMP in the same cluster. This is done when AMP goes down or disk fails.

SUBSTRING (string_expression, N1 [N2]).

SUBSTRING (string_expression, n1 [n2]).

Not NECESSARY but it is GOOD to add a QUIT statement after a QUERY.

Not necessary but it is good to add a QUIT statement after a query.

For FASTER RECORD SEARCH.

For faster record search.

No.

No.

No.

No.

Inserting data records into the table using multiple insert statements. Putting a SEMI COLON in front of the KEY word INSERT in the next statement rather than terminating the first statement with a semi colon ACHIEVES it.

Inserting data records into the table using multiple insert statements. Putting a semi colon in front of the key word INSERT in the next statement rather than terminating the first statement with a semi colon achieves it.

An inner join gets data from both tables where the specified data exists in both tables. An outer join gets data from the source table at all TIMES, and RETURNS data from the outer JOINED table ONLY if it matches the CRITERIA.

An inner join gets data from both tables where the specified data exists in both tables. An outer join gets data from the source table at all times, and returns data from the outer joined table ONLY if it matches the criteria.

FastLoad.

FastLoad.

FASTLOAD uses multiple sessions to quickly load large AMOUNT of data on empty table. MULTILOAD is used for high-volume maintenance on tables and VIEWS. It works with non-empty tables also. Maximum 5 tables can be used in MultiLoad.

FastLoad uses multiple sessions to quickly load large amount of data on empty table. MultiLoad is used for high-volume maintenance on tables and views. It works with non-empty tables also. Maximum 5 tables can be used in MultiLoad.