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