Teradata - SELECT Statement

SELECT statement is used to retrieve records from a table.

Syntax

Following is the basic syntax of SELECT statement.

SELECT 
column 1, column 2, ..... 
FROM  
tablename;

Example

Consider the following employee table.

Following is an example of SELECT statement.

SELECT EmployeeNo,FirstName,LastName 
FROM Employee;

When this query is executed, it fetches EmployeeNo, FirstName and LastName columns from the employee table.

 EmployeeNo            FirstName                       LastName 
-----------  ------------------------------  --------------------------- 
   101                   Mike                            James 
   104                   Alex                            Stuart 
   102                   Robert                          Wilpams 
   105                   Robert                          James 
   103                   Peter                           Paul

If you want to fetch all the columns from a table, you can use the following command instead of psting down all columns.

SELECT * FROM Employee;

The above query will fetch all records from the employee table.

WHERE Clause

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

Syntax

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

SELECT * FROM tablename 
WHERE[condition];

Example

The following query fetches records where EmployeeNo is 101.

SELECT * FROM Employee 
WHERE EmployeeNo = 101;

When this query is executed, it returns the following records.

 EmployeeNo          FirstName                      LastName 
----------- ------------------------------ ----------------------------- 
   101                 Mike                           James 

ORDER BY

When the SELECT statement is executed, the returned rows are not in any specific order. ORDER BY clause is used to arrange the records in ascending/descending order on any columns.

Syntax

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

SELECT * FROM tablename 
ORDER BY column 1, column 2..;

Example

The following query fetches records from the employee table and orders the results by FirstName.

SELECT * FROM Employee 
ORDER BY FirstName;

When the above query is executed, it produces the following output.

 EmployeeNo         FirstName                      LastName 
----------- ------------------------------ ----------------------------- 
    104               Alex                           Stuart 
    101               Mike                           James 
    103               Peter                          Paul 
    102               Robert                         Wilpams 
    105               Robert                         James 

GROUP BY

GROUP BY clause is used with SELECT statement and arranges similar records into groups.

Syntax

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

SELECT column 1, column2 …. FROM tablename 
GROUP BY column 1, column 2..;

Example

The following example groups the records by DepartmentNo column and identifies the total count from each department.

SELECT DepartmentNo,Count(*) FROM   
Employee 
GROUP BY DepartmentNo;

When the above query is executed, it produces the following output.

 DepartmentNo    Count(*) 
------------  ----------- 
     3             1 
     1             1 
     2             3