GYM MANAGEMENT SYSTEM
1) Create the tables
2) Insert a new member whose name is Pooja and provide the member ID where contact
details and address is null.
3) Display the trainers and their respective member name.
4) Provide the expiring memberships in next seven days.
5) Update the fee details as "Paid" for any member.
Creating tables
CREATE TABLE TRAINER (
T_ID INT PRIMARY KEY,
T_NAME VARCHAR(100),
T_PHN VARCHAR(15),
T_ADDRESS VARCHAR(200)
);
CREATE TABLE MEMBER (
M_ID INT PRIMARY KEY,
M_NAME VARCHAR(100),
M_CONTACT VARCHAR(15),
M_ADDRESS VARCHAR(200),
T_ID INT,
FOREIGN KEY (T_ID) REFERENCES TRAINER(T_ID)
);
CREATE TABLE FEE (
F_ID INT PRIMARY KEY,
F_DATE DATE,
F_AMOUNT DECIMAL(10,2),
STATUS VARCHAR(20),
M_ID INT,
FOREIGN KEY (M_ID) REFERENCES MEMBER(M_ID)
);
CREATE TABLE TRAINING_SCHEDULE (
SS_ID INT PRIMARY KEY,
SS_DATE DATE,
SS_TIME TIME,
SS_DURATION VARCHAR(50),
SS_DETAILS VARCHAR(200),
M_ID INT,
FOREIGN KEY (M_ID) REFERENCES MEMBER(M_ID)
);
Inserting tuples
INSERT INTO TRAINER VALUES (1, 'Ravi', '9876543210', 'Hassan');
INSERT INTO TRAINER VALUES (2, 'Anita', '9123456780', 'Bangalore');
INSERT INTO TRAINER VALUES (3, 'Kiran', '9988776655', 'Mysore');
INSERT INTO TRAINER VALUES (4, 'Meena', '9090909090', 'Hassan');
SELECT * FROM TRAINER;
+------+---------+------------+-----------+
| T_ID | T_NAME | T_PHN | T_ADDRESS |
+------+---------+------------+-----------+
|1 | Ravi | 9876543210 | Hassan |
|2 | Anita | 9123456780 | Bangalore |
|3 | Kiran | 9988776655 | Mysore |
|4 | Meena | 9090909090 | Hassan |
+------+---------+------------+-----------+
INSERT INTO MEMBER VALUES (102, 'Rahul', '8888888888', 'Bangalore', 2);
INSERT INTO MEMBER VALUES (103, 'Sneha', '7777777777', 'Mysore', 3);
INSERT INTO MEMBER VALUES (104, 'Arjun', '6666666666', 'Hassan', 4);
INSERT INTO MEMBER VALUES (105, 'Divya', '9999999999', 'Mangalore', 1);
SELECT * FROM MEMBER;
+------+---------+------------+------------+------+
| M_ID | M_NAME | M_CONTACT | M_ADDRESS | T_ID |
+------+---------+------------+------------+------+
| 101 | Pooja | NULL | NULL |1 |
| 102 | Rahul | 8888888888 | Bangalore | 2 |
| 103 | Sneha | 7777777777 | Mysore |3 |
| 104 | Arjun | 6666666666 | Hassan |4 |
| 105 | Divya | 9999999999 | Mangalore | 1 |
+------+---------+------------+------------+------+
INSERT INTO FEE VALUES (202, '2026-05-15', 2000, 'Paid', 102);
INSERT INTO FEE VALUES (203, '2026-05-18', 1800, 'Unpaid', 103);
INSERT INTO FEE VALUES (204, '2026-06-01', 2200, 'Paid', 104);
INSERT INTO FEE VALUES (205, '2026-05-25', 1600, 'Unpaid', 105);
SELECT * FROM FEE;
+------+------------+----------+---------+------+
| F_ID | F_DATE | F_AMOUNT | STATUS | M_ID |
+------+------------+----------+---------+------+
| 202 | 2026-05-15 | 2000.00 | Paid | 102 |
| 203 | 2026-05-18 | 1800.00 | Unpaid | 103 |
| 204 | 2026-06-01 | 2200.00 | Paid | 104 |
| 205 | 2026-05-25 | 1600.00 | Unpaid | 105 |
+------+------------+----------+---------+------+
INSERT INTO TRAINING_SCHEDULE VALUES (302, '2026-05-15', '08:00:00', '2 hours',
'Strength Training', 102);
INSERT INTO TRAINING_SCHEDULE VALUES (303, '2026-05-16', '06:30:00', '1.5
hours', 'Yoga', 103);
INSERT INTO TRAINING_SCHEDULE VALUES (304, '2026-05-17', '07:30:00', '1 hour',
'CrossFit', 104);
INSERT INTO TRAINING_SCHEDULE VALUES (305, '2026-05-18', '07:00:00', '1 hour',
'Pilates', 105);
SELECT * FROM TRAINING_SCHEDULE;
+-------+------------+----------+-------------+--------------------+------+
| SS_ID | SS_DATE | SS_TIME | SS_DURATION | SS_DETAILS | M_ID |
+-------+------------+----------+-------------+--------------------+------+
| 302 | 2026-05-15 | 08:00:00 | 2 hours | Strength Training | 102 |
| 303 | 2026-05-16 | 06:30:00 | 1.5 hours | Yoga | 103 |
| 304 | 2026-05-17 | 07:30:00 | 1 hour | CrossFit | 104 |
| 305 | 2026-05-18 | 07:00:00 | 1 hour | Pilates | 105 |
+-------+------------+----------+-------------+--------------------+------+
Solution to query
1)
INSERT INTO MEMBER (M_ID, M_NAME, M_CONTACT, M_ADDRESS, T_ID)
VALUES (101, 'Pooja', NULL, NULL, 1);
2)
SELECT T.T_NAME AS Trainer, M.M_NAME AS Member
FROM TRAINER T
JOIN MEMBER M ON T.T_ID = M.T_ID;
+---------+--------+
| Trainer | Member |
+---------+--------+
| Ravi | Pooja |
| Anita | Rahul |
| Kiran | Sneha |
| Meena | Arjun |
| Ravi | Divya |
+---------+--------+
3)
SELECT M.M_NAME, F.F_DATE
FROM MEMBER M
JOIN FEE F ON M.M_ID = F.M_ID
WHERE F.F_DATE BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL
7 DAY;
+--------+------------+
| M_NAME | F_DATE|
+--------+------------+
| Rahul | 2026-05-15 |
| Sneha | 2026-05-18 |
+--------+------------+
4)
UPDATE FEE
SET STATUS = 'Paid'
WHERE M_ID = 101;