What is the best way to deal with 8 different SQL questions below.

I have posted the database schema below as presented in my Rails models and seven questions for the data I need to get from my database. Some questions that I answered, others, I’m not sure about the best solution.

Question 7 is a crooked ball because it potentially changes the answers to all other questions.

Criteria

• No n + 1 queries are required. Several queries are fine, but if each returned row requires an additional query, it does not scale.
• You should not require further processing to filter results that SQL can execute on its own. For example, the answer to number five should not pull ALL students out of the data warehouse, and then remove those who do not have courses.
• Retrieving an invoice for an object should not trigger another SQL query.
• No need to add database column through denormalization if SQL allows me to aggregate data
• Was there a NOSQL solution like MongoDB or CouchDB better to answer all the questions below?

Database schema

Students
-------
ID
Name

Courses
-----
ID
Name
Grade

Enrollments
----------
ID
Student_ID
Course_ID

ActiveRecord Models

class Course < ActiveRecord::Base
  has_many :enrollments
  has_many :students, :through=>:enrollments
end
class Enrollment < ActiveRecord::Base
  belongs_to :student
  belongs_to :course
end
class Student < ActiveRecord::Base
  has_many :enrollments
  has_many :courses, :through => :enrollments
end

Questions

1) Remove all 9th ​​grade math students

SQL

SELECT s.* FROM Students s
LEFT JOIN Enrollments e on e.student_id = s.id
LEFT JOIN Courses c on e.course_id = c.id
WHERE c.grade = 9 AND c.name = 'Math'

Decision

It's simple. ActiveRecord does it well

c = Course.where(:grade=>9).where(:name=>'Math').first
c.students

2) Get all courses made by John

SQL

SELECT c.* FROM Courses c
LEFT JOIN Enrollments e on c.id = e.course_id
LEFT JOIN Students s on e.student_id = s.id
WHERE s.name = 'John'

Decision

Again, simple.

s = Student.where(:name=>'John').first
s.courses

3) 9- , ( )

SQL

SELECT c.*, count(e.student_id) FROM Courses C
LEFT JOIN Enrollments e on c.id = e.course_id
WHERE c.grade = 9 GROUP BY c.id

Counter Cache .

class AddCounters < ActiveRecord::Migration
  def up
    add_column :students, :courses_count, :integer, :default=>0
    add_column :courses, :students_count, :integer, :default=>0
    Student.reset_column_information
    Student.all.each do |s|
      Student.update_counters s.id, :courses_count => s.courses.length
    end
    Course.reset_column_information
    Course.all.each do |c|
      Course.update_counters c.id, :students_count => c.students.length
    end
  end

  def down
    remove_column :students, :courses_count
    remove_column :courses, :students_count
  end
end

ActiveRecord

Course.where(:grade=>9).each do |c|
  puts "#{c.name} - #{c.students.size}"
end

4) , 11- , 10- 9-

NO

. SQL, . , . .

. . (, )

students = some_course.students
matching_students = []
students.each do |s|
  courses_9 = 0
  courses_10 = 0
  courses_11 = 0
  s.courses.each do |c|
    courses_9  += 1 if c.grade == 9
    courses_10 += 1 if c.grade == 10
    courses_11 += 1 if c.grade == 11
  end
  if courses_11 <= 3 && courses_10 > 1 && courses_9 == 0
    matching_students << s
  end
end
return matching_students

5) , )

SQL

SELECT s.*, count(e.course_id) as num_Courses FROM Students s
INNER JOIN Enrollments e on s.id = e.student_id
INNER JOIN Courses c on e.course_id = c.id AND c.name = 'Math'
GROUP BY s.id HAVING num_Courses > 0

SELECT DISTINCT s.* FROM Students s
INNER JOIN Enrollments e_math_1 on e_math_1.student_id = s.id
INNER JOIN Courses c_math_1 ON e_math_1.course_id = c_math_1.id AND c_math_1.name = 'Math'
INNER JOIN Enrollments e_math_2 on e_math_2.student_id = s.id
INNER JOIN Courses c_math_2 ON e_math_2.course_id = c_math_2.id AND c_math_2.name = 'Math'
WHERE c_math_1.id != c_math_2.id

NO

. , ActiveRecord ( NoSQL) , .

students = SomeObject.students
multiple_math_course_students = []
students.each do |s|
  has_math_course = false
  add_student = false
  s.courses.each do |c|
    if c.name == 'Math'
      if has_math_course
        add_student = true
      else
        has_math_course = true
      end
    end
  end
  multiple_math_course_students << s if add_student
end

6) ,

SQL

SELECT s.* FROM Students s
INNER JOIN Enrollments e_math on e_math.student_id = s.id
INNER JOIN Courses c_math ON e_math.course_id = c_math.id
INNER JOIN Enrollments e_science on e_science.student_id = s.id
INNER JOIN Courses c_science on e_science.course_id = c_science.id WHERE c_math.name = 'Math' AND c_science.name = 'Science'

NO

( Rails, association) . ActiveRecord AREL? , , № 7 .

students = SomeObject.students
math_and_science_students = []
students.each do |s|
  has_math_course = false
  has_science_course = false
  s.courses.each do |c|
    has_math_course = true if c.name == 'Math'
    has_science_course = true if c.name == 'Science'
  end
  math_and_science_students << s if has_math_course && has_science_course
end

7) , , , , , . , 9- 10- , "10" .

. , 100 , 100 . , " ". ? , ?

, , : , , , , .. ? , ?