Retrieve data from the database - SELECT

SELECT is the most commonly used statement in SQL. The SELECT statement in SQL is used to extract data from a database. We can recover the entire table or according to certain specified rules. The returned data is stored in a results table. This result table is also called a result set.

Syntax :

                                SELECT column1, column2,..., columnN FROM table_name;
                            

Here, column1, column2, ..., columnN are the fields of a table whose values you want to retrieve. If you want to retrieve all the fields, you can use the following syntax:

                                SELECT * FROM table_name;
                            

During this course, we will take the following table as a sample

+----+---------+-----+---------+
| Id | Name    | Age | Salary  |
+----+---------+-----+---------+
|  1 | Ismail  |  25 | 6000.00 |
|  2 | Adam    |  30 | 8000.40 |
|  3 | Fatima  |  29 | 6000.00 |
|  4 | Dounia  |  31 | 9000.00 |
|  5 | Omar    |  30 | 7500.00 |
+----+---------+-----+---------+
                            

Example 1 :

The following query is an example that would extract the Id, Name and Salary fields of the employees available in the Employees table:

                                SELECT Id, Name, Salary FROM Employees;
                            

This would produce the following result:

+----+---------+---------+
| Id | Name    | Salary  |
+----+---------+---------+
|  1 | Ismail  | 6000.00 |
|  2 | Adam    | 8000.40 |
|  3 | Fatima  | 6000.00 |
|  4 | Dounia  | 9000.00 |
|  5 | Omar    | 7500.00 |
+----+---------+---------+
                            

Example 2 :

If you want to retrieve all the fields from the Employees table, you must use the following query:

                                SELECT * FROM Employee;
                            

This would produce the following result:

+----+---------+-----+---------+
| Id | Name    | Age | Salary  |
+----+---------+-----+---------+
|  1 | Ismail  |  25 | 6000.00 |
|  2 | Adam    |  30 | 8000.40 |
|  3 | Fatima  |  29 | 6000.00 |
|  4 | Dounia  |  31 | 9000.00 |
|  5 | Omar    |  30 | 7500.00 |
+----+---------+-----+---------+
                            

To rename a specific column in the result set, use the keyword AS in the query.

Example 1 :

To rename the Name column to "Employee Name" in the result set, use the following query:

                                SELECT Id, Name AS "Employee Name", Salary FROM Employees;
                            

This would produce the following result:

+----+-----------------+---------+
| Id | Employee Name   | Salary  |
+----+-----------------+---------+
|  1 | Ismail          | 6000.00 |
|  2 | Adam            | 8000.40 |
|  3 | Fatima          | 6000.00 |
|  4 | Dounia          | 9000.00 |
|  5 | Omar            | 7500.00 |
+----+-----------------+---------+
                            

You can also use the keyword AS to assign a shortcut to the name of the table and use this shortcut in the query. This is very useful when we are dealing with multiple tables.

Example 1 :

                                SELECT emp.Id, emp.Name, Salary FROM Employees AS emp;
                            

This would produce the following result:

+----+---------+-----+---------+
| Id | Name    | Age | Salary  |
+----+---------+-----+---------+
|  1 | Ismail  |  25 | 6000.00 |
|  2 | Adam    |  30 | 8000.40 |
|  3 | Fatima  |  29 | 6000.00 |
|  4 | Dounia  |  31 | 9000.00 |
|  5 | Omar    |  30 | 7500.00 |
+----+---------+-----+---------+
                            

We can also use expressions in the SELECT statement

Example 1 :

                                SELECT Id, Name, (Salary*1.5) AS "New Salary" FROM Employees;
                            

This would produce the following result:

+----+---------+-----------------+
| Id | Name    | New Salary      |
+----+---------+-----------------+
|  1 | Ismail  |        9000.000 |
|  2 | Adam    |       12000.600 |
|  3 | Fatima  |        9000.000 |
|  4 | Dounia  |       13500.000 |
|  5 | Omar    |       11250.000 |
+----+---------+-----------------+