CST 363 - Week 2

CST 363: Week 2

The most common technique of joining tables in SQL is to join the primary key of one table to the foreign key of another table.  There might only sometimes be a key relationship between the two tables though.  Or, the desired return is purposely not using a primary key or foreign key to join the tables.  An example of this is joining data of one table (A) with additional data that relates from another table (B).  The same data type might have been stored in two different tables and the user wishes to have them together.  To do this you can do,

    SELECT A.col, B.col

    FROM A

    LEFT JOIN (SELECT DISTINCT col FROM B)

    ON A.col = B.col

This will combine the data into one table and exclude duplicate data that might have appeared in both tables.

So far, SQL is an interesting language to learn.  I find it easier to learn than OOP languages because it uses English.  With that being said it is still a difficult language to learn the syntax for.  When translating English to SQL I find it difficult to understand exactly what the prompt is asking.  For example, a prompt might as for the total sales in dollars of a product.  This can be a tricky question because you can't simply just select the product price or the quantity sold.  You would need to perform a calculation during your select statement to have sales in dollars.  Knowing when and how to perform calculations, especially when joining tables, can be tricky.