SQL Commands

3.2.5.2. SQL Commands#

In the previous sections, we encountered some basic SQL usage. Here, we’ll formally categorize and explore the different types of SQL commands you’ll use when working with databases.

These categories are often referred to as SQL sub-languages, as each group serves a distinct purpose in interacting with the database.

Data Definition Language (DDL)#

DDL refers to the set of SQL commands used to define the structure of a database. These commands are typically used by database designers and deal with creating, modifying, or deleting schemas, tables, and relationships – but not the data itself.

Note

We will use SQLite for demonstration purposes. It’s a lightweight, file-based SQL database. We’ll use Python’s built-in sqlite3 package to run SQL queries directly from our code.

SQLite stores the database in a single file (e.g., database.db)
Within a .db file, you can create multiple tables
In our case, we’ll store everything inside a file named database.db

Learn more about it here.

CREATE TABLE#

Creating a table is the most fundamental DDL operation. It defines the table name, its columns, data types, and any constraints like NOT NULL or PRIMARY KEY.

Example:#

CREATE TABLE employee (
    Name TEXT NOT NULL,
    Age INTEGER,
    Department TEXT,
    Salary REAL
);

We can also include Primary Keys and Foreign Keys:

CREATE TABLE employee (
    emp_id INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Age INTEGER,
    Department TEXT,
    Salary REAL
);

CREATE TABLE departments (
    dept_id INTEGER PRIMARY KEY,
    dept_name TEXT NOT NULL
);

CREATE TABLE employees (
    emp_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    dept_id INTEGER,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

In this setup, dept_id in the employees table is a foreign key referencing the departments table, creating a one-to-many relationship.

Running the above in Python:#

import sqlite3

# Connect to SQLite database
conn = sqlite3.connect("database.db")
cursor = conn.cursor()

# Create the tables
cursor.execute("""
CREATE TABLE IF NOT EXISTS departments (
    dept_id INTEGER PRIMARY KEY,
    dept_name TEXT NOT NULL
);
""")

cursor.execute("""
CREATE TABLE IF NOT EXISTS employees (
    emp_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    dept_id INTEGER,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
""")

conn.commit()

print("Tables created!")

Tables created!

Note: Using IF NOT EXISTS in a CREATE TABLE statement allows us to avoid errors if the table already exists. This makes the command safe to run multiple times without causing a conflict.

ALTER TABLE#

If the structure of an existing table needs to be changed, such as adding or removing a column, you use the ALTER TABLE command.

Add a new column:#

ALTER TABLE employees ADD COLUMN hire_date TEXT;

Rename a column (SQLite ≥ 3.25.0):#

ALTER TABLE employees RENAME COLUMN hire_date TO joining_date;

Rename a table:#

ALTER TABLE employees RENAME TO staff;

Python Example:#

# Add a new column
cursor.execute("ALTER TABLE employees ADD COLUMN hire_date TEXT;")

conn.commit()

Note: SQLite has limited support for ALTER TABLE compared to other systems like PostgreSQL or MySQL (e.g., dropping columns is not supported directly).

DROP TABLE#

The DROP TABLE command is used to permanently delete a table and all its data from the database. This action is irreversible, so it should be used with caution.

SQL Example:#

DROP TABLE employees;

Python Example:#

# Create a temporary table for demonstration
cursor.execute("""
CREATE TABLE salary (
    salary_id INTEGER PRIMARY KEY,
    salary_amount REAL NOT NULL
);
""")

# Drop the table
cursor.execute("DROP TABLE IF EXISTS salary;")

conn.commit()

Note: Using IF EXISTS in a DROP TABLE statement allows us to avoid errors if the table does not exist. This makes the command safe to run multiple times without causing a conflict.

CREATE INDEX#

Creating an index improves the speed of data retrieval by allowing the database to look up rows quickly rather than scanning the entire table.

Example: Create an index on the name column of the employees table:

CREATE INDEX idx_employees_name
ON employees(name);

This creates an index named idx_employees_name that will accelerate queries filtering or sorting by name.

Viewing Indexes#

You can inspect which indexes exist on a table and see their details.

To list all indexes on a table:

PRAGMA index_list('employees');

To see details about the columns included in a specific index:

PRAGMA index_info('idx_employees_name');

DROP INDEX#

If you no longer need an index or want to reclaim disk space, you can drop it.

Example:

DROP INDEX idx_employees_name;

This permanently removes the index.

Example: Measuring Query Speed with and without an Index#

Below is a Python SQLite example demonstrating how indexes can significantly improve query performance.

In this example:

The first query without an index scans all 100,000 rows.
After creating the index, the same query uses the index to locate the row almost instantly.

import time

# Create an in-memory database for demonstration
conn_mem = sqlite3.connect(":memory:")
cursor_mem = conn_mem.cursor()

# Create a table
cursor_mem.execute("""
CREATE TABLE data (
    id INTEGER PRIMARY KEY,
    value TEXT
);
""")

# Insert 100,000 rows
cursor_mem.executemany(
    "INSERT INTO data (value) VALUES (?)",
    [("Record_" + str(i),) for i in range(100000)]
)
conn_mem.commit()

# Search for a record WITHOUT index
start_time = time.time()
cursor_mem.execute("SELECT * FROM data WHERE value = 'Record_99999';")
print("Without index:", cursor_mem.fetchone())
print("Time without index: {:.6f} seconds".format(time.time() - start_time))

# Create index on 'value' column
cursor_mem.execute("CREATE INDEX idx_data_value ON data(value);")
conn_mem.commit()

# Search for the same record WITH index
start_time = time.time()
cursor_mem.execute("SELECT * FROM data WHERE value = 'Record_99999';")
print("With index:", cursor_mem.fetchone())
print("Time with index: {:.6f} seconds".format(time.time() - start_time))

conn_mem.close()

Without index: (100000, 'Record_99999')
Time without index: 0.003875 seconds
With index: (100000, 'Record_99999')
Time with index: 0.000119 seconds

Note

While indexes greatly improve read performance, they consume extra disk space and can slow down INSERT and UPDATE operations because the index must be kept up to date.

These DDL operations - creating tables, modifying them, dropping them, and adding indexes - lay the groundwork for how data is structured and accessed efficiently.

Data Query Language (DQL)#

This sub-language family is responsible for fetching data from tables.

SELECT#

The SELECT command is used to retrieve data from a table. Some texts categorize it under “Data Query Language (DQL)” as it essentially does not manipulate data - only queries it.

Note

In some cases, data manipulation elements (such as filtering or aggregation) can be attached to a query, which is why commands like SELECT are sometimes also classified under Data Manipulation Language (DML). However, strictly speaking, since it primarily queries data without modifying it, it is categorized under DQL.

Get all columns:#

SELECT * FROM <TABLE_NAME>;

Get specific columns:#

SELECT column1, column2 FROM <TABLE_NAME>;

Example – Select from `employees`:#

SELECT * FROM employees;

Note

The SELECT command supports many additional operations. You can:

Filter data using the WHERE clause
Combine data from multiple tables using JOINs
Aggregate data using functions like SUM(), AVG(), COUNT(), etc.

We’ll cover these advanced features in the next section: SQL Advanced Queries.

Data Manipulation Language (DML)#

DML (Data Manipulation Language) is used to insert, update, and delete data from the tables defined using DDL commands.

INSERT#

The INSERT command is used to add new rows (records) into a table.

Syntax:#

INSERT INTO <TABLE_NAME> VALUES (...);

You can also specify column names explicitly:

INSERT INTO <TABLE_NAME> (column1, column2, ...) VALUES (...);

Insert multiple records:#

INSERT INTO <TABLE_NAME> (column1, column2, ...) VALUES
(...),
(...),
(...);

Example – Insert a single row into `employees`:#

INSERT INTO employees (emp_id, name, dept_id, hire_date)
VALUES (1, 'Alice', 101, '2023-01-15');

Python Example – Insert#

# Insert a single employee
cursor.execute("""
INSERT INTO departments (dept_id, dept_name)
VALUES (101, 'Engineering');
""")

cursor.execute("""
INSERT INTO employees (emp_id, name, dept_id, hire_date)
VALUES (1, 'Alice', 101, '2023-01-15');
""")

conn.commit()

cursor.execute("SELECT * FROM employees;")
rows = cursor.fetchall()

for row in rows:
    print(row)

(1, 'Alice', 101, '2023-01-15')

UPDATE#

The UPDATE command is used to modify existing records in a table. You can update one or more columns, and typically use a WHERE clause to target specific rows. Without a WHERE clause, all rows in the table will be updated.

SQL Syntax:#

UPDATE <TABLE_NAME>
SET column1 = value1, column2 = value2
WHERE condition;

Example – Update an employee’s department:#

UPDATE employees
SET dept_id = 102
WHERE emp_id = 1;

Python Example – UPDATE#

# Update Alice's department
cursor.execute("""
UPDATE employees
SET dept_id = 102
WHERE emp_id = 1;
""")

conn.commit()

cursor.execute("SELECT * FROM employees;")
rows = cursor.fetchall()

for row in rows:
    print(row)

(1, 'Alice', 102, '2023-01-15')

DELETE#

The DELETE command is used to remove records from a table. Like UPDATE, it supports the WHERE clause to restrict deletion to specific records.

Warning

This is an irreversible action - once executed, the data is permanently removed.

SQL Syntax:#

DELETE FROM <TABLE_NAME>
WHERE condition;

Example – Delete an employee:#

DELETE FROM employees
WHERE emp_id = 1;

Python Example – DELETE#

# Delete employee with emp_id = 1
cursor.execute("DELETE FROM employees WHERE emp_id = 1;")

conn.commit()

cursor.execute("SELECT * FROM employees;")
rows = cursor.fetchall()

for row in rows:
    print(row)

These DML commands let us insert, update, and delete data from our tables - forming the backbone of any SQL-based data pipeline.

Data Control Language (DCL)#

In large and multi-user database systems, access control is a critical requirement. Not every user or application should have unrestricted permission to read from or write to all tables. The Data Control Language (DCL) provides commands to manage these permissions.

Note

SQLite does not support DCL commands. However, most production-grade SQL systems like PostgreSQL, MySQL, and Oracle do.

Users and Roles#

Databases often define users and assign roles with specific privileges. For example, you may have:

A readonly_user who can only query tables
An admin_user who can create or drop tables

Creating users and roles is system-specific. Here’s a general SQL example (PostgreSQL style):

CREATE USER analyst WITH PASSWORD 'secure123';

GRANT#

The GRANT command is used to give specific permissions to users or roles.

Example – Allow a user to read from a table:#

GRANT SELECT ON employees TO analyst;

Example – Allow inserting data:#

GRANT INSERT ON employees TO analyst;

REVOKE#

The REVOKE command is used to remove previously granted permissions from a user or role.

Example – Remove select permission:#

REVOKE SELECT ON employees FROM analyst;

These controls help ensure security, data integrity, and principle of least privilege in production systems.

Transaction Control Language (TCL)#

Transactions are fundamental for atomic, consistent, isolated, and durable (ACID) operations. TCL allows grouping multiple SQL commands into a single transaction block, so that either all changes are committed or none at all.

This is crucial for maintaining data integrity, especially in operations involving multiple steps.

COMMIT#

The COMMIT command permanently saves all changes made in the current transaction.

Example:#

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

COMMIT;

Once committed, the changes cannot be undone.

ROLLBACK#

The ROLLBACK command undoes all changes made in the current transaction block.

Example:#

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
-- Suppose an error happens here
ROLLBACK;

Nothing will be saved unless a COMMIT is executed.

Python Example – Commit and Rollback#

# Re-adding the record into employees table
cursor.execute("""
INSERT INTO employees (emp_id, name, dept_id, hire_date)
VALUES (1, 'Alice', 101, '2023-01-15')
ON CONFLICT(emp_id) DO UPDATE SET
    name=excluded.name,
    dept_id=excluded.dept_id,
    hire_date=excluded.hire_date;
""")
conn.commit()


try:
    conn.execute("BEGIN")

    # Simulate transaction
    cursor.execute("UPDATE employees SET dept_id = 102 WHERE emp_id = 1")
    
    # Simulate error
    # raise Exception("Something went wrong")

    conn.commit()
    print("Transaction committed!")

except Exception as e:
    conn.rollback()
    print("Transaction rolled back due to:", e)


cursor.execute("select * from employees WHERE emp_id = 1")

rows = cursor.fetchall()

for row in rows:
    print(row)

Transaction committed!
(1, 'Alice', 102, '2023-01-15')

Try running this code snipppet in Live Code mode and uncomment the exception.

TCL commands make your database interactions safe and predictable, especially when multiple operations must succeed or fail as a single unit.

SQL Commands

Contents

3.2.5.2. SQL Commands#

Data Definition Language (DDL)#

CREATE TABLE#

Example:#

Running the above in Python:#

ALTER TABLE#

Add a new column:#

Rename a column (SQLite ≥ 3.25.0):#

Rename a table:#

Python Example:#

DROP TABLE#

SQL Example:#

Python Example:#

CREATE INDEX#

Viewing Indexes#

DROP INDEX#

Example: Measuring Query Speed with and without an Index#

Data Query Language (DQL)#

SELECT#

Get all columns:#

Get specific columns:#

Example – Select from employees:#

Data Manipulation Language (DML)#

INSERT#

Syntax:#

Insert multiple records:#

Example – Insert a single row into employees:#

Python Example – Insert#

UPDATE#

SQL Syntax:#

Example – Update an employee’s department:#

Python Example – UPDATE#

DELETE#

SQL Syntax:#

Example – Delete an employee:#

Python Example – DELETE#

Data Control Language (DCL)#

Users and Roles#

GRANT#

Example – Allow a user to read from a table:#

Example – Allow inserting data:#

REVOKE#

Example – Remove select permission:#

Transaction Control Language (TCL)#

COMMIT#

Example:#

ROLLBACK#

Example:#

Python Example – Commit and Rollback#

Example – Select from `employees`:#

Example – Insert a single row into `employees`:#