Databases and Schemas - Part II
1. Databases and Schemas - Part II
In the last video, we started learning about databases and schemas. This would probably be a much more exciting and mysterious video if we were about to cover “Databases and Schemes.” But I think you’ll find that schemas are exciting and mysterious in their own way. Let’s get back to it and start putting our knowledge into practice. Okay, now it’s time to exercise our database and schema muscles. First we’ll create a database using the UI. Go to the “Data” tab in the left-hand menu, click databases, and then click “+ Database.” We’ll name the database “test_database,” and click “Create.” Now we can see the new database, and if we click on it, and then click on “Schemas,” we’ll see it only has the two schemas that you automatically get when you create any database – PUBLIC, and INFORMATION_SCHEMA. Now let’s drop this database by clicking the three dots at the top, and selecting “Drop.” If we refresh our screen, we’ll see it’s gone. We can do all of those things through commands in our SQL worksheet, so let’s do that quickly. Go back to “Worksheets,” and select “Databases and Schemas,” then type: CREATE DATABASE test_database; And run that command. Once we refresh, we’ll see that database listed in the UI, and if we click on that, we’ll see it also has the two default schemas – PUBLIC, and INFORMATION_SCHEMA. We can also use the SHOW DATABASES command to see our new database. You’ll see that “TEST_DATABASE” is there. We can see that it’s the current database, and if we scroll over, we see that it’s a “standard database,” whereas “Snowflake Sample Data” is imported, and “Snowflake” – the one with all the metadata – is of the kind “application.” We can then drop our database by running the “DROP DATABASE” command, followed by the database name – in this case, “test_database.” If we refresh, we’ll see it’s gone. This next part is super cool – We can also *undrop* our database! Just type “UNDROP DATABASE test_database” and we’ll get it back! If we use the “SHOW DATABASES” command, we’ll see our test_database, back from the dead, though notice it’s no longer the current database. If we want to make it our current database, we can run the “USE DATABASE” command – just type “USE DATABASE” followed by the database name. You’ll notice that the little context dropdown at the top switched to the database “TEST_DATABASE” and the default schema, “Public.” This next point is an important one. All the commands we just ran for databases we can also run for schemas – and actually, with the exception of “USE,” for tables as well. CREATE. DROP. UNDROP. SHOW. USE. For these commands – plus one called “alter,” which we haven’t used here – Snowflake has made life easy by keeping the same syntax whether we’re working with databases, schemas, or tables. We’ll discuss tables more in the next video. Okay, so let’s move on to schemas for a moment, though we’ll move quickly here because, as I just mentioned, the mechanics of working with schemas are often similar to the mechanics of working with databases. If we go to the left-hand menu and click Data > Databases > TEST_DATABASE, we can then take a look at the schemas. If you click “+ schema” in the top right corner, you can make a schema, though we’ll skip that and create our schema directly in a SQL worksheet. Go to Projects > Worksheets > “Databases and Schemas,” and create a schema with the command: CREATE SCHEMA test_schema; Because our context was still set to the database “TEST_DATABASE,” we created this “TEST_SCHEMA” inside that database. If we use the “SHOW SCHEMAS” command, we can see all the schemas inside this database – and as expected, we see the two automatically generated schemas (public and information_schema), plus our new schema. You can get similar information by running the command “DESCRIBE DATABASE” followed by the name of the database, though you’ll notice that it’s less descriptive – only three columns here. Now let’s drop our schema using “DROP SCHEMA” followed by the schema name: DROP SCHEMA test_schema; If we run SHOW SCHEMAS, we’ll see that it’s gone. But then we can run UNDROP SCHEMA + our schema name (test_schema) and get it back again. UNDROP SCHEMA test_schema; Let’s run “SHOW SCHEMAS” to confirm. Awesome! We’ve created a database, showed our database, dropped and undropped our database, and selected a database with USE DATABASE. We then did the same things for schemas. We learned that a database is a collection of one or more schemas, and a schema is a collection of objects such as tables and views. We also learned about the two automatically generated schemas – public, and information_schema. We’re making great progress – Now let’s move on to tables.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.