ReadVitamin

SELECT statement is the most used keyword in any SQL language. Using it intelligently saves a lot of time and memory usage. The basic full syntax for the SELECT statement in Teradata SQL goes like this;

SELECT [ALL][TOP {integer | decimal} [PERCENT][WITH TIES]]
{*[.ALL]}
FROM [joined_table [RIGHT] JOIN joined_table ON search condition]
[FULL] [CROSS JOIN]
[(subquery) [AS] derived_tablename [(column name [..., column name])]
[WHERE search condition(s)]
[GROUP BY     [column name |column position|expression][..., column name |column position|expression]]
[HAVING condition]
[QUALIFY condition]
[SAMPLE {fraction_description [..., fraction_description]} ]
{count_decription [..., count_description   ]}
{ expression [ DESC ] [ expression [ ASC ] ] }
[ORDER BY     {column name [      ] [..., column name [     ]]}];
{column position [ASC] [column position [DESC ] ] }
[WITH {expression1 [..., expression1]}]
[BY expression2 [ASC|DESC] [..., expression2 [ASC|DESC]]];

To find out number of employee present in each department, try the following.

SELECT Department.DeptNo, COUNT (Employee.employee)
FROM Employee, Department
WHERE Employee.DeptNo = Department.DeptNo
GROUP BY Department.DeptNo

(instead, you can simply write 1 which is the column position in the SELECT statement)

Example of a sub query in Teradata SQL. The following SQL shows the Name and the Department No of the employees working for the same department under a particular manager, whose ID is 117.

SELECT Name, DeptNo
FROM employee, Department
WHERE employee.DeptNo = Department.DeptNo
AND employee.DeptNo IN (SELECT DeptNo FROM Department WHERE MgrNo = 117);

If the table is very large and you want to see a sample of data, there are a couple of ways to achieve the same in Teradata. One is by using TOP operator and the other is using SAMPLE operator.

SELECT TOP 10 cust_name, balance
FROM Customers
ORDER BY balance DESC;

The above query would give TOP 10 rows after showing the result set in descending order.

This following query would give 10 randomly chosen rows after showing the result set in descending order. Note the position of the operator changes based on which one you use.

SELECT cust_name, balance
FROM Customers sample 10
ORDER BY balance DESC;

Popularity: 98% [?]