SQL – 100+ Lab Exercises (Basic, Intermediate, Advanced)

🔰 Basic Level (30+ Exercises)

Goal: Build a solid foundation in SQL syntax, querying, and database fundamentals.

Fundamental Concepts

Introduction to databases and relational model.
Basic SELECT queries with WHERE clauses.
Filtering data using comparison and logical operators.
Sorting data with ORDER BY.
Using DISTINCT to remove duplicates.

Data Retrieval & Functions

Using aggregate functions: COUNT, SUM, AVG, MIN, MAX.
Grouping data with GROUP BY.
Filtering grouped data with HAVING.
Working with NULL values and IS NULL checks.
Using string functions: UPPER, LOWER, CONCAT.

Basic Joins and Subqueries

INNER JOIN basics to combine tables.
LEFT JOIN and RIGHT JOIN usage.
Writing simple subqueries in SELECT and WHERE.
Understanding aliases for readability.
Using LIMIT and OFFSET for pagination.

🚀 Intermediate Level (40+ Exercises)

Goal: Enhance skills in complex querying, database design, and transaction management.

Advanced Joins and Set Operations

FULL OUTER JOIN and CROSS JOIN.
Using UNION, INTERSECT, and EXCEPT.
Self-joins and recursive queries.
Nested subqueries and correlated subqueries.

Data Modification and Transactions

INSERT, UPDATE, DELETE statements.
Using transactions and understanding ACID properties.
Implementing COMMIT and ROLLBACK.
Locking mechanisms and concurrency control.

Database Design & Integrity

Creating tables with constraints (PRIMARY KEY, FOREIGN KEY).
Implementing UNIQUE, CHECK, and NOT NULL constraints.
Normalization concepts and applying 1NF, 2NF, 3NF.
Creating and using indexes for performance.
Designing ER diagrams.

Stored Procedures & Functions

Writing stored procedures and user-defined functions.
Using variables, control flow, and error handling.
Triggers for automated responses to data changes.

🧠 Advanced Level (40+ Exercises)

Goal: Master performance optimization, complex analytics, and advanced SQL features.

Performance Tuning & Optimization

Analyzing query execution plans.
Index optimization strategies.
Partitioning tables and data sharding.
Optimizing joins and subqueries.
Using materialized views.

Advanced Analytical Queries

Window functions: ROW_NUMBER, RANK, LEAD, LAG.
Complex CTEs (Common Table Expressions) and recursive queries.
Pivot and unpivot data.
JSON and XML data querying within SQL.
Full-text search capabilities.

Security & Administration

Managing user roles and permissions.
Auditing and compliance queries.
Backup and recovery strategies.
Replication and failover setups.
Managing large-scale distributed databases.

Capstone Projects

Design and implement a normalized database schema for an enterprise app.
Write complex reports combining multiple data sources.
Build a data warehouse star schema and query it.
Implement stored procedures for business logic automation.
Optimize and tune queries for a high-traffic application.

✅ Popular SQL Platforms Covered

MySQL
PostgreSQL
Microsoft SQL Server
Oracle SQL
SQLite