SQL SELECT statement

The SQL SELECT operator is used to extract records from one or more tables in a SQL database.

Syntax of the SELECT operator

SELECTexpressions_id
FROM tabs
[WHERE conds]

Parameters or arguments

• expressions_id – fields or calculations that you want to get.
• tabs – tables from which you want to upload data. After FROM operator at least one table must be specified.
• WHERE conds – Optional. Conditions that must be met for the records to be selected. If no conditions are specified, all records will be selected.

SELECTING ALL FIELDS FROM ONE TABLE

Consider an example showing how to use the SQL SELECT operator that selects all fields from a table.

SELECT *
FROM suppls
WHERE city_id = 'London'.
ORDER BY city_id DESC;

In this example of SQL SELECT statement, we used * to view all fields from the suppliers table where the suppliers live in London city. The resulting set is sorted by city in descending order.

SELECT INDIVIDUAL FIELDS FROM THE SAME TABLE

You can also use the SQL SELECT statement to select individual fields from a table.

SELECT suppl_name,
city_id,
state_id
FROM suppls
WHERE suppl_id > 1000
ORDER BY suppl_name ASC, city_id DESC;

This SQL SELECT example will only return suppl_name, city_id, state_id fields from a suppls table where the suppl_id value exceeds 1000. The results are sorted by suppl_name in ascending order, and city in descending order.

SELECTION OF FIELDS FROM SEVERAL TABLES

You can also use the SQL SELECT statement to extract fields from multiple tables.

SELECT ords.order_id,
suppls.suppl_name
FROM suppls
INNER JOIN ords
ON suppls.suppl_id = ords.suppl_id
ORDER BY ord_id;

This SQL SELECT example connects two tables into the resulting set, showing the order_id and suppl_name fields where the suppl_id value is in both the suppls and order tables. The results are sorted by order_id in ascending order.

Read more about SQL joins

Practical Exercise #1

Based on the employee table below, select all fields whose salary is less than or equal to $ 52,500 (sorting is not required):

--create table empls
CREATE TABLE empls
( empl_number int NOT NULL,
empl_name char(50) NOT NULL,
salary_id int,
CONSTRAINT empls_pk PRIMARY KEY (empl_number)
);

-- insert entries in the empls table.
insert into empls values (1, 'Kuzjakin', 3500);
insert into empls values (2, 'Bakini',7200);
insert into empls values (3, 'Galingen',51000);
insert into empls values (4, 'Medajan',68000);
insert into empls values (5, 'Simkhan',85200);

Contents of the empls table:

Solution for Practical Exercise #1:
The next SQL SELECT statement will select records from the empls table:

SELECT *
FROM empls
WHERE salary_id <= 52500;

As a result of sampling we’ll get:

Practical Exercise #2
Based on the table below, select the unique city_id values that are in Florida and organize the results in descending order by city_id:

--create a suppls table
CREATE TABLE suppls
( suppl_id int NOT NULL,
suppl_name char(50) NOT NULL,
city_id char(50),
state_id char(25),
CONSTRAINT suppls_pk PRIMARY KEY (suppl_id)
);
-- insert entries in the suppls table.
insert into suppls values (1, 'Mari', 'Houston', 'Texas');
insert into suppls values (2, 'Frida', 'Melbourne', 'Florida');
insert into suppls values (3, 'Madlen', 'Phoenix', 'Arizona');
insert into suppls values (4, 'Valentina', 'San Diego', 'California');
insert into suppls values (5, "Amba", "Jacksonville", "Florida");

Contents of the suppls table:

Solution for Practical Exercise #2:
The following SQL SELECT operator selects records from the suppls table:

SELECT DISTINCT city_id
FROM suppls
WHERE state_id = 'Florida'.
ORDER BY city_id DESC;

The result of the sample will be:

Practical Exercise #3
Based on the suppliers and orders tables below, select the suppl_id and suppl_name fields from the suppls table, and select the order_date field from the ords table, where the suppl_id field value in the suppliers table matches the suppl_id field value in the orders table. Sort the results by suppl_id in descending order.

--create the suppls table
CREATE TABLE suppls
( suppl_id int NOT NULL,
suppl_name char(50) NOT NULL,
city_id char(50),
state_id char(25),
CONSTRAINT suppls_pk PRIMARY KEY (suppl_id)
);
-- insert entries in the suppls table.
insert into suppls values (1, 'Mari', 'Houston', 'Texas');
insert into suppls values (2, 'Frida', 'Melbourne', 'Florida');
insert into suppls values (3, 'Madlen', 'Phoenix', 'Arizona');
insert into suppls values (4, 'Valentina', 'San Diego', 'California');
insert into suppls values (5, "Amba", "Jacksonville", "Florida");

Contents of the suppls table:

--create an ords table
CREATE TABLE ords
( order_id int NOT NULL,
suppl_id int NOT NULL,
order_date date NOT NULL,
quantity int,
CONSTRAINT ords_pk PRIMARY KEY (order_id)
);
-- insert entries in the table of orders
insert into ords values (1.1, '05.05.2014', 100);
insert into ords values (2,3,'12.02.2015',300);
insert into ords values (3,5,'12.01.2016',500);

Contents of the table:

SELECT statement in SQL Server