SQL has the flexibility to join tables on any column(s) using any predicate (=, >, < ). As long the column(s) are type compatible. Most of the time the join will have a join predicate with primary key of one table equal to a foreign key of the other table. Think of example where joining on something other than keys would be needed. Write the query both as English sentence and SQL. If you can't think of your own example, search the textbook or internet for an example.
Joins can operate on data to match any sort of criteria. Usually join predicates are used to match data that references a given primary key, though these predicates can also be used to search for arbitrary relationships. For example, consider the following query:
English: For each region that starts with the letter ‘T’, display all product names sold in the region, discarding duplicates.
SQL: select distinct regionname, productname from product p, includes i, salestransaction sale, store s join region r on regionname like "T%" where p.productid = i.productid and i.tid = sale.tid and sale.storeid = s.storeid and s.regionid = r.regionid;
In the above, the join predicate regionname like "T%" does not operate on a primary / foreign key.
What do you think of SQL as a query 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 find the query language to be relatively intuitive. Personally, I have found questions that involve aggregate functions the most difficult to answer. Since aggregate functions have special considerations, such as needing to be placed in the SELECT or HAVING sections of a query, and often needing a GROUP BY clause, it can be complicated to directly translate these queries from English to a valid SQL statement.