The SELECT statement is used to retrieve data from one or more tables. It allows you to specify which columns you want to retrieve, filter rows based on conditions, join multiple tables together, perform calculations, and more. Here's a basic overview of the SELECT statement:-
Assuming we have a table named employees with columns employee_id, first_name, last_name, and salary, here's how you can create the table and insert some sample data:-
-- Table creation script
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(10, 2)
);
-- Inserting sample data
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (1, 'John', 'Doe', 50000),
(2, 'Jane', 'Smith', 60000),
(3, 'Michael', 'Johnson', 55000),
(4, 'Emily', 'Brown', 65000);
Now, let's demonstrate several `SELECT` queries:-
1. Select all columns from the employees table:-
SELECT * FROM employees;
Output
+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
| 1 | John | Doe | 50000.00 |
| 2 | Jane | Smith | 60000.00 |
| 3 | Michael | Johnson | 55000.00 |
| 4 | Emily | Brown | 65000.00 |
+-------------+------------+-----------+--------+
2. Select specific columns (first_name, last_name, salary) from the employees table:-
SELECT first_name, last_name, salary FROM employees;
Output:
+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| John | Doe | 50000.00 |
| Jane | Smith | 60000.00 |
| Michael | Johnson | 55000.00 |
| Emily | Brown | 65000.00 |
+------------+-----------+--------+
3. Select employees with a salary greater than 55000:-
SELECT * FROM employees WHERE salary > 55000;
Output:
+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
| 2 | Jane | Smith | 60000.00 |
| 4 | Emily | Brown | 65000.00 |
+-------------+------------+-----------+--------+
4. Select employees with a specific first name (e.g., John):-
SELECT * FROM employees WHERE first_name = 'John';
Output:
+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
| 1 | John | Doe | 50000.00 |
+-------------+------------+-----------+--------+
5. Select employees sorted by salary in descending order:-
SELECT * FROM employees ORDER BY salary DESC;
Output:
+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
| 4 | Emily | Brown | 65000.00 |
| 2 | Jane | Smith | 60000.00 |
| 3 | Michael | Johnson | 55000.00 |
| 1 | John | Doe | 50000.00 |
+-------------+------------+-----------+--------+
These are just a few examples of SELECT queries in MySQL. 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 MySQL:-
1. Can I use wildcard characters with the SELECT statement in MySQL?
- Yes, you can use wildcard characters such as % and _ with the SELECT statement in MySQL. For example, SELECT * FROM employees WHERE last_name LIKE 'Sm%' will select all employees whose last names start with "Sm".
2. What is the difference between SELECT DISTINCT and SELECT in MySQL?
- The SELECT DISTINCT statement is used to retrieve unique values from a specific column or combination of columns, while SELECT retrieves all rows that meet the specified criteria, including duplicate values.
3. How can I limit the number of rows returned by a SELECT query in MySQL?
- You can use the LIMIT clause to restrict the number of rows returned by a SELECT query in MySQL. For example, SELECT * FROM employees LIMIT 10 will return only the first 10 rows from the employees table.
4. Can I perform calculations within a SELECT statement in MySQL?
- Yes, you can perform calculations within a SELECT statement in MySQL using arithmetic operators like +, -, *, and /. For example, SELECT salary * 12 AS annual_salary FROM employees will calculate the annual salary for each employee.
5. Is it possible to join multiple tables in a single SELECT query in MySQL?
- Yes, you can join multiple tables in a single SELECT query in MySQL using different types of joins such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. This allows you to retrieve data from related tables based on specified conditions.
