Fruit Prices¶

Here's some code to make a dataset named mydataset with two tables,

fruits

| fruit_id|fruit   |
|--------:|:-------|
|     NULL|pears   |
|        1|apple   |
|        3|oranges |
|        2|bananas |

and

prices

| fruit_id| price|
|--------:|-----:|
|        3|  3.02|
|     NULL|  1.55|
|        1|  2.12|
|        2|  1.90|

-- Make a dataset named mydataset (in the default region)
CREATE SCHEMA `practice-probs.mydataset`;

-- Make a table named fruits (in mydataset)
CREATE OR REPLACE TABLE `practice-probs.mydataset.fruits` AS
SELECT 1 AS fruit_id, 'apple' AS fruit, 
UNION ALL SELECT 2 AS fruit_id, 'bananas' AS fruit
UNION ALL SELECT NULL AS fruit_id, 'pears' AS fruit
UNION ALL SELECT 3 AS fruit_id, 'oranges' AS fruit;

-- Make a table named prices (in mydataset)
CREATE OR REPLACE TABLE `practice-probs.mydataset.prices` AS
SELECT 1 AS fruit_id, 2.12 AS price, 
UNION ALL SELECT 2 AS fruit_id, 1.90 AS price
UNION ALL SELECT NULL AS fruit_id, 1.55 AS price
UNION ALL SELECT 3 AS fruit_id, 3.02 AS price;

Insert price as a column in the fruits table.

Expected Result

fruits

| fruit_id|fruit   | price|
|--------:|:-------|-----:|
|        1|apple   |  2.12|
|        2|bananas |  1.90|
|        3|oranges |  3.02|
|     NULL|pears   |  1.55|