Why schemas matter in PostgreSQL - See It in Action

Why schemas matter in PostgreSQL

📖 Scenario: You are managing a small company's database using PostgreSQL. The company has different departments like Sales and HR, and you want to keep their data organized and separate inside the same database.

🎯 Goal: Build a simple PostgreSQL setup that creates two schemas named sales and hr, then create a table inside each schema to store department-specific employee data.

📋 What You'll Learn

Create two schemas named sales and hr

Create a table named employees 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 name (text)

Understand how schemas help keep tables organized and avoid name conflicts

💡 Why This Matters

🌍 Real World

Companies often have multiple departments with similar data needs. Schemas help keep their data organized and separate inside one database.

💼 Career

Database administrators and developers use schemas to manage complex databases, avoid naming conflicts, and improve security by controlling access at the schema level.

1

Create the sales schema

Write a SQL command to create a schema called sales in PostgreSQL.

PostgreSQL

-- Your code here to create the sales schema

Need a hint?

Use the CREATE SCHEMA statement followed by the schema name.

2

Create the hr schema

Write a SQL command to create a schema called hr in PostgreSQL.

PostgreSQL

CREATE SCHEMA sales;
-- Your code here to create the hr schema

Need a hint?

Use the same CREATE SCHEMA statement as before but with the name hr.

3

Create employees table in sales schema

Write a SQL command to create a table named employees inside the sales schema. The table should have two columns: id as an integer and name as text.

PostgreSQL

CREATE SCHEMA sales;
CREATE SCHEMA hr;
-- Your code here to create employees table in sales schema

Need a hint?

Use CREATE TABLE sales.employees and define the columns inside parentheses.

4

Create employees table in hr schema

Write a SQL command to create a table named employees inside the hr schema. The table should have two columns: id as an integer and name as text.

PostgreSQL

CREATE SCHEMA sales;
CREATE SCHEMA hr;
CREATE TABLE sales.employees (id INTEGER, name TEXT);
-- Your code here to create employees table in hr schema

Need a hint?

Use CREATE TABLE hr.employees and define the columns inside parentheses.