Assignment 2 FAQ (more recent questions on the top)

Question: In the instructions for assignment 2, you say that we are not allowed to use temporary tables, meaning that we should only have 1 SQL query for each question. Are we allowed to do something like slide #24 on the SQL (chapter 5) lecture notes? In that query you have a subquery in the FROM clause, and use AS to name it Temp, and then in the WHERE clause, you refer to fields using that alias. Is this allowed in this assignment?
Answer: Yes, this is allowed.

Q: For all queries having 'less than' requirements, do we take 'strictly less than' or 'less than equal to, as for query #4 and #6.

A: Strictly less than.

TIP: MS Access has different wildcard characters...for instance a % is a * in MS Access.

Q: Can we assume that a student is only enrolled in one section for a given dname and cno? This makes sense but I need that assumption for how I'm counting how many courses a student takes in Query12. I want to just be able to count how many tuples a student has in the enroll table with dname='Civil Engineering'

A: No, you can't assume that. Try to think of the query in terms of division in relational algebra and how the division is translated into SQL. Be careful when using count in what you are counting. Feel welcome to talk to me tomorrow during the office hours or after class tomorrow.

Q: By class do you mean section or course....basically is the condition (print out stuff for every course with less that 6 students) or (print out stuff for every section with less than 6 students).

A: For every section.

Q: 12. Print the ids, names, and GPAs of the students who are currently taking all of the Civil Engineering courses. Can A student be enrolled in the same class twice?

A: Given that we do not know any other constraints, a student could potentially be enrolled in the same class in different sections.

Q: Should a student who has taken only Civil Engineering courses be in the output of Query 12?

A: Not necessarily. Only if the student is taking all Civil Engineering courses, the student should be in the output of Query 12. (The student could be enrolled in other courses.)

Q: For q7, we were asked to output "the names and majors of students who have a major and are taking one of the College Geometry courses." For the case when a student double-majors, do we print out the same name twice with different majors? Or just once with one of his majors?

A: Twice with different majors.

Q: I have a question regarding query #3: do you want the average gpa of the students enrolled for each computer science class, or for each computer science class' section?

A: For each section. 

Q: Is each student supposed to have at least one major? And some can have more than one? Is each student supposed to only have one major?

A: Students can have more than one major and are not required to have a major. The query text has been slightly modified to only output students who have majors.