🗄️ SQL Fundamentals — The Language of Databases

Learn the core SQL concepts you need to work with databases effectively.

🎯 What is SQL?

SQL (Structured Query Language) is the standard language for working with databases. It lets you:

• Create tables and structure data

• Insert new data

• Query and retrieve data

• Update and delete data

import sqlite3

conn = sqlite3.connect("app.db")
cursor = conn.cursor()

# SQL is database-agnostic (works with SQLite, MySQL, PostgreSQL, etc.)
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
conn.close()

💡 The Four Core SQL Operations (CRUD)

CREATE — Add new records

cursor.execute("""
    INSERT INTO users (name, age, email)
    VALUES ('Alice', 30, 'alice@example.com')
""")
conn.commit()

READ — Retrieve records

cursor.execute("SELECT name, email FROM users WHERE age > 25")
for row in cursor.fetchall():
    print(row)

UPDATE — Modify records

cursor.execute("""
    UPDATE users
    SET age = 31
    WHERE name = 'Alice'
""")
conn.commit()

DELETE — Remove records

cursor.execute("DELETE FROM users WHERE age < 18")
conn.commit()

🔍 Basic Query Patterns

SELECT with WHERE

# Find all users over 25
cursor.execute("SELECT * FROM users WHERE age > 25")

# Find a specific user by name
cursor.execute("SELECT * FROM users WHERE name = 'Alice'")

# Multiple conditions
cursor.execute("""
    SELECT * FROM users
    WHERE age > 25 AND city = 'New York'
""")

ORDER BY

# Sort results by age (ascending)
cursor.execute("SELECT * FROM users ORDER BY age ASC")

# Sort by age descending
cursor.execute("SELECT * FROM users ORDER BY age DESC")

LIMIT

# Get first 10 users
cursor.execute("SELECT * FROM users LIMIT 10")

# Skip first 5, get next 10 (pagination)
cursor.execute("SELECT * FROM users LIMIT 10 OFFSET 5")

🔑 Key SQL Keywords You'll Use

📊 Common Mistakes to Avoid

❌ Forgetting to commit changes

cursor.execute("INSERT INTO users VALUES ...")
# Changes are lost unless you conn.commit()!

✅ Always commit after modifications

cursor.execute("INSERT INTO users VALUES ...")
conn.commit()  # Now the change is permanent

❌ Using untyped comparisons

cursor.execute("SELECT * FROM users WHERE age = '30'")  # Wrong!

✅ Type-safe queries

cursor.execute("SELECT * FROM users WHERE age = 30")  # Correct

🔑 Key Takeaways

• ✅ SQL is the universal database language

• ✅ CRUD operations (Create, Read, Update, Delete) are fundamental

• ✅ Use WHERE to filter results

• ✅ Always commit() changes to make them permanent

• ✅ Write readable queries with proper formatting

Ready to practice? Challenges | Quiz