SQL command

SQL command#

🗃️ Essential SQL Commands Cheat Sheet#

Command

Purpose

Syntax Example

Sample Output / Effect

SELECT

Retrieve data from a table

SELECT name, age FROM employees;

Returns name and age columns from employees

INSERT

Add new data into a table

INSERT INTO employees (name, age) VALUES ('Alice', 30);

Adds a new row with name “Alice” and age 30

UPDATE

Modify existing data

UPDATE employees SET age = 31 WHERE name = 'Alice';

Updates Alice’s age to 31

DELETE

Remove data from a table

DELETE FROM employees WHERE name = 'Alice';

Deletes Alice’s record

CREATE

Create a new table

CREATE TABLE employees (id INT, name TEXT, age INT);

Creates a table with three columns

DROP

Delete a table or database

DROP TABLE employees;

Removes the entire employees table

ALTER

Modify table structure

ALTER TABLE employees ADD salary INT;

Adds a salary column to employees table

WHERE

Filter records based on condition

SELECT * FROM employees WHERE age > 30;

Returns employees older than 30

JOIN

Combine rows from multiple tables

SELECT * FROM orders JOIN customers ON orders.cust_id = customers.id;

Combines matching rows from orders and customers

GROUP BY

Aggregate data by groups

SELECT dept, COUNT(*) FROM employees GROUP BY dept;

Counts employees per department

ORDER BY

Sort results

SELECT * FROM employees ORDER BY age DESC;

Lists employees by descending age

HAVING

Filter grouped data

SELECT dept, COUNT(*) FROM employees GROUP BY dept HAVING COUNT(*) > 5;

Departments with more than 5 employees

LIMIT

Restrict number of results

SELECT * FROM employees LIMIT 10;

Returns only the first 10 rows

🧠 Notes#

  • SELECT is the most frequently used command for querying data.

  • WHERE and HAVING are critical for filtering.

  • JOIN is essential for relational queries across tables.

  • GROUP BY and ORDER BY help summarize and organize results.

Would you like this scaffolded into a Jupyter Book module with interactive SQL widgets or quiz questions for students? I can also generate a syntax comparison across SQL dialects (e.g., MySQL, PostgreSQL, SQLite).

SQL Lite#

import sqlite3
import ipywidgets as widgets
from IPython.display import display, clear_output

### Setup in-memory SQLite DB
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

### Create sample table
cursor.execute("""
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    dept TEXT
);
""")
cursor.executemany("""
INSERT INTO employees (name, age, dept) VALUES (?, ?, ?);
""", [
    ("Alice", 30, "HR"),
    ("Bob", 25, "Engineering"),
    ("Charlie", 35, "HR"),
    ("Diana", 28, "Marketing"),
    ("Eve", 40, "Engineering")
])

### SQL command options
sql_options = {
    "SELECT": "SELECT name, age FROM employees;",
    "INSERT": "INSERT INTO employees (name, age, dept) VALUES ('Frank', 33, 'Sales');",
    "UPDATE": "UPDATE employees SET age = 31 WHERE name = 'Alice';",
    "DELETE": "DELETE FROM employees WHERE name = 'Bob';",
    "GROUP BY": "SELECT dept, COUNT(*) FROM employees GROUP BY dept;",
    "ORDER BY": "SELECT * FROM employees ORDER BY age DESC;",
    "WHERE": "SELECT * FROM employees WHERE age > 30;"
}

### Dropdown widget
dropdown = widgets.Dropdown(
    options=list(sql_options.keys()),
    description='SQL Command:',
    style={'description_width': 'initial'}
)

# Output area
output = widgets.Output()

### Execute and display results
def run_sql(change):
    output.clear_output()
    command = sql_options[change.new]
    try:
        cursor.execute(command)
        result = cursor.fetchall()
        with output:
            print(f"Executed:\n{command}\n")
            print("Result:")
            for row in result:
                print(row)
    except Exception as e:
        with output:
            print(f"Error: {e}")

dropdown.observe(run_sql, names='value')

### Display widgets
display(dropdown, output)
Contents