w3d1-w3d2 revisions Flashcards
T/F: When working in a TDD paradigm and you encounter a bug, write a test to isolate and capture the bug.
T
T/F: Comment Ruby liberally
F; Use comments sparingly; Ruby code should strive to be self-explanatory.
How do && and || short-circuit?
&& short circuits on false, || short circuits on true.
What is ‘fail’ a synonym for?
‘raise’
T/F: Subqueries are run once and used by each row.
F; the subquery is run again for each row that calls it.
What is a join table, and does it need a primary key?
It is a table that serves to join together two otherwise unrelated tables. It needs a primary key, even if it is never used.
When planning a SQL workflow, when is a good time to use a JOIN?
When you need a specific piece of data.
Describe an efficient SQL workflow.
1) Identify the queries that will be needed
2) Start with the FROM clause
3) Break down the pieces of data you need; each one will probably be a JOIN
4) In your WHERE clause, reference the alias of each JOIN
What is “normalization” in SQL, and how is it implemented?
Normalization refers to breaking apart repetitive data into different tables. For example, if you have a table with post and user columns, but many posts are by the same user, it makes more sense to make the user column a foreign key value that points to the primary (id) key of the users table.
This allows you to make a change in one place, and should be implemented whenever you encounter repetition in a table.
When should you use an INNER JOIN?
When you don’t care if you lose values that don’t have a match.
When should you use an OUTER JOIN?
When you want to retain values, even if they don’t have a match (NULL value)
What are the naming conventions for foreign keys?
They should be nameofothertable_id.id
Ex:
posts.user_id = user.id
What is the difference between COUNT(*) and COUNT(colname)
The former will count all rows including NULLs, whereas the latter will only count values that are not NULL.
What is the ‘singleton’ gem, and why bother with it?
It restricts a class to creating only one instance of itself. It is important for threading (i.e. two users shouldn’t be able to access a value at the same time), and avoids large costs (if the size of the instance is massive)
What happens when you JOIN tables where there is a column name collision (besides the keys you are matching)
The column that is being merged to the other column (read: the SECOND one) is retained, the other is not.
What JOIN should you use when you want to augment a table (i.e. not lose any data?)
Use a LEFT OUTER JOIN
Describe the relationship between Ruby classes of tables and SQL tables.
Each row becomes an instance of the table’s class. The table class should be named as the singular version of the table name.
What is a SQL Injection?
SQL takes in values via interpolation (‘?’ in Ruby). Malicious code can be inserted by hackers if there are no restrictions on what can be interpolated.
T/F: When interpolating a ‘?’ in a SQL string, an escape character is NOT needed.
T
How are ‘?’ used in SQL string interpolation.
A list of arguments is given in the heredoc, every time a ‘?’ is reached, a value is shifted off of that list and stands in for that ‘?’
How do you declare a foreign key in a schema?
Declare it as a regular column first; afterwords specify that it is a foreign key.
How can a heredoc take arguments so that ‘?’ do not have to be used, and values do not have to be listed in the order they will be used?
Use a hash:
(«-SQL, name: val)
You can then get the val by referencing :name in the string.
What does the LIMIT(n) function do?
Takes the first n values.
