Write the SQL and relational algebra queries using the following schema Homework Problem
STUDENT(Sid: integer, Fname, Lname, Major)
COURSE(Course_id, Cname, Dept)
ENROLL(Sid, Course_id, Quarter, Grade)
FOREIGN KEY (Sid) REFERENCES STUDENT(Sid)
FOREIGN KEY (Course_id) REFERENCES COURSE(Course_id)
ADOPT_BOOKS(Course_id, Quarter, Isbn)
FOREIGN KEY (Course_id) REFERENCES COURSE(Course_id)
FOREIGN KEY(Isbn) REFERENCES BOOK(Isbn)
BOOK(Isbn, Title, Publisher)
BOOK_AUTHORS(Isbn, Fname, Lname)
FOREIGN KEY (Isbn) REFERENCES BOOK(Isbn)
1. (10 points; 5 each) - Write R. A. and SQL
Retrieve the student id and student name of those who have enrolled in a course that is offered by the department called Computer Science.
SQL:
2. (10 points; 5 each) – Write R. A. and SQL
Retrieve the student ids and their names of those who have enrolled in all of the courses offered by both the Computer Science department and Mathematics departments.
3. (10 points; 5 points) – Write R. A. and SQL
Retrieve all of the student information for all students who have not taken any courses offered by the Physics department.
4. (10 points; 5 each) – Write R. A. and SQL
For each book, retrieve the Isbn, the title and and the count of the number of authors
5. (10 points; 5 each) - Write R. A. and SQL
Retrieve the Cname, Course_id and Quarter for all courses with the following requirements:
enrollments > 40 students
and had at least 3 students withdraw from the class (note: grade of "W" is a withdraw)
both of these must be satisfied to be retrieved by the query
We consider every Course_id and Quarter to be a separate course.
6. (10 points) - Write R. A. and SQL
Retrieve the count of the number of Computer Science majors that took courses offered by the Computer Science department in the S20 quarter. (A Computer Science student needs to be counted every time a Computer Science course was taken by that student in the S20 quarter.)
7. (10 points – 5 each) Write R. A. and SQL
List the book information for books that have an author of "Stephen King" or "Dean Koontz". Note: the book could have other authors but we are looking for books with these authors.
8. (10 points - 5 each) Write R. A. and SQL
Retrieve all student information along with the course_id, quarter and grade for those students who have taken the same course more than once.
9. (5 points)
For problem 8) above show what the final relation would look like in terms of attributes/columns. Be sure to show the order of the columns based on your own R. A.
10. (5 points)
What does this return (be specific)? Write the query that generated this.
SELECT COUNT(*)
FROM STUDENT, COURSE, ENROLL
WHERE STUDENT. Sid = ENROLL. Sid
AND COURSE. Course_id = ENROLL. Course_id
AND Major = "Physics"
AND Dept = "Computer Science"
AND Grade NOT IN ("W", "F", "D")