Schemas for namespace organization in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

PostgreSQL

CREATE SCHEMA [1];

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

Asales

Bdatabase

Ctable

Dpublic

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'database' or 'table' instead of a schema name.

Trying to create a schema without a name.

The CREATE SCHEMA command followed by the schema name creates a new schema. Here, 'sales' is the schema name.

2fill in blank

medium

Complete the code to set the search path to the 'sales' schema.

PostgreSQL

SET search_path TO [1];

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

Apublic

Bsales

Cmain

Ddefault

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'public' when the goal is to use 'sales'.

Using invalid schema names.

The SET search_path TO command changes the schema search order. Setting it to 'sales' makes that schema the default for object lookups.

3fill in blank

hard

Fix the error in the code to create a table named 'orders' inside the 'sales' schema.

PostgreSQL

CREATE TABLE [1].orders (id SERIAL PRIMARY KEY, amount NUMERIC);

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

Apublic

Bdefault

Cmain

Dsales

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting the schema name and creating the table in the default schema.

Using a schema name that does not exist.

To create a table inside a specific schema, prefix the table name with the schema name and a dot. Here, 'sales.orders' creates the table in the 'sales' schema.

4fill in blank

hard

Fill both blanks to query all rows from the 'orders' table in the 'sales' schema.

PostgreSQL

SELECT * FROM [1].[2];

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

Asales

Borders

Cpublic

Dcustomers

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping schema and table names.

Using the wrong schema or table name.

To query a table in a specific schema, use schema_name.table_name. Here, 'sales.orders' selects from the 'orders' table in the 'sales' schema.

5fill in blank

hard

Fill all three blanks to grant SELECT permission on the 'orders' table in the 'sales' schema to user 'analyst'.

PostgreSQL

GRANT [1] ON [2].[3] TO analyst;

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

ASELECT

Bsales

Corders

DUPDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong permission like UPDATE instead of SELECT.

Mixing up schema and table names.

The GRANT SELECT ON schema.table TO user command gives read permission on the table. Here, 'SELECT' is the permission, 'sales' is the schema, and 'orders' is the table.