Week 2

 Week 2 (5/7-5/13)

SQL has the flexibility to join tables on any column(s) using any predicate (=, >, < ).    Most of the time the join will use equality between a primary and foreign key.   Think of example where joining on something other than keys would be needed.  Write the query both as an English sentence and in SQL

An example that was very similar to a few questions in the lab and the homework is:
- List all students who registered for a course before it officially started

So we are comparing the registration_date from the takes table with the start_date from the course tables. These are not keys, we are just comparing dates using <. We are using keys to join (ID, course_id) but the logic is on a non-key comparison, checking if registration_date < start_date

SQL Query:

SELECT s.ID, s.name, c.course_id, c.title, t.reg_date, c.start_date

FROM student s

JOIN takes t ON s.ID = t.ID

JOIN course c ON t.course_id = c.course_id

WHERE t.reg_date < c.start_date;

In student and takes:

student.ID is the primary key in the student table

takes.ID is a foreign key that references student.ID

In course and takes:

course.course_id is the primary key in the course table

takes.course_id is a foreign key that references course.course_id

What is your opinion of SQL as a language?  Do you think it is easy to learn and use?  When translating from an English question to SQL, what kinds of questions do you find most challenging

I think that SQL is a resourceful language for interacting with relational databases. In most companies with big datasets, using queries is the easiest way to keep data clear and concise, as well as making data analysis much more dynamic and easier to digest. When translating from English questions to SQL, what I find more challenging is ambiguous requirements that don't explain exactly what they are looking for. For instance, recently, instead of in the last 5 days or in the last week... Also, when reading the prompt, sometimes I tend to mix GROUP BY and HAVING in aggregations like average enrolled courses per student, but only for students with > 3 credits. I think practicing more and getting more familiar with different schemas, as well as breaking problems into smaller subqueries, will help me overcome these challenges.