8.8. Exercises
Unless specified otherwise, use the Student_course database to answer the following questions. Also, use appropriate column headings when displaying your output.
Use the techniques from this chapter to construct and execute the following queries:
Find the student numbers of students who have earned As or Bs in courses taught in the fall semester. Do this in two ways: first using a subquery, and then using a join. Find all students who took a course offered by the Accounting department. List the student name and student number, the course name, and the grade in that course. (Hint: Begin with Department_to_major and use an appropriate WHERE.) Note that this task cannot be done with a multilevel subquery. Why? For every students who is a sophomore (class = 2), find the name and the name of the department that includes the student's major. Find the names of the departments that offer courses at the junior or senior levels (either one) but not at the freshman level. The course level is the first digit after the prefix; for example, AAAA3yyy is a junior course, and so on. Hint: Begin by creating the outer querythe names of departments that offer courses at the junior or senior levels. Save this query as q8_4. Then, construct the subquerya list of departments that offer courses at the freshman level. Save the subquery as a view. Examine both lists of departments. When you have the outer query and the subquery results, recall the original query that you saved (q8_4) and add the subquery. Check your result with the department lists you just generated. Redo the last part of the experiment with your view. You should get the same result. Find the names of courses that are prerequisites for other courses. List the course number and name, and the number and name of the prerequisite. List the names of instructors who teach courses that have other than three-hour credits. Do the problem in two ways: once with IN and once with NOT..IN. Create a table called Secretary with the columns dcode (of data type CHAR(4)) for department code and name (of data type VARCHAR(20)) for the secretary name. Populate the table as follows: Secretary |
|---|
dCode | name | ACCT | Beryl | COSC | Kaitlyn | ENGL | David | HIST | Christina | BENG | Fred | HINDI | Chloe |
Create a query that lists the names of departments that have secretaries (use IN and the Secretary table in a subquery with the Department_to_major table in the outer query). Save this query as q8_7a. Create a query that lists the names of departments (using the Department_to_major table) that do not have secretaries (use NOT IN). Save this query as q8_7b. Add one more row to the Secretary table that contains <null,'Brenda'> (which you could see, for example, in a situation in which you have hired Brenda but have not yet assigned her to a department). Recall q8_7a and rerun it. Recall q87_b and rerun it. The behavior of NOT..IN when nulls exist may surprise you. If nulls may exist in the subquery, then NOT..IN either should not be used (Chapter 10 shows how to use another predicate, NOT EXISTS, which is a workaround to this problem), or should include AND whatever IS NOT NULL. If you use NOT..IN in a subquery, you must either ensure that nulls will not occur in the subquery or use some other predicate (such as NOT EXISTS). Perhaps the best advice is to avoid NOT..IN unless you cannot figure out another way to solve a problem. To see a correct answer, add the phrase WHERE dcode IS NOT NULL to the subquery in the IN and NOT..IN cases and run them again.
Do not delete the Secretary table, because we will revisit this problem in Chapter 10. Devise a list of course names that are offered in the fall semester in rooms where the capacity is equal to or above the average room size.
 |
