Basic about SELECT Statement – using and example

This SQL tutorial explains how to use the SQL SELECT statement with syntax, examples and practice exercises.

Description

The SQL SELECT statement is used to retrieve archives from one or more tables in your SQL database. The records retrieved are known as a end result set.

Subscribe

Syntax

The syntax for the SELECT statement in SQL is:

SELECT expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

expressions The columns or calculations that you want to retrieve. Use * if you want to pick all columns. tables The tables that you wish to retrieve archives from. There need to be at least one table listed in the FROM clause. WHERE conditions Optional. The conditions that must be met for the files to be selected. If no conditions are provided, then all archives will be selected. ORDER BY expression Optional. The expression used to kind the information in the end result set. If greater than one expression is provided, the values have to be comma separated. ASC Optional. ASC sorts the end result set in ascending order by way of expression. This is the default behavior, if no modifier is provider. DESC Optional. DESC varieties the end result set in descending order with the aid of expression.

DDL/DML for Examples

If you desire to comply with along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your very own database!

Get DDL/DML

Example – Select All Fields from a Table

Let’s seem to be at an example that suggests how to use the SQL SELECT announcement to pick all fields from a table.

In this example, we have a desk referred to as clients with the following data:

customer_id last_name first_name favorite_website 4000 Jackson Joe techonthenet.com 5000 Smith Jane digminecraft.com 6000 Ferguson Samantha bigactivities.com 7000 Reynolds Allen checkyourmath.com 8000 Anderson Paige NULL 9000 Johnson Derek techonthenet.com

Now let’s demonstrate how the SELECT statement works by using choosing all columns from the clients table. Enter the following SELECT statement:

Try It

SELECT *
FROM customers
WHERE favorite_website = 'techonthenet.com'
ORDER BY last_name ASC;

There will be 2 archives selected. These are the effects that you must see:

customer_id last_name first_name favorite_website 4000 Jackson Joe techonthenet.com 9000 Johnson Derek techonthenet.com

In this example, we have used * to signify that we desire to view all fields from the customers desk the place the favorite_website is ‘techonthenet.com’. The end result set is sorted by using last_name in ascending order.

Example – Select Individual Fields from a Table

You can also use the SQL SELECT assertion to pick out person fields from the table, as adversarial to all fields from the table.

In this example, we have a desk called suppliers with the following data:

supplier_id supplier_name metropolis state 100 Microsoft Redmond Washington 200 Google Mountain View California 300 Oracle Redwood City California 400 Kimberly-Clark Irving Texas 500 Tyson Foods Springdale Arkansas 600 SC Johnson Racine Wisconsin 700 Dole Food Company Westlake Village California 800 Flowers Foods Thomasville Georgia 900 Electronic Arts Redwood City California

Now let’s display how to use the SELECT declaration to pick out person columns from a table. Enter the following SELECT statement:

Try It

SELECT supplier_name, city
FROM suppliers
WHERE supplier_id > 500
ORDER BY supplier_name ASC, city DESC;

four records ought to be selected. These are the results that you ought to see:

supplier_name city Dole Food Company Westlake Village Electronic Arts Redwood City Flowers Foods Thomasville SC Johnson Racine

This instance would return solely the supplier_name and town fields from the suppliers desk where the supplier_id fee is greater than 500 The consequences are sorted by using supplier_name in ascending order and then city in descending order.

Example – Select Individual Fields From Multiple Tables

You can additionally use the SQL SELECT assertion to retrieve fields from a couple of tables.

In this example, we have a table known as orders with the following data:

order_id customer_id order_date 1 7000 2016/04/18 2 5000 2016/04/18 3 8000 2016/04/19 4 4000 2016/04/20 5 NULL 2016/05/01

And a desk known as clients with the following data:

customer_id last_name first_name favorite_website 4000 Jackson Joe techonthenet.com 5000 Smith Jane digminecraft.com 6000 Ferguson Samantha bigactivities.com 7000 Reynolds Allen checkyourmath.com 8000 Anderson Paige NULL 9000 Johnson Derek techonthenet.com

Now let’s choose columns from each the orders and clients tables. Enter the following SELECT statement:

Try It

SELECT orders.order_id, customers.last_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id
WHERE orders.order_id <> 1
ORDER BY orders.order_id;

There will be three files selected. These are the outcomes that you should see:

order_id last_name

2 Smith

3 Anderson

4 Jackson

This SELECT example joins two tables to gives us a end result set that shows the order_id from the orders desk and the last_name from the customers table. Each time we use a column in the SELECT statement, we prefix the column with the table name (for example, orders.order_id) in case there is any ambiguity about which table the column belongs to.

If you wanted to select all fields from the orders table and then the last_name subject from the customers table, you enter the following SELECT statement:

Try It

SELECT orders.*, customers.last_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id
WHERE orders.order_id <> 1
ORDER BY orders.order_id;

There will be three records selected. These are the consequences that you ought to see:

order_id customer_id order_date last_name 2 5000 2016/04/18 Smith 3 8000 2016/04/19 Anderson 4 4000 2016/04/20 Jackson

In this example, we use orders.* to signify that we favor to pick out all fields from the orders desk and then we choose the last_name field from the customers table.

Practice Exercises

If you want to check your competencies using the SQL SELECT statement, strive some of our practice exercises.

These workouts allow you to strive out your skills with the SELECT statement. You will be given questions that you want to solve. After every exercise, we supply the solution so you can check your answer. Give it a try!

Go to Practice Exercises