Schemas for namespace organization in PostgreSQL - Mini Project: Build & Apply

Schemas for Namespace Organization in PostgreSQL

📖 Scenario: You are managing a small company's database. To keep things tidy, you want to organize tables into different schemas based on departments.

🎯 Goal: Create schemas to organize tables by department and create tables inside those schemas.

📋 What You'll Learn

Create two schemas named sales and hr

Create a table named customers inside the sales schema with columns id (integer) and name (text)

Create a table named employees inside the hr schema with columns id (integer) and fullname (text)

Set the search path to include sales and hr schemas

💡 Why This Matters

🌍 Real World

Organizing database objects into schemas helps large applications keep data structured and avoid name conflicts.

💼 Career

Database administrators and developers use schemas to manage complex databases efficiently and maintain clear separation of data.

1

Create schemas for departments

Write SQL statements to create two schemas named sales and hr.

PostgreSQL

-- Your code here to create schemas sales and hr

Need a hint?

Use CREATE SCHEMA schema_name; to create a schema.

2

Create tables inside schemas

Create a table named customers inside the sales schema with columns id (integer) and name (text). Also create a table named employees inside the hr schema with columns id (integer) and fullname (text).

PostgreSQL

CREATE SCHEMA sales;
CREATE SCHEMA hr;
-- Your code here to create tables customers and employees inside schemas

Need a hint?

Use CREATE TABLE schema_name.table_name (columns); to create tables inside schemas.

3

Set the search path

Write a SQL statement to set the search path to include the sales and hr schemas in that order.

PostgreSQL

CREATE SCHEMA sales;
CREATE SCHEMA hr;

CREATE TABLE sales.customers (
    id INTEGER,
    name TEXT
);

CREATE TABLE hr.employees (
    id INTEGER,
    fullname TEXT
);

-- Your code here to set search path

Need a hint?

Use SET search_path TO schema1, schema2; to set the search path.

4

Verify table creation with schema prefix

Write SQL statements to select all columns from customers table in the sales schema and from employees table in the hr schema using schema-qualified names.

PostgreSQL

CREATE SCHEMA sales;
CREATE SCHEMA hr;

CREATE TABLE sales.customers (
    id INTEGER,
    name TEXT
);

CREATE TABLE hr.employees (
    id INTEGER,
    fullname TEXT
);

SET search_path TO sales, hr;

-- Your code here to select all from sales.customers and hr.employees

Need a hint?

Use SELECT * FROM schema_name.table_name; to query tables inside schemas.