Unit

SQL Commands and Syntax

Learn SQL command categories, syntax patterns, table design, queries, joins, transactions, permissions, subqueries, and safe database practices.

SQL (Structured Query Language) is the language used to communicate with relational database systems. You use SQL to create database structures, store and change data, retrieve information, control access, and manage transactions.

A database management system (DBMS) is software that stores and manages databases. A database server is the running service that accepts client connections and executes SQL. A database is a logical collection of related data. Within it, a schema groups objects such as tables, views, and indexes.

A table stores data in columns and rows. A column, also called a field, describes one attribute, such as price. A row, also called a record, represents one item or event. A query is a request for data or an operation expressed as a SQL statement. SQL statements commonly end with a semicolon.

MySQL, PostgreSQL, SQL Server, Oracle Database, and SQLite all support core SQL, but their exact syntax differs. Treat examples in this lesson as standards-oriented unless a product-specific variation is identified.

SQL command categories

SQL commands are commonly grouped by purpose. These categories are useful for learning and for understanding permissions, although sources do not always classify commands identically. In particular, some sources place SELECT under DML, while others give it a separate DQL category.

SQL syntax conventions

Building blocks of a statement

• Keywords are reserved or special words such as SELECT, FROM, and WHERE.
• Identifiers name objects such as databases, tables, columns, indexes, and roles.
• Literals are values written directly in a statement, such as 42 or 'Keyboard'.
• An expression produces a value, for example price * quantity or stock_quantity > 0.
• A clause is a logical section, such as FROM, WHERE, or ORDER BY.
• Operators compare or combine values, including =, <>, AND, and OR.
• Functions perform operations such as COUNT(), LOWER(), or CURRENT_TIMESTAMP.
• Commas separate columns or values, and parentheses group expressions, function arguments, and column definitions.

Keywords are generally case-insensitive, so select and SELECT usually mean the same thing. Identifier behavior depends on the DBMS and quoting rules. Use simple, consistent names such as order_items; avoid spaces, punctuation, and reserved words. Quoted identifiers are needed when an object name contains special characters, uses mixed case that a product preserves, or conflicts with a keyword. Quoting rules vary, commonly using double quotes or backticks.

Use single quotes for text values: 'active'. Numeric values are normally unquoted: 19.99. Date and time literal syntax varies, so use the target DBMS's documented format or parameters. NULL means missing, unknown, or not applicable; it is not zero, an empty string, or an ordinary value. Test it with IS NULL or IS NOT NULL, never = NULL.

Many systems support -- for a single-line comment and /* ... */ for a block comment. Keep statements readable with indentation, one clause per line, explicit column names, and a semicolon terminator.

SELECT p.product_name, p.price
FROM products AS p
WHERE p.stock_quantity > 0
  AND p.price < 50
ORDER BY p.price ASC;

Creating and changing structures with DDL

Databases and tables

Some DBMSs allow a server administrator to create a database with CREATE DATABASE. Others create a database file or database during setup. Selecting a database may use a client command, a connection option, or a product-specific SQL statement.

CREATE DATABASE store_db;

After connecting to the chosen database, define a table with column names, data types, and constraints.

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name VARCHAR(120) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Generated identifiers may use identity columns, sequences, serial types, or auto-increment features. The exact syntax is vendor-specific. Use the mechanism documented for your DBMS rather than assuming that one product's syntax works everywhere.

Changing and removing objects

ALTER TABLE products ADD COLUMN discontinued BOOLEAN NOT NULL DEFAULT FALSE;

CREATE INDEX idx_products_name ON products (product_name);
DROP INDEX idx_products_name;

CREATE VIEW available_products AS
SELECT product_id, product_name, price
FROM products
WHERE stock_quantity > 0;

DROP VIEW available_products;
DROP TABLE products;
DROP DATABASE store_db;

ALTER TABLE can add, rename, modify, or remove columns, but each operation has product-specific forms and may fail if existing data violates the new definition. DROP TABLE removes both rows and the table definition. DROP DATABASE removes the database and its objects. These are destructive operations: verify the target, back up important data, and check dependencies first.

An index is an auxiliary structure that can make searches, joins, and ordering faster. Indexes also consume storage and can slow inserts and updates, so create them for justified access patterns and inspect execution plans for slow queries. A view is a named query that presents data like a virtual table; it normally stores the query definition rather than a separate copy of the rows.

Data types and constraints

• Numeric: integer types suit counts and many identifiers; decimal or numeric types suit exact values such as prices; floating-point types suit approximate measurements.
• Character: CHAR is fixed-length, while VARCHAR is variable-length. Some systems also provide large text types.
• Date and time: use date, time, timestamp, and sometimes time-zone-aware types according to the application.
• Boolean: represents true or false where supported; some systems implement it with another numeric type.
• Binary: stores bytes, files, hashes, or other non-text data.

Choose types that represent the domain accurately. Use an integer or generated key for an ID, a character type for a name, a decimal type for a price, a timestamp for creation time, and a Boolean for a flag. Avoid relying on automatic conversion between unrelated types.

• NOT NULL requires a value.
• UNIQUE prevents duplicate values in a column or column combination.
• PRIMARY KEY uniquely identifies each row and cannot be null.
• FOREIGN KEY refers to a key in another table.
• CHECK enforces a condition, such as a nonnegative price.
• DEFAULT supplies a value when an insert omits the column.

Referential integrity means that a foreign-key value must refer to an existing parent key, unless the relationship is intentionally null. For example, order_items.order_id can reference orders.order_id. This protects relationships from pointing to nonexistent rows. See Primary Keys for a focused lesson on key design.

Retrieving data with SELECT

Choosing and filtering rows

SELECT product_id, product_name AS name, price
FROM products
WHERE stock_quantity > 0
  AND (price BETWEEN 10 AND 50 OR product_name LIKE '%USB%')
ORDER BY price ASC
LIMIT 10;

SELECT chooses expressions or columns, and FROM identifies the source. AS creates an alias for a column or table. SELECT * returns every column, but explicit columns are safer and clearer in production because schemas change and unnecessary data increases cost.

DISTINCT removes duplicate result rows. Filtering uses comparison operators such as =, <>, >, and <=; logical operators AND, OR, and NOT; parentheses for intended precedence; IN for a list; BETWEEN for a range; LIKE for patterns; IS NULL for missing values; and EXISTS to test whether a subquery returns at least one row.

ORDER BY sorts results. Without it, row order is not guaranteed. Row limiting differs by product: use LIMIT, FETCH FIRST ... ROWS ONLY, or TOP according to the selected DBMS.

Calculations, grouping, and functions

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(total_amount) AS total_spent,
       AVG(total_amount) AS average_order,
       MIN(total_amount) AS smallest_order,
       MAX(total_amount) AS largest_order
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 500;

COUNT, SUM, AVG, MIN, and MAX are aggregate functions. GROUP BY forms groups before aggregation. WHERE filters individual rows before grouping; HAVING filters groups after aggregation. A selected nonaggregate column generally must appear in GROUP BY.

SELECT product_name,
       price * stock_quantity AS inventory_value,
       CASE
           WHEN stock_quantity = 0 THEN 'out of stock'
           WHEN stock_quantity < 10 THEN 'low stock'
           ELSE 'available'
       END AS stock_status
FROM products;

Calculated expressions can combine columns and operators. Common scalar functions transform one row at a time, such as text case or length functions, rounding functions, and date functions. Function names and date behavior vary between products.

Combining data from tables

Relational designs split information into related tables to reduce duplication. A join brings related rows together using matching keys. For example, customers, orders, products, and order items can be connected through primary-key and foreign-key columns.

SELECT c.customer_id, c.customer_name, o.order_id, o.total_amount
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id;

Outer joins preserve unmatched rows and fill columns from the missing side with NULL. A join is not the same as UNION: joins add columns side by side based on relationships, while UNION stacks compatible result sets vertically. UNION removes duplicates; UNION ALL retains them. A FULL OUTER JOIN is not supported directly by every DBMS.

Inserting, updating, and deleting rows

INSERT INTO products (product_name, price, stock_quantity)
VALUES ('USB Cable', 9.99, 25);

INSERT INTO products (product_name, price)
VALUES ('Keyboard', 39.99),
       ('Mouse', 24.99);

INSERT INTO archived_products (product_id, product_name)
SELECT product_id, product_name
FROM products
WHERE discontinued = TRUE;

Always provide an explicit column list. Omitted columns receive their defaults or null when allowed. INSERT ... SELECT inserts the result of a query and requires compatible column counts and data types.

UPDATE products
SET stock_quantity = stock_quantity + 10
WHERE product_id = 7;

DELETE FROM products
WHERE discontinued = TRUE;

An UPDATE or DELETE without a sufficiently precise WHERE clause can affect every row. First run an equivalent SELECT, inspect the rows, execute the change in a transaction, and verify the affected-row count.

Transactions and consistency

A transaction groups operations into one unit. Atomicity means that the group is committed as a whole or undone as a whole, which protects multi-step changes such as reducing inventory and recording a sale.

BEGIN;

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 7
  AND stock_quantity > 0;

-- Inspect the affected row count and related results.
COMMIT;
-- Use ROLLBACK instead if the result is not correct.

Equivalent start commands include START TRANSACTION. SAVEPOINT creates a point to which part of a transaction can be rolled back where supported.

BEGIN;
SAVEPOINT before_correction;
-- perform a change
ROLLBACK TO SAVEPOINT before_correction;
COMMIT;

Autocommit behavior differs between DBMSs and client tools. Know whether each statement commits automatically, and use explicit transactions for important changes.

Permissions and database security

Users and roles receive privileges such as permission to select, insert, update, or delete rows. Least privilege means granting only the access required for a job. A reporting role might read data without being able to alter tables.

GRANT SELECT ON products TO reporting_role;
REVOKE SELECT ON products FROM reporting_role;

Real syntax for roles, schemas, databases, and object privileges varies. Schema-management permissions such as creating or dropping objects should be restricted to trusted administrative roles.

Subqueries and common query patterns

A subquery is a query nested inside another statement. A scalar subquery returns one value; a single-row subquery returns one row; a multi-row subquery returns several rows; and a correlated subquery refers to a row from the outer query and may run conceptually once per outer row.

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.order_date >= '2026-01-01'
);

Use IN when comparing a value with a set, EXISTS when only existence matters, and scalar comparison when exactly one value is expected. A derived table is a subquery in FROM.

WITH customer_totals AS (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT c.customer_name, ct.total_spent
FROM customers AS c
JOIN customer_totals AS ct
  ON ct.customer_id = c.customer_id
WHERE ct.total_spent > 500;

A common table expression (CTE), introduced with WITH, gives a name to an intermediate result and can make multi-step queries easier to read. Choose a join when expressing a direct relationship, a subquery when testing a focused condition, and a CTE when decomposing a complex query into named steps. Compare execution plans when performance matters.

Core statement patterns

Vendor syntax differences

Core clauses are portable, but edge syntax is not. Identify the DBMS and version before copying a command, then consult that product's documentation for exact syntax, transaction behavior, supported joins, date functions, generated keys, and quoting rules. Modify A Table, Create An Index, and Mysql Date Functions provide focused follow-up material.

Error prevention and troubleshooting

• Use consistent naming, indentation, and explicit column lists.
• Run a SELECT with the same WHERE condition before an UPDATE or DELETE.
• Use transactions for important changes and verify affected-row counts before committing.
• Back up or export data before destructive operations.
• Avoid implicit type conversions; use matching types and deliberate conversions.
• Create indexes thoughtfully and inspect execution plans for slow queries.

No rows returned

Inspect the source table with a simpler query, then add conditions incrementally. Check for unexpected NULL values, case or whitespace differences, incorrect data types, and joins that remove unmatched rows. Use IS NULL rather than = NULL.

Every row was updated or deleted

If the transaction is still open, issue ROLLBACK immediately. Otherwise, recovery may require a backup. Prevent recurrence by previewing with SELECT, using a transaction, checking the row count, and committing only after inspection.

Constraint violation during INSERT

Read the DBMS error, inspect the table definition, and check for duplicate keys, missing NOT NULL values, invalid foreign-key references, failed CHECK conditions, length errors, or incorrect data types.

Unexpected duplicate-looking join rows

This often reflects a one-to-many or many-to-many relationship, an incomplete join predicate, or a nonunique join column. Confirm cardinality and keys, review every join condition, and aggregate when a summary is intended. Do not use DISTINCT merely to hide an incorrect relationship.

Slow query

Inspect the execution plan. Look for large scans, unsuitable indexes, unnecessary columns, functions or implicit conversions on filtered columns, and inefficient joins or subqueries. Test a rewrite while confirming that results remain correct.

Syntax works in one DBMS but not another

Identify the target product and version, consult its syntax reference, and replace dialect-specific row limits, date functions, generated-key syntax, quoting, or ALTER TABLE forms with the appropriate equivalent. Advanced Select Statements is a useful next step for query composition.

Practical workflow

1. Connect to the intended database and confirm the schema.
2. For structure, design columns, types, keys, and constraints before running DDL.
3. For reads, begin with explicit columns and a simple SELECT, then add filters, joins, grouping, and ordering.
4. For changes, preview target rows, begin a transaction, execute the DML, verify the result and affected-row count, then COMMIT or ROLLBACK.
5. For access, grant the smallest required privilege to a role rather than giving every user broad administrative access.
6. For performance, measure with an execution plan before adding or changing indexes.

These habits make SQL statements easier to understand, safer to run, and more portable across relational database systems.