Retrieving data from multiple tables

Question 1

In MySQL what is the default JOIN type?

Answer 1

inner join

Question 2

What two types of JOIN’s are available in MySQL?

Answer 2

inner and outer JOIN’s

Question 3

When selecting a column that’s present on multiple tables what should be prefixed when making a selection?

Answer 3

the table name or alias

(customer_id being present on multiple tables)

SELECT orders.customer_id, first_name, last_name

Question 4

This code has a lot of repeat words, refactor it. Using alias.

• *SELECT** order_id, orders.customer_id, first_name, last_name
• *FROM** orders
• *JOIN** customers ON orders.customer_id = customers.customer_id

Answer 4

• *SELECT** order_id, o.customer_id, first_name, last_name
• *FROM** orders o
• *JOIN** customers ON o.customer_id = customers.customer_id

Question 5

True or False

These aliases would return the same results

FROM orders AS o

FROM orders o

Answer 5

True

Question 6

What are the two aliases being used in this query?

• *SELECT** order_id, o.customer_id, first_name, last_name
• *FROM** orders o
• *JOIN** customers c ON o.customer_id = c.customer_id

Answer 6

o and c

Question 7

True or False

When querying multiple tables, it’s best to:

1. SELECT * (from every table)
2. then make the relevant JOIN first (using aliases)
3. then filter the columns needed.

Answer 7

True

Question 8

True or False

To join multiple tables in different databases you’d prefix the database in the JOIN statement?

Answer 8

True

Question 9

Identify the other database being used in this query?

• *SELECT** *
• *FROM** order_items oi
• *JOIN** sql_inventory.products p
• *ON** oi.product_id = p.product_id

Answer 9

sql_inventory

Question 10

True or False

In MySQL, it’s possible to JOIN a table with itself? What is the benefit?

USE sql_hr;

• *SELECT**
  e. employee_id,
  e. first_name,
  m. first_name AS manager
• *FROM** employees e
• *JOIN** employees m ON e.reports_to = m.employee_id

Answer 10

True

Good for making organizational charts shows who reports to whom if the data is available.

Question 11

How many tables are being joined in this query?

• *USE** sql_store;
• *SELECT**
  o. order_id,
  o. order_date,
  c. first_name,
  os. name AS status
• *FROM** orders o
• *JOIN** customers c
• *ON** o.customer_id = c.customer_id
• *JOIN** order_statuses os
• *ON** o.status = os.order_status_id

Answer 11

3 tables

orders
customers
order_statuses

Question 12

In MySQL what is a composite primary key?

Answer 12

A table where two columns define the primary key.

Question 13

This query is an example of what?

• *SELECT** *
• *FROM** order_items oi
• *JOIN** order_item_notes oin
• *ON** oi.order_id = oin.order_id
• *AND** oi.product_id = oin.product_id

Answer 13

Joint compound condition, more than two columns are the primary keys.

Question 14

Refactor this query using implicit join syntax

• *SELECT** *
• *FROM** orders o
• *JOIN** customers c
• *ON** o.customer_id = c.customer_id

Answer 14

• *SELECT** *
• *FROM** orders o, customers c
• *WHERE** o.customer_id = c.customer_id

Question 15

What is a potential peril using an implicit join syntax?

Answer 15

If you forget the WHERE clause you’d end up with a funky cross-join.

Question 16

True or False

It’s better to use an implicit join statement when executing queries?

Answer 16

True

Question 17

What two types of OUTER JOINS are available in slq?

Answer 17

LEFT
RIGHT

Question 18

True or False

In MySQL a LEFT JOIN will include all the data in the left table regardless of any other condition being met?

Answer 18

True

• *SELECT**
  c. customer_id,
  c. first_name,
  o. order_id
• *FROM** customers c – all records returned from left table
• *LEFT JOIN** orders o – left join
• *ON** c.customer_id = o.customer_id – regardless if this is true
• *ORDER BY** c.customer_id

Question 19

What is the left table in this query?

• *SELECT**
  c. customer_id,
  c. first_name,
  o. order_id
• *FROM** customers c
• *LEFT JOIN** orders o
• *ON** c.customer_id = o.customer_id
• *ORDER BY** c.customer_id

Answer 19

customers table

Question 20

What is the right table in this query?

• *SELECT**
  c. customer_id,
  c. first_name,
  o. order_id
• *FROM** customers c
• *RIGHT JOIN** orders o
• *ON** c.customer_id = o.customer_id
• *ORDER BY** c.customer_id

Answer 20

orders table

same results returned by an inner default join in this instance

Question 21

True or False

In MySQL, the position of the left & right tables in outer joins can change the data being returned.

Answer 21

True

Question 22

True or False

In MySQL the OUTER and INNER keywords are required?

Answer 22

False

JOIN - default inner

LEFT - outer

RIGHT - outer

Question 23

What is the power of an OUTER JOIN?

Answer 23

Returns result from the left table regardless if the join condition is met. Much like returning null values from a query on products sold and shipped.

Question 24

As a rule of thumb it’s best practice to avoid RIGHT JOINS in MySQL?

Answer 24

True

Just switch which table is on the left and use a LEFT JOIN

Question 25

If you are shown a table and are asked to write a query, but the columns can’t be found in any of the tables, what is likely the cause?

Answer 25

Some of the columns have aliases.

• *SELECT**
  o. order_id,
  o. order_date,
  c. first_name AS customer,
  sh. name AS shipper,
  os. name AS status
• *FROM** orders o
• *JOIN** customers c
• *ON** o.customer_id = c.custom
• *ON** o.shipper_id = sh.shipper_ider_id
• *LEFT JOIN** shippers sh
• *JOIN** order_statuses os
• *ON** o.status = os.order_status_id

Question 26

This query is an example of a?

USE sql_hr;

• *SELECT**
  e. employee_id,
  e. first_name,
  m. first_name AS manager
• *FROM** employees e
• *LEFT JOIN** employees m
• *ON** e.reports_to = m.employee_id

Answer 26

self outer join (left)

Question 27

Refactor this query with the USING clause

• *SELECT**
  o. order_id,
  c. first_name
• *FROM** orders o
• *JOIN** customers c
• *ON** o.customer_id = c.customer_id

Answer 27

• *SELECT**
  o. order_id,
  c. first_name
• *FROM** orders o
• *JOIN** customers c
• *USING** (customer_id)

Question 28

In order to use the USING clause when joining tables, the columns must:

a. not match
b. match
c. doesn’t matter

Answer 28

b. columns must match and present in both tables.

Question 29

Refactor this query with the USING statement

• *SELECT** *
• *FROM** order_items oi
• *LEFT JOIN** order_item_notes oin
• *ON** oi.order_id = oin.order_id AND
  oi. product_id = oin.product_id

Answer 29

• *SELECT** *
• *FROM** order_items
• *LEFT JOIN** order_item_notes
• *USING** (order_id, product_id)

Question 30

In MySQL natural joins are what?

• *SELECT**
  o. order_id,
  c. first_name
• *FROM** orders o
• *NATURAL JOIN** customers c

Answer 30

A join controlled by the database schema, can produce bad results best to avoid. It will make the determination is inner, outer (left, right) join is used.

Question 31

Cross joins are used to?

Answer 31

Join every record from the left table to the right table accepts no conditions.

Question 32

What type of cross join is this?

SELECT
c.first_name AS customer,
p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name

Answer 32

explicit

Question 33

What type of fucked up cross join is this?

• *SELECT**
  c. first_name AS customer,
  p. name AS product – no p alias
• *FROM** customers c, orders o
• *ORDER BY** c.first_name

Answer 33

implicit cross join

Question 34

What are the two outer joins in SQL?

Answer 34

right or left

Question 35

What does a UNION keyword do in MySQL?

SELECT
order_id,
order_date,
‘Active’ AS status
FROM orders
WHERE order_date >= ‘2019-01-01’
UNION
SELECT
order_id,
order_date,
‘Archived’ AS status
FROM orders
WHERE order_date < ‘2019-01-01’

Answer 35

combine multiple queries into one result set (same or multiple tables), columns in query must be equal