VMware ESXi and vSphere Cluster Management

SQL Syntax and Common SQL Commands

Learn foundational SQL syntax, relational tables, SELECT, INSERT, UPDATE, DELETE, DDL commands, indexes, relationships, and safe database practices.

SQL, or Structured Query Language, is used to define, read, and modify data and objects in relational databases. A relational database organizes information into related tables. SQL statements combine reserved words, object names, values, expressions, operators, clauses, and punctuation.

SQL is standardized, but database products implement their own SQL dialects. For example, data types, row-limiting syntax, identifier quoting, and ALTER TABLE features can differ among PostgreSQL, MySQL, SQL Server, Oracle, and SQLite. Learn the general SQL pattern first, then check the documentation for the database you use.

For a broader introduction, see SQL syntax fundamentals.

Relational tables, rows, and columns

A table is a named structure that stores data in columns and rows. A row, also called a record, represents one stored entity instance. A column, also called a field in some contexts, represents an attribute recorded for every row.

A customer table might contain an id, name, address, city, state, and zip column. Each row supplies a value for those defined columns. The id column commonly acts as the primary key: a value, or group of values, that uniquely identifies each row.

Relationships between tables

Tables are separate database objects, but they can be related. In a parent-child relationship, the parent table contains the referenced rows, while the child table stores a foreign key that points to the parent key.

customers.id  <--  orders.customer_id

Here, one customer can have many orders. A foreign key helps prevent an order from referring to a customer that does not exist. It can also restrict updates or deletes: a database may reject deletion of a parent customer while child orders still reference it, unless an intentional referential action such as cascading is configured.

Changing table data is different from changing table structure. INSERT, UPDATE, and DELETE change rows. CREATE TABLE, ALTER TABLE, and DROP TABLE change the schema, which is the structure of database objects, columns, types, and constraints.

SQL statement structure

Most statements begin with a command keyword, followed by object names, column names, values, clauses, or conditions. A clause is a meaningful section such as FROM or WHERE.

SELECT column_list
FROM table_name
WHERE condition
ORDER BY column_name;

• SELECT list: the columns or expressions to return.
• FROM: the table or other source of data.
• WHERE: a filter that limits rows.
• SET: assignments used by UPDATE.
• VALUES: values supplied by INSERT.

A semicolon commonly terminates a statement and is required by some tools or execution contexts. Whitespace and line breaks usually do not change meaning, so formatting can make a statement easier to read. SQL systems commonly support comments such as -- comment and, in many dialects, block comments such as /* comment */; exact support can vary.

Keywords, identifiers, literals, and operators

Common keywords include SELECT, FROM, WHERE, INSERT, UPDATE, DELETE, CREATE, ALTER, and DROP. Identifiers name databases, tables, columns, indexes, and constraints.

Literals include numbers, quoted text, dates, times, and NULL. NULL means an unknown, missing, or inapplicable value; it is not the same as zero or an empty string. Text strings are commonly enclosed in single quotes, as in 'CA'. Identifier quoting uses dialect-specific rules, so avoid assuming that string and identifier quotes are interchangeable.

Comparison operators include =, >, <, >=, <=, and a dialect-supported not-equal operator such as <>. Logical operators include AND, OR, and NOT.

Case and formatting conventions

SQL keywords are commonly written in uppercase for readability, although keyword case is not significant in many systems. Identifier case handling varies, especially when identifiers are quoted. Use consistent indentation and descriptive, non-reserved names for tables, columns, indexes, and constraints.

Reading data with SELECT

SELECT is the primary command for reading data. Use * to request all columns, or name only the columns needed.

SELECT *
FROM customers;

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

WHERE limits returned rows, and ORDER BY sorts them. LIMIT is commonly used to restrict the number of rows, but SQL Server, Oracle, and other systems may use different or additional syntax. A normal SELECT reads data without changing it.

Adding rows with INSERT INTO

INSERT INTO creates new rows. Name destination columns explicitly so the statement does not depend on the table's implicit column order.

INSERT INTO customers (id, name, address, city, state, zip)
VALUES (4, 'Alex Jones', '456 Oak Avenue', 'Hope', 'CA', '98765');

You can insert multiple rows with multiple value lists, or insert the result of a query using an INSERT ... SELECT form supported by the dialect.

Changing rows with UPDATE

UPDATE modifies values in existing rows. SET specifies assignments, and WHERE identifies the target rows.

UPDATE customers
SET city = 'New Hope'
WHERE id = 1;

Removing rows with DELETE

DELETE removes rows while leaving the table definition in place.

DELETE FROM customers
WHERE id = 4;

Without WHERE, the statement can delete every row. A delete can also be blocked when a foreign key in a child table references the parent row. Identify dependent rows and choose an intentional action before deleting.

Creating tables with CREATE TABLE

CREATE TABLE is a data-definition language (DDL) command. It defines column names, data types, and constraints.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  address VARCHAR(150),
  city VARCHAR(100),
  state CHAR(2),
  zip VARCHAR(10)
);

• PRIMARY KEY uniquely identifies rows.
• NOT NULL requires a value.
• UNIQUE prevents duplicate values within its rule.
• DEFAULT supplies a value when one is omitted.
• FOREIGN KEY enforces a relationship to another table.

Exact data type names and constraint options vary by database product.

Relationships and foreign keys in table definitions

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  order_total DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

In this example, orders.customer_id references customers.id. The parent key must exist before a valid child row can refer to it.

Changing definitions with ALTER TABLE

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

ALTER TABLE customers
ADD COLUMN email VARCHAR(255);

Depending on the dialect, the command family may also add constraints, change a column definition, rename an object, or drop a column. Capabilities and syntax differ substantially. Schema changes can affect existing data, applications, indexes, constraints, and dependent queries, so test them before production use.

Removing tables with DROP TABLE

DROP TABLE removes both a table definition and its stored data. This differs from DELETE, which removes rows but leaves the table available.

DROP TABLE customers;

Creating and altering databases

Where supported, CREATE DATABASE creates a database container.

CREATE DATABASE sales;

ALTER DATABASE changes database-level settings, but available settings, permissions, and syntax are platform-specific.

ALTER DATABASE database_name ...;

Database creation and alteration often require administrative privileges. Do not assume that a command available in one product exists in another.

Indexes

An index is a data structure that can improve lookups, filtering, joins, or sorting for selected columns.

CREATE INDEX idx_customers_state
ON customers (state);

DROP INDEX idx_customers_state;

Indexes consume storage and can add work to INSERT, UPDATE, and DELETE operations. Create them for measured workloads rather than every column. A primary key commonly creates or uses an index, depending on the database system. The exact DROP INDEX syntax can vary.

SQL command categories

Transaction control commands such as COMMIT and ROLLBACK, and data control commands for permissions, are related areas for continued study.

Common commands reference

Safe SQL execution practices

• Run a SELECT using the same WHERE condition before an UPDATE or DELETE.
• Use transactions for multi-step changes when supported. Inspect results, then commit; roll back if validation fails.
• Back up important data and test destructive data or schema operations outside production.
• Use least-privilege permissions so each account can perform only its required tasks.
• Always list destination columns in INSERT statements rather than relying on implicit column ordering.

Troubleshooting common errors

An UPDATE changed every row

The statement may have omitted WHERE or used an overly broad condition. Run a matching SELECT first, inspect the rows, and use a transaction when available.

A DELETE is blocked

A child table may contain foreign-key rows referring to the target parent. Find the dependent rows and delete or reassign them only when appropriate, or use an intentional cascading rule.

An INSERT does not match the table

The number, order, type, or required presence of values may not match the definition. Inspect required constraints and data types. Explicit destination columns prevent many ordering errors.

A table or column name causes a syntax error

Check for misspellings, reserved keywords, and dialect-specific identifier quoting. Prefer clear names that do not conflict with SQL keywords.

A filtered query is slow

A suitable index may be missing, the query may return many rows, or the condition may prevent index use. Review the execution plan and table size before adding or changing indexes.

A CREATE, ALTER, or DROP command fails

Possible causes include insufficient permissions, an existing object, dependencies, or unsupported dialect syntax. Check the error, object state, dependencies, and product documentation.

Key exam notes

• SELECT reads data; INSERT, UPDATE, and DELETE change rows.
• CREATE, ALTER, and DROP define or change database objects.
• A primary key identifies a row; a foreign key connects a child row to a parent row.
• NULL is not zero and is not an empty string.
• Omitting WHERE from UPDATE or DELETE can affect every row.
• SQL keywords are often uppercase by convention, but identifier case rules depend on the database and quoting.
• SQL syntax is dialect-dependent, especially for data types, database-level commands, row limiting, and schema changes.