Tutorial Automation

  JOIN Questions Part I Question Mania Now that you have been introduced to JOINs, let's practice to build your skills and comfort with this new tool. Below I have provided the  ERD  and a bunch of questions. The solutions for the questions can be found on the next concept for you to check your answers or just in case you get stuck! I recommend testing your queries with the environment below, and then saving them to a file. Then compare your file to my solutions on the next concept! Questions and Solutions Provide a table for all the for all  web_events  associated with account  name  of  Walmart . There should be three columns. Be sure to include the  primary_poc , time of the event, and the  channel  for each event. Additionally, you might choose to add a fourth column to assure only  Walmart  events were chosen. SELECT a . primary_poc , w . occurred_at , w . channel , a . name FROM web_events w JOIN accounts a ON ...

  Alias When we  JOIN  tables together, it is nice to give each table an  alias . Frequently an alias is just the first letter of the table name. You actually saw something similar for column names in the  Arithmetic Operators  concept. Example: FROM tablename AS t1 JOIN tablename2 AS t2 Before, you saw something like: SELECT col1 + col2 AS total , col3 Frequently, you might also see these statements without the  AS  statement. Each of the above could be written in the following way instead, and they would still produce the  exact same results : FROM tablename t1 JOIN tablename2 t2 and SELECT col1 + col2 total , col3 Aliases for Columns in Resulting Table While aliasing tables is the most common use case. It can also be used to alias the columns selected to have the resulting table reflect a more readable name. Example: Select t1 . column1 aliasname , t2 . column2 aliasname2 FROM tablename AS t1 JOIN tablename2 AS t2 The al...

  JOIN More than Two Tables Look at the three tables below. The Code If we wanted to join all three of these tables, we could use the same logic. The code below pulls all of the data from all of the joined tables. SELECT * FROM web_events JOIN accounts ON web_events . account_id = accounts . id JOIN orders ON accounts . id = orders . account_id Alternatively, we can create a  SELECT  statement that could pull specific columns from any of the three tables. Again, our  JOIN  holds a table, and  ON  is a link for our  PK  to equal the  FK . To pull specific columns, the  SELECT  statement will need to specify the table that you are wishing to pull the column from, as well as the column name. We could pull only three columns in the above by changing the select statement to the below, but maintaining the rest of the JOIN information: SELECT web_events . channel , accounts . name , orders . total We could continue this sa...