[Monday, April 3, 18:45] > I'm working on part c and I have tried these two solutions: > > select * > from students s, results r > where r.stud_id = s.stud_id and r.cat = 'H' and > not exists ( select * > from results r2 > where r2.stud_id = r.stud_id and r2.cat = 'H' > and r.ex_no = r2.ex_no ) ; The join in the outer query checks that this student has submitted at least one homework (otherwise there would be no matching tuple in the results table). The inner query checks that the student has not submitted any homework (i.e. that there is no matching tuple in the results table). This is a contradiction, therefore you get no output rows. > select * > from results r > where r.cat = 'H' > and r.ex_no not in ( select ex_no > from resuts > where stud_id = r.stud_id ) ; > > > Neither query is returning any rows and I can't seem to figure out why. This is very similar: In the outer query you look at a specific tuple in the results table. Then you check that this exercise is not solved by this student, but the very existence of the tuple r shows that this is impossible. This exercise is quite difficult. The reason is that I always told you that it is almost always an error if you have no join condition, but for this exercise you actually need a cartesian product. So it should be quite natural that you need a tuple variable over students and another tuple variable over exercises in the outer query. Then you use NOT EXISTS to check that there is no matching tuple in the results table. But in the outer query, you do not access results, so there can be also no join between exercises and students. I hope this helps. ------------------------------------------------------------------------------- [Tuesday, April 4, 12:20] > Thank you so much! I've got it now: > for each student, and each exersize > ( did the student do it ) > Thank you for this nice summary. Yes, the exercise was not very clear. Actually, from the formulation For each student S For each exercise E If the S did not do E, print S and E one can already see that in the outer query one needs precisely these two tuple variables (because it fits to the naive algorithm for evaluating SQL queries). ------------------------------------------------------------------------------- [Tuesday, April 4, 20:15] > I dont understand where I am going wrong for part (e) , can you guide me > in the right direction ? > > SELECT S.FIRST_NAME, S.LAST_NAME > FROM STUDENTS S, RESULTS R, EXERCISES E > WHERE R.STUD_ID = S.STUD_ID > AND R.CAT = 'H' > AND E.CAT = 'H' > AND R.EX_NO = E.EX_No > AND ((R.POINTS)/E.MAX_POINTS) >=1; This query gives you all students who have solved at least one exercise with perfect score (and if a student solved 3 exercises with perfect score, he/she is printed three times). Exercise e) asks only for students who have solved all homeworks in the database with perfect score. So e.g. you could sum up all points this student got for homeworks, and compare it with the sum of all max_points in the exercise table (for homeworks). This would need group by, having, and a subquery. You could also use a double nested NOT EXISTS: In the outer query you have a tuple variable over students, then you say that you want that there is no exercise such that there is no results tuple for this student and this exercise with perfect score. I hope this helps. ------------------------------------------------------------------------------- [Wednesday, April 5, 10:40] > I have a problem to solve hw # 9(f). > Here is my code for that. > SELECT S.FIRST_NAME, S.LAST_NAME > 2 FROM STUDENTS S, RESULTS R > 3 WHERE S.STUD_ID = R.STUD_ID > 4 GROUP BY S.FIRST_NAME, S.LAST_NAME > 5 HAVING MAX(R.POINTS) IN (SELECT SUM(R.POINTS) > 6 FROM RESULTS R > 7 WHERE R.CAT = 'M'); > > no rows selected > > I don't know where I should think more.. In the subquery, you add up all points for midterm exercises of all students. Actually, you can run the subquery on its own (it is a non-correlated subquery) and will see that it returns a very large number: SUM(R.POINTS) ------------- 3206.5 The problem is that the group by in the subquery is missing. I.e. the subquery should compute for every student the sum of his/her points for the midterm exam. In addition, MAX(R.POINTS) in the having clause will compute the maximum of points the student got for a single exercise ("the student" means the student which determines the current group). This can be maximally 12 points (for Exercise 1) (actually, 12.5 points since one student got 0.5 points extra credit). So you need to replace MAX(R.POINTS) by SUM(R.POINTS) and you should make sure that this sum is >= every result of the subquery (i.e. the IN also doesn't work). There is an atomic formula which compares a value with all values returned by a subquery, please check the slides. I hope this helps. ------------------------------------------------------------------------------- [Wednesday, April 5, 11:00] > We are able to get the students with homework and without listed for > exercise D > on the current assignment but problems getting sum(points) to work. Help > > SELECT S.LAST_NAME, SUM(R.POINTS) > FROM STUDENTS S, RESULTS R > WHERE > (SELECT S.LAST_NAME, R.POINTS > FROM STUDENTS S, EXERCISES E, RESULTS R > WHERE E.CAT = 'H'AND R.STUD_ID = S.STUD_ID AND > R.POINTS >= '0'UNION > (SELECT S.LAST_NAME, R.POINTS > FROM STUDENTS S, EXERCISES E, RESULTS R > WHERE E.CAT = 'H' AND > NOT EXISTS > (SELECT * > FROM RESULTS R > WHERE R.STUD_ID = S.STUD_ID AND R.CAT = 'H'))) This has a syntax error in the fourth line: (SELECT S.LAST_NAME, R.POINTS * ERROR at line 4: ORA-00936: missing expression In Oracle, subqueries are allowed only under EXISTS/NOT EXISTS, and on the right hand side of IN, NOT IN, >= ALL etc. You don't have to do the UNION inside a subquery for this exercise. The structure you need is very similar to the example with the composers on slide 10-64: You have one query which computes the result for all students who did solve homeworks, then the keyword UNION, and then another query which computes all students who did not solve any homeworks (and outputs 0 points for them). But both your subqueries are also not correct. > SELECT S.LAST_NAME, R.POINTS > FROM STUDENTS S, EXERCISES E, RESULTS R > WHERE E.CAT = 'H'AND R.STUD_ID = S.STUD_ID AND > R.POINTS >= '0' In the first part you need SUM and GROUP BY. You don't need to access EXERCISES and you don't need to check that the points are greater than 0. If they are 0, this is no problem, you can compute the sum 0. The problem is only if there is no single result tuple for that student so that the join will eliminate that student. This is handled in the second part of the query. > SELECT S.LAST_NAME, R.POINTS > FROM STUDENTS S, EXERCISES E, RESULTS R > WHERE E.CAT = 'H' AND > NOT EXISTS > (SELECT * > FROM RESULTS R > WHERE R.STUD_ID = S.STUD_ID AND R.CAT = 'H'))) There is no need to access EXERCISES and RESULTS in the outer query. Actually, it would be possible that the database contains no RESULTS at all, in which case you are supposed to print 0 for every student (if STUDENTS is not also empty). But if you declare a tuple variable over RESULTS in the outer query, it would give 0 rows if RESULTS is empty. Also, since you have no join in the outer query, you would get every student with very points in the database. You want to have 0 points for such students. I hope this helps. ------------------------------------------------------------------------------- [Wednesday, April 5, 14:20] > I have a few questions about homework # 9 > b) > Do you want the answer for all students or only the ones that have turned in > other homeworks? All students. I agree that it might be more practical to elimite those students who have only registered via the web, but never showed up. But the homework doesn't require that and it is actually simpler if you don't have to check that the student has submitted at least one homework. > c) > Do you want this to include students that have not turned in any homework? Yes. Again, it would be better to eliminate such students, since they will be printed 6 times and and are probably not registered for the course (so I don't have to assign a grade), but the exercise is simpler if you include such students. If you want to do the more practical solution, please put a note in the homework that you consider only students who have submitted at least one homework, because this reduces the output to the interesting cases. ------------------------------------------------------------------------------- [Thursday, April 13, 16:10] > for problem D we can not get the order by to run. > can you help. I am sorry for the late answer. > SQL> SELECT S.LAST_NAME, S.FIRST_NAME, SUM(R.POINTS) > 2 FROM STUDENTS S, RESULTS R > 3 WHERE R.CAT = 'H' AND R.STUD_ID = S.STUD_ID > 4 GROUP BY S.LAST_NAME, S.FIRST_NAME > 5 UNION > 6 (SELECT S.FIRST_NAME, S.LAST_NAME, 0 > 7 FROM STUDENTS S > 8 WHERE NOT EXISTS > 9 (SELECT R.POINTS > 10 FROM RESULTS R > 11 WHERE R.STUD_ID = S.STUD_ID AND R.CAT = 'H')) > 12 ORDER BY SUM(R.POINTS) > 13 ; > ORDER BY S.LAST_NAME, S.FIRST_NAME. SUM(R.POINTS) > * > ERROR at line 12: > ORA-01785: ORDER BY item must be the number of a SELECT-list expression After ORDER BY, you must specify the name or number of an output column. E.g. here you can write ORDER BY 3 and it will sort the query result by the third column. ------------------------------------------------------------------------------- [Thursday, April 13, 16:20] > I am having trouble with problem f. Can you steer me in the > right direction. > SELECT S.LAST_NAME, S.FIRST_NAME > FROM STUDENTS S, RESULTS R, EXERCISES E > WHERE S.STUD_ID=R.STUD_ID AND E.CAT=R.CAT AND > GROUP BY S.LAST_NAME, S.FIRST_NAME > HAVING SUM(R.POINTS) IN > (SELECT SUM(R.POINTS) > FROM RESULTS R > WHERE R.CAT=M) > > (HOW DO WE COMPARE MAX POINTS?) > thanks. You do not need EXERCISES in the outer query, but you must make sure that you look only at midterm points. The HAVING clause is a good idea, but instead of IN you need to make sure that the sum is >= all sums of the other students. Your subquery currently sums up all midterm points of all students, which is much greater than any single student can have.