Difficulty: Advanced
SQL (Structured Query Language) is the universal language for interacting with relational databases, and it is an absolutely essential skill for data analytics. Whether you are extracting data for analysis, building dashboards, or creating data pipelines, SQL will be your primary tool for querying structured data. Understanding SQL fundamentals provides the foundation for more advanced analytical techniques.
The SELECT statement is the workhorse of SQL. It retrieves data from one or more tables, allowing you to specify which columns to include, filter rows with WHERE clauses, sort results with ORDER BY, and limit output with LIMIT. The basic syntax follows a logical flow: SELECT what you want, FROM where it lives, WHERE conditions must be met, ORDER BY how to sort, and LIMIT how many rows to return.
The WHERE clause supports a rich set of comparison operators (=, !=, <, >, <=, >=), logical operators (AND, OR, NOT), pattern matching (LIKE with % and _ wildcards), range checks (BETWEEN), and set membership (IN). Mastering these filtering tools allows you to extract precisely the subset of data needed for any analysis task.
Since SQL cannot run directly in a browser environment, we will learn SQL concepts by expressing queries as Python strings and simulating the operations using Python data structures. This approach reinforces both SQL syntax knowledge and the underlying logic of relational operations, which translates directly to working with pandas DataFrames and actual database systems.
# SQL queries expressed as Python strings for learning
# 1. Select all columns
query1 = "SELECT * FROM employees;"
print(f"Query: {query1}")
print("Purpose: Retrieve all rows and columns from the employees table")
print()
# 2. Select specific columns
query2 = "SELECT name, salary FROM employees WHERE department = 'Engineering';"
print(f"Query: {query2}")
print("Purpose: Get names and salaries of engineers only")
print()
# 3. Sorting and limiting
query3 = "SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;"
print(f"Query: {query3}")
print("Purpose: Get top 3 highest-paid employees")These three queries demonstrate the fundamental SQL pattern: SELECT chooses columns, FROM specifies the table, WHERE filters rows, ORDER BY sorts results, and LIMIT caps the output. Understanding this flow is the foundation for all SQL work.
# Simulating a database table as a list of dictionaries
employees = [
{'id': 1, 'name': 'Alice', 'department': 'Engineering', 'salary': 95000},
{'id': 2, 'name': 'Bob', 'department': 'Marketing', 'salary': 72000},
{'id': 3, 'name': 'Charlie', 'department': 'Engineering', 'salary': 105000},
{'id': 4, 'name': 'Diana', 'department': 'HR', 'salary': 68000},
{'id': 5, 'name': 'Eve', 'department': 'Engineering', 'salary': 88000},
]
# SQL: SELECT name, salary FROM employees WHERE department = 'Engineering'
# Python equivalent:
print("Engineers:")
result = [
{'name': e['name'], 'salary': e['salary']}
for e in employees
if e['department'] == 'Engineering'
]
for row in result:
print(f" {row['name']}: ${row['salary']:,}")
print()
# SQL: SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 2
print("Top 2 by salary:")
sorted_emps = sorted(employees, key=lambda e: e['salary'], reverse=True)[:2]
for e in sorted_emps:
print(f" {e['name']}: ${e['salary']:,}")List comprehensions with conditional filtering mirror SQL WHERE clauses. Python's sorted() with a key function and slice mirrors ORDER BY with LIMIT. This correspondence helps you translate SQL logic to Python and vice versa.
products = [
{'name': 'Laptop', 'price': 999, 'category': 'Electronics', 'stock': 50},
{'name': 'Book', 'price': 15, 'category': 'Education', 'stock': 200},
{'name': 'Phone', 'price': 699, 'category': 'Electronics', 'stock': 0},
{'name': 'Desk', 'price': 250, 'category': 'Furniture', 'stock': 30},
{'name': 'Monitor', 'price': 450, 'category': 'Electronics', 'stock': 75},
{'name': 'Chair', 'price': 180, 'category': 'Furniture', 'stock': 0},
]
# SQL: WHERE price BETWEEN 100 AND 500
print("BETWEEN 100 AND 500:")
for p in products:
if 100 <= p['price'] <= 500:
print(f" {p['name']}: ${p['price']}")
print()
# SQL: WHERE category IN ('Electronics', 'Furniture') AND stock > 0
print("IN ('Electronics','Furniture') AND stock > 0:")
allowed = {'Electronics', 'Furniture'}
for p in products:
if p['category'] in allowed and p['stock'] > 0:
print(f" {p['name']}: {p['category']}, stock={p['stock']}")
print()
# SQL: WHERE name LIKE '%o%' (contains 'o')
print("LIKE '%o%' (name contains 'o'):")
for p in products:
if 'o' in p['name'].lower():
print(f" {p['name']}")SQL's BETWEEN maps to Python's chained comparison (a <= x <= b). The IN operator maps to Python's set/list membership check. LIKE with wildcards maps to string methods like 'in' for contains, startswith() for prefix matching, and endswith() for suffix matching.
def build_select_query(table, columns='*', where=None, order_by=None, limit=None):
query = f"SELECT {columns} FROM {table}"
if where:
query += f" WHERE {where}"
if order_by:
query += f" ORDER BY {order_by}"
if limit:
query += f" LIMIT {limit}"
return query + ";"
# Generate common analytics queries
queries = [
build_select_query('sales', 'product, revenue', 'revenue > 1000', 'revenue DESC', 10),
build_select_query('users', 'COUNT(*)', "created_at >= '2024-01-01'"),
build_select_query('orders', '*', "status = 'pending'", 'created_at ASC'),
build_select_query('products', 'name, price', 'category = \'Electronics\'', 'price DESC', 5),
]
print("Generated SQL Queries:")
for i, q in enumerate(queries, 1):
print(f"\n{i}. {q}")Building SQL queries programmatically is a common task in analytics tools and data pipelines. This builder function follows the standard SQL clause order: SELECT, FROM, WHERE, ORDER BY, LIMIT. Note: in production, always use parameterized queries to prevent SQL injection.
SELECT, WHERE, ORDER BY, LIMIT, basic queries