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.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
SQL: Tips of the Day
Add a column with a default value to an existing table in SQL Server
Syntax:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES
Example:
ALTER TABLE SomeTable
ADD SomeCol Bit NULL --Or NOT NULL.
CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.
Database: SQL Server
Ref: https://bit.ly/3t9rgAW
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises