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;
Relational Algebra Expression:
Relational Algebra Tree:
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;
Relational Algebra Expression:
Relational Algebra Tree:
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;
Relational Algebra Expression:
Relational Algebra Tree:
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;
Relational Algebra Expression:
Relational Algebra Tree:
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.
Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [8 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL SUBQUERIES
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- Introduction
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- Hospital Database
- Employee Database
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework