CSCI A114 / INFO I111
Homework Six

First semester 2001-2002

Due date
(See the what's due for updated due date).
Before the final (Thu Oct 18 at 2:29pm).

Late policy
No late policy. You need to turn this on time.

Work policy
Working in groups is accepted but you need to turn in your own version of the assignment. Also, please make sure you read, understand, and comply with the Computer Science Department's Statement on Academic Integrity before turning in your assignment.

Task
Using the same database as the one for homework five write these queries in SQL.

  1. Which students received B's in any course?

    This query appears solved in section "What is a subquery?" in the Review Notes. Also in the "Using ANY and ALL" section.

  2. Which teachers earn less than average?

    This query is already solved in the Review Notes, in the section called "Using Comparison Operators with Subqueries"

  3. Which sections have more than the average number of students?

    For this query assume we talk about the capacity of a section (the max enrollment instead of the actual enrollment). The query is solved already in the Review Notes, in "Using Comparison Operators with Subqueries."

  4. List all students whose average grade is greater than the lowest average grade.

    Solved, explained, done - in "Using ANY and ALL".

  5. List the names and grades of students who received some grade greater than or equal to the highest all-around average grade.

    Solved, explained, done - in "Using ANY and ALL".

  6. Which students are not enrolled in any courses?

    Done, in "Correlated Subqueries", twice.

  7. Which students received a grade higher than the average in their section?

    Section "Correlated Subqueries" contains the solution to this problem.

  8. Which courses have only male students? 

    SELECT DISTINCT course_name
FROM COURSES stuff
WHERE 'M' = ALL (
                   SELECT _____
                   FROM ENROLLS, STUDENTS
                   WHERE ENROLLS.[student#] = STUDENTS.[student#] 
                     AND ENROLLS.[course#] = stuff.[course#]
                );

  9. Which students are taking more than two courses?

    Solved in "Using HAVING with Correlated Subqueries", in the Review Notes. Already there.

  10. List the departments that have courses with more than 4 students.

    Extrenely similar to the one above.

Grading
Each query is worth the same amount of points: 9.5 out of 100.

Last updated: October 10, 2001 by Adrian German for A114/I111