SQLchapter 4 As you do the exercises, unless it is stated otherwise, you will be

Question

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 A’s or B’s in courses with ACCT prefixes only.

Here’s 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 conditions—one 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 result—it 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.

Explanation / Answer

1. SELECT COUNT(*) FROM Section;

SELECT COUNT(DISTINCT section_id) FROM Grade_report;

The second query returns the number Of sections for which grade report is available.

4.3) SELECT s.name, gr.grade FROM Student s JOIN Grade_report gr ON s.student_id=gr.student_id WHERE gr.course IN ('A', 'B') AND gr.course LIKE 'ACCT%';

Note: Without table structures provided by you, this query might not work properly.

a) SELECT COUNT(*) FROM Student s JOIN Grade_report gr ON s.student_id = gr.student_id JOIN Section sc ON sc.section_id=gr.section_id;

Note: Without table structures provided by you, this query might not work properly.

b) SELECT COUNT(*) FROM Student s JOIN Grade_report gr ON s.student_id = gr.student_id JOIN Section sc ON sc.section_id=gr.section_id WHERE gr.course LIKE 'ACCT%';

Note: Without table structures provided by you, this query might not work properly.

c) SELECT COUNT(*) FROM Student s JOIN Grade_report gr ON s.student_id = gr.student_id JOIN Section sc ON sc.section_id=gr.section_id WHERE gr.course LIKE 'ACCT%' AND gr.grade IN ('A', 'B');

Note: Without table structures provided by you, this query might not work properly.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.