Ben Gorman

Ben Gorman

Life's a garden. Dig it.

Challenge

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

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

and

| 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

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

Solution

This content is gated

Subscribe to one of the products below to gain access