w3d1-w3d2 revisions

Question 1

T/F: When working in a TDD paradigm and you encounter a bug, write a test to isolate and capture the bug.

Answer 1

T

Question 2

T/F: Comment Ruby liberally

Answer 2

F; Use comments sparingly; Ruby code should strive to be self-explanatory.

Question 3

How do && and || short-circuit?

Answer 3

&& short circuits on false, || short circuits on true.

Question 4

What is ‘fail’ a synonym for?

Answer 4

‘raise’

Question 5

T/F: Subqueries are run once and used by each row.

Answer 5

F; the subquery is run again for each row that calls it.

Question 6

What is a join table, and does it need a primary key?

Answer 6

It is a table that serves to join together two otherwise unrelated tables. It needs a primary key, even if it is never used.

Question 7

When planning a SQL workflow, when is a good time to use a JOIN?

Answer 7

When you need a specific piece of data.

Question 8

Describe an efficient SQL workflow.

Answer 8

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

Question 9

What is “normalization” in SQL, and how is it implemented?

Answer 9

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.

Question 10

When should you use an INNER JOIN?

Answer 10

When you don’t care if you lose values that don’t have a match.

Question 11

When should you use an OUTER JOIN?

Answer 11

When you want to retain values, even if they don’t have a match (NULL value)

Question 12

What are the naming conventions for foreign keys?

Answer 12

They should be nameofothertable_id.id

Ex:

posts.user_id = user.id

Question 13

What is the difference between COUNT(*) and COUNT(colname)

Answer 13

The former will count all rows including NULLs, whereas the latter will only count values that are not NULL.

Question 14

What is the ‘singleton’ gem, and why bother with it?

Answer 14

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)

Question 15

What happens when you JOIN tables where there is a column name collision (besides the keys you are matching)

Answer 15

The column that is being merged to the other column (read: the SECOND one) is retained, the other is not.

Question 16

What JOIN should you use when you want to augment a table (i.e. not lose any data?)

Answer 16

Use a LEFT OUTER JOIN

Question 17

Describe the relationship between Ruby classes of tables and SQL tables.

Answer 17

Each row becomes an instance of the table’s class. The table class should be named as the singular version of the table name.

Question 18

What is a SQL Injection?

Answer 18

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.

Question 19

T/F: When interpolating a ‘?’ in a SQL string, an escape character is NOT needed.

Answer 19

T

Question 20

How are ‘?’ used in SQL string interpolation.

Answer 20

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 ‘?’

Question 21

How do you declare a foreign key in a schema?

Answer 21

Declare it as a regular column first; afterwords specify that it is a foreign key.

Question 22

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?

Answer 22

Use a hash:

(«-SQL, name: val)

You can then get the val by referencing :name in the string.

Question 23

What does the LIMIT(n) function do?

Answer 23

Takes the first n values.