SQL Basic Syntax

This document provides a comprehensive overview of the fundamental syntax elements used in SQL (Structured Query Language). Understanding these basics is crucial for anyone working with relational databases.

Core SQL Statements

SQL is a declarative language, meaning you tell the database what you want, not necessarily how to get it. The most common SQL statements can be categorized as follows:

Data Query Language (DQL)

Used for retrieving data from the database.

SELECT: Retrieves data from one or more tables.

Data Manipulation Language (DML)

Used for managing data within schema objects.

INSERT: Adds new records to a table.
UPDATE: Modifies existing records in a table.
DELETE: Removes records from a table.

Data Definition Language (DDL)

Used for defining database structure or schema.

CREATE: Creates database objects like tables, indexes, views, stored procedures, functions, and triggers.
ALTER: Modifies database objects.
DROP: Deletes database objects.

Data Control Language (DCL)

Deals with rights, permissions, and other controls on the database system.

GRANT: Allows database users the access over database.
REVOKE: Takes back authorization from the database users.

Key Syntax Elements

SELECT Statement

The SELECT statement is used to query the database and retrieve a set of records from one or more tables.

Basic SELECT Example:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

SELECT column1, column2, ...: Specifies the columns you want to retrieve. Use * to select all columns.
FROM table_name: Specifies the table from which to retrieve the data.
WHERE condition: (Optional) Filters records based on a specified condition.

INSERT Statement

The INSERT statement is used to add new rows of data into a table.

Basic INSERT Example:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Note: The order of values must match the order of columns specified. If you are inserting values for all columns, you can omit the column list.

UPDATE Statement

The UPDATE statement is used to modify existing records in a table.

Basic UPDATE Example:

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

Tip: Always use a WHERE clause with UPDATE. Without it, all rows in the table will be updated.

DELETE Statement

The DELETE statement is used to remove rows from a table.

Basic DELETE Example:

DELETE FROM table_name
WHERE condition;

Tip: Similar to UPDATE, be cautious with the DELETE statement. Without a WHERE clause, all rows will be deleted. To delete all rows without logging the individual row deletions, use TRUNCATE TABLE table_name; (this is a DDL statement).

CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in the database.

Basic CREATE TABLE Example:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints,
    ...
);

datatype specifies the type of data a column can hold (e.g., INT, VARCHAR(255), DATE). constraints define rules for the data in a column (e.g., PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY).

ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table, or to add or drop constraints.

Examples of ALTER TABLE:

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

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

-- Modify a column's datatype
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;

DROP TABLE Statement

The DROP TABLE statement is used to permanently delete a table from the database.

Basic DROP TABLE Example:

DROP TABLE table_name;

Note: This action cannot be undone.

Common SQL Clauses

ORDER BY: Sorts the result set.
GROUP BY: Groups rows that have the same values in specified columns into summary rows.
HAVING: Filters groups based on a specified condition (used with GROUP BY).
JOIN: Combines rows from two or more tables based on a related column.

Data Types

SQL supports a wide range of data types to store different kinds of information. Some common ones include:

Data Type	Description
`INT` / `INTEGER`	Whole numbers.
`DECIMAL` / `NUMERIC`	Exact numeric values with a fixed precision and scale.
`FLOAT` / `REAL`	Approximate floating-point numeric values.
`VARCHAR(n)`	Variable-length character strings, up to `n` characters.
`CHAR(n)`	Fixed-length character strings, `n` characters long.
`TEXT`	Large character strings.
`DATE`	Stores dates (year, month, day).
`TIME`	Stores time (hour, minute, second).
`DATETIME` / `TIMESTAMP`	Stores both date and time.
`BOOLEAN`	Stores TRUE or FALSE values.

← Previous: Getting Started Next: Data Types →