ORM Familiarity Exercises

ORM

We recognize the need for exercises to introduce how ORMs work and how flexible we can bend the system to our will. ORMs offer amazing abilities to map database records to objects to be interacted with. We will be assuming an ActiveRecord pattern for the following exercises - SQLAlchemy (or other ORMs) are to be used at your own risk! Of course, I will always be available to help with these exercises - but my focus will be on Ruby, meaning that I will offer less-than-optimal help for those remaining with Python.

Introductory Exercises (exploratory)

1. Return how many Subdepartments there are. ✓
2. Return the number of courses in a given Subdepartment - say the CS department. ✓
3. Return an array of professors that correspond to a given course - say ECON 2010. ✓
4. Return an array of days i.e. [Mo,We], that a course has been offered, ever - say ECON 2010. ✓
5. Return an array of Subdepartment mnemonics that a Professor teaches in - say Roquinaldo Ferreira. ✓
6. Find the Subdepartment with the most courses. ✓
7. (advanced) now turn each of the above into one line. ✓

Intermediate Exercises

1. Compute the average rating for all courses in the COMM subdepartment. ✓
2. Find the subdepartment with the largest amount of reviews. ✓
3. Find how many courses that have sections only before 3PM.
4. Find the Subdepartment with the most total capacity.
5. Find the average units per course in the CS department.
6. Return a course matching by title, i.e. "Program and Data" - notice no "Representation"!
7. Now make the prior method match both "Program and Data", "data representation".
8. Now make the prior method match "data and program representation".
9. (advanced) now turn each of the above into one line.

Advanced Exercises

1. Compare class capacities across the CS and COMM department.
2. Compare the professor to capacity ratio between CS and COMM department.
3. Compare the ratio of As (A+, A, A-) to total grades (not including drop/withdraw/other) between the CS and COMM department adjusted per course (per section).
4. Compute the average ratio of drop (stored in grades) to class capacity.
5. Compute the average enjoyability rating for the CS department (and compare with COMM).
6. Compute the average difficulty rating for the CS department (and compare with COMM).
7. Find the course that has the most swear words in their reviews.
8. Find the average number of reviews written by a user (only count those who have written more than 1 review)

Crazy Exercises

1. Find how many semesters would it take to take every CS course (scheduling!!)
2. Compute the total cost of the BIOL department (textbooks based on Amazon price prioritizing new > used) for every course.
3. Give the average enjoyability rating of the ECON department, and determine Elzinga's impact on that rating.
4. Give the r-value correlation between total cost of a BA CS degree (lookup requirements) in textbooks compared to a BA Biology and capacity.
5. More to come.... dun dun dun.

If all of these are finished - one (1) free Boylan Heights meal, on me, for the DATA and DEV team. Finishing Introductory, Intermediate, Advanced gets one (1) free Take-it-Away or Little Johns meal, on me. You get one final submission, in the form of a single script - although I will help those in need. Teams can range from solo to the entire DATA + DEV team - each team only gets one submission, however. Good luck.