CST 363: Week 2

1) 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.  If you can't think of your own example, search the textbook or internet for an example.

Below is a perfect example that comes from homework_2.sql. We needed to join the takes table to student where student received a letter grade, so the second line does this:

-- 9. Calculate the GPA for each student from student table. 

--    GPA is a fraction whose numerator is the sum of the product

--    of grade value times the course credits,

--    and whose denominator is the sum of course credits.  

--    Consider only courses where students have received a grade (takes.grade is not null).

--    The numeric value of a letter grade can be found in the grade_points table.

--    Label the columns id, name, gpa and order by student id.

--    Answer contains:  Zhang with a gpa 3.87143, Snow with a null gpa

SELECT student.ID, name, SUM(credits * points)/SUM(credits) as GPA

FROM student 

LEFT JOIN takes ON student.ID = takes.ID AND takes.grade IS NOT NULL

LEFT JOIN course ON takes.course_id = course.course_id 

NATURAL LEFT JOIN grade_points

GROUP BY ID ORDER BY ID;

2) 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 like SQL thus far. I believe it's easy to implement compared to coding databases from scratch using JAVA language and still far easier than programming in assembly, C++, or Python (which are the only other languages I have experience with). When I am translating an English question to SQL, the questions I find the most challenging are the ones where we are expected to implement queries where 3 or more tables are joined together in different ways or where we need to utilize the WITH clause.