Unformatted text preview: ry key constraint violation. The values should be unique. NOTE: remember that we already have value 1234 in the table because of the previous
List the student ID and course number for every student who took a course in Fall 10 but has not
received a grade yet.
select s.sid, c.course_no
from course_taken ct
where ct.term like '%Fall 10%'
and ct.grade is null;
List the SIDs names and GPAs of the students whose GPAs are greater than 3.7. List them in the
descending order of the GPAs.
select s.SID, s.name, avg(grade) as GPA
from course_taken ct join student s on ct.sid = s.sid
group by s.sid, s.name
having avg(grade) > 3.7
order by avg(grade) desc;
3. List the SIDs and names of all students and the number of courses they have taken.
select s.sid, s.name, count(distinct course_no) as num_courses
from student s, course_taken ct
where s.sid = ct.sid
group by s.sid, s.name; Page 2 of 4 __________________________________________________________________________________
4. List the SIDs and names of the students who have not taken the...
View Full Document
- Fall '08
- Relational model, Articles with example SQL code, Sid, SQL keywords, key constraint violation