---

SQL Development & Analytics Lab

A comprehensive repository containing SQL schemas, data manipulation scripts, analytical queries, and dataset backups. This repository demonstrates proficiency across core relational database management system (RDBMS) concepts, ranging from initial schema creation (DDL) and CRUD operations to advanced PostgreSQL features like window functions, conditional aggregations, and Python database connectivity.

---

📁 Repository Structure

sql/
├── data/                         # Raw data files and database backups
│   ├── dvdrental.tar             # PostgreSQL tar backup (Sakila sample DB)
│   ├── film_backup.csv           # Exported CSV backup of film records
│   ├── new_films.csv             # Raw data file for bulk CSV import testing
│   ├── simple_export.csv         # Exported query output sample
│   └── simple_table.csv          # Simple structured sample dataset
│
└── src/                          # SQL scripts and database interaction logic
    ├── Table_creation.sql        # Core DDL for initializing tables and schemas
    ├── shopping_mall_db.sql      # Schema setup & seed data for Shopping Mall DB
    ├── mall_reporting_queries.sql# Analytical queries for Mall DB schema
    ├── student_enrollments_db.sql# Schema setup & seed data for Academic DB
    ├── student_enrollment_queries.sql # Aggregations & JOINs on Academic DB
    ├── dvd_rental_queries.sql    # Analytical queries on the DVD Rental database
    ├── postgres_ddl_and_analytics.sql # Advanced DDL modifications & window functions
    ├── case_statements.sql       # Conditional logic & conditional aggregation
    ├── sql_basics.sql            # Core SELECT statements, filtering, and sorting
    ├── CRUD.sql                  # Data Manipulation Language (DML) operations
    ├── complex_queries.sql       # Advanced JOINs, CTEs, and multi-table analysis
    ├── datetime.sql             # Date/time arithmetic, timestamping, & extracts
    ├── import_export.sql         # Bulk data loading strategies (`\copy`, CSV import)
    └── db_connect_to_postgre.py  # Python script for PostgreSQL database access

---

🛠️ Key Topics & Features Covered

1. Database Architecture & DDL (Table_creation.sql, shopping_mall_db.sql, student_enrollments_db.sql)

• Designing normalized schemas with primary keys, foreign keys, and cascading rules.
• Structuring multi-table entities (e.g., Shops, Employees, Products, Transactions, Students, Courses, Enrollments).

2. Analytical Querying & Aggregations (mall_reporting_queries.sql, student_enrollment_queries.sql, dvd_rental_queries.sql)

• Multi-table relation mapping using INNER JOIN, LEFT JOIN, and DISTINCT.
• Summarizing metrics using aggregate functions (SUM, AVG, COUNT, MIN, MAX) combined with GROUP BY and HAVING filters.

3. Advanced PostgreSQL Functions (postgres_ddl_and_analytics.sql, datetime.sql, case_statements.sql)

• Window Functions: Analytical ranking and partitioning (RANK(), DENSE_RANK(), OVER (PARTITION BY ...)).
• Conditional Logic: Data categorization and pivot-style conditional aggregations (SUM(CASE WHEN ...)).
• Temporal Operations: Date/time arithmetic using INTERVAL, EXTRACT(), DATE_TRUNC(), and timestamp formats (TIMESTAMPTZ).

4. Bulk Operations & Programmatic Access (import_export.sql, db_connect_to_postgre.py)

• High-performance data ingestion/export using PostgreSQL native copy utilities (\copy).
• Programmatic connection management and automation via Python (psycopg2 / SQLAlchemy).

---

🚀 Getting Started

Prerequisites

• PostgreSQL (v12 or higher recommended)
• pgAdmin 4 or psql CLI
• Python 3.x (with psycopg2 or sqlalchemy for Python automation)

Database Restoration Setup

To load the DVD Rental sample database (dvdrental.tar) into your local PostgreSQL instance:

# 1. Create the target database in PostgreSQL
createdb -U postgres dvdrental

# 2. Restore the database from the .tar file inside the data directory
pg_restore -U postgres -d dvdrental data/dvdrental.tar

---