• Teradata 14 SQL

  • Teradata 14 SQL Exam (TE0-142)

    Exam Details

    Purpose of the Exam

    This exam will certify that the successful candidate has core knowledge and skills about Teradata Structured Query Language (SQL) using both ANSI standard conventions and Teradata extensions to the language.

    Credential Earned

    A separate certification is not awarded for passing the SQL exam. The SQL exam is a requirement of the Certified Technical Specialist Certification.

    Teradata 12 SQL Exam Completion Digital Badge

    Exam Duration

    1 hour 45 minutes*

    *The exam duration shown is the amount of time allowed for answering exam questions. The Test Delivery Provider may add time to your total appointment to provide the opportunity to get seated, read the Non-Disclosure Agreement (NDA) and complete the tutorial (if applicable) before you start the exam.  

    Language

    English

    Cost

    The cost of Teradata exams vary by exam, location and currency. Please visit www.pearsonvue.com/teradata to find the price of an exam where you are located.

    Teradata Employees visit the TCPP Connections Group to obtain an employee voucher. (You must be behind the Teradata firewall to access this page)

    Prerequisites, Eligibility Requirements and Recommended Experience

    Prerequisite Exam

    Exam TE0-142 does not require an eligibility voucher.

    Minimum of 12 months hands-on experience with SQL and Teradata is recommended.

    How to Register

    Online - visit www.pearsonvue.com/teradata  

    For a list of regional phone numbers visit www.pearsonvue.com/teradata/contact/

    Retake Policy

    A failed exam may be retaken after a 21 calendar day waiting period. The 21 calendar day waiting period begins from the date of the failed exam attempt.

    You will be required to schedule a new appointment and pay for the exam.

    You may attempt an exam a maximum of three (3) times during a six (6) month period.

    Re-taking the same Teradata 14 exam after passing that exam will not be allowed.

    Teradata 14 Certification Exams Objectives

    The high level objectives represent the general content areas. The more detailed information below the objective indicates representative topic areas. All Teradata 14 functions and features that fall within the stated objective areas are eligible topics on an exam.

    TE0-142 Teradata 14 SQL

    • Basic Teradata Extensions
      • Describe usage of HELP, SHOW, and EXPLAIN commands and their expected outputs.
      • List the differences between ANSI and Teradata modes in the areas of transaction protocol, defaults, conversions, and table creation.
      • Describe usage of built-in Teradata key words (e.g., CURRENT_DATE).
    • Data Definition Language (DDL) (Non-Temporal)
      • Given a CREATE TABLE statement without a primary index declared, identify the column that Teradata will select as the primary index.
      • Describe the attributes and constraints for a column in a table.
      • Given a scenario, identify considerations using a CREATE TABLE AS definition.
      • Given a scenario, identify the type of table that should be used (global temporary, volatile, and permanent).
    • Data Manipulation Language (DML) (Non-temporal)
      • Identify a correctly written INSERT statement.
      • Identify a correctly written UPDATE statement.
      • Identify a correctly written DELETE statement.
      • Identify a correctly written SELECT statement.
      • Identify a correctly written MERGE INTO statement.
    • Views and Macros (Non-temporal)
      • Identify uses of simple and parameterized macros.
      • Identify the benefits of using views and macros.
      • Identify the restrictions of creating views and macros.
      • Describe the impact of using an access lock in a view or macro.
    • Logical and Conditional Expressions
      • Identify the correct use of comparison operators in logical expressions.
      • Evaluate expressions involving NULLs.
      • Identify the correct use of multiple logical expressions in a conditional expression using AND, OR, NOT.
      • Identify the correct use of the IN, NOT IN logical predicate.
    • Data Conversions and Computations
      • Given a scenario, identify the ramifications of implicit conversions from one data type to another.
      • Given a scenario, identify the ramifications of explicit conversions from one data type to another.
      • Given a scenario using CAST, identify the proper result.
      • Identify the characteristics of ANSI CAST and Teradata extensions for data conversion.
    • CASE Expressions
      • Given a scenario, identify the result of the CASE expression.
      • Given a scenario, identify the use of different forms (searched and valued) of the CASE expression.
      • Given a scenario, identify the use of specialized functions (COALESCE, NULLIF) of the CASE expression.
    • Subqueries and Correlated Subqueries
      • Given a scenario, identify the SQL statement for a correlated subquery to qualify a subset of data.
      • Given a scenario, identify the SQL statement for a noncorrelated subquery to qualify a subset of data.
      • Given a scenario, identify the SQL statement for a correlated scalar subquery to qualify a subset of data.
      • Given a scenario, identify the SQL statement for a noncorrelated scalar subquery to qualify a subset of data.
      • Identify the characteristics of a scalar subquery.
    • Joins (Non-temporal)
      • Given a scenario, determine the type of join to code to get the desired result set.
      • Identify proper and improper use of aliasing in table joins.
      • Given a scenario, identify the evaluation order of various join types.
      • Given a scenario, identify the use of a derived table using single or multiple WITH form.
      • Given a scenario, identify the use of a derived table using the FROM clause form.
      • Given a scenario, identify the use of a derived table using a recursive WITH form.
      • Identify the characteristics of outer joins.
    • Attribute and String Functions
      • Identify the attribute functions and identify how they work within SQL.
      • Identify the correct use of string manipulation functions and attributes.
      • Identify formatting options for internationalization of various data types.
      • Identify the correct use of new embedded services functions (e.g., TO_CHAR, TO_NUMBER, NVL, etc.)
    • Set Operations
      • Given a Venn diagram, identify the set operator.
      • Given a request and a set operator, identify the outcome.
    • Analytical Functions
      • Describe the functionality of Ordered Analytic Functions.
      • Describe the functionality of RANK in the Window Aggregate function.
      • Describe the functionality of RESET WHEN in the Window Aggregate function.
      • Describe the functionality of QUALIFY in the Window Aggregate function.
    • Time/Date/Timestamp/Intervals (ANSI vs. Teradata) (Non-temporal)
      • Identify the correct use of Time/Date in expressions involving computations, conversions, literals, and extractions.
      • Identify the correct use of Timestamp in expressions involving computations, conversions, literals, and extractions.
      • Identify the correct use of Intervals in expressions involving computations, conversions, literals, date intervals, and extractions.
      • Given a date, identify valid date calculations in a Teradata database.
      • Describe the functionality of the EXPAND ON keyword.
    • Aggregations
      • Identify the use of GROUP BY, HAVING, DISTINCT, and WHERE.
      • Identify the impact of NULLs on the aggregate functions.
      • Given a scenario, identify which extended GROUP BY operator (e.g., ROLLUP) to use.
      • Describe the characteristics of properly using GROUP BY, WHERE and HAVING.
    • SQL Optimization Concepts
      • Identify characteristics of a query that indicates optimization opportunities.
      • Given a scenario, identify the result set returned by SAMPLE (e.g., replacement vs. no replacement).
      • Identify the processing differences between TOP N and SAMPLE.
    • Temporal Concepts
      • Describe the use of temporal data types and qualifiers.
      • Describe the effect of INSERTS using Current, Sequenced or Non-sequenced qualifiers.
      • Describe the effect of UPDATES using Current, Sequenced or Non-sequenced qualifiers.
      • Describe the effect of DELETES using Current, Sequenced or Non-sequenced qualifiers.
      • Describe the effect of the AS OF qualifier.
      • Describe the characteristics and use of VALIDTIME and TRANSACTIONTIME.

    Click for Printable Exam Objectives PDF

    Recommended Courses

    The SQL exam also covers Temporal topics. You may wish to review additional courses that include this information.

    The Teradata Certified Professional Program partners with the Teradata Education Network to provide Teradata courseware and curriculum that specifically relates to each certification level. You can view course descriptions and schedules, and enroll online. Several courses are offered as web-based training (WBT) courseware for your convenience.

    Teradata Employees only: visit the Teradata internal TCPP Connections site for more information on recommended Teradata training. (You must be logged in to the Teradata network).

    Resources & Preparation Materials

    Teradata Certification App (TCaPP)

    The TCaPP App is free and provides instant access to: program information, tutorials, Authorized Study Guides, practice question packs, exam registration and results from the convenience of your smartphone or tablet.

    Download the App for Android or iOS.

    Authorized Study Guide

    Authorized Teradata Certification Study Guides are available to support your Teradata Certification goals. Each guide provides focused content, simplified examples and explanations around key objective areas, and help in determining topics for further study prior to sitting for your Teradata Certification exams. The guides are intended as a study companion for individuals who have completed recommended Teradata training courses and who have the suggested experience using Teradata Database technology.

    Teradata 14 Certification Study Guide

    Note: The Teradata Certification Study Guides and Apps are the only authorized study guide publications in the market. The Teradata Certified Professional Program partners with Cerulium Corporation to author the Teradata Certification Study Guides and develop mobile applications.

    Sample Questions

    Access Teradata 14 Sample Questions  

    Sample questions are designed to give you an example of the format and types of questions on the actual Teradata Certification exams. The practice questions are not intended to duplicate items on the actual exams. Questions are rotated and will be slightly different each time.  

    The sample questions are offered on-line free of charge and are not “scored” or tracked. Practice at your own pace and as often as you want.  

    More sample questions are available for purchase through the Teradata Certification App (TCaPP) and end of chapter questions are included with the Authorized Study Guides.

    Documentation and Downloads

    Documentation: Search and access Teradata installation, programming, operations and service guides, and reference manuals.

    Software Downloads: are available through Teradata Developers Exchange to help you prepare through hands on application.

    Back to Top