SQL – SELECT Query
”;
The SQL SELECT Statement
The SQL SELECT Statement is used to fetch the data from a database table which returns this data in the form of a table. These tables are called result-sets.
CLAUSES and OPERATORS available in SQL can be used with the SELECT statement in order to retrieve the filtered records of a database table.
Syntax
The basic syntax of the SELECT Query 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 columns available in a table, then you can use the following syntax −
SELECT * FROM table_name;
Example
Assume we have created a table named CUSTOMERS using the CREATE TABLE statement as shown below −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Now, insert values into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS VALUES (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ), (2, ''Khilan'', 25, ''Delhi'', 1500.00 ), (3, ''Kaushik'', 23, ''Kota'', 2000.00 ), (4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ), (5, ''Hardik'', 27, ''Bhopal'', 8500.00 ), (6, ''Komal'', 22, ''Hyderabad'', 4500.00 ), (7, ''Muffy'', 24, ''Indore'', 10000.00 );
The table will be created as −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | Kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
Retrieving Selected Fields/Columns
The following statement fetches the ID, Name and Salary fields of the records available in CUSTOMERS table.
SELECT ID, NAME, SALARY FROM CUSTOMERS;
Output
The above query would produce the following table −
ID | NAME | Salary |
---|---|---|
1 | Ramesh | 2000.00 |
2 | Khilan | 1500.00 |
3 | Kaushik | 2000.00 |
4 | Chaitali | 6500.00 |
5 | Hardik | 8500.00 |
6 | Komal | 4500.00 |
7 | Muffy | 10000.00 |
Retrieving All Fields/Columns
If you want to fetch all the fields of the CUSTOMERS table, then you should use the query of SELECT statement with an Asterisk (*) instead of the column names, as shown below −
SELECT * FROM CUSTOMERS;
Output
The resultant table will be −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | Kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
Computing Using SELECT
The SQL SELECT statement can also be used to retrieve the results of various mathematical computations in the form of a table. In such cases, you do not need to specify any database table in the statement.
Following is the syntax to do so −
SELECT mathematical_expression;
Example
Following is an example which multiply two given numbers using SQL statement.
SELECT 56*65;
The query above produces the following output −
56*65 |
---|
3640 |
Aliasing a Column in SELECT Statement
Whenever a column name in a table is too difficult to read and understand, SQL provides a method to alias 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 alias name.
Following is the syntax to do so −
SELECT column_name AS alias_name FROM table_name;
You can also use an alias to display SELECT expressions with the same syntax; you should use a mathematical statement instead of column_name.
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 aliasing 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 |
---|---|
Chaitali 25 | Mumbai |
Hardik 27 | Bhopal |
Kaushik 23 | Kota |
Khilan 25 | Delhi |
Komal 22 | Hyderabad |
Muffy 24 | Indore |
Ramesh 32 | Ahmedabad |
”;