SQL Syntax (original) (raw)

Summary: in this tutorial, you’ll learn about SQL syntax that helps you to write SQL statements effectively.

Getting started with SQL syntax #

SQL (Structured Query Language) is the language you use to communicate with the databases.

SQL is a declarative language. It means you tell the database what you want, not how to get it. This feature makes SQL very easy to learn compared to other programming languages.

Basic SQL statement structures are verbs, subjects, and conditions.

Most SQL statements follow a pattern:

• Verb (Action): is the action you want the database to do, such as SELECT, INSERT, UPDATE, and DELETE.
• Subject (Target): is the database object you work with, such as a table.
• Condition (Filter): choose which data you’re interested in.

For example, you want to find the employees hired in 1999. In SQL, you use the following statement:

SELECT first_name FROM employees WHERE YEAR (hire_date) = 1999;

Try it

In this statement:

• SELECT first_name: retrieves the values in the first_name column.
• FROM employees: gets the data from the employees table.
• WHERE YEAR(hire_date) = 1999: only consider employees who were joined in 1999.

SQL building blocks #

SQL statements consist of smaller pieces called tokens. These are like words and punctuation in an English sentence.

Here are the important types of tokens:

Literals #

Literals are the constants or actual values you place in SQL statement:

Strings are enclosed in single quotes like 'Anthony' and 'Blue'. Note that string literal is case-sensitive. For example, 'Anthony' is different from 'anthony'.

If a string contains a single quote, you can escape it with another single quote like this:

'I''m Anthony'Code language: JavaScript (javascript)

There are two single quote characters between the letters I and m.

Numbers. SQL supports integers, decimals, and floats. Literal numbers do not have quotes such as 100 and 59.99.

Booleans: are true and false.

Dates: date literals are in the format ‘yyyy-mm-dd’, for example '2000-12-31' is December 31, 2000.

Times: time literals are in the format 'hh:mm:ss' such as '12:45:30' .

Timestamps: timestamps include both date and time. Timestamp literals use the format 'yyyy-mm-dd hh:mm:ss' such as '2000-12-31 12:45:30' .

Keywords #

Keywords are reserved words in SQL. They have special meanings. The common keywords are SELECT, FROM, WHERE, CREATE, INSERT, etc.

Keywords are case-insensitive. By convention, we’ll use uppercase for keywords to make them stand out.

Identifiers #

Identifiers refer to objects in the database, such as tables, columns, indexes, etc.

Expressions (Combinations): Combinations of literals, identifiers, and operators that produce a value. For example, salary * 1.10 (calculates a 10% raise) or hire_date < '2023-01-01' (checks if the hire date is before January 1, 2023).

Comments are notes you add to your SQL code to explain why it does a specific task.

The database ignores comments, but they’re helpful for you and others to understand and troubleshoot later.

SQL supports two types of comments:

• Single-line comments start with two hyphens (--) and is followed by a comment.
• Multi-line comments start with /* and end with */. The text between these is a comment.

/* find employees who earn more than $50,000 */ SELECT first_name FROM employees WHERE salary > 50000; -- USD currency Code language: SQL (Structured Query Language) (sql)

The best way to learn SQL is to practice it.

Summary #

• SQL is declarative, i.e., you tell the database what you want, not how.
• Literals are constants you use in SQL statements. Literals are case-sensitive.
• Keywords are reserved words with special meanings. Keywords are case-insensitive.
• Identifiers are table names, columns, etc.
• Use a semicolon (;) to separate two SQL statements.
• Use a comment to add a note to SQL statements.

Quiz #

Was this tutorial helpful ?