corise-edu/course-intermediate-sql

Intermediate-sql package

Run an SQL query for the "Intermediate SQL Course" using sql_course package.

Install

You can install the package by running:

pip install git+https://github.com/uplimit/course-intermediate-sql.git

Then you can run a SQL query like:

from sql_course import run, check

### Question: q1_1_1
query = "SELECT * FROM courses"

run(query)
check(q1_1_1 = query)

The database and its content is locally constructed from the data contained in this package.

course-intermediate-sql's Issues

Query verification fails if column names repeat

Week 2 project question q2_4_2 has repeating column names resulting from a join
"course_run_id | course_run_id | nps | nps | affiliation | affiliation | start_date | start_date"
As a result, check(q2_4_2 = query) fails with error MergeError: Data columns not unique: Index(['affiliation', 'course_run_id', 'nps', 'start_date'], dtype='object')

SQL query check fails even if the query is the same as the solution

In the Week2 project, query q2_4_2 fails the check even though I cheat and copy from the project_solutions.csv file. I get this error - "Your SQL query does NOT match our solution. The number of rows is different."
I suspect this might be related to an issue comparing dataframes ( similar to this stackoverflow - https://stackoverflow.com/questions/73450402/dataframe-equals-returns-false-when-comparing-data-frames-with-the-same-conten ) .

Query q_0_1 missing and q1_1_1 misspelled

The colab refers to q_0_1 as a simple demo query but running the cell throws a warning "The variable name used for the parameter in the check function does not match any of our solution keys."

Misleading error "The number of rows is different" when dataframe mismatches

When we compare the student's query with the solution query and the contents of the dataframe do not match, we throw an error with the message "The number of rows is different". We should be throwing this error if and only if the number of rows is different and throw "The values are different." in all other cases.

check query fails in Project colab

When a student has to check if their query is correct or not, the 'check' function fails with the following error:

/usr/local/lib/python3.7/dist-packages/sql_course/sql.py in check(**key_user_sql_query)
     50     sql_solutions = pd.read_csv(StringIO(pkgutil.get_data(
     51             __name__, DATA_FOLDER_URL + SOLUTION_CSV).decode("utf-8")), sep=";", header=None)
---> 52     sql_sol_dict = dict(zip(sql_solutions.key, sql_solutions.value))
     53 
     54     for key, user_sql_query in key_user_sql_query.items():

/usr/local/lib/python3.7/dist-packages/pandas/core/generic.py in __getattr__(self, name)
   5485         ):
   5486             return self[name]
-> 5487         return object.__getattribute__(self, name)
   5488 
   5489     def __setattr__(self, name: str, value) -> None:

AttributeError: 'DataFrame' object has no attribute 'key'