CODEWITHSUNDEEP
sqldatabase
Dandiss Valjean
Dandiss Valjean

Reputation: 113

Is it correct that predication in WHERE is not about the relation in the FROM in SQL query?

This is problem about answer to "Database System Concepts(6th Edition)" Practice Exercise 3.2-a.

This error is not included in the errata. I think this should be updated to the errata of this book!

Give four relations:

  1. grade_points

    grade_points(grade, points)

    If grade = 'A+', points = 4,

    if grade = 'A-', points = 3.7

    just like this ...

  2. student

    student(ID, name, dept_name, tot_cred)

    ID is the id of the student, name is his name, dept_name is his department name, tot_cred is his the total credits he got.

  3. course

    course(course_id, title, dept_name, credits)

  4. takes

    takes(ID, course_id, sec_id, semester, year, grade)

    The ID is the student ID who takes the course of course_id, the grade is the level he got in this course.

The problem is:

Find the total grade-points earned by the student with ID 12345, across all courses taken by the student.

The solution given by Answers of textbook is this:

(select sum(credits * points)

from (takes natural join course) natural join grade_points

where ID = '12345')

union

(select 0

from student -- This is relation "student"

where takes.ID = '12345' and -- takes.ID should be student.ID ???

      not exists (select * from takes where takes.ID = '12345'))

If a student has not taken any course, we would expect to give 0 as the answer.

I think the first "takes.ID" in last where clause should be "student.ID" to indicate that he is a student and then check whether he takes courses. Is the using of "takes.ID" legal and correct? If it is correct, why?

Upvotes: 2

Views: 468

Answers (1)

Dandiss Valjean
Dandiss Valjean

Reputation: 113

This is just and error in the book's practice answers. I will email the authors to update the errata.

Upvotes: 1

Related Questions