Section 10.7. Exercises

Unless specified otherwise, use the Student_course database to answer the following questions. Also, use appropriate column headings when displaying your output.

List the names of students who have received Cs. Do this in three ways: (a) as a join, (b) as an uncorrelated subquery, and (c) as a correlated subquery. Show both results and account for any differences.
In section "Existence Queries and Correlation," you were asked to find the names of students who have taken a computer science class and earned a grade of B. We noted that it could be done in several ways. One query could look like this:
```
SELECT   s.sname
FROM     Student s
WHERE    s.stno IN
   (SELECT  gr.student_number
    FROM    Grade_report gr, Section
    WHERE   Section.section_id =  gr.section_id
    AND         Section.course_num LIKE 'COSC___  _'
    AND         gr.grade = 'B')
```
Redo this query, putting the finding of the COSC course in a correlated subquery. The query should be as follows:
The Student table uncorrelated subquery to the Grade_report table, correlated EXISTS to the Section table.
In the section "SQL Universal and Existential Qualifiers," we illustrated both an existence query:
```
SELECT  s.sname
FROM    Student s
WHERE EXISTS
  (SELECT 'X'
   FROM     Grade_report gr
   WHERE    Student.stno = gr.student_number
   AND      gr.grade = 'C')
```
and a NOT EXISTS version:
```
SELECT   s.sname
FROM     Student s
WHERE NOT EXISTS
  (SELECT 'X'
   FROM     Grade_report gr
   WHERE    Student.stno = gr.student_number
   AND      gr.grade = 'C')
```
Show that the EXISTS version is the complement of the NOT EXISTS versioncount the rows in the EXISTS result, the rows in the NOT EXISTS result, and the rows in the Student table. Also, devise a query to give the same result with IN and NOT..IN.
Discover whether all students take courses by counting the students, and then count those students whose student numbers are in the Grade_report table and those whose student numbers are not in the table. Use IN and then NOT..IN, and then use EXISTS and NOT EXISTS. How many students take courses and how many students do not?
1. Find out which students have taken courses but who have not taken COSC courses. Create a set of student names and courses from the Student, Grade_report, and Section tables (use the prefix COSC to indicate computer science courses). Then, use NOT..IN to "subtract" from that set another set of student names of students (who take courses) who have taken COSC courses. For this set difference, use NOT..IN.
2. Change NOT..IN to NOT EXISTS (with other appropriate changes) and explain the result. The "other appropriate changes" include adding the correlation and the change of the result column in the subquery set.
There exists a table called Plants. List the table and then find out what company or companies have plants in all cities. Verify your result manually.
Run the following query and print the result:
```
SELECT distinct name, langu
FROM Cap x
WHERE NOT EXISTS
              (SELECT 'X'
              FROM Cap y
              WHERE NOT EXISTS
                          (SELECT 'X'
                          FROM Cap z
                          WHERE X.langu =Z.langu
                          AND Y.name=Z.name))
```
Save the query (e.g., save forall) and hand in the result.
1. Recreate the Cap table (e.g., call it some other name, such as LANG1). To do this, first create the table and then use the INSERT statement with the sub select option (INSERT INTO LANG1 AS SELECT * FROM Cap).
2. Add a new person to your table who speaks only BENG.
3. Recall your previous SELECT (get for all).
4. CHANGE the table from CAP to LANG1 (for all occurrences, use CHANGE/Cap/lang1/ repeatedly, assuming that you called your table LANG1).
5. Start the new query (the one you just created with LANG1 in it).
6. How is this result different from the situation in which Newperson was not in LANG1? Provide an explanation of why the query did what it did.
The Department_to_major table is a list of four-letter department codes with the department names. In Chapter 8, Exercise 7 (hereafter referred to as Exercise 8-7), you created a table called Secretary, which should now have data like this:
Secretary

dCode
Name
ACCT
Beryl
COSC
Kaitlyn
ENGL
David
HIST
Christina
BENG
Fred
Null
Brenda

In Exercise 8-7, you did the following:
1. Create a query that lists the names of departments that have secretaries (use IN and the Secretary table in a subquery). Save this query as q8_7a.
2. Create a query that lists the names of departments that do not have secretaries (use NOT..IN). Save this query as q8_7b.
3. Add one more row to the Secretary table that contains <null,'Brenda'>. (This could be a situation in which you have hired Brenda but have not yet assigned her to a department.)
4. Recall q8_7a and rerun it.
5. Recall q8_7b and rerun it.
We remarked in Exercise 8-7 that the NOT..IN predicate has problems with nulls: the behavior of NOT..IN when nulls exist may surprise you. If nulls may exist in the subquery, then NOT..IN should not be used. If you use NOT..IN in a subquery, you must ensure that nulls will not occur in the subquery or you must use some other predicate, such as NOT EXISTS. Perhaps the best advice is to avoid NOT..IN.
Here, we repeat Exercise 8-7 using NOT EXISTS:
Reword query q8_7a to use EXISTS. You will have to correlate the inner and outer queries. Save this query as q10_7aa.
Reword query q8_7b to use NOT EXISTS. You will have to correlate the inner and outer queries. Save this query as q10_7bb. You should not have a phrase IS NOT NULL in your NOT EXISTS query.
Rerun q8_9a with and without <null, Brenda>.
Rerun q8_9b with and without <null, Brenda>.
Note the difference in behavior versus the original question. List the names of those departments that do or do not have secretaries. The point here is to encourage you to use NOT EXISTS in a correlated subquery, rather than NOT..IN.