1)Which is always one amp operation upi(ANS) nupi usi nusi 2)which _ and _ combination will minimizes the AMP retreival PPI is used to improve performance for large tables when you submit queries that specify a range constraint. A Secondary Index (SI) is an alternate data access path. It allows you to access the data without having to do a full-table scan. An USI is always a Two-AMP operation so it is almost as fast as a Primary Index, but a NUSI is an All-AMP operation, but not a Full Table Scan. There can be up to 32 Secondary Indexes on a table 3)Pi can be null cannot be null should not unique none Primary Index Rules Rule 1: One Primary Index per table. Rule 2: A Primary Index value can be unique or non-unique. Rule 3: The Primary Index value can be NULL. Rule 4: The Primary Index value can be modified. Rule 5: The Primary Index of a populated table cannot be modified. Rule 6: A Primary Index has a limit of 64 columns. Secondary Index Rules Rule 1: Secondary Indexes are optional. Rule 2: Secondary Index values can be unique or non-unique. Rule 3: Secondary Index values can be NULL. Rule 4: Secondary Index values can be modified. Rule 5: Secondary Indexes can be changed. Rule 6: A Secondary Index has a limit of 64 columns. 4)Which is not type of journal ? Pemamnent recovery transcation (ANs) transient Recovery Journal The Teradata Database uses Recovery Journals to automatically maintain data integrity in the case of: *An interrupted transaction (Transient Journal) *An AMP failure (Down-AMP Recovery Journal) Transient Journal A Transient Journal maintains data integrity when in-flight transactions are interrupted (due to aborted transactions, system restarts, and so on). Data is returned to its original state after transaction failure. Down-AMP Recovery Journal The Down-AMP Recovery Journal allows continued system operation while an AMP is down (for example, when two disk drives fail in a rank or mirrored pair). A Down-AMP Recovery Journal is used with Fallback-protected tables to maintain a record of write transactions (updates, creates, inserts, deletes, etc.) on the failed AMP while it is unavailable. Permanent Journals Permanent Journals are an optional feature used to provide an additional level of data protection. You specify the use of Permanent Journals at the table level. It provides full-table recovery to a specific point in time. It can also reduce the need for costly and time-consuming full-table backups. 5)Nupi allow null value (ANS) does not allow null value performes uniforms row distribution allows unique value only Unique Primary Index(UPI) A Unique Primary Index (UPI) is unique and cannot have any duplicates. An UPI enforces UNIQUENESS for a column. A Unique Primary Index (UPI) will always spread the rows of the table evenly amongst the AMPs. UPI access is always a one-AMP operation. Non-Unique Primary Index (NUPI) A Non-Unique Primary Index (NUPI) means that the values for the selected column can be non-unique. Duplicate values can exist. A Non-Unique Primary Index will almost never spread the table rows evenly. An All-AMP operation will take longer if the data is unevenly distributed. You might pick a NUPI over an UPI because the NUPI column may be more effective for query access and joins. 6)Grant ,Revoke , Dml dcl (ANS) DDl none 7)Insert , delete ,select Dml (ANS) dcl DDl none 8)Select max(sal) from emp sal < (sel max(sal) from emp) a)highest salary b)least salary c)second highest (ANS) d)between highest and lowest 9)same users but data got doubled and # amps are doubled what happens to the performance double same (ANS) reduce by 50% reduce by 25% 10)Show table emp will give a)Data definition of the query (ANS) b)show the hidden data c) 11)In teradata date format will be int string specified type date format (ANS) 12)Teradata is called Born parallel (ANS) scalable network ease 13)how many bynets are present in teradata 1 2 (ANS) 3 4 14) AMP is a Intel proccess virtual processor (ANS) DWh processor 15) Raid will always overcome (ANS) Data protection at disk level Redundant Array of Inexpensive Disks (RAID) is a storage technology that provides data protection at the disk drive level. 16)Fastload will single table (Ans) multiple table text file csv file Fastload loads huge amount of data from flat file into EMPTY tables. target tables should be empty. Fast load is always load single table as target table . Multi load is load one or more target tables . 17) Which of the following is correct ,temparory space will (ANS)to hold temparory records unused perm space 18)What is the answer the for the given SQL Select * from table where col_a not between 8 and 15 a)All excluding 9 and 14 b)All excluding only 9 and 14 c)all rows excluding 9 through 14 d)all rows excluding 8 through 15 (ANS) ---------------------------------------------------------------------------------------------------------------------------- 1.what statement provides exclusive lock in teradata? A. INSERT B.SELECT C.Create (ANS) D. UPDATE Levels of Locking Locks may be applied at three levels: Database Locks: Apply to all tables and views in the database. Table Locks: Apply to all rows in the table or view. Row Hash Locks: Apply to a group of one or more rows in a table. Types of Locks The four types of locks are described below. Exclusive Exclusive locks are applied to databases or tables, never to rows. They are the most restrictive type of lock. With an exclusive lock, no other user can access the database or table. Exclusive locks are used when a Data Definition Language (DDL) command is executed (i.e., CREATE TABLE). An exclusive lock on a database or table prevents other users from obtaining any lock on the locked object. Write (Update) Write locks enable users to modify data while maintaining data consistency. While the data has a write lock on it, other users can only obtain an access lock. During this time, all other locks are held in a queue until the write lock is released. Read (Select) Read locks are used to ensure consistency during read operations. Several users may hold concurrent read locks on the same data, during which time no data modification is permitted. Read locks prevent other users from obtaining the following locks on the locked data: Exclusive locks Write locks Access (view the updating record) Access locks can be specified by users unconcerned about data consistency. The use of an access lock allows for reading data while modifications are in process. Access locks are designed for decision support on tables that are updated only by small, single-row changes. Access locks are sometimes called "stale read" locks, because you may get "stale data" that has not been updated. Access locks prevent other users from obtaining the following locks on the locked data: Exclusive locks 2. Non-Unique Primary Index will contain:- A. NULL Values (ANS) B. Duplicates Records C. Duplicate Rows D. Unique values 3. In Teradata ,Procedure Stored in? 4.Teradata has been called a ____________ architecture. A.Shared Nothing (ANS) B.Anything Goes C.Shared Everything D.Dual Redundant 5. Which statement is true about temporary space? A.Temporary space is assigned at the table level B.Temporary space is substracted from SysAdmin C.Temporary space is spool space currently not used D.Temporary space is permanen tspace currently not used(Answer) 6. What is the function of AMP? A. It has access to a single vdik B. It does output conversation and formatting C. It does the physical work associated with generating an answer set D. All of the above (ANS) AMP AMPs (Access Module Processors) are virtual processors (vprocs) that receive steps from PEs (Parsing Engines) and perform database functions to retrieve or update data. Each AMP is associated with one virtual disk (vdisk), where the data is stored. An AMP manages only its own vdisk, not the vdisk of any other AMP. Vdisk (Virtual Disk) A vdisk is the logical disk space that is managed by an AMP. Depending on the configuration, a vdisk may not be contained on the node; however, it is managed by an AMP, which is always a part of the node. The vdisk is made up of 1 to 64 pdisks (user slices in UNIX or partitions in Windows NT, whose size and configuration vary based on RAID level). The pdisks logically combine to comprise the AMP's vdisk. Although an AMP can manage up to 64 pdisks, it controls only one vdisk. An AMP manages only its own vdisk, not the vdisk of any other AMP. 6. BYNET Is _________? A. Software B. Hardware (ANS) C. OS D. Processor 7. Parsing engine does?? (ANS):(Session control, Parsing, Optimizer :auhtorization :dispatcher ) 8. GRANT Is what type of SQL language? A. DCL (ANS) B. DDL C. DML D. None of the Above 9. What are the following object required for Teradata Database-Prem space?. A. Tables & Stored Procedure (Ans) B. Tables & Views C. Macros & Triggers D. Views & Stored Proce 10.Global Temporary Tables are stored in the Teradata are A. Perm Space B. Spool Space C. Temp Space (ans) D. None of the above 11.Total Number of nodes suppport by BYNET are A.510 SMP Nodes B.512 SMP Nodes (Ans) C.513 SMP Nodes D.514 SMP Nodes 12. Which of the below statement gives same output as outer join? A.INTERSECT B.MINUS C.UNION (ANS) D. UNION ALL 13.Which of the following is true concerning a procedure? A.You do not create them with SQL. B.They do not need to have a unique name. C.They include procedural and SQL statements. (Ans) D.They are the same thing as a function. 14.A UNION query is which of the following? A.Combines the output from no more than two queries and must include the same number of columns. B.Combines the output from no more than two queries and does not include the same number of columns. C.Combines the output from multiple queries and must include the same number of columns. (Ans) D.Combines the output from multiple queries and does not include the same number of columns. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1. --------- applies a exclusive loc a) Select b) Rename c) Give d) Revoke Question is Ambigious. Answer is Create table(DDL) 2. ----------Supports multiple sessions a) Fast load b) Data load c) Sams load d) Teralink 3. UPI _____________ a) Doesn't allow duplicates b) Allow duplicates c) Retrieve NULL rows d) accept NULL values Question is Ambigious. Answer is A and D. 4. Amp funtion_____________ Below are AMP functions: perform database functions to retrieve or update data AMPs do the physical work associated with generating an answer set which includes sorting, aggregating, formatting, and converting. AMPs is also able to redistribute a copy of the data to other AMPs. 5. Bynet Feature _______ All communication between PEs and AMPs is done via the BYNET 6. DDl commands____ Create,Alter, Drop 7. Drop macro ________ a) Throws error b) Delete Macro successfully c) Return NULL values d) Delete and Recreate Ans: B 8. Each always connect to__________ Single Vdisk ' 9. Each AMP has its own dedicated ________________ Vdisk(Virtual disk) 10. What is result of sel AVG(Column) from table, if table is Empty? a) Error b) zero rows returned c) NULL d) Zero Ans: NULL 11. DML commands_______________ Select,update, insert, delete 12. MPP_________ Massively parallel processing 13. Temp space is _____________ Unused Perm space 14. Role of Primary indexes _____________________ Three major role of Primary index *data distribution *Faster way to retrieve data *incredibly important for joins 15. An user has no permanent space assigned. which objects could not found in space? a) Trigger b) View c) Table d) Macro Ans: Table A database or user with no PERM space can still own views, macros, and triggers but cannot have objects that require space such as tables, UDFs, stored procedures, HIs, JIs, or journals. 16. Select distinct Salary, Employee name from EMP table where Salary >5000 Ans: Displays only distinct combination of Salary , Employee satify condition Salary >5000 17. select EMP id, EMp name from EMP table Inner join DEPT table a) throws error b) Zero c) Null d) All data displayed Ans: A 18. SMP means________________ Symmetric multiprocessing 19. Lock may be applied at _________________levels a) 2 b) 3 c) 4 d) 5 Ans: 3 Levels of Locking Locks may be applied at three levels: Database Locks: Apply to all tables and views in the database. Table Locks: Apply to all rows in the table or view. Row Hash Locks: Apply to a group of one or more rows in a table. 20. PDE has ability ______________ Parallel Database Extensions (PDE) The Parallel Database Extensions (PDE) software layer was added to the operating system to support the parallel software environment. The PDE controls the virtual processor (vproc) resources. 21. Explain plan will not give_________ a) Execution time b) lock profiles c) CPU time d) rows count Guess Ans: CPU 22. Sorting of rows happens in ______________ Ans: AMP AMPs do the physical work associated with generating an answer set which includes sorting, aggregating, formatting, and converting. 23. What is linear scalabilty__________ 24. PE V/PROC manages_____________ 25. Which is not type of journal ? Pemamnent recovery transcation (ANs) transient -------------------------------------------------------------------------- 1.What happens spool space is zero? Query will throw an error or session will be aborted . 2.Table is empty, what is the ave (col1)? AnS ( Null) 3.Where GT will store? Temporaray Space 4.Foreign key condition ? 5.Macro and SP comparisson 6.PE and AMP comunication channel ? Ans :(Bynet ) 6.Tera support OS ? Ans :(Linux ) Select * from sample .1 Random 10 % Fisrt 10% --------------------------------------------------------------------------------------------------------- 1) Which is not a type of Journal ? a) Function b) Procedure Answer : Procedure (Beacuse Journal is a function used in the DDL ) 2) Input data conversion happens at ? ANswer : Parsing engine 3) Near realtime updates from transactinal system into warehouse is supported by ? 4) Which Feauture allows Teradata to process large amount of data quickly ? Answer : Performance and Efficiency 5) PDE provides ability to ? Answer : Run in paralele environment The Parallel Database Extensions (PDE) software layer was added to the operating system to support the parallel software environment. The PDE controls the virtual processor (vproc) resources. 6) Primary Key and Primary index are composed of the same column ? Answer : Not always 7) Teradata is a a) relational database b) data base Answer : relational database 8) The Components included in the teradata database system to support data communcation management are? The components included in the Teradata database system to support datacommunication management are: Call level interface (CLI) WinCLI and open database connectivity (ODBC) Teradata Director Program (TDP) Micro TDP The Teradata client software components include: Basic Teradata Query C CLI ODBC TDP Archive Query man FastLoad MultiLoad FastExport Open Teradata Backup Tpump Teradata Manager WinDDI 9) Which is the common data source for the central enterprise data warehouse ? Answer : ODS (Operational Data sources) or Datamarts 10) All Communication between nodes and Vprocs is through a) bynet b) internet Answer : Bynet 11) Amps are What is AMP in Teradata ? The AMP is a vproc in the Teradata Database's shared-nothing architecture that is responsible for managing a portion of the database. Each AMP will manage some portion of each table on the system. AMPs do the physical work associated with generating an answer set which includes sorting, aggregating, formatting, and converting. The AMPs retrieve and perform all database management functions on the required rows from a table. An AMP accesses data from its single associated vdisk. AMPs is also able to redistribute a copy of the data to other AMPs. 12) Amp's feautures Answer: Manages the portion Physical Disk Storage 13) Create,alter,drop fall under a) DCL b) DML c) DDL d) None of the above Answer : DDL 14) Each Amp is always connected to (Vprocs ) Answer : True 15) Each Amp vproc has its own dedicated _________________ Ans : Vdisks( Virtual disk) no disks are shared between AMPs (disks are dedicated to a single AMP) Each AMP then contained its own microprocessor, disk drive, file system, database software (Database Manager), Teradata Operating System (TOS), and YNET interface. In that sense, each AMP was a node. 16) MPP Means Answer : Massively Parallel processing 17) Temp Space is a) Function b) procedure c)Unused perm Space (Answer : Unused perm Space ) Permanent Space is where objects (i.e., databases, users, tables) are created and stored. Permanent Space is released when data is deleted or when objects are dropped. Spool Space is PERM space on the system that has not been allocated. The Primary reason for SPOOL space is to be available to store intermediate results or queries that are being processed in Teradata. Spool Space is released when the query is over or when the query no longer needs it. Volatile Tables consume Spool Space and Permanent Tables consumes Perm Space. Apart from this, there is TEMP SPACE which is all unused space of PERM SPACE and it is used by Global Temporary Tables. 18) Teradata is designed for ANS : parallel processing 19) The role of primary indexes are ( Answer : Determines the data Distribution ) Three major role of Primary index *data distribution *Faster way to retrieve data *incredibly important for joins 20) For Same number of users and double the data volume, when the # of Amps is double what happens to the performance Answer : Performance will increases Ambiquity question 21) _____ is always a one AMP Operation unified non unified both a and b Answer : Unified UPI access is always a one-AMP operation. 22) _____ Parallelism is available in Teradata Answer : Query 23) _____ Require no perm space ANS: views, macros, and triggers A database or user with no PERM space can still own views, macros, and triggers but cannot have objects that require space such as tables, UDFs, stored procedures, HIs, JIs, or journals. 24) A Collection of related data fields is called a a) data b) record c) file Answer : Record In data organization in information system a group of related records is called a File. 25) A RAID Configuration for system availability has what advantage RAID is a technology that is used to increase the performance and/or reliability of data storage. The abbreviation stands for Redundant Array of Inexpensive Disks. A RAID system consists of two or more disks working in parallel. RAID 5 is the most common secure RAID level. ------------------------------------------------------------------------------------------------------------- 1)In which phase of the active dataware house evovle data what will happen Even triggering contionus update analytical modeling 2.How will you run bteq ? a.run file = bteqfile.btq b.bteq < bteqfile c.both a and b (Ans) d.none of the above 3.Select * from table where zipcode mod 1000 = 0; It will select all the rows where the zipcode ends with 4 zeros 4.Which operators will behave in the same way ? a)Qaulify and having (Ans ) b)gorup by and having c)having and distinct d)Distinct and having 5.Revoke will fall under a)Dml b)DCL(ANS) C)DDl D)TCL 6.If in a systme we are having 100 GB as perm space and we allocated 50 gb as spool space and 30 gb as Perm space what will be your Spool space now ? a)70 GB b)50 GB C)30 GB D)20 GB 7.Which will show the create table statement ? a)Show b)Help C)both a and b D)None of the above 8.Which is true about bynet ? a)It will check the syntax errors b)It does DB functions C)COmmunication to SMP D)I/o Optmization plan to AMP . 9.Data Distribution a)Using Primay Key B)Using FOriegn key C)Using USI 10)Which is the must statement for Forign key ? a)Foriegn key must be a primary Key b)Foriegn key cannot contain null values and missing values --------------------------------------------------------------------------------------------------------------------- The syntax for the RANK function is: RANK( ) OVER (ORDER BY sales DESC); Example Show the ranking of product sales for store 1001. SELECT storeid ,prodid ,sales ,RANK( ) OVER (ORDER BY sales DESC) AS "Rank" FROM salestbl WHERE storeid = 1001; Ranking With PARTITION BY SELECT storeid ,prodid ,sales ,RANK( ) OVER (PARTITION BY storeid ORDER BY sales DESC) AS "Ranking" FROM salestbl QUALIFY Ranking <= 3; Macro: A MACRO is a Teradata extension to ANSI SQL that contains prewritten SQL statements. The actual text of the macro is stored in a global repository called the Data Dictionary (DD) Below is complete list of commands to manipulate macros. CREATE MACRO macroname AS ( . . . ); Define a macro and store it in the DD. EXEC macroname; Execute statements within a macro. SHOW MACRO macroname; Display a macro. REPLACE MACRO macroname AS (. . . ); Apply changes to a macro or create a new one. DROP MACRO macroname; Remove a macro definition from the DD. EXPLAIN EXEC macroname; Display EXPLAIN text for the macro's execution. Join: A join is a technique for accessing data from more than one table in a single answer set. Types of Joins Inner Rows- which match based on join criteria Outer Inner -join rows and remaining unmatched rows Cross- Each row of one table matched with each row of another Self - Rows matching other rows within the same table Cartesian Products: A completely unconstrained cross join is called a Cartesian product. Each row of one table is joined to each row of another table. A Cartesian product results from a CROSS JOIN without a WHERE clause. Cartesian products can also result from an INNER JOIN with improper aliasing or improper join conditions. Temporary tables Teradata has implemented three types of temporary tables. Derived tables Volatile temporary tables Global temporary tables Link: http://www.teradatawiki.net/ http://softdocs.blogspot.com/2011/03/teradata-multiple-choice-questions.html