October 25, 2018 at 1:12 am
Hello,
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:
_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;
_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;
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;
_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;
_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;
_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
October 25, 2018 at 1:34 am
Don't post the same question across multiple forums, it will only dilute the answers.
😎
October 25, 2018 at 2:07 am
Sorry that was my mistake.
If you could or if anyone else could please assist, that would be greatly appreciated =
October 25, 2018 at 2:13 am
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.
Larnu.uk
October 25, 2018 at 2:39 am
Correct. I think it's just poorly worded. Creating tables doesn't make sense. But are instead SELECT commands.
October 25, 2018 at 2:46 am
tempdownloads - Thursday, October 25, 2018 2:39 AMCorrect. 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
October 25, 2018 at 2:47 am
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.
October 25, 2018 at 2:48 am
John Mitchell-245523 - Thursday, October 25, 2018 2:46 AMtempdownloads - Thursday, October 25, 2018 2:39 AMCorrect. 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?
October 25, 2018 at 2:55 am
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
October 25, 2018 at 4:04 am
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 :
October 25, 2018 at 4:15 am
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
October 25, 2018 at 4:22 am
tempdownloads - Thursday, October 25, 2018 4:04 AMI 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 :
- 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.
Larnu.uk
October 25, 2018 at 4:40 am
Thom A - Thursday, October 25, 2018 4:22 AMtempdownloads - Thursday, October 25, 2018 4:04 AMI 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 :
- 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?
October 25, 2018 at 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).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 25, 2018 at 11:17 am
Thom A - Thursday, October 25, 2018 4:45 AMtempdownloads - 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 17 total)
You must be logged in to reply to this topic. Login to reply