Public schema vs custom schemas in PostgreSQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all tables from the default schema in PostgreSQL.

PostgreSQL

SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = '[1]';

Drag options to blanks, or click blank then click option'

Apublic

Bcustom

Cdefault

Dmain

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'default' or 'main' instead of 'public' as the schema name.

Confusing schema with database name.

The default schema in PostgreSQL is named public. This query lists all tables in that schema.

2fill in blank

medium

Complete the code to create a new schema named 'sales'.

PostgreSQL

CREATE SCHEMA [1];

Drag options to blanks, or click blank then click option'

Apublic

Bdefault

Csales

Dmain

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to create a schema named 'public' which already exists.

Using keywords like 'default' or 'main' which are not schema names.

To create a custom schema named sales, use CREATE SCHEMA sales;.

3fill in blank

hard

Fix the error in the code to select from a table named 'orders' in the 'sales' schema.

PostgreSQL

SELECT * FROM [1].orders;

Drag options to blanks, or click blank then click option'

Apublic

Bmain

Cdefault

Dsales

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'public' when the table is in 'sales' schema.

Omitting the schema name causing an error if search_path doesn't include it.

To select from a table in a custom schema, prefix the table name with the schema name, like sales.orders.

4fill in blank

hard

Fill both blanks to set the search path to use the 'sales' schema first, then the default schema.

PostgreSQL

SET search_path TO [1], [2];

Drag options to blanks, or click blank then click option'

Asales

Bpublic

Cdefault

Dmain

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'default' or 'main' which are not valid schema names.

Reversing the order and putting 'public' first.

The search_path controls which schemas PostgreSQL looks in first. Setting it to sales, public means it looks in sales first, then public.

5fill in blank

hard

Fill all three blanks to grant usage on the 'sales' schema to user 'alice' and allow her to create tables.

PostgreSQL

GRANT [1], [2] ON SCHEMA [3] TO alice;

Drag options to blanks, or click blank then click option'

AUSAGE

BCREATE

Csales

DSELECT

Attempts:

3 left

💡 Hint

Common Mistakes

Granting 'SELECT' which is for tables, not schemas.

Using the wrong schema name like 'public' instead of 'sales'.

Granting USAGE allows access to the schema, and CREATE allows creating tables inside it. The schema name is sales.