Journal 16

 May 7 - May 13

One example where joining on something other than keys would be needed is when linking search keywords to matching products, such as when a customer is looking for a type of product in a store without knowing the exact brand name. In English it would be: For each keyword, return all products whose name contains keyword as a substring. In SQL it would be:

SELECT k.keyword, p.product_id, p.product_name
FROM search_keywords AS k
JOIN products AS p
ON p.product_name LIKE CONCAT('%', k.keyword, '%');

In my opinion, SQL as a language is relatively straightforward in keywords and their intended meanings. I am finding it easy to learn and use, but I am also dedicating time to actually reading and taking notes on the textbook, so that is likely a contributing factor. When translating from an English question to SQL, I am finding syntax to be the most challenging portion, as there are specific orders in which keywords must be placed. I find myself often referencing back to my notes to be sure that I am writing my SQL code in the correct order. For example, the SQL line of

SELECT * FROM country
WHERE country code LIKE 'A_W';

makes sense grammatically. In contrast, I have to check back and reference my notes for correct order in longer statements such as 

SELECT Department.Name FROM Department
WHERE Manager = ID
UNION SELECT Department.Name, NULL FROM Department
WHERE Manager IS NULL
OR Manager NOT IN (SELECT ID FROM Employee
WHERE ID IS NOT NULL);

I'm sure these longer statements will become more engrained in my mind as time goes on and I won't need to reference my notes.