The SELECT statement in SQL is used to retrieve data from a database. Below are different variations of the SELECT statement to suit different needs.
1. Select All Information
To retrieve all columns from a table:
SELECT * FROM table_name;*selects all columns.
2. Select Specific Columns
To retrieve specific columns:
SELECT column1, column2, ... FROM table_name;- List only the columns you need.
3. Select Distinct
To retrieve unique values (eliminating duplicates) for a column:
SELECT DISTINCT column_name FROM table_name;- Removes duplicate values in the specified column.
4. Select Distinct with Multiple Columns
To retrieve unique combinations of values from multiple columns:
SELECT DISTINCT column1, column2 FROM table_name;- Removes duplicate combinations of the specified columns.
5. Alias (AS)
To assign a temporary name (alias) to a column or table:
SELECT column_name AS alias_name FROM table_name;-
Alias makes the result more readable.
-
Example:
SELECT first_name AS "Employee Name" FROM employees;
6. Arithmetic Operations (Multiplication, Division, Addition, Subtraction)
Multiplication:
SELECT column1 * column2 FROM table_name;-
Example: Multiply quantity by unit price:
SELECT quantity * unit_price AS total_sales FROM sales;
Division:
SELECT column1 / column2 FROM table_name;-
Example: Divide total revenue by number of items:
SELECT total_revenue / items_sold AS average_price FROM sales;
Addition:
SELECT column1 + column2 FROM table_name;-
Example: Add two columns together:
SELECT base_salary + bonus AS total_salary FROM employees;
Subtraction:
SELECT column1 - column2 FROM table_name;-
Example: Subtract discount from price:
SELECT price - discount AS final_price FROM products;
Key Points:
- DISTINCT: Removes duplicates for one or more columns.
- Arithmetic Operations: Allows calculations like addition, subtraction, multiplication, and division on column values.
- Alias (AS): Improves query readability by giving columns or tables temporary names.