Help with SQL Queries

  • tempdownloads

    SSC Enthusiast

    Points: 159

    Can anyone help with this question please?:

    Create a table to show the aggregate workout hours for each “Hard” and “Basic” level of workout endurance by day:


    CREATE TABLE athlete_table (athleteID VARCHAR(3), endurance_group VARCHAR(5));
    INSERT INTO athlete_table VALUES (‘201’,’Hard');
    INSERT INTO athlete_table VALUES (‘202’,’Basic');
    INSERT INTO athlete_table VALUES (‘203’,’Hard');


    CREATE TABLE workout_table (date_ VARCHAR(4), athleteID VARCHAR(3), Workout_Hours INT);
    INSERT INTO workout_table VALUES ('12/3','201','54');
    INSERT INTO workout_table VALUES ('12/4','203','19');
    INSERT INTO workout_table VALUES ('12/7','201','32');


    CREATE TABLE track_table (date_ VARCHAR(4), athleteID VARCHAR(3));
    INSERT INTO track_table VALUES ('12/1','201');
    INSERT INTO track_table VALUES ('12/3','201');
    INSERT INTO track_table VALUES ('12/3','202');
    INSERT INTO track_table VALUES ('12/4','201');
    INSERT INTO track_table VALUES ('12/4','203');
    INSERT INTO track_table VALUES ('12/5','202');


    CREATE TABLE date_table (date_ VARCHAR(4));
    INSERT INTO date_table VALUES (’12/1’);
    INSERT INTO date_table VALUES (’12/2’);
    INSERT INTO date_table VALUES (’12/3’);
    INSERT INTO date_table VALUES (’12/4’);
    INSERT INTO date_table VALUES (’12/5’);
    INSERT INTO date_table VALUES (’12/6’);

    Here's what my query:


    SELECT d.date_, SUM(w.workout_hours) AS ‘Total_Workout_High’, SUM(w.workout_hours) AS ‘Total_Workout_Low’
    FROM workout_table w
    JOIN
    athlete_table a ON w.athleteID = a.athleteID
    JOIN
    date_table d ON w.date_ = d.date_
    WHERE a.Endurance_Group = 'Hard', a.Endurance_Group = 'Basic'
    GROUP BY d.date_;

  • tempdownloads

    SSC Enthusiast

    Points: 159

    I think i got it. 

    Can someone please double check?


    SELECT d.date_, SUM(w.workout_hours) AS Total_Workout_High, SUM(w.workout_hours) AS Total_Workout_Low
        FROM workout_table w
    JOIN
    athlete_table a ON w.athleteID = a.athleteID
    JOIN
    date_table d ON w.date_ = d.date_
        WHERE
        a.endurance_group = 'Hard' OR
        a.endurance_group = 'Basic'
        GROUP BY d.date_;

  • Thom A

    SSC Guru

    Points: 98029

    tempdownloads - Thursday, October 25, 2018 10:21 PM

    I think i got it. 

    Can someone please double check?


    SELECT d.date_, SUM(w.workout_hours) AS Total_Workout_High, SUM(w.workout_hours) AS Total_Workout_Low
        FROM workout_table w
    JOIN
    athlete_table a ON w.athleteID = a.athleteID
    JOIN
    date_table d ON w.date_ = d.date_
        WHERE
        a.endurance_group = 'Hard' OR
        a.endurance_group = 'Basic'
        GROUP BY d.date_;

    Data for 12/7 (these should still be dates) appears to be missing; I assume it should be included? Should you also be providing data for day where there are no work outs?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply