What is SQL Syntax?

SQL syntax is the set of rules used to write SQL statements. SQL commands are made up of keywords, table names, column names, operators, expressions, and other clauses.

A SQL statement normally ends with a semicolon (;), which separates one statement from another.

Basic SQL Statement

A simple SQL query can retrieve columns from a table using the SELECT and FROM keywords.

WITH students(id, name, score) AS (
  VALUES
    (1, 'Aman', 85),
    (2, 'Neha', 92),
    (3, 'Rahul', 78)
)
SELECT name, score
FROM students;

SQL Keywords

SQL keywords are reserved words that perform specific operations. Common keywords include SELECT, FROM, WHERE, INSERT, UPDATE, and DELETE.

WITH products(id, name, price) AS (
  VALUES
    (1, 'Keyboard', 800),
    (2, 'Mouse', 400),
    (3, 'Monitor', 1500)
)
SELECT name, price
FROM products
WHERE price > 500;

SQL Statements End with a Semicolon

A semicolon is commonly used to terminate an SQL statement. It is especially important when multiple SQL statements are written together.

WITH numbers(value) AS (
  VALUES
    (10),
    (20),
    (30)
)
SELECT value
FROM numbers;

Selecting Specific Columns

Column names are placed after the SELECT keyword. Multiple columns are separated by commas.

WITH employees(id, name, department, salary) AS (
  VALUES
    (1, 'Aarav', 'IT', 65000),
    (2, 'Meera', 'Finance', 72000),
    (3, 'Rohan', 'Sales', 58000)
)
SELECT name, department, salary
FROM employees;

Selecting All Columns

The asterisk (*) is used to select all columns from a table or query result.

WITH employees(id, name, department) AS (
  VALUES
    (1, 'Aarav', 'IT'),
    (2, 'Meera', 'Finance'),
    (3, 'Rohan', 'Sales')
)
SELECT *
FROM employees;

Using WHERE in SQL Syntax

The WHERE clause is used to filter rows according to a condition.

WITH products(name, price) AS (
  VALUES
    ('Keyboard', 800),
    ('Mouse', 400),
    ('Monitor', 1500),
    ('Webcam', 1000)
)
SELECT name, price
FROM products
WHERE price >= 1000;

Using ORDER BY

The ORDER BY clause sorts query results. Use ASC for ascending order and DESC for descending order.

WITH products(name, price) AS (
  VALUES
    ('Keyboard', 800),
    ('Mouse', 400),
    ('Monitor', 1500),
    ('Webcam', 1000)
)
SELECT name, price
FROM products
ORDER BY price DESC;

Using LIMIT

In SQLite, LIMIT restricts the number of rows returned by a query.

WITH products(name, price) AS (
  VALUES
    ('Keyboard', 800),
    ('Mouse', 400),
    ('Monitor', 1500),
    ('Webcam', 1000),
    ('Speaker', 2200)
)
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

Using DISTINCT

The DISTINCT keyword removes duplicate values from the result.

WITH employees(name, department) AS (
  VALUES
    ('Aarav', 'IT'),
    ('Meera', 'Finance'),
    ('Rohan', 'IT'),
    ('Anika', 'HR'),
    ('Vikram', 'Finance')
)
SELECT DISTINCT department
FROM employees;

SQL String Values

Text values in SQL are normally enclosed in single quotation marks. Numeric values do not require quotation marks.

WITH students(name, age) AS (
  VALUES
    ('Aman', 21),
    ('Neha', 22),
    ('Rahul', 20)
)
SELECT name, age
FROM students
WHERE name = 'Neha';

SQL Comparison Operators

SQL uses comparison operators such as =, >, <, >=, <=, and <> to compare values.

WITH products(name, price) AS (
  VALUES
    ('Mouse', 400),
    ('Keyboard', 800),
    ('Monitor', 1500),
    ('Laptop', 60000)
)
SELECT name, price
FROM products
WHERE price > 700;

Using AND

The AND operator requires all specified conditions to be true.

WITH employees(name, department, salary) AS (
  VALUES
    ('Aarav', 'IT', 65000),
    ('Meera', 'Finance', 72000),
    ('Rohan', 'IT', 55000),
    ('Anika', 'HR', 60000)
)
SELECT name, department, salary
FROM employees
WHERE department = 'IT'
  AND salary > 60000;

Using OR

The OR operator returns rows when at least one of the specified conditions is true.

WITH products(name, category) AS (
  VALUES
    ('Keyboard', 'Computer'),
    ('Mouse', 'Computer'),
    ('Notebook', 'Stationery'),
    ('Pen', 'Stationery')
)
SELECT name, category
FROM products
WHERE category = 'Computer'
   OR category = 'Stationery';

Using IN

The IN operator checks whether a value matches any value in a specified list.

WITH employees(name, department) AS (
  VALUES
    ('Aarav', 'IT'),
    ('Meera', 'Finance'),
    ('Rohan', 'Sales'),
    ('Anika', 'HR')
)
SELECT name, department
FROM employees
WHERE department IN ('IT', 'Finance');

Using BETWEEN

The BETWEEN operator checks whether a value falls within an inclusive range.

WITH products(name, price) AS (
  VALUES
    ('Mouse', 400),
    ('Keyboard', 800),
    ('Monitor', 1500),
    ('Speaker', 2500)
)
SELECT name, price
FROM products
WHERE price BETWEEN 500 AND 1600;

Using LIKE

The LIKE operator is used to search for text patterns. The percent sign (%) represents zero or more characters.

WITH students(name) AS (
  VALUES
    ('Aman'),
    ('Anika'),
    ('Rahul'),
    ('Meera')
)
SELECT name
FROM students
WHERE name LIKE 'A%';

Using IS NULL

IS NULL is used to find rows where a column contains a NULL value.

WITH employees(name, manager) AS (
  VALUES
    ('Aarav', 'Ravi'),
    ('Meera', NULL),
    ('Rohan', 'Priya')
)
SELECT name, manager
FROM employees
WHERE manager IS NULL;

Using Column Aliases

The AS keyword can create a temporary alias for a column or calculated expression.

WITH products(name, price, quantity) AS (
  VALUES
    ('Keyboard', 800, 2),
    ('Mouse', 400, 3)
)
SELECT name AS product_name,
       price * quantity AS total_price
FROM products;

Comments in SQL

SQL supports comments that can be used to document queries. A single-line comment starts with two hyphens (--).

WITH products(name, price) AS (
  VALUES
    ('Keyboard', 800),
    ('Mouse', 400),
    ('Monitor', 1500)
)
-- Select products costing more than 500
SELECT name, price
FROM products
WHERE price > 500;

Common SQL Clauses

General SELECT Query Structure

A common SQL query can contain several clauses. Not every query requires every clause.

WITH employees(name, department, salary) AS (
  VALUES
    ('Aarav', 'IT', 65000),
    ('Meera', 'Finance', 72000),
    ('Rohan', 'IT', 58000),
    ('Anika', 'HR', 62000)
)
SELECT department,
       COUNT(*) AS employee_count,
       AVG(salary) AS average_salary
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 0
ORDER BY average_salary DESC
LIMIT 10;

Important SQL Syntax Rules

• SQL keywords are generally written in uppercase for readability, although SQLite is case-insensitive for keywords.
• Column and table names should be written consistently.
• Separate multiple column names with commas.
• Use single quotes for SQL string literals.
• Use parentheses when needed to control the order of expressions.
• Use a semicolon to terminate SQL statements.
• Use comments to document complicated queries.

Advantages of Following Proper SQL Syntax

• Makes queries easier to read.
• Reduces syntax errors.
• Makes complex queries easier to maintain.
• Improves consistency between SQL queries.
• Helps developers understand the purpose of each clause.

Best Practices

• Write SQL keywords consistently in uppercase.
• Use indentation for complex queries.
• Use meaningful table and column names.
• Use aliases when they make a query easier to understand.
• Avoid unnecessary complexity in SQL statements.
• Test queries with small datasets before using them on large datasets.