SQL Syntax
SQL syntax refers to the set of rules and
conventions used to construct valid SQL statements
for querying and manipulating databases.
It includes the specific keywords, operators, and
structure required to perform various operations
such as retrieving, inserting, updating, and deleting
data.
SQL syntax ensures that statements are correctly
formatted so that the database management
system can interpret and execute them accurately,
enforcing consistency and functionality in database
Key Aspects of SQL Syntax
Basic Structure Punctuation
Keywords Case Sensitivity
Clauses String Literals
Expressions Comments
Operators
Basic Structure
The basic structure of an SQL statement typically
begins with a keyword that specifies the type of
operation to be performed, such as SELECT,
INSERT, UPDATE, or DELETE.
This is followed by clauses that define the details
of the operation, such as FROM to specify the table,
WHERE to set conditions for filtering rows, and
ORDER BY to sort the results.
Basic Structure
Each clause is usually separated by whitespace
and punctuation, and the statement often ends
with a semicolon ( ; ) to indicate the conclusion
of the command.
This structured format ensures that SQL
commands are clear and executable by the
database management system.
Basic Structure
Example:
Keywords
In SQL, keywords are reserved words that define the
operations and structure of a query, such as SELECT,
INSERT, UPDATE, DELETE, FROM, WHERE, etc…
They serve as the building blocks for SQL statements,
guiding the database management system on how to
process the data.
Keywords must be used according to their predefined
roles and are case-insensitive in most SQL
implementations.
Clauses
Clauses in SQL are distinct components of a
statement that define specific aspects of the query,
such as SELECT for specifying which columns to
retrieve and FROM for indicating the table to query.
Each clause has a specific purpose and is typically
used in a particular order to form a complete SQL
statement.
Proper use of clauses ensures that SQL commands
are structured correctly and produce the desired
Expressions
Expressions in SQL are combinations of columns,
constants, operators, and functions that evaluate to
produce a value.
They are used within SQL statements to compute
values, filter results, and perform operations such as
arithmetic or string manipulation.
Expressions can be simple, like adding two numbers,
or complex, involving multiple operations and
functions.
Expressions
In this example, (price * quantity) is an expression that
calculates the total cost for each order by multiplying the
price and quantity columns. The result is aliased as
total_cost and used in the SELECT clause to display the
computed value.
Operators
Operators in SQL are symbols or keywords used to
perform operations on data within expressions.
They include arithmetic operators like + (addition)
and - (subtraction), comparison operators like =
(equal to) and < (less than), and logical operators like
AND and OR.
Operators help manipulate and compare values,
allowing for complex queries and data retrieval
conditions.
Arithmetic Operators
Operator Description
+ Addition
- Subtraction
* Multiplication
/ Division
% Modulus (remainder)
Comparison Operators
Operator Description
= Equal to
<> Not equal to
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
Logical Operators
Operator Description
Logical conjunction (both conditions must be
AND true)
Logical disjunction (at least one condition must
OR be true)
NOT Negates a condition
Pattern Matching Operators
Operator Description
LIKE Pattern matching for string comparisons
NOT LIKE Pattern does not match the specified pattern
Null Check Operators
Operator Description
IS NULL Checks if a value is NULL
IS NOT NULL Checks if a value is not NULL
Punctuation
In SQL, punctuation refers to symbols used to structure
and separate elements within SQL statements.
Common punctuation marks include commas (,), which
separate columns or values; semicolons (;), which
terminate SQL statements; parentheses (()), which group
expressions and parameters; and quotation marks ('),
which delimit string literals.
Proper use of punctuation is essential for ensuring that
SQL commands are interpreted correctly by the database
management system.
Case Sensitivity
Case sensitivity in SQL refers to whether the
database system distinguishes between uppercase
and lowercase letters in identifiers such as table
names, column names, and string literals.
Most SQL keywords are case-insensitive, meaning
SELECT is equivalent to select.
However, case sensitivity for identifiers can vary
depending on the database system and its
configuration, which can affect how queries are
interpreted and executed.
String Literals
String literals in SQL are sequences of characters
enclosed in single quotation marks (').
They represent textual data used in queries,
conditions, and commands, such as specifying search
criteria or defining values to be inserted into a
database.
String literals can include letters, numbers, and
special characters, and they must be correctly
formatted to ensure accurate interpretation by the
Comments
Comments in SQL are annotations within the code
that provide explanations or notes for the benefit of
the developer.
They are ignored during execution and do not affect
the performance of the SQL statements.
Comments can be single-line, denoted by --, or
multi-line, enclosed between /* and */, and are used
to clarify the purpose or functionality of code
segments.
Comments
D a t a Ty p e s
Data Types
Data types are classifications that specify the kind of
data a column in a database table can hold.
They define the nature of data, such as whether it is
numeric, text, date, or binary, and determine the
operations that can be performed on that data.
Choosing the correct data type is crucial for ensuring
data integrity, optimizing storage, and enabling
efficient data retrieval and manipulation.
Commonly Used
D a t a Ty p e s
String Data Types
Data Type Syntax Maximum Size Description
Where size is the number of characters to store.
CHAR(size) 255 chars Fixed-length strings. Space padded on the right to
equal size characters.
Where size is the number of characters to store.
VARCHAR(size) 255 chars Variable-length string.
The actual storage used will depend on the length
TINYTEXT 255 chars of the data stored.
The actual storage used will depend on the length
TEXT 65,535 chars of the data stored, plus a small amount of
overhead.
The actual storage used will depend on the length
16,777,215
MEDIUMTEXT chars
of the data stored, plus a small amount of
overhead.
The actual storage used will depend on the length
4,294,967,295
LONGTEXT chars
of the data stored, plus a small amount of
overhead.
String Data Types
Data Type Syntax Maximum Size Description
Where size is the number of binary characters to
BINARY(size) 255 chars store. Fixed-length strings. Space padded on the
right to equal size characters.
Where size is the number of characters to store.
VARBINARY(size) 255 chars Variable-length string.
Numeric Data Types
Data Type Syntax Remarks
Very small integer value that is equivalent to TINYINT(1).
BIT Signed values range from -128 to 127. Unsigned values ranges
from 0 to 255.
Very small integer value. Signed values range from -128 to
TINYINT 127. Unsigned values ranges from 0 to 255.
Small integer value. Signed values range from -32768 to
SMALLINT 32767. Unsigned values ranges from 0 to 65535.
Medium integer value. Signed values range from -8388608 to
MEDUIMINT 8388607. Unsigned values ranges from 0 to 16777215.
Standard integer value. Signed values range from -
INT 2147483648 to 2147483647. Unsigned values ranges from 0
to 4294967295.
Numeric Data Types
Data Type Syntax Remarks
Standard integer value. Signed values range from -
INTEGER 2147483648 to 2147483647. Unsigned values ranges from 0
to 4294967295.
Big integer value. Signed values range from -
BIGINT 9223372036854775808 to 9223372036854775807. Unsigned
values ranges from 0 to 18446744073709551615.
Unpacked fixed point number.
DECIMAL(m,d) m defaults to 10, if not specified.
d defaults to 0, if not specified.
NUMERIC(m,d) Equivalent to DECIMAL
Numeric Data Types
Data Type Syntax Remarks
FLOAT(m,d) Single precision floating point number.
DOUBLE(m,d) Double precision floating point number.
Treated as a boolean data type where a value of 0 is
BOOL considered to be FALSE and any other value is considered to
be TRUE.
Treated as a boolean data type where a value of 0 is
BOOLEAN considered to be FALSE and any other value is considered to
be TRUE.
Date / Time Data Types
Data Type
Remarks
Syntax
Values range from 1000-0-01 to 9999-12-31.
DATE Displayed as YYYY-MM-DD
Values range from 1000-0-01 00:00:00 to 9999-12-31
DATETIME 23:59:59 in UTC
Displayed as YYYY-MM-DD HH:MM:SS
Values range from 1000-0-01 00:00:00 to 9999-12-31
TIMESTAMP 23:59:59
Displayed as YYYY-MM-DD HH:MM:SS
Values range from -838:59:59 to 838:59:59
TIME Displayed as HH:MM:SS
Large Object Data Types
Data Type
Remarks
Syntax
TINYBLOB Maximum size of 255 bytes
Maximum size of 65,535 bytes.
BLOB(size) Where size is the number of characters to store.
MEDIUMBLOB Maximum size of 16,777,215 bytes
LONGBLOB Maximum size of 4,294,967,295 bytes
Understanding
Basic S Q L
Statements