SQL Select Statement
In this lesson, you will learn how to select records from a database table using SQL.
Select Records In SQL
The SELECT statement is used to retrieve data from a database.
Syntax
When selecting records, you can specify which columns you want to return from the table.
SELECT column1, column2, ... FROM table_name;
Also, you can use the asterisk character * to select all columns from the table.
SELECT * FROM table_name;
Preparing A Database
Since you will learn how to retrieve data from tables, you must have a database table with some records to practice retrieving them. Execute the following query to create the database.
Example
-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;
-- Create a new database called "demo"
CREATE DATABASE demo;
-- Select the "demo" database
USE demo;
-- Create a new table called "employees" inside the selected database
CREATE TABLE employees (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
is_married BOOLEAN,
salary DECIMAL(7,2),
birthday Date
);
-- Insert 10 records in the "employees" table
-- We add null in the id column because its value will be generated automatically by the database server
INSERT INTO employees VALUES (null, "Emily", "Johnson", false, 800, "1990-10-23");
INSERT INTO employees VALUES (null, "Benjamin", "Anderson", false, 750, "1992-05-08");
INSERT INTO employees VALUES (null, "Olivia", "Smith", true, 900, "1988-07-12");
INSERT INTO employees VALUES (null, "William", "Davis", false, 845, "1994-12-04");
INSERT INTO employees VALUES (null, "Sophia", "Brown", true, 1000, "1991-10-27");
INSERT INTO employees VALUES (null, "Daniel", "Martinez", false, 700, "1993-11-12");
INSERT INTO employees VALUES (null, "Ava", "Taylor", true, 850, "1995-03-10");
INSERT INTO employees VALUES (null, "James", "Wilson", true, 920, "1990-02-14");
INSERT INTO employees VALUES (null, "Mia", "Garcia", false, 780, "1991-08-12");
INSERT INTO employees VALUES (null, "Ethan", "Jones", true, 880, "1985-09-20");
Select All Records Example
The following query selects all records from the "employees" table.
Note: We use the asterisk character * to return all columns.
Example
SELECT * FROM employees;
Output
| id | first_name | last_name | is_married | salary | birthday |
|---|---|---|---|---|---|
| 1 | Emily | Johnson | 0 | 800.00 | 1990-10-23 |
| 2 | Benjamin | Anderson | 0 | 750.00 | 1992-05-08 |
| 3 | Olivia | Smith | 1 | 900.00 | 1988-07-12 |
| 4 | William | Davis | 0 | 845.00 | 1994-12-04 |
| 5 | Sophia | Brown | 1 | 1000.00 | 1991-10-27 |
| 6 | Daniel | Martinez | 0 | 700.00 | 1993-11-12 |
| 7 | Ava | Taylor | 1 | 850.00 | 1995-03-10 |
| 8 | James | Wilson | 1 | 920.00 | 1990-02-14 |
| 9 | Mia | Garcia | 0 | 780.00 | 1991-08-12 |
| 10 | Ethan | Jones | 1 | 880.00 | 1985-09-20 |
Return Specific Columns Data Example
The following query returns the first_name, last_name and salary values for all records in the "employees" table.
Example
SELECT first_name, last_name, salary
FROM employees;
Output
| first_name | last_name | salary |
|---|---|---|
| Emily | Johnson | 800.00 |
| Benjamin | Anderson | 750.00 |
| Olivia | Smith | 900.00 |
| William | Davis | 845.00 |
| Sophia | Brown | 1000.00 |
| Daniel | Martinez | 700.00 |
| Ava | Taylor | 850.00 |
| James | Wilson | 920.00 |
| Mia | Garcia | 780.00 |
| Ethan | Jones | 880.00 |
Select Records Exercise
1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.
2- Write a query that creates a table called "customers" and check whether it exists or not. the table should contain the following fields:
id INT NOT NULL PRIMARY KEY AUTO_INCREMENTfirst_name VARCHAR(50)last_name VARCHAR(50)phone VARCHAR(20)
3- Write a query that inserts 5 rows in the "customers" table.
4- Write a query that selects all records from the "customers" table.
Solution
DROP DATABASE IF EXISTS training;
CREATE DATABASE training;
USE training;
CREATE TABLE IF NOT EXISTS customers (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
phone VARCHAR(20)
);
INSERT INTO customers (first_name, last_name, phone) VALUES
('Jack', 'Reed', '+4420123456'),
('Emma', 'Clark', '+4420551025'),
('Luke', 'Lee', '+4420102044'),
('Jim', 'Wilson', '+4420119043'),
('Lily', 'Brooks', '+4420203458');
SELECT * FROM customers;
