Getting Started with DBMS-SQL-Labs

📋 Table of Contents

Introduction

Welcome to DBMS-SQL-Labs! This repository contains a comprehensive collection of hands-on SQL laboratories designed to teach database concepts from basic queries to advanced database management techniques. Whether you're a beginner learning SQL for the first time or an experienced developer looking to sharpen your database skills, these labs provide structured, practical experience.

Prerequisites

Required Knowledge

Software Requirements

1. Database Management System

You'll need at least one of the following:

MySQL/MariaDB (Recommended for most labs)

  # Ubuntu/Debian
  sudo apt-get update
  sudo apt-get install mysql-server
  
  # macOS (using Homebrew)
  brew install mysql
  
  # Windows
  # Download installer from MySQL website

PostgreSQL (Alternative option)

2. Programming Languages (for integration labs)

Python (Labs 13-15)

  pip install mysql-connector-python
  pip install pymysql

Java (Lab 12)

Node.js (Labs 7-8)

  npm install mysql2

3. Additional Tools

Jupyter Notebooks (For interactive labs)

pip install jupyter
pip install ipython-sql
pip install pymysql

MongoDB (Lab 20)

Neo4j (Lab 21)

Database GUI Tools (Optional but recommended)

Installation & Setup

Step 1: Clone the Repository

git clone https://github.com/TeachingOW/DBMS-SQL-Labs.git
cd DBMS-SQL-Labs

Step 2: Set Up MySQL

  1. Start MySQL service:
   # Linux
   sudo systemctl start mysql
   
   # macOS
   brew services start mysql
   
   # Windows
   # Use MySQL Workbench or Services panel
  1. Secure your installation (recommended):
   sudo mysql_secure_installation
  1. Log into MySQL:
   mysql -u root -p
  1. Create a database for practice:
   CREATE DATABASE practice_db;
   USE practice_db;

Step 3: Test Your Setup

Run a simple query to verify everything works:

SELECT 'Hello, SQL!' AS greeting;

If you see the output, you're ready to start!

Quick Start Guide

For Complete Beginners

Start with these labs in order:

  1. Lab 1: Database Creation & Basic Queries
  1. labs/foundations/inclass-exercise.html: Workers Database
  1. Lab 2: Advanced Queries & Joins

For Intermediate Users

If you already know basic SQL:

  1. Lab 7: CASE Expressions
  2. Lab 8: Window Functions
  3. Lab 9: Views

For Advanced Users

Jump to these advanced topics:

  1. Lab 16: Triggers & Stored Procedures
  2. Lab 17: Transaction Management
  3. Lab 20: MongoDB (NoSQL)
  4. Lab 21: Neo4j (Graph Database)

Lab Navigation

Lab Structure

Each lab follows this structure:

Lab Categories

📘 Foundational Labs (Weeks 1-4)

📗 Intermediate Labs (Weeks 5-8)

📙 Advanced SQL (Weeks 9-12)

📕 Programming Integration (Weeks 13-14)

📓 Modern Databases (Weeks 15-16)

Troubleshooting

Common Issues

1. "Access denied for user"

Problem: Can't connect to MySQL Solution:

# Reset MySQL root password
sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword';
FLUSH PRIVILEGES;

2. "Can't connect to local MySQL server"

Problem: MySQL service not running Solution:

# Start MySQL service
sudo systemctl start mysql  # Linux
brew services start mysql   # macOS

3. "Unknown database"

Problem: Database doesn't exist Solution:

-- Create the database first
CREATE DATABASE your_database_name;
USE your_database_name;

4. "Table doesn't exist"

Problem: Trying to query a table that hasn't been created Solution:

5. Jupyter Notebook can't connect to MySQL

Problem: Jupyter can't find MySQL connection Solution:

# Install required packages
!pip install pymysql ipython-sql

# Load SQL extension
%load_ext sql

# Connect to MySQL
%sql mysql+pymysql://username:password@localhost/database_name

Getting Help

Additional Resources

Learning Platforms

Documentation

Video Tutorials

Books

Practice Datasets

The data/ directory contains several datasets for practice:

Next Steps

Once you've completed the setup:

  1. ✅ Start with Lab 1: Basic Queries
  2. ✅ Complete the labs/foundations/inclass-exercise.html
  3. ✅ Progress through labs sequentially
  4. ✅ Practice with the provided datasets
  5. ✅ Try the challenge exercises
  6. ✅ Build your own projects using learned concepts

Happy Learning! 🚀

Need Help?