w3d1

Question 1

SQL: How is an unknown value represented?

Answer 1

NULL

Question 2

What does NULL represent?

Answer 2

An unknown value

Question 3

How do you test to see if something matches (or doesn’t match) with NULL?

Answer 3

IS NULL

IS NOT NULL

Question 4

How do conditional statements work in SQL?

Answer 4

CASE statements

Question 5

What’s the general CASE statement syntax?

Answer 5

CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
[ELSE result_n]
END;

Question 6

What’s the simple CASE statement syntax?

Answer 6

CASE expression
WHEN value_1 THEN
result_1
WHEN value_2 THEN
result_2
...
ELSE
result_n
END;

Question 7

What does COALESCE do?

Answer 7

It selects the first non-NULL argument from its list.

Question 8

How do you select the first non-NULL argument from a list?

Answer 8

COALESCE

Question 9

What are the rails conventions for table names?

Answer 9

snake_case

pluralized

Question 10

What are the rails conventions for foreign ids?

Answer 10

single_form_of_table + _id

Example:
tablename is bands
foreign id is band_id

Question 11

What are some of SQL’s data types?

8-9: 5 stars
6-7: 4 stars
4-5: 3 stars
1-3: 1 star

Answer 11

BOOLEAN
INT
FLOAT
VARCHAR(255)
TEXT
BLOB
DATE
DATETIME
TIME
BLOB

Question 12

What are the 4 main SQL data manipulation operators?

Answer 12

SELECT
INSERT
UPDATE
DELETE

Question 13

What’s the basic SELECT structure?

Answer 13

SELECT
one or more columns (or all cols with *)
FROM
one (or more tables, joined with JOIN)
WHERE
one (or more conditions, joined with AND/OR)
;

Question 14

What’s the basic INSERT structure?

Answer 14

INSERT INTO
table_name (column names)
VALUES
(values)
;

Question 15

What’s the basic UPDATE structure?

Answer 15

UPDATE
table_name
SET
col_name1 = value1,
col_name2 = value2, 
...
WHERE
conditions
;

Question 16

What’s the basic DELETE syntax?

Answer 16

DELETE FROM
table_name
WHERE
conditions
;

Question 17

What’s the basic table creation syntax for a user with name, birth_date and address?

Answer 17

CREATE TABLE table_name (
 id INTEGER PRIMARY KEY,
name VARCHAR(100 NOT NULL,
birth_date DATE,
address VARCHAR(255)
);

Question 18

What’s the basic JOIN syntax?

Answer 18

SELECT
table1.colname, table2.colname
FROM
table1
JOIN
table2 ON table1.foreign_id = table2.id;

Question 19

What’s the 8-step order of operations of a SELECT query?

Answer 19

FROM and JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT / OFFSET