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 (ID,NAME,AGE,ADDRESS,SALARY) 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 −

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 −

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 &minus

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 −

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 −