Part 1: Joining tables on columns that are not keys
An example of joining two tables using columns that are not keys would be in the example below:
Query: select I.ID, I.name, C.course_id, C.title from instructor I inner join course C on I.dept_name=C.dept_name;
English sentence: List all instructors and the possible courses they can teach in their subject area.
In the example above, we are combining an instructor table and a course table, where the primary keys are instructor ID and Course ID. We are connecting the two via dept_name, which is a column they share. The join above will connect each instructor to a possible class they can teach based on their assigned department.
Part 2: Opinion of SQL as a Language
SQL is a pretty fun language to learn, even though it is a bit stressful at times. I think of it as a puzzle where each part matters in obtaining the right results (just like math!). It is easy to learn and use but one must be careful in each detail of the data that needs to be obtained. It is a bit difficult to translate from an English question with simple directions but requires queries that join two tables and the use of a subquery. There was one specific question on Homework 2 where we had to find the department with the most students majoring in that department. The directions are simple enough, but the query requires a temporary table joining two tables and a separate select statement with a subquery. It took me a while to come up with a solution, but it helped me learn more about how to use temporary tables and subqueries.
Comments
Post a Comment