Ch16: The SQL Sequel

Question 1

Describe a one-to-many relationship

Answer 1

Each entry in a table relates to many rows

Question 2

Define primary key

Answer 2

unique numerical identifier given to an entry

Question 3

Define foreign key

Answer 3

Integers that tie directly to primary key of entries in a different table

Question 4

What does the following achieve in MySQL? column_name INTEGER PRIMARY KEY AUTO_INCREMENT

Answer 4

Creates a primary key column

Question 5

What does the following achieve in MySQL? 
column_name INTEGER,
FOREIGN KEY (column_name) REFERENCES other_table(primary_key_column)

Answer 5

Creates a foreign key

Question 6

(T/F) Primary key and foreign key columns should share a name

Answer 6

True, this convention helps keep relationships between table clear

Question 7

(T/F) Each table in a database should have a primary key

Answer 7

True

Question 8

(T/F) You should define the foreign key column for a table fulfilling the one role of a one-to-many relationship

Answer 8

False, add the foreign key column to a table fulfilling the MANY role

Question 9

What performance considerations are there when utilizing a one-to-one relationship

Answer 9

Might increase performance overhead to have to link table rows to query related data. However, if some columns are rarely retrieved may make sense to separate them out so only the most frequently used data is queried.

Question 10

(T/F) An additional table is needed to reflect many-to-many relationships

Answer 10

True, including a joining/bridging/mapping table that sits between two tables with this relationship is considered a more robust data structure

Question 11

(T/F) When importing tables it is helpful to use the same column names as the external source

Answer 11

True

Question 12

Define subquery

Answer 12

SQL query embedded in another query. Usually placed within parentheses.

Question 13

(T/F) Subquery must be placed on the left hand side of a comparison operator in a WHERE clause

Answer 13

False, place subquery on right hand side of comparison operator in WHERE clause

Question 14

The ____ keyword allows the combining of the results of separate SELECT commands

Answer 14

UNION

Question 15

MySQL has no FULL OUTER JOIN syntax. How could you combine all data from two tables in MySQL?

Answer 15

Use UNION between LEFT JOIN and RIGHT JOIN queries

Question 16

What does the SQL function MAX(column_name) achieve?

Answer 16

Returns largest value in the specified column