Teradata SELECT Statement



SELECT is the simplest among all other statements and it is used to retrieve the records from the table.

Teradata SELECT Syntax

Following is the basic syntax of a SELECT statement.

SELECT column1, column2, ...
FROM tablename;

 

Teradata SELECT Example

Consider the below customer table.

cust_id income years_with_bank nbr_children gender marital_status
1,362,487 6,605 1 0 M 2
1,362,956 0 4 0 F 1
1,362,752 47,668 3 0 F 1
1,363,221 0 4 0 F 1
1,362,813 15,684 3 2 F 2
1,362,609 1,929 8 0 F 2

Following is the example of a SELECT query.

SELECT    cust_id, income, age, years_with_bank

FROM teradatapoint.customer;

 

If you execute the above query, it retrieves cust_id, income, age and years_with_bank columns from customer table.

 

    cust_id       income     age  years_with_bank
-----------  -----------  ------  ---------------
    1362487         6605      71                1
    1362956            0      10                4
    1362752        47668      54                3
    1363221            0      10                4
    1362813        15684      53                3
    1362609         1929      79                8

 

If you want to retrieve all the columns of customer table, you can use below query.

 

SELECT    * FROM teradatapoint.customer;  

 

Teradata Order by

The order by  clause can be used to get the records in an order according to your need. It may be either ascending or descending. The default is ascending.

 

Teradata Order by Syntax

Following is the syntax of the SELECT statement with ORDER BY clause.

SELECT column1, column2, ....
FROM tablename
ORDER BY 1 DESC;
 

Teradata Order by Example

Following is an example of a SELECT statement with ORDER BY clause.

SELECT    * FROM teradatapoint.customerorder by 1;

 

When you execute the above query, it will provide resultset with cust_id in ascending order.

cust_id       income     age  years_with_bank  nbr_children  gender  marital_status
-----------  -----------  ------  ---------------  ------------  ------  --------------
1362487         6605      71                1             0  M       2
1362609         1929      79                8             0  F       2
1362752        47668      54                3             0  F       1
1362813        15684      53                3             2  F       2
1362956            0      10                4             0  F       1
1363221            0      10                4             0  F       1
 

 

Teradata Group by

The GROUP BY is used to get the similar records into groups.

 

Teradata Group by Syntax

Following is the syntax of the SELECT statement with GROUP BY clause.

SELECT coulmn1, column2, ...
from tablename
group by 1;

 

Teradata Group by Example

The following example groups the records by years_with_bank and identifies the number of customer for each group.

SELECT    years_with_bank,count(*) FROM teradatapoint.customergroup by years_with_bankorder by years_with_bank;

 

Below is the output of the above query.

years_with_bank     Count(*)
---------------  -----------
              1            1
              3            2
              4            2
              8            1
 

Teradata WHERE clause

WHERE clause is used to filter the records returned by the SELECT statement. Only the records that satisfy the condition in the WHERE clause are returned.

 

Teradata Where clause Syntax

Following is the syntax of the SELECT statement with WHERE clause.

SELECT * from tablename

WHERE [condition];

 

Teradata WHERE clause Example

The following query fetches records where cust_id is 1362487.

SELECT    * FROM teradatapoint.customer WHERE cust_id=1362487;

 

Below is the output of above query.

    cust_id       income     age  years_with_bank  nbr_children  gender  marital_status
-----------  -----------  ------  ---------------  ------------  ------  --------------
    1362487         6605      71                1             0  M       2