Schemas for namespace organization in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Querying tables in different schemas

Consider two schemas sales and hr in a PostgreSQL database. Each schema has a table named employees. What will the following query return?

SELECT * FROM sales.employees;

A All rows from the employees table in the sales schema

B All rows from the employees table in the hr schema

C All rows from employees tables in both sales and hr schemas combined

D An error because employees table exists in multiple schemas

💡 Hint

Think about how schema-qualified names work in PostgreSQL.

When you specify a schema name before a table, PostgreSQL queries that specific table only. So sales.employees refers to the employees table inside the sales schema.

🧠 Conceptual

intermediate

Purpose of schemas in PostgreSQL

What is the main purpose of using schemas in PostgreSQL?

A To improve query performance by indexing tables automatically

B To encrypt data stored in tables

C To organize database objects into separate namespaces to avoid name conflicts

D To automatically backup tables daily

💡 Hint

Think about why you might want to have multiple tables with the same name in one database.

Schemas allow grouping tables and other objects into separate namespaces. This helps avoid name clashes and organize objects logically.

📝 Syntax

advanced

Creating a schema and setting search path

Which of the following commands correctly creates a schema named marketing and sets it as the first schema in the search path?

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

B CREATE SCHEMA marketing; SET search_path = marketing, public;

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

D CREATE SCHEMA marketing; SET search_path = public, marketing;

💡 Hint

Remember the syntax for setting the search path and the order of schemas matters.

The command SET search_path TO marketing, public; sets the search path so that PostgreSQL looks first in the marketing schema, then public. The keyword TO is correct syntax.

❓ optimization

advanced

Optimizing schema usage for large databases

You have a large database with many tables. How can using schemas help optimize database management and queries?

A Schemas automatically partition tables to improve query speed

B By grouping related tables into schemas, you can manage permissions and backups more efficiently

C Schemas compress data to reduce storage size

D Schemas replicate tables across servers for load balancing

💡 Hint

Think about administrative tasks and security.

Schemas help organize tables logically, making it easier to assign permissions and manage backups per group. They do not automatically partition or compress data.

🔧 Debug

expert

Resolving ambiguous table reference error

You run the query SELECT * FROM employees; but get an error saying the table reference is ambiguous. You have two schemas, sales and hr, each with an employees table. How can you fix this error?

A Set the search_path to only one schema containing employees table

B Rename one of the employees tables to a different name

C Drop one of the employees tables

D Specify the schema explicitly in the query, e.g., <code>SELECT * FROM sales.employees;</code>

💡 Hint

Think about how to tell PostgreSQL exactly which table you want.

Explicitly specifying the schema removes ambiguity. Renaming or dropping tables is not always practical. Setting search_path might help but can still cause ambiguity if both schemas are in the path.