THE CLOUD EXPEDITION! · Chapter 4

Stock the Pond

Marina swims up with an armload of data scrolls. 'An empty database is like an empty pond — no fun at all!' She winks. 'Lucky for you, my lake's species data is publicly shared. Let's copy it into YOUR database and make it your own.' Learn to Populate a Database — copying data from a public share into your own cloud database.

Your task

Copy Marina Lake's species data into your own database, then verify the results. First create a table in your database using <code>CREATE OR REPLACE TABLE ... AS SELECT</code>, then query the top 5 species by population. Run both statements together.<br><br><b>Re-attach if needed:</b><ul><li><code>dbquacks_marina_lake</code> → <code>md:_share/marina_lake_db/3be07852-dfce-4b29-b415-99c9f7fdcc6a</code></li></ul>

Hint

Use CREATE OR REPLACE TABLE ... AS SELECT to copy data into your new database

Expected result

5 rows with columns: species_name, population_count from your own database's my_species table

Tables

lake_visits (500 rows)

columntype
visit_idBIGINT
duck_idBIGINT
destination_pondVARCHAR
visit_dateDATE
purposeVARCHAR

pondville_events (50 rows)

columntype
event_idBIGINT
event_nameVARCHAR
event_dateDATE
organizer_idBIGINT
locationVARCHAR
attendee_countBIGINT

pondville_residents (100 rows)

columntype
duck_idBIGINT
nameVARCHAR
speciesVARCHAR
home_pondVARCHAR
ageBIGINT
occupationVARCHAR
joined_dateDATE

dbquacks_marina_lake.species

A table in a MotherDuck share; its columns are not listed here.

Database: https://dbquacks.com/data/databases/motherduck_tutorial.duckdb

This page as markdown: https://dbquacks.com/motherduck/10.md

Start level 10 →

DuckDB docs for this lesson

All levels

  1. 1. Connect DuckDB to MotherDuck
  2. 2. Query a MotherDuck Table
  3. 3. MotherDuck Hybrid Queries
  4. 4. DESCRIBE a MotherDuck Table
  5. 5. Join Local and Cloud Tables
  6. 6. Aggregate Cloud Data in MotherDuck
  7. 7. ATTACH a MotherDuck Share
  8. 8. Query Several MotherDuck Shares
  9. 9. CREATE DATABASE in MotherDuck
  10. 10. Copy Data into a MotherDuck Table
  11. 11. CREATE SHARE in MotherDuck
  12. 12. DuckDB read_csv from a URL
  13. 13. DuckDB read_parquet from a URL
  14. 14. Join a Remote CSV with Cloud Tables
  15. 15. DuckLake Time Travel
  16. 16. Compare DuckLake Snapshots
  17. 17. DuckLake Time Travel with Shares
  18. 18. Aggregate 100 Million Rows in MotherDuck
  19. 19. EXPLAIN a MotherDuck Hybrid Query
  20. 20. Combine Local, Cloud and DuckLake Data

More: every SQL tutorial lesson · practice SQL exercises online