1. sql
  2. /syntax

SQL Syntax

Definition

The commands used to insert, retrieve, update or delete data in SQL databases are called statements. The statements that are used to retrieve the data are most often called queries (as you query the database for answers).

The SQL syntax can consist of multiple elements:

  • Keywords - Words with a special meaning in SQL, such as SELECT, FROM, WHERE, etc.
  • Clauses - Keyword phrases that perform specific functions in a query, such as the SELECT clause to retrieve data.
  • Expressions - Combinations of values, operators, and functions used to manipulate data in a query.
  • Operators - Symbols used to compare or manipulate values in an expression, such as equal to (=), greater than (>), etc.
  • Functions - Built-in or user-defined procedures that perform specific calculations or operations on data.
  • Identifiers - Names assigned to database objects, such as tables, columns, and indexes.
  • Literals - Representations of constant values, such as numbers, strings, or dates.
  • Delimiters - Characters used to separate elements in a statement, such as commas or parentheses.
  • Comments - Explanatory text within a statement, ignored by the database management system.

Simple structure example of an SQL statement

To make things easier to understand, we'll have an example statement that we'll build along the way of explaining it. The statement will be a simple query, intented to list all the names and ages of users that are at least 25 years old, ordered by their age descendingly.

1. What action to perform?

This part of the statement tells the database system what action we would want performed. The most common ones are:

  • SELECT represents getting data displayed from the database
  • INSERT which allows us to insert new data to database
  • UPDATE lets us alter already existing data
  • DELETE removes data

Have in mind that the structure might vary from action to action. For our example, we will be using a very simple form of SELECT queries.

-- We tell the database that we want to SELECT the name and age
SELECT name, age

2. Where is the data found?

In the case of SELECT statements, this is the FROM clause. It tells the database what table we want to get the data from.

-- We tell the database that we want to SELECT the name and age
SELECT name, age
-- Our data is found in the users table, so we tell the database to take data FROM the users table
FROM users

3. What is the condition that we want the data to fulfill?

This is most usually the WHERE clause. It describes a condition that acts as a filter on the data. Only the data that fulfills the condition will pass through the "filter".

-- We tell the database that we want to SELECT the name and age
SELECT name, age
-- Our data is found in the users table, so we tell the database to take data FROM the users table
FROM users
-- We only want to see the users that are at least 25 years old, so we say that the age column must be greater than or equal to 25
WHERE age >= 25

4. In which order do we want the data to be displayed?

In our basic example, this is the last piece of the statement - the ORDER BY clause. This clause determines the order in which we will receive the data.

-- We tell the database that we want to SELECT the name and age
SELECT name, age
-- Our data is found in the users table, so we tell the database to take data FROM the users table
FROM users
-- We only want to see the users that are at least 25 years old, so we say that the age column must be greater than or equal to 25
WHERE age >= 25
-- We want the users to be ORDERed BY their age, with the oldest user being shown first and the rest following in a DESCending manner.
ORDER BY age DESC

You should now know about the most basic form of SQL queries. As the complexity of the data increases, so can the queries. For example, we could select data from multiple related tables (by using JOIN), we can add multiple conditions (by using AND or OR) and we could even get aggregated data (by using GROUP BY).

Clauses

SQL statements, in their most basic form, can be divided into multiple so-called "clauses". To better describe it, you can look at each of those clauses as a separate question that you need to answer in order to exactly describe what data you would want extracted, changed, inserted or deleted from the database system.

Below, you can find a list of the most commonly found SQL clauses across multiple DBMS (Database Management Systems):

  • SELECT - retrieves data from one or more tables in a database.
  • FROM - specifies the table or tables from which to retrieve data.
  • WHERE - specifies conditions that rows must meet to be included in the result set.
  • GROUP BY - groups rows that have the same values in specified columns.
  • HAVING - filters groups based on aggregate values computed within each group.
  • ORDER BY - sorts the result set in ascending or descending order based on one or more columns.
  • LIMIT - limits the number of rows returned in the result set.
  • OFFSET - specifies the starting point of the first row to be returned in the result set.
  • INSERT INTO - inserts new rows into a table.
  • UPDATE - modifies existing data in a table.
  • DELETE - removes rows from a table.
  • CREATE - creates a new table, index, or other database object.
  • ALTER - modifies the structure of an existing table or other database object.
  • DROP - deletes an existing table, index, or other database object.
  • JOIN - combines rows from two or more tables based on a related column between them.
  • UNION - combines the results of two or more SELECT statements into a single result set.
  • EXISTS - tests for the existence of rows that meet specified conditions.

Best Practices

In order to more easily master the craft of SQL, following best practices and conventions is a good habit to pick up early:

  • Use Uppercase for SQL keywords: SQL keywords are typically written in uppercase letters to make them more easily distinguishable from other elements in a query. This also helps to make SQL code easier to read.

  • Use Proper Indentation: Proper indentation helps to organize the code and make it easier to understand. Indent each clause and sub-clause within a statement to improve readability.

  • Use Aliases for Table Names: When working with multiple tables, it's a good idea to use aliases for table names. This makes the code shorter and more readable, and also helps to avoid ambiguity in the case of columns with the same name in different tables.

  • Use Single Quotes for String Literals: String literals in SQL are typically enclosed in single quotes. Double quotes are often used for identifiers, such as column or table names, while single quotes are used for string literals.