← Previous Next →

SQL Basics

Structured Query Language (SQL) is the foundational language used to communicate with relational databases. At its core, SQL allows you to define, manipulate, retrieve, and control data stored in tables that relate to one another through keys and relationships. This lesson explores the essential building blocks of SQL—from understanding database schema design, writing complex queries that join multiple tables, filtering data with conditions, to modifying datasets safely using transactions. Mastery of SQL is crucial for database administrators, data analysts, backend developers, and anyone involved with data-driven applications. We will dive into advanced SQL concepts with practical examples and best practices that unlock powerful data operations beyond basic queries.

💡 A Simple Analogy: SQL as a Language for Data Conversations

Imagine a large library where books are stored in shelves (tables). Each book has attributes like title, author, and genre (columns). SQL is like a librarian’s language that helps you ask precise questions such as “Which authors wrote more than five books?” or “Show me all thriller books published after 2010.” Just as you’d ask the librarian for specific information, SQL queries instruct the database how to retrieve or update data efficiently and correctly.

🎯 Real-World Use Case: Customer Data Management in E-commerce

In e-commerce platforms, SQL is essential for managing customer information, orders, and product inventories. For example, an SQL query can quickly provide insights such as customers who purchased a specific product in the last month, or identify products that are out of stock. This data-driven decision making improves inventory control, personalized marketing, and overall customer experience.

⚠️ Common Pitfall: Overusing SELECT *

While SELECT * is convenient for quick queries, relying on it in production or complex queries can lead to performance issues. It fetches all columns, including those unnecessary for your task, increasing data transfer and processing time. Always specify the exact columns you need to optimize query speed and maintain clarity.

1

Understanding Databases and Tables A relational database organizes data into tables, each representing an entity (e.g., Customers, Orders). Tables have columns (attributes) with defined data types and rows (records) containing the actual data entries.

2

Basic Querying with SELECT The SELECT statement retrieves data. You specify columns to fetch and the table to query from. For example, SELECT first_name, last_name FROM customers; returns customer names.

3

Filtering Rows with WHERE Use the WHERE clause to restrict results based on conditions (e.g., WHERE age > 30). Operators include =, <, >, IN, LIKE, and BETWEEN.

4

Sorting Results Using ORDER BY Sort query results by columns in ascending (ASC) or descending (DESC) order to organize output logically.

5

Joining Tables Combine rows from two or more tables using JOIN operations based on related columns. Common types: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN.

6

Aggregation and Grouping Use aggregate functions like COUNT(), SUM(), AVG(), and group rows with GROUP BY to summarize data.

7

Inserting, Updating, and Deleting Data Modify data using INSERT INTO, UPDATE, and DELETE statements. Always use transactions to ensure data integrity.

8

Using Subqueries and Common Table Expressions (CTEs) Embed queries within queries or use CTEs with WITH clauses for clearer, modular SQL code.

Architecture of SQL Basics
Architecture of SQL Basics

📌 Deep Dive: Complex SQL Query Combining Joins, Filtering, and Aggregation

PYTHON 

# This example uses Python's sqlite3 module to execute an advanced SQL query.
# The database has two tables:
# - customers (customer_id, first_name, last_name, city)
# - orders (order_id, customer_id, order_date, total_amount)
#
# Goal: Find the total order amount per customer who placed orders in 2023,
# sorted from highest to lowest total spent.

import sqlite3

# Connect to the SQLite database file (or create it)
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Create tables
cursor.execute('''
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    city TEXT
)
''')

cursor.execute('''
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    total_amount REAL,
    FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
)
''')

# Insert sample data
customers_data = [
    (1, 'Alice', 'Johnson', 'New York'),
    (2, 'Bob', 'Smith', 'Los Angeles'),
    (3, 'Charlie', 'Lee', 'Chicago')
]

orders_data = [
    (101, 1, '2023-01-15', 250.00),
    (102, 1, '2023-02-20', 125.50),
    (103, 2, '2022-11-05', 300.00),
    (104, 3, '2023-03-12', 450.75),
    (105, 3, '2023-04-01', 100.25)
]

cursor.executemany('INSERT INTO customers VALUES (?, ?, ?, ?)', customers_data)
cursor.executemany('INSERT INTO orders VALUES (?, ?, ?, ?)', orders_data)

# Advanced SQL query using INNER JOIN, WHERE, GROUP BY, and ORDER BY
query = """
SELECT c.first_name || ' ' || c.last_name AS customer_name,
       SUM(o.total_amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date LIKE '2023%'
GROUP BY c.customer_id
ORDER BY total_spent DESC
"""

cursor.execute(query)
results = cursor.fetchall()

# Print results
for row in results:
    print(f"Customer: {row[0]}, Total Spent in 2023: ${row[1]:.2f}")

conn.close()
Output
Customer: Charlie Lee, Total Spent in 2023: $551.00 Customer: Alice Johnson, Total Spent in 2023: $375.50
← Previous Next →