Database Management System (DBMS) SQL Labs
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.
🎯 Learning Objectives
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 Structure
Foundational Labs (SQL Basics)
| 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 |
Database Design Labs
| 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 |
Programming Integration Labs
| Lab |
Topic |
Description |
Resource |
| 11 |
Python Database Interface |
Connect Python applications to databases using connectors |
Python Lab |
| 12 |
Jupyter Notebook Integration |
Interactive database analysis with Jupyter notebooks |
Jupyter Lab |
Advanced Database Concepts
| Lab |
Topic |
Description |
Resource |
| 13 |
Analytical Functions |
Master ROLLUP, CUBE, and advanced grouping operations |
Rollup Lab |
| 14 |
Triggers & Stored Procedures |
Implement database automation and business logic |
Triggers Lab |
| 15 |
Transaction Management |
Understand ACID properties and concurrency control |
Transactions |
| 16 |
Isolation Levels |
Learn about database isolation and consistency |
Isolation Levels |
Modern Database Technologies
| Lab |
Topic |
Description |
Resource |
| 17 |
JSON & XML Processing |
Handle semi-structured data in relational databases |
JSON/XML Lab |
| 18 |
MongoDB (NoSQL) |
Introduction to document-based databases |
MongoDB Lab |
| 19 |
Neo4j (Graph Database) |
Explore graph database concepts and Cypher queries |
Neo4j Lab |
🗃️ Data Sets
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 |
🚀 Getting Started
Prerequisites
- MySQL or MariaDB server installed
- Basic understanding of relational database concepts
- Text editor or SQL client (MySQL Workbench, phpMyAdmin, etc.)
Quick Start Guide
- Clone the repository
git clone https://github.com/TeachingOW/DBMS-SQL-Labs.git
cd DBMS-SQL-Labs
- Set up your database
CREATE DATABASE dbms_labs;
USE dbms_labs;
- Start with Lab 1
- Navigate to InClass Exercises
- Follow the step-by-step instructions
- Practice with the provided examples
Lab Progression
- Beginners: Start with Labs 1-4 (Foundational Labs)
- Intermediate: Continue with Labs 5-8 (Advanced SQL)
- Advanced: Explore Labs 9+ (Database Design & Modern Technologies)
Database Systems
- MySQL/MariaDB: Primary database system for most labs
- MongoDB: NoSQL document database (Lab 18)
- Neo4j: Graph database system (Lab 19)
Programming Languages
- SQL: Standard query language for relational databases
- Python: Database connectivity and data analysis
- Cypher: Query language for Neo4j graph database
Development Environment
- Jupyter Notebooks: Interactive data analysis and visualization
- Command Line Tools: Direct database interaction
- Database Clients: GUI tools for database management
📖 Additional Learning Resources
Interactive Tutorials
Advanced Topics
🤝 Contributing
We welcome contributions to improve these labs! Please:
- Fork the repository
- Create a feature branch
- Make your improvements
- Submit a pull request
Areas for Contribution
- Additional practice exercises
- New dataset examples
- Improved explanations and documentation
- Bug fixes and corrections
- Translation to other languages
📄 License
This project is licensed under the MIT License - see the LICENSE file for details.
🌐 Project Website
Visit the project website at https://teachingow.github.io/DBMS-SQL-Labs/ for additional resources and updates.