Aliases in SQL

Did you know you can list your tables as aliases in SQL?

Say you have a table called "quiz" with unique user_id's.

You could list your table as "q" (or whatever you like) and call its user_id column in the following way:
SELECT q.user_id
FROM quiz q
LIMIT 10;

Did you see that?  Where you place your table name ("quiz"), you can place your variable name after it, i.e. "q". 

This tidies up your code a little bit.  Say you had two other columns called "question" and "answer" - you could select the three columns by using the code: 
SELECT q.user_id, q.question, q.answer
FROM quiz q
LIMIT 10; 

Below is some example code from Codecademy's Funnels excercises on their Data Science course: 



SELECT q.user_id, 
h.user_id IS NOT NULL AS 'is_home_try_on', 
  h.number_of_pairs, 
  p.user_id IS NOT NULL AS 'is_purchase'
FROM quiz q
LEFT JOIN home_try_on h
ON q.user_id = h.user_id
LEFT JOIN purchase p
ON p.user_id = q.user_id
LIMIT 10; 

Here is a link to the W3 School's examples of how to create SQL aliases: https://www.w3schools.com/sql/sql_alias.asp

Remember this about joins in SQL...

You may be confused by the differences between joins in SQL.

A standard JOIN or inner-join in SQL will join tables where the rows are exactly matching on the column that you are joining on.  It will automatically not include non-matching rows, so that you are only presented with rows that are consistent.  The benefit of this is that you are only presented with consistent and accurate information.   The disadvantage is that you are missing information from some items.

However, a LEFT JOIN will join tables on a column where rows may not be matching (inconsistency between tables can be caused by one table being updated, but corresponding information in another table not being updated). In this scenario, you are given all information, but some attributes may be listed as NULL.

Image above from codecademy.com
https://s3.amazonaws.com/codecademy-content/courses/learn-sql/multiple-tables/left-join.gif

A CROSS JOIN creates a Cartesian Product.  This means that it will allow us to combine all rows of one table with all rows of another table. If there are three rows in table A, and three rows in table B, all three rows of table A will be joined with all three rows of table B.  The results of this join will have 9 rows.

SQLite strftime() Function

Did you know that strftime() is an SQLite function than allows the programmer to return a formatted date. 

It takes two arguments: 

strftime(format, column)

• To get an hour: strftime('%H', column_name)
• 
  
• To get the year: strftime('%Y', column_name)
• 
  
• To get the month: strftime('%m', column_name)
• 
  
• To get the day: strftime('%d', column_name)
• 
  
• To get the minute: strftime('%m', column_name)
• 
  
• To get the second: strftime('%S', column_name)

The above is true as long as the time format is YYYY-MM-DD HH:MM:SS

More on this function can be read from the SQL documentation here.