SQL Syntax

Summary: in this tutorial, we will introduce you to the SQL syntax that helps you understand the details of SQL statements.

SQL is a declarative language, therefore, its syntax reads like a natural language. An SQL statement begins with a verb that describes the action, for example, SELECT, INSERT, UPDATE or DELETE. Following the verb are the subject and predicate.

A predicate specifies conditions that can be evaluated as true, false, or unknown.

See the following SQL statement:

SELECT 
    first_name
FROM
    employees
WHERE
    YEAR(hire_date) = 2000;Code language: SQL (Structured Query Language) (sql)

As you see, it reads like a normal sentence.

Get the first names of employees who were hired in 2000.

The SELECT first_name, FROM employees, and WHERE  are clauses in the SQL statement. Some clauses are mandatory e.g., the SELECT and FROM clauses whereas others are optional such as the WHERE clause.

Because SQL was designed specifically for the non-technical people in mind, it is very simple and easy to understand. To write an SQL statement, you just need to tell what you want instead of how you want it like other imperative languages such as PHP, Java, and C++.

SQL is a user-friendly language because it is mainly for the users who perform ad-hoc queries and generate reports.

Nowadays, SQL is used by the highly technical people like data analysts, data scientists, developers, and database administrators.

SQL commands

SQL is made up of many commands. Each SQL command is typically terminated with a semicolon (;). For example, the following are two different SQL commands separated by a semicolon (;).

SELECT 
    first_name, last_name
FROM
    employees;

DELETE FROM employees 
WHERE
    hire_date < '1990-01-01';Code language: SQL (Structured Query Language) (sql)

SQL uses the semicolon (;) to mark the end of a command.

Each command is composed of tokens that can be literals, keywords, identifiers, or expressions. Tokens are separated by space, tabs, or newlines.

Literals

Literals are explicit values which are also known as constants. SQL provides three kinds of literals: string, numeric, and binary.

String literal consists of one or more alphanumeric characters surrounded by single quotes, for example:

'John'
'1990-01-01'
'50'Code language: SQL (Structured Query Language) (sql)

50 is a number. However, if you surround it with single quotes e.g., '50', SQL treats it as a string literal.

Typically, SQL is case sensitive with respect to string literals, so the value 'John' is not the same as 'JOHN'.

Numeric literals are the integer, decimal, or scientific notation, for example:

200
-5
6.0221415E23Code language: SQL (Structured Query Language) (sql)

SQL represents binary value using the notation x'0000', where each digit is hexadecimal value, for example:

x'01'
x'0f0ff'
Code language: SQL (Structured Query Language) (sql)

Keywords

SQL has many keywords that have special meanings such as SELECT, INSERT, UPDATE, DELETE, and DROP. These keywords are the reserved words, therefore, you cannot use them as the name of tables, columns, indexes, views, stored procedures, triggers, or other database objects.

Identifiers

Identifiers refer to specific objects in the database such as tables, columns, indexes, etc. SQL is case-insensitive with respect to keywords and identifiers.

The following statements are equivalent.

Select  * From employees;

SELECT * FROM EMPLOYEES;

select * from employees;

SELECT * FROM employees;Code language: SQL (Structured Query Language) (sql)

To make the SQL commands more readable and clear, we will use the SQL keywords in uppercase and identifiers in lower case throughout the tutorials.

Comments

To document SQL statements, you use the SQL comments. When parsing SQL statements with comments, the database engine ignores the characters in the comments.

A comment is denoted by two consecutive hyphens ( --) that allow you to comment the remaining line.  See the following example.

SELECT 
    employee_id, salary
FROM
    employees
WHERE
    salary < 3000;-- employees with low salaryCode language: SQL (Structured Query Language) (sql)

This is an SQL comment.

-- employees with low salaryCode language: SQL (Structured Query Language) (sql)

To document the code that can span multiple lines, you use the multiline C-style notation ( /**/) as the shown in the following statement:

/* increase 5% for employees whose salary is less than 3,000 */
UPDATE employees 
SET 
    salary = salary * 1.05
WHERE
    salary < 3000;Code language: SQL (Structured Query Language) (sql)

In this tutorial, we have introduced you to the SQL syntax that helps you understand each component of an SQL statement.

To learn the SQL language more effectively, you need to have a good sample database to practice with. In the next tutorial, we will introduce you to a simple SQL database.