Newbie: SQL Query Help

• tempdownloads

  SSC Enthusiast

  Points: 189

  More actions

  October 25, 2018 at 1:18 am

  #409149

  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;

  2. 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;

  3. 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;

  4. 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

  GROUP BY t.athleteID

  WHERE w.Workout_Hours = null;

  5. 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;

  6. 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

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 25, 2018 at 2:30 am

  #2011084

  Direct answers to duplicate here: https://www.sqlservercentral.com/Forums/2006233/Help-with-SQL-Queries

  Thom~

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