SQLchapter 4

As you do the exercises, unless it is stated otherwise, you will be using the tables from our standard Student- Course database. Also, as you do the exercises, it will be a good idea to copy/paste your query as well as your query result into a word processor.

Display the COUNT of section-ids from the Section table, and then the COUNT of DISTINCT section-ids from the Grade_report table. What does this information tell you? Hint: section_id is the primary key of the Section table.

4-3. Write, execute, and print a query to list student names and grades (just two attributes) using the table alias feature. Restrict the list to students that have either As or Bs in courses with ACCT prefixes only.

Heres how to complete this problem:

a. Get the statement to work as a COUNT of a join of the three tables, Student, Grade_report, 105 and Section. Use table aliases in the join condition (remember to use /* join conditions */). Note that a join of n tables requires (n-1) join conditions, so here you have to have two join conditionsone to join the Student and Grade_report tables, and one to join the Grade_

report and Section tables. Note the number of rows that you get (expect no more rows than is in the Grade_report table.) Why?

b. Modify the query and put the accounting (ACCT) condition in the WHERE clause. Note the number of rows in the resultit should be a good bit less than in (a).

c. Again, modify the query and add the grade constraints. The number of rows should decrease again. Note that if you have WHERE x and y or z, parentheses are optional, but then the criteria will be interpreted according to precedence rules.