Esteban_Menchu SQL CODE

an anonymous user · May 15, 2022
/* 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

Please sign up or log in to contribute to the discussion.