SQL Commands

SQL commands are organized into distinct categories based on their function. Understanding these categories helps you know which commands to use for different tasks and how they interact with the database.

In this tutorial, you will learn:

• The five categories of SQL commands
• What each command category does
• When to use each type of command
• Examples of commands in each category
• Which commands are most important for data analysis

SQL commands are grouped into five main categories, often called "sublanguages" of SQL.

The Five Categories

Visual Overview

Which Categories Are Most Important?

For data analysts and query writers, the priority order is:

1. DQL (SELECT) - 80%+ of your work
2. DML - Occasionally modifying data
3. DDL - Creating tables, usually less frequent
4. TCL/DCL - Typically handled by database administrators

DQL is used to retrieve data from the database. It consists of just one command: SELECT.

The SELECT Statement

SELECT is the most commonly used SQL command. It retrieves data from one or more tables.

Basic Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column
HAVING group_condition
ORDER BY column
LIMIT number;

SELECT Capabilities

Examples

Simple SELECT:

SELECT customer_city, customer_state
FROM olist_customers_dataset
LIMIT 5;

SELECT with Aggregation:

SELECT customer_state, COUNT(*) AS customer_count
FROM olist_customers_dataset
GROUP BY customer_state
ORDER BY customer_count DESC;

Why SELECT is Special

• Read-only: Does not modify any data
• Safe to run: Cannot accidentally delete or change data
• Most used: Analysts spend most time with SELECT
• Our focus: This course emphasizes mastering SELECT

DML commands modify data within existing tables. They change the content but not the structure.

DML Commands

INSERT - Adding New Records

-- Insert a single row
INSERT INTO customers (customer_id, name, email)
VALUES ('C001', 'Alice Johnson', 'alice@mail.com');

-- Insert multiple rows
INSERT INTO customers (customer_id, name, email)
VALUES 
    ('C002', 'Bob Smith', 'bob@mail.com'),
    ('C003', 'Carol Davis', 'carol@mail.com');

-- Insert from another table
INSERT INTO archive_customers
SELECT * FROM customers WHERE status = 'inactive';

UPDATE - Modifying Existing Records

-- Update specific rows
UPDATE customers
SET email = 'newemail@mail.com'
WHERE customer_id = 'C001';

-- Update with calculation
UPDATE products
SET price = price * 1.10  -- 10% increase
WHERE category = 'electronics';

-- IMPORTANT: Always use WHERE!
-- Without WHERE, ALL rows are updated
UPDATE products SET price = 0;  -- Dangerous!

DELETE - Removing Records

-- Delete specific rows
DELETE FROM orders
WHERE order_status = 'canceled';

-- Delete based on date
DELETE FROM logs
WHERE created_at < '2023-01-01';

-- IMPORTANT: Always use WHERE!
-- Without WHERE, ALL rows are deleted
DELETE FROM orders;  -- Deletes everything!

DML Safety Tips

⚠️ Critical Warning:

-- ALWAYS preview before modifying:
SELECT * FROM orders WHERE order_status = 'canceled';
-- Then run:
DELETE FROM orders WHERE order_status = 'canceled';

-- Use transactions for safety:
BEGIN TRANSACTION;
DELETE FROM orders WHERE order_status = 'canceled';
-- Check the result, then:
COMMIT;  -- or ROLLBACK; if wrong

DDL commands define and modify database structure. They work on tables, indexes, and other database objects.

DDL Commands

CREATE - Building Database Objects

-- Create a new table
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Create a table with foreign key
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATETIME,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Create an index for faster queries
CREATE INDEX idx_customer_email ON customers(email);

-- Create a view (saved query)
CREATE VIEW active_customers AS
SELECT * FROM customers WHERE status = 'active';

ALTER - Modifying Existing Objects

-- Add a new column
ALTER TABLE customers
ADD COLUMN phone TEXT;

-- Rename a column (syntax varies by DBMS)
ALTER TABLE customers
RENAME COLUMN phone TO phone_number;

-- Drop a column
ALTER TABLE customers
DROP COLUMN phone_number;

DROP - Removing Objects

-- Drop a table (deletes everything!)
DROP TABLE old_customers;

-- Drop only if exists (prevents errors)
DROP TABLE IF EXISTS temp_data;

-- Drop a view
DROP VIEW customer_summary;

-- Drop an index
DROP INDEX idx_customer_email;

TRUNCATE vs DELETE

DCL commands manage access and security. They control who can do what in the database.

DCL Commands

GRANT - Giving Permissions

-- Grant SELECT permission on a table
GRANT SELECT ON customers TO analyst_user;

-- Grant multiple permissions
GRANT SELECT, INSERT, UPDATE ON orders TO sales_team;

-- Grant all permissions
GRANT ALL PRIVILEGES ON products TO admin_user;

-- Grant with ability to pass on
GRANT SELECT ON reports TO manager WITH GRANT OPTION;

REVOKE - Removing Permissions

-- Revoke specific permission
REVOKE INSERT ON customers FROM intern_user;

-- Revoke all permissions
REVOKE ALL PRIVILEGES ON sensitive_data FROM public;

Common Permission Types

Typical Permission Patterns

-- Read-only analyst
GRANT SELECT ON ALL TABLES TO analyst;

-- Application user
GRANT SELECT, INSERT, UPDATE, DELETE ON app_tables TO app_user;

-- Report viewer
GRANT SELECT ON report_views TO reporting_team;

Note: DCL is typically managed by Database Administrators (DBAs). As a data analyst, you'll usually just use the permissions granted to you.

TCL commands manage transactions—groups of SQL statements that should succeed or fail together.

TCL Commands

Why Transactions Matter

Consider a bank transfer:

-- Transfer $100 from Account A to Account B
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';

What if the second statement fails?

• Account A lost $100
• Account B never received it
• Money is lost!

Using Transactions

-- Start a transaction
BEGIN TRANSACTION;

-- Perform operations
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';

-- If everything worked:
COMMIT;

-- If something went wrong:
-- ROLLBACK;  -- Undo everything

SAVEPOINT - Partial Rollback

BEGIN TRANSACTION;

INSERT INTO orders VALUES (1, 'Order 1');
SAVEPOINT after_order1;

INSERT INTO orders VALUES (2, 'Order 2');
-- Oops, this was wrong!

ROLLBACK TO SAVEPOINT after_order1;
-- Order 1 is kept, Order 2 is undone

COMMIT;

Transaction Example Flow

┌──────────────────────────────────────────────────┐
│                                                  │
│  BEGIN TRANSACTION                               │
│       │                                          │
│       ▼                                          │
│  ┌─────────┐    ┌─────────┐    ┌─────────┐      │
│  │Statement│───►│Statement│───►│Statement│      │
│  │    1    │    │    2    │    │    3    │      │
│  └─────────┘    └─────────┘    └─────────┘      │
│       │                             │            │
│       │         All OK?             │            │
│       │                             │            │
│       ▼                             ▼            │
│  ┌─────────┐                   ┌─────────┐      │
│  │ COMMIT  │   Any Error?      │ROLLBACK │      │
│  │(Saved)  │◄─────────────────►│(Undone) │      │
│  └─────────┘                   └─────────┘      │
│                                                  │
└──────────────────────────────────────────────────┘

Here's a complete reference of SQL command categories:

Quick Reference Table

Command Impact Levels

What You'll Use Most

As a Data Analyst (80%+):

SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ...

Occasionally:

INSERT INTO ... VALUES ...
UPDATE ... SET ... WHERE ...
CREATE TABLE ... AS SELECT ...

Rarely (handled by DBAs):

GRANT ... TO ...
ALTER TABLE ...
DROP TABLE ...

Let's practice with SELECT commands, which are the focus of this course. Our practice database only allows SELECT queries (read-only for safety).

You now understand the complete landscape of SQL commands!

Key Takeaways

✅ DQL (SELECT) - Query data (read-only, safe, most used)

✅ DML (INSERT, UPDATE, DELETE) - Modify data within tables

✅ DDL (CREATE, ALTER, DROP) - Define and modify table structure

✅ DCL (GRANT, REVOKE) - Control user permissions

✅ TCL (COMMIT, ROLLBACK) - Manage transactions

What to Focus On

Safety Reminder

🟢 SELECT - Always safe
🟡 INSERT - Usually safe (adds data)
🔴 UPDATE/DELETE - Use WHERE clause!
🔴 DROP/TRUNCATE - Be very careful!

Next Up

Continue to Why Learn SQL? to understand the career benefits and practical applications of SQL skills!