Esteban_Menchu SQL CODE
an anonymous user
·
SQL
/* Creating Fitness Club Table*/
CREATE TABLE Fitness_Club_T
(Club_ID NUMBER (9) NOT NULL,
Club_Name VARCHAR (100) NOT NULL
CHECK (Club_Name IN ('Athletic Club', 'Supreme Sports Club', 'Columbia Gym', 'Hobbits Glen Golf Club', 'Splash Club')),
Club_Address VARCHAR (50) NOT NULL,
CONSTRAINT Club_ID_PK5 PRIMARY KEY (Club_ID));
/*Create Fitness Class Table */
CREATE TABLE Fitness_Class_T
(Class_ID NUMBER (9) NOT NULL,
Club_ID NUMBER (9) NOT NULL,
Class_Name VARCHAR (50) NOT NULL,
Class_Type VARCHAR (25) NOT NULL
CHECK (Class_Type IN ('Aqua', 'HIIT', 'Dance', 'LesMills', 'Golf')),
Class_Duration_Mins INTEGER NOT NULL,
Xtra_Fee DECIMAL (9,2),
Class_Day VARCHAR (10) NOT NULL
CHECK (Class_Day IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')),
Class_Date DATE NOT NULL,
Instructor_ID NUMBER (9) NOT NULL,
Instructor_Name VARCHAR (50) NOT NULL,
CONSTRAINT Class_ID_PK PRIMARY KEY (Class_ID),
CONSTRAINT Instructor_ID_FK FOREIGN KEY (Instructor_ID) REFERENCES Instructor_T (Instructor_ID),
CONSTRAINT Club_ID_FK6 FOREIGN KEY (Club_ID) REFERENCES Fitness_Club_T (Club_ID));
/* Create Participant Table */
CREATE TABLE Participant_T
(Account_ID NUMBER (9) NOT NULL,
Participant_Name VARCHAR (50) NOT NULL,
Membership_Type VARCHAR (25) NOT NULL,
Club_Name VARCHAR (100) NOT NULL,
Monthly_Charge DECIMAL (9,2) NOT NULL,
CONSTRAINT Account_ID_PK PRIMARY KEY (Account_ID));
/* Create Instructor Table*/
CREATE TABLE Instructor_T
(Instructor_ID NUMBER (9) NOT NULL,
Instructor_Name VARCHAR (25) NOT NULL,
Class_Type1 VARCHAR (25),
Class_Type2 VARCHAR (25),
Class_Type3 VARCHAR (25),
CONSTRAINT Instructor_ID_PK2 PRIMARY KEY (Instructor_ID));
/*Create Membership Table*/
CREATE TABLE Membership_T
(Membership_Type VARCHAR (25) NOT NULL,
Membership_Cost NUMBER (10,2) NOT NULL,
Club_ID NUMBER (9) NOT NULL,
Club_Name VARCHAR (50) NOT NULL,
CONSTRAINT Membership_Type_PK PRIMARY KEY (Membership_Type)
CONSTRAINT Club_ID_FK FOREIGN KEY (Club_ID) REFERENCES Fitness_Club_T (Club_ID));
/*Insert Values into Fitness Club Table*/
INSERT INTO Fitness_Club_T VALUES (50001, 'Athletic Club', '5435 BeaverKill Rd, Columbia, MD 21044');
INSERT INTO Fitness_Club_T VALUES (50002, 'Supreme Sports Club', '7080 Deepage Dr., Columbia MD 21045');
INSERT INTO Fitness_Club_T VALUES (50003, 'Columbia Gym', '6151 Day Long Ln., Columbia, MD 21029');
INSERT INTO Fitness_Club_T VALUES (50007, 'Hobbits Glen Golf Club', '11130 Willow Bottom Drive, Columbia, MD 21044');
INSERT INTO Fitness_Club_T VALUES (50009, 'Splash Club', '1254 Crystal Drive, Columbia, MD 21045');
/*Insert Values into Membership Table*/
INSERT INTO Membership_T VALUES ('CA Fit & PLay', 122, 50002, 'Supreme Sports Club');
INSERT INTO Membership_T VALUES ('1Fit', 84, 50003, 'Columbia Gym');
INSERT INTO Membership_T VALUES ('1Play', 40, 50001, 'Athletic Club');
INSERT INTO Membership_T VALUES ('Golf Fit & Play', 200, 50007, 'Hobbits Glen Golf Club');
INSERT INTO Membership_T VALUES ('Splash Play', 100, 50009, 'Splash Club');
/*Insert Values into Instructor Table*/
INSERT INTO Instructor_T VALUES (66654, 'Menchu Esteban', 'LesMills', 'Dance', 'Aqua');
INSERT INTO Instructor_T VALUES (75488, 'Jennifer Lopez', 'LesMills', 'HIIT', 'Dance');
INSERT INTO Instructor_T VALUES (75489, 'Mariah Carey', 'Golf', 'HIIT', 'Aqua');
INSERT INTO Instructor_T VALUES (78456, 'Elvis Presley', 'HIIT', 'Dance', 'LesMills');
INSERT INTO Instructor_T VALUES (54879, 'Oprah Winfrey ', 'HIIT', 'Dance', 'Golf');
INSERT INTO Instructor_T VALUES (68957, 'Chicho Machuchi ', 'LesMills', 'HIIT', 'Dance');
/*Insert Values into Fitness Class Table*/
INSERT INTO Fitness_Class_T VALUES (200012, 50001, 'Aqua Fitness', 'Aqua', 60, 0, 'Thursday', '05/05/2022', 68957, 'Chicho Machuchi');
INSERT INTO Fitness_Class_T VALUES (200014, 50003, 'Aqua Spin', 'Aqua', 60, 10, 'Friday', '05/13/2022', 66654, 'Menchu Esteban');
INSERT INTO Fitness_Class_T VALUES (300011, 50002, 'Strong Nation', 'HIIT', 45, 40, 'Sunday', '05/08/2022', 75489, 'Mariah Carey');
INSERT INTO Fitness_Class_T VALUES (300007, 50001, 'Insanity', 'HIIT', 120, 0, 'Saturday', '05/07/2022', 78456, 'Elvis Pressley');
INSERT INTO Fitness_Class_T VALUES (400022, 50001, 'LaBlast', 'Dance', 60, 0, 'Thursday', '05/05/2022', 75488, 'Jennifer Lopez');
INSERT INTO Fitness_Class_T VALUES (400026, 50003, 'LaBlast', 'Dance', 60, 0, 'Thursday', '05/05/2022', 54879, 'Oprah Winfrey');
INSERT INTO Fitness_Class_T VALUES (400023, 50003, 'Zumba', 'Dance', 60, 0, 'Wednesday', '05/11/2022', 68957, 'Chicho Machuchi');
INSERT INTO Fitness_Class_T VALUES (700054, 50003, 'BodyPump', 'LesMills', 60, 0, 'Friday', '05/06/2022', 75488, 'Jennifer Lopez');
INSERT INTO Fitness_Class_T VALUES (700050, 50002, 'BodyPump', 'LesMills', 45, 0, 'Sunday', '05/08/2022', 66654, 'Menchu Esteban');
INSERT INTO Fitness_Class_T VALUES (800001, 50007, 'Golf For Life', 'Golf', 180, 50, 'Saturday', '05/14/2022', 54879, 'Oprah Winfrey');
/*Inserting Rows to Participant Table*/
INSERT INTO Participant_T VALUES (81401, 'Armando Bronca Segura', '1Fit','Columbia Gym', 84);
INSERT INTO Participant_T VALUES (80245, 'Carmelo Cotton', '1Fit', 84, 'Supreme Sports Club');
INSERT INTO Participant_T VALUES (80678, 'Dolores Fuertes', 'Golf Fit & PLay', 'Hobbits Glen Club', 200);
INSERT INTO Participant_T VALUES (70999, 'Jaime Tralleta', 'CA Fit & Play', 'Supreme Sports Club', 122);
INSERT INTO Participant_T VALUES (71056, 'Ester Colero ', '1Play', 'Athletic Club', 40);
INSERT INTO Participant_T VALUES (80325, 'Jonny Melavo', 'Golf Fit & PLay', 'Hobbits Glen Club', 200);
INSERT INTO Participant_T VALUES (84527, 'Pancho Colate', 'CA Fit & Play', 'Supreme Sports Club', 122);
INSERT INTO Participant_T VALUES (85694, 'Martirio Penitencia', '1Fit', 'Columbia Gym', 84);
/*Create a couple of daily tables for members participation per class*/
CREATE TABLE Class200012_T
(MemberCount INTEGER NOT NULL,
Account_ID NUMBER (9) NOT NULL,
Participant_Name VARCHAR (50) NOT NULL,
Instructor_ID NUMBER (9) NOT NULL,
Instructor_Name VARCHAR (25) NOT NULL,
CONSTRAINT MemberCount_PK PRIMARY KEY (MemberCount));
CREATE TABLE Class700050_T
(MemberCount INTEGER NOT NULL,
Account_ID NUMBER (9) NOT NULL,
Participant_Name VARCHAR (50) NOT NULL,
Instructor_ID NUMBER (9) NOT NULL,
Instructor_Name VARCHAR (25) NOT NULL,
CONSTRAINT MemberCount_PK3 PRIMARY KEY (MemberCount));
/* Insert Rows into these two tally tables*/
INSERT INTO Class200012_T VALUES (1,70999, 'Jaime Tralleta', 68957, 'Chicho Machuchi');
INSERT INTO Class200012_T VALUES (2,71056, 'Ester Colero', 68957, 'Chicho Machuchi');
INSERT INTO Class200012_T VALUES (3,80325, 'Jonny Melavo', 68957, 'Chicho Machuchi');
INSERT INTO Class200012_T VALUES (4,81401, 'Armando Bronca Segura', 68957, 'Chicho Machuchi');
INSERT INTO Class200012_T VALUES (5,84527, 'Pancho Colate', 68957, 'Chicho Machuchi');
INSERT INTO Class700050_T VALUES (1,70999, 'Jaime Tralleta', 66654, 'Menchu Esteban ');
INSERT INTO Class700050_T VALUES (2,71056, 'Ester Colero', 66654, 'Menchu Esteban ');
INSERT INTO Class700050_T VALUES (3,80325, 'Jonny Melavo', 66654, 'Menchu Esteban ');
INSERT INTO Class700050_T VALUES (4,80245, 'Carmelo Cotton', 66654, 'Menchu Esteban ');
INSERT INTO Class700050_T VALUES (5,80678, 'Dolores Fuertes', 66654, 'Menchu Esteban ');
INSERT INTO Class700050_T VALUES (6,81401, 'Armando Bronca Segura', 66654, 'Menchu Esteban ');
/* INNER JOIN QUERY Classes by Location*/
SELECT Fitness_Class_T.Club_ID, Class_Name, Club_Name
FROM Fitness_Class_T INNER JOIN Fitness_Club_T ON
Fitness_Class_T.Club_ID = Fitness_Club_T.Club_ID
ORDER BY Fitness_Club_T.Club_Name;
/* LEFT OUTER JOIN QUERY Classes by Location, Date and Day of the week*/
SELECT Fitness_Class_T.Club_ID, Fitness_Class_T.Class_Name, Class_Day, Class_Date, Fitness_Club_T.Club_Name
FROM Fitness_Class_T LEFT OUTER JOIN Fitness_Club_T
ON Fitness_Class_T.Club_ID = Fitness_Club_T.Club_ID;
/*Select Query Teachers that do Dance Classes*/
SELECT Instructor_ID,Instructor_Name
FROM Instructor_T
WHERE Class_Type1= 'Dance' OR Class_Type2 = 'Dance' OR Class_Type3 = 'Dance';
/* GROUP BY: Type of membership sold*/
SELECT Membership_Type, COUNT (Monthly_Charge)
FROM Participant_T
WHERE Membership_Type = '1Fit'
GROUP BY Membership_Type
ORDER BY Membership_Type;
/*Select Query for Membership research */
SELECT Membership_Type, Membership_Cost
FROM Membership_T
WHERE Membership_Cost < 125;
/*How many classes is Menchu Esteban teaching? */
SELECT COUNT (*)AS "Menchu's Classes"
FROM Fitness_Class_T
WHERE Instructor_ID = 66654;
/* Query to search classes types and duration */
SELECT Class_Name, Class_Type, Class_Duration_Mins
FROM Fitness_Class_T
WHERE Class_Name LIKE 'Aqua%'
OR Class_Name LIKE '%Nation'
AND Class_Duration_Mins > 45;
/* GROUP BY: How many classes available of the different types of class formats */
SELECT Class_Type, COUNT (Class_Type)
FROM Fitness_Class_T
GROUP By Class_Type
ORDER By Class_Type;
/*Equi-Join Query For classes by day of the week with type of class and name of isntructor*/
SELECT Fitness_Class_T.Instructor_ID, Instructor_T.Instructor_ID, Class_Name, Class_Day, Instructor_T.Instructor_Name
FROM Fitness_Class_T, Instructor_T
WHERE Fitness_Class_T.Instructor_ID = Instructor_T.Instructor_ID
ORDER BY Class_Day;
/*UNION JOIN Of participants in two classes */
SELECT Membercount, Account_ID, Participant_Name, Instructor_Name
FROM Class200012_T
UNION ALL
SELECT Membercount, Account_ID, Participant_Name, Instructor_Name
FROM Class700050_T
ORDER BY Participant_Name;
/*LEFT OUTER JOIN for dates by instructor by length of class...works on mycompiler, but not in sql developer*/
SELECT Instructor_T.Instructor_ID, Fitness_Class_T.Instructor_ID, Class_Date, Class_Duration_Mins, Fitness_Class_T.Instructor_Name
FROM Fitness_Class_T LEFT OUTER JOIN Instructor_T
ON Instructor_T.Instructor_ID = Fitness_Class_T.Instructor_ID
GROUP BY Fitness_Class_T.Instructor_Name;
/* Inner Join to test another GROUP BY expression...It works on mycompliler, but not on sql developer*/
SELECT Fitness_Club_T.Club_ID, Membership_T.Club_ID, Fitness_Club_T.Club_Name, Club_Address, Membership_Type, Membership_Cost
FROM Fitness_Club_T INNER JOIN Membership_T
ON Fitness_Club_T.Club_ID = Membership_T.Club_ID
GROUP BY Membership_T.Club_ID;
/*UPDATE Statements to change a gym member mebership type for Martirio Penitencia from 1Fit/Columbia gym/84 to 1Play/Athletic Club/40 */
UPDATE Participant_T SET
Membership_Type = '1Play'
WHERE Participant_Name = 'Martirio Penitencia';
UPDATE Participant_T SET
Club_Name = 'Athletic Club'
WHERE Participant_Name = 'Martirio Penitencia';
UPDATE Participant_T SET
Monthly_Charge = 40
WHERE Participant_Name = 'Martirio Penitencia';
/* SELECT Query to make sure change was made */
SELECT Participant_Name, Membership_Type, Club_Name, Monthly_Charge
FROM Participant_T
WHERE Participant_Name = 'Martirio Penitencia';
/* Update Statement to change all classes with $0 extra fee to $25 */
UPDATE Fitness_Class_T SET
Xtra_Fee = 25
WHERE Xtra_Fee = 0;
/* Testing the change via SELECT Query*/
SELECT *
FROM Fitness_Class_T;
/*Update Statement to update the name of a class from LaBlass to LaBlass Ultra*/
UPDATE Fitness_Class_T SET
Class_Name = 'LaBlast Ultra'
WHERE Class_Name = 'LaBlast';
/*Testing the update statement to make sure change was made*/
SELECT Class_Name, Class_Type
FROM Fitness_Class_T
WHERE Class_Type = 'Dance';
Output
(Run the program to view its output)
Comments