CST363: Introduction to Database, Week 2

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.

One situation where you might join tables on something other than a primary/foreign key is when you need to compare ranges of values. For example, imagine having an Employee table with a salary column and a SalaryGrade table that shows ranges for each grade level. Instead of joining on matching IDs, the join would check whether the employee’s salary falls between the minimum and maximum salary for a grade.

In English: List each employee with their salary grade by matching the employee’s salary to the grade range it falls into.

In SQL:

SELECT Employee.name, Employee.salary, SalaryGrade.grade_level FROM Employee JOIN SalaryGrade
ON Employee.salary BETWEEN SalaryGrade.min_salary AND SalaryGrade.max_salary;

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 it is pretty straightforward once you get the hang of the basic structure. It reads almost like English, but the small details matter a lot. Overall, I don’t find SQL hard to learn, but I do notice that some English questions are easier to translate into SQL than others. Straightforward ones, like “list all students in a department,” are quick to write. The harder ones are the layered questions, like “find the students who have taken all the courses offered by their department.” Those force me to slow down, break the problem into smaller steps, and sometimes use nested queries.