SQL Queries for Gym Management Systems

1. Create the Members Table

CREATE TABLE Members (member_id INTEGER PRIMARY KEY, name TEXT NOT NULL, joined INTEGER, membership_type TEXT, monthly_fee REAL);

2. List Members Who Joined in 2022

Retrieve member names joined in 2022, ordered alphabetically:

SELECT name FROM Members WHERE joined = 2022 ORDER BY name;

3. Basic Members Joined in 2023

SELECT name FROM Members WHERE membership_type = 'Basic' AND joined = 2023;

4. Total Monthly Gym Revenue

SELECT SUM(monthly_fee) AS total_monthly_income FROM Members;

5. Monthly Revenue by Membership Type

SELECT membership_type, SUM(monthly_fee) AS total_monthly_income_per_type FROM Members GROUP BY membership_type;

6. Long Workouts (Over 60 Minutes)

SELECT activity_type FROM Workouts WHERE duration_minutes > 60;

7. Total Workout Minutes per Member

SELECT member_id, SUM(duration_minutes) AS total_minutes FROM Workouts GROUP BY member_id;

8. High-Activity Members (Over 100 Minutes)

SELECT member_id, SUM(duration_minutes) AS total_minutes FROM Workouts GROUP BY member_id HAVING SUM(duration_minutes) > 100;

9. Details of High-Activity Members

SELECT name, membership_type FROM Members WHERE member_id IN (SELECT member_id FROM Workouts GROUP BY member_id HAVING SUM(duration_minutes) > 100);

10. Workout Frequency per Member

SELECT member_id, COUNT(*) AS workout_count FROM Workouts GROUP BY member_id;

11. Add a New Member

INSERT INTO Members (name, joined, membership_type, monthly_fee) VALUES ('Alex Turner', 2026, 'Standard', 39.99);

12. Frequent Gym Users (More Than Once)

SELECT member_id, COUNT(*) AS workout_count FROM Workouts GROUP BY member_id HAVING COUNT(*) > 1;

13. Details of Frequent Gym Users

SELECT name, membership_type FROM Members WHERE member_id IN (SELECT member_id FROM Workouts GROUP BY member_id HAVING COUNT(*) > 1);

14. Categorize Workouts by Duration

SELECT workout_id, workout_date, CASE WHEN duration_minutes <= 60 THEN 'short duration' WHEN duration_minutes > 60 THEN 'long duration' END AS duration_label FROM Workouts;