SQL SELECT Statement with Examples

SQL SELECT Statement

The SQL SELECT statement is used to retrieve data from a database. The data returned by a SELECT query is called a result set, which is structured in rows and columns according to the specified criteria in the query.

In this tutorial, we will go through the syntax of SQL SELECT statement, detailed explanation for each part of the syntax, and then an example using MySQL 8.0 with queries and results from the Workbench.

---

Basic Syntax of the SQL SELECT Statement

The basic syntax of a SQL SELECT statement is as follows:

SELECT select_list [ INTO new_table ]
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

Each part of this syntax has a specific purpose:

• SELECT: Specifies the columns to be retrieved. It can be specific columns or * to select all columns.
• INTO: Optional. Creates a new table to store the result.
• FROM: Specifies the table(s) from which to retrieve data.
• WHERE: Filters rows based on a specified condition.
• GROUP BY: Groups rows based on one or more columns.
• HAVING: Filters groups after grouping, typically used with aggregate functions.
• ORDER BY: Sorts the result set by one or more columns, in ascending (default) or descending order.

---

Step-by-Step Guide with MySQL

We’ll walk through each part of the SELECT syntax using a database and table in MySQL 8.0 with MySQL Workbench. The examples will focus on a sample students table with fields id, name, age, grade, and locality.

Step 1: Setting up the Database

1. Open MySQL Workbench.

2. To create a new database, run:

CREATE DATABASE school;

3. Select the school database to use in subsequent queries:

USE school;

Step 2: Creating the Table

Create a students table with fields for demonstration:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

Step 3: Inserting Sample Data into the Table

Insert some records into the students table for use in the examples:

INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

After inserting, the table students will look like this:

---

Examples of SQL SELECT Statement

Below are examples of different forms of the SELECT statement, explaining each part of the syntax.

---

1. Selecting All Columns

This query selects all columns from the students table:

SELECT * 
FROM students;

---

2. Selecting Specific Columns

This query selects only the name and age columns:

SELECT name, age 
FROM students;

---

3. Using WHERE Clause to Filter Rows

This query selects students who are 15 years old:

SELECT * 
FROM students 
WHERE age = 15;

---

4. Using GROUP BY Clause

This query groups students by their grade:

SELECT grade, COUNT(*) AS student_count 
FROM students 
GROUP BY grade;

---

5. Using HAVING with GROUP BY

This query shows only grades with more than one student:

SELECT grade, COUNT(*) AS student_count
FROM students
GROUP BY grade
HAVING COUNT(*) > 1;

---

6. Sorting Results with ORDER BY

This query orders students by their age in descending order:

SELECT * 
FROM students 
ORDER BY age DESC;

---

7. Combining WHERE and ORDER BY

This query selects students from Westend and orders them by name:

SELECT * 
FROM students 
WHERE locality = 'Westend' 
ORDER BY name;

---

8. Using WHERE with LIKE Operator

This query finds students whose name contains li:

SELECT * 
FROM students 
WHERE name LIKE '%li%';

---

Frequently Asked Questions (FAQ)

1. What does the SQL SELECT statement do?

The SQL SELECT statement is used to retrieve data from a database. It can return specific columns or all columns from a table, based on criteria defined by the user.

2. How do I select all columns from a table?

To select all columns from a table, use the * symbol with SELECT, like this:

SELECT * FROM table_name;

3. How do I filter results in a SELECT query?

To filter results, use the WHERE clause in the SELECT query. For example, to select rows where age is greater than 18:

SELECT * FROM students WHERE age > 18;

4. How can I sort the results of a SELECT query?

You can sort results using the ORDER BY clause. By default, it sorts in ascending order, but you can use DESC to sort in descending order:

SELECT * FROM students ORDER BY age DESC;

5. What is the difference between WHERE and HAVING in SQL?

The WHERE clause filters rows before grouping, while the HAVING clause filters groups after GROUP BY. For example:

SELECT grade, COUNT(*) FROM students GROUP BY grade HAVING COUNT(*) > 2;