Interview Questions.

Question 1. What Is Basic Teradata Query Language?

Answer :

1. It lets in us to jot down SQL statements at the side of BTEQ commands. We can use BTEQ for importing, exporting and reporting purposes.
2. The instructions start with a (.) dot and may be terminated by using the use of (;), it isn't always mandatory to use (;).
3. BTEQ will assume any component written without a dot as a SQL announcement and calls for a (;) to terminate it.

Question 2. What Is The Difference Between Fastload And Multiload?

Answer :

FastLoad uses more than one sessions to fast load big quantity of statistics on empty table. MultiLoad is used for excessive-quantity preservation on tables and perspectives. It works with non-empty tables also. Maximum five tables may be utilized in MultiLoad.

Microstrategy Interview Questions
Question three. Which Is Faster Fastload Or Multiload?

Answer :

FastLoad.

Question 4. Difference Between Inner Join And Outer Join?

Answer :

An internal be part of gets data from each tables where the required records exists in both tables. An outer be a part of receives records from the source desk always, and returns information from the outer joined desk ONLY if it suits the criteria.

Microstrategy Tutorial
Question five. What Is Multi Insert?

Answer :

Inserting statistics facts into the desk the use of a couple of insert statements. Putting a semi colon in front of the key phrase INSERT within the next announcement in preference to terminating the primary announcement with a semi colon achieves it.

Insert into Sales “choose * from patron”;
Insert into Loan “select * from patron”;
Informatica Interview Questions
Question 6. Is Multi Insert Ansi Standard?

Answer :

No.

Question 7. How Do You Create A Table With An Existing Structure Of Another Table With Data And With No Data?

Answer :

Create desk Customerdummy as Customer with records / without a records;

Informatica Tutorial MySQL Interview Questions
Question eight. What Is The Opening Step In Basic Teradata Query Script?

Answer :

.Logon tdipid/username, password.

Question nine. Can You Fastexport A Field, Which Is Primary Key By Putting Equality On That Key?

Answer :

No.

Oracle 11g Interview Questions
Question 10. Did You Write Stored Procedures In Teradata?

Answer :

No, because they end up a single amp operation and my business enterprise didn’t inspire that.

MySQL Tutorial
Question eleven. What Is The Use Of Having Index's On Table?

Answer :

For quicker record seek.

Hadoop Interview Questions
Question 12. Is It Necessary To Add? Quit Statement After A Bteq Query When I Am Calling It In A Unix Environment?

Answer :

Not important but it is right to add a QUIT assertion after a question.

Microstrategy Interview Questions
Question 13. There Is A Column With Date In It. If I Want To Get Just Month How It Can Be Done? Can I Use Sub String?

Answer :

Sub string is used with char fields. So it cannot be used. To extract month from a date column, ex pick out extract (month from <column name>). Same issue for year or day. Or hour or minutes if it’s a time stamp (choose extract (minute from column call).

Oracle 11g Tutorial
Question 14. What's The Syntax Of Sub String?

Answer :

SUBSTRING (string_expression, n1 [n2]).

Question 15. While Creating Table My Dba Has Fallback Or No Fallback In His Ddl. What Is That?

Answer :

FALLBACK requests that a 2nd copy of every row inserted right into a table be saved on any other AMP inside the equal cluster. This is done when AMP is going down or disk fails.

TeraData DBA Interview Questions
Question sixteen. My Table Got Locked During Mload Due To A Failed Job. What Do I Do To Perform Other Operations On It?

Answer :

Using RELEASE MLOAD. It removes get right of entry to locks from the target tables in Teradata. It have to be entered from BTEQ and no longer from MultiLoad. To continue, you may do RELEASE MLOAD <table name>.

Hadoop Tutorial
Question 17. How To Find Duplicates In A Table?

Answer :

Group by the ones fields and select id, be counted(*) from desk group through identification having depend (*) > 1.

MYSQL DBA Interview Questions
Question 18. How Do You Verify A Complicated Sql?

Answer :

I use give an explanation for declaration to test if the query is doing what I wanted it to do.

Informatica Interview Questions
Question 19. How Many Tables Can You Join In V2r5?

Answer :

Up to sixty four tables.

Question 20. How Do You See A Ddl For An Existing Table?

Answer :

By the use of display desk command.

Database Administration Interview Questions
Question 21. Which Is More Efficient Group By Or Distinct To Find Duplicates?

Answer :

With greater duplicates GROUP BY is more green, if only a few duplicates exist DISTINCT is extra green.

Question 22. Syntax For Case When Statement?

Answer :

CASE value_expression_1 WHEN value_expression_n THEN scalar_expression_n
END;

Question 23. What's The Difference Between Timestamp (zero) And Timestamp (6)?

Answer :

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

DB2 SQL Programming Interview Questions
Question 24. How Do You Determine The Number Of Sessions?

Answer :

Teradata performance and workload.
Client platform kind, performance and workload.
Channel performance for channel attached structures.
Network topology and overall performance for community attached structures.
Volume of facts to be processed with the aid of the application.
MySQL Interview Questions
Question 25. What Is Node? How Many Nodes And Amps Used In Your Previous Project?

Answer :

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

Question 26. What Is A Clique?

Answer :

Clique is a group of disk arrays bodily cabled to a set of nodes.

Hadoop Administration Interview Questions
Question 27. What Is The Purpose Of Indexes?

Answer :

An index is a mechanism that can be used by the SQL query optimizer to make table get admission to greater performant. Indexes enhance information access by means of supplying a moreor- less direct path to saved information and averting the need to carry out complete table scans to locate the small range of rows you usually need to retrieve or update.

Oracle 11g Interview Questions
Question 28. What Is Primary Index And Secondary Index?

Answer :

Primary index is the mechanism for assigning a records row to an AMP and a location on the AMP’s disks. Indexes also used to access rows from a table while not having to go looking the whole desk. Secondary indexes beautify set choice by specifying access paths less often used than the number one index course. Secondary indexes also are used to facilitate aggregate operations.

If a secondary index covers a question, then the Optimizer determines that it'd be less luxurious to accesses its rows immediately in place of the use of it to get right of entry to the bottom table rows it points to. Sometimes a couple of secondary indexes with low character selectivity may be overlapped and bit mapped to offer more desirable.

Question 29. What Are The Things To Be Considered While Creating Secondary Index?

Answer :

Creating a secondary index reasons Teradata to construct a sub-table to include its index rows, thus including another set of rows that requires updating on every occasion a desk row is inserted, deleted, or updated. Secondary index sub-tables are also duplicated each time a table is defined with FALLBACK, so the upkeep overhead is successfully doubled.

Java Hadoop Developer Interview Questions
Question 30. What Is Collect Statistics?

Answer :

Collects demographic facts for one or more columns of a table, hash index, or be a part of index, computes a statistical profile of the amassed facts, and shops the synopsis within the information dictionary. The Optimizer makes use of the synopsis data while it generates its table access and join plans.

Question 31. Can We Collect Statistics On Multiple Columns?

Answer :

Yes we can gather records on multiple columns.

Question 32. Can We Collect Statistics On Table Level?

Answer :

Yes we will collect data on desk stage. The syntax is COLLECT STAT ON TAB_A;

Informatica MDM Interview Questions
Question 33. What Is Inner Join And Outer Join?

Answer :

An inner join receives information from each tables where the specified information exists in both tables. An outer be part of gets data from the supply table always, and returns facts from the outer joined table ONLY if it suits the criteria.

Hadoop Interview Questions
Question 34. When Tpump Is Used Instead Of Multiload?

Answer :

TPump presents an opportunity to MultiLoad for the low quantity batch upkeep of massive databases below manipulate of a Teradata gadget. Instead of updating Teradata databases overnight, or in batches at some stage in the day, TPump updates information in actual time, obtaining every little bit of information from the client system with low processor utilization. It does this thru a non-stop feed of records into the information warehouse, in preference to the traditional batch updates. Continuous updates effects in more correct, well timed statistics. And, unlike maximum load utilities, TPump uses row hash locks in place of table level locks. This allows you to run queries even as TPump is strolling. This also manner that TPump can be stopped right away. As a result, corporations could make higher decisions which are based totally on the most modern statistics.

Question 35. What Is Spool Space And When Running A Job If It Reaches The Maximum Spool Space How You Solve The Problem?

Answer :

Spool space is used to keep intermediate rows during processing, and to hold the rows in the solution set of a transaction. Spool space reaches most while the question is not properly optimized. Use suitable situations in WHERE clause of the question to limit the answer set.

Question 36. What Is Data Mart?

Answer :

A records mart is a unique purpose subset of corporation information utilized by a specific department, function or software. Data marts may additionally have each precis and details facts, but, generally the information has been pre aggregated or converted in a few manner to higher manage the unique form of requests of a particular user network. Data marts are categorised as unbiased, logical and dependant data marts.

TeraData DBA Interview Questions
Question 37. Difference Between Star And Snowflake Schemas?

Answer :

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

Question 38. Why Are Oltp Database Designs Not Generally A Good Idea For A Data Warehouse?

Answer :

OLTP designs are for real time records and they're not normalized and preaggregated. They aren't precise for selection aid systems.

Question 39. What Type Of Indexing Mechanism Do We Need To Use For A Typical Data Warehouse?

Answer :

Primary Index mechanism is the suitable sort of index for statistics warehouse.

Question 40. What Is Real Time Data Warehousing?

Answer :

Real-time records warehousing is a mixture of  matters:

 real-time interest and
 records warehousing.
Real-time hobby is activity that is going on right now. The activity might be anything such as the sale of widgets. Once the activity is entire, there may be records approximately it. Data warehousing captures business hobby records. Real-time information warehousing captures business interest information as it occurs. As soon because the business interest is entire and there's facts approximately it, the finished interest facts flows into the statistics warehouse and will become to be had right away. In other phrases, real-time information warehousing is a framework for deriving data from information as the information turns into to be had.

MYSQL DBA Interview Questions
Question forty one. What Is Ods?

Answer :

An operational statistics keep (ODS) is typically a "unload" of applicable statistics from a totally small variety of structures (regularly simply one) commonly with little or no transformation. The benefits are an advert hoc question database, which does now not affect the operation of systems required to run the enterprise. ODS’s commonly deal with information "uncooked" and "cutting-edge" and might answer a restricted set of queries as a result.

Question 42. What Is Real Time And Near Real Time Data Warehousing?

Answer :

The distinction among actual time and near real time may be summed up in one phrase: latency. Latency is the time lag that is among an pastime completion and the completed pastime information being available within the records warehouse. In real time, the latency is negligible while in near real time the latency is a tangible time frame which include two hours.

Database Administration Interview Questions
Question 43. What Are Normalization, First Normal Form, Second Normal Form And Third Normal Form?

Answer :

Normalization is the system of effectively organizing information in a database. The two desires of the normalization method are remove redundant facts (storing the equal data in a couple of desk) and make sure records dependencies make sense (only storing related records in the table).

First normalization shape:

Eliminate reproduction columns from the identical desk.
Create separate tables for every organization of associated information and identify each row with a completely unique column or set of columns (primary key).
Second normal shape:

Removes sub set of statistics that follow to more than one rows of desk and location them in separate desk.
Create relationships among these new tables and their predecessors through using foreign keys.
Third normal form:

Remove column that are not based upon the primary key.
Question 44. What Is Fact Table?

Answer :

The centralized table in a celebrity schema is known as as FACT desk i.E; a table in that includes records and connected to dimensions. A truth desk commonly has two styles of columns: those who include records and people which can be overseas keys to measurement tables. The primary key of a reality table is often a composite key this is made of all of its foreign keys. A fact table might incorporate either element stage facts or data which have been aggregated (fact tables that include aggregated information are regularly as a substitute known as summary tables). In the actual global, it's far viable to have a fact table that carries no measures or statistics. These tables are referred to as as Factless Fact tables.

Question 45. What Is Etl?

Answer :

Extract, transformation, and loading. ETL refers back to the strategies concerned in gaining access to and manipulating supply statistics and loading it into goal database. The first step in ETL process is mapping the facts between source systems and target database (facts warehouse or facts mart). The 2d step is cleansing of supply statistics in staging area. The third step is reworking cleansed supply statistics and then loading into the goal gadget. Note that ETT (extract, transformation, transportation) and ETM (extraction, transformation, circulate) are on occasion used rather than ETL.

Question 46. What Is Er Diagram?

Answer :

It is Entity relationship diagram. Describes the relationship most of the entities inside the database model.

Question forty seven. What Is Data Mining?

Answer :

Analyzing of huge volumes of distinctly easy information to extract important traits and new, higher stage statistics. For example, a records-mining application might examine millions of product orders to determine developments amongst top-spending clients, consisting of their probability to buy again, or their likelihood to replace to a one of a kind dealer.

Question 48. What Is Star Schema?

Answer :

Star Schema is a relational database schema for representing multi-dimensional statistics. It is the handiest shape of statistics warehouse schema that carries one or more dimensions and reality tables. It is known as a celebrity schema because the entityrelationship diagram among dimensions and fact tables resembles a star in which one fact desk is attached to multiple dimensions. The middle of the megastar schema consists of a large reality desk and it factors in the direction of the dimension tables. The benefits of superstar schema are cutting down, overall performance growth and easy expertise of information.

Question 49. What Is A Level Of Granularity Of A Fact Table?

Answer :

The components that make up the granularity of the fact desk correspond immediately with the size of the data model. Thus, when you define the granularity of the truth table, you perceive the size of the information version. The granularity of the fact table also determines how plenty garage area the database requires. For instance, remember the following viable granularities for a reality table:
• Product by using day via location
• Product through month by using place
The length of a database that has a granularity of product by using day with the aid of region might be an awful lot greater than a database with a granularity of product by way of month by means of place due to the fact the database incorporates information for each transaction made every day rather than a monthly summation of the transactions. You must carefully decide the granularity of your reality desk because too first-rate a granularity ought to result in an astronomically massive database. Conversely, too coarse granularity ought to mean the information isn't always designated enough for customers to carry out significant queries in opposition to the database.

Question 50. What Is A Dimension Table?

Answer :

Dimension table is one which describes the commercial enterprise entities of an organisation, represented as hierarchical, categorical information such as time, departments, locations, and products. Dimension tables are now and again referred to as research or reference tables. In a relational statistics modeling, for normalization purposes, united states research, nation research, county research, and city lookups are not merged as a single table. In a dimensional facts modeling (superstar schema), these tables might be merged as a single table called LOCATION DIMENSION for overall performance and slicing information necessities. This region measurement helps to compare the income in a single location with some other location. We may additionally see correct sales earnings in one area and loss in some other area. If it's miles a loss, the reasons for that is a new competitor in that vicinity, or failure of our advertising approach etc.

Question fifty one. What Are The Various Reporting Tools In The Market?

Answer :

Crystal reports, Business objects, micro strategy and and so forth.,

Question 52. What Are The Various Etl Tools In The Market?

Answer :

Ab Initio, Informatica and and many others.

Question fifty three. What Is A Three-tier Data Warehouse?

Answer :

The 3-tier differs from the 2-tier architecture by using strictly imposing a logical separation of the graphical person interface, commercial enterprise good judgment, and information. The 3-tier is widely used for facts warehousing today. Organizations that require more performance and scalability, the 3-tier structure may be greater appropriate. In this architecture, records extracted from legacy systems is cleansed, converted, and saved in excessive –velocity database servers, which might be used because the target database for the front-give up records get entry to.

Question 54. Differentiate Primary Key And Partition Key?

Answer :

Primary Key is a aggregate of precise and not null. It can be a collection of key values known as as composite primary key. Partition Key is a simply a part of Primary Key. There are several techniques of partition like Hash, DB2, and Random and so forth. While the use of Hash partition we specify the Partition Key.

Question 55. Differentiate Database Data And Data Warehouse Data?

Answer :

Data in a Database is Detailed or Transactional, Both Readable and Write in a position and modern.
Data in information warehouse is certain or summarized, garage area for ancient statistics.

Question 56. What Is Oltp?

Answer :

OLTP stands for Online Transaction Processing. OLTP uses normalized tables to fast file huge quantities of transactions at the same time as ensuring that these updates of statistics arise in as few places as feasible. Consequently OLTP database are designed for recording the every day operations and transactions of a enterprise. E.G. A timecard system that supports a big manufacturing surroundings should record correctly a large wide variety of updates during important periods like lunch hour, breaks, startup and close of labor.

Question 57. What Is Staging Area?

Answer :

The statistics staging area is a gadget that stands among the legacy systems and the analytics device, normally a records warehouse and now and again an ODS. The information staging vicinity is taken into consideration the "back room" portion of the data warehouse environment. The data staging region is wherein the extract, rework and cargo (ETL) takes region and is out of bounds for quit users. Some of the functions of the information staging place consist of:

Extracting records from multiple legacy systems.
Cleansing the statistics, commonly with a specialized device.
Integrating facts from more than one legacy structures into a unmarried facts warehouse.
Transforming legacy device keys into records warehouse keys, usually surrogate keys.
Transforming disparate codes for gender, marital reputation, and many others., into the records warehouse fashionable.
Transforming the heterogeneous legacy records systems to the information warehouse facts structures.
 Loading the diverse statistics warehouse tables through computerized jobs in a particular sequence via the bulk loader supplied with the information warehouse database or a third-party bulk loader.
Question 58. What Is Subject Area?

Answer :

Subject region method fundamental entities that make up the primary additives of the enterprise, e.G. Client, product, worker.

Question fifty nine. What Is A Checkpoint?

Answer :

Checkpoints are entries posted to a restart log desk at ordinary durations at some point of the data switch operation. If processing stops at the same time as a activity is jogging, you could restart the activity on the maximum latest checkpoint.

Question 60. What Is Slowly Changing Dimension?

Answer :

In a slowly changing dimension the characteristic for a record varies over time. There are three methods to solve this hassle.
• Type 1 – Replace an old record with a new report. No historical facts available.
• Type 2 – Keep the old document and insert a new record. Historical data available however resources in depth.
• Type 3 – In the existing report, maintain extra columns for the new values.

Question sixty one. Difference Between Multiload And Tpump?

Answer :

Tpump affords an alternative to MultiLoad for low volume batch preservation of huge databases below manipulate of a Teradata device. Tpump updates statistics in actual time, acquiring each bit of a records from the patron device with low processor utilization. It does this through a continuous feed of information into the statistics warehouse, in preference to the conventional batch updates. Continuous updates effects in extra accurate, timely facts. Tpump uses row hash locks than table degree locks. This permits you to run queries whilst Tpump is running.

Question 62. Different Phases Of Multiload?

Answer :

• Preliminary segment.
• DML section.
• Acquisition section.
• Application section.
• End section.

Question sixty three. What Is Dimensional Modeling?

Answer :

Dimensional Data Modeling accommodates of one or extra size tables and reality tables. Good examples of dimensions are area, product, time, promoting, employer and many others. Dimension tables keep records associated with that particular dimension and no statistics (measures) are stored in these tables.

Question 64. How Will You Solve The Problem That Occurs During Update?

Answer :

When there may be an errors throughout the replace procedure, an entry is posted in the blunders log table. Query the log table and attach the mistake and restart the task.

Question sixty five. Can You Connect Multiload From Ab Initio?

Answer :

Yes we are able to join.

Question sixty six. What Interface Is Used To Connect To Windows Based Applications?

Answer :

WinCLI interface.

Question 67. What Is Data Warehousing?

Answer :

A information warehouse is a subject orientated, included, time variation, non-unstable collection of records in support of management's choice-making manner.

Question sixty eight. What Is Data Modeling?

Answer :

A Data version is a conceptual illustration of facts structures (tables) required for a database and is very effective in expressing and speaking the enterprise necessities.

Question sixty nine. What Is Logical Data Model?

Answer :

A Logical information version is the model of a information version that represents the enterprise necessities (entire or part) of an business enterprise and is evolved earlier than the bodily facts version. A sound logical design have to streamline the bodily layout process by way of genuinely defining information systems and the relationships among them. A right information version is created through actually thinking about the present day and future commercial enterprise necessities. Logical statistics model consists of all required entities, attributes, key agencies, and relationships that represent commercial enterprise statistics and outline commercial enterprise rules.

Question 70. Steps To Create A Data Model?

Answer :

 Get business necessities.
 Create High Level Conceptual Data Model.
 Create Logical Data Model.
 Select goal DBMS where information-modeling tool creates the bodily schema.
 Create standard abbreviation report in step with business trendy.
Question 71. What Is The Maximum Number Of Dml Can Be Coded In A Multiload Script?

Answer :

Maximum 5 DML can be coded in a MultiLoad script.

Question 72. Does Sdlc Changes When You Use Teradata Instead Of Oracle?

Answer :

If the teradata is going to be simplest a information base means It received’t trade the System improvement life cycle (SDLC).
If you are going to use the teradata utilities then it'll trade the Architecture or SDLC.
If your schema goes to be in 3NF then there received’t be large in alternate.
 

Question 73. How Many Codd's Rules Are Satisfied By Teradata Database?

Answer :

There are 12 codd's guidelines carried out to the teradata database.

Question seventy four. How Teradata Makes Sure That There Are No Duplicate Rows Being Inserted When Its A Set Table?

Answer :

Teradata will redirect the brand new inserted row as consistent with its PI to the target AMP (on the idea of its row hash price), and if it locate equal row hash value in that AMP (hash synonyms) then it start evaluating the entire row, and discover if reproduction. If its a reproduction it silently skips it without throwing any mistakes.

Question 75. What Is The Difference Between Global Temporary Tables And Volatile Temporary Tables?

Answer :

Global Temporary tables (GTT) -
1. When they're created, its definition goes into Data Dictionary.
2. When materialized statistics goes in temp space.
3. Thats why, records is energetic upto the session ends, and definition will continue to be there upto its no longer dropped using Drop table assertion.
If dropped from a few other consultation then its should be Drop table all;
four. You could acquire stats on GTT.

Volatile Temporary tables (VTT) -
1. Table Definition is saved in System cache
2. Data is stored in spool area.
3. Thats why, facts and desk definition each are lively handiest upto consultation ends.
Four. No collect stats for VTT.

Question 76. What Is Filler Command In Teradata?

Answer :

at the same time as the usage of the mload of fastload in case you dont need to load a selected filed inside the datafile to the target then use this filler command to acquire this.

Question seventy seven. What Is The Command In Bteq To Check For Session Settings ?

Answer :

The BTEQ .SHOW CONTROL command shows BTEQ settings.

Question seventy eight. How Do You Set The Session Mode Parameters In Bteq?

Answer :

set consultation transaction ANSI /* that is to set ANSI mode */
set session transaction BTET /* this is to set Teradata transaction mode */
Question seventy nine. How Many Types Of Index Are Present In Teradata?

Answer :

There are 5 one-of-a-kind indices found in Teradata
1. Primary Index.
A.Unique number one index.
B. Non Unique primary index.
2. Secondary Index.
A. Unique Secondary index.
B. Non Unique Secondary index.
3. Partitioned Primary Index.
A. Case partition (ex. Age, revenue...).
B. Variety partition ( ex. Date).
4. Join index.
A. Single desk be part of index.
B. More than one desk join index.
C. Sparse Join index ( constraint carried out on be part of index in where clause).
5. Hash index.

Question 80. What Does Sleep Function Does In Fast Load?

Answer :

The SLEEP command specifies the amount mins to attend earlier than retrying to logon and establish all classes. Sleep command can be used with all load utilities now not best fastload. This scenario can occur if all the loader slots are used or if the quantity of requested classes is not to be had. The default price is 6 mins. If tenacity turned into set to two hours and sleep 10 minutes, Sleep command will try and logon for every 10 mins up to two hours period.

Question eighty one. What Are Types Of Partition Primary Index (ppi) In Teradata?

Answer :

1. Partition by way of CASE
CREATE TABLE ORDER_Table
(
ORD_number integer NOT NULL,
customer_number integer NOT NULL,
Generated with the aid of Foxit PDF Creator © Foxit Software
http://www.Foxitsoftware.Com For assessment best.
Order_date date ,
order_total integer
)
PRIMARY INDEX (customer_number)
PARTITION BY case1 (
order_total < ten thousand ,
order_total < 20000 ,
order_total < 30000,
NO CASE OR UNKNOWN ) ;
2. Partition by Range - example using date range
CREATE TABLE ORDER_Table
(
ORD_number integer NOT NULL,
customer_number integer NOT NULL,
order_date date ,
order_total integer
)
PRIMARY INDEX (customer_number)
PARTITION BY range1 (
Order_date BETWEEN date '2010-01-01' AND date '2010-12-01'
EACH interval '1' month ,
NO RANGE OR UNKNOWN);

Question 82. What Is A Sparse Index?

Answer :

Sparse Join Indexes are a type of Join Index which contains a WHERE clause that reduces the number of rows which would otherwise be included in the index. All types of join indexes, including single table, multitable, simple or aggregate can be sparse.

Question 83. Difference Between Stored Procedure And Macro?

Answer :

Stored Procedure:

It does not return rows to the user.
It has to use cursors to fetch multiple rows
It used inout/Out to send values to user
It contains comprehensive SPL
It is stored in DATABASE or USER PERM
A stored procedure also provides output/Input capabilities
Macros:

It returns set of rows to the user.
It is stored in DBC PERM space
A macro that allows only input values
Question 84. What Is Difference B/w User And Database In Teradata?

Answer :

- User is a database with password but database cannot have password.
- Both can contain Tables, views and macros.
- Both users and databases may or may not hold privileges.
- Only users can login, establish a session with Teradata database and they can submit requests.

Question 85. What Are Differences Between Teradata And Ansi Session Modes In Teradata?

Answer :

TERADATA MODE:

1. Comparison is not Case sensitive.
2. Create table are default to SET tables.
3. Each transaction is committed implicitly.
4. Supports all Teradata commands.
5. It follows BTET (Begin and End Transaction) Mode.

ANSI MODE:

1. Comparison is CASE sensitive.
2. Create table are default to MULTISET tables.
3. Each transaction has to be committed explicitly.
4. Does not support all Teradata commands.
5. It does not follow BTET Mode.

Question 86. What Are The Scenarios In Which Full Table Scans Occurs?

Answer :

1. The where clause in SELECT statement does not use either primary index or secondary index.
2. SQL Statement which uses a partial value (like or not like), in the WHERE statement.
3. SQL Statement which does not contain where clause.
4. SQL statement using range in where clause. Ex. (col1 > 40 or col1 < =ten thousand).

Question 87. How To Identify Ppi Columns?

Answer :

Select databasename , tablename , columnposition ,columnname from dbc.Indices 
wherein indextype ='Q'
order via 1 ,2,three ;
Question 88. Can You Load Multiple Data Files For Same Target Table Using Fastload?

Answer :

Yes, we can Load a table the usage of a couple of datafiles in Fastload. Before giving "quit loading" announcement person can outline record direction and use insert square for a couple of source files and supply "quit loading" assertion on the give up.

Question 89. Why Does Varchar Occupy 2 Extra Bytes?

Answer :

The  bytes are for the number of bytes for the binary duration of the sector. It shops the precise no of characters saved in varchar.

.

Question ninety. What Do High Confidence, Low Confidence And No Confidence Mean In Explain Plan?

Answer :

Explain gives the execution strategy way what are the distinctive steps that the query will undergo.
HIGH CONFIDENCE: Statistics are gathered.
LOW CONFIDENCE: Statistics are not collected. But the wherein situation is having the circumstance on indexed column. Then estimations can be based on sampling.
NO CONFIDENCE: Statistics aren't collected and the condition is on non listed column.

Question ninety one. How Can You Track Login Parameters Of Users In Teradata?

Answer :

SELECT LOGDATE, LOGTIME, USERNAME, EVENT FROM DBC.LOGONOFF;

Question ninety two. What Are Tpump Utility Limitations?

Answer :

Following are the restrictions of Teradata TPUMP Utility:

Use of SELECT statement is not allowed.
 Concatenation of Data Files isn't always supported.
 Exponential & Aggregate Operators aren't allowed.
 Arithmatic features aren't supported.
 

Question ninety three. What Are The Multiload Utility Limitations?

Answer :

MultiLoad is a very powerful software; it has following limitations:

MultiLoad Utility doesn’t help SELECT declaration.
 Concatenation of multiple input facts documents isn't allowed.
 MultiLoad doesn’t help Arithmatic Functions i.E. ABS, LOG and so on. In Mload Script.
 MultiLoad doesn’t guide Exponentiation and Aggregator Operators i.E. AVG, SUM and many others. In Mload Script.
 

Question 94. What Are The Functions Of A Teradata Dba?

Answer :

Following are the one of a kind capabilities which a DBA can carry out:
1. User Management – Creation and handling Users, Databases, Roles, Profiles and Accounts.
2. Space Allocation – Assigning Permanent Space, Spool Space and Temporary Space.
3. Access of Database Objects – Granting and Revoking Access Rights on distinctive database items.
4. Security Control – Handling logon and logoff rules for Users.
5. System Maintenance – Specification of machine defaults, restart etc.
6. System Performance – Use of Performance Monitor(PMON), Priority Scheduler and Job Scheduling.
7. Resource Monitoring – Database Query Log(DBQL) and Access Logging.
Eight. Data Archives, Restores and Recovery – ARC Utility and Permanent Journals.

Question ninety five. What Are The 5 Phases In A Multiload Utility?

Answer :

Preliminary Phase – Basic Setup
 DML Phase – Get DML steps down on AMPs
 Acquisition Phase – Send the input information to the AMPs and type it
 Application Phase – Apply the input facts to the right Target Tables
 End Phase – Basic Cleanup
 

Question 96. How To View Every Column And The Columns Contained In Indexes In Teradata?

Answer :

Following question describes every column inside the Teradata RDBMS

SELECT * FROM DBC.TVFields;
Following question describes columns contained in indexes inside the Teradata RDBMS

SELECT * FROM DBC.Indexes;
Question ninety seven. How To Select First N Records In Teradata?

Answer :

To select N records in Teradata you may use RANK function. Query syntax would be as follows

SELECT BOOK_NAME, BOOK_COUNT, RANK(BOOK_COUNT)A FROM LIBRARY QUALIFY A <= 10;
Question 98. How To Eliminate Product Joins In A Teradata Sql Query?

Answer :

1. Ensure statistics are collected on join columns and this is especially important if the columns you are joining on are not unique.
2. Make sure you are referencing the correct alias.
3. Also, if you have an alias, you must always reference it instead of a fully qualified tablename.
4. Sometimes product joins happen for a good reason. Joining a small table (100 rows) to a large table (1 million rows) a product join does make sense.

Question 99. How Does Hashing Happens In Teradata?

Answer :

Hashing is the mechanism through which data is distributed and retrieved to/from AMPs.
 Primary Index (PI) value of a row is the input to the Hashing Algorithm.
 Row Hash (32-bit number) value is the output from this Algorithm.
 Table Id + Row Hash is used to locate Cylinder and Data block.
 Same Primary Index value and data type will always produce same hash value.
 Rows with the same hash value will go to the same AMP. So data distribution depends directly on the Row Hash uniqueness; be careful while Choosing Indexes in Teradata.
 

Question 100. How Do You Transfer Large Amount Of Data In Teradata?

Answer :

Transferring of large amount of data can be done using various Application Teradata Utilities which resides on the host computer ( Mainframe or Workstation) i.E. BTEQ, FastLaod, MultiLoad, Tpump and FastExport.

BTEQ (Basic Teradata Query) supports all 4 DMLs: SELECT, INSERT, UPDATE and DELETE. BTEQ also support IMPORT/EXPORT protocols.
 Fastload, MultiLoad and Tpump transfer the data from Host to Teradata.
 FastExport is used to export data from Teradata to the Host.
 

Question 101. How Can You Find The Teradata Release And Version Information From Data Dictionary Table?

Answer :

To find Release and Version information you can query this Data Dictionary table DBC.DBCINFO

SELECT * FROM DBC.DBCINFO;
Question 102. Can You Recover The Password Of A User In Teradata?

Answer :

No, you can’t recover the password of a user in Teradata. Passwords are stored in this data dictionary table (DBC.DBASE) using a one-way encryption method. You can view the encrypted passwords using the following query.

SELECT * FROM DBC.DBASE;
Question 103. What Are The Types Of Tables In Teradata ?

Answer :

1. Set table.
2. Multiset table.
3. Derived table.
4. Global temporary table(temporary table).
5. Volatile table.

Question 104. In A Table Can We Use Primary Key In One Column And In Another Column Both Unique And Not Null Constrains.If Yes How?

Answer :

Yes, you can have a column for Primary key and have another column which will have no duplicates or null. E.G. A Salary Table will have employee ID as primary key. The table also contains TAX-ID which can not be null or duplicate.

Question 105. Can I Use "drop" Statement In The Utility "fload"?

Answer :

YES,But you have to declare it out of the FLOAD Block it means it should not come between .Begin loading,.End loading FLOAD also supports DELETE,CREATE,DROP statements which we have to declare out of FLOAD blocking the FLOAD Block we can give only INSERT.

Question 106. Can We Load A Multi Set Table Using Mload?

Answer :

We can Load SET, MULTISET tables using Mload, But here when loading into MULTISET table using MLOAD duplicate rows will not be rejected, we have to take care of them before loading.But in case of Fload when we are loading into MULTISET duplicate rows are automatically rejected, FLOAD will not load duplicate rows weather table is SET or MULTISET.

Question 107. Teradata Performance Tuning And Optimization?

Answer :

1. Collecting statistics.
2. Explain Statements.
3. Avoid Product Joins when possible.
4. Select appropriate primary index to avoid skewness in storage.
5. Avoid Redistribution when possible.
6. Use sub-selects instead of big "IN" lists.
7. Use derived tables.
8. Use GROUP BY instead of DISTINCT ( GROUP BY sorts the data locally on the VPROC. DISTINCT sorts the data after it is redistributed).
9. Use Compression on large tables.

Question 108. What Is An Optimization And Performance Tuning And How Does It Really Work In Practical Projects?

Answer :

Performance tuning and optimization of a query involves collecting statistics on join columns, avoiding cross product join, selection of appropriate primary index (to avoid skewness in storage) and using secondary index.

Question 109. What Are The Enhanced Features In Teradata V2r5 And V2r6?

Answer :

V2R6 included the feature of replica in it. In which copy of data base are available on another system which means V2R6 provide the additional data protection as comparison to V2R5 while if data from one system has been vanishes.

Question 110. What Is A Common Data Source For The Central Enterprise Data Warehouse?

Answer :

ODS=>Operational Data Source.

Question 111. What Are The Basic Rules That Define How Pi Is Defined In Teradata?

Answer :

The following guidelines govern how Primary Indexes in a Teradata Database have to be described as well as how they characteristic:
One Primary Index in step with table.
A Primary Index cost can be precise or non-particular.
The Primary Index cost can be NULL.
The Primary Index value may be modified.
The Primary Index of a populated desk can't be modified.
A Primary Index has a limit of sixty four columns.