SQL Testing & Ticket Resolution Mini-Project

A hands-on, realistic mini-project that simulates IT support workflows across Analyst Trainee, Application Support, IT Operations, and Technical Support roles. You will diagnose data issues, perform root cause analysis (RCA), apply fixes, validate with test cases, and document findings—mirroring real-world production support.

Overview

• Reproducible SQL environment with intentional data issues
• Tickets that represent real support incidents
• Diagnostic queries to detect anomalies
• Fix scripts with safe corrections
• Verification queries and test cases to confirm resolution
• Documentation for RCA and future improvements

Roles Simulated

• Analyst Trainee: Run diagnostics, document findings, follow SOPs
• Application Support: Investigate customer/order issues, apply controlled fixes
• IT Operations: Ensure data integrity, enforce constraints, maintain procedures
• Technical Support: Communicate impact, reproduce issues, verify resolutions

Technologies Used

• MySQL/MariaDB (XAMPP environment friendly)
• SQL DDL/DML
• Markdown for tickets and documentation

Folder Structure

sql-testing-ticket-resolution-mini-project/
├─ README.md
├─ database/
│  ├─ schema.sql
│  ├─ sample_data.sql
│  └─ constraints_and_procedures.sql
├─ tickets/
│  ├─ ticket_1_inactive_customer.md
│  ├─ ticket_2_negative_amount.md
│  ├─ ticket_3_status_mismatch.md
│  └─ summary_all_tickets.md
├─ queries/
│  ├─ diagnostic_queries.sql
│  ├─ fix_queries.sql
│  └─ verification_queries.sql
├─ test_cases/
│  ├─ test_cases.md
│  ├─ tc_ticket_1.md
│  ├─ tc_ticket_2.md
│  └─ tc_ticket_3.md
└─ documentation/
   ├─ project_overview.md
   ├─ rca_notes.md
   └─ future_improvements.md

How to Run

1. Ensure MySQL/MariaDB is running (XAMPP: start MySQL).
2. Create and populate schema:
   • Option A: phpMyAdmin → Import database/schema.sql, then database/sample_data.sql
   • Option B: CLI

     mysql -u root -p < database/schema.sql
     mysql -u root -p < database/sample_data.sql

3. Inspect issues:
   • Run queries/diagnostic_queries.sql to list current anomalies.
4. Add guardrails and normalization:
   • Run database/constraints_and_procedures.sql to add a non-negative CHECK constraint on orders.amount and create normalize_order_statuses() procedure.
5. Apply fixes:
   • Run queries/fix_queries.sql to correct negative amounts, cancel completed orders for inactive customers, and normalize statuses.
6. Verify:
   • Run queries/verification_queries.sql to confirm no anomalies remain.
7. Review tickets and documentation:
   • See tickets/ and documentation/ for RCA and future work.

Skills Learned

• Writing DDL for robust schemas
• Crafting diagnostic SQL to detect data integrity issues
• Performing RCA and communicating impact
• Designing stored procedures for data normalization
• Applying targeted UPDATEs safely
• Writing verification queries and test cases

Author

• Project: SQL Testing & Ticket Resolution Mini-Project
• Maintainer: Your Name (replace in GitHub)
• License: MIT (optional)