SQL Syntax

In today’s article, we’ll give you a walkthrough of core SQL syntax with a focus on the SELECT, INSERT INTO, UPDATE, and DELETE statements.

SQL is a programming language designed specifically to communicate with databases. It allows you to get the data from a database or change the data within it.

Like any programming language, SQL has a syntax you must adhere to. In other words, there are rules to follow regarding its commands if you want your code to work.

SQL syntax is best learned on the basic SQL statements. You can then build on them and write more complex code following the same rules. If you’re new to SQL, we recommend our SQL Basics interactive course. It covers the SELECT statement and other very common SQL statements and functions. For example, you’ll learn to filter data using the WHERE clause and logical operators. This course will teach you to combine data from multiple tables using JOINs, aggregate data into groups using GROUP BY, and combine query results using the set operators UNION, UNION ALL, and INTERSECT.

With more than 120 interactive exercises, SQL Basics is a good start for learning how to use SQL. You can also use our free SQL Basics Cheat Sheet as you read this article.

We have already mentioned databases several times. What is so specific about them that they need a dedicated programming language?

How Are Databases Organized?

The databases consist of one or more tables. The purpose of the tables is to store data in an organized way. The key word here is ‘organized’, which means tables must have a certain structure.

Each table has a name to differentiate between the tables within the database – preferably a name that is descriptive enough to know what data is stored in that table.

Each table has columns that also have names. The columns define the data in the table, while the column names describe it.  The tables also have rows, and this is where data is stored. For example, the table below  is named employees. It has the following columns:

• id – The employee’s ID number.
• first_name – The employee’s first name.
• last_name – The employee's last name.
• salary – The employee’s salary.
• department – The employee’s department.

The data from the table is shown below:

As you can see, the table has ten rows, with each row holding data for one employee.

Common SQL Statements and Their Syntax

To use SQL for querying data, you need SQL statements. They are commands issued to the database, which performs a task according to what is written in the code.

The SQL statements usually end with a semicolon. This isn’t a mandatory part of the syntax: the query will work the same without it. However, ending the SQL statement with a semicolon is a good practice. It adds to code readability, especially when you write a lot of code. In the following examples, we’ll stick to that practice.

Now let’s review some of the most common SQL statements.

SELECT Statement

The SELECT statement is used for retrieving data from the databases. You can select all the data or just the information in specific columns. You can also filter data, perform computations and data aggregations, and do other things all within this statement.

Let’s look at some of SELECT’s possibilities and how its syntax works.

Select All Data from the Table

The syntax for selecting all data from the table is:

SELECT *
FROM table;

First, you write SELECT.  After that, you specify that you want to select all the data. When we say ‘all data’, we mean ‘all columns from the table’ — an asterisk (*) in the SELECT statement does precisely that.

After that, you need to tell the database from which table you want to fetch data. This is the second line of the code, which starts with the FROM clause. It announces that the table will be specified immediately after this word, so you simply write the table's name.

Here’s how it works:

SELECT *
FROM employees;

This code translates as ‘select all columns from the table employees. And it does exactly that!

Select Specific Columns from the Table

The only difference compared to the syntax above is that you list specific columns instead of writing an asterisk:

SELECT column1,
	 column2
FROM table;

When you list the columns, it’s important to separate the columns with a comma; otherwise, the code won’t work.

It’s also important to note that there’s no comma after the last column.

While it’s not necessary to write each table column in a new row, it’s a good practice. We advise you to follow it. It helps with the code readability as statements get more complex. Having every column in a separate row makes it easier to follow which columns you selected and whether you made a syntax mistake – e.g. noticing that you missed a comma between column names is easier.

Let’s use this syntax on our example table:

SELECT first_name,
	 last_name
FROM employees;

This query selects the columns first_name and last_name from the table employees.

Filter Data Using WHERE

The SELECT statement allows you to use other clauses in it, such as WHERE.

SELECT …
FROM table
WHERE …;

This is the same SELECT statement as before. The only difference is you write the WHERE clause after specifying the table in the FROM clause.

The WHERE clause allows you to filter the returned data by setting one or more conditions that must be satisfied.

For example:

SELECT first_name,
	 last_name,
	 salary
FROM employees
WHERE salary > 3000;

This query selects employees’ first and last name and their salary. These are all columns from the table employees. However, I want to show only the employees that have a salary above 3,000. To do that, I first write the WHERE clause. Then comes the condition: the column salary has to be higher than 3,000.

Here is the data that the query returns:

There are five employees with a salary higher than 3,000 and they are all listed above.

This is only one of many ways to use WHERE. To learn more, check out this guide to writing the WHERE clause.

Group Rows with GROUP BY

If you use the GROUP BY clause in the SELECT statement, you can group rows into groups.

SELECT …
FROM table
GROUP BY …; 

The syntax shows a standard SELECT statement. The FROM clause is followed by the GROUP BY clause, where you list the columns by which you want data to be grouped. The rows are grouped by the data in a specific column or columns; rows with the same column value will be in the same group.

One of GROUP BY’s practical uses is to count the number of employees in each department. To do this, you need to select the department column and use the COUNT() aggregate function to count the number of employees. When you use GROUP BY, you can group the result by the department. And there you have it: the number of employees by department.

For examples on how GROUP BY works, read GROUP BY in SQL Explained or 5 GROUP BY examples.

Filter Data Using HAVING

The HAVING clause is most often used with the GROUP BY clause. It has the same purpose as WHERE, except that  WHERE filters data before aggregation, while HAVING filters aggregated records. That’s why you always have to write HAVING after GROUP BY:

SELECT …
FROM table
GROUP BY …
HAVING …;

The conditions in HAVING are written the same way as in the WHERE clause. Since it filters data after aggregation, aggregate functions are allowed in HAVING.

How do you use this in real life? One common example is to calculate the average salary by department, and then use HAVING to show only those departments with an average salary above a certain amount.

For detailed examples of how this clause works, see The SQL HAVING Clause Explained.

Sort Query Results with ORDER BY

One of the commonly used clauses in the SELECT statement is ORDER BY. It is used to sort the query results in ascending or descending order. Depending on the data type, this could mean sorting data from the highest to lowest value, from the oldest or the newest date, or alphabetically.

The ORDER BY clause allows you to sort results by one or more columns.

Here’s the syntax:

SELECT …
FROM table
…
ORDER BY …;

A simple and practical use of ORDER BY is to sort employees according to their salary, from highest to lowest.

You can find some detailed examples of this clause in How to Sort Records with the ORDER BY Clause.

INSERT INTO Statement

This command is used to insert data into the database. Here’s the syntax:

INSERT INTO table (column1, column2, …)
VALUES (value1, value2, …);

After INSERT INTO, you have to write the name of the table you want the data to insert into. Then, in the parentheses, you write the columns you want to change. As in SELECT, columns are separated by a comma.

After that, there’s the VALUES command. In the parentheses, you write the values you want to insert into the table. Commas also separate these values. Have a look at this example:

INSERT INTO employees (id, first_name, last_name)
VALUES (11, 'Obi-Wan', 'Kenobi');

This query inserts data into the table employees. It changes the columns id, first_name, and last_name. The new row inserted in the table will be for the employee Obi-Wan Kenobi, whose ID is 11.

If you selected all the columns from this table (you know how to do it!), here’s what you’d get:

As I didn’t set the salary and department values for the new employee, they’re empty. The database uses NULL to mark empty values.

UPDATE Statement

UPDATE is used to modify data in the table. This command is usually run with the WHERE clause. If you didn’t do that, all the data in the table would be changed.

The syntax looks like this:

UPDATE table
SET column1 = value1, column2 = value2, …
WHERE …;

After writing UPDATE, you write the name of the table that is getting updated. Then comes the SET command. It sets the columns to a specific value. In other words, you say which column you want to be updated and with which values. You use an equal sign to set the values, with every column and its new value separated by a comma. WHERE is here to limit the updates only to those values that satisfy a specific condition.

Here’s an example of updating the values in the table employees:

UPDATE employees
SET department = 'Sales'
WHERE id = 2;

In this query, I specify that I want to update the table employees. Then I want to have the value ‘Sales’ in the column department for the employee whose ID is 2. This employee moved from Accounting to Sales; that’s why this update is required.

Here’s the updated table:

The result shows Steve Mason is now in Sales.

DELETE Statement

As you probably guessed, this command is used for deleting data from the table.

Again, the WHERE clause here plays an important part. It allows deleting only certain data, i.e., that satisfies the condition in the WHERE clause. If you omit WHERE, the DELETE statement deletes all data from the table.

This is the syntax:

DELETE
FROM table
WHERE …;

After  DELETE comes the FROM clause. Similar to the SELECT statement, it holds the name of the table you want to delete data from. After that, be sure to write the WHERE clause if you don’t want to delete all the data in the table.

Here’s a practical example:

DELETE 
FROM employees
WHERE id = 11;

This query deletes all the rows where the ID is 11. Here’s the result:

I deleted the new employee Obi-Wan Kenobi from the table.

From SQL Syntax to SQL Statements

To make a statement in SQL – as in any natural language – you have to know its syntax. This opens many otherwise closed doors.

As you saw, knowing only the basics allows you to select data from the table, filter it, and group it. This is the SELECT statement, and knowing this basic command is only a start. There are some other common SQL statements that allow you to insert data into the table, change it, or delete it.

Any language is learned faster if you use it every day. For SQL, this translates to writing code regularly. Finding opportunities to ‘speak’ SQL might be hard, but don’t worry. There are plenty of them in the SQL Basics course. Give it a try. Before you know it, you’ll be fluent in SQL!