SQL - SELECT Query

After creating the table in an SQL database and inserting values into it, the next step is to check whether the values are inserted properly into this table or not. For that, one must try to retrieve the records present in the said table. You can do this using the SELECT statement.

The SELECT statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called result-sets.

Note − Clauses and Operators available in SQL are used with the SELECT statement in order to retrieve the filtered records of a database tables.

Syntax

The basic syntax of the SELECT statement is as follows −

SELECT column1, column2, columnN FROM table_name;

Here, column1, column2... are the fields of a table whose values you want to fetch. If you want to fetch all the fields available in the field, then you can use the following syntax.

SELECT * FROM table_name;

Example

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

To Retrieve Selective Fields

The following code is an example, which would fetch the ID, Name and Salary fields of the customers available in CUSTOMERS table.

SELECT ID, NAME, SALARY FROM CUSTOMERS;

Output

This would produce the following result −

+----+----------+----------+
| ID | NAME     | SALARY   |
+----+----------+----------+
|  1 | Ramesh   |  2000.00 |
|  2 | Khilan   |  1500.00 |
|  3 | kaushik  |  2000.00 |
|  4 | Chaitap |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal    |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+----------+

To Retrieve All Fields

If you want to fetch all the fields of the CUSTOMERS table, then you should use the following query of SELECT statement with an Asterisk (*).

SELECT * FROM CUSTOMERS;

Output

This would produce the result as shown below.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Computing using SELECT

Not only is the SELECT statement used to retrieve existing data from database tables, but also can be used to retrieve the results of various mathematical computations in the form of a table. In such cases, you need not specify any database table in the statement.

Following is the syntax to do so −

SELECT math_computation;

Example

In the following example, let us try to solve a mathematical computation using the SELECT statement.

SELECT 56*65;

The query above produces the following output −

+-------+
| 56*65 |
+-------+
| 3640  |
+-------+

Apasing a Column in SELECT Statement

Whenever a column name in a table is too difficult to read and understand, database provides a method to apas this column name into another understandable and relative name. This is done using the AS keyword. You can use the AS keyword in a SELECT statement to display the column names of a table as an apas name.

Following is the syntax to do so −

SELECT column_name AS apas_name FROM table_name;

You can also use an apas to display select expressions with the same syntax; you should use a select expression instead of column_name in the syntax.

Example

In the example below, we are trying to retrieve customer details NAME and AGE in a single column of the resultant table using the concat() expression and apasing the column as DETAILS along with the customer addresses from the CUSTOMERS table. This will be done using SELECT statement in the following query −

SELECT CONCAT(NAME,   ,AGE) AS DETAILS, ADDRESS FROM CUSTOMERS ORDER BY NAME;

The query above produces the following output −

+-----------------+-------------+
| DETAILS         | ADDRESS     |
+-----------------+-------------+
| Chaitap 25     | Mumbai      |                  
| Hardik 27       | Bhopal      |                  
| kaushik 23      | Kota        |                   
| Khilan 25       | Delhi       |                    
| Komal 22        | MP          |                      
| Muffy 24        | Indore      |                  
| Ramesh 32       | Ahmedabad   |               
+-----------------+-------------+