HagDZNsy

1class Student
2  attr_accessor :id, :name, :grade
3
4  def self.new_from_db(row)
5    # create a new Student object given a row from the database
6    new_student = self.new 
7    new_student.id = row[0]
8    new_student.name = row[1]
9    new_student.grade = row[2]
10    new_student
11  end
12
13  def self.all
14    # retrieve all the rows from the "Students" database
15    # remember each row should be a new instance of the Student class
16    sql= <<-SQL
17    SELECT * FROM students
18    SQL
19
20    DB[:conn].execute(sql).map do |row|
21      self.new_from_db(row)
22    end
23  end
24
25  def self.find_by_name(name)
26    # find the student in the database given a name
27    # return a new instance of the Student class
28    sql =  <<-SQL
29    SELECT * FROM students 
30    WHERE name = ? 
31    LIMIT 1
32    SQL
33
34    DB[:conn].execute(sql, name).map do |row|
35      self.new_from_db(row)
36    end.first
37  end
38  
39  def save
40    sql = <<-SQL
41      INSERT INTO students (name, grade) 
42      VALUES (?, ?)
43    SQL
44
45    DB[:conn].execute(sql, self.name, self.grade)
46  end
47  
48  def self.create_table
49    sql = <<-SQL
50    CREATE TABLE IF NOT EXISTS students (
51      id INTEGER PRIMARY KEY,
52      name TEXT,
53      grade TEXT
54    )
55    SQL
56
57    DB[:conn].execute(sql)
58  end
59
60  def self.all_students_in_grade_9
61    sql = <<-SQL
62      SELECT * FROM students
63      WHERE grade = 9 
64    SQL
65
66    DB[:conn].execute(sql)
67  end
68
69  def self.students_below_12th_grade
70    sql = <<-SQL
71      SELECT * FROM students
72      WHERE grade < 12
73    SQL
74
75    DB[:conn].execute(sql).map do |row|
76      self.new_from_db(row)
77    end
78  end
79
80
81  def self.first_X_students_in_grade_10(x)
82    sql = <<-SQL
83      SELECT * FROM students
84      WHERE grade = 10
85      LIMIT ?
86    SQL
87
88    DB[:conn].execute(sql, x).map do |row|
89      self.new_from_db(row)
90    end
91  end
92
93  def self.first_student_in_grade_10
94    sql = <<-SQL
95      SELECT * FROM students
96      WHERE grade = 10 
97      LIMIT 1
98    SQL
99
100    DB[:conn].execute(sql).map do |row|
101      self.new_from_db(row)
102    end.first
103  end
104
105  def self.all_students_in_grade_X(x)
106    sql = <<-SQL
107      SELECT * FROM students
108      WHERE grade = ?
109    SQL
110
111    DB[:conn].execute(sql, x).map do |row|
112      self.new_from_db(row)
113    end
114  end
115
116  def self.drop_table
117    sql = "DROP TABLE IF EXISTS students"
118    DB[:conn].execute(sql)
119  end
120end