I am writing this today as I have a midterm tomorrow for this class. I have an assignment due as well and figured to write this in light of some of my frustrations I encounter as I go through the assignment. Note: I cannot disclose the entirety of this assignment or its details since it is my university’s policy.

Question 1: Write a SQL query to generate a report showing the total enrollment broken down by year, then semester, and then department.

So first, I know I need to

SELECT year, semester FROM

Since department and enrollment are in different tables, I will need to join them somehow. Let’s see which join is the most optimal in this situation.

Let’s start with department since that one is easier. There is no attribute called enrollment so I would need to create a more complex sub-query for that.

I’m using: https://www.devart.com/dbforge/sql/sqlcomplete/sql-join-statements.html to gain some clarity and explanation for the different types of SQL Joins.

Knowing that there’s an attribute in <table1> that connects with an attributeC in another table, let’s call it <table-dept>, we should look for a JOIN where the attributes should match.

Found it! I think INNER JOIN would be the best option.

SELECT year, semester, c.department
FROM s
INNER JOIN c ON s.attributeC = c.attributeC;

Ok, now that resulted out an input that let us see the year, semester, and department.

Now, for the hardest part… Getting the enrollment.

The enrollment, again as said before, is not an attribute so we can’t just select it. We can get enrollment by taking the COUNT of an ID attribute in a tableT that has the ID attribute. These tables are also connected by attributeC. However, for this one, which join should we use? In addition, since we’re using COUNT, it might also be ideal to use GROUP BY.

GROUP BY is something that I struggle with a lot… As for the JOIN, I think we should use LEFT OUTER JOIN.

Ok, so going through it, it seems like we don’t need LEFT OUTER JOIN.

I got this error so it seems like it has something to do with my GROUP BY command. I need to select the COUNT of ID for enrollment which is in tableT. How can I group it?

---

Taking a break…

realizes that I’ve interpreted the question incorrectly…

---

Through searching through the course’s Q&A page, I found out that we had to use ROLLUP for the problem. I did not think about that at all!

ROLLUP is essentially when you take everything that you selected and then unravel each column, going from the most detailed to the least detailed. In my case, I had to find the total enrollment, so first I’d find it at the most specific case where I needed to specify by year, semester, and department name. Then, I’d specify by just year and semester. The by just year, and then the total enrollment across all years, semesters, and departments.

Through this process I also realized I didn’t have to use INNER JOIN. I could use JOIN or no join at all and just several conditions using WHERE.

However, now I’ve encountered another problem… My numbers are wrong, meaning that my query output is not correct. My course provides the query output to make sure that we are doing it correctly.

… Problem not resolved quite yet …