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;
Examples
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 | +----+---------+-----------------+
0 Comment(s)