In IBM DB2, 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 DB2 with 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 DB2. 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 DB2:-
1. Can I use wildcard characters with the SELECT statement in DB2?
- Yes, you can use the asterisk (*) wildcard character 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 DB2?
- By default, DB2 performs case-insensitive searches. Therefore, you don't need to worry about case sensitivity when performing searches. For example:-
SELECT * FROM employees WHERE first_name = 'John';
This query retrieves 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 DB2?
- 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 DB2?
- 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 DB2?
- 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.
