SQL Syntax - Standard Language Elements

This document provides a comprehensive guide to the standard syntax and structure of SQL (Structured Query Language) statements. Understanding SQL syntax is fundamental for interacting with relational databases.

Basic Statement Structure

Most SQL statements follow a common pattern, typically consisting of keywords, identifiers, and clauses. The general structure of many SQL statements can be represented as:

[CLAUSE1]
[CLAUSE2]
...
[CLAUSE N];

Keywords

Keywords are reserved words that have a special meaning to the SQL parser. They cannot be used as identifiers (table names, column names, etc.) unless properly quoted, if supported by the specific SQL dialect.

Common SQL keywords include:

• SELECT, INSERT, UPDATE, DELETE
• FROM, WHERE, GROUP BY, HAVING, ORDER BY
• CREATE, ALTER, DROP
• TABLE, INDEX, VIEW
• JOIN (INNER, LEFT, RIGHT, FULL)
• AND, OR, NOT, IN, LIKE
• NULL, IS

Identifiers

Identifiers are names given to database objects such as tables, columns, views, indexes, schemas, and stored procedures. Identifiers must generally follow these rules:

• Start with a letter.
• Can contain letters, numbers, and underscores.
• Cannot be SQL keywords.
• Some database systems allow quoted identifiers (e.g., "My Table") to include spaces or special characters, but this is not standard ANSI SQL.

Clauses

Clauses are segments of an SQL statement that perform specific actions or define conditions.

SELECT Clause

Specifies the columns to retrieve from a table.

SELECT column1, column2, ...
FROM table_name;

You can use * to select all columns:

SELECT *
FROM table_name;

FROM Clause

Specifies the table(s) from which to retrieve data.

SELECT column_list
FROM table1
[JOIN table2 ON join_condition];

WHERE Clause

Filters records based on specified conditions.

SELECT column_list
FROM table_name
WHERE condition;

Example conditions:

• age > 30
• city = 'New York'
• name LIKE 'A%'
• id IN (101, 105, 200)

GROUP BY Clause

Groups rows that have the same values in specified columns into summary rows.

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;

HAVING Clause

Filters groups based on specified conditions. It's like WHERE, but for groups.

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > 5;

ORDER BY Clause

Sorts the result set in ascending or descending order.

SELECT column_list
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

Data Manipulation Language (DML) Statements

INSERT Statement

Adds new records to a table.

-- Inserting specific values
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

-- Inserting all values (order must match table schema)
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

UPDATE Statement

Modifies existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

DELETE Statement

Deletes records from a table.

DELETE FROM table_name
WHERE condition;

Data Definition Language (DDL) Statements

CREATE TABLE Statement

Creates a new table in the database.

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    PRIMARY KEY (column_name)
);

ALTER TABLE Statement

Modifies an existing table (e.g., adds, deletes, or modifies columns).

-- Add a column
ALTER TABLE table_name
ADD column_name datatype constraints;

-- Drop a column
ALTER TABLE table_name
DROP COLUMN column_name;

DROP TABLE Statement

Deletes an existing table and all its data.

DROP TABLE table_name;

Common Operators

SQL supports various operators for comparisons, logical operations, and arithmetic.

Comparison Operators

Operator	Description
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<> or !=	Not equal to

Logical Operators

Operator	Description
AND	Combines two conditions; returns true if both are true.
OR	Combines two conditions; returns true if at least one is true.
NOT	Reverses the logical state of its operand.

Pattern Matching Operator

Operator	Description
LIKE	Searches for a specified pattern in a column. %: Represents zero, one, or multiple characters. _: Represents a single character.