Help with SQL Queries

  • tempdownloads

    SSC Enthusiast

    Points: 159

    Hello,

    I'm hoping experienced SQL professionals can help me with 6 queries I wrote for the 6 questions below please?
    Example tables for the questions:

    Tables:

    Workout: all workout hours by date and athlete

    _Date

    AthleteID

    Workout_Hours

    12/3

    201

    54

    12/4

    203

    19

    12/7

    201

    32

    Track Usage: all track usage records by date and athlete:

    _Date

    AthleteID

    12/1

    201

    12/3

    201

    12/3

    202

    12/4

    201

    12/4

    203

    12/5

    202

    Date: each date has a row:

    _Date

    12/1

    12/2

    12/3

    12/4

    12/5

    12/6

    Athlete: each athlete falls will be either ‘hard’ or ‘basic’ in terms of level of endurance captured by “Endurance_Group”

    AthleteID

    Endurance_Group

    201

    Hard

    202

    Basic

    203

    Hard

    Questions

    Please write an SQL query for each of the following:

    1. Create a table to display total workout hours & number of athletes by day

    _Date

    Workout_Hours

    Num_Athletes

    SELECT d._date(*), SUM(w.workout_hours), COUNT(w.athleteID)

    FROM

    date_table d,

    workout_table w

    WHERE

    d._date = w._date

    GROUP BY d._date;

    1. Create a table to display the top ranked athlete on each day

    _Date

    athleteID

    SELECT d._date(*), TOP 1 w.athleteID FROM workout_table p, date_table d

    WHERE

    d._date = w._date

    GROUP BY d._date

    ORDER BY w.workout_hours desc;

    1. A distribution of number of days the track was used to train: Create a table showing the athlete count (number of applicable athletes) that used the track by the number of training days.

    training_days

    Num_Athletes

    1

    2

    3

    4

    5

    6

    7

    SELECT t.training_days(*), COUNT(t.athleteID) AS ‘training_days' FROM Active_table a, visitor_table v

    WHERE

    a.training_days = v.training_days

    GROUP BY v._date;

    1. Create a table to display the athletes who worked out but didn’t use the track, on each day

    _Date

    athleteID

    SELECT t._date, t.athleteID FROM track_usage_table t, workout_table 2
    WHERE w.Workout_Hours is NULL
    GROUP BY t.athleteID;

    1. Add a third column to the workout table that displays the percent each athlete’s workout hours of the total aggregate workout hours by date 

    _Date

    athleteID

    Workout_Hours

    aggregate_hours

    SELECT w._date, w.athleteID, w.Workout_Hours, (w.Workout_Hours / SUM(w.Workout_Hours (*))) AS ‘aggregate_hours'

    FROM workout_table w

    GROUP BY w.athleteID;

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

    _Date

    Total_Workout_High

    Total_Workout_Low

    SELECT w._date, SUM(w.workout_hours) AS ‘Total_Workout_High’, SUM(w.workout_hours) AS ‘Total_Workout_Low’

    FROM workout_table w, athlete_table a

    WHERE a.Endurance_Group = 'Hard', a.Endurance_Group = 'Basic'

    JOIN a.athleteID ON w.athleteID;

    Thanks

  • Eirikur Eiriksson

    SSC Guru

    Points: 182321

    Don't post the same question across multiple forums, it will only dilute the answers.
    😎

  • tempdownloads

    SSC Enthusiast

    Points: 159

    Sorry that was my mistake.

    If you could or if anyone else could please assist, that would be greatly appreciated =

  • Thom A

    SSC Guru

    Points: 98023

    This looks like you're asking for homework validation. Have you spoken to your tutor? Also, all the questions state "Create a table/column", however, every solution you've provided are SELECT statements to return datasets; they aren't creating any objects. To create a table you would use the CREATE TABLE syntax. This could, however, be poorly worded questions, but to me "create a table" means "CREATE TABLE" and "Add a third column to the...table" means "ALTER TABLE

    ADD..." (but creating the tables here makes no sense).

    Thom~

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

  • tempdownloads

    SSC Enthusiast

    Points: 159

    Correct.  I think it's just poorly worded.  Creating tables doesn't make sense.  But are instead SELECT commands.

  • John Mitchell-245523

    SSC Guru

    Points: 148218

    tempdownloads - Thursday, October 25, 2018 2:39 AM

    Correct.  I think it's just poorly worded.  Creating tables doesn't make sense.  But are instead SELECT commands.

    OK, understood. But you've answered the questions.  What are you looking for from us?

    John

  • tempdownloads

    SSC Enthusiast

    Points: 159

    If you could help me with question 3 in particular please?

    A distribution of # days active within a week: Create a table to show how many athletes used the track (training days) for 1 day, 2 days, 3 days,… 7 days during 12/1 - 12/7.

  • tempdownloads

    SSC Enthusiast

    Points: 159

    John Mitchell-245523 - Thursday, October 25, 2018 2:46 AM

    tempdownloads - Thursday, October 25, 2018 2:39 AM

    Correct.  I think it's just poorly worded.  Creating tables doesn't make sense.  But are instead SELECT commands.

    OK, understood. But you've answered the questions.  What are you looking for from us?

    John

    Are they correct?  (The SQL queries I wrote)  How would you write them?

  • John Mitchell-245523

    SSC Guru

    Points: 148218

    There appears to be a syntax error in the last query, at least.  So make sure you run them and that they return the correct results.  You will, of course, need to go to the trouble of creating the tables to do that.  You might then post the CREATE TABLE and INSERT statements for better help from the people on this forum.

    Have you really been taught to use a "'," instead of the JOIN keyword?  I would recommend that you read up on how the latter works, and use it in your queries.

    John

  • tempdownloads

    SSC Enthusiast

    Points: 159

    I could use some help on question 3 please?

    Here's the code to create the table:

    CREATE TABLE track_table (date_ VARCHAR(4), athleteID VARCHAR(1));

    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');

    Query for the question :

    1. A distribution of number of days the track was used to train: Create a table showing the athlete count (number of applicable athletes) that used the track by the number of training days.

  • John Mitchell-245523

    SSC Guru

    Points: 148218

    You've already written the query - what's wrong with it?  I'm guessing you don't know because you haven't actually tested it.  How do I know that?  The DDL and sample data you provided don't work.

    This is your homework assignment.  You need to do the hard work yourself.  By all means ask questions if there are specific things you don't understand, but general questions such as "I need help~" aren't going to get you anywhere.

    John

  • Thom A

    SSC Guru

    Points: 98023

    tempdownloads - Thursday, October 25, 2018 4:04 AM

    I could use some help on question 3 please?

    Here's the code to create the table:

    CREATE TABLE track_table (date_ VARCHAR(4), athleteID VARCHAR(1));

    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');

    Query for the question :

    1. A distribution of number of days the track was used to train: Create a table showing the athlete count (number of applicable athletes) that used the track by the number of training days.

    Those values aren't dates. They should be.  If you have data for more than one year, that's not going to provide correct results (even if this is sample data, it should still be representative). You've also declared atheleteID as a varchar(1), where as they are clearly integer values, and made up of 3 characters (so that isn't going to work at all as 3 characters do not fit into 1).

    It's important to include your attempt too:
    SELECT t.training_days(*),
           COUNT(t.athleteID) AS 'training_days'
    FROM Active_table a, visitor_table v
    WHERE a.training_days = v.training_days
    GROUP BY v._date;

    I've no idea what t,training_days(*) means. Also, like mentioned earlier you need to use JOIN syntax, not the ANSI-89 syntax. What results are you expecting here?

    I suggest having another go using JOIN syntax, and fixing the syntax error, as well as fixing the data type and providing the results you expect. Then, if it doesn't look right still, we can nudge you on the right path.

    Thom~

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

  • tempdownloads

    SSC Enthusiast

    Points: 159

    Thom A - Thursday, October 25, 2018 4:22 AM

    tempdownloads - Thursday, October 25, 2018 4:04 AM

    I could use some help on question 3 please?

    Here's the code to create the table:

    CREATE TABLE track_table (date_ VARCHAR(4), athleteID VARCHAR(1));

    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');

    Query for the question :

    1. A distribution of number of days the track was used to train: Create a table showing the athlete count (number of applicable athletes) that used the track by the number of training days.

    Those values aren't dates. They should be.  If you have data for more than one year, that's not going to provide correct results (even if this is sample data, it should still be representative). You've also declared atheleteID as a varchar(1), where as they are clearly integer values, and made up of 3 characters (so that isn't going to work at all as 3 characters do not fit into 1).

    It's important to include your attempt too:
    SELECT t.training_days(*),
           COUNT(t.athleteID) AS 'training_days'
    FROM Active_table a, visitor_table v
    WHERE a.training_days = v.training_days
    GROUP BY v._date;

    I've no idea what t,training_days(*) means. Also, like mentioned earlier you need to use JOIN syntax, not the ANSI-89 syntax. What results are you expecting here?

    I suggest having another go using JOIN syntax, and fixing the syntax error, as well as fixing the data type and providing the results you expect. Then, if it doesn't look right still, we can nudge you on the right path.

    (This seems to be a SELF JOIN?)
    SELECT t.training_days, COUNT(t.athleteID AS 'training_days' FROM track_table t
    WHERE t.training_days = training_days
    GROUP BY t.date;

    The question is asking for a table distribution with 2 columns: first column is training days 1-7.  Second column is the number of athletes that worked out by using the track (in days from first column)

    Does that make sense?

  • Thom A

    SSC Guru

    Points: 98023

    tempdownloads - Thursday, October 25, 2018 4:40 AM

    (This seems to be a SELF JOIN?)
    SELECT t.training_days, COUNT(t.athleteID AS 'training_days' FROM track_table t
    WHERE t.training_days = training_days
    GROUP BY t.date;

    The question is asking for a table distribution with 2 columns: first column is training days 1-7.  Second column is the number of athletes that worked out by using the track (in days from first column)

    Does that make sense?

    You've clearly not tested that query. If you had, you would know that that answer isn't correct, as it won't run (for several reason).

    Thom~

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

  • tempdownloads

    SSC Enthusiast

    Points: 159

    Thom A - Thursday, October 25, 2018 4:45 AM

    tempdownloads - Thursday, October 25, 2018 4:40 AM

    (This seems to be a SELF JOIN?)
    SELECT t.training_days, COUNT(t.athleteID AS 'training_days' FROM track_table t
    WHERE t.training_days = training_days
    GROUP BY t.date;

    The question is asking for a table distribution with 2 columns: first column is training days 1-7.  Second column is the number of athletes that worked out by using the track (in days from first column)

    Does that make sense?

    You've clearly not tested that query. If you had, you would know that that answer isn't correct, as it won't run (for several reason).

    Yes I'm aware it won't work.  That's what I need help with.

Viewing 15 posts - 1 through 15 (of 18 total)

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