SQL with Examples : SQL SELECT Statement

Hits: 37

SQL SELECT Statement

The SQL statement that you will probably use most often is the SELECT statement. Its purpose is to retrieve data from one or more tables of the database (or even several databases) and display it.

The result of a SELECT statement is another table, also known as a result set.

Syntax

The simplest form of the SELECT statement contains a SELECT list with the FROM clause. It has the following syntax:

SELECT column_name(s)
FROM table_name;

In practice, however, there are always several more clauses in a SELECT statement than in the statement. The following is the syntax of a SELECT statement, with almost all possible clauses included:

SELECT column_name(s)
[INTO new_table_name]
FROM table_name
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression];

Note that anything inside the square bracket is an optional clause.

Sample Table

To help you better understand the examples, and enable you to follow along with the tutorial, we are going to use the following sample table.

This table is part of an ‘Employee Management System’ that contains basic information about employees.

ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
2 Eve 24 New York Developer 32000
3 Max 26 New York Janitor 9000
4 Kim 25 Chicago Manager 55000
5 Joe 23 Chicago Developer 30000
6 Sam 27 Chicago Janitor 10000

SELECT a Column

To use SELECT to retrieve table data, you must specify two things – what you want to select (column_name), and from where you want to select it (table_name).

For example, this simple SELECT statement retrieves a single column called ‘Name’ from the ‘Employees’ table.

SELECT Name
FROM Employees;
Name
Bob
Eve
Max
Kim
Joe
Sam

It is important to note that all SQL statements are case-insensitive, so SELECT is the same as select, which is the same as Select.

SELECT Multiple Columns

The same SELECT statement can be used to retrieve multiple columns from a table. Just specify the desired column names after the SELECT keyword, and separate them with a comma.

The following SELECT statement retrieves two columns from the ‘Employees’ table:

SELECT Name, Job
FROM Employees;
Name Job
Bob Manager
Eve Developer
Max Janitor
Kim Manager
Joe Developer
Sam Janitor

Note that the value of each column you name is retrieved in the order specified in the SELECT clause.

SELECT * (All Columns)

In addition to being able to specify desired columns, SELECT can also retrieve all columns without having to list them individually. This is done by specifying the * wildcard character in place of column names.

Let’s retrieve all columns from the table.

SELECT *
FROM Employees;
ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
2 Kim 25 Chicago Manager 55000
3 Eve 24 New York Developer 32000
4 Joe 23 Chicago Developer 30000
5 Max 26 New York Janitor 9000
6 Sam 27 Chicago Janitor 10000

When you specify a wildcard *, the columns are retrieved in the same order as they appear in the table’s definition.

SELECT * is usually read as “Select all columns”.

Limiting Results

The SELECT statement returns all matched rows by default. What if you want to return a specific number of rows?

This is possible, but unfortunately, it is one of those situations where not all SQL implementations are created equal. Each DBMS has its own clause to limit the number of entries to be retrieved.

For example, below queries retrieve only the first three rows.

--for MS SQL and MS Access
SELECTTOP 3 *
FROM Employees;

--for DB2
SELECT *
FROM Employees
FETCH FIRST 3 ROWS ONLY;

--for Oracle
SELECT *
FROM Employees
WHERE ROWNUM <=3;

--for MySQL, MariaDB, PostgreSQL, and SQLite
SELECT *
FROM Employees
LIMIT 3;
ID Name Age City Job Salary
1 Bob 28 New York Manager 60000
2 Eve 24 New York Developer 32000
3 Max 26 New York Janitor 9000

Removing Duplicates

In some cases, a query might return duplicate rows of data. For example, if you were to retrieve the list of jobs, you would see the following:

SELECT Job
FROM Employees;
Job
Manager
Developer
Janitor
Manager
Developer
Janitor

What you probably want in this case is the list of unique jobs. You can achieve this by adding the keyword DISTINCT just after the SELECT keyword.

SELECTDISTINCT Job
FROM Employees;
Job
Developer
Janitor
Manager

Column Aliases

Although the SQL server will generate labels for the columns returned by your queries, you may want to assign your own labels. You can do so by adding a column alias after each column of your SELECT clause.

Here’s the previous query against the ‘Employees’ table with column alias ‘Job_Titles’ applied to the column ‘Job’:

SELECTDISTINCT Job Job_Titles
FROM Employees;
Job_Titles
Developer
Janitor
Manager

An alias is just an alternate name for a column. It does not actually change the name of the column in the table, but it gives it a new name within the scope of the SELECT statement.

You can also include the AS keyword before the alias name. Use of the AS keyword is optional, but you can use it to improve readability.

SELECTDISTINCT Job AS Job_Titles
FROM Employees;
Job_Titles
Developer
Janitor
Manager

Expressions in SELECT Statement

The SELECT statement can do far more than simply retrieve columns. You can also perform some calculations on one or more columns and include them in a result.

Suppose you wanted to generate a calculated column called ‘Tax_paid’ that is 22% of the salary. You could issue the following SELECT query to calculate this dynamically.

SELECT Name,
       Salary,
       0.22 * Salary AS Tax_paid
FROM Employees;
Name Salary Tax_paid
Bob 60000 13200.00
Eve 32000 7040.00
Max 9000 1980.00
Kim 55000 12100.00
Joe 30000 6600.00
Sam 10000 2200.00

Notice that the ‘Tax_paid’ column is not stored in the table, but computed on-the-fly. This is a powerful feature of SQL, which allows us to keep stored data simple and use queries to perform complex calculations on top of it.

Here are some more things you can include in your SELECT clause.

  • Literals, such as strings or numbers
  • Built-in function calls
  • User-defined function calls

The next query demonstrates the use of a table column, a literal, an expression, and a built-in function call in a single query against the ‘Employees’ table:

SELECT Name,
       'Active' AS Status,
	   0.22 * Salary AS Tax_paid,
	   UPPER(Job) AS Job
FROM Employees;
Name Status Tax_paid Job
Bob Active 13200.00 MANAGER
Eve Active 7040.00 DEVELOPER
Max Active 1980.00 JANITOR
Kim Active 12100.00 MANAGER
Joe Active 6600.00 DEVELOPER
Sam Active 2200.00 JANITOR

Concatenating Column Values

You can use expressions not only with numbers but also with text and other data types.

For example, a helpful operator to use with text is concatenation, which merges two or more pieces of data together.

--for SQL server
SELECT Name + ' works as a ' + Job AS Description
FROM Employees;

--for DB2, Oracle and PostgreSQL
SELECT Name || ' works as a ' || Job AS Description
FROM Employees;

--for MySQL
SELECT CONCAT(Name, ' works as a ', Job) AS Description
FROM Employees;
Description
Bob works as a Manager
Eve works as a Developer
Max works as a Janitor
Kim works as a Manager
Joe works as a Developer
Sam works as a Janitor

 

 

Python Example for Beginners

Two Machine Learning Fields

There are two sides to machine learning:

  • Practical Machine Learning:This is about querying databases, cleaning data, writing scripts to transform data and gluing algorithm and libraries together and writing custom code to squeeze reliable answers from data to satisfy difficult and ill defined questions. It’s the mess of reality.
  • Theoretical Machine Learning: This is about math and abstraction and idealized scenarios and limits and beauty and informing what is possible. It is a whole lot neater and cleaner and removed from the mess of reality.

Data Science Resources: Data Science Recipes and Applied Machine Learning Recipes

Introduction to Applied Machine Learning & Data Science for Beginners, Business Analysts, Students, Researchers and Freelancers with Python & R Codes @ Western Australian Center for Applied Machine Learning & Data Science (WACAMLDS) !!!

Latest end-to-end Learn by Coding Recipes in Project-Based Learning:

Applied Statistics with R for Beginners and Business Professionals

Data Science and Machine Learning Projects in Python: Tabular Data Analytics

Data Science and Machine Learning Projects in R: Tabular Data Analytics

Python Machine Learning & Data Science Recipes: Learn by Coding

R Machine Learning & Data Science Recipes: Learn by Coding

Comparing Different Machine Learning Algorithms in Python for Classification (FREE)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.