Part VI: Advanced SQL Concepts

 

By now, you’ve mastered the foundations of SQL—from basic queries to table design. In this final module, we’ll dive into three advanced pillars that elevate your database skills:

• Views, Stored Procedures, and Functions

• Transactions and Concurrency

• Performance Tuning & Best Practices

These topics empower you to encapsulate logic, ensure data integrity under load, and optimize queries for real-world workloads.

Views, Stored Procedures, and Functions

Encapsulating complex logic in the database makes your applications cleaner and more maintainable.

Views

A view is a virtual table defined by a SQL query. Use views to:

• Simplify joins and aggregations into a single, reusable object

• Enforce data security by exposing only selected columns

• Provide backward compatibility when underlying schemas change

Example:

sql

CREATE VIEW sales_summary AS
SELECT 
  product_id,
  DATE_TRUNC('month', sale_date) AS sale_month,
  SUM(quantity * price) AS total_revenue
FROM sales
GROUP BY product_id, sale_month;

Querying sales_summary hides the complexity of aggregation and date functions.

Stored Procedures

Stored procedures bundle SQL statements and procedural logic (loops, conditionals) into callable routines. Benefits include:

• Reduced network traffic by running multiple statements on the server

• Centralized business rules that multiple applications can invoke

• Error handling and transaction control within the procedure

Example:

sql

CREATE PROCEDURE adjust_prices(pct_increase DECIMAL)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE products
  SET price = price * (1 + pct_increase);
  RAISE NOTICE 'Prices increased by %%', pct_increase * 100;
END;
$$;

Call it with:

sql

CALL adjust_prices(0.05);

User-Defined Functions

Functions return a scalar value or a table, and can be used directly in SELECT lists, WHERE clauses, or joins. They’re ideal for encapsulating reusable calculations.

Example of a scalar function in PostgreSQL:

sql

CREATE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
  RETURN ROUND(amount * 0.08, 2);
END;
$$ LANGUAGE plpgsql;

Use it inline:

sql

SELECT order_id, calculate_tax(subtotal) AS tax
FROM orders;

Transactions and Concurrency

Ensuring data consistency when multiple users interact with the database concurrently is critical.

ACID Properties

Transactions must satisfy:

• Atomicity: All steps succeed or none do.

• Consistency: Database moves from one valid state to another.

• Isolation: Concurrent transactions don’t interfere.

• Durability: Committed changes persist despite failures.

Transaction Control

Basic commands:

sql

BEGIN;                -- Start a transaction
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;               -- Save changes
-- or ROLLBACK;      -- Revert if something goes wrong

Isolation Levels

Different levels balance performance and strictness:

• READ UNCOMMITTED: Dirty reads allowed

• READ COMMITTED: Only committed data visible

• REPEATABLE READ: Consistent snapshot per transaction

• SERIALIZABLE: Full isolation, like transactions run sequentially

Choose the lowest level that meets your integrity needs to reduce locking overhead.

Locking and Deadlocks

• Row‐level locks prevent concurrent writes to the same row.

• Table‐level locks block access to entire tables.

Deadlocks occur when two transactions wait on each other’s locks. Prevent them by:

• Keeping transactions short

• Accessing tables in a consistent order

• Handling deadlock errors in application code and retrying

Performance Tuning & Best Practices

Writing correct SQL is only half the battle—writing efficient SQL ensures your applications scale.

Reading Execution Plans

Most RDBMS provide an EXPLAIN command that reveals how a query runs:

sql

EXPLAIN ANALYZE
SELECT * FROM orders WHERE order_date > '2024-01-01';

Look for:

• Full table scans vs. index scans

• Nested loop joins vs. hash joins

• Estimated vs. actual row counts

Indexing Strategies

• Single‐column indexes: Ideal for frequent filters and joins.

• Composite indexes: Cover multiple columns in WHERE clauses or ORDER BY.

• Covering indexes: Include extra columns so the query can be satisfied entirely by the index.

Avoid over-indexing—each index adds overhead to INSERT, UPDATE, and DELETE.

Query Optimization Techniques

• Push filters early (in JOIN conditions, WHERE clauses).

• Avoid SELECT *; list only needed columns.

• Replace subqueries with joins when beneficial.

• Use set-based operations instead of row-by-row loops.

Maintenance and Monitoring

• Regularly run VACUUM or OPTIMIZE TABLE to reclaim space and update statistics.

• Monitor slow‐query logs to identify bottlenecks.

• Automate alerts when query execution time or lock waits exceed thresholds.

Wrapping Up

Advanced SQL concepts let you build maintainable, high-performance database applications. By mastering views, procedures, and functions, enforcing ACID transactions under concurrency, and tuning queries with effective indexing and plan analysis, you’ll be equipped to handle any data challenge.

Thank you for joining our complete SQL tutorial series on Data Analyst BI. Now it’s time to put these skills into practice and transform raw data into actionable insights—efficiently and reliably.