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.

Practice

1. What is the main purpose of creating schemas in PostgreSQL?

easy

A. To encrypt the database tables

B. To speed up query execution automatically

C. To create backups of the database

D. To group database objects and organize them logically

Solution

1. Step 1: Understand what schemas do

   Schemas help organize tables, views, and other objects into groups.

2. Step 2: Identify the main purpose

   This organization helps keep the database tidy and manageable.

3. Final Answer:

   To group database objects and organize them logically -> Option D

4. Quick Check:

   Schemas = Organize objects [OK]

Hint: Schemas group objects to keep your database tidy [OK]

Common Mistakes:

• Thinking schemas speed up queries automatically
• Confusing schemas with backups
• Assuming schemas encrypt data

2. Which of the following is the correct syntax to create a new schema named sales?

easy

A. NEW SCHEMA sales;

B. CREATE SCHEMA sales;

C. MAKE SCHEMA sales;

D. CREATE DATABASE sales;

Solution

1. Step 1: Recall the command to create schemas

   The correct SQL command is CREATE SCHEMA schema_name;.

2. Step 2: Match the syntax

   CREATE SCHEMA sales; matches this syntax exactly for schema sales.

3. Final Answer:

   CREATE SCHEMA sales; -> Option B

4. Quick Check:

   CREATE SCHEMA = create schema [OK]

Hint: Use CREATE SCHEMA followed by the schema name [OK]

Common Mistakes:

• Using MAKE or NEW instead of CREATE
• Confusing CREATE SCHEMA with CREATE DATABASE
• Missing the semicolon at the end

3. Given the following commands:

CREATE SCHEMA marketing;
SET search_path TO marketing;
CREATE TABLE campaigns (id SERIAL PRIMARY KEY, name TEXT);

What schema will the table campaigns belong to?

medium

A. marketing

B. None, it will cause an error

C. default

D. public

Solution

1. Step 1: Understand SET search_path effect

   Setting search_path to marketing means new objects are created in that schema by default.

2. Step 2: Check where the table is created

   The CREATE TABLE campaigns command creates the table inside the current schema in search_path, which is marketing.

3. Final Answer:

   marketing -> Option A

4. Quick Check:

   SET search_path = schema for new objects [OK]

Hint: SET search_path sets default schema for new tables [OK]

Common Mistakes:

• Assuming tables go to public schema by default
• Thinking SET search_path only affects queries, not creation
• Believing this causes an error without schema prefix

4. You run the command SET search_path TO sales; but get an error: ERROR: schema "sales" does not exist. What is the most likely cause?

medium

A. The schema sales was not created before setting search_path

B. You need to use USE sales; instead

C. The database connection is closed

D. The table sales does not exist

Solution

1. Step 1: Understand error meaning

   The error says the schema sales does not exist, so PostgreSQL cannot set search_path to it.

2. Step 2: Identify cause

   This usually means the schema was never created with CREATE SCHEMA sales;.

3. Final Answer:

   The schema sales was not created before setting search_path -> Option A

4. Quick Check:

   SET search_path needs existing schema [OK]

Hint: Create schema before setting search_path to it [OK]

Common Mistakes:

• Using USE instead of SET search_path in PostgreSQL
• Confusing tables with schemas
• Ignoring the error and continuing

5. You have two schemas: public and archive. You want to query a table named orders in archive without typing the full name every time. Which command will let you switch your session to use archive schema first, then public?

hard

A. USE archive, public;

B. ALTER SCHEMA archive SET search_path;

C. SET search_path TO archive, public;

D. SET schema TO archive, public;

Solution

1. Step 1: Recall how to set search_path for multiple schemas

   PostgreSQL uses SET search_path TO schema1, schema2; to set schema priority.

2. Step 2: Identify correct syntax

   SET search_path TO archive, public; correctly sets archive first, then public.

3. Final Answer:

   SET search_path TO archive, public; -> Option C

4. Quick Check:

   SET search_path sets schema order [OK]

Hint: Use SET search_path with comma-separated schemas [OK]

Common Mistakes:

• Using USE instead of SET search_path
• Trying ALTER SCHEMA to set search_path
• Using SET schema instead of SET search_path