w3resource logo

:

SQL Tutorial

SQL SELECT statement

SELECT statement

SQL Select statement tells the database to fetch information from a table.

A query or SELECT statement is a command which gives instructions to a database to produce certain information(s) from the table in its memory.

The SELECT command starts with the keyword SELECT followed by a space and a list of comma separated columns. A * character can be used to select all the columns of a table.

The table name comes after the FROM keyword and a white-space.

Syntax:

SELECT *|{[DISTINCT] column|expression [alias]...}
FROM <table_name>;

Parameters:

Name Description
* Specifies all the columns of a table.
table_name Name of the table.
column Column names.

SQL select all columns

To retrieve all the columns from a table, * character is used with SQL SELECT statement.

Example:

The following query displays all the columns of agents table:

Sample table: agents


SQL Code:

SELECT * FROM agents;

To achieve the same result, you can use the following statement :

SQL Code:

SELECT agent_code,agent_name,
working_area,commission,phone_no
FROM agents;

Output:

AGENT_CODE AGENT_NAME                     WORKING_AREA          COMMISSION PHONE_NO
---------- ------------------------------ --------------------- ---------- -------------
A003       Alex                           London                       .13 075-12458969
A001       Subbarao                       Bangalore                    .14 077-12346674
A009       Benjamin                       Hampshair                    .11 008-22536178
A007       Ramasundar                     Bangalore                    .15 077-25814763
A008       Alford                         New York                     .12 044-25874365
A011       Ravi Kumar                     Bangalore                    .15 077-45625874
A010       Santakumar                     Chennai                      .14 007-22388644
A012       Lucida                         San Jose                     .12 044-52981425
A005       Anderson                       Brisban                      .13 045-21447739
A002       Mukesh                         Mumbai                       .11 029-12358964
A006       McDen                          London                       .15 078-22255588
A004       Ivan                           Torento                      .15 008-22544166

SQL SELECT: specific columns

The SELECT command can be used to fetch specific column(s) from a table.

Example:

To get all values of agent_name, working_area and commission columns from the agents table, the following SQL statement can be used :

Sample table: agents


SQL Code:

SELECT agent_name,working_area,commission
FROM agents;

Output:

AGENT_NAME                               WORKING_AREA                        COMMISSION
---------------------------------------- ----------------------------------- ----------
Alex                                     London                                     .13
Subbarao                                 Bangalore                                  .14
Benjamin                                 Hampshair                                  .11
Ramasundar                               Bangalore                                  .15
Alford                                   New York                                   .12
Ravi Kumar                               Bangalore                                  .15
Santakumar                               Chennai                                    .14
Lucida                                   San Jose                                   .12
Anderson                                 Brisban                                    .13
Mukesh                                   Mumbai                                     .11
McDen                                    London                                     .15
Ivan                                     Torento                                    .15

SQL SELECT : Using Arithmetic Operators (+, -, *, /)

Within SELECT statement you can create an expression with number and field value using arithmetic operators. Here is an example with output:


SQL Code:

SELECT first_name, last_name, salary, (12*salary + 400)
FROM employees;

Output:

FIRST_NAME           LAST_NAME                     SALARY (12*SALARY+400)
-------------------- ------------------------- ---------- ---------------
Steven               King                           24000          288400
Neena                Kochhar                        17000          204400
Lex                  De Haan                        17000          204400
Alexander            Hunold                          9000          108400
Bruce                Ernst                           6000           72400
David                Austin                          4800           58000
Valli                Pataballa                       4800           58000
Diana                Lorentz                         4200           50800
Nancy                Greenberg                      12008          144496
Daniel               Faviet                          9000          108400
John                 Chen                            8200           98800
Ismael               Sciarra                         7700           92800
..................................................

SQL SELECT: Using Column Aliases

- To renames a column heading temporarily for a particular SQL query you can use column aliases.
- There can be an optional AS keyword between the column name and alias
- It requires double quotation marks if the column name string contains spaces or special characters or if it is case sensitive.

See the following examples using AS keyword and without AS Keyword.


SQL Code:

SELECT first_name AS "First Name", 
last_name AS "Last Name", salary AS "Salary"
FROM employees;

Output:

First Name           Last Name                     Salary
-------------------- ------------------------- ----------
Steven               King                           24000
Neena                Kochhar                        17000
Lex                  De Haan                        17000
Alexander            Hunold                          9000
Bruce                Ernst                           6000
David                Austin                          4800
Valli                Pataballa                       4800
Diana                Lorentz                         4200
Nancy                Greenberg                      12008
Daniel               Faviet                          9000
John                 Chen                            8200
Ismael               Sciarra                         7700
Jose Manuel          Urman                           7800
Luis                 Popp                            6900
Den                  Raphaely                       11000
Alexander            Khoo                            3100
Shelli               Baida                           2900
Sigal                Tobias                          2800
...................................

SQL Code:

SELECT first_name "First Name", 
last_name "Last Name", salary "Salary"
FROM employees;

Output:

First Name           Last Name                     Salary
-------------------- ------------------------- ----------
Steven               King                           24000
Neena                Kochhar                        17000
Lex                  De Haan                        17000
Alexander            Hunold                          9000
Bruce                Ernst                           6000
David                Austin                          4800
Valli                Pataballa                       4800
Diana                Lorentz                         4200
Nancy                Greenberg                      12008
Daniel               Faviet                          9000
John                 Chen                            8200
Ismael               Sciarra                         7700
Jose Manuel          Urman                           7800
Luis                 Popp                            6900
Den                  Raphaely                       11000
Alexander            Khoo                            3100
Shelli               Baida                           2900
Sigal                Tobias                          2800
Guy                  Himuro                          2600
...........................................

SQL SELECT statement with NULL values

Before storing a value in any field of a table, a NULL value can be stored; later that NULL value can be replaced with the desired value. When a field value is NULL it means that the database assigned nothing (not even a zero "0" or blank " " ), in that field for that row.

Since the NULL represents an unknown or inapplicable value, it can’t be compared using the AND / OR logical operators. The special operator ‘IS’ is used with the keyword ‘NULL’ to locate ‘NULL’ values. NULL can be assigned to both types of fields i.e. numeric or character type.

Example:

Sample table: foods


To get data of all columns from the foods table with the following condition -

1. company_id column must contain NULL value,

the following SQL statement can be used:

SQL Code:

SELECT * FROM foods
WHERE company_id IS NULL;

Output:

ITEM_ID  ITEM_NAME                 ITEM_UNIT COMPANY_ID
-------- ------------------------- --------- ----------
7        Salt n Shake              Pcs

In the next session, we have discussed Select with distinct and SQL select with distinct on multiple columns in detail.