Week 101 – Easy

Ahoy, matey! Welcome to the Pirate SQL Challenge!

Your mission is to manage the data of infamous pirates and their treasures. You will be working with three tables: a source table src containing details of the pirates and their loot, and two destination tables t1 and t2 where you’ll be inserting data based on certain conditions:

• Pirates with booty amounts greater than 700 units should be inserted into t1 only.
• Pirates with the rank “First Mate” should be inserted into t2 only.
• Pirates with less than 100 units of booty should be inserted into both tables.
• All other pirates should be inserted into t2 only.

Follow the steps below to create the tables and insert the data as per the conditions. To solve the challenge, take a look at the types of INSERT (multi-table) we can use in Snowflake.

-- Creating the destination tables t1 and t2
CREATE OR REPLACE TABLE t1 (
    pirate_name STRING,
    booty_amount NUMBER,
    rank STRING,
    ship_name STRING
);

CREATE OR REPLACE TABLE t2 (
    pirate_name STRING,
    booty_amount NUMBER,
    rank STRING,
    ship_name STRING
);

-- Creating the source table src with pirate-themed data
CREATE OR REPLACE TABLE src (
    pirate_name STRING,
    booty_amount NUMBER,
    rank STRING,
    ship_name STRING
);

-- Inserting data into the src table
INSERT INTO src (pirate_name, booty_amount, rank, ship_name) VALUES
    ('Blackbeard', 500, 'Captain', 'Queen Anne\'s Revenge'),
    ('Anne Bonny', 300, 'First Mate', 'Revenge'),
    ('Calico Jack', 200, 'Captain', 'Ranger'),
    ('Henry Morgan', 1000, 'Admiral', 'Oxford'),
    ('Bartholomew Roberts', 400, 'Captain', 'Royal Fortune'),
    ('Mary Read', 150, 'Quartermaster', 'Ranger'),
    ('Stede Bonnet', 50, 'Captain', 'Revenge'),
    ('Charles Vane', 250, 'Captain', 'Lark'),
    ('Jack Sparrow', 800, 'Captain', 'Black Pearl'),
    ('William Kidd', 600, 'Captain', 'Adventure Galley');

May the wind be at your back as you navigate the treacherous waters of SQL and prove your mettle as a true pirate captain! Like our comrade Niccolò who suggested the theme of the challenge!

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge.