Keys, Join, and Union

Question 1

Primary Keys

Answer 1

are used to uniquely identify a row on a table.

Question 2

Natural Key

Answer 2

a unique value in the data that can be used to identify a specific row on the table. (example a student id)

Question 3

Surrogate Key

Answer 3

a generated unique value that is used when no natural key is available (example a sequentially generated number)

Question 4

Composite Key

Answer 4

when a 2 or more columns are used as the key to identify a unique row on a table. (example a card suit and rank)

Question 5

Foreign Key

Answer 5

Exist in other tables to reference a unique related row in the source table. Used to create relationships between tables.
Usually References a primary key, but can reference any column that contains a unique value that can be used to identify a specific row.
Can reference a composite key, but all columns that make up the primary key on the source table must be referenced on the table.

Question 6

Cardinality, Degrees of Cardinality

Answer 6

One-to-One (1:1)
One-to-Many (1:N)
Many-to-Many (M:N)

Question 7

One-to-One (1:1) Cardinality

Answer 7

One entity of data on a table relates to a single entity of data on another table.
An entity on one table relates to a single entity on a second table.
Requires no extra tables, a foreign key can be used either way without duplicating data.

Question 8

One-to-Many (1:N) Cardinality

Answer 8

One entity of data on a table relates to a multiple entities of data on another table.
An entity on one table can relate to multiple entities on a second table.
Requires no extra table, the table on the 1 side’s primary key can be added to the table with the M relationships without duplication of data

Question 9

Many-to-Many (M:N) Cardinality

Answer 9

Multiple entities of data on a table relates to a multiple entities of data on another table.

Question 10

Joins

Answer 10

SQL JOINs allow us to create queries that produce data from one or more tables.
Related records are “joined” into a single result.
Joins are referred to as INNER and OUTER.

Question 11

Inner Join(default)

Answer 11

Selects all rows from both participating tables as long as there is a match between the columns.

Question 12

Left Join

Left Outer Join

Answer 12

SELECT one.number AS one_number, one.description as one_description, two.number as two_number, two.description as two_description
FROM one
LEFT JOIN two ON one.number = two.number

I’d stress that things that don’t have a match on the right have null values for columns from that table in the results.

Question 13

Right Join

Right Outer Join

Answer 13

SELECT one.number AS one_number, one.description as one_description, two.number as two_number, two.description as two_description
FROM one
RIGHT JOIN two ON one.number = two.number

Question 14

Question: What is the difference between a LEFT JOIN and a LEFT OUTER JOIN?

Answer 14

Answer: Nothing, they are the same!

Question 15

Question: What is the difference between a RIGHT JOIN and RIGHT OUTER JOIN?

Answer 15

Answer: Nothing, they are the same!

Question 16

Full Outer Join

Answer 16

SELECT one.number AS one_number, one.description as one_description, two.number as two_number, two.description as two_description
FROM one
FULL OUTER JOIN two ON one.number = two.number

Question 17

Only the Left Table Values

Unnamed

Answer 17

SELECT one.number AS one_number, one.description as one_description, two.number as two_number, two.description as two_description
FROM one
LEFT JOIN two ON one.number = two.number
WHERE two.number IS NULL

Question 18

Only the Right Table Values

Unnamed

Answer 18

SELECT one.number AS one_number, one.description as one_description, two.number as two_number, two.description as two_description
FROM one
RIGHT JOIN two ON one.number = two.number
WHERE one.number IS NULL

Question 19

In the LEFT or RIGHT, but not both

Unnamed

Answer 19

SELECT one.number AS one_number, one.description as one_description, two.number as two_number, two.description as two_description
FROM one
FULL OUTER JOIN two ON one.number = two.number
WHERE one.number IS NULL OR two.number IS NULL

Question 20

Union

Answer 20

A SQL UNION combines the results of two or more queries into a single result set.
The number of columns involved must match exactly and data types must be identical.
Duplicate rows are removed.