SQL online course

SQL Syntax and Common SQL Commands

Learn SQL statement structure and common commands for querying data, changing rows, creating tables and databases, managing indexes, and working safely.

SQL (Structured Query Language) is a language for defining, querying, and modifying data in relational database systems. This lesson introduces the structure of SQL statements and the commands used most often with databases, tables, rows, and indexes.

SQL syntax is broadly standardized, but exact data types, clauses, identifier rules, privilege requirements, and transaction behavior vary among systems such as PostgreSQL, MySQL, SQL Server, SQLite, and Oracle.

Relational Database Tables

A relational database stores data in related tables. A table is a structured collection of records organized into rows and columns. Each table usually represents one type of entity, such as a customer, product, employee, or order.

• A row, also called a record, represents one instance of the entity.
• A column represents a named attribute, or field, stored for each record.
• A table's column definitions describe what kind of values are allowed.

In this customers table, each row is one customer. The labels id, name, and city are columns. The id column is commonly the primary key: a column, or group of columns, that uniquely identifies every row. Other columns provide descriptive information.

Keys and Relationships

Tables can be related through keys. A foreign key is a column or set of columns that refers to a key in another table. For example, an orders.customer_id column can refer to customers.id.

The referenced table is the parent table; the table containing the reference is the child table. A relationship or integrity constraint helps ensure that an order does not refer to a customer that does not exist.

Tables are generally independent: changing a value in customers ordinarily changes only that table. However, relationships can restrict or extend the effect of a change. Deleting a customer that has orders may be blocked, or configured actions may delete or update dependent rows. Always understand the foreign-key rules before changing a referenced parent row.

SQL Statement Structure

An SQL statement combines keywords, identifiers, values, expressions, and optional clauses. A keyword is a reserved word with special meaning, such as SELECT, FROM, or WHERE. An identifier is the name of a database object, such as a database, table, column, or index.

Whitespace and line breaks normally do not change the meaning of a statement. SQL is conventionally written with keywords in uppercase and identifiers in lowercase, which improves readability. In many systems keyword case is not significant, but identifier case rules can differ by database product, operating system, quoting style, and configuration.

SELECT id, name, city
FROM customers
WHERE state = 'CA'
ORDER BY name;

The semicolon is a typical statement terminator. Some database clients require it, while others execute a statement without it or use it to separate multiple statements.

SELECT: Retrieving Data

SELECT is the principal command for retrieving rows and columns. Use FROM to specify the source table.

SELECT id, name, city FROM customers;

To return every column, use an asterisk:

SELECT * FROM customers;

WHERE restricts the returned rows:

SELECT id, name
FROM customers
WHERE state = 'CA';

ORDER BY sorts results, and many systems provide a limiting clause such as LIMIT or an equivalent:

SELECT id, name, city
FROM customers
ORDER BY name
LIMIT 10;

In production-oriented queries, select the columns you need instead of relying on SELECT *. Explicit columns make the result clearer, reduce unnecessary data transfer, and avoid unexpected results when a table gains new columns.

For more practice, see the SQL SELECT statement, the SQL WHERE clause, the SQL ORDER BY clause, and SQL SELECT LIMIT.

INSERT, UPDATE, and DELETE

These commands modify table data and are commonly grouped as data manipulation language (DML).

INSERT INTO

INSERT INTO adds new rows. Name the destination columns explicitly so that each value has a clear target.

INSERT INTO customers
    (id, name, address, city, state, postal_code)
VALUES
    (4, 'Example Customer', '100 Market Street',
     'Exampletown', 'CA', '90210');

The number and order of values must match the listed columns. In many systems the primary key can be generated automatically, but the syntax for that behavior differs.

UPDATE

UPDATE changes values in existing rows. The WHERE clause is essential when only particular rows should change.

UPDATE customers
SET address = '200 New Street'
WHERE id = 4;

Without WHERE, the statement can change the selected column in every row.

DELETE

DELETE removes rows from a table.

DELETE FROM customers
WHERE id = 4;

Without WHERE, every row in the table can be deleted. Before an update or delete, preview the target rows with an equivalent SELECT:

SELECT id, name, address
FROM customers
WHERE id = 4;

When supported, use a transaction to treat related operations as one unit of work. A transaction can usually be completed with COMMIT or undone with ROLLBACK. Check whether the client uses auto-commit, because an automatically committed change may not be reversible through rollback.

Detailed command references are available for INSERT INTO, UPDATE, and DELETE.

CREATE DATABASE and ALTER DATABASE

CREATE DATABASE creates a database container or catalog, depending on the DBMS.

CREATE DATABASE training;

ALTER DATABASE changes database-level properties, such as settings or configuration supported by the product.

ALTER DATABASE training;

The second example is intentionally incomplete because valid options differ substantially among database systems. Database creation and alteration often require administrative privileges. These are database-level operations, not table-level operations: use CREATE TABLE and ALTER TABLE to work with tables inside a database.

See the SQL CREATE DATABASE statement reference for product-specific details.

Table Definition Commands

CREATE TABLE

CREATE TABLE defines a new table, including its columns, data types, primary key, nullability, and other constraints.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(200),
    city VARCHAR(100),
    state VARCHAR(50),
    postal_code VARCHAR(20)
);

INTEGER and VARCHAR are example data types. PRIMARY KEY requires unique row identification, while NOT NULL prevents a value from being absent. Other constraints can enforce uniqueness, valid values, and foreign-key relationships.

Data types, automatic key generation, and constraint syntax vary by DBMS. Read more about SQL CREATE TABLE and SQL constraints.

ALTER TABLE

ALTER TABLE changes an existing table definition. A common operation is adding a column:

ALTER TABLE customers
ADD COLUMN email VARCHAR(255);

Depending on the DBMS, ALTER TABLE can also add, change, or remove columns and constraints. Structural changes may lock a table, rewrite data, or fail when existing rows do not satisfy the new definition. Test migrations and check the target product's syntax.

See SQL ALTER TABLE for more examples.

DROP TABLE

DROP TABLE removes the table definition and its stored rows.

DROP TABLE customers;

Dropping a table is destructive. It can also fail because other objects depend on it, or it can trigger configured dependency actions. Confirm backups, retention requirements, foreign keys, views, applications, and migration plans before executing it.

Indexes

An index is a database structure that can make lookups, filters, joins, or ordering faster. For example, an index may help queries that frequently search by city:

CREATE INDEX idx_customers_city
ON customers (city);

Remove an index with:

DROP INDEX idx_customers_city;

Indexes have costs. They require storage and may make INSERT, UPDATE, and DELETE operations more expensive because the index must be maintained. An index is not automatically useful for every query: the optimizer may ignore it when the table is small, the predicate does not use the indexed column, or another plan is cheaper. Use query plans and workload measurements when evaluating indexes.

Index creation and removal syntax varies; some systems require the table name when dropping an index. See the SQL DROP statement reference for related removal operations.

Common SQL Commands by Purpose

DQL means data query language and commonly refers to retrieval commands such as SELECT. DML covers data changes. DDL defines or changes database objects. Some references also classify transaction and permission commands separately.

Safety Checklist

Troubleshooting SQL Commands

• Too many rows changed: The WHERE clause was omitted or too broad. Run the equivalent SELECT first and use a transaction when available.
• A parent row cannot be deleted: Child rows probably reference it through a foreign key. Review dependent rows and the configured referential action before reassigning or removing anything.
• A table statement has a syntax error: The data type, clause, or identifier quoting may belong to another SQL dialect. Check the documentation for the connected DBMS.
• An object already exists or does not exist: Verify the active database, schema, and object name. Conditional creation or removal syntax may be available.
• An index provides no visible improvement: Inspect the query plan. The table may be small, the predicate may not use the indexed column, or another plan may be more efficient.
• A valid-looking command is rejected: The account may lack the required database, schema, table, or index privilege. Request only the permissions needed.

Key Points

• Relational data is organized into tables containing rows and columns.
• Primary keys identify rows; foreign keys connect child tables to parent tables.
• SQL statements use keywords, identifiers, values, expressions, clauses, and usually a semicolon terminator.
• SELECT reads data; INSERT INTO, UPDATE, and DELETE modify rows.
• CREATE, ALTER, and DROP define or remove database objects.
• Indexes can improve reads but add storage and write-maintenance costs.
• Exact syntax, privileges, supported features, and auto-commit behavior depend on the SQL implementation.