In Teradata, the SELECT statement is used to retrieve data from one or more tables in the database. It allows you to specify the columns you want to retrieve and filter the rows based on specific conditions. Here's how you can use the SELECT statement in Teradata with various examples:-
Assuming we have a table named employees with columns employee_id, first_name, last_name, and salary, let's demonstrate various SELECT queries:-
Table: employees
+-------------+-------------+------------+--------+
| employee_id | first_name | last_name | salary |
+-------------+-------------+------------+--------+
| 1 | John | Doe | 50000 |
| 2 | Jane | Smith | 60000 |
| 3 | Michael | Johnson | 55000 |
| 4 | Emily | Brown | 65000 |
+-------------+-------------+------------+--------+
1. Select all columns from the employees table:-
SELECT * FROM employees;
2. Select specific columns (first_name, last_name, salary) from the employees table:-
SELECT first_name, last_name, salary FROM employees;
3. Select employees with a salary greater than 55000:-
SELECT * FROM employees WHERE salary > 55000;
4. Select employees with a specific first name (e.g., John):-
SELECT * FROM employees WHERE first_name = 'John';
5. Select employees sorted by salary in descending order:-
SELECT * FROM employees ORDER BY salary DESC;
6. Select the total number of employees:-
SELECT COUNT(*) AS total_employees FROM employees;
7. Select the average salary of employees:-
SELECT AVG(salary) AS average_salary FROM employees;
8. Select distinct last names from the employees table:-
SELECT DISTINCT last_name FROM employees;
These are just a few examples of how to use the SELECT statement in Teradata. You can modify and expand upon these queries based on your specific requirements and table structure.
Here are five frequently asked questions (FAQs) about using the `SELECT` statement in Teradata:-
1. Can I use wildcard characters with the SELECT statement in Teradata?
- Yes, you can use wildcard characters like * to select all columns from a table. For example:-
SELECT * FROM employees;
This query selects all columns from the employees table.
2. How do I perform case-insensitive searches in Teradata?
- Teradata's default behavior is case-insensitive, so you can perform searches without worrying about case sensitivity. For example:-
SELECT * FROM employees WHERE first_name = 'John';
This query will retrieve records where the first_name is John, regardless of the case.
3. Can I use aggregate functions like SUM, AVG, and COUNT with the SELECT statement in Teradata?
- Yes, you can use aggregate functions to perform calculations on columns in your `SELECT` queries. For example:-
SELECT AVG(salary) AS average_salary FROM employees;
This query calculates the average salary of employees.
4. How can I filter results based on multiple conditions in Teradata?
- You can use logical operators like AND, OR, and NOT to combine multiple conditions in your WHERE clause. For example:-
SELECT * FROM employees WHERE salary > 50000 AND department = 'IT';
This query retrieves employees with a salary greater than 50000 and working in the IT department.
5. Is it possible to sort query results in Teradata?
- Yes, you can use the ORDER BY clause to sort query results based on one or more columns. For example:
SELECT * FROM employees ORDER BY salary DESC;
This query sorts employees by salary in descending order.
