[Monday, Mar 27, 9:35] > Regarding the HW8, I believe you mentioned that we won't be able to do some > sub-exercise(s) due to the fact that we're running behind. If this is > correct, would you please inform us about it? > > I'm asking this because I have a problem with e) in that I have to limit the > output for those students who have solved Homework 1-5. My understanding is > that the query should print out the points for those who completed all five > homework. Is this correct? Yes, the result should only be printed for those students who have completed all five homeworks. The sum of the points would not be comparable if a student has not yet submitted all these homeworks and still has the chance to submit them late (with maybe some penality). Yes, you need to understand the HAVING clause to answer e). Actually, you could solve it with five tuple variables, but this is too much effort. You can submit e) next week, but if you read in the handouts or the textbook about HAVING, you can solve it already. ------------------------------------------------------------------------------- [Monday, Mar 27, 19:15] > I have a question about the part c) of the homework #8. > " How many students have done any homework?"------ Does this mean > " print out students who did all of the 5 questions or who did at least > one of the questions? " It means at least one homework (if homework 6 etc. is in the database, it would also count). I am sorry for the unclear formulation. ------------------------------------------------------------------------------- [Tuesday, Mar 28, 17:45] > I dont know if you are already aware of this, but the same table is called > Category on the HW and Categories in the sql code. I was following the HW > nomenclature and ended up with a table does not exist error. Thank you for your email. I made this change because all other table names are in plural. But I should have made some notice of the change. So you get 1% of extra credit because this would normally have been simply an error. ------------------------------------------------------------------------------- [Tuesday, Mar 28, 18:00] > On HW8, stud_id = 3127 has 6.5 points on teh 2nd exercise of teh > midterm, but the max_points are only 6. Also, I do not understand what > part c is asking, is it to get the number of students who have turned in > atleast one homework or do you want like a parameter query with the input > parameter being the homework number. I wasnt' able to attend last week > and you probably clarified some of these points then, sorry to be > bothering you with all these questions. Thanks. I am sorry for the unclear formulations. I didn't notice that somebody got extra credit for Exercise 2 in the midterm exam. So the best solution is probably to find all students who got at least the official full points (i.e. MAX_POINTS). We will also accept solutions where = is used (i.e. exactly MAX_POINTS), although in the presence of extra credit, this is probably not what was intended. In c) you should find the number of students who have done at least one homework. ------------------------------------------------------------------------------- [Tuesday, Mar 28, 18:15] > Would you please clarify. Question a states "print the students who got max > points on the Midterm exercise two. Should the query take into account the > possibility of extra credit? > eg: > select distinct s.first_name, s.last_name > from students s, results r, exercises e > where s.stud_id=r.stud_id > and e.ex_no=r.ex_no > and r.ex_no=2 and r.cat='M' and r.points>= e.max_points; outputs 11 names > > should r.point>=e.max_points to take into account the possiblity of extra > credit? I am sorry for the unclear formulation. I forgot about the possibility of extra credit. Yes, it is better to write >= instead of = here (it is more what I intended when I wrote the exercise), but we will accept = also. By the way, your SQL query seems to miss one condition for e. In the example, it does not influence the result, but you cannot assume this. > also, > > > Question e. > > Do you want the Total for the summation of students? Or do you want the > total for each individual student? I want the total for each individual student, but only considering students who have solved all five exercises. You more or less need the HAVING clause for this exercise (read the notes a bit ahead, or read the textbook, or submit e) next week). ------------------------------------------------------------------------------- [Tuesday, Mar 28, 18:20] > I am having trouble with Part d of the homework. When I run my query, I > get an error. Here is what I have for my query > > select s.first_name, s.last_name, sum(r.points) > from students s, results r, > where s.stud_id = r.stud_id > and > r.cat = 'M' There is a comma after the tuple variable r in the FROM clause which must be deleted. The tuple variable declarations must be separated by commas, i.e. there must be a comma between each two, but not at the end. Also, you if you do an aggregation like sum(r.points) in the SELECT list, you are only allowed to list other attributes outside of aggregation functions, in this case s.first_name and s.last_name, if they are listed under GROUP BY. Without the GROUP BY, the aggregation is done over the entire table (or whatever is the output of FROM and WHERE). But here you want the aggregation done in smaller groups, one group for every student. I think that these two changes should solve the problem, but I can't promise that. ------------------------------------------------------------------------------- [Tuesday, Mar 28, 18:25] > I was wondering if you could answer a question about homework 8 for me: > > Does EX_NO refer to the overall number of the homework (for this week our > EX_NO > would be 8) or does EX_NO refer to an actual question on the Homework > (such as question 1: print the first and last......). EX_NO is the number of the homework sheet. In this week it would be 8 (the exercise number 5 which is printed is a typing error). I used earlier a database schema which contained subexercises, i.e. each exercise would be identified by the three attributes CAT, EX_NO, SUB_EX, e.g. 'H',8,'a' but it turned out to be impractical: The GSA didn't like to type in so much information. Also this homework becomes simpler. ------------------------------------------------------------------------------- [Thursday, Mar 30, 11:15] > I'm a little confused about part e of homework 8. When you say print all > students who have solved homeworks 1-5, do you mean students who have > gotten the max_points for those exercises? Or do you mean students that > have gotten at least 1 point? I mean all students who have records for all five homeworks in the RESULTS table. The actual points could be 0. Siripun only enters a row into the RESULTS table for a student and a homework after she graded the homework. If a student X did not yet submit a homework Y, there is no entry for X and Y in the RESULTS table. I should have made this more explicit. I was asked yesterday by several students whether they should check that POINTS IS NOT NULL. But this can never happen, it is actually declared as a NOT NULL attribute. There will be simply no matching row. ------------------------------------------------------------------------------- [Saturday, April 15, 18:40] In 8e) my intention was that you consider only students who have solved all 5 homeworks. I am sorry for the unclear wording of the exercise. The motivation is that if someone has submitted only Homework 1 to Homework 4, then his/her points are not comparable to the sum of the points for all five homeworks which other students have.