Week 18: Advanced SQL

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.

The textbook has an example where foreign/primary keys are not used to join.

For each manager who has a staff member with the same name as the manager’s first name, show the manager's ID, first name, and last name, and the ID of the staff members who have the same name as the manager’s first name.

SELECT m.managerid, m.mfname, m.mlname, s.smemberid

FROM manager m, staffmember s

WHERE m.mfname = s.smembername;

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?

SQL is fairly intuitive although it does require some learning but overall it is fairly easy to use. The most challenging questions are where I need to join multiple columns to find an answer.