Creating and switching schemas in PostgreSQL - Practice Exercises

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this schema creation query?

Consider the following SQL commands executed in PostgreSQL:

CREATE SCHEMA sales;
CREATE TABLE sales.orders (id SERIAL PRIMARY KEY, amount INT);
SELECT * FROM sales.orders;

What will be the result of the SELECT query?

PostgreSQL

CREATE SCHEMA sales;
CREATE TABLE sales.orders (id SERIAL PRIMARY KEY, amount INT);
SELECT * FROM sales.orders;

A An empty result set with columns id and amount

B Error: relation "sales.orders" does not exist

C Error: schema "sales" does not exist

D A result set with one row containing id=1 and amount=NULL

💡 Hint

Think about what happens when you create a new table and select from it without inserting data.

The table sales.orders is created empty. Selecting from it returns zero rows but shows the columns defined.

❓ query_result

intermediate

What is the current schema after SET search_path?

Given these commands:

CREATE SCHEMA marketing;
SET search_path TO marketing, public;
SHOW search_path;

What will the SHOW search_path command output?

PostgreSQL

CREATE SCHEMA marketing;
SET search_path TO marketing, public;
SHOW search_path;

A "default"

B "public, marketing"

C "marketing, public"

D Error: invalid schema name

💡 Hint

The SET search_path command sets the order of schemas PostgreSQL looks into.

The search_path is set to 'marketing, public' so SHOW search_path returns that exact string.

📝 Syntax

advanced

Which option correctly creates a schema and switches to it?

Which of the following SQL command sequences correctly creates a schema named 'archive' and sets it as the current schema for subsequent commands?

A CREATE SCHEMA archive; SET search_path TO archive;

B CREATE SCHEMA archive; USE archive;

C CREATE SCHEMA archive; SWITCH TO archive;

D CREATE SCHEMA archive; SET SCHEMA archive;

💡 Hint

PostgreSQL uses search_path to control schema lookup, not USE or SWITCH TO.

In PostgreSQL, USE and SWITCH TO are not valid commands. The correct way to change schema context is SET search_path.

🔧 Debug

advanced

Why does this query fail after creating a schema?

A user runs these commands:

CREATE SCHEMA reports;
CREATE TABLE reports.data (id INT);
SELECT * FROM data;

The SELECT query fails with "relation \"data\" does not exist". Why?

PostgreSQL

CREATE SCHEMA reports;
CREATE TABLE reports.data (id INT);
SELECT * FROM data;

A Because the table 'data' was not created successfully

B Because the search_path does not include the 'reports' schema, so 'data' is not found

C Because SELECT * FROM data requires schema qualification even if search_path is set

D Because CREATE SCHEMA must be followed by SET SCHEMA to access tables

💡 Hint

Think about how PostgreSQL finds tables without schema prefix.

Without setting search_path to include 'reports', PostgreSQL looks only in 'public' by default and cannot find 'data'.

🧠 Conceptual

expert

What is the effect of setting search_path to "$user", public?

In PostgreSQL, what does setting search_path to "$user", public do?

A It causes an error because "$user" is not a valid schema name

B It searches only the public schema ignoring user schemas

C It sets the schema to a fixed schema literally named "$user"

D It first searches a schema named after the current user, then the public schema

💡 Hint

"$user" is a special placeholder in PostgreSQL search_path.

The "$user" placeholder is replaced by the current database user name, so PostgreSQL searches that schema first, then public.