A comprehensive collection of hands-on SQL laboratories designed to teach database concepts from basic queries to advanced database management techniques. These labs provide practical experience with SQL, database design, and modern database technologies.
By completing these labs, students will:
- Master fundamental SQL operations (SELECT, INSERT, UPDATE, DELETE)
- Understand database design principles and normalization
- Learn advanced SQL concepts (joins, subqueries, window functions)
- Gain experience with database programming and interfaces
- Explore modern database technologies (NoSQL, Graph databases)
- Understand transaction management and concurrency control
| Lab | Topic | Description | Resource |
|---|---|---|---|
| 1 | Database Creation & Basic Queries | Learn to create databases, tables, and perform simple SELECT operations | Lab 1 |
| 2 | Advanced Queries & Joins | Master multi-table queries, joins, and complex WHERE clauses | Lab 2 |
| 3 | Foreign Keys & Relationships | Understand referential integrity and table relationships | Foreign Keys |
| 4 | Multi-Table Operations | Practice complex joins and relationship queries | Multi Tables |
| Lab | Topic | Description | Resource |
|---|---|---|---|
| 5 | Set Operations & Nested Queries | Learn UNION, INTERSECT, and subquery techniques | Lab 2 Advanced |
| 6 | Aggregate Functions & Grouping | Master COUNT, SUM, AVG, GROUP BY, and HAVING clauses | Lab 2 Aggregates |
| 7 | Window Functions | Learn advanced analytical functions and partitioning | Window Functions |
| 8 | Views & Virtual Tables | Create and manage database views for data abstraction | Views Lab |
| Lab | Topic | Description | Resource |
|---|---|---|---|
| 9 | Database Normalization | Learn 1NF, 2NF, 3NF and database design principles | Normal Forms |
| 10 | Advanced SQL Techniques | Practice complex queries and optimization | Advanced SQL |
| Lab | Topic | Description | Resource |
|---|---|---|---|
| 11 | Java Database Interface | Connect Java applications to databases | Java Lab |
| 12 | Python Database Interface | Connect Python applications to databases using connectors | Python Lab |
| 13 | Jupyter Notebook Integration | Interactive database analysis with Jupyter notebooks | Jupyter Lab |
| Lab | Topic | Description | Resource |
|---|---|---|---|
| 14 | Analytical Functions | Master ROLLUP, CUBE, and advanced grouping operations | Rollup Lab |
| 15 | Triggers & Stored Procedures | Implement database automation and business logic | Triggers Lab |
| 16 | Transaction Management | Understand ACID properties and concurrency control | Transactions |
| 17 | Isolation Levels | Learn about database isolation and consistency | Isolation Levels |
| Lab | Topic | Description | Resource |
|---|---|---|---|
| 18 | JSON & XML Processing | Handle semi-structured data in relational databases | JSON/XML Lab |
| 19 | MongoDB (NoSQL) | Introduction to document-based databases | MongoDB Lab |
| 20 | Neo4j (Graph Database) | Explore graph database concepts and Cypher queries | Neo4j Lab |
The repository includes several real-world datasets for hands-on practice:
| Dataset | Description | Use Cases |
|---|---|---|
| IMDB Movie Data | Movie information including ratings, genres, and revenue | Complex queries, aggregations, data analysis |
| Air Travel Data | Flight and passenger information | Time-series analysis, grouping operations |
| Cities Data | Geographic and demographic information | Joins, geographic queries |
| Employee Data | HR database with employee information | Relationship modeling, hierarchical queries |
| Student Grades | Academic performance data | Statistical analysis, ranking functions |
| Drivers Database | Complete database schema with sample data | Full database operations, complex relationships |
- MySQL/MariaDB: Primary database system for most labs
- MongoDB: NoSQL document database (Lab 18)
- Neo4j: Graph database system (Lab 19)
- SQL: Standard query language for relational databases
- Python: Database connectivity and data analysis
- Cypher: Query language for Neo4j graph database
- Jupyter Notebooks: Interactive data analysis and visualization
- Command Line Tools: Direct database interaction
- Database Clients: GUI tools for database management
- SQLZoo - Interactive SQL tutorial with exercises
- W3Schools SQL Tutorial - Comprehensive SQL reference
- MySQL Tutorial - Official MySQL documentation
- Database Design Principles - ER modeling and normalization
- SQL Performance Tuning - Query optimization techniques
- NoSQL Databases - Understanding document and graph databases
- DataLemur
- HackerRank SQL - SQL coding challenges
- LeetCode Database - Database problem solving
- SQLBolt - Interactive SQL lessons
We welcome contributions to improve these labs! Please:
- Fork the repository
- Create a feature branch
- Make your improvements
- Submit a pull request
- Additional practice exercises
- New dataset examples
- Improved explanations and documentation
- Bug fixes and corrections
Visit the project website at https://teachingow.github.io/DBMS-SQL-Labs/ for additional resources and updates.